Index tuning wizard | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Index tuning wizard

HELP
we have sql 2000, just trying to use the wizard to optimize the indexes of our db. The wizard keeps giving us the error message ‘The workload does not contain any events or queries….’ Any suggestions ?

Try capturing more events like SQL transaction, Object created/dropped… try looking at SP_TRACE_SETEVENT from books online and submit to Index tuning wizard for a recommendation. HTH Satya SKJ

I have already tried logging all events – created a huge trace file – but with the same results from the itw. Any other suggestions ? – thanks
See any informational message in SQL error log/ Satya SKJ

Refer to this MSDN link too http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sqlindex.asp] about ITW. Satya SKJ

When I collect a profiler trace for using the Index Wizard, I often just use the SQLProfilerStandard template, and have had no problems. But you are saying that you have collected all events and still get the error? When you look at the trace do you see events? If so, then we know your trace is OK, and my best guess is that you are having some problem with the Index Wizard, perhaps not pointing to the correct file, or something else simple like this. Let us know what you find. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I do the exact same steps on another machine with the same db ( this time the machine is running in a dev envirnoment) and ITW returns some results. COuld it be to do with security ?
I have always done this when I am the SA and a local admin of the server in question. Is your security different between the two systems? ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Ok we have it working now – but not sure why. Looks like the only thing we did was delete one table from the database. the table had three fields in it varchar(1000), and was to a total of 3600 bytes. Should this have been the cause of the problem ? We rebooted a few times as well.
I can’t imagine this causing the problem, but rebooting might have helped. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
I just had this problem with two query files (a third was OK), and fixed it by running the problem files through the Query Analyzer.
Although invisible in Notepad, there were some spurious characters at the start of the file that prevented both Query Analyzer and, presumably, Index Tuning Wizard from recognising it as a valid query (I imagine these characters came from the clipboard).
Anyhow, the cleaned-up files worked perfectly. Happy tuning!
]]>