Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server profiler Expand / Collapse
Author
Message
Posted Wednesday, June 17, 2009 5:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 12:04 AM
Points: 91, Visits: 227
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


Post #736377
Posted Wednesday, June 17, 2009 7:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 14,005, Visits: 28,387
SAT_SQL (6/17/2009)
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.

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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #736568
Posted Thursday, June 18, 2009 12:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 12:04 AM
Points: 91, Visits: 227
Hi
here is one more ececution plan






regards
sate


  Post Attachments 
margin3.zip (7 views, 3.54 KB)
Post #737206
Posted Thursday, June 18, 2009 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 14,005, Visits: 28,387
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #737445
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse