I am running SQL Profiler against a simple stored procedure and am recording
RPC: Completed to get the duration of the procedure. This is working fine
but I need a little help with what this really means because I am seeing
variance anywhere between 13ms to 6000ms. I am assuming that the duration
measures the time period starting when the server receives the query -
through execution - and ultimately to formulating the output...but NOT
actual delivery of the output. I guess what I am trying to determine is if
a slow network link will alter this result or if the variance I am seeing is
most likely caused by an internal resource on the SQL Server
(CPU/Disk/Memory/Locking etc.). Can I eliminate the network from being a
problem? Can I eliminate the client machine sending the requests as being
the problem? (it is always the same client btw).
Any help would be appreciated.
Thanks
The duration is from start to finish meaning until the last row is delivered. If this was network related I would expect to see a very high value in the waitstats for NetworkIO waits. You can use DBCC SQLPERF(WAITSTATS) to get a snap shot of how many milliseconds in wait times you have for each wait. You can clear it with DBCC SQLPERF(WAITSTATS, CLEAR). But I would put my bet on blocking as the culprit. What does the CPU & Reads columns say for a sp that completed in 13ms vs. 6000ms? If they are roughly the same you can pretty much bet you are being blocked can can confim this with sp_who2 or sp_lock. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Jim Norton" <joe@joe.com> wrote in message
news:OBv6hHbtGHA.4140@TK2MSFTNGP03.phx.gbl...
>I am running SQL Profiler against a simple stored procedure and am
>recording RPC: Completed to get the duration of the procedure. This is
>working fine but I need a little help with what this really means because I
>am seeing variance anywhere between 13ms to 6000ms. I am assuming that the
>duration measures the time period starting when the server receives the
>query - through execution - and ultimately to formulating the output...but
>NOT actual delivery of the output. I guess what I am trying to determine
>is if a slow network link will alter this result or if the variance I am
>seeing is most likely caused by an internal resource on the SQL Server
>(CPU/Disk/Memory/Locking etc.). Can I eliminate the network from being a
>problem? Can I eliminate the client machine sending the requests as being
>the problem? (it is always the same client btw).
>
> Any help would be appreciated.
>
> Thanks
>
[quoted text, click to view] On Tue, 1 Aug 2006 16:31:40 -0500, "Jim Norton" <joe@joe.com> wrote:
>I am running SQL Profiler against a simple stored procedure and am recording
>RPC: Completed to get the duration of the procedure. This is working fine
>but I need a little help with what this really means because I am seeing
>variance anywhere between 13ms to 6000ms.
As others have said, so the basic question is whether the time varies according to the parameters in the SQL, the same SP or SQL can have very different execution plans depending on values specified. J.
This is only true if the SP happens to recompile due to some other factor - as differing param values (even wildly different values) alone don't cause a SP to recompile.. Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:83q1d252ha9l08l6mqk0q5ahk63l8eronh@4ax.com...
> On Tue, 1 Aug 2006 16:31:40 -0500, "Jim Norton" <joe@joe.com> wrote:
>>I am running SQL Profiler against a simple stored procedure and am
>>recording
>>RPC: Completed to get the duration of the procedure. This is working fine
>>but I need a little help with what this really means because I am seeing
>>variance anywhere between 13ms to 6000ms.
>
> As others have said, so the basic question is whether the time varies
> according to the parameters in the SQL, the same SP or SQL can have
> very different execution plans depending on values specified.
>
> J.
>
>
Hi Jim Just adding to what Andy's already said - another useful Profiler measure is CPU, which tells you how much time the query spends on the CPU within SQL Server (as opposed to Duration, which involves returning the resultset). CPU also doesn't provide the full picture though, as blocked processes don't report much CPU time. But it can give you a feel for how much actual execution time with SQL Server a query takes up. The fact that you've said this is always the same client might be important if you expect a lot of data to be transfered back to clients from this proc. If you ARE expecting lots of data to be sent back to the client, network issues could be involved & Wireshark (previously Ethereal) is a great way to confirm / deny this (as it provides network protocol level stats). If you AREN'T expecting lots of data to be sent back, this is less likely & my money's with Andys - that you could well have a blocking issue. Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "Jim Norton" <joe@joe.com> wrote in message
news:OBv6hHbtGHA.4140@TK2MSFTNGP03.phx.gbl...
>I am running SQL Profiler against a simple stored procedure and am
>recording RPC: Completed to get the duration of the procedure. This is
>working fine but I need a little help with what this really means because I
>am seeing variance anywhere between 13ms to 6000ms. I am assuming that the
>duration measures the time period starting when the server receives the
>query - through execution - and ultimately to formulating the output...but
>NOT actual delivery of the output. I guess what I am trying to determine
>is if a slow network link will alter this result or if the variance I am
>seeing is most likely caused by an internal resource on the SQL Server
>(CPU/Disk/Memory/Locking etc.). Can I eliminate the network from being a
>problem? Can I eliminate the client machine sending the requests as being
>the problem? (it is always the same client btw).
>
> Any help would be appreciated.
>
> Thanks
>
On Thu, 3 Aug 2006 08:42:01 +1000, "Greg Linwood" [quoted text, click to view] <g_linwood@hotmail.com> wrote:
>This is only true if the SP happens to recompile due to some other factor -
>as differing param values (even wildly different values) alone don't cause a
>SP to recompile..
Not true, it might have compiled several plans previously and keeps them in cache, and just choses between them by parameter sniffing. J. [quoted text, click to view] >
>Regards,
>Greg Linwood
>SQL Server MVP
>
>"JXStern" <JXSternChangeX2R@gte.net> wrote in message
>news:83q1d252ha9l08l6mqk0q5ahk63l8eronh@4ax.com...
>> On Tue, 1 Aug 2006 16:31:40 -0500, "Jim Norton" <joe@joe.com> wrote:
>>>I am running SQL Profiler against a simple stored procedure and am
>>>recording
>>>RPC: Completed to get the duration of the procedure. This is working fine
>>>but I need a little help with what this really means because I am seeing
>>>variance anywhere between 13ms to 6000ms.
>>
>> As others have said, so the basic question is whether the time varies
>> according to the parameters in the SQL, the same SP or SQL can have
>> very different execution plans depending on values specified.
>>
>> J.
>>
>>
>
This isn't true - multiple plans are NOT stored in the proc cache for different parameters at all. Multiple plans ARE stored for different user connection settings though.. Regards, Greg Linwood SQL Server MVP [quoted text, click to view] "JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:fcv4d2d2lejplfb4mk90dq839qp0mlnkcu@4ax.com...
> On Thu, 3 Aug 2006 08:42:01 +1000, "Greg Linwood"
> <g_linwood@hotmail.com> wrote:
>>This is only true if the SP happens to recompile due to some other
>>factor -
>>as differing param values (even wildly different values) alone don't cause
>>a
>>SP to recompile..
>
> Not true, it might have compiled several plans previously and keeps
> them in cache, and just choses between them by parameter sniffing.
>
> J.
>
>
>>
>>Regards,
>>Greg Linwood
>>SQL Server MVP
>>
>>"JXStern" <JXSternChangeX2R@gte.net> wrote in message
>>news:83q1d252ha9l08l6mqk0q5ahk63l8eronh@4ax.com...
>>> On Tue, 1 Aug 2006 16:31:40 -0500, "Jim Norton" <joe@joe.com> wrote:
>>>>I am running SQL Profiler against a simple stored procedure and am
>>>>recording
>>>>RPC: Completed to get the duration of the procedure. This is working
>>>>fine
>>>>but I need a little help with what this really means because I am seeing
>>>>variance anywhere between 13ms to 6000ms.
>>>
>>> As others have said, so the basic question is whether the time varies
>>> according to the parameters in the SQL, the same SP or SQL can have
>>> very different execution plans depending on values specified.
>>>
>>> J.
>>>
>>>
>>
>
Greg is correct here. If the query was properly parsed and had valid parameters (or was auto parameterized) the values them selves would not be a factor. At least not in how many plans there were. Just what plan it may use depending on the values passed in at compile time. Parameters themselves do not cause recompiles. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Greg Linwood" <g_linwood@hotmail.com> wrote in message
news:eblvLb1tGHA.476@TK2MSFTNGP06.phx.gbl...
> This isn't true - multiple plans are NOT stored in the proc cache for
> different parameters at all. Multiple plans ARE stored for different user
> connection settings though..
>
> Regards,
> Greg Linwood
> SQL Server MVP
>
> "JXStern" <JXSternChangeX2R@gte.net> wrote in message
> news:fcv4d2d2lejplfb4mk90dq839qp0mlnkcu@4ax.com...
>> On Thu, 3 Aug 2006 08:42:01 +1000, "Greg Linwood"
>> <g_linwood@hotmail.com> wrote:
>>>This is only true if the SP happens to recompile due to some other
>>>factor -
>>>as differing param values (even wildly different values) alone don't
>>>cause a
>>>SP to recompile..
>>
>> Not true, it might have compiled several plans previously and keeps
>> them in cache, and just choses between them by parameter sniffing.
>>
>> J.
>>
>>
>>>
>>>Regards,
>>>Greg Linwood
>>>SQL Server MVP
>>>
>>>"JXStern" <JXSternChangeX2R@gte.net> wrote in message
>>>news:83q1d252ha9l08l6mqk0q5ahk63l8eronh@4ax.com...
>>>> On Tue, 1 Aug 2006 16:31:40 -0500, "Jim Norton" <joe@joe.com> wrote:
>>>>>I am running SQL Profiler against a simple stored procedure and am
>>>>>recording
>>>>>RPC: Completed to get the duration of the procedure. This is working
>>>>>fine
>>>>>but I need a little help with what this really means because I am
>>>>>seeing
>>>>>variance anywhere between 13ms to 6000ms.
>>>>
>>>> As others have said, so the basic question is whether the time varies
>>>> according to the parameters in the SQL, the same SP or SQL can have
>>>> very different execution plans depending on values specified.
>>>>
>>>> J.
>>>>
>>>>
>>>
>>
>
>
On Fri, 4 Aug 2006 00:37:19 -0400, "Andrew J. Kelly" [quoted text, click to view] <sqlmvpnooospam@shadhawk.com> wrote:
>Greg is correct here. If the query was properly parsed and had valid
>parameters (or was auto parameterized) the values them selves would not be a
>factor. At least not in how many plans there were. Just what plan it may use
>depending on the values passed in at compile time. Parameters themselves do
>not cause recompiles.
And an explicit sp_recompile first clears the cache, so that won't do it either? How about recompiles that take place due to, say, #temp tables? J.
If a plan gets recompiled it will create a new one that replaces the old one given that everything is the same, not counting the values passed in. It is certainly true that you can get a different type of plan (meaning scan vs seek etc.) depending on the values passed in when the query is first run or first run after it becomes invalidated. But if the only thing that changes is the value of the parameters you will get a plan signature that is always the same. So you should never have two different plans in cache with the same signature at any given time. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:pes6d29ph3l606nbhep17qes28350i0cqr@4ax.com...
> On Fri, 4 Aug 2006 00:37:19 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@shadhawk.com> wrote:
>
>>Greg is correct here. If the query was properly parsed and had valid
>>parameters (or was auto parameterized) the values them selves would not be
>>a
>>factor. At least not in how many plans there were. Just what plan it may
>>use
>>depending on the values passed in at compile time. Parameters themselves
>>do
>>not cause recompiles.
>
> And an explicit sp_recompile first clears the cache, so that won't do
> it either?
>
> How about recompiles that take place due to, say, #temp tables?
>
> J.
>
On Fri, 4 Aug 2006 16:57:50 -0400, "Andrew J. Kelly" [quoted text, click to view] <sqlmvpnooospam@shadhawk.com> wrote:
>If a plan gets recompiled it will create a new one that replaces the old one
>given that everything is the same, not counting the values passed in. It is
>certainly true that you can get a different type of plan (meaning scan vs
>seek etc.) depending on the values passed in when the query is first run or
>first run after it becomes invalidated. But if the only thing that changes
>is the value of the parameters you will get a plan signature that is always
>the same. So you should never have two different plans in cache with the
>same signature at any given time.
Well then, I'm confused. If "parameter sniffing" is going to do something, doesn't it need a couple of different plans to choose from? What are the scenarios that this works for? I know that one of the boogeymen in SQLServer operations is an SP that uses a plan optimized for one value and given another, but I thought that the value (or at least the statistics slot the value corresponds to somewhere downstream in the code) was part of the signature. Josh
The parameters are not part of the 'signature'. You only need one plan in cache for parameter sniffing to be a problem. I must admit, I had been hearing the term 'parameter sniffing' being tossed about for quite a while before I found out what it meant, and to me, it doesn't really seem like an appropriate term for the behavior it is applied to. Parameter sniffing is when SQL Server 'sniffs' the values coming on when a plan is first compiled, and then continues to use that same plan for subsequent executions, even if that plan is not a good plan for the new values being passed in. -- HTH Kalen Delaney, SQL Server MVP [quoted text, click to view] "JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:8ql9d2p7k2r8fkdlgp904ur2ga2cav1f95@4ax.com...
> On Fri, 4 Aug 2006 16:57:50 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@shadhawk.com> wrote:
>>If a plan gets recompiled it will create a new one that replaces the old
>>one
>>given that everything is the same, not counting the values passed in. It
>>is
>>certainly true that you can get a different type of plan (meaning scan vs
>>seek etc.) depending on the values passed in when the query is first run
>>or
>>first run after it becomes invalidated. But if the only thing that changes
>>is the value of the parameters you will get a plan signature that is
>>always
>>the same. So you should never have two different plans in cache with the
>>same signature at any given time.
>
> Well then, I'm confused.
>
> If "parameter sniffing" is going to do something, doesn't it need a
> couple of different plans to choose from? What are the scenarios that
> this works for?
>
> I know that one of the boogeymen in SQLServer operations is an SP that
> uses a plan optimized for one value and given another, but I thought
> that the value (or at least the statistics slot the value corresponds
> to somewhere downstream in the code) was part of the signature.
>
> Josh
>
[quoted text, click to view] > I must admit, I had been hearing the term 'parameter sniffing' being tossed
> about for quite a while before I found out what it meant, and to me, it
> doesn't really seem like an appropriate term for the behavior it is applied
> to.
Kalen, How would you prefer to call it?
In class, I usually just describe the problem as inappropriate re-use of plans. I haven't come up with some short catch-phrase yet. :-) -- HTH Kalen Delaney, SQL Server MVP [quoted text, click to view] "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1154808889.397499.240070@75g2000cwc.googlegroups.com...
>> I must admit, I had been hearing the term 'parameter sniffing' being
>> tossed
>> about for quite a while before I found out what it meant, and to me, it
>> doesn't really seem like an appropriate term for the behavior it is
>> applied
>> to.
>
> Kalen,
>
> How would you prefer to call it?
>
On Sat, 5 Aug 2006 13:39:08 -0700, "Kalen Delaney" [quoted text, click to view] <replies@public_newsgroups.com> wrote:
>In class, I usually just describe the problem as inappropriate re-use of
>plans.
>I haven't come up with some short catch-phrase yet. :-)
So, one sniff and it's history? Well then. So, what if you have an app that feeds in dynamic SQL, like select * from mytable where notpk = 'abc' select * from mytable where notpk = 'xyz' If 'abc' and 'xyz' fall into very different stat slots, could that produce two different plans in cache? Or only a single plan, possibly inappropriate for one value? But the full text is different then, so does that constitute two signatures? But then it wouldn't be 'sniffing' as such. Guess I could try it and see. If the 'sniffing' is that limited, hmm, have to reevaluate what was going on in some past situations I worked on. Thanks all. Josh ps - so, what about in SQL2005, where the unit of recompile is just the statement, not the SP? Can't even formulate the questions for that yet, but if it only keeps one plan for any unit, then, um, something something.
No, one sniff and that's what it continues to use until the plan is removed from cache. Dynamic SQL is not the same as stored procedures. In your example, if the two values have a potential of leading to completely different plans, then two plan will be stored and these will be considered 'ad-hoc queries'. There are no parameters and thus nothing to sniff. Take a look at this whitepaper: Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx -- HTH Kalen Delaney, SQL Server MVP [quoted text, click to view] "JXStern" <JXSternChangeX2R@gte.net> wrote in message
news:hv3ad2d4clcvts403osl0ja5i9deg8su1d@4ax.com...
> On Sat, 5 Aug 2006 13:39:08 -0700, "Kalen Delaney"
> <replies@public_newsgroups.com> wrote:
>>In class, I usually just describe the problem as inappropriate re-use of
>>plans.
>>I haven't come up with some short catch-phrase yet. :-)
>
> So, one sniff and it's history? Well then.
>
> So, what if you have an app that feeds in dynamic SQL, like
>
> select * from mytable where notpk = 'abc'
> select * from mytable where notpk = 'xyz'
>
> If 'abc' and 'xyz' fall into very different stat slots, could that
> produce two different plans in cache? Or only a single plan, possibly
> inappropriate for one value? But the full text is different then, so
> does that constitute two signatures? But then it wouldn't be
> 'sniffing' as such. Guess I could try it and see.
>
> If the 'sniffing' is that limited, hmm, have to reevaluate what was
> going on in some past situations I worked on.
>
> Thanks all.
>
> Josh
>
> ps - so, what about in SQL2005, where the unit of recompile is just
> the statement, not the SP? Can't even formulate the questions for
> that yet, but if it only keeps one plan for any unit, then, um,
> something something.
>
On Sat, 5 Aug 2006 15:38:26 -0700, "Kalen Delaney" [quoted text, click to view] <replies@public_newsgroups.com> wrote: >No, one sniff and that's what it continues to use until the plan is removed >from cache. > >Dynamic SQL is not the same as stored procedures. In your example, if the >two values have a potential of leading to completely different plans, then >two plan will be stored and these will be considered 'ad-hoc queries'. There >are no parameters and thus nothing to sniff. > >Take a look at this whitepaper: > >Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 > http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx I'd read the paper, but managed to read it with the *assumption* (yeah, I know) that it *meant* the plans were reused and that the values, or the stats slot of the value, *were* part of the signature, or at least the autoparameterization, or something like it, was very common, and a number of similar plans differing only by the value would be cached. Looking at it again now, knowing this is *not* the case, well, "that's very different!". And again, thinking how this is supposed to work in SQL2005, where the unit of recompilation is smaller, and/or where you can lock the plan (I forget exactly the new terminology there), well, I'll have to spend some time on it, I guess. Josh
Don't see what you're looking for? Try a search.
|
