July 2, 2015 at 8:13 am
I have an application running a search engine but which is returning in an incomplete set of records (compared to another user).
We host the database in house. I would like to see the sql query that is generated when I run the query (compared to when the other user does) to determine the discrepancy.
I cannot use profiler as we host a number of databases and applications on the server and cannot risk any performance hits.
Can anyone guide me to other methods to use to view recent queries. I have looked at queries such as sys.dm_exec_requests, DBCC INPUTBUFFER(spid) and sp_who but not having any luck.
Thanks!
July 2, 2015 at 8:55 am
you can run profiler using filters(by db_id, login_Name) , so that it will reduce the burden on the server , anyhow its not a good idea.
select text from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
add where condition by using text column
July 2, 2015 at 10:19 am
Don't run Profiler on a production server. Profiler is for consuming the data, not doing the monitoring.
To capture the information with the least possible hit on your server, use extended events and output to a file. The data will come back in XML format, so you either have to query it that way, or download a 2012 or better version of SSMS and look through the data that way. You want to capture sql_batch_complete since it sounds like this is an ad hoc T-SQL statement. But, just in case you can also capture rpc_complete. You can filter by database to ensure you only capture events from the database in question.
You can also use trace events, but they're not as lightweight as extended events. They also don't filter well at all, capturing the event first, using up memory, and then throwing it away if it doesn't match the filter criteria. Not recommended.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 7, 2015 at 3:14 am
Thanks for the advice and quick response. I will be trying the extended events.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply