March 14, 2014 at 2:04 am
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
March 14, 2014 at 2:37 am
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."
March 14, 2014 at 2:48 am
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
March 14, 2014 at 3:29 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2014 at 10:47 am
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.
March 14, 2014 at 12:25 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 14, 2014 at 5:28 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply