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

SP tuning Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 2:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 10:54 AM
Points: 132, Visits: 817
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
Post #1551041
Posted Friday, March 14, 2014 2:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1551049
Posted Friday, March 14, 2014 2:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 40,160, Visits: 36,547
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 2008, MVP
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

Post #1551055
Posted Friday, March 14, 2014 3:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 13,864, Visits: 28,259
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
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 #1551065
Posted Friday, March 14, 2014 10:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 10:54 AM
Points: 132, Visits: 817
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.
Post #1551251
Posted Friday, March 14, 2014 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:37 AM
Points: 13,864, Visits: 28,259
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
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 #1551283
Posted Friday, March 14, 2014 5:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 10:54 AM
Points: 132, Visits: 817
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.

Post #1551415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse