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.

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

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