Unused Index, How to find them
-
I noticed a posting from may of 2004 requesting info on how to find an unused index in MS SQL. At the time the option was to run the trace util and work backwords. Has anyone seen anything since then. From MS or other third party.
Mark
-
You got me curious so I put the following script together. It generates SQL that will exec every Stored Procedure while reporting the execution plan (which doesn't ACTUALLY run the SP, only reports the plan). You can then take the generated output, copy and paste it into Query Analyzer, and run it. It will generate a lot of output and will error on occasion due to tables not existing (should normally only be #Temp tables). Once you scan for those errors you can add code to the top prior to the SET SHOWPLAN_TEXT ON line to create each required #Temp table with the correct columns (hopefully how don't share #Temp table names with different structures). You can then run the SQL again hopefully with no errors. When done, save the report to a text file. If you have a specific index you are curious about you can simply perform a FIND within the report to see if it is used, and if so where and how. If you want to know in general if any indexes are not used you could import the report into a report table, run a cursor against the sysindexes table, and scan for references to the index name within the report table.
Note that I just through this together and so it may not support all parameter types correctly, but should you get an error because of it you should be able to fix it fairly easily.
declare @Proc_Name nvarchar(128)
declare @Owner_Name nvarchar(128)
declare @strSQL nvarchar(8000)
declare @strCRLF varchar(5)
set @strCRLF = '
'
print 'SET SHOWPLAN_TEXT ON' + @strCRLF + 'GO' + @strCRLF
DECLARE curList CURSOR READ_ONLY FOR
SELECT DISTINCT ROUTINE_SCHEMA, ROUTINE_NAME
FROM information_schema.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND NOT ROUTINE_NAME like 'sp_%'
AND NOT ROUTINE_NAME like 'xp_%'
AND NOT ROUTINE_NAME like 'dt_%'
OPEN curList
FETCH NEXT FROM curList INTO @Owner_Name, @Proc_Name
while @@FETCH_STATUS = 0
begin
set @strSQL = 'exec ' + @Owner_Name + '.' + @Proc_Name + ' '
select @strSQL = @strSQL + Parameter_Name + ' = ' + case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL then ''''' ,' when DATETIME_PRECISION IS NOT NULL then ''''' ,' else '0 ,' end
from information_schema.Parameters where specific_name = @Proc_Name
if RIGHT( @strSQL, 1 ) = ',' set @strSQL = LEFT( @strSQL, LEN( @strSQL ) - 1 )
set @strSQL = @strSQL + @strCRLF + 'GO' + @strCRLF
print @strSQL
FETCH NEXT FROM curList INTO @Owner_Name, @Proc_Name
end
CLOSE curList
DEALLOCATE curList
print 'SET SHOWPLAN_TEXT OFF' + @strCRLF + 'GO' + @strCRLF
-
When using the showplan, it will not generate plans for queriess using temp tables, or remote execution procs, etc....
While your script is very cool, the only real option is actually to generate actual plans, with many different parameters for the procs, as single parameters may not actually cause it to use different paths with in the procs, over a time period which will accurrately describe the overall usages of the database. Include the object ID and the index id in the stored data gathered in the profile, and reference it back to the system tables to eliminate the ones actually used.
-
This archived webcast describes a methodology.
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032256511&Culture=en-US
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply