Simplest way to get the number of calls to an MSSQL2000 Server

Question

Hi All,

Does anyone know how one can get the total number of calls to an MSSQL2000 server during a specified time, let’s say 24 hours?

We want figures of how many calls our production machine gets per day, but we can’t find any good tools/strategies for this.

Best regards Fredrik

Answer 1

You could use SQL Profiler?

http://technet.microsoft.com/en-us/library/aa173918(SQL.80).aspx
http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
http://support.microsoft.com/kb/325263

Answer 2

Thanks for the links kev!

We have looked at this, but isnt this a tool for monitoring individual tables/views or stored procedures?

We need to monitor all calls to the SQL-Server.

Does anyone else have any ideas?

/Ausgar

Answer 3

I think using SQL Profiler here is overkill in this situation, particularly as it can create a substantial load on the server depending on what you trace. SQL Server exposes the raw values used for its performance counters via the sysperfinfo system table; you should just be able to run this query once each day and subtract the values to work out how many SQL requests you received for the day:

SELECT cntr_value 
FROM sysperfinfo 
WHERE object_name = 'SQLServer:SQL Statistics' 
AND counter_name = 'Batch Requests/sec'

This will obviously only work if the server is up for the whole day; restarting will reset the number.

Answer 4

Hi!

I sloved this another way (all calls are "routed" thru an IIS cluster and I where able to analyze their logs).

Thanx!