Stephen Goodman's Blog

We've been looking through some slow executing SQL today and came across what at first seemed like a strange occurrence whereby if we ran the contents of the stored proc in Management Studio it executed in a second, but when the stored proc was run it took over 4 minutes.

We looked at the execution plans for each and they appeared to be different, after doing some searching on the internet we came across the term for our particular issue "parameter sniffing" - 

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation.

It seems that when a stored procedure is first executed SQL (cleverly) stores an execution plan based on the parameters and then all subsequent calls to the stored proc use exactly the same execution plan regardless of the supplied parameters (in our case this was true).

The problems then occur when different parameters are supplied which require different execution plans. When running dynamic SQL, SQL Server makes up the plan on the fly this can be time consuming but it uses the latest stats it has available, whereas the stored proc uses the original plan which is faster but can be potentially wrong.

In order to speed the stored procedure execution we tried adding "with recompile" before the AS command in the stored procedure however this seemed to make no difference and the system continued to use the original execution plan.
Our second approach was to change the way the parameters were used in the stored proc, for example a simplified version of our original stored proc was:

create procedure MyProc(@date datetime)

as begin

    select * from myTable where date<@date

end

We then changed the proc to

create procedure MyProc(@date datetime)

as begin

    declare @localDate datetime

     Set @localdate = @date

    select * from myTable where date<@localDate

end

Once we used a local parameter rather than the supplied parameter in the query, we found the stored proc ran at the same speed as the dynamic SQL.

So in conclusion if you use a lot of varying values in your stored proc, or over time the execution speed slows right down then I suggest reading up on Parameter Sniffing and maybe implementing our approach of using local variables.

The boys (and gals) at Microsoft have finally released SP 3 for SQL 2005, finally no more installing SP2 and hotfix X (think they got up to 10) to prevent the distribution database growing in size after installing SP2.

You can find out more about the release at http://blogs.msdn.com/sqlreleaseservices/archive/2008/12/16/sql-server-2005-sp3-released.aspx but please remember never to install directly onto live servers without reviewing the new features and testing heavily.

I've now become an IPhone convict, and I was a bit sceptical about paying for applications to run on the iphone after forking out all the money for the phone in the first place, however I've become a convert with 2 new apps that I have.

Skype for iPhone - I've been looking around for several weeks for a decent way of being able to use skype with my iPhone, and was only finding suggestions of using Fring, but the poor reviews and the hassle of creating more accounts put me off. Then i stumbled across s4iphone which works by using an ajax web page, so there is a zero client foot print. The iphone simple connects to a webpage via Wifi or 3G and then you type   It seems to poll for new messages every 10 seconds, but posts your response immediately in the tests I've performed here in the office. The product also supports voice calls, however they fake it by using your credit to call you and then linking you into a call to the other person over the net. The only downside I can see to this website is that you have to give them your skype username and password and it costs $20 after the free 10 day trial.

Roku Remote - This is a fantastic application for controlling my Roku Soundbridge which is a media streamer attached to my hifi. This application can be downloaded from the app store for a very reasonable cost of £1.78. It then allows your iphone via wifi (or over 3g if your Soundbridge is accessible via the net) to fully control your Soundbridge using the touch display of the iphone. The Roku Forums have a lot more information on this application and the some great screen shots.

Let me know if you have found any other good answers to using Skype on your iphone.

Whilst code reviewing some SQL today I came across the syntax:

select sum(amount) from reward_expired (nolock)

I was interested to find out what the difference was between using the table hint (nolock) and the table hint with (nolock).

Looking in MSDN I found this entry under SQL 2005,

"In SQL Server 2005, with some exceptions, table hints are supported in the FROM clause only when the hints are specified with the WITH keyword. Table hints also must be specified with parentheses.

The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND. When these table hints are specified without the WITH keyword, the hints should be specified alone."

I then looked at SQL 2008 and found that Micorosft plan to remove the ability to use just (nolock), and to future proof all SQL, users should write all table hints using the with keyword.

"Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

So in future I will avoid the lazy approach of missing out the with keyword.