June 10, 2011 at 5:01 pm
i want to give a report to my client as no of transactions per day and what they are .
can any one have any idea about this?
better to get that information in a db table .
June 10, 2011 at 5:14 pm
Before I go haring off into the distance on a few assumptions, let's confirm them...
... What's a transaction to you?
... What types of transactions are you expecting to report?
... Where do you plan on retriving them from/sending them to, and how?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 10, 2011 at 6:25 pm
Craig has some good questions. Performance monitor has some counters, but depending on how you answer Craig's questions, they may or may not work
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 11, 2011 at 4:52 am
Depending on how you're defining transactions, you could go the Performance Monitor route, which just counts transactions, or you may need to set up a server side trace. You can also get some information from dynamic management objects, but without answers to the questions asked, it's hard to know which of these will help you.
----------------------------------------------------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
June 13, 2011 at 1:49 am
i need all trasactions ( insert,update,delete,select... )on database per day.
i knew some DMVs but i did not get clear information from them.
i suggested profiler to management ..but they did not satistified with that....
iam looking to put this information in a table .then we will run a query to get information from that table ....
actually getting information into table is not must for me ,normally we store information in tables from them we manage sites to see db uptime,server uptime.... .
June 13, 2011 at 3:43 am
If all you're looking for is a count of statements, I'd go with just pulling the data out of performance monitor then. It's standard set of data. As long as you always pull the data in the same way and compare it to previous pulls of that data, it will have meaning.
Be wary of profiler. You'll note that many, most, of us who post around here use the term trace or server-side trace. That's because running the profiler gui in a production environment has implications. You should use tsql to create a trace that outputs data to a file and then manage information from there.
----------------------------------------------------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
June 13, 2011 at 4:05 am
If you need the details, you'll have to use profiler to capture it all.
Maybe have a look at my little article on how we performed it for our usage gathering:
http://qa.sqlservercentral.com/articles/Security/3203/
Keep in mind, these trace files also need space. We captured more than 1.5TB in the first year.
Off course that depends on the load on your instance(s).
ps: the article is only mentioning the usage data collection, not the processing of it. There are products on the market for that. Depends on your budget.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 13, 2011 at 9:29 am
A second for ALZDBA's article on trace, and second that Grant has noted trace is what you need, not Profiler.
Another note. Management shouldn't care how you do this. Your job is to gather certain information, and the tool you use, or the method isn't typically voted on by management. If they know that much technically, then they should have some suggestions or ideas about what to do.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 13, 2011 at 9:35 am
This is a start, you could develop this further:
SELECT
last_execution_time
, name
, text
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY fn_get_sql(DMExQryStats.sql_handle)
LEFT JOIN sys.procedures ON objectid=sys.procedures.object_id
Where
DMExQryStats.last_execution_time < GETDATE()
ORDER BY
DMExQryStats.last_execution_time DESC
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply