Indexing Newbie

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Indexing Newbie

Indexing Newbie

Rate Topic

Display Mode

Topic Options

Author

Message

John_Idol

Posted Tuesday, January 08, 2008 6:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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

Post #440029

Jeff Moden

Posted Tuesday, January 08, 2008 7:37 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 32,906, Visits: 26,792

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #440050

John_Idol

Posted Tuesday, January 08, 2008 7:58 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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

Post #440067

John_Idol

Posted Tuesday, January 08, 2008 9:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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

Post #440124

GilaMonster

Posted Tuesday, January 08, 2008 9:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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 2008, MVP
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

Post #440143

John_Idol

Posted Tuesday, January 08, 2008 10:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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

Post #440157

GilaMonster

Posted Tuesday, January 08, 2008 11:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 PM
Points: 37,722, Visits: 29,977

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 2008, MVP
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

Post #440429

John_Idol

Posted Wednesday, January 09, 2008 3:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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 Smile

I'll keep you posted

Cheers,

JI

Post #440500

John_Idol

Posted Wednesday, January 09, 2008 8:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 15, 2009 4:10 AM
Points: 29, Visits: 29

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

Post #440718

« Prev Topic | Next Topic »

Permissions