Parameter Sniffing and Reporting service through Visual studio

by Deepak Dhakal 16. June 2009 11:20

Today I had a very bad day. My friend left me a job that he estimated for 2 hours and Now I need to finish that ..There is no chance that i can write multiple pages sql Stored procedures /query and create report in 2 hours ..

It is going to take me at least 15 hours for everything .. Ok that is not the point .. the point .. I spent 5 hours to write all the query .. tested in Sql query manager and ran great ... Thought I was really good .. created a report layout .. added few dataset and ran the report using VS 2005 IDE .. Now the drama begins .. It took 5-10 mins to render report by VS 2005. I went to sql server machine.. checked for task manager .. CPU uses was more than 50% for that 10 mins time .WOW .. When I ran the same query in query manager .. it took less than 5 seconds .. I was upset .. restarted VS .. restarted computer .. nothing happened .. recompiled code .. redeployed .. Nothing ..Nothing at all.. I googled about it .. I am not only one.. If u have same problem ..

Google about "Parameter Sniffing " ..U will see many article about it .. In short the solution was: Suppose I have :

create proc newProc { @name varchar(200) }

as select name from nametable where name=@name

.. go

everytime u call it from outside with even NULL value or some value not equal to varchar(200) SQl server tried to create a brand new execution plan ..

and it takes forever to run the script if u have pretty complex sql..

Sol Very simple .

create proc newProc

{ @name varchar(200) }

@declare @newName varchar(200)

set @newname=@name as

select name from nametable where name=@newname .. go

done !! SQL just ignores to create execution plan and runs the last used plan ..

Ok lesson of today:

There are lot of thing u dont know about .


Powered by BlogEngine.NET - Old School Theme by n3o Web Designers