sys.dm_db_index_physical_stats - Doubt in the Results

Question

sys.dm_db_index_physical_stats - Doubt in the Results

balasach82

SSCrazy Eights

Points: 9653
More actions
July 17, 2013 at 2:45 pm

#275422

For a table with 3 indexes, 1 clustered and 2 NC, Index Physical status dmv returned 53 rows with clustered index repeated 4 times with varying % fragmentation, similarly for other 2 indexes too.
why multipls rows for one index?

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

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

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

One for each level of the index.

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/ms188917.aspx

Returns size and fragmentation information for the data and indexes of the specified table or view. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

I ran indexphysical stats dmv and found 4 rows for 1 clustered index with depth 4 row having 80% fragmentation. Even after runnning, ALTER INDEX [INDEX] ON [dbo].

REBUILD WITH (FILLFACTOR=90, online=on). Fragmentation remains at 100%.

1. How to reduce this fragmentation in Clustered index at depth 4(in general for all depth rows)

2. How to reduce frag for NC which too are at 100%

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

balasach82 (7/18/2013)
1. How to reduce this fragmentation in Clustered index at depth 4(in general for all depth rows)

Don't bother trying. The highest level is the root, which is only a single page.

2. How to reduce frag for NC which too are at 100%

Same way you do for a clustered index. Alter index ... rebuild or Alter index .. reorganize

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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 5

1. The other 3 Depth rows (other than 1) has more frag >70. Rebuild reduced frag for depth id 1 row

2. NC - I ran both rebuild and reorganize for couple of NC. There was no change in frag levels

My select which accesses these tables runs very slow in one server, but runs very quickly in another server with less config setup (less cpu, ram etc)

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

Ignore the non-leaf levels unless they're absolutely huge.

Let me guess, those nonclustered indexes are tiny, just a couple of pages?

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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 7

Yes, starting from 1 to 230 is the page count for such NC which has more % frag

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

Too small to bother with.

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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 9

Gail, what else can be done to improve query running time. Since query runs in <1min in another dev server but runs for 2hrs in production machine

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

Well index rebuilds are unlikely to make noticable improvements in performance, so maybe the question should rather be 'what can be done'

Tune the query to use indexes, modify/add indexes to support the query. That's the basis of performance tuning.

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 11

GilaMonster (7/18/2013)
Well index rebuilds are unlikely to make noticable improvements in performance, so maybe the question should rather be 'what can be done'
Tune the query to use indexes, modify/add indexes to support the query. That's the basis of performance tuning.

I was reading this thread and couldn't really find an answer why the query is running so fast in Dev and takes long on Prod. Gail, can you suggest anything, in terms of that....because if everything you mentioned above could be the issue, then it should behave same in both env, correct?

Regards,
SQLisAwe5oMe.

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

SQLisAwE5OmE (7/18/2013)
I was reading this thread and couldn't really find an answer why the query is running so fast in Dev and takes long on Prod. Gail, can you suggest anything, in terms of that....

Data volumes, concurrent load, there are hundreds of possible causes.

because if everything you mentioned above could be the issue, then it should behave same in both env, correct?

No, definitely not.

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

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 13

Gail,

Both the db's are exact copy of each other and return same amount of records.

In the server where its working i see parallel query threads. But in issue server, only one thread, no parallel execution. there is nothing against Wait type, its just blank in activity monitor.

So i tried with max dop in query and gave 16. even then it keeps running. But in the dev server, which hs 2 processors, it runs fine.

I am at clueless as to what to do next. Any guidance would be helpful

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

Tune the queries, tune the indexes. There's no magic 'go-faster' button

Grant Frichey's written a good book on performance tuning or you can identify the problematic portion of the procedure and post a thread here for assistance in tuning

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