<cffunction name="getPotentiallyUsefulIndexes" output="No" returntype="any" description="identifies potentially useful indexes. The script makes recommendations for changes but does not actually modify a query">
<cfargument name="DBName" default="" type="any">
<cfargument name="DBUserName" default="dbo" type="any">
<cfargument name="DBTblName" default="" type="any">
<cfset var getPotentiallyUsefulIndexes = "">
<cfquery name="getPotentiallyUsefulIndexes" datasource="#dsn#" username="#userName#" password="#passwd#">
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
,sys.dm_db_missing_index_groups g
,sys.dm_db_missing_index_details d
where s.group_handle = g.index_group_handle
and d.index_handle = g.index_handle
<cfif Len(Trim(DBTblName)) and Len(Trim(DBName))>
and d.statement = '[#DBName#].[#DBUserName#].[#DBTblName#]'
</cfif>
order by s.avg_user_impact desc
</cfquery>
<cfreturn getPotentiallyUsefulIndexes>
</cffunction>
Enjoy.
This looks very interesting, but what does "unique compiles" and "avg total user cost" mean? Is a larger number a good or a bad thing? If you could point me to some explanations I'd be very grateful. Thanks.