Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SP tuning


SP tuning

Author
Message
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
Hello,

I have a big SP,which i need to fine tune for good performance.

I don't want to change any code as such,but i need to check if any indexes are missing or any redudant indexes are present or any kind of issue need to look over.

Could you please help me to do this by tool or anything ?

Attached stored proc and Indexes using in the table.Plea
free_mascot
free_mascot
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 2235
Attach execution plan too which will be easy to help you.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47187 Visits: 44356
Table definitions, index definitions (both as create statements please), execution plan please, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Which queries in the procedure are the slowest?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
I know you said you don't want to change the code, but it looks like there might be problematic bits in there. However, without an execution plan, I've got nothing for you. I don't know what the optimizer is doing on any of these things. I don't know the tables, the indexes, all the stuff that Gail says. Further, all those EXEC statements, what are they all doing? There's just no way to provide you with meaningful answers with so little information.

----------------------------------------------------
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
Hi Experts,

I have attached the Query plan ,indexes created on the table and part of the queries which i have a problem ,please help me.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17569 Visits: 32254
The plans are all pretty small and nothing stands out as horrifically scary. But, the large number of scans would be very concerning for me. Granted, everything seems to be returning a single row, but that's after filtering possibly the entire table. Why process teh @ExtractID over and over? Once should be enough, each of those is a scan and, cumulatively, 20% of the estimated cost. You also see that from Parameter name, again, estimated to be 16% of the total cost (although those at least are Seek operations).

I'm still unsure of what all this is doing, but the one place you could clearly index is the PDSProcessLog table. Something on ProcessName. Is ExtractID the clustered key? If not, add that as an INCLUDE column.

Most of the rest of the cost is associated with the inserts and I don't see any major issues there.

----------------------------------------------------
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Gangadhara MS
Gangadhara MS
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 840
But i can see lot of logical reads and read-ahead reads here for the PDSPrioritizedPriceListItem table.

How can we reduce this to minimum. Any index suggestion to specially for this table.


Table 'PDSPrioritizedPriceListItem'. Scan count 1, logical reads 1399767, physical reads 0, read-ahead reads 871343, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 83812 ms, elapsed time = 89501 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search