Five Ways to Reduce Database Size and Improve Performance

5 Ways to Reduce Database Size and Improve Performance - Dmitri Korotkevitch

Hosting and administration of large databases presents many challenges. These databases require powerful hardware to operate, and thoroughly designed maintenance, HA and DR strategies to meet SLAs.

Even though data retention policies are controlled by business requirements, this session will review the following techniques and best practices that can be applied to reduce the size of the data in a database and improve performance of the system.

1. Reducing internal index fragmentation in the system
2. Implementing ROW and PAGE Data Compression
3. Compressing LOB Data on the client and/or with CLR routines
4. Detecting and removing unused and redundant indexes
5. Following best practices in Database Design and Deployment, such as enabling Instant File Initialization, disabling Auto-Shrink and choosing correct data types based on business requirements.
6. Releasing free space from the data files

Prerequisites: Knowledge of SQL Server Data Storage Architecture and underlying objects, such as Filegroups, Files, Tables, Indexes, Allocation Units, Extents, Pages

SPEAKER
Dmitri Korotkevitch is a Microsoft Data Platform MVP and Microsoft Certified Master, author of Pro SQL Server Internals and other books with years of experience working with SQL Server as an Application and Database Developer, Database Administrator, and Database Architect.