Integrated Security: Intermittent slow performing SP

Hello,
I have a stored proc that has started behaving strangely a few w

s
after upgrading to SQL Server 2005. This problem cannot be replicated
in the test environment.
The stored proc responds within 1 second in the Production environment
until it slows down (not sure why) and takes about 2 minutes to run
when called by the application. The same proc in production contines
to run in less than a second when called from SQL Server Query
Analyzer. When I run an Alter Proc and run the script for altering
the proc (without ANY change in it), response gets back to what it
used to be - <1 sec when called by the application. Does this have
something to do with the optimizer and query plan reuse? If yes - what
causes the sudden change - for SP performance to be great part of the
day and then degrade at some point (and that too only when called by
the app, when it works fine in Query Analyzer - and only this SP -
since the rest of the app works fine)?
Indexes are defragged every night, statistics are updated with full
scan every night.
I plan to investigate this further but thought to check first so I
needn't reinvent a wheel that already exists. Thanks much.This is not that easy to answer. You've already ruled out the obvious one
of stat's not up to date.
Things to check next are....
1. what about the inputs? do they vary wildly?
2. the content of the procedure. Does it have several different execution
paths?
Regards
Colin Dawson
www.cjdawson.com
<smithabreddy@.gmail.com> wrote in message
news:1146338367.532365.141660@.g10g2000cwb.googlegroups.com...
> Hello,
> I have a stored proc that has started behaving strangely a few w

s
> after upgrading to SQL Server 2005. This problem cannot be replicated
> in the test environment.
> The stored proc responds within 1 second in the Production environment
> until it slows down (not sure why) and takes about 2 minutes to run
> when called by the application. The same proc in production contines
> to run in less than a second when called from SQL Server Query
> Analyzer. When I run an Alter Proc and run the script for altering
> the proc (without ANY change in it), response gets back to what it
> used to be - <1 sec when called by the application. Does this have
> something to do with the optimizer and query plan reuse? If yes - what
> causes the sudden change - for SP performance to be great part of the
> day and then degrade at some point (and that too only when called by
> the app, when it works fine in Query Analyzer - and only this SP -
> since the rest of the app works fine)?
> Indexes are defragged every night, statistics are updated with full
> scan every night.
> I plan to investigate this further but thought to check first so I
> needn't reinvent a wheel that already exists. Thanks much.
>|||I will try sp_recompile the next time this happens and update this
post. Thank you for your prompt responses.|||(smithabreddy@.gmail.com) writes:
> I will try sp_recompile the next time this happens and update this
> post. Thank you for your prompt responses.
Also, compare query plan before and after.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Not sure what you mean by "Also, compare query plan before and after. "
I ran profiler while the problem was occuring and although it captured
stats and showplantext for some of the other things happening at that
time, I can't find that information for the SPID which was executintg
this SP.
--
All I could see for this SP:
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
select PROCEDURE_CATALOG...
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
exec [ExpesiteProduction]..sp_procedure_params_rowset
N'usp_proc',1,N'app',NULL
SET NO_BROWSETABLE ON
declare @.p1 int set @.p1=25 exec sp_prepare @.p1 output,N'@.P1 int
OUTPUT,@.P2 int,@.P3 varchar(25),@.P4 bit',N' EXEC @.P1=app.usp_proc
@.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4 ',1 select @.p1
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
if @.ModifyType = "create"
EXEC @.P1=app.usp_proc @.PersonID=@.P2,@.ModifyType=@.P3,@.isPrivate
=@.P4
SET FMTONLY ON exec sp_execute 25,0,0,' ',0 SET FMTONLY OFF
set fmtonly off
exec sp_unprepare 25
SET NO_BROWSETABLE OFF
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
SET NOCOUNT ON
CREATE TABLE #tmpPTID (ProjectTemplateID int)
if @.ModifyType = "create"
NULL
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
exec app.usp_proc @.PersonID=454,@.ModifyType='create',@.isPr
ivate=1
Also when I ran the SP from QA today, I ran DBCC FreeProccache and ran
it as exec app.usp_proc. Also ran it using the verbage captured via
Profiler (again after DBCC FreeProccache):
exec sp_prepare...
EXEC app.usp_proc...
They generated exactly the same stats and execution plan...|||NNTP-Posting-Host: gw-uu-virtual.levonline.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: black.telenor.se 1146509970 22605 217.70.32.2 (1 May 2006 18:59:30
GMT)
X-Complaints-To: postmaster@.telenordia.se
NNTP-Posting-Date: Mon, 1 May 2006 18:59:30 +0000 (UTC)
User-Agent: Xnews/2006.03.07 Mime-proxy/2.1.c.0 (Win32)
Path: TK2MSFTNGP01.phx.gbl!TK2MSFTFEEDS01.phx.gbl!newsfeed00.sul.t-online.de
!t-online.de!130.59.10.21.MISMATCH!kanaga.switch.ch!switch.ch!newsfeed2.fune
t.fi!newsfeed1.funet.fi!newsfeeds.funet.fi!newsfeed1.telenor.se!news.telenor
.se!not-for-mail
Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:600229
(smithabreddy@.gmail.com) writes:
> Not sure what you mean by "Also, compare query plan before and after. "
I meant that you should compare the query plan when the procedure appears
to be slow, and after you have used sp_recompile or whatever to force a
new plan.

> I ran profiler while the problem was occuring and although it captured
> stats and showplantext for some of the other things happening at that
> time, I can't find that information for the SPID which was executintg
> this SP.
Did you filter on this spid? If you include too many events, the
Profiler will have to throw events away. (You can see this as a special
event in Profiler.)
But I hoped it would be easier to see this from QA - or it is not.
QA runs with SET ARITHABORT ON by default, but this is not the case
for an ASP client, and you get different plans because of this setting.
So capturing plans in Profiler may be better after all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP slowed down again. I ran sp_recompile for the SP (did not have
a profiler template set up yet and it was too much of a fire-fighting
situation to do that right away) and this fixed performance
immediately. The page refreshed almost instantaneously instead of
taking the 2-4 minutes it was taking just before running sp_recompile.
So what is the solution for such a stored proc? Run Sp_Recompile
against it every so often?
There are two queries in this SP. The first one is an Insert into a
temp table (and this statement gets recompiled every time the SP is
run) and the second is a SELECT.
Should I
1) add a recompile hint to this SELECT query
2) set the SP to recompile everytime
3) run a job with sp_recompile <storedProc> every half hour
I realize I will need to test whatever solution...but would one of the
above be inherently better than the others or am I missing something
else that can be done? I suspect we have several such procs in the
database which may also need some help before too long.
P.S. I have created a Profiler template to capture events related to a
specific object ID - I will use this to perform a short capture the
next time I see this latency. This should help me figure out what
causes the bottleneck and if it can be eliminated.|||(smithabreddy@.gmail.com) writes:
> There are two queries in this SP. The first one is an Insert into a
> temp table (and this statement gets recompiled every time the SP is
> run) and the second is a SELECT.
Do you have any idea of what amount of data that goes into the temp table?
Maybe you get more consistent performance with a table variable, as table
variables do not cause recompilations?
Or would it be possible to integrate the query that fills the temp table
into the final SELECT?

> Should I
> 1) add a recompile hint to this SELECT query
> 2) set the SP to recompile everytime
> 3) run a job with sp_recompile <storedProc> every half hour
Of these I would actually lean towards the latter. Compilation can be costly
too. Then again, a recompilation hint on the SELECT query is the prettiest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The SP I mentioned above has been behaving well the last couple of days
- haven't had to recompile or do anything so I'll let it be until I
need to touch it again.
I have ANOTHER SP (:\) which is giving me some problems. In the PROD
database running this PROC with one set of parameters causes it finish
executing in under 1 second. Another set of parameters causes the
query to take close to 6 minutes to execute.
With the problem set of parameters, if I run against:
- Prod Server:
PROD database: 6 minutes
- Development Server:
DB1: 0 seconds
DB2: 6 Minutes
DB3 (Copy of PROD from yesterday): 0 Seconds
All of the databases have the same data. They have the same defrag,
update usage and update stats job run on them. On the DEV server, I've
run DBCC FreeProccache and recompiled the proc before running it in
each database.
--
I've narrowed down the latency to one section of the SP. This set of
parameters causes this one section of the SP to run and this section
consists of:
IF @.maxrows>0
SET ROWCOUNT @.maxrows
INSERT INTO...SELECT
.
.
.
SET ROWCOUNT 0
--
This runs fine in DB1 and DB3 but not in PROD and DB2. The execution
plan is different in for this query and two more nested loops have been
added in the inefficient query plan, plus a Table Spool/Lazy Spool step
for which rowcount = 1.6 million.
If I remove the SET Rowcount - the SP executes within 1 second.
Replacing SET Rowcount with TOP also causes the query to take between 5
and 6 minutes.
Why the different execution plans for the same set of parameters? They
are different copies of the same database.
What causes the Table Spool/Lazy Spool to come in to play?
(Adding a recompile/force order query hint does not make a change.)

Wednesday, March 28, 2012

Intermittent slow performing SP

No comments:

Post a Comment

Integrated Security

Blog Archive

About Me