patternsqlModerate
SQL Server Frozen Ghost Cleanup workaround needed
Viewed 0 times
neededsqlcleanupfrozenghostserverworkaround
Problem
I have several tables with amount of rows between 5M and 1.5G
Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON
Tables are stored in different filegroups with 3-4 files each on different disk @ different LUNs @ very fast SAN
Every day these tables grow for 5-100 Gb in size and with 600k - 1.5M rows
After certain amount of time, which varies from 2 weeks to 6 months some of the rows are deleted or moved to archive DB, so - there is no any rows in worktables that older than 6 months.
Current configuration of server:
-T 3640; (Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting
of SET NOCOUNT ON, but when set as a trace flag, every client session
is handled this way)
-T 1118;(Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent.)
-T 2301;(Enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of
large data sets)
-T 1117;(Grows all data files at once, else it goes in turns.)
-E; (Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse
applications that have a limited number of users running index or data
scans)
-T 834; (Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool,
http://msdn2.microsoft.com/en-us/library/aa366720.aspx,
http://support.microsoft.com/kb/920093)
The problem is - that starting from some point of server's uptime (from couple of days to months)
Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON
Tables are stored in different filegroups with 3-4 files each on different disk @ different LUNs @ very fast SAN
Every day these tables grow for 5-100 Gb in size and with 600k - 1.5M rows
After certain amount of time, which varies from 2 weeks to 6 months some of the rows are deleted or moved to archive DB, so - there is no any rows in worktables that older than 6 months.
Current configuration of server:
- SQL server engine is 2008 R2 SP1 Enterprise @ 24 cores, @ 64Gb RAM
- SQL Server runs with extra startup flags:
-T 3640; (Eliminates sending DONE_IN_PROC messages to client for each statement in stored procedure. This is similar to the session setting
of SET NOCOUNT ON, but when set as a trace flag, every client session
is handled this way)
-T 1118;(Switches allocations in tempDB from 1pg at a time (for first 8 pages) to one extent.)
-T 2301;(Enables advanced optimizations that are specific to decision support queries. This option applies to decision support processing of
large data sets)
-T 1117;(Grows all data files at once, else it goes in turns.)
-E; (Increases the number of extents that are allocated for each file in a filegroup. This option may be helpful for data warehouse
applications that have a limited number of users running index or data
scans)
-T 834; (Causes SQL Server to use Windows large-page allocations for the memory that is allocated for the buffer pool,
http://msdn2.microsoft.com/en-us/library/aa366720.aspx,
http://support.microsoft.com/kb/920093)
- SQL Server uses Large Page Extensions
- SQL Server utilizes fast file initialization option
- AUTOSHRINK is OFF for all the databases
The problem is - that starting from some point of server's uptime (from couple of days to months)
GHOST CLEANUPSolution
Finally, MS has recognized the issue as a bug: http://support.microsoft.com/kb/2622823
Briefly: It is fixed in
In Sql Server 2012 SP1 I'm not experiencing the issue for more than year of runtime.
Briefly: It is fixed in
- Sql Server 2008 SP3 CU4
- Sql Server 2008 R2 CU10
- Sql Server 2008 R2 SP1 CU4
In Sql Server 2012 SP1 I'm not experiencing the issue for more than year of runtime.
Context
StackExchange Database Administrators Q#10200, answer score: 12
Revisions (0)
No revisions yet.