Index Fragmentation for All Databases

Question

Post reply

Index Fragmentation for All Databases

Jon.Morisi

SSChampion

Points: 12846
More actions
June 9, 2009 at 10:25 am

#206002

Comments posted to this topic are about the item Index Fragmentation for All Databases

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Mark Thabet-457565 SSC Veteran Points: 270 More actions · Answer 1

Mark Thabet-457565

SSC Veteran

Points: 270

June 16, 2009 at 7:27 am

#1010167

I had a syntax error. Is it just me???

Jon.Morisi SSChampion Points: 12846 More actions · Answer 2

Jon.Morisi

SSChampion

Points: 12846

June 16, 2009 at 8:11 am

#1010200

the compatibility level all databases must be 90

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

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

kevin-1084732 Valued Member Points: 61 More actions · Answer 4

It would be great to have a version of this that works at compatibility level 65.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 5

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 7

Now that is interesting, didn't know what. Worth of a blog post.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 9

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

vic610 SSC Veteran Points: 203 More actions · Answer 10

Hi,

I am getting syntax error. please see below...

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '('.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '('.

Jon.Morisi SSChampion Points: 12846 More actions · Answer 11

The script is written such that the compatibility level all databases must be 90.

To check run

select compatibility_level from sys.databases

vic610 SSC Veteran Points: 203 More actions · Answer 12

vic610

SSC Veteran

Points: 203

March 31, 2010 at 9:37 am

#1143208

100

Jon.Morisi SSChampion Points: 12846 More actions · Answer 13

100 should work just fine, it's really SQL 2000 (80) that's the issue.

I use the script all the time on SQL 2005, SQL 2008 servers. If even 1 db is in mode 80 that's the error your typically get.

vic610 SSC Veteran Points: 203 More actions · Answer 14

Hi Jon,

Still getting this...

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '('.

(0 row(s) affected)

can you please copy and paste your script in the Article you posted, and run it in sql 2008? I hope you will get the same error as I got.

Thanks.