Microsoft SQL Server has a reputation of being one of the easier database platforms to administer and manage. Oddly enough, however, when it comes to understanding what SQL Server is doing underneath the covers with respect to space, SQL Server actually isn’t as straightforward as some of the other database platforms. The ability to easily report database and transaction log space from a global perspective isn’t as simple or thorough as some administrators would like. Also,the sp_spaceused procedure used for object (and database) space doesn’t really tell the whole space picture for an object or database. And make no mistake, diagnosing fragmentation problems in a database requires a fairly skilled hand that knows how to not only interpret a number of different object fragmentation metrics, but also understands the environment and mechanics of the applications that use the database.

Like other database engines, SQL Server storage problems have the capability to immediately stop an otherwise well-running database and server completely in its tracks. Space problems also have the potential to slowly eat away at performance until response times become unbearable for a database community. Because storage has such a powerful hold over a database, it is imperative that today’s database professional understand how SQL Server uses space, be equipped with the right tools and knowledge to proactively plan their storage and object structures, and be able to quickly diagnose and fix space problems when they occur.

Space Analyst, one of the components in Embarcadero Technologies’ Analyst product line, assists the database professional in understanding the total space picture of SQL Server. It also ensures that no storage problem will reach critical mass by providing intelligent space diagnostic, management, and correction utilities that are powerful, yet easy to use. Such facilities protect the database server against the two broad types of storage problems, which are:
1. Storage Bottlenecks
2. Performance Inefficiencies

The rest of this paper will examine how SQL Server uses space and will discuss each type of storage problem in detail. It will also highlight how Embarcadero Space Analyst can be used to understand how SQL Server is using space and helps to identify and correct space problems before they threaten the availability and performance of a critical database server.

Download