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 :
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 Pressure – Memory Current Used Versus Target Total
- Buffer Cache (or database cache)
- Plan Cache
- Workspace Memory (temp working area for sorts, merges etc.)
Large JOINs, over normalization etc.
- Disk Contention (Not separating large tables into their own filegroups).
- 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.