May 20, 2008 at 11:34 am
I have a stored procedure containing a single SELECT query. The query runs in 1 sec, while the stored procedure takes 2.5 min to finish execution!
The query is a complex multi-join query
Any ideas what could be happening?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 20, 2008 at 11:46 am
Post the proc definition please.
I am thinking parameter sniffing....
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
May 20, 2008 at 12:05 pm
Here you go: 🙂
CREATE PROC [dbo].[ssp_elect_NewImportSummary]
@ImportIDint
AS
SET NOCOUNT ON
SELECT
DISTINCT ( CI.ImportID )
,CI.NumRecordsInFile AS TotalNumberOfRecordsInADJFile
,CI.ImportFile
, CI.ImportDate
, CI.PayrollDate
,COALESCE ( StatusCount.TotalContractsInBatStatus, 0 ) AS TotalContractsInBatStatus
,COALESCE ( StatusCount.TotalContractsInVerStatus, 0 ) AS TotalContractsInVerStatus
,COALESCE ( StatusCount.TotalContractsInXxxStatus, 0 ) AS TotalContractsInXxxStatus
,COALESCE ( PDC.NoOfPreDataCancel, 0 ) AS NoOfPreDataCancel
,COALESCE ( CIS.NoOfContractsInCIS, 0 ) AS NoOfContractsInCIS
,COALESCE ( RejectedErrorMessage.ErrorMessage , '' )AS ErrorMessage
,COALESCE ( RejectedErrorMessage.ErrorCount , 0 )AS ErrorCount
,Total.TotalNumberOfRecordsImported
FROM
dbo.elect_ContractImport AS CI WITH ( NOLOCK )
INNER JOIN
dbo.elect_ContractImportDetail AS CID WITH ( NOLOCK )
ON CI.ImportID = CID.ImportID
LEFT JOIN
(
SELECT statCount.ImportID
,SUM( statCount.BatCount ) AS TotalContractsInBatStatus
,SUM( statCount.XxxCount ) AS TotalContractsInXxxStatus
,SUM( statCount.VerCount ) AS TotalContractsInVerStatus
FROM
(
SELECTDISTINCT Cust_ID AS ContractID
,ImportID
, CASE WHEN ErrCode = 'BAT' THEN 1 ELSE 0 END AS BatCount
,CASE WHEN ErrCode = 'XXX' THEN 1 ELSE 0 END AS XxxCount
,CASE WHEN ErrCode = 'VER' THEN 1 ELSE 0 END AS VerCount
FROM
dbo.elect_ContractImportDetail AS CI WITH ( NOLOCK )
WHERE ErrCode IN ( 'BAT', 'XXX', 'VER' ) AND Importid = @ImportID
) AS statCount
GROUP BY
statCount.ImportID
) AS StatusCount ON StatusCount.ImportID = CI.ImportID
LEFT JOIN
(
SELECTImportID
, COUNT ( PreDataActivitytype ) AS NoOfPreDataCancel
FROM dbo.elect_ContractImportDetail WITH ( NOLOCK )
WHERE PreDataActivitytype = 'Cancel' AND ImportID = @ImportID
GROUP BY
ImportID
) AS PDC ON PDC.ImportID = CI.ImportID
LEFT JOIN
(
SELECT COUNT ( DISTINCT ContractID ) AS NoOfContractsInCIS -- Distinct as there may be multiple cust_ids for same import.
FROM dbo.elect_Contracts AS eco WITH ( NOLOCK )
INNER JOIN
dbo.elect_ContractImportDetail AS c WITH ( NOLOCK ) ON c.Cust_ID = CAST ( eco.ContractID AS VARCHAR (10))
WHERE ImportID = @ImportID
) AS CIS ON @ImportID = CI.ImportID
LEFT JOIN
(
SELECT COUNT(ErrorMessage) AS ErrorCount
,ErrorMessage
, ImportID
FROM (
SELECT Cust_ID,
CASE WHEN Count(Cust_ID) > 1 THEN '*** Multiple Errors ***'
ELSE MAX(ErrorMessage)
END AS ErrorMessage
,ImportID
FROMdbo.elect_ContractImportDetailAS CID WITH ( NOLOCK )
INNER JOIN
dbo.Elect_ContractImportErrors ERR WITH ( NOLOCK )
ON((CID.Errval & ERR.ErrorNum) = ERR.ErrorNum AND ERR.ErrorType = 'D')
OR((CID.Errval_C & ERR.ErrorNum) = ERR.ErrorNum AND ERR.ErrorType = 'B')
WHERE ImportID = @ImportID
GROUP BY
ImportID
,Cust_ID
) AS ERR1
GROUP BY
ImportID
,ErrorMessage
) AS RejectedErrorMessage ON RejectedErrorMessage.ImportID = CID.ImportID AND CID.ImportId = RejectedErrorMessage.ImportID
LEFT JOIN
(
SELECTCOUNT(*) AS TotalNumberOfRecordsImported
FROM
dbo.elect_ContractImportDetail WITH ( NOLOCK )
WHERE
ImportID = @ImportID
) AS Total ON @ImportID = CID.ImportID
WHERECID.ContType = 'N'
AND CI.ImportID = @ImportID
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 20, 2008 at 12:10 pm
We changed the sproc like this at the top (everything else left the same) and the problem disappeared! So it does look like parameter sniffing!
Why is the performance difference so drastic in this case?
ALTER PROC [dbo].[ssp_elect_NewImportSummary_test]
@ImportIDtestint
AS
SET NOCOUNT ON
Declare @ImportID int
Set @ImportID=@ImportIDtest
...
...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 20, 2008 at 12:13 pm
That's a big proc... Hmmm...
Run it in the proc and as a query, and have a look at the execution plan of each, and the results of statistics IO.
Are they different?
Does the exec plan of the proc have inaccurate cardinality estimates (estimated rows and actual rows wildly different)?
If you recompile the proc (sp_recompile 'ssp_elect_NewImportSummary'), does the performance improve? Does the execution plan change?
If you look at sys.dm_exec_requests while that proc is running, do you see a wait type? If so, what?
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
May 20, 2008 at 12:29 pm
GilaMonster (5/20/2008)
That's a big proc... Hmmm...Run it in the proc and as a query, and have a look at the execution plan of each, and the results of statistics IO.
Are they different?
Does the exec plan of the proc have inaccurate cardinality estimates (estimated rows and actual rows wildly different)?
If you recompile the proc (sp_recompile 'ssp_elect_NewImportSummary'), does the performance improve? Does the execution plan change?
If you look at sys.dm_exec_requests while that proc is running, do you see a wait type? If so, what?
CXPACKET is the wait type, so parallelism is an issue, but that may be because the query is poorly optimized.
I ran "sp_recompile 'ssp_elect_NewImportSummary'", but the performance did not improve.
The Statistics IO results when running the sproc and when running the individual query are shown below (sproc first). The difference is huge:
Table 'Worktable'. Scan count 694799, logical reads 695095, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImportDetail'. Scan count 2063, logical reads 12662, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 294, logical reads 303, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImportErrors'. Scan count 588, logical reads 20580, physical reads 0, read-ahead reads 0.
Table 'elect_Contracts'. Scan count 1, logical reads 1124, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImport'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 293, logical reads 302, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImportDetail'. Scan count 6, logical reads 4542, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImportErrors'. Scan count 588, logical reads 20580, physical reads 0, read-ahead reads 0.
Table 'elect_Contracts'. Scan count 1, logical reads 1124, physical reads 0, read-ahead reads 0.
Table 'elect_ContractImport'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 20, 2008 at 12:48 pm
Can you post the execution plans please. You can right-click on the exec plan, save it as a .sqlplan file, zip and attach to the post. (assuming SQL 2005)
Somewhere I think the optimiser's making a very bad guess of row counts, and I'm not sure why...
Hmm, are the statistics on the table up to date?
update statistics elect_ContractImportDetail with full_scan
update statistics elect_ContractImportErrors with full_scan
Also, amybe try breaking some of those subqueries out and storing interim results in temp tables. The query is fairly complex. It's likely that the optimiser is bailing early with a 'good-enough' plan that isn't
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
May 20, 2008 at 1:22 pm
GilaMonster (5/20/2008)
Can you post the execution plans please. You can right-click on the exec plan, save it as a .sqlplan file, zip and attach to the post. (assuming SQL 2005)Somewhere I think the optimiser's making a very bad guess of row counts, and I'm not sure why...
Hmm, are the statistics on the table up to date?
update statistics elect_ContractImportDetail with full_scan
update statistics elect_ContractImportErrors with full_scan
Also, amybe try breaking some of those subqueries out and storing interim results in temp tables. The query is fairly complex. It's likely that the optimiser is bailing early with a 'good-enough' plan that isn't
Thank you Gail, you nailed it! It was the stats!
This is a TEST machine, and the stats had not been updated since May 8. As soon as I updated the stats, the proc executes on par with the query itself.
Thanks again for the help!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 21, 2008 at 12:23 am
Glad to hear it.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply