September 2011 - Posts

Making SQLNexus work smarter with Enterprise Edition
19 September 11 09:31 AM | GavinPayneUK | with no comments

For those not familiar with it, SQLNexus is a free graphical analysis tool from Microsoft which analyses SQL Server traces captured using the perfstats set of scripts.  They’re all free and available from Codeplex (link) and most importantly, use diagnostic components built into the SQL Server installation.  As a SQL Server consultant Perfstats and SQLNexus are invaluable to me when I want to find the most expensive queries executed on a system, by execution count, IO or CPU time, and look at the execution plans which were being used.

The SQLNexus application uses a SQL Server database into which it initially imports the trace files and stores aggregated data about every statement captured in the trace files.  The application then uses a reporting control to graphically show summaries of the imported data. 

Page Compression

Anyway, the purpose of my post was about something I found last week which positively surprised me.  SQLNexus not only detects which version of SQL Server it’s using for its data store, but also which edition.  I know this as by accident I found 65 tables in a SQLNexus database which have page compression enabled.  Page compression in case some are unaware is only available in the Enterprise, Developer and Evaluation editions of SQL Server 2008 and R2.  The benefit of this feature comes by turning an initial expensive CPU task of compressing the data into subsequently physically smaller amounts of data to write and then read from physical disk.  When you consider how large some SQLNexus databases get, some of mine are 8GB+, the IO saving page compression brings is a big performance boost when analysing the data.

In the database I found with it enabled the space saving wasn’t amazing, approximately 20%, but I only had 8498 rows.  In previous databases I’ve had SQLNexus create I’ve seen over a million rows, so knowing there’s some compression optimisation happening in the background is handy.