Help on sys.dm_db_index_physical_stats

Question

Post reply

Help on sys.dm_db_index_physical_stats

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
February 6, 2013 at 12:41 am

#394112

Hi all,
I am trying sys.dm_db_index_physical_stats. I observed something about sys.dm_db_index_physical_stats and i am not able to make it out why it is so. I search on internet for the same but to no help.
First i created a table as
CREATE TABLE EMP2 (EMPID INT,EMPNAME NVARCHAR(100))
Then i created a clustered on it as
CREATE CLUSTERED INDEX TEMP_CL_ID ON EMP2(EMPNAME)
Now when i fried a query on sys.dm_db_index_physical_stats for the index i have created (using object_id for that index), it return me 2 record . I don't know why i did return me 2 records. Could anybody explain me why is this happening.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Copy the data for those records

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

If you use the detailed option, index physical stats shows you all levels of the indexes, so that'll be leaf and non-leaf (root if there's only 2). You can see that with the index_level column.

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

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 3

GilaMonster (2/6/2013)
If you use the detailed option, index physical stats shows you all levels of the indexes, so that'll be leaf and non-leaf (root if there's only 2). You can see that with the index_level column.

Hi Gila i am just a newbie and wanted to know wheather i am correct or not : Leaf/non-leaf of a index is nothing but a Page storage?

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

Huh? Don't understand the question.

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Shadab Shah (2/6/2013)
Hi Gila i am just a newbie and wanted to know wheather i am correct or not : Leaf/non-leaf of a index is nothing but a Page storage?

Read these articles http://msdn.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

and http://msdn.microsoft.com/en-us/library/aa174537(v=sql.80).aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Shadab Shah (2/6/2013)
GilaMonster (2/6/2013)
If you use the detailed option, index physical stats shows you all levels of the indexes, so that'll be leaf and non-leaf (root if there's only 2). You can see that with the index_level column.
Hi Gila i am just a newbie and wanted to know wheather i am correct or not : Leaf/non-leaf of a index is nothing but a Page storage?

An index is stored as a balanced tree. This means it has a series of pages going down. They appear as different levels. When you look at the detailed plan, you get each of these levels. The bottom level, where the details of the index (and for a clustered index, all the data in the table) is referred to as the leaf level.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning