Performance monitor, SQL Server and PAGE compression

  • Article

I tend to automate perfmon collection with logman.exe logging to a SQL Server 2008 database via the System DSN (ODBC data source) as follows:

@echo off
REM create the counter log, counters specified in .config file
REM stores the counter log in a .blg format, this can alos be stored in a SQL Server database if required

REM set environment variables
SET SERVER1=\\benjones01
SET CONFIGFILE=SQL2005BaselineCounters.config
SET START=09/02/2010 16:00:00
SET END=09/02/2010 17:00:00

ECHO ====================================================
ECHO Creating performance monitor counter sets on %SERVER1%
ECHO ====================================================

logman create counter MicrosoftSSIS2005Perf -s %SERVER1% -f bin -b %START% -E %END% -si 5 -v mmddhhmm -o "Perfmon_SSIS_Performance" -cf %CONFIGFILE%

PAUSE

In this example, the config file contains a list of the counters I am interested in:

"\\SERVERNAME\LogicalDisk(*)\*"
"\\SERVERNAME\Memory\*"
"\\SERVERNAME\MSSQL:Access Methods\*"
"\\SERVERNAME\MSSQL:Buffer Manager\*"
"\\SERVERNAME\MSSQL:Buffer Node(*)\*"
"\\SERVERNAME\MSSQL:CLR\*"
"\\SERVERNAME\MSSQL:Databases(*)\*"
"\\SERVERNAME\MSSQL:Exec Statistics(*)\*"
"\\SERVERNAME\MSSQL:General Statistics\*"
"\\SERVERNAME\MSSQL:Latches\*"
"\\SERVERNAME\MSSQL:Locks(*)\*"
"\\SERVERNAME\MSSQL:Memory Manager\*"
"\\SERVERNAME\MSSQL:Plan Cache(*)\*"
"\\SERVERNAME\MSSQL:SQL Statistics\*"
"\\SERVERNAME\MSSQL:Transactions\*"
"\\SERVERNAME\MSSQL:Wait Statistics(*)\*"
"\\SERVERNAME\Network Interface(*)\*"
"\\SERVERNAME\PhysicalDisk(*)\*"
"\\SERVERNAME\Process(sqlservr)\*"
"\\SERVERNAME\SQLServer:SSISPipeline\*"
"\\SERVERNAME\Processor(*)\*"
"\\SERVERNAME\System\Context Switches/sec"
"\\SERVERNAME\System\Processor Queue Length"
"\\SERVERNAME\System\Threads"

Logging can generate quite a lot of data depending on the threshold specified in the –si parameter for logman.exe.

Logging to a SQL Server database makes it easier to query the data as the binary (blg) files can be awkward to deal with when they are very large.

SQL Server 2008 also provides an added benefit in that I can compress the main table, [dbo].[CounterData] quite significantly using PAGE compression.  The stored procedure shown below provided an estimate of the space saving, this was about 1/3 of the original table size.

EXEC sp_estimate_data_compression_savings 'dbo', 'CounterData', NULL, NULL, 'PAGE' ;
GO

The screenshot below illustrates rebuild procedure in order to apply compression at the page level.  My laptop is definitely struggling whilst the data is being compressed…

  image