Disabling SQL Server Caching Through .NET Code

Question

I am currently invoking several stored procedures in some .NET code, SqlConnection . I would like to disable caching performed by SQL Server in order to periodically measure performance (I am going to compare it with another server, which probably also will not have cached data). Can this be done without changing sprocs?

This is the code I'm currently using:

 using (SqlConnection connection = new SqlConnection(/* connection string goes here */)) { SqlCommand command = new SqlCommand(procName, connection); command.Parameters.AddRange(parameters); command.CommandType = System.Data.CommandType.StoredProcedure; connection.Open(); SqlDataReader r = command.ExecuteReader(); // todo: read data here r.Close(); connection.Close(); } 

Answer 1

First, by "caching" here, I assume that you are referencing the execution plan cache. As soon as SQL Server determines the best execution order for your statements, it saves it for a while. This problem is commonly known as the "snaping parameter." This is what you clear when you start dbcc freeproccache . Unfortunately, this is a privileged command and it affects all connections.

The root of the problem is that your SQL probably works differently with a different set of parameters. SQL Server will only store the execution plan for the first run that it sees, and its associated parameters. Therefore, if the arguments at the first execution are suitable for the usual case, your application will work fine. But from time to time, the wrong arguments will be used on the first run, and your entire application may work poorly.

There are several ways to optimize your SQL statement to reduce the impact of this, but this cannot be completely avoided.

• Generate SQL dynamically. You take a performance hit when generating a query plan at each execution, but it can be useful if using the wrong execution plan causes your query to never return. I suggest this path, although it is more cumbersome. I found SET STATISTICS TIME ON and SQL Profiler useful for reducing the time it takes to create a plan. The greatest improvement was achieved by using the 3-part name ( owner.schema.table ) for tables.
• Indicate a "good set" of input parameters for your query with query hints.

 SELECT Col1, Col2 FROM dbo.MySchema.MyTab WHERE Col1=@Parameter OPTION (OPTIMIZE FOR (@Parameter='value')); 

This link describes the sniffing problem parameter pretty well. This was a big problem in SQL 2005. Later versions of SQL did a better job of this.