March 17, 2014 at 12:37 am
Hi,
I have a Database of more than 480 GB and some of the table indexes are heavily fragmented (% fragment) more than 60 and record counts are an average of above 50 million.
Currently we have Index rebuild on week end and we are facing some performance issue and these tables will have some kind of inserts/updates for every day job run.What would be suggesting to make this scenario good.
Please advise. Do we need to update the statistics on every index rebuild/reorg ?
SQL : 2008 R2 Enterprise Edition.
March 17, 2014 at 1:29 am
It all depend on your environment. You can do Reindexing but advisable to have low activity on the server. You can have reorg + reindexing of highly fregmented indexes. Least you can have reorg on your database.
If you are using SQL Server Enterprise Edition than you can do online reindexing.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 17, 2014 at 2:18 am
I completely agree with free_mascot's answer, but just wanted to add few points. Fragmentation does some damage but most of it is for queries that have range scan on fragmented tables, so I would start first to deal with tables that queries on them return lots of record and deal last with tables that I have more seeks for one record on.
As for update statistics – this has nothing to do with fragmentation. It has to do with the evaluations of number of rows that will be returned in each step of the query plan, so you should update statistics if you see that you are getting wrong query plans or if you want to prevent it before it will happen, but not because you had maintenance on the index. Also if you rebuild an index, the server will update the statistics for that index. In fact that will be the best statistics because it will sample the whole table (since it has to go over all the records). If after you rebuild the index, you'll update the statistics, you'll get statistics from a sample which of course will be less reliable.
Another thing that you might look into is using a different fill factor for those tables.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 17, 2014 at 3:26 am
Are you seeing performance issues? Have you determined that these issues are directly related to the fragmentation of the indexes? If the answer to both these is yes, then you still need to defragment your indexes, but you may need to break up the process, only do the large ones on the weekend and do smaller ones on week nights or something.
And as was noted above, but bears repeating, if you do an index rebuild (not a reorganization) you have a new set of statistics. Don't update statistics after an index rebuild. If you use a sampled setting for the statistics update (the default), then you'll be making less accurate statistics. If you use a full scan, you're just replicating what was already done.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply