March 1, 2011 at 10:42 am
My sql code runs fine when i run on the database but it takes more than expected time when it is accessed through the application. Are there any set of counters with threshold values i can monitor to identify if network/webserver is an issue?
March 1, 2011 at 10:58 am
How much of data is it returning? Check the network bandwidth in the Task Manager. Also Is any App Server being used? How is the traffic / response on the Middle Layer? If the problem is with the end client check how the client machine is performing. I have had scenarios where some anti-virus software starts scanning during peak time of the day and the end user is not aware of it.
March 1, 2011 at 11:08 am
is your code a stored procedure which runs quickly when static values are tested in SSMS, but slow when it is called with parameters from the application?
That's a sign of parameter sniffing, you can search for more info on SSC to address it;
Lowell
March 1, 2011 at 12:21 pm
Lowell (3/1/2011)
is your code a stored procedure which runs quickly when static values are tested in SSMS, but slow when it is called with parameters from the application?That's a sign of parameter sniffing, you can search for more info on SSC to address it;
Something similar yeah , but before even i get there i need to see if the n/w bandwidth is an issue . So how do i measure? Or should i directly look for parameter sniffing?
March 1, 2011 at 12:39 pm
definitely parameter sniffing first; it is very doubtful your network is bogging down just for one app, unless it is returning a MillionBillion rows. this is one of the first things i'd look for in a performance issue....
1. output of data is massively oversized or not?
2. parameter sniffing?
3.Out of date statistics?
4. Good indexing/Execution plan?
5. SARG-able arguments for the WHERE statements?
6. Obvious codeing errors like inadvertant Cartesian Joins /CROSS Joins?
Lowell
March 1, 2011 at 2:11 pm
Just to add to Lowell's list , different SET options can cause different query plans to be created.
But 9 times out of 10 ( or even more) this sort of issue is parameter sniffing
March 2, 2011 at 10:08 pm
Dave Ballantyne (3/1/2011)
Just to add to Lowell's list , different SET options can cause different query plans to be created.But 9 times out of 10 ( or even more) this sort of issue is parameter sniffing
I am using the following set options in my proc:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
Can these be the issue? How do i identify if these are the/one of the cause?
March 4, 2011 at 10:30 am
Often overlooked is grabbing more data than needed (SELECT * FROM). Had someone trying to figure this one out for hours and it took about 5 minutes to realize there was a BLOB attached, pumping gigs through the network when all that was needed was a few K of data
March 4, 2011 at 11:19 am
We are selecting only few columns. Do the above SET options could be an issue?
March 4, 2011 at 11:24 am
definitely my first instinct, the parameter sniffing issue, is what i would check first. what you are describing is a classic symptom.
does your stored procedure have default values for any parameters?
an execution plan gets created when the procedure is created,as everyone probably knows.
When the parameter values are not defaulted, SQL Server uses the statistics on the columns being compared to the parameters for how granular the column's values are for uniqueness, and creates a plan based on that info.
When the parameters are defaulted to NULL (or any static value, technically)
the SQL engine makes an assumption that since the default values are provided, the best execution plan should assume the default values are best, and builds a plan without peeking at the statistics at all.
The problem is that when you call the proc with real parameters, the cached plan with those NULL defaults is not suitable to get the data, and SQL goes off on a tangent trying to get the data; my assumption is that the plan ends up using techniques that are ideal for tiny result sets, and the technique is extremely inefficient when it has to go through lots of pages of data to really find the data it was looking for. , so big MillionRowTables get the same section of the plan million times, hence the huge time difference...whether that assumption is really true or not, the results are the same... it takes too long.
search for parameter sniffing to learn more, but the two general fixes are:
use the WITH RECOMPILE option so the proc recompiles each time it is called... or assign local variables to the actual values being passed to the procedure.
Lowell
March 4, 2011 at 11:26 am
Please post the execution plans as per this article http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
It will take a lot of the guesswork out
March 4, 2011 at 1:11 pm
Lowell (3/4/2011)
definitely my first instinct, the parameter sniffing issue, is what i would check first. what you are describing is a classic symptom.does your stored procedure have default values for any parameters?
an execution plan gets created when the procedure is created,as everyone probably knows.
When the parameter values are not defaulted, SQL Server uses the statistics on the columns being compared to the parameters for how granular the column's values are for uniqueness, and creates a plan based on that info.
When the parameters are defaulted to NULL (or any static value, technically)
the SQL engine makes an assumption that since the default values are provided, the best execution plan should assume the default values are best, and builds a plan without peeking at the statistics at all.
The problem is that when you call the proc with real parameters, the cached plan with those NULL defaults is not suitable to get the data, and SQL goes off on a tangent trying to get the data; my assumption is that the plan ends up using techniques that are ideal for tiny result sets, and the technique is extremely inefficient when it has to go through lots of pages of data to really find the data it was looking for. , so big MillionRowTables get the same section of the plan million times, hence the huge time difference...whether that assumption is really true or not, the results are the same... it takes too long.
search for parameter sniffing to learn more, but the two general fixes are:
use the WITH RECOMPILE option so the proc recompiles each time it is called... or assign local variables to the actual values being passed to the procedure.
Thanks. I have BIG concern using recompile option because procedure takes little more than a minute to compile the proc and since then 12 secs. If i use recompile option it will recompile every time it is being used. I think i might be a victim of parameter sniffing but one of the solutions which you mentioned (using recompile) is not good for me.Any thoughts?
March 4, 2011 at 1:16 pm
the other option isusing locally declared variables. then the compiler cannot use parameter sniffing;
something like
CREATE PROCEDURE AVOIDSNIFFING(@Param1 int=0,@Param2 datetime = NULL)
AS
DECLARE @localParam1 int,
@localParam2 datetime
SET @localParam1 = @Param1;
SET @localParam2 = @Param2;
SELECT * FROM MyTable WHERE Col1 = @localParam1 AND Col2 = @localParam2
Lowell
March 4, 2011 at 1:44 pm
sqldba_icon (3/4/2011)
Thanks. I have BIG concern using recompile option because procedure takes little more than a minute to compile the proc and since then 12 secs. If i use recompile option it will recompile every time it is being used. I think i might be a victim of parameter sniffing but one of the solutions which you mentioned (using recompile) is not good for me.Any thoughts?
There are other ways then the two that were presented, but swapping to local variables might be your best bet, the rest get more complex.
You can do parameter hints (OPTIMIZE FOR), force specific query plans (USE PLAN), and a few other things.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 8, 2011 at 2:38 pm
Lowell (3/4/2011)
the other option isusing locally declared variables. then the compiler cannot use parameter sniffing;something like
CREATE PROCEDURE AVOIDSNIFFING(@Param1 int=0,@Param2 datetime = NULL)
AS
DECLARE @localParam1 int,
@localParam2 datetime
SET @localParam1 = @Param1;
SET @localParam2 = @Param2;
SELECT * FROM MyTable WHERE Col1 = @localParam1 AND Col2 = @localParam2
Thanks.. How can i findout if i am victim of paramter sniffing?Any trace counter to capture?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply