In-depth analysis of system traces, diagnostics and solution following a database consolidation study

Following a consolidation study, a server was identified as having an abnormally high disk impact on the SAN (80% of total activity).

A detailed analysis was conducted to understand the origin of this overconsumption:

1. Collection of SQL Server system traces 2. Analysis of the most expensive queries 3. Study of table structure and indexes 4. Identification of full table scans

The analysis revealed that a critical table was constantly fully scanned due to lack of appropriate indexes, generating 41 TB of reads per day on the SAN.

The proposed solution: create 2 targeted indexes on the most used columns in WHERE clauses.