Traces

Question

Traces

ps.

One Orange Chip

Points: 29422
More actions
November 12, 2010 at 9:30 am

#231543

Is there a way to capture those sql statements which didn't complete may be due to time out or cancellation?

Pradeep Singh

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

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

Don Stevic SSC Enthusiast Points: 108 More actions · Answer 1

In you SQL Trace, enable RPC: Starting, SP:Starting, or SP:StatementStarting and you will see where they began. If you add the :Ending columns, you will see that they did not end. I do not recall if there is a TimeOut event. Be warned about SP:StatementStarting however. If you have heavy function usage, capturing this event can cause more harm than good

ps. One Orange Chip Points: 29422 More actions · Answer 2

That's what i was thinking but it doesn't look so simple to me.

for every statement start i need to find matching statement end. if i dont find one, it means it was cancelled or timed out...

Pradeep Singh

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I'm guessing you are supporting 3rd pary apps which you can't access or modify the logging systems therein?

ps. One Orange Chip Points: 29422 More actions · Answer 4

Yes i am supporting 3rd party apps. I was trying to figure out a way to track such queries at database level which don't complete because of timeouts. The application has a time out of 8 minutes. queries which take more time are timed out.

Pradeep Singh

Don Stevic SSC Enthusiast Points: 108 More actions · Answer 5

Are you dealing with stored procedures or ad hoc queries?

ps. One Orange Chip Points: 29422 More actions · Answer 6

I would say a mix of both. mostly ad-hoc (hard-coded in the application).

Pradeep Singh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Trace for errors. I think what you want is the attention event, possibly coupled with sp:starting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Don Stevic SSC Enthusiast Points: 108 More actions · Answer 8

Ugh. SQL Code hard coded in the application? Do they never learn. A solution I have used in the past, which is kind of bulky, but it works is this.

Run a server side trace into a CSV, and makes sure the start and end events are tagged. Make sure you are grabbing the textdata field in your trace, as well. Import the file into a table and run a query against it sorting by textdata, spid and starttime.

You can automate this using SSIS.

ps. One Orange Chip Points: 29422 More actions · Answer 9

Thanks Gail for this one. And thanks once more for writing those 2 articles on analyzing performance on simple talk. I have used it several times in the past. 🙂

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 10

Don Stevic (11/12/2010)
Ugh. SQL Code hard coded in the application? Do they never learn. A solution I have used in the past, which is kind of bulky, but it works is this.
Run a server side trace into a CSV, and makes sure the start and end events are tagged. Make sure you are grabbing the textdata field in your trace, as well. Import the file into a table and run a query against it sorting by textdata, spid and starttime.
You can automate this using SSIS.

I have already done this for past 2 days but couldn't find timed out queries. I'll do it again on tuesday. They face this issue between 4 am -7 AM EST.

Pradeep Singh

Don Stevic SSC Enthusiast Points: 108 More actions · Answer 11

If you are trying to track ad hoc code in you application, you are going to need to track batch started and ended. That will let you see the ad hoc coming in and out. If you sort the data by start/end time and spid, you can see with ones start and don't end.

Do you know what code is timing out? You might be able to search the textdata for the code specifically.

Also, if the queries are timing out betweeen 4-7AM, are these queries that only run at that time of day, or do they run all the time and just time out at that time.

Have you looked to see what other maitnenance is going on at the time?