sp_helpindex2

Replacement for SP_HELPINDEX. This proc enumerates the INCLUDE columns.

Reposting from:

http://www.sqlservercentral.com/scripts/Indexing/71223/

 

use master
go
create procedure dbo.sp_helpindex2

    @objname nvarchar(776)        -- the table to check for indexes
 

as
 

/*
 

Original Script - Microsoft Corporation
Modified by - Girish Sumaria - girish.sumaria@gmail.com
Information - The sp_helpindex only provides the list of columns in the index and not the INCLUDED COLUMNS.
       In order to retrieve complete index information, I have modified the original code so that
        you can now retrieve INCLUDED COLUMNS list as well. Also, the index type can be retrived separately.
Tip from my fellow Database Developer - Prasant Nanda - prasant.nanda@gmail.com
        Ensure that you mark this procedure as a SYSTEM PROCEDURE to leverage its use from all databases.
*/
    -- PRELIM
    set nocount on
    declare @objid int,            -- the object id of the table
            @indid smallint,    -- the index id of an index
            @groupid int,         -- the filegroup id of an index
            @indname sysname,
            @groupname sysname,
            @status int,
            @keys nvarchar(2126),    --Length (16*max_identifierLength)+(15*2)+(16*3)
            @included_keys nvarchar(2126),    --Length (16*max_identifierLength)+(15*2)+(16*3)
            @InclCol nvarchar(225),
            @dbname    sysname,
            @ignore_dup_key    bit,
            @is_unique        bit,
            @is_hypothetical    bit,
            @is_primary_key    bit,
            @is_unique_key     bit,
            @auto_created    bit,
            @no_recompute    bit
    -- Check to see that the object names are local to the current database.
    select @dbname = parsename(@objname,3)
--    print @dbname
    if @dbname is null
        select @dbname = db_name()
    else if @dbname <> db_name()
        begin
            raiserror(15250,-1,-1)
            return (1)
        end
    -- Check to see the the table exists and initialize @objid.
    select @objid = object_id(@objname)
 

--    print @objid
 

    if @objid is NULL
 

    begin
 

        raiserror(15009,-1,-1,@objname,@dbname)
 

        return (1)
 

    end
 

    -- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
 

    declare ms_crs_ind cursor local static for
 

        select i.index_id, i.data_space_id, i.name,
 

            i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
 

            s.auto_created, s.no_recompute
 

        from sys.indexes i join sys.stats s
 

            on i.object_id = s.object_id and i.index_id = s.stats_id
 

        where i.object_id = @objid
 

    open ms_crs_ind
 

    fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute
 

    -- IF NO INDEX, QUIT
 

    if @@fetch_status < 0
 

    begin
 

        deallocate ms_crs_ind
 

        raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
 

        return (0)
 

    end
 

    -- create temp table
 

    CREATE TABLE #spindtab
 

    (
 

        index_name            sysname    collate database_default NOT NULL,
 

        index_id                int,
 

        ignore_dup_key        bit,
 

        is_unique                bit,
 

        is_hypothetical        bit,
 

        is_primary_key        bit,
 

        is_unique_key            bit,
 

        auto_created            bit,
 

        no_recompute            bit,
 

        groupname            sysname collate database_default NULL,
 

        index_keys            nvarchar(2126)    collate database_default NOT NULL, -- see @keys above for length descr
 

        included_keys            nvarchar(2126)    collate database_default NULL
 

    )
 

    -- Now check out each index, figure out its type and keys and
 

    --    save the info in a temporary table that we'll print out at the end.
 

    while @@fetch_status >= 0
 

    begin
 

        -- First we'll figure out what the keys are.
 

        declare @i int, @thiskey nvarchar(131) -- 128+3
 

        select @keys = index_col(@objname, @indid, 1), @i = 2
 

        if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
 

            select @keys = @keys + '(-)'
 

        select @thiskey = index_col(@objname, @indid, @i)
 

        if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
 

            select @thiskey = @thiskey + '(-)'
 

        while (@thiskey is not null )
 

        begin
 

            select @keys = @keys + ', ' + @thiskey, @i = @i + 1
 

            select @thiskey = index_col(@objname, @indid, @i)
 

            if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
 

                select @thiskey = @thiskey + '(-)'
 

        end
 

/* Code to find Included Columns goes here */
 

    set @InclCol=null
 

    set @included_keys=null
 

    declare ms_crs_inc_cols cursor local static for
 

        SELECT --sys.tables.object_id,
 

            --sys.tables.name as table_name, sys.indexes.name as index_name,sys.indexes.type_desc as Ind_Type,
 

        sys.columns.name as column_name
 

        --,sys.index_columns.index_column_id, sys.indexes.is_unique, sys.indexes.is_primary_key , sys.index_columns.is_included_column
 

        FROM sys.tables, sys.indexes, sys.index_columns, sys.columns
 

        WHERE (sys.tables.object_id = sys.indexes.object_id AND sys.tables.object_id = sys.index_columns.object_id AND sys.tables.object_id = sys.columns.object_id
 

        AND sys.indexes.index_id = sys.index_columns.index_id AND sys.index_columns.column_id = sys.columns.column_id)
 

        AND sys.indexes.object_id = @objid
 

            and sys.indexes.index_id = @indid
 

            and is_included_column=1
 

        order by index_column_id
 

    open ms_crs_inc_cols
 

    fetch next from ms_crs_inc_cols into @InclCol
 

    while @@fetch_status >= 0
 

    begin
 

        if @included_keys is null
 

            set @included_keys=@InclCol
 

        else
 

            set @included_keys=@included_keys+','+@InclCol
 

        print @included_keys
 

        fetch next from ms_crs_inc_cols into @InclCol
 

    end
 

    close ms_crs_inc_cols
 

    deallocate ms_crs_inc_cols
 

/* Code to find Included Columns ends here */
 

        select @groupname = null
 

        select @groupname = name from sys.data_spaces where data_space_id = @groupid
 

        -- INSERT ROW FOR INDEX
 

        insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys,@included_keys)
 

        -- Next index
 

        fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
 

            @is_primary_key, @is_unique_key, @auto_created, @no_recompute
 

    end
 

    deallocate ms_crs_ind
 

    -- DISPLAY THE RESULTS
 

    select
 

        'index_name' = index_name,
 

        'type' = case when index_id = 1 then 'clustered' else 'nonclustered' end,
 

        'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
 

                case when ignore_dup_key <>0 then 'ignore duplicate keys' else '' end
 

                + case when is_unique <>0 then ', unique' else '' end
 

                + case when is_hypothetical <>0 then ', hypothetical' else '' end
 

                + case when is_primary_key <>0 then ', primary key' else '' end
 

                + case when is_unique_key <>0 then ', unique key' else '' end
 

                + case when auto_created <>0 then ', auto create' else '' end
 

                + case when no_recompute <>0 then ', stats no recompute' else '' end
 

                + ' located on ' + groupname),
 

        'index_keys' = index_keys,
 

        'included_keys' = included_keys
 

    from #spindtab
 

    order by index_name
 

    return (0) -- sp_helpindex
 

go
 

USE Master
 

EXEC sp_MS_marksystemobject sp_helpindex2
 

go

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