Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_buddy
Starting Member
41 Posts |
 Posted - 2009-07-14 : 03:21:41
|
| how could we check performance of the index we have made on our view or tables in sql 2005 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-14 : 03:32:20
|
| SELECT t.name AS table_name , i.name AS index_name , i.type_desc AS index_type , i.is_primary_key , ISNULL(u.user_seeks,0) AS seeks , ISNULL(u.user_scans,0) AS scans , ISNULL(u.user_lookups,0) AS lookups, ISNULL(u.user_updates,0) AS updates, SUM( CASE WHEN a.type = 1 THEN p.rows ELSE 0 END) AS records_in_index, -- Only count the rows once (SUM(a.total_pages) * 8) / 1024.00 AS index_size_in_MB -- Pages are 8 Bytes in sizeFROM sys.indexes i INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats u ON u.index_id = i.index_id AND u.object_id = i.object_idWHERE t.type_desc = 'USER_TABLE' AND i.type_desc IN ('CLUSTERED', 'NONCLUSTERED') AND t.name NOT LIKE 'sys%' -- Some system tables show as user tables (?)GROUP BY t.name , i.name , i.type_desc , i.is_primary_key, u.user_seeks , u.user_scans , u.user_lookups , u.user_updates , u.object_id , u.index_idtable_name - The table name that the index is on index_name - The name of the index index_type - CLUSTERED (only one per table, most commonly on primary keys) or NONCLUSTERED seeks - Number of times an index seek has been used on this index. Seeks are used to find a unique value and are the fastest. (Imagine thumbing through the Yellow pages directly to the R's.) scans - Number of times an index scan has been used on this index. Scans are commonly used to find a range of values. lookups - Applies only to Clustered Indexes. This is the number of times this index was used in a bookmark lookup. This happens in an un-covered query where the records were found using another index, but additional columns are required to be returned, so SQL Server turns to this index to get the remaining data using a "bookmark" pointer stored in the non clustered index. updates - This is the number of times this index was updated due to an insert or update on the table. Updates are a requirement, but they are a big trade off since they are sometimes costly. records_in_index - This is the number of records in the index. Index scans and updates slow down as this number increases. index_size_in_MB - This is the size of the data in the index in MBs. If database size is important to you, large indexes should be noted. -------------------------R.. |
 |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
|
|
|
|
|
|
|
checking the performance of indexed