CODEWITHSUNDEEP
sql-server-2005
DEE
DEE

Reputation: 373

performance tuning with procedure having temp tables

i have many queries which needs to be tunned and i was relaying on DTA(datbase engine tuning adviser which comes with sql 2005) to give me some recommendation on indexes. but looks like DTA fails to understand the queries which uses temp tables . is there any way we could get the index recommendation.

Thanks for your suggestions

Regards DEE

Upvotes: 2

Views: 1360

Answers (1)

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66702

To do this sort of thing you will have to pull the stored procedures apart and set up some 'fake' scenarios based on the temp tables built by the sprocs (i.e. make a scratch database and reconstruct the tables as permanent tables). Set up the scenario and try tuning advisor on it.

In practice, you're pretty much stuck doing this sort of thing if you want to tune sprocs that use temporary tables in any particularly complex way.

Upvotes: 2

Related Questions