November 24, 2003 at 1:16 pm
Hello everyone!! Not trying too put pressure on anyone here 🙂 But, I need the consolidated brain power of the members of this forum...
I am interested in making a list of everything that needs to be monitored for SQL Server. What I would like is some input from others stating what they monitor and what they think should be monitored.
Here are some of mine...
*Disk space utilization
*Database File size and growth
*Transaction Log size and usage
*SQL Server agent jobs
*Backups
*etc..
I can think of some more, but really want some input.
Thanks everyone!!!
"Keep Your Stick On the Ice" ..Red Green
November 24, 2003 at 1:55 pm
Okay...Here is my list so far....
· Disk space available
· Database File size
· Potential for file growth and when files grow.
· Transaction log growth
· SQL Server Log file growth
· Tempdb utilization.
· Backups
· SQL Server Agent jobs
· SQL Server Error Logs
· Server availablilty
· Database connectivity
· Database fragmentation (dbcc showcontig)
· Table/Column Statistics
· Deadlocks, locking, and blocks
· Number of connections, Unused connections
· Invalid data, Data inconsistencies, Data integrity
· Any modifications to DDL
· Long running queries
· Performance Monitor: Memory - Pages/Sec
· Performance Monitor: Network Interface – Bytes Total/Sec
· Performance Monitor: PhysicalDisk - % Disk Time - _Total
· Performance Monitor: PhysicalDisk – Current Disk Queue Length - _Total
· Performance Monitor: System - % Total Processor Time
· Performance Monitor: System – Processor Queue Length
· Performance Monitor: SQL Server – User Connections
· Performance Monitor: SQLServer - Access Methods - Page Splits/sec
· Performance Monitor: SQL Server – Buffer Manager – Buffer Cache Hit Ratio
· Performance Monitor: SQL Server – Memory Manager - Target Server Memory (KB)
· Performance Monitor: SQL Server – Memory Manager – Total Server Memory (KB)
"Keep Your Stick On the Ice" ..Red Green
November 24, 2003 at 2:59 pm
Nice list!
You might also keep an eye on this one. Especially if you are trying to track down a performance issue.
Performance Monitor: SQL Server : SQL Statistics - SQL Recompilations / sec
-Dan B
November 24, 2003 at 5:22 pm
Something more.
Login Failure
Processes with high waittime and the last waittype
Eventlogs
SQLServer:Access Methods - Full Scans/sec
SQLServer:Databases Application Database - Transactions/sec
SQLServer:Memory Manager - Memory Grants Pending
SQL Server Services
Cluster SQL Server Resources
Tracking high-resource queries
Replication
Log Shipping
November 25, 2003 at 4:50 am
Some of this may be depend upon how many people can make changes to your servers!! But these are some items I monitor as well...
SQL configuration settings
SQL file versions (Service Pack/Patch level)
OS Service Pack/Patch level
Any other 3rd party software installed / updated
Maintenance history and runtimes for all DBCC statements
November 25, 2003 at 5:19 am
Replication Latency
Log Shipping Latency
When you come up with your full list of things to monitory, any chance you could post it here?
November 25, 2003 at 7:44 am
This is great...AND yes I will post a full list. Some of the responses are great and I am sure I am not the only one who would like this list.
Don't be afraid to think outside the box (sorry for the 'box' cliche)
Jeff
"Keep Your Stick On the Ice" ..Red Green
November 25, 2003 at 8:52 am
Need more input here 🙂 I've come up with some more, but am sure there is plenty that I miss.....
"Keep Your Stick On the Ice" ..Red Green
November 25, 2003 at 9:08 am
Before the line "Database fragmentation (dbcc showcontig)" you may include "Disk fragmentation" especially if the data drives are not SQL designated. One of my servers is running other very old apps and although my Database fragmentation is OK, the disk fragmentation was killing me before to schedule nightly Defrag on the drive.
November 25, 2003 at 9:09 am
quote:
Hello everyone!! Not trying too put pressure on anyone here 🙂 But, I need the consolidated brain power of the members of this forum...I am interested in making a list of everything that needs to be monitored for SQL Server. What I would like is some input from others stating what they monitor and what they think should be monitored.
Here are some of mine...
*Disk space utilization
*Database File size and growth
*Transaction Log size and usage
*SQL Server agent jobs
*Backups
*etc..
I can think of some more, but really want some input.
Thanks everyone!!!
November 25, 2003 at 10:27 am
This is what I have so far. No explanations are given on how to monitor these things..just a list of things to monitor. This is what I am turning over to my Supervisor, but I think it would be fun if some more input and suggestions could be provided....
Administrative and Configuration:
· Release of SQL Server Service Packs and Hot Fixes
· Operating System Service Packs and Release Levels.
· Database Servers on the network osql /L
· Licensing Agreements
· Any Third Party Software Installed or Updated
· SQL Server Configuration settings
· Database options
File Systems:
· Disk space available
· Database File size
· Potential for file growth and when files grow.
· Transaction log growth and utilization.
· SQL Server Log file growth
· Tempdb objects and utilization.
· Consistency of Disk Space Allocation Structures
Tables and Indexes
· Fragmentation
· Table growth
· Duplicate indexes
· Table/Column Statisitics
· Changes in Table and Index structure and filegroups
· Fill Factor Settings
· Integrity of the data, index, text, ntext, and image pages
· Integrity of Constraints
Connectivity and Availability:
· Server Availability
· Database Connectivity
· Status of SQL Server Agent
· Status of Distributed Transaction Coordinator
· Status of Full-Text Search
· Status of SQL Mail
Logs:
· SQL Server Error Logs for Errors and Odd Entries.
· SQL Server Agent Logs for Errors and Odd Entries.
· Operating System Logs for Errors and Odd Entries.
Backup and Recovery:
· Success of Backups
· Ability to Recover
· Backup Schedules
SQL Server Agent:
· Failed Jobs
· Job Execution Times
· Job Schedules
Processes and Queries:
· Deadlocks, locking, and blocks
· Number of connections
· Old unused connections
· Long running queries
· Processes with a high waittime and last waittype.
· Tracking high resource queries
· Open Transactions
Security and Logins:
· Login failures
· Proxy Account
· Service Owners
· User/Login permissions
· Usage of the sa and any Administrator accounts.
· Orphaned users.
Data and Database Objects
· Changes/Modifications to DDL
· Orphaned temporary tables and objects
· Trigger status (Disabled vs. Enabled)
· Extended Stored Procedures
Clustered Servers
· Failovers
· Status and Availability of Cluster Resources
Replication, Log Shipping, and Standby Servers
· Replication Latency
· Log Shipping Latency
Performance Monitor Objects:
· Memory - Pages/Sec
· Network Interface – Bytes Total/Sec
· PhysicalDisk: % Disk Time - _Total
· PhysicalDisk: Current Disk Queue Length - _Total
· System: % Total Processor Time
· System: Processor Queue Length
· SQL Server: User Connections
· SQLServer: Access Methods - Page Splits/Sec
· SQLServer: Access Methods – Full Scans/Sec
· SQL Server: Buffer Manager – Buffer Cache Hit Ratio
· SQL Server: Memory Manager - Target Server Memory (KB)
· SQL Server: Memory Manager – Total Server Memory (KB)
· SQLServer: Memory Manager – Memory Grants Pending.
· SQL Server: SQL Statistics - SQL Recompilations / sec
"Keep Your Stick On the Ice" ..Red Green
November 25, 2003 at 10:30 am
That's a good one mjschwenger!!! Didn't think of disk fragmentation!!
"Keep Your Stick On the Ice" ..Red Green
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply