February 27, 2015 at 10:30 am
I have a standalone SQL Server, running SQL 2014 Standard. It is virtualized, running off VMWare. The box has ample resources (24 cores, and 192GB RAM, of which 128GB is allocated to MSSQL - maximum possible for Standard).
Lately the index rebuild speeds have increased significantly. For example, a rebuild that normally took 35 minutes now takes 1 hour.
I haven't made any changes to the environment. The data rate changes (that is, how much indexes become fragmented every day) has been relatively constant. The data set itself has grown, but not enough to double the time needed to rebuild the indexes.
When index rebuilds execute, the server is otherwise idle.
I've checked everything I could think of, but index rebuilds and performance thereof are a bit of a black box to me. Can anyone think of an obscure reason why the index rebuilds would suddenly take longer?
February 27, 2015 at 10:45 am
I assumed you double checked as you say it's idle, but did you make sure there aren't other jobs or processes kicking off at the same time the index is running?
February 27, 2015 at 11:19 am
That's correct. Double and triple. That was my first hunch actually.
February 27, 2015 at 1:01 pm
It could be any number of things. The best bet would be to capture wait statistics before and after to understand where the slow down is occurring. That will help narrow down the choices.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2015 at 1:09 pm
regis.traiter (2/27/2015)
I have a standalone SQL Server, running SQL 2014 Standard. It is virtualized, running off VMWare. The box has ample resources (24 cores, and 192GB RAM, of which 128GB is allocated to MSSQL - maximum possible for Standard).Lately the index rebuild speeds have increased significantly. For example, a rebuild that normally took 35 minutes now takes 1 hour.
I haven't made any changes to the environment. The data rate changes (that is, how much indexes become fragmented every day) has been relatively constant. The data set itself has grown, but not enough to double the time needed to rebuild the indexes.
When index rebuilds execute, the server is otherwise idle.
I've checked everything I could think of, but index rebuilds and performance thereof are a bit of a black box to me. Can anyone think of an obscure reason why the index rebuilds would suddenly take longer?
Do yourself a favor (thank me later)
Visit this page[/url] and implement Ola's solution, if you have not and you have the freedom to do it.
Once deployed, review command log table and see which tables have the longest rebuild times and how fragmentation changed from that week to previous one.
In my experience, if a defrag job is taking longer than expected and I have no server issues, is mostly due heavy inserts or deletes that I had that particular week against a particular database or table.
vCPU bottlenecks can also affect the speed of your defrag jobs. If you started having CPU issues or your CPU workload changed, you may also see decrease in performance when running your defrag jobs.
Last but not least. Check for any unusual query , antivirus, or something that all of the sudden started to use the disk at the same day and time. That will slow down your disk's performance and will also affect defrag jobs. And I'm referring not only to your VM but any possible "noisy neighbor". If your VM is in a SAN and you started having I/O issues due other stuff running hard at same time, it will also affect the I/O performance of your SQL server.
March 2, 2015 at 6:14 am
Just a couple of thoughts to add:-
1. No-one has added some maintenance tasks that you didn't know about, have they? Perhaps an ill-advised statistics rebuild just before your index rebuild runs?
2. Is your storage SSD based? It's becoming clear that such devices require periodic maintenance to prevent their performance from dropping off.
March 3, 2015 at 1:19 pm
Has the number of rows in the tables that you are rebuilding the indexes on greatly increased? Has anyone created any new indexes?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply