script timeout... sometimes

Question

script timeout... sometimes

the_fenix

Right there with Babe

Points: 789
More actions
April 13, 2009 at 8:41 pm

#129303

Hi guys!
I have a stored procedure that is causing a timeout "sometimes" on a live site. Executing the procedure manually takes no more than 5 seconds normally. Running a trace over the database I realized that the time vary very much from one execution of the stored to another. I'm suppossing that there is a task that consume a lot of resources and when executed at the same time that the stored cause the timeout. This is my best guess.
The question is: Which is the best approach for detecting this high resource consuming task? Profiler? If the profiler is the best... could you help me with parameter configuration for this trace?
I'm using SQL SERVER 2005 Enterprise Edition.
Thanks in advance!

Viewing 11 posts - 1 through 10 (of 10 total)

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

BJ Cummings-783681 SSC Enthusiast Points: 101 More actions · Answer 1

I would suggest starting with the tuning template provided in profiler. It will contain the basic information that you will need to begin identifying poorly performing code. I would also suggest running this as a server side trace instead of through the profiler GUI since the server side trace is much lighter weight. If you need instructions on how to do this just let me know.

You may also take a look at some of the DMV's. The following are some helpful links to some good performance related scripts:

http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx?mfr=true

Thanks.

BJC

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I'd recommend looking at the execution plan for the stored procedure and tuning it first. I'd also verify the statistics are up to date for the tables included in the stored procedure. Then I would look at a server-side trace for blocking issues.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

the_fenix Right there with Babe Points: 789 More actions · Answer 3

Hi! I was trying to execute a server side trace but It just doesn't create the trace file.

I followed this tutorial:

http://www.mssqltips.com/tip.asp?tip=1035

I get no error but the physical file wasn't created.

Can anyone help me out to create this server trace? It would be something like the tunning template of the profiler.

Thanks!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

What do you get when you run this query:

SELECT

*

FROM

sys.traces

WHERE

is_default = 0

The status column should be 1 if it is running. If you get no results then you did have an error creating the trace.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

the_fenix Right there with Babe Points: 789 More actions · Answer 5

The status is 1 but I see no file on thi path 'D:\TestTrace3.trc'

Is this OK?

Thanks!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

What is in start-time, last_event_time, and event_count? I would venture to say that the file is not created until an event is created.

Are you looking in the D: drive of the server or your local PC?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

the_fenix Right there with Babe Points: 789 More actions · Answer 7

Sorry, my fault.

I was trying to insert results to a table like this

SELECT * INTO #temp

FROM ::fn_trace_gettable(N'D:\TestTrace3', DEFAULT)

But an extension is added (.trc) so I have to do it like this

SELECT * INTO #temp

FROM ::fn_trace_gettable(N'D:\TestTrace3.trc', DEFAULT)

Thanks guys!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Why are you inserting into a temp table?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

the_fenix Right there with Babe Points: 789 More actions · Answer 9

the_fenix

Right there with Babe

Points: 789

April 15, 2009 at 11:35 am

#978278

for quering the data. Ther is any other way?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

I'm not sure what you mean by querying. You can apply a where clause and join to the function. Like this:

SELECT

*

FROM

sys.fn_trace_gettable(PATH, Default) AS FTG

WHERE

FTG.TextData LIKE '%[proc_name]%'

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question