SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Need to track transactions and execution time		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sunsanvin
Flowing Fount of Yak Knowledge

India
1256 Posts
Posted - 11/22/2012 :  06:08:14  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
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

USA
693 Posts
Posted - 11/22/2012 :  09:38:28  Show Profile  Reply with Quote
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.
Go to Top of Page

sunsanvin
Flowing Fount of Yak Knowledge

India
1256 Posts
Posted - 11/22/2012 :  10:13:05  Show Profile  Send sunsanvin a Yahoo! Message  Reply with Quote
oh great. could you please eloborate on this. what is the server side trace.

Arnav
Even you learn 1%, Learn it with 100% confidence.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/22/2012 :  10:15:31  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/22/2012 :  10:20:07  Show Profile  Reply with Quote
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/
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 11/22/2012 :  12:16:59  Show Profile  Reply with Quote
You can use mine with changes from your side

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105302
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000