Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Index Fragmentation for All Databases Expand / Collapse
Author
Message
Posted Tuesday, June 9, 2009 10:25 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
Comments posted to this topic are about the item Index Fragmentation for All Databases
Post #731628
Posted Tuesday, June 16, 2009 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:48 AM
Points: 7, Visits: 68
I had a syntax error. Is it just me???
Post #735645
Posted Tuesday, June 16, 2009 8:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
the compatibility level all databases must be 90
Post #735684
Posted Tuesday, June 16, 2009 8:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
Be careful running a script like this that touches all indexes on all databases. Even though the LIMITED mode is only oging to read the level of the index above the leaf-level to get the logical fragmentation (I wrote the code inside SQL Server), running it over all indexes on all databases may do the equivalent of flushing out your buffer pool.

A much better approach once you've got some knowledge of which indexes are your problem-children is to have a table with those indexes in and only check fragmentation on them - rather than everything in all databases. This does much less, and more targeted reads.

If you don't have a clue about what the problem is though, this script is great.

Thanks


Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #735696
Posted Tuesday, June 16, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 8, 2010 2:31 PM
Points: 1, Visits: 4
It would be great to have a version of this that works at compatibility level 65.
Post #735827
Posted Tuesday, June 16, 2009 11:03 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 10, 2014 4:15 AM
Points: 293, Visits: 816
This is pseudocode, but you could do something like this...if sp_MSForEachDB works in mode 65

BEGIN
CREATE TABLE #INDEXFRAGINFO
--Create table structure based on result fields
DECLARE @command VARCHAR(128)
SELECT @command = 'Use [' + '?' + '] dbcc showcontig WITH ALL_INDEXES, TABLERESULTS, FAST'
INSERT #INDEXFRAGINFO EXEC sp_MSForEachDB @command
SELECT * FROM #INDEXFRAGINFO
DROP TABLE #INDEXFRAGINFO
END
GO

Post #735861
Posted Tuesday, June 16, 2009 11:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
You don't need to use DBCC SHOWCONTIG for databases in non-90 compat modes. As long you run the DMV script in a database that's in 90-compat mode, it'll work fine against databases that aren't. Common misconception.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #735870
Posted Tuesday, June 16, 2009 12:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
Now that is interesting, didn't know what. Worth of a blog post.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #735929
Posted Tuesday, June 16, 2009 1:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
ok - will do. It's lunchtime - nothing else to do

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #735988
Posted Tuesday, June 16, 2009 1:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
Here you go: Misconceptions about running DMVs on database with lower compatibility levels

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #736006
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse