Configuring a server side trace

When I’m after SQL Server performance  problems, SQL Server Profiler is still my number one tool. Allthough I know that extended events provide a mor lightweight solution, those are still a bit cumbersome to use (but I’ve seen that we can expect some improvements with SQL Server 2012).

When I’m using profiler to isolate performance issues, I try to configure server side traces, whenever possible. Fortunately, SQL Server Profiler will help you creating a script for a server side trace (File/Export/Script Trace Definition), so you don’t have to figure out all the event- and column-codes. Very good!

As I was doing the same configuration again and again, I decided to separate the TSQL code for the configuration inside a stored procedure.

And here comes dbo.configureServerSideTrace:

if object_id('dbo.configureServerSideTrace', 'P') is not null   drop procedure dbo.configureServerSideTrace go -- Example for: -- Start Trace --   declare @traceID int --   exec dbo.configureServerSideTrace @traceStatus = 1 --                                    ,@traceID = @traceID output --                                    ,@maxFileSize = 10000 --                                    ,@traceFileName = N'e:\VMITrace\Undo' --                                    ,@spId = @@spid -- -- End Trace --   exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID create procedure dbo.configureServerSideTrace                                  (@traceStatus   bit                  -- 1 => Start Trace                                                                       -- 0 => Stop Trace                                  ,@traceID       int output           -- If the Trace is started, this param will return the TraceID                                                                       -- For stopping the trace, the param has to be provided                                  ,@spId          int           = null -- provide the @@spid, if you want to filter only events for this conection                                                                       -- Optional. If not provided => no filter. Not needed for stopping the trace                                  ,@maxFileSize   bigint        = 5000 -- Maximum Trace File Size in Megabyte. Trace will be stopped, if the filesize is reached.                                  ,@traceFileName nvarchar(200) = null -- Name of the trace file (server side!)                                                                       -- Optional. Not neded for stoping the trace                                                                       -- Attention! If the file already exists, the SP will yield an error                                                                       -- and no trace is started.                                  ) as begin if (@traceStatus = 0) begin    exec sp_trace_setstatus @TraceID, 0    exec sp_trace_setstatus @TraceID, 2    return; end -- Create a Queue declare @rc int exec @rc = sp_trace_create @TraceID output, 0, @traceFileName, @maxfilesize, NULL if (@rc != 0) goto error -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 43, 15, @on exec sp_trace_setevent @TraceID, 43, 48, @on exec sp_trace_setevent @TraceID, 43, 1, @on exec sp_trace_setevent @TraceID, 43, 34, @on exec sp_trace_setevent @TraceID, 43, 35, @on exec sp_trace_setevent @TraceID, 43, 51, @on exec sp_trace_setevent @TraceID, 43, 4, @on exec sp_trace_setevent @TraceID, 43, 12, @on exec sp_trace_setevent @TraceID, 43, 13, @on exec sp_trace_setevent @TraceID, 43, 14, @on exec sp_trace_setevent @TraceID, 43, 22, @on exec sp_trace_setevent @TraceID, 42, 1, @on exec sp_trace_setevent @TraceID, 42, 14, @on exec sp_trace_setevent @TraceID, 42, 22, @on exec sp_trace_setevent @TraceID, 42, 34, @on exec sp_trace_setevent @TraceID, 42, 35, @on exec sp_trace_setevent @TraceID, 42, 51, @on exec sp_trace_setevent @TraceID, 42, 4, @on exec sp_trace_setevent @TraceID, 42, 12, @on exec sp_trace_setevent @TraceID, 45, 16, @on exec sp_trace_setevent @TraceID, 45, 48, @on exec sp_trace_setevent @TraceID, 45, 1, @on exec sp_trace_setevent @TraceID, 45, 17, @on exec sp_trace_setevent @TraceID, 45, 18, @on exec sp_trace_setevent @TraceID, 45, 34, @on exec sp_trace_setevent @TraceID, 45, 35, @on exec sp_trace_setevent @TraceID, 45, 51, @on exec sp_trace_setevent @TraceID, 45, 4, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 22, @on exec sp_trace_setevent @TraceID, 45, 15, @on -- XML Statistics Profile exec sp_trace_setevent @TraceID, 146, 1, @on exec sp_trace_setevent @TraceID, 146, 51, @on exec sp_trace_setevent @TraceID, 146, 4, @on exec sp_trace_setevent @TraceID, 146, 12, @on -- Filter: Log only events for the provided @@spid if @spId is not null   exec sp_trace_setfilter @TraceID, 12, 0, 0, @spID -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 goto finish error: select ErrorCode=@rc finish: end go

Some annotations:

• For the parameters, see the comments.
• Don’t specify a filename extension for the trace file. .TRC will be added automatically.
• Ensure that the output file does not already exist. Otherwise you’ll get an error.
• Very often I replace the code for starting and stopping the trace inside “interesting code” inside a stored procedure. That is, I’m wrapping some more or less awkward code by starting and stopping a trace like this:

declare @traceID int exec dbo.configureServerSideTrace @traceStatus = 1                                  ,@traceID = @traceID output                                  ,@maxFileSize = 10000                                  ,@traceFileName = N'e:\MyTrace\Test'                                  ,@spId = @@spid -- -- Code of interest -- exec dbo.configureServerSideTrace @traceStatus = 0, @traceID = @traceID

Cheers.

Calculating SQL Server Data Compression Savings

SQL Server 2008 Enterprise edition comes with an opportunity for storing table or index data in a compressed format which may save huge amount of storage space and – much more important – IO requests and buffer pool utilization. There’s two different options for data compression, namely Row and Page level compression. This blog post is not concerned with how these two work internally and will also not explain the differences between the two. If you like to know more about this, you find much of useful information on the internet - including links to further articles (e.g. here, here, and here).

Whether compression is worth or not isn’t an easy question to answer. One aspect that has to be taken into account is certainly the amount of storage that may be saved by storing a distinct table or index in any of the two compressed formats. SSMS offers a Data Compression Wizard than can provide storage-saving estimates for row or page level compression. From the context menu for a table or index just open Storage/Manage Compression… In the Combo box at the top select the compression type (Row or Page) and press the Calculate Button at the bottom. Here’s a sample of a calculated saving for an index:

Unfortunately, SSMS does not offer an option for calculating estimated savings for more than one table or index at once. If you, let’s say, would like to know the estimated storage savings of page level compression for you largest 10 tables, there’s no GUI support in SSMS that will assist you in finding an answer. This is, where the stored procedure sp_estimate_data_compression_savings comes in handy. This procedure – as you may have guessed from its name – provides estimated savings for row or page level compression for any table or index. You have to provide the table or index as a parameter to the procedure. In other words: The procedure will only calculate the estimations for one table or index at a time. If you want to retrieve the calculations of more than one table or index as a result set, there’s some more work to do, since the procedure has to be invoked multiple times. Here’s a script that calculates the estimated savings of page level compression for the database in context.

-- Determine the estimated impact of compression -- NOTE: This script is only for SQL Server Enterprise and Developer edition. set nocount on -- We create a temp table for the result if (object_id('tempdb..#comp', 'U') is not null)   drop table #comp go  create table #comp (   object_name sysname  ,schema_name sysname  ,index_id int  ,partition_number int  ,[size_with_current_compression_setting (KB)] bigint  ,[size_with_requested_compression_setting (KB)] bigint  ,[sample_size_with_current_compression_setting (KB)] bigint  ,[sample_size_with_requested_compression_setting (KB)] bigint ) go -- Calculate estimated impact of page level compression for all -- user-tables and indexes in all schemas. -- NOTE: --  1) To get the estimated impact of row level compression change the last parameter --     of sp_estimate_data_compression_savings to 'row' instead. --  2) We don't care about partitioning here. If this is important for you, --     you have to modify forth parameter of sp_estimate_data_compression_savings. --     Please refer to BOL. declare @cmd nvarchar(max) set @cmd = '' select @cmd = @cmd     +';insert #comp exec sp_estimate_data_compression_savings '''     + schema_name(schema_id)+''','''     + name + ''',null, null, ''page'''   from sys.tables  where objectproperty(object_id, 'IsUserTable') = 1 exec (@cmd) ; -- Do some further calculations for a more meaningful result with compressionSavings as (   select quotename(schema_name) + '.' + quotename(object_name) as table_name         ,index_id         ,[size_with_current_compression_setting (KB)]         ,[size_with_requested_compression_setting (KB)]         ,cast(case                 when [size_with_current_compression_setting (KB)] = 0 then 0                 else 100.0*(1.0-1.0                        *[size_with_requested_compression_setting (KB)]                        /[size_with_current_compression_setting (KB)])               end as decimal(6,2)) as [Estimated Savings (%)]   from #comp ) select cs.table_name        ,isnull(i.name, i.type_desc) as index_name        ,cs.[size_with_current_compression_setting (KB)]        ,cs.[size_with_requested_compression_setting (KB)]        ,cs.[Estimated Savings (%)]    from compressionSavings as cs         left outer join sys.indexes as i                      on i.index_id = cs.index_id                     and i.object_id = object_id(cs.table_name, 'U')   order by cs.[Estimated Savings (%)] desc -- Get rid of the temp table drop table #comp go

The script calculates the impact of Page level compression but may easily be adapted to consider Row level compression instead. Please read the comments inside the script. Also, please notice that the script will only run on SQL Server Enterprise and Developer edition. All other editions don’t provide the opportunity for data compression.

Here’s a partial result retrieved from running the script against the AdventureWorksDW2008R2 database.

If you execute the script, please be aware that it may produce some extensive I/O. Running the script against your production database at business hours wouldn’t be a very good idea therefore.

Exploring SQL Server Blockings and Timeouts

Last Thursday I was giving a presentation about information collection and evaluation of SQL Server Blockings and Timeouts at the regional PASS chapter meeting in Munich.

You may download the presentation as well as the corresponding scripts here (German only).

Backup and Restore of the SQL Server buffer pool

Have you ever missed this feature? Whenever you install the latest security update, it is very likely that your server requires a restart, leading also to a restart of SQL Server. If this ever happens, SQL Server needs to start from scratch, having nothing in the plan cache and also no data pages in the pool.I have many customers that complain about applications performing poorly after a server restart – for whatever reason that may have been necessary.

So finally, in the current project, we ended up developing a method for warming up the cache on our own after SQL Server has been started. Obviously not an easy task, but we had a smart guy who implemented this. The final solution is by no means perfect but almost sufficient according to our requirements.

When discussing about how we should implement this feature and also the pros and cons, we had the idea that we did nothing more than SQL Server itself should be capable of. Wouldn’t it be nice, if we could just perform a backup of the buffer pool and later restore it? Or even better, how about just sending SQL Server to hibernate mode (like Windows) and wake it up later? Clearly this won’t work under every circumstance, since some updates or patches may require a "cold" restart. But in those cases where it’s technically possible, I’d like to have this opportunity.

I like the idea so much that I’ve added a regarding change request on Microsoft’s connect platform (item# 561951). If you agree, you can vote for it here.

Do you rebuild your indexes periodically?

Most of you will – hopefully – have some index maintenance strategy. That is, you need some criteria, when to rebuild or reorganize an index. I will not repeat every advice that you may find elsewhere on the internet, e.g. here, or here.

In almost all given advices the dynamic management view sys.dm_db_index_physical_stats plays a central role, since it can easily be used to detect the degree of fragmentation of an index and the course of action to perform. Generally spoken, if index fragmentation exceeds a distinct value (let’s say 40%), you should perform a complete rebuild of the index. By doing so, the whole index tree is built from scratch and also the index related statistics is rebuilt. If the fragmentation is noticeable, but not too high (somewhere between 10% and 40%), an index reorganize may be sufficient. By performing a reorganize only the index pages in the leaf level are rearranged which is less cost (I/O) intensive than a complete index rebuild.

Unfortunately, sys.dm_db_index_physical_stats can create huge I/O stress, as you can check inside this blog post of Paul Randal (yes, the guru). There is, of course, an opportunity to affect the I/O load created, by specifying the last parameter of the sys.dm_db_index_physical_stats DMF. IF you use LIMITED here, the I/O load is as minimal as possible. DETAILED, on the other hand, may create very noticeable physical I/O, especially for larger indexes, although it should reveal very detailed and realistic.

But be aware that querying the fragmentation by the use of sys.dm_db_index_physical_stats, as also suggested in Books Online (see here), may not be sufficient to detect candidates for necessary rebuilds.

Look at the following example.

We start by creating a simple table and inserting 400,000 rows:

create table t1 (   c1 uniqueidentifier not null default newsequentialid() primary key  ,c2 nchar(513) not null default '#' ) go -- Insert 400,000 rows insert t1(c2)   select top 400000 '?' from sys.trace_event_bindings c1,sys.trace_event_bindings as c2 go

Now, let’s see the fragmentation of the primary key (which is also the clustered key in our case). Oh, btw.: I know very well that a clustered index on a GUID column is less than optimal, but I see this practice all the time. It’s just tat developers love the idea of creating objects (table rows) that are unique throughout the whole universe. But that’s not the point here, so let’s see how the fragmentation as well as the space used values look like. First, we use the LIMITED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'limited')

Here’s the result:

Looks pretty good, he? 0.7% fragmentation, so no need for any actions, right? (avg_space_used_in_percent is not shown in LIMITED mode.) Well, let’s see what sys.dm_db_index_physical_stats returns when running in DETAILED mode:

select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Here’s the result:

Also nothing to worry about, right? Almost no fragmentation. But wait! Why has the index tree a depth of 4? That seems too deep! So let’s see, if an index rebuild will shrink the depth:

alter index all on t1 rebuild go select index_level, avg_fragmentation_in_percent, avg_page_space_used_in_percent, ''   from sys.dm_db_index_physical_stats(db_id(),object_id('t1'),null,null,'detailed')

Now the index looks like that:

So, the index three has one level less than before! Therefore, every index seek has to perform one read less – that’s 25% less!

If you look at the avg_space_used_in_percent column, you see why.The non-leaf levels of the index are more packed with data and don’t contain as much space after the rebuild as before.

So be aware, when following the best practices guides in BOL or the internet. Those guidelines are definitely very good and helpful, but may not be sufficient under all circumstances.

Boost the performance of your SQL Server

Today I discovered a fascinating, although also weird thing regarding SQL Server Standard Edition. We all know that SQL Server has many of undocumented commands. DBCC makes no difference here. You probably know it as the DBA’s all purpose tool, but what I found by coincidence really puzzled me when I came across it this afternoon.

So here’s the story. A customer of mine runs some SQL Server Standard Editions which – as you may know – has some limitations in comparison to the Enterprise Edition. Those limitations also include some features of the optimizer, e.g. regarding the use of Bitmap Filters, or Indexed Views. Today, just for fun, I entered and ran the following DBCC command:

DBCC SQLPERF(ON)

And, believe it or not, all the limitations of the SQL Server Standard Edition went away instantly. It really behaves like it was the Enterprise Edition now. Amazing!

Of course, I can’t give any explanation what’s behind this. But hey, all that counts is, it finally works, right? So why not giving it a try? But be prepared that the command is undocumented and maybe not work as expected on all systems. But for me – it did! At least today…

Have fun!

Bad index usage within stored procedure

I came across this problem last week. Here’s a table containing contact data:

create table contact  (   last_name nvarchar(200)  ,city nvarchar(200)  ,filler nchar(200)  ) go insert contact (last_name,City)   select LastName, City     from AdventureWorks2008R2.Sales.vIndividualCustomer

Yes, I’m already using SQL Server 2008 R2 but I was able to reproduce the behavior described below also with SQL Server 2008. The script above will insert 18,508 rows into our table.

Let’s create a clustered index on the last_name column and a nonclustered index on the city column:

create clustered index ix_t1_last_name on contact(last_name) go create nonclustered index ix_t1_city on contact(city)

Given this table, what do you think about index utilization of the following ad hoc statement:

select *     from contact    where last_name like '%nobody%'      and city like 'nowhere%'

The SELECT statement returns 0 rows and the optimizer is able to find out this by using the nonclustered index on the city column. The execution plan proves it:

As the index contains two levels, two logical reads are necessary to obtain the empty result set.

Ok, now let’s create the following stored procedure:

create procedure getNames(@name nvarchar(50), @city nvarchar(50)) with recompile as   select *     from contact    where city like @city      and last_name like @name go

Inside the SP the same statement as before is executed. Also, the procedure is created with the RECOMPILE option, to ensure a fresh execution plan is generated for every execution of the SP. Let’s say, we like it to be this way, since we expect very different parameter values will be provided which in turn will create result sets of very different cardinality.

If we call the procedure like this:

exec getNames @name = '%nobody%', @city = 'nowhere%'

What will the execution plan look like? Inside the procedure the same statement is executed as the ad hoc statement above, so I’d expected to see the same execution plan as before.

But surprisingly, the execution plan reveals a clustered index seek this time which is far from optimal! Look at this:

Although the plan shows a Clustered Index Seek, this seek actually is a scan, because of the provided search argument “%nobody%”. (See here for more information.) Therefore, all leaf level pages of the clustered index have to be scanned, and this takes 1,065 reads. That’s about than 300 times more then it was with the ad hoc query!

I did some more experiments, where I left out the clustered index, e.g. and created both indexes nonclustered. Same result. If executing the procedure, always the suboptimal index is chosen, with one exception: If I create the clustered index on the city column, both queries will take advantage of the clustered index.

Strange. I wasn’t expecting this. Due to parameter sniffing, the procedure’s plan should be adjusted to the parameter values provided. This statement should be even more valid, since the procedure was created by specifying the RECOMPILE option. But parameter sniffing seems not to work in this case. As I don’t know why, and I also consider this behavior totally unexpected and kind of wrong, I’ve created a bug for this on MSFT’s connect platform. You can vote for it here.