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

Help on sys.dm_db_index_physical_stats Expand / Collapse
Author
Message
Posted Wednesday, February 06, 2013 12:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:24 AM
Points: 165, Visits: 349
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.
Post #1416285
Posted Wednesday, February 06, 2013 12:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
Copy the data for those records

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1416293
Posted Wednesday, February 06, 2013 1:02 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 38,069, Visits: 30,363
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 2008, MVP
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

Post #1416295
Posted Wednesday, February 06, 2013 2:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:24 AM
Points: 165, Visits: 349
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?
Post #1416320
Posted Wednesday, February 06, 2013 2:22 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 38,069, Visits: 30,363
Huh? Don't understand the question.




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1416332
Posted Wednesday, February 06, 2013 3:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453
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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post
Post #1416369
Posted Wednesday, February 06, 2013 4:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:49 AM
Points: 13,436, Visits: 25,281
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1416404
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse