April 10, 2014 at 5:45 am
Hi Guys,
Its me again..!!
I have a fact table, below is some more information on it,
# Records Reserved (KB)Data (KB) Indexes (KB)Unused (KB)
579,126,119389,356,128247,399,656141,672,088284,384
For simplicity please assume every metric is above 100GB. except the unused KB.
There are 8 indexes on this table, i ran the 'index physical statistic report' and it recommends me to perform reorganize on 4 of the indexes and rebuild on the other 4. How to go about maintaining index ifor such tables?
We have nearly 1000 Business objects (reporting tool) reports reading this table
Thanks for all your help.
April 10, 2014 at 5:58 am
As per your requirement and setup you can perform ALTER INDEX on object level for specific index or for all indexes. Also you can perform REBUILD or REORGANIZE.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 10, 2014 at 6:05 am
I'd suggest looking into using Michelle Ufford's scripts for maintaining your indexes. That way you don't have to write something yourself.
"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
April 10, 2014 at 6:54 am
hi grant, could you share the link please?
April 10, 2014 at 6:59 am
"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
April 10, 2014 at 7:03 am
thank you grant , my bad i was missing the 'defrag' keyword in my search ..
below is the link i used
http://lmgtfy.com/?q=Michelle+Ufford%27s+scripts+for+maintaining+your+indexes
April 10, 2014 at 7:46 am
I would also consider taking a look at Ola Hallegren's scripts. He has lots of documentation. The "recommended" maintenance is if fragmentatation is over 30%, rebuild, if it is between 5% and 30 %, reorg, and if it is less than 5% (or the table is very small) do nothing. Those numbers may or may not work for your environment.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply