Deepak Dhakal's Blog: Parameter Sniffing and Reporting service through Visual studio

Deepak Dhakal's Blog: Parameter Sniffing and Reporting service through Visual studio

Parameter Sniffing and Reporting service through Visual studio

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 ..so lesson of today: There are lot of thing u dont know about which is realy important