July 21, 2009 at 12:44 pm
Hi,
I am new to SQL Server. In DEV only, but I think the issue is for administration so putting it here.
We had some client facing issue with slow SQL Sevrer DB, and after a while they confirmed that the issue was with their env where their indexes (for perticular table) were fragmented heavily [95-99%]
They rebuild all indexed and it resolved the performance issue.
Would you please confirm whats the way to find out a Fragmentation for a perticular table.
And how to rebuild all indexes?
This is my first question on site... So apologies if this is not the correct place to put such questions.
Thanks,
Jaimin Soni
July 21, 2009 at 12:46 pm
You could use SQL Profiler to generate a trace file and use that within the Database Index Tuning Wizard.
Not sure if this is exactly what you are looking for but hopefully points you to the right direction.
--
:hehe:
July 21, 2009 at 12:55 pm
Here is an article about index fragmentation
http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
And here is the script that I am currently using to defrag/rebuild all indexes.
There are several others available as well.
July 21, 2009 at 5:48 pm
Kill two birds with one stone... lookup "DBCC SHOWCONTIG" in Books Online and read all about it. Then, find section "E." in that same BOL article to see the code that will do this.
--Jeff Moden
July 22, 2009 at 11:33 am
Ken Simmons (7/21/2009)
Here is an article about index fragmentationhttp://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
And here is the script that I am currently using to defrag/rebuild all indexes.
There are several others available as well.
Ken,
I tried your second link and it took me to a login page.
July 22, 2009 at 11:46 am
nathan.holtrop (7/22/2009)
Ken Simmons (7/21/2009)
Here is an article about index fragmentationhttp://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
And here is the script that I am currently using to defrag/rebuild all indexes.
There are several others available as well.
Ken,
I tried your second link and it took me to a login page.
Check out the below article. It has some great useful information and somewhere I pointed the original OP to.
Thanks,
S
http://www.sql-server-performance.com/articles/per/index_fragmentation_p1.aspx
--
:hehe:
July 22, 2009 at 12:00 pm
Hi Jaimin,
AFAIK, the index has to be reorganized if the the amount of fragmentation is less than 30% and the index has to be rebuilt if the the amount of fragmentation is greater than 30% .
To find the amount of fragmentation, you can use the DMV
sys.dm_db_index_physical_stats
Thank You,
Best Regards,
SQLBuddy.
July 22, 2009 at 12:33 pm
sqlbuddy123 (7/22/2009)
Hi Jaimin,AFAIK, the index has to be reorganized if the the amount of fragmentation is less than 30% and the index has to be rebuilt if the the amount of fragmentation is greater than 30% .
To find the amount of fragmentation, you can use the DMV
sys.dm_db_index_physical_stats
Thank You,
Best Regards,
SQLBuddy.
Jaimin,
Some people will tell you 30% others will tell you 60%. Whatever you do make you do your rebuilds after hours when no one is using the system. Rebuild drops and recreates the indexes whereas reorg keeps them in place.
Nate
July 22, 2009 at 2:13 pm
Here is an article about index fragmentation
http://msdn.microsoft.com/en-us/library/ms189858(SQL.90).aspx
You can use DBCC CHECKTABLE command to check index of that tables...
Here is an script to reindex all tables in Database
Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
July 22, 2009 at 10:31 pm
Why not just use DBCC SHOWCONTIG and save it in a table?
Also, most folks might be able to live with 30% fragmentation, but I can't... the tables I work with have millions and tens of millions of rows. 30% fragmentation on a million row table basically means that I'll have more "out of place rows" than most people have in a table. 😛
--Jeff Moden
July 23, 2009 at 1:38 am
As Jeff already told, using DBCC SHOWCONTIG can give you a good idea about the status of fragmentation.
You can also use the "WITH TABLERESULTS" and "ALL_INDEXES" parameters.
DBCC SHOWCONTIG('TABLE') WITH TABLERESULTS, ALL_IDEXES
The "with tableresults" will give you some more details that can help you.
But, before conclude anything about the results that you will gave, it's a good idea to check about the index, if they are clustered or not.
Regards,
Shin-Iti.
July 23, 2009 at 5:18 pm
Hi,
For your information only
Microsoft discourages the use of DBCC SHOWCONTIG . This is the official statement from the Microsoft in BOL 2008
"This feature will be removed in the next version of Microsoft SQL Server . Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead."
http://msdn.microsoft.com/en-us/library/ms175008.aspx
So refrain from using DBCC SHOWCONTIG.If you have very large table you needdn't use 30%. You can use a lower value.
Thank You,
Yours
SQLBuddy.
July 23, 2009 at 5:40 pm
sqlbuddy123 (7/23/2009)
Hi,For your information only
Microsoft discourages the use of DBCC SHOWCONTIG . This is the official statement from the Microsoft in BOL 2008
"This feature will be removed in the next version of Microsoft SQL Server . Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_db_index_physical_stats instead."
http://msdn.microsoft.com/en-us/library/ms175008.aspx
So refrain from using DBCC SHOWCONTIG.If you have very large table you needdn't use 30%. You can use a lower value.
Thank You,
Yours
SQLBuddy.
That's nice... I wish Microsoft would stop deprecating stuff... folks stuck in the 2k world would sure appreciate it.
On the 30% stuff on a very large table... heck, I know I can use a lower value and do. I just wanted folks to know that the 30% recommendation certainly isn't for everyone. In fact, I'd make the recommendation that the 30% recommndation is way too high for most folks. THAT was the point I was really trying to make and obviously fell a bit short on.
--Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply