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.

Cloud Advisory Services | Security Advisory Services | Data Science Advisory and Research

Specializing in high volume web and cloud application architecture, Anuj Varma’s customer base includes Fortune 100 companies (dell.com, British Petroleum, Schlumberger).

All content on this site is original and owned by AdverSite Web Holdings, Inc. – the parent company of anujvarma.com. No part of it may be reproduced without EXPLICIT consent from the owner of the content.

Anuj Varma – who has written posts on Anuj Varma, Technology Architect.


Leave a Reply

Your email address will not be published. Required fields are marked *