Compare Statistics IO Outputs

Return to Resume

ISSUE:

How can I use output from "SET STATISTICS IO ON;" " to determine the impact of my changes to the stored procedure

Some Initial Setup Required:


/*
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
********************************
ERROR: Msg 7403, Level 16, State 1, Line 31
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
FIX = INSTALL "AccessDatabaseEngine_x64.exe"
********************************
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
********************************
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 47
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
FIX =
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
GO  --Alternately In Sql Management Studio go to Server Objects - Linked servers - Providers - Microsoft.Jet.OLEDB.4.0 then Options and Uncheck "Allow Inprocess".
FIX = Add “-g512;” to the front of the value for parameter “Startup Parameters”.
********************************
********************************
Msg 7399, Level 16, State 1, Line 57
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 57
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
FIX = Change SQL Server Data Engine Account from "NT Service\MSSQLServer" to a Domain Account with access
********************************
*/
	

Now that setup is complete " ... Time To Collect [StatsIO] Data


USE [master];
GO
IF NOT EXISTS
(
SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID('dbo.uspCompareStatsIO')
AND [type] = 'P'
)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.uspCompareStatsIO AS'
END
GO
/*
EXEC [master].dbo.uspCompareStatsIO 'C:\Temp\','_ForLoading_StatisticsIO_0.csv','_ForLoading_StatisticsIO_1.csv';
*/
ALTER PROCEDURE dbo.uspCompareStatsIO
(
	@FilePath VARCHAR(50) = 'C:\Temp\'
,@BaseLineFile VARCHAR(50) = 'BaseLine.csv'
,@CompareFile VARCHAR(50) = 'Compare.csv'
,@SQLText NVARCHAR(MAX) = NULL
) AS
SET NOCOUNT ON;
SELECT @SQLText =
'WITH cteOrigVersion AS
(
SELECT
TN=SUBSTRING(F1,CHARINDEX('''''''',F1)+1,CHARINDEX(''.'',F1)-CHARINDEX('''''''',F1)-2)
,SumLR=SUM(CAST(SUBSTRING(F2,CHARINDEX(''logical reads '',F2)+14,10) AS INT))
,SumSC=SUM(CAST(SUBSTRING(F1,CHARINDEX(''Scan Count '',F1)+11,10) AS INT))
,CPUTime=NULL
,RunTime=NULL
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@BaseLineFile +'''
)
WHERE LEFT(F1,5)=''Table''
GROUP BY
SUBSTRING(F1,CHARINDEX('''''''',F1)+1,CHARINDEX(''.'',F1)-CHARINDEX('''''''',F1)-2)
UNION
SELECT TOP 1
TN=''ParseAndCompile''
,SumLR=NULL
,SumSC=NULL
,CPUTime=CAST(SUBSTRING(F1,CHARINDEX(''CPU time = '',F1)+11,(CHARINDEX('' ms'',(F1)))-(CHARINDEX(''CPU TIME = '',F1)+11)) AS INT)
,RunTime=CAST(SUBSTRING(F2,CHARINDEX(''elapsed time = '',F2)+15,(CHARINDEX('' ms'',(F2)))-(CHARINDEX(''elapsed time = '',F2)+15)) AS INT)
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@BaseLineFile +'''
)
WHERE F1 LIKE ''CPU time = %''
AND F1<>''CPU time = 0 ms'' AND F2<>''elapsed time = 0 ms.''
UNION
SELECT
TN=''Execution''
,SumLR=NULL
,SumSC=NULL
,CPUTime=SUM(X.CPUTime)
,RunTime=SUM(X.RunTime)
FROM
(
SELECT
CPUTime=CAST(SUBSTRING(F1,CHARINDEX(''CPU time = '',F1)+11,(CHARINDEX('' ms'',(F1)))-(CHARINDEX(''CPU TIME = '',F1)+11)) AS INT)
,RunTime=CAST(SUBSTRING(F2,CHARINDEX(''elapsed time = '',F2)+15,(CHARINDEX('' ms'',(F2)))-(CHARINDEX(''elapsed time = '',F2)+15)) AS INT)
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@BaseLineFile +'''
)
WHERE F1 LIKE ''CPU time = %''
ORDER BY F3
OFFSET 1 ROWS
) X
)
,cteNewVersion AS
(
SELECT
TN=SUBSTRING(F1,CHARINDEX('''''''',F1)+1,CHARINDEX(''.'',F1)-CHARINDEX('''''''',F1)-2)
,SumLR=SUM(CAST(SUBSTRING(F2,CHARINDEX(''logical reads '',F2)+14,10) AS INT))
,SumSC=SUM(CAST(SUBSTRING(F1,CHARINDEX(''Scan Count '',F1)+11,10) AS INT))
,CPUTime=NULL
,RunTime=NULL
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@CompareFile +'''
)
WHERE LEFT(F1,5)=''Table''
GROUP BY
SUBSTRING(F1,CHARINDEX('''''''',F1)+1,CHARINDEX(''.'',F1)-CHARINDEX('''''''',F1)-2)
UNION
SELECT TOP 1
TN=''ParseAndCompile''
,SumLR=NULL
,SumSC=NULL
,CPUTime=CAST(SUBSTRING(F1,CHARINDEX(''CPU time = '',F1)+11,(CHARINDEX('' ms'',(F1)))-(CHARINDEX(''CPU TIME = '',F1)+11)) AS INT)
,RunTime=CAST(SUBSTRING(F2,CHARINDEX(''elapsed time = '',F2)+15,(CHARINDEX('' ms'',(F2)))-(CHARINDEX(''elapsed time = '',F2)+15)) AS INT)
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@CompareFile +'''
)
WHERE F1 LIKE ''CPU time = %''
AND F1<>''CPU time = 0 ms'' AND F2<>''elapsed time = 0 ms.''
UNION
SELECT
TN=''Execution''
,SumLR=NULL
,SumSC=NULL
,CPUTime=SUM(X.CPUTime)
,RunTime=SUM(X.RunTime)
FROM
(
SELECT
CPUTime=CAST(SUBSTRING(F1,CHARINDEX(''CPU time = '',F1)+11,(CHARINDEX('' ms'',(F1)))-(CHARINDEX(''CPU TIME = '',F1)+11)) AS INT)
,RunTime=CAST(SUBSTRING(F2,CHARINDEX(''elapsed time = '',F2)+15,(CHARINDEX('' ms'',(F2)))-(CHARINDEX(''elapsed time = '',F2)+15)) AS INT)
FROM OPENROWSET
(
''Microsoft.ACE.OLEDB.12.0''
,''Text;Database='+@FilePath+';HDR=No;''
,''SELECT * FROM '+@CompareFile +'''
)
WHERE F1 LIKE ''CPU time = %''
ORDER BY F3
OFFSET 1 ROWS
) X
)
,cteRollup AS
(
SELECT
O.TN
,olr=SUM(O.SumLR)
,nlr=SUM(ISNULL(N.SumLR,0))
,lrd=SUM(ISNULL(N.SumLR,0)-O.SumLR)
,osc=SUM(O.SumSC)
,nsc=SUM(ISNULL(N.SumSC,0))
,scd=SUM(ISNULL(N.SumSC,0)-O.SumSC)
,oct=SUM(O.CPUTime)
,nct=SUM(ISNULL(N.CPUTime,0))
,ctd=SUM(ISNULL(N.CPUTime,0)-O.CPUTime)
,ort=SUM(O.RunTime)
,nrt=SUM(ISNULL(N.RunTime,0))
,rtd=SUM(ISNULL(N.RunTime,0)-O.RunTime)
FROM
cteOrigVersion O
LEFT JOIN
cteNewVersion N
ON N.TN=O.TN
GROUP BY
O.TN
WITH ROLLUP
)
,cteBaseResults AS
(
SELECT DISTINCT
TN =
CASE
WHEN TN IS NULL THEN ''--- Grand Total ---''
ELSE TN
END
,RecType =
CASE
WHEN TN IS NULL THEN 3
WHEN nlr=0 AND nlr<>olr THEN 1
ELSE 0
END
,KeyValue =
CASE
WHEN TN IS NULL THEN 3
ELSE 0
END
,olr
,nlr
,lrd
,osc
,nsc
,scd
,oct
,nct
,ctd
,ort
,nrt
,rtd
FROM
cteRollup
)
,cteAdjustedTotalFix AS
(
SELECT
TN=''--- Adjusted Total ---''
,RecType=2
,KeyValue=3
,nlr=SUM(nlr)
,nsc=SUM(nsc)
,nct=SUM(nct)
,nrt=SUM(nrt)
FROM
cteBaseResults
WHERE
RecType=0
AND TN IS NOT NULL
GROUP BY
RecType
)
,cteGrandTotalFix AS
(
SELECT
TN=''--- Adjusted Total ---''
,RecType=2
,KeyValue=3
,olrCorrection=SUM(olr)
,nlrCorrection=SUM(nlr)
,lrdCorrection=SUM(lrd)
,oscCorrection=SUM(osc)
,nscCorrection=SUM(nsc)
,scdCorrection=SUM(scd)
,octCorrection=SUM(oct)
,nctCorrection=SUM(nct)
,ctdCorrection=SUM(ctd)
,ortCorrection=SUM(ort)
,nrtCorrection=SUM(nrt)
,rtdCorrection=SUM(rtd)
FROM
cteBaseResults
WHERE
RecType=1
GROUP BY
RecType
)
,cteFinalResults AS
(
SELECT
TN
,olr
,nlr
,lrd
,osc
,nsc
,scd
,oct
,nct
,ctd
,ort
,nrt
,rtd
,RecType
FROM
cteBaseResults
UNION
SELECT
GTF.TN
,olr=(BR.olr-GTF.olrCorrection)
,nlr=ATF.nlr
,lrd=(BR.lrd-GTF.lrdCorrection)
,osc=(BR.osc-GTF.oscCorrection)
,nsc=ATF.nsc
,scd=(BR.scd-GTF.scdCorrection)
,oct=(BR.oct-GTF.octCorrection)
,nct=ATF.nct
,ctd=(BR.ctd-GTF.ctdCorrection)
,ort=(BR.ort-GTF.ortCorrection)
,nrt=ATF.nrt
,rtd=(BR.rtd-GTF.rtdCorrection)
,GTF.RecType
FROM
cteGrandTotalFix GTF
INNER JOIN
cteBaseResults BR
ON BR.KeyValue=GTF.KeyValue
INNER JOIN
cteAdjustedTotalFix ATF
ON ATF.KeyValue=GTF.KeyValue
)
SELECT
TN
,origLogicalReads=LEFT(CONVERT(VARCHAR,CAST(olr AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(olr AS MONEY),1))-3)
,newLogicalReads=CASE WHEN TN IN (''Execution'',''ParseAndCompile'') THEN NULL ELSE LEFT(CONVERT(VARCHAR,CAST(nlr AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(nlr AS MONEY),1))-3) END
,LogicalReads_Diff=LEFT(CONVERT(VARCHAR,CAST(lrd AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(lrd AS MONEY),1))-3)
,origScanCount=LEFT(CONVERT(VARCHAR,CAST(osc AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(osc AS MONEY),1))-3)
,newScanCount=CASE WHEN TN IN (''Execution'',''ParseAndCompile'') THEN NULL ELSE LEFT(CONVERT(VARCHAR,CAST(nsc AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(nsc AS MONEY),1))-3) END
,ScanCount_Diff=LEFT(CONVERT(VARCHAR,CAST(scd AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(scd AS MONEY),1))-3)
,origCPUTime=LEFT(CONVERT(VARCHAR,CAST(oct AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(oct AS MONEY),1))-3)
,newCPUTime=CASE WHEN TN IN (''Execution'',''ParseAndCompile'',''--- Grand Total ---'') THEN LEFT(CONVERT(VARCHAR,CAST(nct AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(nct AS MONEY),1))-3) ELSE NULL END
,CPUTime_Diff=LEFT(CONVERT(VARCHAR,CAST(ctd AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(ctd AS MONEY),1))-3)
,origRunTime=LEFT(CONVERT(VARCHAR,CAST(ort AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(ort AS MONEY),1))-3)
,newRunTime=CASE WHEN TN IN (''Execution'',''ParseAndCompile'',''--- Grand Total ---'') THEN LEFT(CONVERT(VARCHAR,CAST(nrt AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(nrt AS MONEY),1))-3) ELSE NULL END
,RunTime_Diff=LEFT(CONVERT(VARCHAR,CAST(rtd AS MONEY),1),LEN(CONVERT(VARCHAR,CAST(rtd AS MONEY),1))-3)
FROM
cteFinalResults
ORDER BY
RecType
,olr DESC
,osc DESC
,TN;'
--PRINT @SQLText;
EXEC sys.sp_executesql @SQLText;
GO

Collect Data ... then ... View Results


/*
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
ORIGINAL VERSION (_ForLoading_StatisticsIO_0.csv):
EXEC TimeHistory.dbo.usp_RPT_Unsent_PayRecords NULL,'1/1/2013','12/31/2013','ADSU',186,'2',197906,176483,'1','1';
NEW VERSION (_ForLoading_StatisticsIO_1.csv):
EXEC TimeHistory.dbo.usp_RPT_Unsent_PayRecords_UBB NULL,'1/1/2013','12/31/2013','ADSU',186,'2',197906,176483,'1','1';
EXEC [master].dbo.uspCompareStatsIO
	@FilePath = 'C:\Temp\'
,@BaseLineFile = 'ProdBaseline_SprocRun1_51209Rows.csv'
,@CompareFile = 'ProdCompare_AdHocRun1_51209Rows.csv';
EXEC [master].dbo.uspCompareStatsIO
	@FilePath = 'C:\Temp\'
,@BaseLineFile = '_ForLoading_StatisticsIO_0.csv'
,@CompareFile = '_ForLoading_StatisticsIO_1.csv';
*/