capturing timeout queries

Question

capturing timeout queries

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
May 14, 2008 at 11:24 am

#182433

Hello!
I am trying to run profiler to capture the queries that time out. But how do I specify the events? Using 'SP:completed' ,'SQL:batch completed', 'SQL:stmt completed' wont do it because the timeout queries are not considered 'completed', right?
Thanks in advance for any insight!
Kathleen

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

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

the__t Hall of Fame Points: 3260 More actions · Answer 1

Just guessing here, but I think if you had query governor enabled, you could take the value used for query governor and filter the events you listed on the 'duration' column >= that value (I think you have to convert the value first to microseconds, as query governor setting is in seconds).

Again, just a guess

Edit:

Reading more on this, it looks like query governor cost limit applies to estimated time a query will take. I'm not sure that it kills an already executing query...

"Got no time for the jibba jabba!"
-B.A. Baracus

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 2

This blog entry from Ken Henderson might help answer that question.

http://blogs.msdn.com/khen1234/archive/2005/10/20/483015.aspx

To summarize, one of the indicators is the Attention event under Errors and Warning.

the__t Hall of Fame Points: 3260 More actions · Answer 3

Interesting and helpful... bookmarked.

"Got no time for the jibba jabba!"
-B.A. Baracus

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 4

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534

May 14, 2008 at 12:05 pm

#815463

Thank you!!

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 5

So I ran the profiler to capture 'attention' event, I saw a lot of it happening. The 'application name' is 'SQL Server', the Database is 'Master', so I figured out they are remote query calls, I found most of them are like this

DBCC SHOW_STATISTICS(N'"myDB"."dbo"."myTable"',"Index11") WITH HISTOGRAM_STEPS

I ran index defrag job last night thinking its index/statistics related. I am still seeing a lot of these. May I know how to fix it and also, why is it using 'Master' database?

Thanks!

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 6

I think that's normal with distributed queries to see DBCC SHOW_STATISTICS commands.

Might be that the timeouts are due to a less than optimal query plan caused by inaccurate statistics.

Have you tried manually updating the statistics on those tables?

Reference:

When were the statistics for a table last updated?

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 7

Thanks. That makes sense. My initial thought was these statistics commands that cause the timeout. I guess maybe not.

I ran 'EXEC sp_updatestats' on the database. I am going to run profiler to capture the remote queries coming Monday.