February 27, 2014 at 7:44 am
Hi Experts,
In one of our requirement, I want all the query details for the SQL query batch that got executed for the day. I know, we can get sql query from dm_exec_query_stats. But I want all sql query along with their session details ie. ExecutedDateTime, SessionId, UserID etc. I have tried using sys.dm_exec_sessions. But it contains only last executed query details for all the sessions.
Please suggest me how to obtain all the session details for all the query executed for the day in the server.
Thanks in advance....
Debanjan
February 27, 2014 at 7:46 am
you could run profiler as a server side trace
---------------------------------------------------------------------
February 27, 2014 at 7:50 am
we dont want to run a trace from SQL Profiler since it will slow down the server. Is there any query or ETL, so that we can get those details into a table and process further.
February 27, 2014 at 7:55 am
you could run an extended event session to capture that since it looks like you are on 2012 and sql 2012 is more equipped and more friendly than previous versions for XE.
In either case, if you have an extremely busy server you might see a small performance hit trying to capture every query (either via server side trace or via XE).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 27, 2014 at 8:03 am
debanjan.ray (2/27/2014)
we dont want to run a trace from SQL Profiler since it will slow down the server. Is there any query or ETL, so that we can get those details into a table and process further.
yes it would if you run direct from the gui, but low overhead if run as a server side trace
---------------------------------------------------------------------
February 27, 2014 at 8:04 am
SQLRNNR (2/27/2014)
you could run an extended event session to capture that since it looks like you are on 2012 and sql 2012 is more equipped and more friendly than previous versions for XE.In either case, if you have an extremely busy server you might see a small performance hit trying to capture every query (either via server side trace or via XE).
Running graphical profiler against the server (especially ON the server) can have a significant performance hit. Running a profiler trace to disk has minimal impact. There is actually ALWAYS a profiler running to disk on EVERY SQL Server instance by default. That should show you it can be done without undue impact. Oh, I also note that the GUI profiler can skip events too.
You should be very particular in what fields and events you trace though. You can REALLY bloat out the trace files (and cause some impact on the server) if you aren't knowledgeable. You should trace to local disk and have sufficient space there.
What do you plan on doing with this information? I have clients that generate 100+MB PER MINUTE of very narrow and restricted trace data. Making use of that much data (or even a small fraction of it) for an entire day can be a challenge.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 27, 2014 at 8:13 am
A much, much, less accurate mechanism would be to capture the information from sys.dm_exec_query_stats. You can see the last executed date there. That will show you what has been called in the last 24 hours. But, there are simply tons of limitations here. First, and most importantly, this data is cache dependent, so if your query aged out of cache or was never in cache, it won't show up in sys.dm_exec_query_stats when you call it. Also, you won't be able to see who made the call, what parameters and values were used, or any of that kind of detail. And, in order to know how many times the query was called, you'd have to compare it between today & yesterday (and cross your fingers that it didn't recompile during the day, resetting all the values).
As I said, less accurate. However, it is easy & quick and can give you a general idea of the calls made to the system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2014 at 8:14 am
TheSQLGuru (2/27/2014)
SQLRNNR (2/27/2014)
you could run an extended event session to capture that since it looks like you are on 2012 and sql 2012 is more equipped and more friendly than previous versions for XE.In either case, if you have an extremely busy server you might see a small performance hit trying to capture every query (either via server side trace or via XE).
Running graphical profiler against the server (especially ON the server) can have a significant performance hit. Running a profiler trace to disk has minimal impact. There is actually ALWAYS a profiler running to disk on EVERY SQL Server instance by default. That should show you it can be done without undue impact. Oh, I also note that the GUI profiler can skip events too.
You should be very particular in what fields and events you trace though. You can REALLY bloat out the trace files (and cause some impact on the server) if you aren't knowledgeable. You should trace to local disk and have sufficient space there.
What do you plan on doing with this information? I have clients that generate 100+MB PER MINUTE of very narrow and restricted trace data. Making use of that much data (or even a small fraction of it) for an entire day can be a challenge.
+1
Here is an interesting article on the performance impacts of xe and server side traces etc.
http://www.sqlperformance.com/2013/07/sql-trace/observer-overhead-and-waits
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply