April 24, 2012 at 10:09 am
Hey Guys,
I have searched google over a few times and have not been successful at remedying this problem. Please help!
I have a database, Train, which is used for training in a test environment. It is restored monthly from a backup of the production db.
I have a second database, CalcPrice, which is used for testing new policy calculations. It is restored weekly from the same production db.
I use the following code in a job to refresh the dbs.
****************************************************************************
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = 'CalcPrice_PlanData') ALTER DATABASE CalcPrice SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE CalcPrice_PlanData FROM DISK= N'X:\MSSQL\Restore\db_Restore.bak' WITH MOVE 'PlanData_Data' TO 'X:\MSSQL\Data\CalcPrice_PlanData.mdf', REPLACE, MOVE 'PlanData_1_Data' TO 'X:\MSSQL\Data\CalcPrice_PlanData_1.ndf', REPLACE, MOVE 'PlanData_2_Data' TO 'X:\MSSQL\Data\CalcPrice_PlanData_2.ndf', REPLACE, MOVE 'PlanData_Log' TO 'X:\MSSQL\Logs\CalcPrice_PlanData_3.ldf', REPLACE ;
ALTER DATABASE CalcPrice_PlanData SET MULTI_USER
GO
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
go
EXEC master.sys.xp_cmdshell "del X:\MSSQL\Restore\db_Restore1.bak"
go
sp_configure 'xp_cmdshell', 0
go
reconfigure
go
sp_configure 'show advanced options', 0
go
reconfigure
go
****************************************************************************
Both the production and training environments are SQL Server 2008 SP3 Enterprise x64 (10.00.5500) running on Windows 2008 R2 Standard x64.
Table and Index Row level compression have been enabled on both the production dbs and the test dbs with the following statements:
ALTER TABLE [dbo].table REBUILD WITH (DATA_COMPRESSION=ROW,MAXDOP=3);
ALTER INDEX [index] ON [dbo]. REBUILD WITH (DATA_COMPRESSION=ROW,MAXDOP=3);
I have a maintenance plan that runs every Saturday. It runs the following tasks:
Check Database Integrity
Rebuild Index
Update Statistics: Column Statistics Only
Clean Up History
Execute T-SQL Statement Task -> Sets all db's recovery mode to simple as this is a test server.
The problem is that Train somehow "loses" its compression while CalcPrice does not. When compressed, the db is roughly 50GB. When it “loses” its compression it is roughly 80GB. At first we thought it was randomly "losing" its compression and expanding to 80GB, but we couldn't figure out what would be causing that. Then we thought it might be losing its compression on the refreshes, but after monitoring it more closely, we determined that was not the case. Our current theory is that something in the maintenance plan is causing it, but why wouldn’t it affect both dbs equally? I’m pretty flustered with this problem and having to re-compress it every couple weeks. I need some new theories to test. I have not begun fine grain tracking of when it loses its compression, but that may be necessary.
Has anyone ever experienced or heard of something like this?
Any thoughts as to what is causing it?
Thanks in advance for your help,
Landon
April 24, 2012 at 10:15 am
The only way something is going to 'lose' compression is if someone runs an ALTER INDEX ... WITH (DATA_COMPRESSION = NONE)
ALTER TABLE? No clustered index?
Have you monitored what commands the maint plan is running?
Have you checked if it really is losing compression? (the data_compression and data_compression_desc columns in sys.partitions will help here)
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
April 24, 2012 at 10:33 am
Thanks for your prompt reply Gail.
I'm fairly certain it is actually 'losing' compression because I used the following statement to generate the statements that actually do the compression and they rely on the data_compression column in the sys.partitions statement:
SET NOCOUNT ON
SELECT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=ROW,MAXDOP=3);' AS SqlStatement
FROM sys.objects o WITH (NOLOCK)
JOIN sys.tables t WITH (NOLOCK) ON t.[object_id] = o.[object_id]
JOIN sys.indexes i WITH (NOLOCK) ON i.[object_id] = o.[object_id]
JOIN sys.schemas s WITH (NOLOCK) ON s.[schema_id] = o.[schema_id]
JOIN sys.partitions AS p WITH (NOLOCK) ON p.[object_id] = i.[object_id]
AND p.[index_id] = i.[index_id]
WHERE o.[type] = 'U'
AND i.[type] IN (0,1)
AND p.data_compression != 1
ORDER BY SqlStatement;
If it is compressed and I run this statement, I get nothing. When it 'loses' its compression, I can rerun this statement and get every table in the db. FYI, I also have a similar statement for indexes.
I have not monitored the commands the maintenance plan is running. I will research how to do that and see what I can find. I've only been a DBA for 10 months. Sorry if that should be obvious.
I will also look further into the data_compression_desc column in sys.partitions.
April 24, 2012 at 10:45 am
lwright 19926 (4/24/2012)
If it is compressed and I run this statement, I get nothing. When it 'loses' its compression, I can rerun this statement and get every table in the db. FYI, I also have a similar statement for indexes.
Ok, so something, somewhere is running an alter index and explicitly stating data_compression = none. Probably the maintenance plan, they're terrible for index maintenance.
Try http://ola.hallengren.com/Versions.html or http://sqlfool.com/2011/06/index-defrag-script-v4-1/ instead of maint plans for index maintenance.
I have not monitored the commands the maintenance plan is running. I will research how to do that and see what I can find. I've only been a DBA for 10 months. Sorry if that should be obvious.
SQL Profiler
I will also look further into the data_compression_desc column in sys.partitions.
Don't bother. It's just the description where data_compression is the number. If the column data_compression is 1, data_compression_desc will be ROW
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply