NO Recompilations when (parameter) values are different

Please read another post on this subject by Dinesh Priyankara.
http://dinesql.blogspot.com/2010/10/do-we-need-to-recompile-stored.html

This is another topic came in the last user group meeting: 
Does SQL Server generate a new plan (in other word recompile) the query if the parameter value is changed?
The actual answer is it depends:
First of all, SQL Server does not go through the recompilation process simply because the parameter is changed.
As I mentioned in the previous blog, there are only certain conditions where an existing execution plan is thrown away and a new plan is used.

Even though in my example, a new plan is created between these two statements, the value is not the cause.

DBCC FREEPROCCACHE

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1

GO

SELECT * FROM Sales.SalesOrderDetail

WHERE SalesOrderDetailID <= 1000

GO

When I ran the queries and captures the SQL:Recompile event, nothing was captured. Interestingly, ShowPlan XML for Query Compilation too, didn't give any number as the reason for recompilation. For me it looked like SQL Server didn't consider them as same query.
I removed the GO statement in between and tried too. The result was same. (except the column line number showed 1 and 2 instead of 1 always.
The image is attached.

Fortunately, SQL Server provides a few DMVs (dynamic management views) which allows us to check the execution plans

Recompilations after index creation

Yesterday I got an interesting question: Does adding an index to a table force the execution plans to be recreated?
Books online says this:

 

Please note the first, second and fourth bulleted points.

It says either a structure should be changed or index used by the query should be changed. In other words not any change in the index will not force recompile.

Looking For More Security Vulnerabilities in Database Code

A few days back I read a blog from Kimberly L. Tripp, one of the great teachers on SQL.Server. She opened the question on ways to identify vulnerable code written inside stored procedures. Even though she came up with dynamic SQL and execution context the discussion went on one direction only on dynamic SQL.

She proposed two main areas for search:

  CASE

      WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' THEN 'WARNING: code contains EXEC'

      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' THEN 'WARNING: code contains EXECUTE'


When discussion proceeded two major additions came up: One on handling white spaces and commented lines and the other on sp_ExecuteSQL

Does that mean if we take care of the above vulnerabilities our code is secure? Unfortunately, we have many other possibilities to look into.

In fact, when I saw Kimerly’s blog, I thought of writing some additional vulnerabilities there as a response, but a few factors prevented me: 1. My answer seems to be slightly larger for a response. 2. The original post was made months ago. So, here I have the things I found.
I suggest you all to read her blog and the responses.
Looking for security vulnerabilities in database code

Well SQL Server 2000 prior to sp3 had an undocumented extended stored procedure called xp_ExecResultset which allowed the SQL statement to be constructed using another query. In later service pack this was replaced with a TSQL stored procedure. With sp3, another procedure named sp_ExecResultSet too was added (this did nothing more than calling the previous procedure).

Even though the extended procedure was removed, it is not impossible to use the same dll used for xp_ExecResultset in SQL Server 2000 to create a new extended procedure.

Of course no one can take the same dll and attach to SQL Server 2005 due to architectural changes, but it does not mean that no one can attach a dll and call dynamic SQL. Actually calling dynamic SQL is simpler than before due to the introduction of CLR integration. That means someone can write code that simply runs ad-hoc query (or similar) statement based on the parameters passed.

In SQL Server 2005, there is a new stored procedure named sp_sqlExec which accepts SQL statement/variable as parameter and simply executes it.

Additionally there is another undocumented procedure sp_execute which accepts @handle (int) as a parameter. This refers to a cached plan in memory prepared by sp_prepare (another undocumented extended procedure) That means someone could call sp_execute and pass the handle of a dynamic statement (which would have been executed before too).

Interested in finding more security vulnerabilities? I see a lot of other places like xp_cmdshell, sqlcmd, jobs (which can run ActiveX & WMI scripts, OS commands and TSQL statements) and alerts (where you have a chance to run powershell script too.

So, be wise and do a security audit on the system.

Share this post :