Indexing Newbie

Question

Indexing Newbie

John_Idol

Ten Centuries

Points: 1011
More actions
January 8, 2008 at 6:46 am

#180242

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

Dunno about that... but I do know that throwing indexes at tables will absolutely not help code that is written incorrectly because the code will not be able to use the indexes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John_Idol Ten Centuries Points: 1011 More actions · Answer 2

I know about that.

I didn't write the code so before doing anything else I am trying to index the DB (it's not indexed at all now). I am doing so in a test environment, so it won't be no harm.

I have a system in place so I am trying to run a number of queries through it for generating the workload, but I don't have any idea about how many queries I should run to generate a relevant pool of samples.

Thanks,

JI

John_Idol Ten Centuries Points: 1011 More actions · Answer 3

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 error "the workload does 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 hint/suggestion/help?

thanks,

JI

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Try running profiler and capturing the SP:StmtCompleted event (under stored procedures). That will fire an event for each query in the stored proc. You're best off running profiler for a couple of hours against your production system. Otherwise the workload you generate won't be representative and you may get useless results from the tuning wizard

On Index Tuning Wizard. Be very, very careful with it. It's in no way guaranteed to find correct or optimal indexes. It's got a reputation for suggesting absolutely stupid indexes and going way, way overboard in what it suggests.

If you can, get an expert on performance tuning in for a few days to get you started. If you understand what you're doing, you can tune far better that the ITW can.

Also, if you know what to look for, you can identify bad code and fix that as well.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John_Idol Ten Centuries Points: 1011 More actions · Answer 5

GilaMonster (1/8/2008)
Try running profiler and capturing the SP:StmtCompleted event (under stored procedures)

Thanks for your answer and advices.

I am supposed to capture this event? Just let the profiler run and wait fot it to happen?

JI

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

The event captures queries completing within procedures.

Start profiler, connect to a server (I would suggest for a good workload, use the production server. If you use anything else, you won't get a representatve sample)

Select save to disk and increse the file size (around 200MB is probably OK. More if your server is very busy.) Set a stop time if you so choose.

Go to the events tab. Deselect all the selected events. Expand out stored procedures and check SP:StmtCompleted

Run profiler for the chosen period (maybe an hour)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John_Idol Ten Centuries Points: 1011 More actions · Answer 7

Many thanks for helping.

Fact is my production server is not busy enough. The app is an internal one which is used only "sometimes". As I know what the most used features are I am generating traffic myself: I know for sure I am going to get more traffic this way 🙂

I'll keep you posted

Cheers,

JI

John_Idol Ten Centuries Points: 1011 More actions · Answer 8

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.

I think there's something in some of the queries the wizard doesn't like.

JI