January 10, 2008 at 1:40 pm
Hello,
I am having trouble finding the source of a query. It is turning up as a costly query in a SQL Profiler trace I ran - 189 executions in less than 24 hours with total Duration of 10,350,072 ms and total reads of 8,664,841. It is returning routine information, but I want to find it because it is running about every 5 minutes and each run takes from 45 to 60 seconds. If it is possible to optimize it, I want to know where to make the changes or whom to contact about it.
I can see the database that the query is running against and the NTUserName and LoginName, but Profiler is not getting the host name or application name.
I have searched the syscomments table to look for the query text but found nothing in the database itself. We have more than 20 other databases on the server, so I would like to avoid looking in each one if possible.
Is there a way I can find out where this query is coming from?
Thanks in advance 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
January 10, 2008 at 1:52 pm
What profiler event is it appearing under?
SQL:Batch completed means it's an ad-hoc query, with the text been directly executed.
You can include the dbid and objectid columns in profiler. The first will tell you which DB the query's been run in. The second, if it's part of a stored proc, will give you the objectid of that proc.
Also, iirc, there's a column that gives the IP address. That may help you track the machine down
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
January 10, 2008 at 2:16 pm
Hi,
Thanks for your reply.
The event class is SQL:BatchCompleted. I added ObjectName but the column is blank for this query. And the fact that it runs like clockwork every 5 minutes makes me think it is a scheduled process, job, etc.
I will look further.
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
January 10, 2008 at 3:05 pm
PS - I also included HostName (I did not see any column called IP, but I see IP addresses in the HostName column), but it too comes back blank for that query.
When you said that SQL:BatchCompleted means it is an ad hoc query, does that mean it is run in a tool such as Query Analyzer, or just that the query is not in a view or stored procedure? Could it, for example, say SQL:BatchCompleted for a query being issued from a ColdFusion or ASP web page?
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
January 10, 2008 at 6:59 pm
Because it takes about a minute to execute that query you may add BatchStarted event in your trace and try to catch its SPID in master..sysprocesses while it's being executed.
No application name usually means it's some windows or web service. If it was a call from Query Analyzer or even remote SQL Server it would identify itself.
----------
Try to disable that NT User and see who's yelling.
😀
_____________
Code for TallyGenerator
January 10, 2008 at 7:59 pm
If it runs at predictable times, you could try running sp_who2 when its running, and try to identify the connection using the CPUTime, DiskIO and LastBatch values as markers. You could also run DBCC INPUTBUFFER or fn_get_sql to confirm that you've identified the correct connection.
Ray Mond
http://www.sqlinspect.com
In-depth query analysis for SQL Server
January 10, 2008 at 11:28 pm
webrunner (1/10/2008)
PS - I also included HostName (I did not see any column called IP, but I see IP addresses in the HostName column), but it too comes back blank for that query.
Sorry, was thinking about 2005 DMVs. Error on my part.
When you said that SQL:BatchCompleted means it is an ad hoc query, does that mean it is run in a tool such as Query Analyzer, or just that the query is not in a view or stored procedure? Could it, for example, say SQL:BatchCompleted for a query being issued from a ColdFusion or ASP web page?
Just that it's not a stored procedure. SQL:Batch completed means that the query was passed from some front end (ASP page, crystal reports, cold fuxsion, VB/C# app, query anlyser, etc, etc)
ie, if it's a SQL Batch completed event, don't waste your time searching through syscomments.
It's possible to get a stored proc appearing in the batch completed event, but it will appear as EXEC StoredProcedureName ...
Is it just doing a select, or is there some data modification? (insert, update, delete)?
Sergiy's sugggestion on batch started and sp_who2 is probably you best bet. Thought, again, if the app isn't passing the client name or app name sysprocesses isn't going to have much more info.
If you have the Domain login name, perhaps you can ask your domain admin to see which machine the login was coming from.
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
January 11, 2008 at 7:55 am
Thanks, everyone.
Yes, the query is just a select statement. I will try to trace the batch starting and also see if Sergiy's suggestion of using sp_who2 can provide more information.
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
January 11, 2008 at 8:01 am
===== Call off the search. :w00t:=====
I found the query source. Gail, After reading your confirmation that the SQL:BatchCompleted could be from a web front end, I checked our scheduled tasks and found one running every 5 minutes. It has the query I wanted.
Thanks again for everyone's help. I really appreciate having a forum like this as a resource.
Sincerely,
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
January 14, 2008 at 12:04 am
Pleasure
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply