October 27, 2011 at 1:35 am
Hi all, I have a query that was taking between 15 and 40 seconds to run. So I took a look at what I could do to improve performance. I rewrote some of the query to remove any default parameters, and then tried using a CTE (as the proc uses quite a number of tables in a select statement), along with a temp table. I then created an index on the biggest table, and my query was now running in well under a second - Great I thought. But then I thought let's take a look at it again to make sure that the changes are all needed. I restored the database again, added the index and copied my two new versions of the query onto the server (One with a CTE, the other with a temp table). I then ran the two new versions and sure enough - Under a second, the original SP took less than a second now as well. So it looked like the index was what was needed, so to prove that I dropped the index and ran the queries again - Again they ran in under a second. So I thought it must be a statistics problem - But the stats on the fields I'm using still appear out of date - One for the DBA to look at :). So does anyone have any idea why adding and then dropping an index would give me massive performance gains? I realise that the query plan is cached, but surely dropping the index would force the query plan to be recalculated?
Help !!!
October 27, 2011 at 2:01 am
Can you post the query plans from before and after creating/dropping the index?
This does sound like statistics...
Also try updating stats on the table before creating the index, do you get the same plan?
October 27, 2011 at 2:02 am
First of all, a white paper on plan caching.
What you may have experienced is a sub-optimal execution plan being cached. The first time a query is executed, the execution plan is generated, based on the parameters specified. If one of these are very atypical, you may get the situation you're describing. The first execution may for instance use @Country='Iceland', and since you have very few customers in Iceland SQL Server decideds that nested loop inner joins are the more efficient, and use that in the execution plan, instead of a merge or hash join which would be more efficient 99.9 percent of the time.
October 27, 2011 at 2:04 am
can you post details of the query, table and the indexes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 27, 2011 at 2:26 am
Here's the main guts of the query (modified for security)
WITH C_CTE (IssueID
,StartNo
,EndNo
,No
,LCSID
,GID
,TID
,IssueDate
,AID
,SID
,SSID
,PID
)
AS
(
SELECT LCS.IssueID
,LCS.StartNo
,LCS.EndNo
,LCS.No
,LCS.LCSID
,GAD.GID
,GAD.TID
,CI.IssueDate
,GAD.AID
,GAD.SID
,GAD.SSID
,OD.PID
FROM LCS
INNER JOIN dbo.udf_CI() AS CI
ON CI.IssueID = lcs.IssueID
INNER JOIN OD
ON OD.OID = CI.OID
INNER JOIN GAD
ON GAD.GID = OD.GID
AND GAD.AID = CI.AID
WHERE LCS.SID IN (@iIssuedStatus, @iPendingRevokeStatus)
AND LCS.OID = @pOID
AND LCS.EndDate IS NULL
AND OD.GID = ISNULL(@pGSID, OD.GID)
AND GAD.TID = ISNULL(@pTID, GAD.TID)
AND GAD.SID IN (SELECT Number FROM dbo.udf_CommaSeperatedListToTable(@pSIDs))
AND (GAD.SSID IN (SELECT Number FROM dbo.udf_CommaSeperatedListToTable(@pSSIDs)) OR @pSSIDs IS NULL)
AND (GAD.EndDate > @pEffectiveDate OR GAD.EndDate IS NULL)
AND((CI.IssueDate BETWEEN @pIssueStartDate AND @pIssueEndDate) OR @pIssueStartDate IS NULL)
)
SELECT CTE.IssueID
,CTE.StartNo
,CTE.EndNo
,CTE.No
,CTE.LCSID
,GS.GName
,TV.TName
,CONVERT(VARCHAR(14),CTE.IssueDate,103) AS IssueDate
,CTE.IssueDate
,CTE.AID
,SD.SName
,SS.SSName
,CASE (SD.SID)
WHEN 2 THEN (CONVERT(VARCHAR(23),OP.OPS,103)+' - '+CONVERT(VARCHAR(23),OP.OPE,103))
ELSE SUBSTRING(CONVERT(VARCHAR(23),OP.OPS,106),3,LEN(CONVERT(VARCHAR(23),OP.OPS,106))-2)
END AS OutputPeriod
FROM C_CTE AS CTE
INNER JOIN GS
ON CTE.GID = GS.GID
LEFT OUTER JOIN TV
ON CTE.TID = TV.TID
INNER JOIN SD
ON CTE.SID = SD.SID
AND SD.StartDate <= @pEffectiveDate
AND (SD.EndDate > @pEffectiveDate OR SD.EndDate IS NULL)
LEFT OUTER JOIN SS
ON CTE.SSID = SS.SSID
INNER JOIN OP
ON CTE.PID = OP.OPID
AND OP.StartDate <= @pEffectiveDate
AND (OP.EndDate > @pEffectiveDate OR OP.EndDate IS NULL)
WHERE (dbo.udf_DateOnly(CTE.IssueDate) >= @pIssueStartDate OR @pIssueStartDate IS NULL)
AND(dbo.udf_DateOnly(CTE.IssueDate) <= @pIssueEndDate OR @pIssueEndDate IS NULL)
AND((@pGSNLike = 0 AND lower(ltrim(rtrim(GS.GName))) = lower(ltrim(rtrim(@pGSName))))
OR (@pGSNLike = 1 AND lower(ltrim(rtrim(GS.GName))) LIKE '%'+lower(ltrim(rtrim(@pGSName)))+'%')
OR @pGSName IS NULL)
AND(OP.OPID = @pOPID OR @pOPID IS NULL)
The only paramters that have a non NULL value passed are @pOID, @pSID and @pGSNLike
The biggest table is LCS with about 1.4 million records (This is the table that I put the covering index on)
Hope this helps and that changing names hasn't made it too difficult to read
October 27, 2011 at 4:03 am
Also if I recompile the SP again - It goes back on a go slow
October 27, 2011 at 4:18 am
So if I run the query without doing anything = SLOW
create and drop index = FAST
Recompile = SLOW
So what does creating and dropping the index do - Even without running the query whilst the index exists ?
October 27, 2011 at 4:30 am
If I leave the index - it obviously uses this index and it's quick
If I then drop the index it uses a slightly different index with an index seek again and it's still quick
If I recompile it uses a different index and it's really slow - Thare are other changes to index usage on other table as well - Even though I haven't gone near them.
Am I going mad ?
October 27, 2011 at 4:46 am
Parameter sniffing? Can you post the query and the plans?
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
October 27, 2011 at 5:39 am
The new Index is called idx_LiveCertificateSummary_OwnerID_EndDate_StatusID
October 27, 2011 at 7:17 am
Is it possible that the issue lies with poor statistics on the OutputData table? I notice that there is significant difference between the Estimated and Actual rowcounts for that table in the query plan. In the original "Slow" query, this appears to be enough to cause the optimizer to perform a Hash Match with the inputs in the wrong order (the input with the most rows is being used for the Build, the one with the least is being used for the Probe).
I'm a bit fuzzy on all the conditions that can cause a cached plan to become invalidated, but I'm wondering if the scenario is something like this:
* Original query: poor statistics cause a suboptimal plan, with poor performance as the result
* Adding the index: the index allows the optimizer to reorganize the plan enough to minimize the impact of the unexpected large number of rows from the OutputData table (but if you look, it's still estimating the rowcount incorrectly for that table).
* Dropping the index: the plan is cached, SQL Server attempts to use it, but an index is missing...however, there is another index present that is almost as good, so it uses that one but doesn't bother to redo the whole query plan.
* Recompiling the sproc causes the optimizer to recreate the query plan from scratch, and it once again gets thrown off by bad statistics on OutputData
October 27, 2011 at 7:29 am
That would make a lot of sense - I'm going to get the stats updated and see what the effect is
Cheers
October 27, 2011 at 8:34 am
Ok, after scanning the whitepaper that okbangas linked earlier, at least part of my theory appears to be wrong; dropping the index should have caused a full recompile of the query plan (and it appears to have done so, since the pre- and post-drop plans are noticeably different). So, I really don't know why dropping the index results in a plan different from before. However, I still suspect that a significant issue with the first query has something to do with statistics on the _dta_index_OutputData... index; it could be old statistics, it could be an uneven distribution in the statistics...not sure.
October 31, 2011 at 8:10 am
The problem turned out to be one of statistics. Some of the stats were quite a way out of date. Updating the stats didn't make any difference until I included with FULLSCAN. It looks like creating the index forced a recompile (with the optimal or close to optimal plan). Dropping this index forced it to look again at the indexes and it ascertained that there was a similar index that it could substitute. A recompile forced it to look from scratch with the result being a vastly inferior query plan.
Thanks for your help.
Andy
October 31, 2011 at 8:12 am
Excellent! Glad you've got it resolved.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply