July 29, 2008 at 9:47 am
Hello,
I want to give some background to a performance problem I am seeing, and then a couple of questions after that. I hope someone can help me.
I found this link while researching how to identify slow or long running queries in SQL Server 2005.
http://8086.blogcu.com/4880469/
On that page is this query, which I tested and ran against a SQL server that supports a web site where we have been experiencing performance problems. (Usually no blocking, but the web site becomes unresponsive and the app server crashes, requiring a restart of the web site services.)
This is the query:
[font="Courier New"]SELECT creation_time
,last_execution_time
,total_physical_reads
,total_logical_reads
,total_logical_writes
, execution_count
, total_worker_time
, total_elapsed_time
, total_elapsed_time / execution_count avg_elapsed_time
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;[/font]
When I looked at the results, I saw that this query was at the top:
[font="Courier New"]select * from OpenRowset(TrcData, @traceid, @records) [/font]
And the data I see looks incredibly high. For example:
total_elapsed_time: 42,947,947,700
avg_elapsed_time: 14,315,982,566
I was somewhat relieved to learn that these values are in microseconds, so the values in seconds are
total_elapsed_time: 42,947.9477 seconds
avg_elapsed_time: 14,315.98257 seconds
But that is still 238 min. average elapsed time for this query, which seems absurdly high to me.
So my questions are:
1. What is this query - is it a SQL system query or a user query that I need to track down?
[font="Courier New"]select * from OpenRowset(TrcData, @traceid, @records) [/font]
2. What would cause a query to take so long to complete? I see a few other long-running queries below this top one, but they average 9, 56, 60, and 116 seconds respectively. Three of those are queries run as part of a data transfer job against a linked server, and perhaps they can be further optimized but I am not as worried about them since I know what they are. It's the giant query I described above that I can't figure out.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 29, 2008 at 9:57 am
It's not really possible to say where that comes from. It doesn't look like a system query, but that doesn't mean it isn't.
I would suggest you set up a server-side trace and run it during your most active periods. You can script the trace definition from profiler, it's probably the easiest way of generating it. Write the trace to a file on a fast drive that doesn't have any of your database files on it.
Is your slowdown regular or doers it happen at random times?
OpenRowset reads from a remote data source using OLEDB. It could be anything from a text file to another database server on the other side of the world.
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
July 29, 2008 at 2:04 pm
GilaMonster (7/29/2008)
It's not really possible to say where that comes from. It doesn't look like a system query, but that doesn't mean it isn't.I would suggest you set up a server-side trace and run it during your most active periods. You can script the trace definition from profiler, it's probably the easiest way of generating it. Write the trace to a file on a fast drive that doesn't have any of your database files on it.
Is your slowdown regular or doers it happen at random times?
OpenRowset reads from a remote data source using OLEDB. It could be anything from a text file to another database server on the other side of the world.
Thanks for your reply. I've never set up a server-side trace before, but I will research it. This page has been recommended by several web sites I've seen:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
It turns out the web site performance issues were due to something not directly database related, but I still want to find out what that OpenRowset query is. In particular, what values it is using for the variables TrcData, @traceid, and @records.
I happened to be running a T-SQL Duration profile trace yesterday. Could this be the performance info on that trace?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 29, 2008 at 2:23 pm
I am guessing, but that query definitely looks like something that would come from running a Profiler trace. If you were running profiler at the same time - that would explain what you were seeing.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 29, 2008 at 2:29 pm
A trace would also show its run-time in hours pretty normally, so it might not even be something to worry about.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 2:35 pm
I'm starting to think the same thing. I found this page in a Google search:
http://jimmers.info/kb/VTables.txt
The file is titled "Undocumented virtual tables for Microsoft SQL Server 2000 SP3" and it has these lines in it:
-- used in sp_trace_getdata
-- SELECT * FROM OpenRowSet (TrcData, @traceid, @records)
So it seems this is related to trace files after all. (Even in 2005 it may be the same function.) I wonder if it may also be the performance totals for the query that runs when I save trace info to a table.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 29, 2008 at 2:40 pm
webrunner (7/29/2008)
I happened to be running a T-SQL Duration profile trace yesterday. Could this be the performance info on that trace?
Depends. Were you running it to file or to table. If to table, then yes, very likely.
btw, of all the ways to run profiler, saving to table (especially on the server you are tracing) is the worst option i.t.o. overhead to the server
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
July 29, 2008 at 2:49 pm
GilaMonster (7/29/2008)
webrunner (7/29/2008)
I happened to be running a T-SQL Duration profile trace yesterday. Could this be the performance info on that trace?Depends. Were you running it to file or to table. If to table, then yes, very likely.
btw, of all the ways to run profiler, saving to table (especially on the server you are tracing) is the worst option i.t.o. overhead to the server
Yes, I have read that advice and would definitely not save to a table during the trace. I was running the trace to a file on my PC (not processing data on the server). But in the past few days I have saved the trace files to a table on my local PC's test SQL server after the trace was stopped. Does saving the trace to a table hit the server too, or does it just use my local PC resources? The files rolled over every 50 MB but the total was pretty big - millions of trace rows.
If saving to a table after the trace is stopped does not hit the server, then it seems the query that showed up in the stats was the original trace itself.
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 30, 2008 at 12:59 am
Where was the table you were saving to?
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
July 30, 2008 at 1:32 am
Hi webrunner,
Don't know if you've found what this was, but was wondering if this is the blackbox trace that runs:
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
http://decipherinfosys.wordpress.com/2007/12/26/black-box-trace-in-sql-server-2000-and-2005/
Just a guess...
July 30, 2008 at 7:29 am
GilaMonster (7/30/2008)
Where was the table you were saving to?
I was saving to a table on my local test SQL Server instance on my desktop. (Where I was also storing the trace file as well.)
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 30, 2008 at 7:32 am
humbleDBA (7/30/2008)
Hi webrunner,Don't know if you've found what this was, but was wondering if this is the blackbox trace that runs:
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
http://decipherinfosys.wordpress.com/2007/12/26/black-box-trace-in-sql-server-2000-and-2005/
Just a guess...
Thanks for your reply. I don't think the black box trace is running (it is SQL 2005), but I will check to see if the OpenRowset query is the result of the SQL 2005 default trace, which I know is running on that server.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
July 30, 2008 at 7:42 am
Then that should have only affected your local machine, not the server.
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
July 30, 2008 at 9:17 am
webrunner (7/30/2008)
humbleDBA (7/30/2008)
Hi webrunner,Don't know if you've found what this was, but was wondering if this is the blackbox trace that runs:
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx
http://decipherinfosys.wordpress.com/2007/12/26/black-box-trace-in-sql-server-2000-and-2005/
Just a guess...
Thanks for your reply. I don't think the black box trace is running (it is SQL 2005), but I will check to see if the OpenRowset query is the result of the SQL 2005 default trace, which I know is running on that server.
Thanks again,
webrunner
Ahhhhh, yes. It's likely as the Default Trace is on, unless you've physically switched it off - if I remember right :ermm:. Though they say that this shouldn't have much of an impact.
Hope you get it sorted.
July 30, 2008 at 9:30 am
I doubt it. Openrowset is a function to pull data into SQL in a table format. The default trace runs to disk.
I've never seen this on my servers and I do monitor the query stats for long running queries. Never seen the default trace appear.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply