SQL Server Performance

Transactions Per Day?

Discussion in 'Performance Tuning for DBAs' started by hthan, Dec 4, 2002.

  1. hthan New Member

    Hi,
    How do you determine number of trans per day? From Performance counters I could see Trans per second. Is it determined by getting an averate number of Trans/s multiplied by 60, by 60,and then by 24 to have the number per day? Or is there other way to determine this?
    Thx.
  2. bradmcgehee New Member

    I am curious as to why you need this figure. Can you tell us why? It will help me come up with a better answer.

    The problem is that the Trans/sec counter is not all that accurate or meaningful. IT only measures activity that is inside a transaction, not all server activity, producing skewed results.

    Another option is to monitor the SQLServer: SQL Statistics: Batch Requests/Sec counter instead. This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are, more so than the Trans/sec counter.

    Because of these complications, that is why I am asking what the goal of your question is.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. hthan New Member

    Hi,<br />I need to come up with ans for how many trans my SQL got hit a day? I thought if I look at SQLServer<img src='/community/emoticons/emotion-2.gif' alt=':D' />atabase: Transactions/sec I'd have the answer. I seen people asking for a proper configuerd server, given 100000 trans per day. I wonder how thet got this number.<br />Thx.
  4. sqljunkie New Member

    For approximate counts I think the Transactions/sec and Batch Requests/sec with a little math will give you what you're looking for.

    Another suggestion would be to gather StmtCompleted events in SQL Profiler. There is a lot more overhead and storage required for this though.
  5. bradmcgehee New Member

    I agree with rortloff. If you are just looking to see how many transactions a day you are getting, use Profiler to capture the data into a SQL Server table (in a different database than the one you are measuring), then use T-SQL to sum the transactions. If you do this, you will need to minimize the amount of data you capture (to reduce the performance load during the data collection), and to only gather those events you are interested in, not all traffic needs to be captured.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. hthan New Member

    thx. will try this.
  7. hthan New Member

    Hi,
    The SQLTransaction Event in Profiler seems to be the correct one to tell if a trans takes place, is it right? This event has a Trans ID.
  8. sqljunkie New Member

    The SQLTransaction Event is one way. The StmtCompleted events like TSQLSQL<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted will give you every SQL statement that is executed on the server. That might give you more information that you want for an entire day.<br /><br />If you go with Profiler I agree with Brad's comment that you should really prune the Data Columns you collect. Running Profiler for a day is going to generate a lot of samples. Remember there is a Filter option that allows you to collect data from certain applications or databases.<br /><br />You don't have to log the Profiler data direct to a SQL table to run queries against the data. You can log to a file, if you wish, and execute a query like this <br /><br />SELECT count(*) FROM <br />::fn_trace_gettable( 'trace file path and name', default )<br /><br />in Query Analyzer.
  9. hthan New Member

    I setup profiler with EventClass, Hostname, TransactionID, ApplicationName. This is a backend for an IIS box. I set a filter to get collect data from the IIS box only and exclude system databases.
    Thx.

Share This Page