This is regarding the Trace & DTA.
I am working in sql-2005,If i want to no that weather the Trace is running for what i required , i.e fro EX:if i want to get the trace details for only this procedure so that i can optimize and ican creat the index i can apply
EXEC PrRunMarginReport '4/1/2008', '3/31/2009',
what should i do.
You can capture the trace data for the system and then search specifically for that procedure name in the textdata column of the trace information
In DTA, there is two options to select
But when ever i went to the query editor and then i open through right click on DTA, then it is displaying the third option called
then i run the above procedure then it shows the recemondation to creat the non-clustered indexs as per the definetions , i taken the scripts.
3) If i apply the recomended index on my instance the effect was showing on my system local server only(s1), if i wantto run the same thing in (s2) it is not optimized and it is showing the same time what it was at actually, it is not showing the optimized time ,
in this if i included the recomended indexes from the DTA, weather it will not included the whole query are not.
I'm not sure I understand what you're asking here. But it sounds like, can you run the index creation script on more than one server? I'm not sure I got that right. If so, yes. If not, please try to clarify.
Last but not least
4)when i run this sp
EXEC PrRunMarginReport '4/1/2008', '3/31/2009'
and i execute the execution plan , then i check there the clustered index delete take cost is 54%,46% for the clusterindex insert, then in clusertindex it is showing
Weather i have to create the non clustered index on this particular opfno are..........
When you're modifying data on a table that has a clustered index, you're going to be modifying the clustered index. Even if you provide a nonclustered index for a particular search, you'll still see the clustered index affected by insert/update/delete operations.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore RooseveltThe Scary DBA
Author of: SQL Server Query Performance Tuning
and SQL Server Execution Plans
Product Evangelist for Red Gate Software