index

I had the pleasure of co-presenting a webcast for SentryOne with Kevin Kline and Jason Hall recently as we highlighted their product: SQL Sentry and also raised awareness about the approaching SQL Cruise this January in the Caribbean. We spent the hour talking theory, internals of SQL Server, and both the Dynamic Management Objects and System Catalog Views used in the scripts I’m providing here to help diagnose how well you’re creating, maintaining, and making use of the indexes in your SQL Server environment.

In the scripts provided in this link I give you the ability to address the following questions:

USAGE

• Which tables are read from | written to most | least?
• Which tables have no activity since last restart | rebuild?
• What are the use patterns for all indexes?

PHYSICAL

• What are my largest indexes?
• Which indexes are fragmented?
• Which indexes that are most used for reads most fragmented?

PERFORMANCE

• Which indexes have the most page splits?
• Which indexes have the most latching | locking time?
• Which indexes have the highest average latching | locking time per user interaction?
• Which indexes have the most table lock promotion attempts | successes?
• Are there any missing indexes that could help reduce latch | lock | IO latch waits?

INFORMATIONAL

• What indexes are on (this) table?

Listen to the webcast and want to know more about SQL Server, Data Science for DBAs, Powershell, and get free consulting time and a sun tan? If so register for SQL Cruise today!