How do I get fragmentation levels for indexes...

Question

Post reply

How do I get fragmentation levels for indexes...

jblovesthegym

Ten Centuries

Points: 1048
More actions
May 1, 2012 at 5:46 am

#98630

...WITHOUT creating a sproc? Is there a Select I can run to get fragmentation levels for all indexes on a particular table/db/server?

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

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

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

Just did a quick google search:

http://www.mssqltips.com/sqlservertip/1708/index-fragmentation-report-in-sql-server-2005-and-2008/

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

jblovesthegym (5/1/2012)
...WITHOUT creating a sproc? Is there a Select I can run to get fragmentation levels for all indexes on a particular table/db/server?

SQL Server 2005 Diagnostic Information Queries

Glenn Berry

January 2011

http://sqlserverperformance.wordpress.com/

Twitter: GlennAlanBerry

Frag stats are near the bottom. Beware - it can take a while to run especially if your indexes are in a mess. Read about the limitations of sys.dm_db_index_physical_stats on Paul Randall's blog before playing with it and save yourself some time if you want fill factor, reads & writes output with frag stats.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jblovesthegym Ten Centuries Points: 1048 More actions · Answer 3

jblovesthegym

Ten Centuries

Points: 1048

May 1, 2012 at 7:11 am

#1481771

This is for SS 2000, gentlemen!!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

jblovesthegym (5/1/2012)
This is for SS 2000, gentlemen!!

Oops - humble apologies, JB. Glen's written the same suite for different versions, have you looked for 2k?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jblovesthegym Ten Centuries Points: 1048 More actions · Answer 5

jblovesthegym

Ten Centuries

Points: 1048

May 2, 2012 at 1:43 am

#1482212

Not found, no.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

jblovesthegym (5/2/2012)
Not found, no.

Here you go.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jblovesthegym Ten Centuries Points: 1048 More actions · Answer 7

ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
Not found, no.
Here you go.

??

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

jblovesthegym (5/2/2012)
ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
Not found, no.
Here you go.
??

What's up?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kent.mcconnell Grasshopper Points: 18 More actions · Answer 9

@jblovesthegym, the here you go was a hyperlink you need to click on to follow

jblovesthegym Ten Centuries Points: 1048 More actions · Answer 10

ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
Not found, no.
Here you go.
??
What's up?

Fellas,

1) The link was not present on my screen, possibly the result of being stripped out of the page by corporate AV software - I only saw it when I attempted to quote Chris' post;

2) The link does NOT explain how to determine fragmentation, which is the entire point of this thread;

3) I posted the issue to another forum, and the first post gave me the resolution ; DBCC ShowContig.

Thanks for your efforts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

jblovesthegym (5/3/2012)
2) The link does NOT explain how to determine fragmentation, which is the entire point of this thread;

Really? Quoting from that article:

Using DBCC SHOWCONTIG to Identify Fragmented Indexes
When deciding to defragment, you must first identify the fragmented indexes. DBCC SHOWCONTIG allows you to measure fragmentation and page density levels on indexes.

Followed by a page or so on DBCC Showcontig, including how to interpret the output.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

jblovesthegym (5/3/2012)
ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
ChrisM@Work (5/2/2012)
jblovesthegym (5/2/2012)
Not found, no.
Here you go.
??
What's up?
Fellas,
1) The link was not present on my screen, possibly the result of being stripped out of the page by corporate AV software - I only saw it when I attempted to quote Chris' post;
2) The link does NOT explain how to determine fragmentation, which is the entire point of this thread;
3) I posted the issue to another forum, and the first post gave me the resolution ; DBCC ShowContig.
Thanks for your efforts.

Three quarters down the first page of the link:

"Before you defragment indexes, ensure that system resources issues, such as physical disk fragmentation or inappropriate schemas, are not adversely affecting workload performance.

DBCC SHOWCONTIG allows you to determine the amount of index fragmentation. When you run this statement, pay particular attention to the values for Logical Fragmentation and Page Density."

The context of the statement is significant.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jblovesthegym Ten Centuries Points: 1048 More actions · Answer 13

jblovesthegym

Ten Centuries

Points: 1048

May 3, 2012 at 7:40 am

#1483024

I sit corrected on #2.