| Author |
Topic |
|
John_Idol
Starting Member
39 Posts |
 Posted - 2008-01-08 : 09:06:09
|
| Hi All,I understand I can use Index tuning Wizard to index a DB. I understand as well I have to feed it with a workload file, which is a collection of queries. I do have a limited number of stored procedure (150-200 in total) being constanlty executed against this DB (around 700MB). Can I generate a workload file with these storedprocedures optimizing so the DB for their execution?If yes, how? can someone point out some good tutorial about this?Any help would be much appreciated,JI |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2008-01-08 : 11:13:26
|
| Hi TG,thanks for the link. I was able to produce a trace from the SQL Profiler, but When i run the Index Tuning Wizard with that trace I get the "the workload doesn not contain any events or queries that can be tuned against current database"; I found out this is due to stored procedures in the trace. As my app is firing ONLY stored procedures this makes me no happy.Any help guys?Cheers,JI |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-08 : 12:38:37
|
>>this makes me no happy Hopefully someone with experience will pipe in here but until then...you may want to try adding an "event" to the properties of your trace: StoredProcedures | StmtCompleted. That will add a lot more to your trace results but some of them will be individual statements from within your SPs.Be One with the OptimizerTG |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2008-01-08 : 16:37:23
|
| Sounds good. No idea about how to do it, but I'll try first thing in the morning.Thanks! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-08 : 23:32:45
|
| You can look at execution plan of those sps, and check if have indexes on columns used in where clauses. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-01-09 : 04:38:35
|
| When you set up a trace through Profiler there is a drop down box called Templates - choose the one called Performance Tuning. This will supply the Events required by Index Tuning WizardJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2008-01-09 : 08:16:48
|
quote:
Originally posted by jackv
When you set up a trace through Profiler there is a drop down box called Templates - choose the one called Performance Tuning. This will supply the Events required by Index Tuning WizardJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com
Great Tip! I tried first with the plan for stored procedures, but it doesn't show the duration. Now it seems to be working. The Index Tuning Wizard tells me to expect an impprovement of 58%, shall I believe it?  |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2008-01-09 : 10:48:23
|
| I was able to run the profile with small files using the settings you suggested.So I decided to use a bigger workload to get some statistical variety. I used a 25MB file with the same settings as the smaller ones I used before (5MB) but when I use the big one Index it tells me there are no events (same error as first post...) etc. Probably there's some query the wizard doesn't like.I'll try to create a relatively small trace with the most significant queries/activities for the app. that seems reasonable.There's extensive use of temporary table in the code, and I've seen the Stored procedures are often recompiling. If I'd be able to get a performance increase from indexing and then gain some more optimizing the code it'd be ideal. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-01-09 : 10:56:44
|
| >>There's extensive use of temporary table in the code, and I've seen the Stored procedures are often recompilingminimizing re-compiles can be a big gain if you are in an environment with lots of quick, concurrent calls. There is a very good article (i think i found it on sql-server-performance.com) that details most of the causes and what the solutions are. It was some time ago so it may be a little outdated but still very good read.I'll look for it...EDIT:Haven't looked yet but I did see a post where I plagerized whatever points I had remembered from that article:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51491Be One with the OptimizerTG |
|
|
|
|
Indexing Newbie - SQLServer2000