October 8, 2012 at 5:02 am
Hi Everyone,
I have a fairly simple query... it's sums a value from a single table and inner joins to a couple of other tables for filtering purposes. I created a filtered nonclustered index on the main table in order to improve the performance of a different query.
If I try to run the query I get "A severe error occurred on the current command..." I figured the only thing that changed was the addition of the index so I disabled it even though the query that's breaking doesn't make use of the index. With the index disabled the query runs. This didn't make much sense to me so I did a rebuild of the index and started tinkering with the query. Various changes cause the query run fine. If I remove any of the filters or joins then the query returns results (albeit incorrect results).
So I get the error message if the index is enabled and I leave all the joins in, but I can run a query if I disable the index or remove any of the filters. I figured this might be a space issue (log, tempdb or something). The DBAs here at the office are looking into it. They cleared the transaction log but that didn't make a difference.
I also ran DBCC CHECKDB WITH ALL_ERRORMSGS and that didn't return any errors.
Any suggestions as to what I can try in order to get this resolved?
Thanks!
October 8, 2012 at 5:40 am
Is it anything to do with this connect entry?
Can you post the full query which causes the error?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 8, 2012 at 6:44 am
Thanks for the response. I don't think it's related to the link you sent because I'm not using INTERSECT. Here is the query which generates the error:
SELECT Shift, SUM(Value) AS ShiftTotal
FROM Reporting.fctDeltaGammaShift dgs
INNER JOIN Reporting.dimLifeLicence LifeLic
ON dgs.LifeLicenceID = LifeLic.ID
INNER JOIN Reporting.dimCategory cat
ON dgs.CategoryID = cat.ID
WHERE Shift in (-1,1)
AND ValuationDateID = 20121003
AND cat.Category = 'ED'
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
GROUP BY Shift
If I disable the index or if I comment out either of the following lines:
AND cat.Category = 'ED'
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
then the query runs. Here's the code for the index:
CREATE NONCLUSTERED INDEX [idx_fctDeltaGammaShift_ValueAggregation]
ON [Reporting].[fctDeltaGammaShift]
([ValuationDateID]
,[BookID]
,[LifeLicenceID]
,[HiPortNameID]
,[CategoryID]
,[Shift])
INCLUDE (Value)
WHERE Value <> 0
As you can see - this index is very specifically designed to improve the performance of an unrelated query so it shouldn't form part of the execution plan. I can't see an execution plan however because if I try to view it I get the same error.
October 8, 2012 at 7:27 am
What happens if you force your query to use a particular index?
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
October 8, 2012 at 7:34 am
Hi,
maybe it has to do with Filtered index limitations?
https://connect.microsoft.com/SQLServer/feedback/details/341891
October 8, 2012 at 7:58 am
Grant Morton (10/8/2012)
Thanks for the response. I don't think it's related to the link you sent because I'm not using INTERSECT. Here is the query which generates the error:
SELECT Shift, SUM(Value) AS ShiftTotal
FROM Reporting.fctDeltaGammaShift dgs
INNER JOIN Reporting.dimLifeLicence LifeLic
ON dgs.LifeLicenceID = LifeLic.ID
INNER JOIN Reporting.dimCategory cat
ON dgs.CategoryID = cat.ID
WHERE Shift in (-1,1)
AND ValuationDateID = 20121003
AND cat.Category = 'ED'
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
GROUP BY Shift
If I disable the index or if I comment out either of the following lines:
AND cat.Category = 'ED'
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
then the query runs. Here's the code for the index:
CREATE NONCLUSTERED INDEX [idx_fctDeltaGammaShift_ValueAggregation]
ON [Reporting].[fctDeltaGammaShift]
([ValuationDateID]
,[BookID]
,[LifeLicenceID]
,[HiPortNameID]
,[CategoryID]
,[Shift])
INCLUDE (Value)
WHERE Value <> 0
As you can see - this index is very specifically designed to improve the performance of an unrelated query so it shouldn't form part of the execution plan. I can't see an execution plan however because if I try to view it I get the same error.
All of the columns of table fctDeltaGammaShift required by the query are present in the filtered index, and the query can safely ignore rows where value = 0.
Here's a stab in the dark; rearrange the query a little, add missing table aliases, and switch on the actual execution plan.
SELECT dgs.Shift, SUM(dgs.Value) AS ShiftTotal
FROM Reporting.fctDeltaGammaShift dgs
INNER JOIN Reporting.dimLifeLicence LifeLic
ON dgs.LifeLicenceID = LifeLic.ID
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
INNER JOIN Reporting.dimCategory cat
ON dgs.CategoryID = cat.ID
AND cat.Category = 'ED'
WHERE dgs.Shift in (-1,1)
AND dgs.ValuationDateID = 20121003
AND dgs.Value <> 0
GROUP BY dgs.Shift
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 9, 2012 at 12:01 am
Thanks for the responses everyone.
I tried the following code which ChrisM@Work suggested:
SELECT dgs.Shift, SUM(dgs.Value) AS ShiftTotal
FROM Reporting.fctDeltaGammaShift dgs
INNER JOIN Reporting.dimLifeLicence LifeLic
ON dgs.LifeLicenceID = LifeLic.ID
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
INNER JOIN Reporting.dimCategory cat
ON dgs.CategoryID = cat.ID
AND cat.Category = 'ED'
WHERE dgs.Shift in (-1,1)
AND dgs.ValuationDateID = 20121003
AND dgs.Value <> 0
GROUP BY dgs.Shift
This fixed the issue! The execution plan shows that the index is being used for this query as well, which is great. I didn't think about filtering out the 0 values so thanks for that. If I remove the
AND dgs.Value <> 0
filter then I get the "A severe error..." message back again. I'm happy I have a solution, but I don't really understand why it works. I don't understand how the absence of the filter can cause it to break. To my mind the impact should be that it either uses the filter or doesn't so there's obviously a gap in my understanding. Would you mind explaining what's happening here?
October 9, 2012 at 1:07 am
Grant Morton (10/9/2012)
Thanks for the responses everyone.I tried the following code which ChrisM@Work suggested:
SELECT dgs.Shift, SUM(dgs.Value) AS ShiftTotal
FROM Reporting.fctDeltaGammaShift dgs
INNER JOIN Reporting.dimLifeLicence LifeLic
ON dgs.LifeLicenceID = LifeLic.ID
AND LifeLic.LifeLicence IN ('LL','LA','CAL')
INNER JOIN Reporting.dimCategory cat
ON dgs.CategoryID = cat.ID
AND cat.Category = 'ED'
WHERE dgs.Shift in (-1,1)
AND dgs.ValuationDateID = 20121003
AND dgs.Value <> 0
GROUP BY dgs.Shift
This fixed the issue! The execution plan shows that the index is being used for this query as well, which is great. I didn't think about filtering out the 0 values so thanks for that. If I remove the
AND dgs.Value <> 0filter then I get the "A severe error..." message back again. I'm happy I have a solution, but I don't really understand why it works. I don't understand how the absence of the filter can cause it to break. To my mind the impact should be that it either uses the filter or doesn't so there's obviously a gap in my understanding. Would you mind explaining what's happening here?
Filtered indexes are explained in this Stairway article by David Durant[/url]. This query uses the filtered index because it carries all of the columns necessary to support the query and also because the WHERE clause is a lexical match to the filter in the index.
I've encountered this error message a couple of times in the past and (being an old git) I cannot remember exactly what the solution was, though indexing stumbles to mind. I'd recommend rebuilding the remaining indexes on the same table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 9, 2012 at 1:51 am
This keeps getting stranger... I've got a few tables with very similar filtered indexes and I've been going through our stored procedures adding the Value <> 0 filter to a number of similar queries. I get the "A severe error..." message at every similar query and that fix resolves the issue in every case so far... except for the following. This piece of code uses a derived table. It fails with that message even with the "AND bond.Value <> 0" filter added:
UPDATE Reporting.fctRiskLimitUtilisation
SET Exposure = (
SELECT ISNULL(SUM(bond.Value),0) AS Total
FROM Reporting.fctBondCpiPrll bond
INNER JOIN Reporting.dimParallelShift PS
ON PS.ID = bond.ParallelShiftID
AND PS.ParallelShift = 1
INNER JOIN Reporting.dimCategory Cat
ON Cat.ID = bond.CategoryID
AND Cat.Category = 'ED'
WHERE bond.ValuationDateID = 20121003
AND bond.Value <> 0
)
WHERE ValuationDateID = 20121003
AND RiskBookID = 1
AND RiskCategoryID = 10
AND LimitLevelID = 0
AND LimitDescription = 'Bond PV01 up'
In most of the other similar cases CTEs have been used as opposed to derived tables. So I rewrote the above as the following CTE and it works!
;WITH TotalCTE (Total)
AS
(
SELECT ISNULL(SUM(bond.Value),0) AS Total
FROM Reporting.fctBondCpiPrll bond
INNER JOIN Reporting.dimParallelShift PS
ON PS.ID = bond.ParallelShiftID
AND PS.ParallelShift = 1
INNER JOIN Reporting.dimCategory Cat
ON Cat.ID = bond.CategoryID
AND Cat.Category = 'ED'
WHERE bond.ValuationDateID = 20121003
AND bond.Value <> 0
)
UPDATE limUt
SET Exposure = cte.Total
FROM Reporting.fctRiskLimitUtilisation limUt, TotalCTE cte
WHERE ValuationDateID = 20121003
AND RiskBookID = 1
AND RiskCategoryID = 10
AND LimitLevelID = 0
AND LimitDescription = 'Bond PV01 up'
The execution plan shows the filtered index is being used. Any ideas why the derived table solution doesn't work but the equivalent CTE solution does? I get the feeling something is wrong on the server because none of this makes sense to me. I tried your suggestion of rebuilding all indexes on this table but it made no difference.
October 9, 2012 at 2:47 am
Microsoft has a ton of relevant support articles:
Of those, this looks promising:
http://support.microsoft.com/kb/948525
Scanning through this lot, here's what I'd suggest:
List the version / SP / CU level etc of the instance. Looks like CU <=5 fixes most of this lot.
Does the table Reporting.fctDeltaGammaShift have a clustered index?
Monitor memory and - if you can - cycle the server.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 9, 2012 at 4:11 am
ChrisM@home (10/9/2012)
Microsoft has a ton of relevant support articles:Of those, this looks promising:
http://support.microsoft.com/kb/948525
Scanning through this lot, here's what I'd suggest:
List the version / SP / CU level etc of the instance. Looks like CU <=5 fixes most of this lot.
Does the table Reporting.fctDeltaGammaShift have a clustered index?
Monitor memory and - if you can - cycle the server.
Thanks for these useful links. The one you said looked promising certainly was. I did a test by creating a clustered index on the table and that seems to resolve the issue. What's interesting to me is that according to the info on that page this was apparently fixed in cumulative updates on SQL Server 2005. We're running 2008 R2 (SP1).
Thanks again for helping me get to bottom of this.
October 9, 2012 at 4:16 am
Grant Morton (10/9/2012)
ChrisM@home (10/9/2012)
Microsoft has a ton of relevant support articles:Of those, this looks promising:
http://support.microsoft.com/kb/948525
Scanning through this lot, here's what I'd suggest:
List the version / SP / CU level etc of the instance. Looks like CU <=5 fixes most of this lot.
Does the table Reporting.fctDeltaGammaShift have a clustered index?
Monitor memory and - if you can - cycle the server.
Thanks for these useful links. The one you said looked promising certainly was. I did a test by creating a clustered index on the table and that seems to resolve the issue. What's interesting to me is that according to the info on that page this was apparently fixed in cumulative updates on SQL Server 2005. We're running 2008 R2 (SP1).
Thanks again for helping me get to bottom of this.
That's excellent news Grant - and many thanks for the feedback.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply