locked
Large Temp DB Growth RRS feed

  • Question

  • Currently running Service Manager 2012 R2 CU7 with Cireson integration.

    2 Management Servers, Databases running on SQL 2012 R2 Cluster. TempDB 8 x 50Gb files.

    We have notice over the past few months that Operations Manager notes low disk space on them volumes, when we check the TempDB files have grown full and subsequently cleared leaving just the TempDB and lots of white space.

    Not sure if this is of an expected size or not, can anyone shed any light or suggestions on this?

    We restarted SQL and found the following observations:

    Since the restart there’s been 3 queries running and they are the ones eating the tempdb.

    The sessions are running against :-

    Database :           Service_Manager_Live_2012

    Host  :                   ServiceManagerMS1

    Application :       SC DAL-MOM-SDK

    It’s actually 1 query being run 3 times, the query is below.

    They’ve just finished, they ran for just over an hour and tempdb is at 400GB.

    INSERT INTO @Path

    -- Composite_Select_Path_0e1313ab-dc5c-cf9d-d6b0-e2e9835a132a <S_InElem0,S_InElem1,S_InElem2,S_InElem3,S_InElem4,S_InElem5,S_InElem6,S_InElem7,S_InElem8>

    SELECT [PC].[S],0 AS CS,[PC].[T0],1 AS CT0,[PC].[T1],2 AS CT1,[PC].[T2],3 AS CT2,[PC].[T3],4 AS CT3,[PC].[T4],5 AS CT4,[PC].[T5],6 AS CT5,[PC].[T6],7 AS CT6,[PC].[T7],8 AS CT7,[PC].[T8],9 AS CT8,[PC].[T9],10 AS CT9

    FROM (SELECT [S].[BaseManagedEntityId] AS [S],0 AS CS,[R0].[SourceEntityId] AS [T0],1 AS CRT0,[R1].[TargetEntityId] AS [T1],2 AS CRT1,[R2].[TargetEntityId] AS [T2],3 AS CRT2,[R3].[TargetEntityId] AS [T3],4 AS CRT3,[R4].[SourceEntityId] AS [T4],5 AS CRT4,[R5].[SourceEntityId] AS [T5],6 AS CRT5,[R6].[SourceEntityId] AS [T6],7 AS CRT6,[R7].[SourceEntityId] AS [T7],8 AS CRT7,[R8].[SourceEntityId] AS [T8],9 AS CRT8,[R9].[SourceEntityId] AS [T9],10 AS CRT9

    FROM dbo.TypedManagedEntity AS S

     LEFT OUTER JOIN dbo.Relationship AS R0 /*TARGET_HINT_PATTERN*/-- System.UserManagesUser

         ON (R0.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R0.[RelationshipTypeId] = '4a807c65-6a1f-15b2-bdf3-e967e58c254a'

         AND R0.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R1 /*SOURCE_HINT_PATTERN*/-- System.UserHasPreference

         ON (R1.[SourceEntityId] = S.[BaseManagedEntityId]

         AND R1.[RelationshipTypeId] = '649e37ab-bf89-8617-94f6-d4d041a05171'

         AND R1.[IsDeleted] = 0

         AND EXISTS ( -- Restricted to System.Notification.Endpoint

            SELECT 1 FROM dbo.[TypedManagedEntity]

            WHERE R1.[TargetEntityId] = [BaseManagedEntityId]

            AND [ManagedTypeId] = 'c08e20e3-c0fe-66b8-8c12-bcc1f3f11d5d' AND [IsDeleted] = 0))

    LEFT OUTER JOIN dbo.Relationship AS R2 /*SOURCE_HINT_PATTERN*/-- System.UserHasPreference

         ON (R2.[SourceEntityId] = S.[BaseManagedEntityId]

         AND R2.[RelationshipTypeId] = '649e37ab-bf89-8617-94f6-d4d041a05171'

         AND R2.[IsDeleted] = 0

         AND EXISTS ( -- Restricted to System.UserPreference.Localization

            SELECT 1 FROM dbo.[TypedManagedEntity]

            WHERE R2.[TargetEntityId] = [BaseManagedEntityId]

            AND [ManagedTypeId] = 'efa8bbd3-3fa4-2f37-d0d5-7a6bf53be7c8' AND [IsDeleted] = 0))

    LEFT OUTER JOIN dbo.Relationship AS R3 /*SOURCE_HINT_PATTERN*/-- System.UserManagesUser

         ON (R3.[SourceEntityId] = S.[BaseManagedEntityId]

         AND R3.[RelationshipTypeId] = '4a807c65-6a1f-15b2-bdf3-e967e58c254a'

         AND R3.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R4 /*TARGET_HINT_PATTERN*/-- System.WorkItemCreatedByUser

         ON (R4.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R4.[RelationshipTypeId] = 'df738111-c7a2-b450-5872-c5f3b927481a'

         AND R4.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R5 /*TARGET_HINT_PATTERN*/-- System.WorkItemAffectedUser

         ON (R5.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R5.[RelationshipTypeId] = 'dff9be66-38b0-b6d6-6144-a412a3ebd4ce'

         AND R5.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R6 /*TARGET_HINT_PATTERN*/-- System.WorkItemAssignedToUser

         ON (R6.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R6.[RelationshipTypeId] = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722'

         AND R6.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R7 /*TARGET_HINT_PATTERN*/-- System.WorkItem.TroubleTicketClosedByUser

         ON (R7.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R7.[RelationshipTypeId] = '76bc6c3b-a77b-2468-0a63-169d23dfcdf0'

         AND R7.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R8 /*TARGET_HINT_PATTERN*/-- System.ConfigItemOwnedByUser

         ON (R8.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R8.[RelationshipTypeId] = 'cbb45424-b0a2-72f0-d535-541941cdf8e1'

         AND R8.[IsDeleted] = 0)

    LEFT OUTER JOIN dbo.Relationship AS R9 /*TARGET_HINT_PATTERN*/-- System.ComputerPrimaryUser

         ON (R9.[TargetEntityId] = S.[BaseManagedEntityId]

         AND R9.[RelationshipTypeId] = 'aaf7adeb-920c-3d3f-2184-1de2a2cba5a0'

         AND R9.[IsDeleted] = 0)

    WHERE (S.[ManagedTypeId] = 'eca3c52a-f273-5cdc-f165-3eb95a2b26cf'

         AND S.[IsDeleted] = 0)) AS PC

    WHERE [PC].[S] IN (@S_InElem0,@S_InElem1,@S_InElem2,@S_InElem3,@S_InElem4,@S_InElem5,@S_InElem6,@S_InElem7,@S_InElem8) OPTION (KEEP PLAN)


    Regards, Robert --------- You can view my blog at: http://scnuggets.blogspot.co.uk

    Wednesday, January 27, 2016 1:26 PM

Answers

  • From time to time, the SQL statistics and indexes become "corrupt" (for lack of a better term). This usually manifests when running type-projection queries..the SQL execution engine "forgets" to use the indexes, and so the queries run ridiculously slow and use up a huge amount of tempdb space.

    So, I recommend running the following maintenance queries on your database, but first, some caveats.

    Backup your ServiceManager database before you do this. Shutdown your management servers and portal while you run these queries. Preferably, after shutting down your management servers, restart your SQL instance (to make sure there are no rogue queries still running).

    These queries can take a long time to run (anywhere from 10 minutes to 2 hours on my 30 gig DB).

    I recommend running these queries at least once a week..but when the SQL database is behaving, you can run these queries while Service Manager is running (but again, you want to backup your DB before running these queries, just to be on the safe side).

    I always run the statistics update first, then the index update. Don't try to run both at the same time. These queries work on EVERY table in the ServiceManager database.

    It also wouldn't hurt to do the same maintenance to your data warehouse databases as well.

    USE ServiceManager
    GO
    sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS'
    
    USE ServiceManager
    GO
    exec sp_MSforeachtable  'SET QUOTED_IDENTIFIER ON;
    ALTER INDEX ALL ON ? REBUILD'



    Thursday, January 28, 2016 3:42 PM

All replies

  • From time to time, the SQL statistics and indexes become "corrupt" (for lack of a better term). This usually manifests when running type-projection queries..the SQL execution engine "forgets" to use the indexes, and so the queries run ridiculously slow and use up a huge amount of tempdb space.

    So, I recommend running the following maintenance queries on your database, but first, some caveats.

    Backup your ServiceManager database before you do this. Shutdown your management servers and portal while you run these queries. Preferably, after shutting down your management servers, restart your SQL instance (to make sure there are no rogue queries still running).

    These queries can take a long time to run (anywhere from 10 minutes to 2 hours on my 30 gig DB).

    I recommend running these queries at least once a week..but when the SQL database is behaving, you can run these queries while Service Manager is running (but again, you want to backup your DB before running these queries, just to be on the safe side).

    I always run the statistics update first, then the index update. Don't try to run both at the same time. These queries work on EVERY table in the ServiceManager database.

    It also wouldn't hurt to do the same maintenance to your data warehouse databases as well.

    USE ServiceManager
    GO
    sp_msforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS'
    
    USE ServiceManager
    GO
    exec sp_MSforeachtable  'SET QUOTED_IDENTIFIER ON;
    ALTER INDEX ALL ON ? REBUILD'



    Thursday, January 28, 2016 3:42 PM
  • Cheers for the reply! Looks like I need to book in some downtime :D

    Regards, Robert --------- You can view my blog at: http://scnuggets.blogspot.co.uk

    Monday, February 1, 2016 1:05 PM
  • Hi Robert,

    did you manage to try the proposed solution ?

    I have the exact same problem in a quite similar environment (Service Manager 2012 R2 CU7 with Cireson integration, 3 Management Servers, TempDB 8 x 15Gb files.) and have been struggling with that for about 3 months.

    Aaron's solution looks really to be the good one.

    Thank you for your answer if you get back around here.

    Thursday, September 29, 2016 10:20 AM