Evaluating a SQL Server environment

This is a topic with more than one meaning, so I will narrow the focus to the aspect of just looking at an existing SQL Server environment for the first time. If you are experiencing performance issues with an existing environment, I recommend you start with the Microsoft (MS) SQL Server performance monitor to concentrate your effort instead of looking at the overall SQL Server. This is especially true if there are only a few problematic processes.

It is good to start asking basic questions for better understanding of the environment to evaluate. A short checklist of the items to inquire about before proceeding includes:

  • Is this a 24×7 environment?
  • What are the peak times of usage and critical availability?
  • Are there any current performance concerns?
  • What is the current backup configuration?
  • When was the last time a recovery was performed? Practice recovery? Cloning using backups?

This information can help you determine the best approach for your evaluation like the best times to execute any of the more resource-intensive examinations. There are many tools to choose from when evaluating a SQL Server environment. Some of these are MS native tools and some are third-party. As a precaution, verify the presence of a recent backup or even execute a one-off full backup before evaluation.

The most recognized MS built-in tool for evaluation is DBCC CHECKDB which is included with every SQL Server version. DBCC CHECKDB is a Transact SQL based utility that checks the logical and physical integrity of all the objects in the specified database. This process also calls other DBCC programs and performs these steps while executing:

  • Runs DBCC CHECKALLOC on the database to check the consistency of disk space allocation structures.
  • Runs DBCC CHECKTABLE on every table and view in the database to check the integrity of all the pages and structures that make table or indexed views.
  • Runs DBCC CHECKCATALOG on the database to check for catalog consistency within the database.
  • Validates the contents of every indexed view in the database.
  • Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
  • Validates the Service Broker data in the database.

The DBCC CHECKDB utility includes options to repair issues it finds, but I do not recommend the use of these options until any existing issues have been identified and understood. All of these checks and validations can take a long time to execute against a sizeable database, so there are options to exclude one or more of the above steps for time and resource considerations. MS SQL Server versions 2014 through 2017 even offer the ability to execute this utility with a parallel degree. Consult the MS DBCC CHECKDB documentation for the version you are evaluating for more specific information.

Basic execution (from a SQL Server Management Studio prompt):

use <db_name>


dbcc checkdb



Note – For larger database executions, use the “Results To” option under the Query menu item in SQL Server Management Studio to spool the results for later review.

Brent Ozar, a well-regarded SQL Server expert, created a tool called the First Responder Kit, or sp_Blitz for short.   It is available for free from https://www.brentozar.com/first-aid/ and provides a list of its findings prioritized for immediate action or informational purposes. The lower the number a finding is given, the higher the priority. This tool installs required stored procedures and an output table in the database. However, it is free and you can use parameters at execution to avoid checking certain objects or ignore findings over a certain priority level.

Basic execution (from a SQL Server Management Studio prompt):

After downloading the First Responder’s Kit compressed archive and placing it in an accessible location, execute the sp_blitz.sql file to install the database objects. Then, from a SQL Server command prompt, execute:



Note – By default, this script examines ALL databases present on the current SQL Server and groups the findings by order of priority, sorting by database name.

My own SQL Server Health Check script (https://wordpress.com/post/dbajonblog.wordpress.com/178), which is also free, may not have the maturity of some other tools, but it does not require any objects to be installed and provides a good overall picture of a database environment. It is a single script that can be executed the same as a standard SQL script.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s