August 8, 2006 at 7:39 am
I've been trying to run the Index Tuning wizard against a database with an profiler trace saved to a SQL Server trace table. There are a lot of queries against the database in the trace table, but when I set the option 'limit number of workload queries' higher than 400 or I don't set a limit (in advanced options), I get the message 'The workload does not contain any events or queries that can be tuned against current database.'
I've tried different types of standard templates in the Profiler, but that doesn't seem to work. (SQLProfilerStandard, SQLProfilerTSQL, SQLProfilerReplay, SQLProfilerDuration)
Can someone please tell me what I'm doing wrong?
August 8, 2006 at 7:51 am
Hmmm I remember this myself - to be honest the index tuning wizard is pretty well rubbish - if you've a copy of 2005 the tuning advisor is much better. Anyway I seem to remember it was something to do with having the database you got the trace from selected somewhere in the options. It was a pig to get to produce some results which were largely useless.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2006 at 11:57 pm
We don't have SQL server 2005 here so I can't use that.
I have put a filter on database id in the trace, but reading your answer I've decided to analyze the SQL statements in the trace myself.
August 21, 2006 at 9:46 am
you can always get an eval of 2005
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 24, 2006 at 3:21 am
How much use the Profiler's trace results are to the Index Tuning wizard also depends on the nature of what is in the results. If I remember correctly the Tuning Wizard can't evaluate stored procedure calls so if your application(s) are firing stored procs instead of ad-hoc SQL then you won't get any meaningful data in the trace.
As for SQL 2005, as someone else pointed out you can download an eval of the full product but also you can download the Management Studio Express for free which gives you some of the improved functionality it has over Enterprise Manager. I have started using the Management Studio to work on my SQL 2000 instances, I have seen that it will let me run the Performance Advisor but I haven't actually tried it yet. Might be worth a look if it can advise on 2000 servers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply