June 17, 2009 at 5:09 am
HI
This is regarding the Trace & DTA.
Question:
1)
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.
2)
In DTA, there is two options to select
1)file 2)table
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
QUERY,
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.
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
seekpredicate:[sateesh].[dbo].[tblmargin].opf_no=[@opfNO]
Weather i have to create the non clustered index on this particular opfno are..........
Regards
sat
June 17, 2009 at 7:54 am
SAT_SQL (6/17/2009)
HIThis is regarding the Trace & DTA.
Question:
1)
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
2)
In DTA, there is two options to select
1)file 2)table
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
QUERY,
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
seekpredicate:[sateesh].[dbo].[tblmargin].opf_no=[@opfNO]
Weather i have to create the non clustered index on this particular opfno are..........
Regards
sat
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2009 at 12:55 am
Hi
here is one more ececution plan
regards
sate
June 18, 2009 at 6:06 am
OK? What's the problem and/or question?
You've got a cursor. It looks like it's doing updates.
It's hard to comment in any other way without knowing what you're trying to do and what the problem is.
Just so you know, more often than not, using a cursor is not the best way to solve most TSQL problems. I'm not sure what's going on here, but based on the little I can see, I suspect you don't need the cursor.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply