March 2, 2006 at 11:52 am
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
March 2, 2006 at 3:20 pm
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.
March 3, 2006 at 12:02 pm
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."
January 25, 2012 at 11:36 am
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.
January 26, 2012 at 7:18 am
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