SQL Server Performance Forum – Threads Archive

Help with Tuning Wizard

Being new to SQL Server, I’m sure I’m missing something very obvious, but after quite a bit of time trying to figure this out, I realize I need some help. I ran a trace for 4-5 hours on a server, and ended up with around 160k rows, with plenty of activity. However, when I run this thru the tuning wizard, it tells me the workload does not contain any events or queries that can be tuned against the database. I am running it against the correct database, and there are loads of SQL:BatchCompleted and RPC:Completed events. Columns are eventclass, textdata, duration, binarydata, and spid. What am I missing here? Thanks very much for your assistance…

Tom: Everthing looks write. 1) Open the trace file.
2) Find a Sql completed.
3) Cut and Paste into SQL Analyzer.
4) Execute the query.
5) If the results is the same, look if SQL have a create table #xxxx, means temporary table witch is one of the cause because SQL Index Tuning donÂ´t undertand.
6) If SQL run OK. Try then with Index Tuning from Query Analyzer. BTW SQL version and SP? Luis Martin

Luis, Thanks very much for your reply. Sorry, I forgot to mention the version, it’s 2000 SP3. I did try your suggestion before posting, and the query ran fine in Analyzer, and also in the tuning wizard. I tried again just now, with the same results. Kind of a mystery, eh? Thanks again for your assistance. Tom Tucker TomT

Tom: Is really a mystery, so we try to learn toghether. Try the following: Run a trace only with SQL. In the same trace you have, filter only SQL and not RPC.
Save the file and create another file with RPC only.
Run Index Tuning for each file.
BTW if you are agree, send me via e-mail RAR or ZIP with trace and IÂ´ll see what is going on.
The idea is to aislate the problem using: what if? Luis Martin

Luis, I tried your suggestion of separating the file into
sql and rpc. Neither of the files made any
difference. I’d be glad to email the two files if you like (or
would you prefer the original?). Let me know, and also where to send them. Thanks very much for your kind assistance. Tom
TomT

Tom: I was looking you trace, and I made a table like one of the SQL trace: SELECT "TeamID" ,"TeamName" ,"Active" FROM "dbo"."Teams" ORDER BY "dbo"."Teams"."Active" DESC,"dbo"."Teams"."TeamName" I filter the trace file by SPID=78 and duration >= 15 to get a small trace (69 rows). I run Index tuning and there (obviusly) is nothing to do, but no error.
Same with SQL Analizer. I first I suspect ", because in my traces I have no " at all, but run Ok in my test. After that I run all trace, and no errors at all So, I’ll wait for anothers ideas from Forum. Luis Martin

TomT, let’s talk about basics first. What makes you think that Index tuning wizard should give you some recommendations? Do you face any performance issues? How many databases are involved in the trace? Tuning wizard has the limitation of being able to look at only one database. At this point I would like to mention that Tuning Wizard is evolving and is not a perfect tool to tune. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Gaurav, I’m only working with one database. I was just interested in seeing what the tuning wizard suggested about my indices. I am not seeing performance problems, but wanted to see if, based on user activity throughout the day, the trace along with the tuner might suggest some improvements. I was very surprised it could not find anything to work with, since there are lots of sql and rpc events. thanks for your assistance, Tom TomT

Probably you have the perfectly designed database…. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Wouldn’t that be nice. The problem however is that the tuning wizard claims there are no valid events to work with, which as far as I can see, is not true…. TomT

Can you make sure the following events are included in the profiler
1)SQL:BatchCompleted 2) RPC:Completed. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Satya, Thanks for your reply, yes, both of these events were included (they were the only events captured). Tom TomT

Then try capturing other events also that should add-up the trace file to recommend better indexes.
I have seen this issue earlier and simply arranged time to refresh the services, thought its not required but it rectified so. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

I think we are missing something here. Tom sed: "it tells me the workload does not contain any events or queries that can be tuned against the database" There is not a case when Index Tuning has nothing to do. This case mean and error, like profiler is working in other database, different were trace was captured. If there is no improvement the is no message at all. I’m rigth? Luis Martin

Wouldn’t that be nice. The problem however is that the tuning wizard claims there are no valid events to work with
I think this problem is a chain event and realted to profiler/ITW and lets wait for Tom.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Satya, I’m not sure what you mean by "chain event"… TomT

PROFILER trace events —-> ITW. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>