How to figure out why this is happening

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to figure out why this is happening

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-02 : 04:41:46

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

52326 Posts

Posted - 2013-05-02 : 05:03:04

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-02 : 05:21:15

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-02 : 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
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 06:09:57

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-02 : 06:16:30

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 07:06:06

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-02 : 07:22:33

i am running profiler - how can I see if there is a lock

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 07:42:16

http://msdn.microsoft.com/en-IN/library/ms190216.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-05-02 : 13:19:10

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 02:59:09

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-03 : 03:43:57

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-03 : 04:19:25

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-03 : 04:40:54

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-03 : 07:41:15

exec sp_readrequest @receive_timeout=600000

is this an sql function?
what does it do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources