Fancy Missing Indexes

You've seen fancy ketchup. Here's fancy missing indexes.

This script is database specific. It does not use the server-wide DMV's alone, it joins to INFORMATION_SCHEMA.TABLES to generate index create scripts. This is a very fast way to enter an environment, and take a peek at the indexing situation, something I do regularly as a DBA consultant.

SELECT
improv = migs.avg_total_user_cost * (avg_user_impact/50)
, mid.statement
, create_index_statement = 'CREATE NONCLUSTERED INDEX IDX_NC_' + t.table_name
+ replace(replace(replace(ISNULL(mid.equality_columns,'') , '], [' ,'_'),'[','_'),']','')
+ replace(replace(replace(ISNULL(mid.inequality_columns,''), '], [' ,'_'),'[','_'),']','')
+ ' ON ' + statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '')
, unique_compiles, migs.user_seeks, migs.user_scans, last_user_seek, avg_total_user_cost, avg_user_impact, mid.equality_columns, mid.inequality_columns, mid.included_columns, t.table_catalog
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
INNER JOIN INFORMATION_SCHEMA.tables t
on t.table_name = replace(replace(substring(substring(statement, charindex('.', statement)+2, len(statement)), charindex('.', substring(statement, charindex('.', statement)+1, len(statement))), len(statement)),'[',''),']','')
and t.table_schema = replace(replace(substring( substring( statement , charindex('.', statement)+2 , len(statement) ), 0, charindex('.', substring( statement , charindex('.', statement)+2 , len(statement) ) )),'[',''),']','')
and db_name() = t.table_catalog
and db_ID() = mid.database_id
WHERE 1=1
--and (datediff(week, last_user_seek, getdate())) <> 1
AND migs.avg_total_user_cost * (avg_user_impact/75) > 5
or migs.avg_user_impact > 50
order by
improv desc

Pardon blogspot's inane raw code posting ability.

• The improv calc up top is simply to evaluate the relative use of a potential index against others that show up in the report. Its not terribly scientific but weighs usage and value.
• The WHERE clause items can be used depending on the situation. If your server has been operating for a while and you'd like to see what indexing needs recent changes have been generating, use the date filter. Have a lot of ad hocs? Filter on unique_compiles > 1. The third WHERE clause is the same as the improv value above, helps me filter out things that are too small to care about. Lastly, I filter out anything that won't have at least a 50% impact. Oftentimes, I'll set this last value much higher.
• Joins to INFORMATION_SCHEMA.TABLES to generate a proper index create statement. It follows a naming convention I prefer, IDX_NC_ for nonclustered indexes, followed by the table name and the key fields. For covering indexes, I'll remove the key fields from the name and just put "covering". Your naming convention for indexes is probably different.
  
• Before creating any indexes from the missing index DMV's, do a little research on them and keep in mind that they reset with the server. It maybe useful to retain these over time, spanning multiple resets.
• Oftentimes, you'll see many of the rows in this result overlap. The INCLUDES might be in a different order, or you'll see one suggested index that has all but one other field that another suggestion has. Intelligently combine these together, keeping in mind that order DOES matter in the key fields.
  
• Lastly, if you've examined existing indexing on the table, use the provided creation script in the column create_index_statement .
• This script, aside from a few tweaks here and there, is hardly an original creation of this blog. As part of your research on missing index DMV's, don't miss: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Other linkage:
http://msdn.microsoft.com/en-us/library/ms179325.aspx
http://blogs.digineer.com/blogs/jasons/archive/2009/04/19/analyze-this-your-indexes-analysis-part-1.aspx
http://www.mssqltips.com/tip.asp?tip=1634

"As I leave you I want you to know... just think how much you'll be missing." -Richard Nixon

Don't stop that mouse from moving

Awesome.

http://support.microsoft.com/kb/168702

Method 2: Move Your Mouse Pointer
If you move your mouse pointer continuously while the data is being returned to Microsoft Excel, the query may not fail. Do not stop moving the mouse until all the data has been returned to Microsoft Excel.

NOTE: Depending on your query, it may take several minutes to return the results of your query to the worksheet.

"Words represent your intellect. The sound, gesture and movement represent your feelings." -Patricia Fripp

Defrag Complications

Former colleague of mine called me up on a Saturday morning. His manager, who spends most of his day staring at a unnamed, 3rd party, leading SQL monitoring software, was really giving him hell about a table in one of their performance databases that was at 90% fragmentation.

Rebuilding the indexes didn't seem to help. So I asked him how large the table was, and it was nine pages.

I feel sorry for my buddy, who now has to try and explain this to his boss. His report of high-fragmentation tables is just not going to be cleared unless he can filter out the tiny tables.

"Against stupidity the gods themselves contend in vain." -Friedrich von Schiller

clear out DTA indexes and statistics

Here's a helpful script I keep around in my portable HD. When the Database Tuning Advisor fails or is aborted for whatever reason, it can leave behind its hypothetical indexes and stats. This script will remove these active indexes and stats from your database. Uses the is_hypothetical flag to find indexes. Uses the _dta prefix to find stats. This assumes that when you implement DTA-recommended stats, you change their name (always a best practice). If you have implemented DTA-recommneded stats and not changed their name, this script will remove those too.

WHILE EXISTS (SELECT * FROM sys.indexes WHERE is_hypothetical = 1)
BEGIN
DECLARE @sql varchar(max)
SELECT @sql = 'drop index ' + indexname + ' on [' + tablename + ']'
FROM (
SELECT TOP 1 indexname = i.name, tablename = o.name
FROM sys.indexes i
INNER JOIN sys.objects o
ON i.object_id = o.object_id
WHERE i.is_hypothetical = 1 and o.type = 'u'
) x
SELECT @sql
EXEC (@sql)
END
GO

WHILE EXISTS (SELECT * FROM sys.stats i WHERE OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND i.[name] LIKE '_dta%' and user_created = 0)
BEGIN
DECLARE @sql varchar(max)
SELECT @sql = 'drop statistics [' + object_name(i.[object_id]) + '].['+ i.[name] + ']'
FROM sys.stats i
WHERE
OBJECTPROPERTY(i.[object_id],'IsUserTable') = 1 AND
i.[name] LIKE '_dta%' and user_created = 0
SELECT @sql
EXEC (@sql)
END

See also:
I originally posted this on tsqlscripts.com (drink!)
http://www.graytechnology.com/Blog/post.aspx?id=e21cbab0-8ae2-478e-a027-1b3b14e7d0b9
http://weblogs.sqlteam.com/mladenp/archive/2007/07/16/60257.aspx
http://www.sqlservercentral.com/Forums/Topic398549-360-1.aspx


"The great tragedy of science - the slaying of a beautiful hypothesis by an ugly fact." -T.H. Buxley

blog intent

Pardon the title, which treads the thin ice of being too wannabe-serious. My first dozen or so attempts at a sql blog title failed for not being unique enough or not available on blogspot.

The intent of this blog is not personal. As a consultant database administrator in the South, I'm regularly encountering things that I think other DBA's should have quicker access to. Follow this blog if you like, but its intended purpose is to be that one resource on the web that specifically addresses some DBA's problem or question.

If you've found this blog via a web search engine and it helped you out, then this small effort on my part has served its purpose. Writing this initial post is more for my own edification and benefit, a mission statement of sorts.

"There is no restraining men's tongues or pens when charged with a little vanity." - George Washington

Launch