Some time Stored Procedure suddenly taking 10 seconds when we called through application.

Question

Some time Stored Procedure suddenly taking 10 seconds when we called through application.

patelchandresh330 80387

Mr or Mrs. 500

Points: 512
More actions
August 22, 2014 at 3:07 am

#289365

Sometime following stored procedure suddenly taking 10 seconds when we called through application.
CREATE PROCEDURE TestStatus
AS
BEGIN
SELECT1 AS [StatusCount]
END
At that time memry pressure and cpu pressure is not so high. And our production environment is on VMWare.
can you please guide me why it takes 10 seconds.
Thanks in advance..

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

patelchandresh330 80387 (8/22/2014)
Sometime following stored procedure suddenly taking 10 seconds when we called through application.
CREATE PROCEDURE TestStatus
AS
BEGIN
SELECT1 AS [StatusCount]
END
At that time memry pressure and cpu pressure is not so high. And our production environment is on VMWare.
can you please guide me why it takes 10 seconds.
Thanks in advance..

I suspect that isn't your actual procedure code?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

patelchandresh330 80387 (8/22/2014)
Sometime following stored procedure suddenly taking 10 seconds when we called through application.
CREATE PROCEDURE TestStatus
AS
BEGIN
SELECT1 AS [StatusCount]
END
At that time memry pressure and cpu pressure is not so high. And our production environment is on VMWare.
can you please guide me why it takes 10 seconds.
Thanks in advance..

If that's your actual code, then there are a thousand reasons.

If your code is a bit more sophisticated and takes parameters, then possibly parameter sniffing of initial caching.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Neil Burton SSC-Insane Points: 22908 More actions · Answer 3

I also noticed that the OP doesn't mention the normal time it takes to run. It's possible that 10 seconds could actually be an improvement. Things inexplicably running better can be as puzzling as them slowing down.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 4

Jeff Moden,

This is the actual code, we used this stored procedure to check connection to the database and this is also taking 10 seconds sometimes. and also other SP which have business logic taking 10 seconds..

so from where we start to troublshooting..

WorkasDBA SSCommitted Points: 1684 More actions · Answer 5

Please check if there are network issues. Try running the same query directly on the server & retrieve the time

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

patelchandresh330 80387 (8/22/2014)
Jeff Moden,
This is the actual code, we used this stored procedure to check connection to the database and this is also taking 10 seconds sometimes. and also other SP which have business logic taking 10 seconds..
so from where we start to troublshooting..

My apologies for the late reply.

The first step, if you already haven't done so, is to log into the server itself, fire up SSMS, and execute that proc. If it takes longer than 0 ms the second time you run it, then something is seriously wrong with the server.

Next stepSET STATISTICS TIME,IO ON;

EXEC dbo.TestStatus;

SET STATISTICS TIME,IO OFF;

The next step (after logging off the server), would be to do the same thing from a local desktop looking for not only the same results, but also checking how long it takes from the time you punch the execute button until you get a return. If it's a lot slower, it could be your machine or something on the network. Try the same thing from a couple of machines. If there's no appreciable delay, it could be the app/webserver or the network connection from those to the server or it could be those other machines are IO saturated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

patelchandresh330 80387 Mr or Mrs. 500 Points: 512 More actions · Answer 7

Jeff Moden,

we replaced #table to @table in stored procedure.. it works for 2 days. and suddenly it started to taking more then 10 seconds.

if we execute from Java application then only it behave like this.. in "SQL Performance Dashboard" shows top 20 CPU Pressure.. whithin that showing me one of the sql statement of same SP.

but if i excecutes it from SQL Server 2012 SSMS it takes 0 to 1 seconds only.

Please guide me..

Thanks in Advanced

//

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 8

You don't have #table or @table in your original post so it clearly isn't the whole procedure. Please post the full definition of the stored procedure and the definition of #table/@table.