Index Views: IndexPhysical

2010-02-26 - General

With this post I am going to start a series about queries that I find helpful while researching indexes and index issues. In fact, because I use them often, I encapsulated them in views.

The first one is SATools.IndexPhysical:

CREATE VIEW SATools.IndexPhysical

AS

SELECT OBJECT_NAME(i.object_id) Tbl,

i.name Idx,

i.type_desc TYPE,

ps.partition_number AS Partition,

ps.index_level AS Level,

ps.alloc_unit_type_desc AS DataType,

ps.record_count AS Records,

ps.page_count AS Pages,

ps.fragment_count AS Fragments,

ps.avg_fragmentation_in_percent AS FragPerc,

ps.min_record_size_in_bytes AS MinRecBytes,

ps.avg_record_size_in_bytes AS AvgRecBytes,

ps.max_record_size_in_bytes AS MaxRecBytes,

ps.avg_page_space_used_in_percent AS PageSpaceUsedPerc

FROM sys.indexes AS i

JOIN sys.dm_db_index_physical_stats(DB_ID(), DEFAULT, DEFAULT, DEFAULT,

'DETAILED') AS ps

ON i.object_id = ps.object_id AND

i.index_id = ps.index_id;

This view gives you information about Index depth, approximate number of records, average page fullness and average page fragmentation.

Take for example this table:

IF OBJECT_ID('dbo.IndexTest') IS NOT NULL

DROP TABLE dbo.IndexTest ;

GO

CREATE TABLE dbo.IndexTest (Id INT

CONSTRAINT IndexTest_PK PRIMARY KEY CLUSTERED,

Uk INT NOT NULL,

IdxFill CHAR(800)

NOT NULL

CONSTRAINT IndexTest_IdxFill_Dflt

DEFAULT REPLICATE('I', 800),

PageFill CHAR(7000)

NOT NULL

CONSTRAINT IndexTest_PageFill_Dflt

DEFAULT REPLICATE('P', 7000),

CONSTRAINT IndexTest_U1 UNIQUE (Uk, IdxFill)

) ;

GO

INSERT INTO dbo.IndexTest

(Id,

Uk

)

SELECT n,

CHECKSUM(NEWID())

FROM dbo.GetNums(1000) ;

GO

It contains two integer columns and to fill columns. The fill columns serve the purpose of filling enough pages, so that we get to see multiple levels in the indexes. There are two (implicit) indexes declared on this table. It is filled with 1000 rows using Itzik's GetNums function.

SATools.IndexPhysical returns this information:

Tbl

Idx

TYPE

Partition

Level

DataType

Records

Pages

Fragments

FragPerc

MinRecBytes

AvgRecBytes

MaxRecBytes

PageSpaceUsedPerc

IndexTest

IndexTest_PK

CLUSTERED

1

0

IN_ROW_DATA

1000

1000

5

0.4

7815

7815

7815

96.5530022238695

IndexTest

IndexTest_PK

CLUSTERED

1

1

IN_ROW_DATA

1000

3

3

0

11

11

11

53.5128984432913

IndexTest

IndexTest_PK

CLUSTERED

1

2

IN_ROW_DATA

3

1

1

0

11

11

11

0.457128737336299

IndexTest

IndexTest_U1

NONCLUSTERED

1

0

IN_ROW_DATA

1000

112

30

25

809

809

809

89.437509266123

IndexTest

IndexTest_U1

NONCLUSTERED

1

1

IN_ROW_DATA

112

27

27

0

811

811

811

41.6412774894984

IndexTest

IndexTest_U1

NONCLUSTERED

1

2

IN_ROW_DATA

27

6

6

0

811

811

811

45.1754385964912

IndexTest

IndexTest_U1

NONCLUSTERED

1

3

IN_ROW_DATA

6

1

1

0

811

811

811

60.2421546824809

It shows nicely the tree levels of the clustered index and the four levels of the nonclustered index and how many records and how many pages are part of each index level.

If you have a table, that is partitioned like this

CREATE PARTITION FUNCTION IndexTest_PF(SMALLINT) AS RANGE LEFT FOR VALUES (0) ;
GO
CREATE PARTITION SCHEME IndexTest_PS AS PARTITION IndexTest_PF ALL TO ([PRIMARY]) ;
GO

CREATE TABLE dbo.IndexTest (Id INT,

ArchiveInd SMALLINT

NOT NULL

CONSTRAINT IndexTest_ArchiveInd_Dflt DEFAULT 0,

PageFill CHAR(3000)

NOT NULL

CONSTRAINT IndexTest_PageFill_Dflt

DEFAULT REPLICATE('P', 3000),

CONSTRAINT IndexTest_PK PRIMARY KEY CLUSTERED

(Id, ArchiveInd) ON IndexTest_PS ( ArchiveInd)

) ;

GO

INSERT INTO dbo.IndexTest

(Id,

ArchiveInd

)

SELECT n,

SIGN(ABS(CHECKSUM(NEWID())) % 5)

FROM dbo.GetNums(2000) ;

SATools.IndexPhysical returns this information:

Tbl

Idx

TYPE

Partition

Level

DataType

Records

Pages

Fragments

FragPerc

MinRecBytes

AvgRecBytes

MaxRecBytes

PageSpaceUsedPerc

IndexTest

IndexTest_PK

CLUSTERED

1

0

IN_ROW_DATA

374

187

4

1.6

3013

3013

3013

74.47

IndexTest

IndexTest_PK

CLUSTERED

1

1

IN_ROW_DATA

187

1

1

0

13

13

13

34.63

IndexTest

IndexTest_PK

CLUSTERED

2

0

IN_ROW_DATA

1626

813

6

0.62

3013

3013

3013

74.47

IndexTest

IndexTest_PK

CLUSTERED

2

1

IN_ROW_DATA

813

3

3

0

13

13

13

50.2

IndexTest

IndexTest_PK

CLUSTERED

2

2

IN_ROW_DATA

3

1

1

0

13

13

13

0.53

Here you can see that the clustered index has two partitions. Partition 1 has two index levels and partition 2 has three index levels.

I both cases you can see how badly (in percent) each index level is fragmented (Column FragPerc). You also can get information about the length of each record in the index (level 0) or the length of each key (level > 0). In these examples Min, Avg and Max Bytes per Record are the same, because all fields are fixed length. If you have variable length fields, you will get different values here.

Categories: General

Leave a Reply

We know how to make a database fast. We’ll help you do the same.

1.832.3SQLITY
Ask for our friendly assistance

From the sqlity.net blog

Read more posts »