SQL Traces mixing SQLServer2005 - 2008

Today was expiriencing some problem opening a Trace File:

Unable to locate trace definition file Microsoft SQL Server TraceDefinition 10.0.0.xml for open trace file... It took me more than 10 min to figure out that the server I was profiling (Using SQLServer 2005 profiler) was a SQLServer 2008. :-p

So you can trace a SQLServer 2008 using a SQLServer 2005 Profiler, but you cannot re-open the same traces using a SQLServer 2005 profiler, you have to use a SQLServer 2008 Profiler instead!

Or if you don't have the 2008 tools installed yet on your locale machine, you can use:

select *
from fn_trace_gettable('\\share\...\TestTrc.trc',default)

It works fine on both SQLServer 2005 and SQLServer2008 SSMS to connect to a SQLServer2005 or SQLServer2008 trace.

Fatal error 8646 occurred when performing an "ALTER PARTITION FUNCTION"

Today I was just doing some tests to create a partitioned table with a sliding window on an existing table.

So I created my partition function and partition scheme, and did a drop index with move to my partition scheme on the existing table. Everithing (selects, inserts,...) worked well, until I wanted to Alter my Partition function to do an extra RANGE SPLIT. Than I did get a Fatal Error 8646... error message. Fortunately, I remembered some problems with indexes and replication from the past who gave the same "8646" error message. And when looking at the table, I saw there was also an existing non-clustered index. After I dropped the non-clustered index, the problem was solved.

I tried to reproduce the same error with a smaller table, but didn't get any "succes".

SQLServer Parameter sniffing

We had an issue in two of our stored procedures recently with query execution plan caching. After digging deep in the code base we found what was causing the problem.

One of the great features in SQL Server is its optimizing query processor. There are, however, times when some of the advanced features of the optimizer can cause problems. Sometimes it simply helps too much. One of those is when it automatically uses the parameter values passed into a stored procedure to tailor the execution plan it will construct for a query. This is called "parameter sniffing" and is normally a good thing. It allows the optimizer to take into account the actual values of stored procedure parameters as it builds plans to service queries within the procedure that make use of them. IOW, if you pass a value of "test" for a parameter named @param, then use @param in the WHERE clause of a query within the procedure, the optimizer is smart enough to use "test" when it estimates how many rows will match the WHERE clause as it compiles a plan for the query.
While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is suboptimal (or even downright slow) when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance.
SQL Server 2005 has some features to help combat this, but there are no built-in features for dealing with it in prior releases. There are three simple tricks that you can use to solve this problem:

- the use of "decoy variables”. This means that we filter the query on a local variable value; witch replaces the parameter that we actually pass into the procedure.

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN
Decalre @lclVarParam int

SET @lclVarParam = @param

SELECT x, y
FROM MyTable
WHERE z = @lclVarParam
END

GO

- Filter the query on a dummy parameter, i.e. @template_param, that we never actually pass into the procedure. We always pass in the filter value we want to use via @param and let @template_param retain its default. The optimizer notices that we’re filtering the query against the sales table using @template_param and sniffs its value (which will always be the default value at compilation time) when creating the query plan for it.

CREATE PROCEDURE usp_avoidSniff2(@param int, @param_def int= ‘My optim val’)
AS

BEGIN

SET @param_def = @param


SELECT x, y
FROM MyTable
WHERE z = @param_def
END

GO

- Use the OPTIMIZE FOR statement (SQLServer 2005). Adding this to a query makes sure the plan is optimized for the value you specify. And when all else fails you can even force a particular query plan with the new USE PLAN statement

CREATE PROCEDURE usp_avoidSniff1(@param int)
AS

BEGIN

SELECT x, y
FROM MyTable
WHERE z = @param OPTION(OPTIMIZE FOR (@IDate = Null))

END

GO