JD Edwards and SQL Server Compression

Over the last few weekends we’ve been compressing a JD Edwards database, and I thought I’d post the results. The following is a report based on the project.

Compression Results

Summary:

Over the weekend I completed compressing the Top 10 Tables per our original discussion.

Initially, the database was 273GB of space used inside it. After all the compression has been finished, we are now at 157GB.

This is a reduction of 42.51% in the JDE database size.

The database files are still the same size. I have not shrunk them yet… and wouldn’t advise it unless you needed the space back for some reason or another.

You should now be able to go years without having to consider adding any more disk space or worry about IO problems.

List of Tables/Indexes Compressed

Schema Table Rows Type
1 PRODDTA F0101Z2 2728142 PAGE
2 PRODDTA F0115 3583766 ROW
3 PRODDTA F03B11 3813755 PAGE
4 PRODDTA F03B14 5277380 PAGE
5 PRODDTA F03B21 6766369 PAGE
6 PRODDTA F0911 19424122 PAGE
7 PRODDTA F4074 6801498 PAGE
8 PRODDTA F4111 8210392 PAGE
9 PRODDTA F42119 8104459 PAGE
10 PRODDTA F49211 2799436 PAGE
11 PRODDTA F49299 6878974 PAGE

Data Points:

Baseline:

BACKUP DATABASE successfully processed 33442892 pages in 473.482 seconds (551.811 MB/sec).

After First Wave of Compression:

BACKUP DATABASE successfully processed 26944689 pages in 340.404 seconds (618.398 MB/sec).

After Final Compression:

BACKUP DATABASE successfully processed 19244052 pages in 354.502 seconds (424.099 MB/sec).

Final File Sizes and Utilization :

As you can see, you now have 434GB of empty space in your database now…

 FILEID FILE_SIZE_MB SPACE_USED_MB FREE_SPACE_MB NAME
1 75000.00 23223.69 51776.31 JDE_PROD_Data
2 109000.00 92.33 108907.67 JDE_PROD_Log
3 100000.00 38333.88 61666.13 JDE_PROD_Data2
4 150000.00 44162.38 105837.63 JDE_PROD_Data3
5 150000.00 44173.75 105826.25 JDE_PROD_Data4

Here is a list of the scripts that I ran for each table over the weekend and the amount of time it took to compress each one. To remove the compression on a table, run the same script, but just change the followiong parameter:   DATA_COMPRESSION = PAGE  to DATA_COMPRESSION = NONE

— 13 min 50 sec

ALTER INDEX ALL ON [PRODDTA].F03B14 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 min 47 sec

ALTER INDEX ALL ON [PRODDTA].F03B21 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 1 hour 6 min 18 sec

ALTER INDEX ALL ON [PRODDTA].F42119 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 17 min 17 sec

ALTER INDEX ALL ON [PRODDTA].F4111 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— 3 hours 27 min 57 sec

ALTER INDEX ALL ON [PRODDTA].F0911 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

—  F49299 2 min 36 sec

ALTER INDEX ALL ON [PRODDTA].F49299 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F49211 2 min 7 sec

ALTER INDEX ALL ON [PRODDTA].F49211 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

— F0101Z2 4 min 56 sec

ALTER INDEX ALL ON [PRODDTA].F0101Z2 REBUILD WITH (MAXDOP=0, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE);

Plan Cache Analysis: The Down Side

The other day my brother sends me this blog link and asks what I think about it:

https://www.simple-talk.com/sql/t-sql-programming/checking-the-plan-cache-warnings-for-a-sql-server-database

I answer back to him with a recent story from another one of my customers.

The issue here is that people write posts on analyzing memory structures without ever addressing the potential side effects of them. In this case, scanning the database cache looking for errors, missing indexes, scans, etc…

Just like with anything, there is no free lunch. As much upside as there is to looking through the cache, here is the downside: depending on the size of the cache being analyzed– the DBA risks being the biggest problem on the entire system.

Here is an example of what a DBA did at one of my customers. They scraped a script off some website and just started running it in the middle of the business day looking for parallelism in plan caches:

-- Collect parallel plan information
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO master.dbo.PlanCacheForMaxDop
SELECT
 query_plan AS CompleteQueryPlan,
 n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS StatementText,
 n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel,
 n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost,
 n.query('.') AS ParallelSubTreeXML,
 ecp.usecounts,
 ecp.size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS eqp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
WHERE n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1;

What he didn’t understand was that each time he ran it, about 600GB of reads was being pushed through the CPU (as seen via Profiler):

 
Month-Day   Hour        Bytes Per Query
----------- ----------- ----------------------------- 
1-6         16          589,001,506,816.00 
1-8         19          622,114,177,024.00 

While in itself, there’s nothing wrong with looking at the caches, just be aware of the classic Heisenberg Principle: You can’t monitor something without disturbing it.

In this case, his monitoring was actually worse than the queries he was looking for.

PeopleSoft PROCESS_INSTANCE Normalizer

In the previous post, I covered how to normalize hard coded expressions inside a query. In this post, there is a specific problem related to PeopleSoft; it’s that often the PROCESS_INSTANCE is hard coded and needs to be normalized.

For example, here’s an INSERT statement from a Profiler trace of a PeopleSoft system:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

The problem is, we can’t do any aggregations on this to find the number of unique queries in the file as all of them could be unique simply because the PROCESS_INSTANCE keeps changing… but in reality… it’s all the same query… there really is only ONE unique query.

What we want to do is make a function that strips out the hard coded values and replaces them with “xxx” or whatever you want to put in.

select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')

-- the dbo.norm_psft function will convert

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

-- into:

INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT xxx , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = xxx AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)

Here’s the code:

create function norm_psft(@str varchar(8000))
returns varchar(8000)
as begin

-- usage:
-- select dbo.norm_psft('INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)')
-- select dbo.norm_psft('frank mcbath')


-- testing
-- declare @str varchar(8000) 
-- set @str = 'INSERT INTO PS_REQ_HDR_T4 ( PROCESS_INSTANCE , BUSINESS_UNIT , REQ_ID) SELECT DISTINCT 26337824 , BUSINESS_UNIT , REQ_ID FROM PS_REQ_DST_SUM_T4 WHERE PROCESS_INSTANCE = 26337824 AND (QTY_OPEN_STD > 0 OR AMT_OPEN <> 0)'


declare @i int
declare @c char(1)
declare @pi_offset_len int
set @i = charindex( 'PROCESS_INSTANCE = ', @str)

if (@i > 1)
begin

set @i = charindex( 'PROCESS_INSTANCE = ', @str)
set @i = @i + 20 -- this is the beginning char after the '= '

while (@i < (len(@str)+1))
begin

-- figure out how long the process instance integer is

 set @c = substring(@str,@i,1)

 if substring(@str,@i,1) = ' '
 begin
 set @pi_offset_len = @i
 break
 end

 set @i=@i+1

end

-- dig the PID out of the string... only one occurence is needed
declare @pid nvarchar(20)
set @pid = substring(
@str, 
(charindex( 'PROCESS_INSTANCE = ', @str)+19), 
@i-(charindex( 'PROCESS_INSTANCE = ', @str)+19))

-- now replace ALL occurences of the PID in the string and return the fixed string:

end

if (charindex( 'PROCESS_INSTANCE = ', @str) > 0)
 set @str = replace(@str, @pid, 'xxx')
 
return @str

end

Script to Reorg/Reindex/Rebuild Based On Percentages – A Re-Blog

I’m putting this script out here so I don’t need to hunt for it later on. It’s useful and common task people ask about often enough.

Original Blog:

http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

Note, I fixed some bugs in his original script.

 -- http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx
/*
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE  statement has been executed first.
*/

USE <change_me>
go

SET nocount ON;
go


DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);
DECLARE @dbid SMALLINT;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.

if OBJECT_ID('tempdb..#work_to_do') is not null
begin

drop table #work_to_do

end

SET @dbid = Db_id();
SELECT [object_id] AS objectid,
 index_id AS indexid,
 partition_number AS partitionnum,
 avg_fragmentation_in_percent AS frag,
 page_count
INTO #work_to_do
FROM sys.Dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR
SELECT objectid,
 indexid,
 partitionnum,
 frag
FROM #work_to_do;

OPEN partitions

 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

-- Open the cursor.OPEN partitions;
-- Loop through the partitions.
WHILE @@FETCH_STATUS = 0
BEGIN

 
 IF @@FETCH_STATUS < 0 BREAK;
 SELECT @objectname = Quotename(o.NAME),
 @schemaname = Quotename(s.NAME)
 FROM sys.objects AS o
 JOIN sys.schemas AS s
 ON s.schema_id = o.schema_id
 WHERE o.object_id = @objectid;
 
 SELECT @indexname = Quotename(NAME)
 FROM sys.indexes
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 SELECT @partitioncount = Count (*)
 FROM sys.partitions
 WHERE object_id = @objectid
 AND index_id = @indexid;
 
 -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

 IF @frag < 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
 IF @frag >= 30.0
 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
 IF @partitioncount > 1
 SET @command = @command + N' PARTITION=' + Cast(@partitionnum AS NVARCHAR(10));
 EXEC (@command);
 PRINT N'Executed: ' + @command;


 FETCH next
 FROM partitions
 INTO @objectid,
 @indexid,
 @partitionnum,
 @frag;

END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
go 

String Flattening and Normalization

I often end up with a variety of data where strings are hard coded with values, but the queries are really the same under the sheets. This means I have a hard time really determining how many unique queries I really have.

This function will flatten out the hard coded values into ‘xxx’… thus allowing me to compare like strings.

create table tempdb..denorm(strMixed varchar(8000))

insert into tempdb..denorm values ('select * from tblContacts where Id = ''123'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''234'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''456'' and type = 12')

select strMixed from tempdb..denorm

select dbo.norm_string(strMixed) from tempdb..denorm

strMixed
--------------------------------------------------------------
select * from tblContacts where Id = '123' and type = 12
select * from tblContacts where Id = '234' and type = 12
select * from tblContacts where Id = '456' and type = 12

(3 row(s) affected)


--------------------------------------------------------------
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12

(3 row(s) affected)

Here’s the code:

create function norm_string(@str varchar(8000))
returns varchar(8000)
as begin

declare @tblOffset table
(
position int identity,
offset int
)



declare @i bigint
declare @c char(1)
declare @str2 varchar(8000)
declare @str3 varchar(8000)

set @i = 1


while @i < (len(@str)+1)
begin

 set @c = substring(@str,@i,1)
 if @c = char(39)
 begin
 insert into @tblOffset(offset) values (@i)
 end

 set @i=@i+1

end

-- select * from @tblOffset

declare @positionprime int
declare @offsetprime int

declare @position int
declare @offset int
declare @offset1 int

declare @start int
declare @end int

declare @position2 int
declare @offset2 int
declare @stuff varchar(10)
declare @primingoffset int


set @stuff = 'xxx'


declare offsets_cursor cursor for 
select position, offset from @tblOffset

OPEN offsets_cursor

FETCH NEXT FROM offsets_cursor INTO @position, @primingoffset

-- first, get the head of the string. read from beginning to the first delimeter.

set @str2=substring(@str,1,@primingoffset)+@stuff


FETCH NEXT FROM offsets_cursor INTO @position, @offset1
FETCH NEXT FROM offsets_cursor INTO @position2, @offset2



WHILE @@FETCH_STATUS = 0
BEGIN


 set @str2 = @str2+substring(@str,@offset1,@offset2-@offset1)+char(39)+@stuff

 FETCH NEXT FROM offsets_cursor INTO @position, @offset1
 FETCH NEXT FROM offsets_cursor INTO @position2, @offset2


end

close offsets_cursor
deallocate offsets_cursor

-- now put the tail on


set @str2 = @str2+substring(@str,@offset1,len(@str)-@offset1+1)

return @str2
end

Compression of a PeopleSoft/SQL Server Database

Summary: A PeopleSoft 9.1 database on SQL Server compressed a maximum of 70%.

I’ve been doing some rudimentary testing today on a customer’s SQL Server 2008 R2 PeopleSoft database just to see what the compression ratios are. Different data types compress at different rates, but this post will show you what a PeopleSoft 9.1 database compresses at.

One large table:

ALTER INDEX ALL ON PS_CM_MATCHED_COST REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);

sp_spaceused PS_CM_MATCHED_COST

-- NO COMPRESSION

name                  rows       data      index_size 
------------------- --------- ----------- ------------ 
PS_CM_MATCHED_COST  52661662  10406784 KB   135888 KB 

-- PAGE 
-- 1 min 29 sec to compress to PAGE
 
name                  rows       data       index_size 
------------------- --------- ------------ ------------ 
PS_CM_MATCHED_COST  52661662    2168888 KB    11496 KB 

-- ROW
name                  rows        data      index_size 
-------------------- --------- ----------  ------------
PS_CM_MATCHED_COST   52661662   5455040 KB    28176 KB 



Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the

Here’s the uncompressed baseline:

FILE_SIZE_MB  SPACE_USED_MB NAME       FILENAME
------------- ------------- ---------- ------------------- 
242595.63     236784.56     FSSYS_Data F:\data\FS91SSD.mdf
   700.50         10.58     FSSYS_Log  L:\FS91SSD_log.LDF

Using PAGE compression, it took 2 hours, 6 minutes and 22 seconds to compress the 236GB of data and indexes. The size went from 236GB used to 70GB for a reduction of 70%.

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------ -------------- ---------- ---------------------
242595.63    70592.00       FSSYS_Data F:\data\FS91SSD.mdf
   700.50       78.89       FSSYS_Log  L:\FS91SSD_log.LDF

Compression with ROW method the database goes from 236GB to 142GB:

FILE_SIZE_MB SPACE_USED_MB  NAME       FILENAME
------------- ------------- ---------- ------------------
142500.00     142190.44     FSSYS_Data F:\data\FS91SSD.mdf
  1300.50         21.27     FSSYS_Log  L:\FS91SSD_log.LDF

Here’s the script to compress the entire database:

DECLARE @SQL NVARCHAR(2000) 
declare @psfttblname sysname

declare tblname cursor for select name from sysobjects where name like 'PS_%' and type = 'U'
OPEN tblname

FETCH NEXT FROM tblname 
INTO @psfttblname

WHILE @@FETCH_STATUS = 0
BEGIN
 
-- SET @SQL = 'ALTER TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ' REBUILD WITH (DATA_COMPRESSION=PAGE, maxdop = 0)' 
 set @SQL = 'ALTER INDEX ALL ON '+ @psfttblname+' REBUILD WITH (SORT_IN_TEMPDB = ON, data_compression = page, STATISTICS_NORECOMPUTE = ON, maxdop = 0);'
 print @SQL
 print 'start: '+cast(getdate() as nvarchar(20))
 EXEC (@SQL) 
 print 'end: '+cast(getdate() as nvarchar(20))
 print ''
FETCH NEXT FROM tblname 
INTO @psfttblname
end
close tblname
deallocate tblname


Space Free and Check Status of Compression Scripts:

select
 a.FILEID,
 [FILE_SIZE_MB] =
 convert(decimal(12,2),round(a.size/128.000,2)),
 [SPACE_USED_MB] =
 convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
 [FREE_SPACE_MB] =
 convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
 NAME = left(a.NAME,15),
 FILENAME = left(a.FILENAME,30)
 from
 dbo.sysfiles a

-- http://stackoverflow.com/questions/16988326/query-all-table-data-and-index-compression

SELECT [t].[name] AS [Table], [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
WHERE [p].[index_id] in (0,1)

SELECT [t].[name] AS [Table], [i].[name] AS [Index], 
 [p].[partition_number] AS [Partition],
 [p].[data_compression_desc] AS [Compression]
FROM [sys].[partitions] AS [p]
INNER JOIN sys.tables AS [t] ON [t].[object_id] = [p].[object_id]
INNER JOIN sys.indexes AS [i] ON [i].[object_id] = [p].[object_id] AND [i].[index_id] = [p].[index_id]
WHERE [p].[index_id] > 1 --and object_id('PS_CM_MATCHED_COST') = t.object_id

To shrink the database file from 242GB to 80GB took 37 minutes:

-- shrink database

dbcc shrinkfile('FSSYS_Data', 80000)

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
---- ------ ----------- ----------- --------- --------------
6      1    10240000    256000      9014872   9014872

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- 37 min

When RCSI Hurts You

Read Commit Snapshot Isolation (RCSI) is a feature in SQL Server that can help alleviate blocking in an application.

From an application point of view, there are pro’s and con’s. They are well enumerated in this post here:

http://sqlperformance.com/2014/05/t-sql-queries/read-committed-snapshot-isolation

With the applications I work with a lot (PeopleSoft, Siebel, & JD Edwards), it’s encourages to use this feature:

http://docs.oracle.com/cd/E38689_01/pt853pbr0/eng/pt/tadm/concept_MicrosoftSQLServerFeatureConsiderations-0779f1.html

So with so much going for it, what’s the downside?

SQL Server uses tempdb for row versioning. It keeps the BEFORE copy/version of the data in tempdb. As such, tempdb gets hammered a lot more than a database without RCSI. This is discussed here:

https://www.simple-talk.com/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/

Since tempdb is getting a lot more IO, you need more IOPS from your disk subsystem to support this new workload. There are only two ways to get this… use SSD or buy a lot more physical drives to handle the workload. I think about it like a car engine. Each cylinder does “x” many units of work. The more cylinders in and engine, the more work the engine can handle. Keep in mind your RAID levels, too. RAID 5 puts a 42% penalty on all writes. So NEVER use RAID 5 for tempdb as it’s 80% writing all the time.

Here’s what happened to a customer who is running a 300GB PeopleSoft database and turned on RCSI without re-sizing tempdb… write latency at 15 seconds… yes… seconds!

vReadLatency  vWriteLatency      DB     FileName
------------  -----------------  ------ ----------------
14            15032              tempdb T:\tempdb3.ndf
14            15032              tempdb T:\tempdb4.ndf
13            15030              tempdb T:\tempdb5.ndf
20            15025              tempdb T:\tempdb.mdf
14            15028              tempdb T:\tempdb7.ndf
17            15027              tempdb T:\tempdb8.ndf
13            15024              tempdb T:\tempdb6.ndf
19            12997              tempdb T:\tempdb2.ndf

There are many blog postings on sizing for RCSI. Just Google: “sizing tempdb for rcsi”

The easiest, cleanest, and often the cheapest solution is to just use local attached SSD for tempdb. Since tempdb is rebuilt every time SQL Server is cycled, you don’t need to RAID it, etc…

First choice for SSD would be something connected to the PCI bus. These are always faster. Second choice is something connected to the SATA/SAS bus… you will bottleneck on the bus… but it’s faster than than physical disk and a lot cheaper than SAN disk drives.

Script to see latency (Courtesy Jimmy May):

SELECT
--virtual file latency
vReadLatency
= CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms/num_of_reads) END
, vWriteLatency
= CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms/num_of_writes) END
, vLatency
= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END
----avg bytes per IOP
--, BytesperRead
--= CASE WHEN num_of_reads = 0
--THEN 0 ELSE (num_of_bytes_read/num_of_reads) END
--, BytesperWrite
--= CASE WHEN num_of_writes = 0
--THEN 0 ELSE (num_of_bytes_written/num_of_writes) END
--, BytesperTransfer
--= CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
--THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END

--, LEFT(mf.physical_name,2) as Drive
, substring(DB_NAME(vfs.database_id), 1, 10) as DB
--, mf.name AS FileName
--, vfs.*
, substring(mf.physical_name, 1, 15)
FROM sys.dm_io_virtual_file_stats(NULL,NULL) as vfs
JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
--WHERE mf.type_desc = ‘LOG’ — log files
--WHERE DB_NAME(vfs.database_id) IN (‘tpcc’,’tpcc2′)
ORDER BY vLatency DESC