Stored procedure timeout
in [Programming SQL]
|
From: Tony Rogerson on 16 Jun 2010 12:22 There will be different ANSI settings or language or something else between QA and the web app hence they will be different plans. If you really want to work out where the problem is happening you need to use sql profiler like Bob suggests to track to the statement causing all the time, you can then add OPTION ( RECOMPILE ) to that specific statement - so long as you are using the latest CU and service packs - what version you on? Tony "Jose Nadim" <josenadim(a)gmail.com> wrote in message news:adb65256-cc82-44f2-9cbd-6c1b5cae2b8b(a)z8g2000yqz.googlegroups.com... > Hi Bob, on profiler : > 1. QA ok 10 seconds > 2. Web page asp 2 minutes > > And i put WITH RECOMPILE on my second stored procedure and it > works !! > but i dont understand the executions time from client application : > QA its OK and web pages is bad > the time should be same on both client applications,because the sotred > procedure is on the server. > > thanks > > Jose Nadim > > > On 16 jun, 09:49, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote: >> Start by using SQL Profiler to isolate the slow sql statement. You will >> need the SP:StmtCompleted event at a minimum. >> >> >> >> >> >> Jose Nadim wrote: >> > Hi Tony !, >> > I will read about of parameter sniffing; and i forget how is create >> > my sp : (exec to 25 sotred procedures) >> > ALTER PROCEDURE [dbo].[Proc_Consultas] >> > -- Add the parameters for the stored procedure here >> > @fecha1 as datetime, >> > @fecha2 as datetime, >> > @codisec as char(2), >> > @codiest as char(2), >> > @coditra as char(2), >> > @codipat as char(3), >> > @funcion as char(12), >> > @combo as char(1), >> > @markmue as char(1) >> > AS >> > BEGIN >> >> > SET NOCOUNT ON; >> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED >> > if @funcion = 'DOCCONSA' >> > EXEC PROC_DOCCONSA @fecha1,@fecha2,@coditra,@combo >> > ELSE if @funcion = 'DOCCONSRECA' >> > EXEC PROCDOCCONSRECA @fecha1,@fecha2,@combo >> > ELSE if @funcion = 'DOCCONSINCA' >> > EXEC PROCDOCCONSINCA @fecha1,@fecha2,@combo >> >> > ELSE if @funcion = 'DOCCONSA1' >> > EXEC PROCDOCCONSA1 @fecha1,@fecha2,@coditra,@combo >> > ELSE if @funcion = 'DOCCONSBA' >> > EXEC PROCDOCCONBA @fecha1,@fecha2 >> > ELSE if @funcion = 'DOCCONSAU' >> > EXEC PROCDOCCONSAU @fecha1,@fecha2 >> > etc,etc,etc >> > END >> >> > thanks >> >> > On 15 jun, 23:38, "Tony Rogerson" <tonyroger...(a)torver.net> wrote: >> >> Hi Jose, >> >> >> It will probably be a bad plan in procedure cache. >> >> >> add "WITH RECOMPILE" to the stored procedure definition... >> >> >> create proc blah >> >> with recompile >> >> as >> >> >> Does that fix the problem? >> >> >> If that fixes the problem then the original problem was caused by >> >> "parameter sniffing" and an original call to the stored procedure >> >> with a given value skewing the data causing a plan that is now being >> >> reused to be created - a plan that is grossly inefficient for the >> >> second query you are running. >> >> >> Tony. >> >> >> "Jose Nadim" <josena...(a)gmail.com> wrote in message >> >> news:00855529-3158-4bc0-8d60-89db707d8154(a)y11g2000yqm.googlegroups.com... >> >> >> >> >>> Hello,i have a stored procedure that is called from an ASP.net page >> >>> and it takes >> >>> about 2 minutes to execute. When I run it from QA it takes about 10 >> >>> seconds. the procedure which is not recompiled and it is in >> >>> procedure cache. >> >>> Thanks for your help. >> >>> Jose Nadim- Ocultar texto de la cita - >> >> >> - Mostrar texto de la cita - >> >> -- >> HTH, >> Bob Barrows- Ocultar texto de la cita - >> >> - Mostrar texto de la cita - >
From: Erland Sommarskog on 17 Jun 2010 07:12 Jose Nadim (josenadim(a)gmail.com) writes: > Hi Bob, on profiler : > 1. QA ok 10 seconds > 2. Web page asp 2 minutes > > And i put WITH RECOMPILE on my second stored procedure and it > works !! > but i dont understand the executions time from client application : > QA its OK and web pages is bad > the time should be same on both client applications,because the sotred > procedure is on the server. Typically QA and the application will use different cache entries, because QA by default runs with SET ARITHABORT ON, whereas an in application ARITHABORT by default is OFF. The root problem is a parameter sniffing issue, but it certainly very confusing to see these different execution times. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Jose Nadim on 17 Jun 2010 15:33 Hi all ,thanks for your comments ; i will change the sotred procedure to avoid parameter sniffing and i will be watching for. thanks again for your help. Jose Nadim On 17 jun, 06:12, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Jose Nadim (josena...(a)gmail.com) writes: > > Hi Bob, on profiler : > > 1. QA ok 10 seconds > > 2. Web page asp 2 minutes > > > And i put WITH RECOMPILE on my second stored procedure and it > > works !! > > but i dont understand the executions time from client application : > > QA its OK and web pages is bad > > the time should be same on both client applications,because the sotred > > procedure is on the server. > > Typically QA and the application will use different cache entries, > because QA by default runs with SET ARITHABORT ON, whereas an in application > ARITHABORT by default is OFF. > > The root problem is a parameter sniffing issue, but it certainly very > confusing to see these different execution times. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 17 Jun 2010 17:39 Jose Nadim (josenadim(a)gmail.com) writes: > Hi all ,thanks for your comments ; i will change the sotred procedure > to avoid parameter sniffing and i will be watching for. Note that parameter sniffing is not an evil in itself. Without parameter sniffing, SQL Server would certainly perform a lot worse. But, yes, there are situation when it backfires. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
First
|
Prev
|
Pages: 1 2 Prev: for xml auto and synonyms Next: Is any group function which concatenates data? |