X hits on this document

117 views

0 shares

0 downloads

0 comments

32 / 48

666

CHAPTER 28 PERFORMANCE TUNING

, { mode | NULL | DEFAULT }

)

The arguments of this command are described in Table 28-5.

T a b l e 2 8 - 5 . s y s . d m _ d b _ i n d e x _ p h y s i c a l _ s t a t s A r g u m e n t s

Argument database_id | NULL

Description

The database ID of the indexes to evaluate. If NULL, all databases for the SQL Server instance are returned.

object_id | NULL

The object ID of the table and views (indexed views) to evaluate. If NULL, all tables are returned.

index_id | NULL | 0

The specific index ID of the index to evaluate. If NULL, all indexes are returned for the table(s).

p a r t i t i o n _ n u m b e r |

NULL

The specific partition number of the partition to evaluate. If NULL,

all partitions are returned based on the defined database/table/indexes selected.

LIMITED | SAMPLED | DETAILED | NULL | DEFAULT

These modes impact how the fragmentation data is collected. The LIMITED mode scans all pages for a heap and the pages above the leaf-level. SAMPLED collects data based on a 1% sampling of pages in the heap or index. The DETAILED mode scans all pages (heap or index). DETAILED is the slowest, but most accurate option. Designating NULL or DEFAULT is the equivalent of the LIMITED mode.

In this example, the sys.dm_db_index_physical_stats dynamic management view is queried for all objects in the AdventureWorks database with an average fragmentation percent greater than 30:

USE AdventureWorks GO

index_type_desc

avg_fragmentation_in_percent

NONCLUSTERED INDEX

33.3333333333333

CLUSTERED INDEX

50

HEAP

54.1666666666667

CLUSTERED INDEX

57.1428571428571

NONCLUSTERED INDEX

66.6666666666667

CountryRegion

1

DatabaseLog

0

Employee

1

Employee

2

S E L E C T O B J E C T _ N A M E ( o b j e c t _ i d ) O b j e c t N a m e ,

index_id, index_type_desc, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'),NULL, NULL, NULL, WHERE avg_fragmentation_in_percent > 30 ORDER BY OBJECT_NAME(object_id)

'LIMITED')

...

This returns the following (abridged) results:

ObjectName BillOfMaterials

index id 2 _

This second example returns fragmentation for a specific database, table, and index: SELECT OBJECT_NAME(f.object_id) ObjectName,

  • i.

    name IndexName,

  • f.

    index_type_desc,

  • f.

    avg_fragmentation_in_percent

F R O M s y s . d m _ d b _ i n d e x _ p h y s i c a l _ s t a t s

Document info
Document views117
Page views117
Page last viewedFri Dec 09 10:13:22 UTC 2016
Pages48
Paragraphs987
Words15822

Comments