USers and DML statement count

Question

USers and DML statement count

Ryan007

SSCrazy Eights

Points: 8275
More actions
April 15, 2011 at 1:06 am

#236699

Hi,
I need to measure users count and no of DML statement executed during a certain period.
I want the max. no of users every hour.
Without using any trigger is there a way to find total no of DML statement executed for a particular time frame?
Ryan
//All our dreams can come true, if we have the courage to pursue them//

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Use Performance Monitor/ System Monitor, set up a trace that counts the number of users. For DML, you can get batch requests, which is likely close, but depending on the application or use of stored procedures, it might be low.

Ysaias Portes-QUI Group SSCrazy Points: 2127 More actions · Answer 2

Your best way is yo use Database Specification Audits to track your NO DML statements.

You can enable it at a certain time and disable with the job at specified time.

This will help you track the time, the users and the actual statements they are running.

You can then query that data to run your analysis.

Here is the best article I have found on how to easily setup Audits:

http://www.sqldbatips.com/showarticle.asp?ID=136

QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

Lowell SSC Guru Points: 323518 More actions · Answer 3

i have a DML trace running all the time, which is recreated by a startup proc whenever the server restarts.

i then also create a view which points to the trace, and I can easily get exactly the data you are looking for:

/*--results

loginname(No column name)

mydomain\matheson 318

mydomain\lowell 128

Dev 64530

sa 87488

*/

select loginname, count(*)

from sp_dmltrace

where textdata is not null

and starttime between '2011-04-01 00:00:00.000' AND '2011-04-31 00:00:00.000'

group by loginname

here is a link to the proc i use, if you want to give it a whirl. it creates a proc, which in tern would try to create a trace and then a view that points to the trace.

sp_AddMyTrace_Latest.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Change data capture provides information about DML changes on a table and a database. By using change data capture, you eliminate expensive techniques such as user triggers, timestamp columns, and join queries.

You can enable or disable CDC

Change Data Capture

http://msdn.microsoft.com/en-us/library/cc627397.aspx

Thanks
Parthi