A severe error occurred on the current command

  • 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!

  • Is it anything to do with this connect entry?

    Can you post the full query which causes the error?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • 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

  • Hi,

    maybe it has to do with Filtered index limitations?

    https://connect.microsoft.com/SQLServer/feedback/details/341891

  • 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

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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?

  • 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 <> 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?

    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.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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

  • 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.

  • Microsoft has a ton of relevant support articles:

    http://support.microsoft.com/search/default.aspx?query=a+severe+error+occurred&catalog=LCID%3D2057&mode=r

    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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • ChrisM@home (10/9/2012)


    Microsoft has a ton of relevant support articles:

    http://support.microsoft.com/search/default.aspx?query=a+severe+error+occurred&catalog=LCID%3D2057&mode=r

    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.

  • Grant Morton (10/9/2012)


    ChrisM@home (10/9/2012)


    Microsoft has a ton of relevant support articles:

    http://support.microsoft.com/search/default.aspx?query=a+severe+error+occurred&catalog=LCID%3D2057&mode=r

    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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/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