Skip to main content

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

Comments

Anonymous said…
Hi Deepak - I have a project I would like to talk to you about
August 10, 2009 at 9:48 AM
Post a Comment

Popular posts from this blog

MS interview

Algorithm Collection Q1: How would you find a cycle in a linked list? Try to do it in O(n) time. Try it using constant amount of memory. Q2: Given a history of URLs, how would you determine if a particular URL had been seen before? Q3: Since pages can have multiple URLs pointing to them, how can you make sure you've never seen the same CONTENT before? Q4: Come up with the plan on how to traverse a graph, as well as to quickly determine if a given URL is one of the million or so you've previously seen. Q5: The Web can be modeled as a directed graph. Come up with a graph traversal algorithm. Make the algorithm non-recursive and breadth-first. Q6: Write a function to print the Fibonacci numbers Q7: Write a function to print all of the permutations of a string. Q8: Design a memory management scheme. Q9: Give a one-line C expression to test whether a number is a power of 2. Now implement it without using loops. Q10: How can I swap two integers in a single l...
111 comments
Read more

Amazon interview question Collection

General Questions and Comments [more] generic questions on software development -------------------------------------------------------------------------------- What is the complexity of this algorithm -------------------------------------------------------------------------------- What is your stronger language (Java or C#) -------------------------------------------------------------------------------- Lot of design questions and OOAD stuff 2 -------------------------------------------------------------------------------- Programming questions asked were more or less same as the ones listed at this site -------------------------------------------------------------------------------- more architectural view about solve problem capability. I think the intervier was more realistic than the other two . Not just because he recommend to 2nd interview, since I also have the experience with recuriting other employees in the past. I felt the potenial is more than anything in work. Coding is j...
184 comments
Read more

Few Software Development Knowledge

 What are the benefits of OOD? o Object oriented design facilitates “Reusability”, “Extensibility” and “Modularity” of the software. We can divide the software into many components/classes so that “Abstraction” can be done on the changing part and non changing part to support the “changeability”. Plus Inheritance, Composition and polymorphism will always help in implementation.  Explain how the Garbage Collector works in C# o The GC of the C# (.Net rather) is a cool component in .NET framework. Being a C++ programmer, I truly understand the importance of it. I don’t need to worry about allocating /freeing the memory once I use it in .net. In c#, all the object memory management is done in “Managed heap. GC collects the unused memory based on metadata information which gives the memory layout of the created object. It performs the collection in two phases. Mark -> GC first finds the root of each object and traverse to the bottom of it adding each object and make a graph of it. It...
1 comment
Read more