SQL Server Monitoring Tool Recommendations

  • We are installing SQL Server 2005 on Windows 2003 64 bit Cluster server with an attached SAN. What monitoring tools other than those included by Microsoft are recommended?  My primary concern is monitoring application response times and what resources are being used by the Server and SQL Server in executing SQL. 

    Here is a list of Requirements

    1) Provide 24x7 performances monitoring of SQL Server. 

    2) Provide real time monitoring and alerts

    5) Store historical performance data.

    6) Provide performance trend reports

    7) Breaks down the resources used during the execution of a SQL statement.  i.e. Network, Disk usage, memory, CPU, lock waits, and other relevant information.

    8) Failed SQL statements (non-zero sql code) are captured in their entirety

    9) Captures all unauthorized access along with the source location

    10) Captures all failed logins along with the source location

    11) Captures all SQL executed outside of the applications

    12) Cluster Aware.  It knows when a failover occurs and adjusts accordingly.

    13) Low overhead on the server

    14) Reports such as worst performing queries

    15) Trace SQL for a specific user or application connection

     

    David Bird

  • After having writen reviews on most of the ones on the market, I can tell you there are no monitoring tools which meet ALL of your requirements.

    Drop off the cluster aware, and the low overhead, and most of them will satisfy your needs.

    The recording of historical data in and of itself requires a write for every transaction you perform, under the best of circumstances, even if batched, and several writes under the worst, and although most of the monitoring software "claims" low overhead, it's all relative. Many of them base their information off of transaction logs, claiming that reduces overhead, or hooks the sysprocesses table behind the scenes, etc....but whether the gathering of this data happens through a hook, the transaction logs, or otherwise, the recording of the transactions to the level your asking for will put a load on the server being monitored.

    I would suggest that you need to revise your requirements, or at the very least, order them in priority, and match the closest.

  • Here's doing it the MS way:

    1) Provide 24x7 performances monitoring of SQL Server. 

    2) Provide real time monitoring and alerts

    5) Store historical performance data.

    6) Provide performance trend reports

     Items 1,2,5 & 6 --> MOM - Microsoft Operations management

    7) Breaks down the resources used during the execution of a SQL statement.  i.e. Network, Disk usage, memory, CPU, lock waits, and other relevant information.

     SQL Profiler

    8) Failed SQL statements (non-zero sql code) are captured in their entirety

     Sorry

    9) Captures all unauthorized access along with the source location

     Sorry - but isn't this a network access issue ?

    10) Captures all failed logins along with the source location

     SQL Server errorlog, failed logins, no location though.

    11) Captures all SQL executed outside of the applications

     SQL Profiler

    12) Cluster Aware.  It knows when a failover occurs and adjusts accordingly.

     MOM - Microsoft Operations management

    13) Low overhead on the server

     MOM - Microsoft Operations management

    14) Reports such as worst performing queries

     SQL Profiler

    15) Trace SQL for a specific user or application connection

     SQL Profiler

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Actually there is a tool that will cover most of what your wanting DIAB from http://www.diabsqlsoftware.com/ it can monitor your disk, memory, cpu, network, backups, scheduled jobs, it will alert via email if needed. It even can start a trace on a user, spid, hostid or application without going through profiler it also can validate SQL code for best practices, table scans, mismatched data, clustered index scans. The list goes on to much to mention. Note SQLH2 will monitor your servers for historacle data and it's free.

  • Russ, please don't post to 5 year old threads. Thanks!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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