Overall Strategy for Monitoring SQL Server Performance

This is just a quick cliff’s notes on what to look for in terms of the Physical Design of the database.

The four main categories of concern are :

1. Memory
2. Storage
3. I/O
4. SQL

Storage Overview

Lack of free space  on the SERVER
— Server Space

Lack of Free Space in the database
— DB Space and Log Space (Count of databases, filegroups and files) – along with totals for database and logs.

Memory

Memory Pressure – Memory Current Used Versus Target Total

Memory Ratios

1. Buffer Cache (or database cache)
2. Plan Cache
3. Workspace Memory (temp working area for sorts, merges etc.)

SQL

Large JOINs, over normalization etc.

I/O

1. Disk Contention (Not separating large tables into their own filegroups).
2. Abnormally High Physical I/O

Physical Design (Static) versus Performance analysis (Dynamic)

Performance monitoring basically validates the physical design. If the physical design is solid, the performance metrics will be ok. There are SOME situations where performance is NOT related to the PHYSICAL design.

Lock Contention – is usually poor application design.