SQL Server Tips by Robin Schumacher

Storage Monitoring Basics

With a basic understanding of how SQL Server uses storage out of the way, attention can be turned to how one should monitor space in SQL Server to avoid immediate and vampire bottlenecks.

As has already been presented, a storage bottleneck can be an overwhelming event that can completely stop the operation of a database if it is severe enough. In SQL Server, a storage bottleneck normally takes one of four forms:

• Lack of free space in a database.

• Lack of free space in a database transaction log.

• Lack of free space on the server.

• One or multiple forms of fragmentation.

It is important that a DBA be able to quickly spot storage bottlenecks before they threaten a database’s availability and also know to correct and proactively prevent bottlenecks from interrupting the key operations of a database. In online transaction processing (OLTP) environments, the threat of exhausting the available free space in a database during peak working hours is always present. This being the case, how can the DBA easily spot storage bottlenecks before they stop the operation of one or more of the key databases?

The up_bn_storage_overview procedure below provides an overview of the storage situation on an SQL Server, versions 7 - 2000. It provides overview sections that display a count of databases, filegroups, and files, along with amounts for total database and log used in MB. It then displays detailed information for every database on the server and breaks out space by database and log. Finally, it displays information regarding space utilization on the server’s hard disks.

* up_bn_storage_overview.sql

-- Script is available in the Online Code Depot

Before examining the procedure’s output, a couple of notes about the SQL procedures and code that are shown in this book should be explained.

One will see a number of cursors used throughout most every procedure that builds constructs of tables and databases. It is certainly OK to replace them with the sp_msforeachtable and sp_foreachdb procedures that allow easy looping through table and database lists.

Sometimes it is necessary to show alternative procedures or queries for different versions of SQL Server because they vary too much to simply point out the distinctions. Other times, only one or more differences will be pointed out so the entire procedure’s code will not have to be repeated.

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm