SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to figure out why this is happening
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/02/2013 :  04:41:46  Show Profile  Reply with Quote
i have a very active website that has a separate web server and sql server.

We have some posts that must run in under 2 seconds and I'm getting errors in the iis error log that they are timing out.
when I run the stored procedure (or even post) manually it's running in less then a second -How can I figure out what is happening?

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  05:03:04  Show Profile  Reply with Quote
how is the values being send to db? is it by means of row by row or by means of set baset statement? also try to use stored procedure over adhoc queries as former can cache plan and resuse it resulting in much better performance.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/02/2013 :  05:21:15  Show Profile  Reply with Quote
it's sent to a stored procedure

if i run the stored procedure itself then It works then less then a second but we are having many timeouts from our web server

How can I debug where the issue is?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  05:30:38  Show Profile  Reply with Quote
are the set of parameters passed in both cases same?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/02/2013 :  05:52:03  Show Profile  Reply with Quote
yes but it's not always happening
some are going through and some that do the same request are timing out

when i check the weblog it does say a mssql timeout error

the sql server is very powerful --so I don't know where the issue is
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  06:09:57  Show Profile  Reply with Quote
quote:
Originally posted by esthera

yes but it's not always happening
some are going through and some that do the same request are timing out

when i check the weblog it does say a mssql timeout error

the sql server is very powerful --so I don't know where the issue is


are there multiple concurrent executions of stored procedure happening? Can you check for any possible locking issues?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/02/2013 :  06:16:30  Show Profile  Reply with Quote
there can be multiple calls to the same sp at the same time - is that what you mean?

how can I check for locking problems or know if this is the issue
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  07:06:06  Show Profile  Reply with Quote
you can either use profiler for that
or use sp_lock

http://msdn.microsoft.com/en-us/library/ms187749.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/02/2013 :  07:22:33  Show Profile  Reply with Quote
i am running profiler - how can I see if there is a lock
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/02/2013 :  07:42:16  Show Profile  Reply with Quote
http://msdn.microsoft.com/en-IN/library/ms190216.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
790 Posts

Posted - 05/02/2013 :  13:19:10  Show Profile  Reply with Quote
This could also be a parameter sniffing issue - where the plan being generated is good for some sets of requests, but horrible for others.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/03/2013 :  02:59:09  Show Profile  Reply with Quote
quote:
Originally posted by jeffw8713

This could also be a parameter sniffing issue - where the plan being generated is good for some sets of requests, but horrible for others.


i dont think it can be parameter sniffing as OP confirmed this

quote:

esthera Posted - 05/02/2013 : 05:52:03
--------------------------------------------------------------------------------
yes but it's not always happening
some are going through and some that do the same request are timing out

when i check the weblog it does say a mssql timeout error

the sql server is very powerful --so I don't know where the issue is
visakh16 Posted - 05/02/2013 : 05:30:38
--------------------------------------------------------------------------------
are the set of parameters passed in both cases same?





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/03/2013 :  03:43:57  Show Profile  Reply with Quote
thanks after running profiler and analyzing the results I see 2 stored procedures with a lot of duration
1 i know what it is and it does a lot but when I run it it's taking less then a second though obviously sometimes it's taking longer - i'm not sure why

the other is sp_readrequest -- i'm not sure what this is
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/03/2013 :  04:19:25  Show Profile  Reply with Quote
quote:
Originally posted by esthera

thanks after running profiler and analyzing the results I see 2 stored procedures with a lot of duration
1 i know what it is and it does a lot but when I run it it's taking less then a second though obviously sometimes it's taking longer - i'm not sure why

the other is sp_readrequest -- i'm not sure what this is


Are you running it in the same environment and same database/schema with the same set of parameter values when you notice this change in duration of execution?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/03/2013 :  04:40:54  Show Profile  Reply with Quote
Are you running it in the same environment and same database/schema with the same set of parameter values when you notice this change in duration of execution?


yes

could it be with how many requests hit at the same second
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1388 Posts

Posted - 05/03/2013 :  07:41:15  Show Profile  Reply with Quote
exec sp_readrequest @receive_timeout=600000

is this an sql function?
what does it do?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000