Need to track transactions and execution time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Need to track transactions and execution time

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2012-11-22 : 06:08:14

Hi Team,
Need to track the transactions and time of execution periodically.

could you please suggest some queries for this. of cource we have profiler, but keep on running the rpofiler on a production system is not suggestable. so please suggest some queries to track.

i can shedule that to a job to run every one hour.

Thanks in advance.

Arnav
Even you learn 1%, Learn it with 100% confidence.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-11-22 : 09:38:28

I would use a server-side trace that is filtered on batch completed and/or rpc completed events. I would also set it up to filter on the duration being greater than xx seconds (e.g. 2 seconds) to see all long running queries.

I would setup the server-side trace to use 10 files of 100MB each - with rollover enabled. That will give you 1GB of trace data available at any time to review for long running queries or issues.

If you need this to run all the time - I would then setup an agent job that runs on startup that creates the server-side trace. As long as the filtering is minimal this will have little or no impact on your system.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2012-11-22 : 10:13:05

oh great. could you please eloborate on this. what is the server side trace.

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-22 : 10:15:31

If you are just trying to see a summary of the query stats either periodically or on demand, you can use a query such as shown below. The sys.dm_exec_query_stats view has a number of other columns that also may be of interest to you.

SELECT q.total_elapsed_time / 1000.0 AS [Totalms],
       q.execution_count,
       q.total_elapsed_time / 1000.0 / q.execution_count Average,
       e.[text] AS queryText
FROM   sys.dm_exec_query_stats q
       CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) AS e
ORDER BY
       1 DESC

You also mentioned transactions. sys.dm_os_performance_counters may be a good one to look at. It has summary information such as Active Transactions, Transactions/sec etc.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-22 : 10:20:07

quote:
Originally posted by sunsanvin

oh great. could you please eloborate on this. what is the server side trace.

Arnav
Even you learn 1%, Learn it with 100% confidence.

SqServerCentral has a good series on server side tracing here: http://www.sqlservercentral.com/stairway/72363/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-22 : 12:16:59

You can use mine with changes from your side

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105302

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources