See BOL: http://technet.microsoft.com/en-us/library/ms174281(v=sql.105).aspx
This query will return the number of inserts, updates and delete operations against all indices in the targeted database:
SELECT
DB_NAME(database_id) [Database],ios.object_id [ObjectID],
QUOTENAME(OBJECT_SCHEMA_NAME(ios.object_id,database_id)) + N'.'+
QUOTENAME(OBJECT_NAME(ios.object_id, database_id)) [ObjectName],
i.index_id [IndexID],
i.name [IndexName],
i.fill_factor [FillFactor],
ios.partition_number [PartitionNumber],
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.[type_desc] [IndexType],
ios.leaf_insert_count [LeafInserts],
ios.leaf_update_count [LeafUpdates],
ios.leaf_delete_count [LeafDeletes],
FROM
sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios INNER JOIN sys.indexes i
ON ios.object_id = i.object_id
AND ios.index_id = i.index_id
ORDER BY
ios.page_latch_wait_count + ios.page_io_latch_wait_count DESC
This query will return
the TOP 10 objects with waits on page locks:
SELECT
TOP
10 OBJECT_NAME(os.object_id, os.database_id) [ObjectName],os.index_id [IndexID],
partition_number [PartitionNumber],
page_lock_wait_count [PageLockWaitCount],
page_lock_wait_in_ms [PageLockWaitMS],
CASE WHEN mid.database_id IS NULL THEN 'N' ELSE 'Y' END [MissingIndex]
FROM
sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL) os
LEFT OUTER JOIN
(SELECT DISTINCT database_id, object_id
FROM sys.dm_db_missing_index_details id ) id
ON os.database_id = id.database_id
AND os.object_id = id.object_id
ORDER BY
page_lock_wait_count DESC
No comments:
Post a Comment