performance tuning with procedure having temp tables

Question

Hi There,

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

Answer 1

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.