Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

SQL Server System Report

By Sean Smith,

There's a lot of information that can be retrieved from an instance of SQL Server; some of it you will find helpful, some of it you won't (it all depends on your environment, needs, and perspective). I've found, however, that there are certain key elements that I want to be kept up to date with, either as a way to spot potential problems or just for my own personal knowledge. With that in mind, I decided to construct a general "System Report" procedure that I could run either on-the-fly and display the results on-screen, or deploy and schedule on any instance and have the results automatically delivered to my inbox.

If you wish to output to the screen, simply type and run:

EXECUTE dbo.usp_SQL_Server_System_Report

There are 14 optional input parameters:

  1. @Output_Mode: defaulted to NULL, you can pass a value of 'E' to have the results sent via e-mail (see below for further details)
  2. @Unused_Index_Days: defaulted to 7, this is the number of days the server has to have been online (since the last SQL Server startup) before the report will execute the "Unused Indexes" section of the code (see below for further details)
  3. @Recipients: defaulted to NULL, e-mail address(es) to send the report to (separated by semicolons if there's more than one) when @Output_Mode is set to 'E'
  4. @Copy_Recipients: defaulted to NULL, e-mail address(es) to CC the report to (separated by semicolons if there's more than one) when @Output_Mode is set to 'E'
  5. @Instance_Information: defaulted to 1, enables or disables the "Instance Information" section of the report (0 = disabled, 1 = enabled)
  6. @Instance_Settings: defaulted to 1, enables or disables the "Instance Settings" section of the report (0 = disabled, 1 = enabled)
  7. @Drives_Space: defaulted to 1, enables or disables the "Fixed Drives Free Space" section of the report (0 = disabled, 1 = enabled)
  8. @Database_Summary: defaulted to 1, enables or disables the "Database Summary" section of the report (0 = disabled, 1 = enabled)
  9. @Database_Details: defaulted to 1, enables or disables the "Database Details" section of the report (0 = disabled, 1 = enabled)
  10. @Last_Backup: defaulted to 1, enables or disables the "Last Backup Set Details" section of the report (0 = disabled, 1 = enabled)
  11. @Agent_Jobs: defaulted to 1, enables or disables the "SQL Server Agent Jobs (Last 24 Hours)" section of the report (0 = disabled, 1 = enabled)
  12. @Fragmentation: defaulted to 1, enables or disables the "Index Fragmentation" section of the report (0 = disabled, 1 = enabled)
  13. @Missing_Indexes: defaulted to 1, enables or disables the "Missing Indexes" section of the report (0 = disabled, 1 = enabled)
  14. @Unused_Indexes: defaulted to 1, enables or disables the "Unused Indexes" section of the report (0 = disabled, 1 = enabled)

Additional Notes:

  • Any piece of the code which produces an empty result set will be omitted from the final report (eliminating blank report sections)
  • All numeric values use commas for thousandths separators
  • All "size" values are rounded and converted to MB
  • All "duration" values are in DAY(s) HH:MM:SS format, with any leading zeroes (when the time unit contains all zeroes) replaced by underscores for easier readability (example: a duration of 8 minutes and 12 seconds will be represented as _ Day(s) __:08:12, and a duration less than a second will be represented as _ Day(s) __:__:__)
  • If you run the procedure using the e-mail output option, the subject line of the report will automatically be set to: SQL Server System Report: "Your Server Name"

A nice part about this procedure is that it sets up the initial framework to store various data set results into permanent tables (if you wish, and are willing to add a bit of extra code) so that the results can be analyzed and trended at a later point in time.

Notes Specific To The E-Mail Output Option:

Personally, I use the e-mail output option and have it set to run daily through a SQL Server Agent Job, though your needs may differ (if you decide to use an alternate schedule then you may want to adjust the code accordingly as some stats are for the last 24 hours only and will not be very useful if you plan to run the procedure say once a week). Also, feel free to disable any sections which you feel add little or no value to your particular environment.

The end result is an HTML formatted series of data grids (with header labels) sent in the body of an e-mail via sp_send_dbmail (database mail must be set up before you can use this option!).

Report Output:

Each part of the report is listed below (e-mail / HTML output version shown), along with additional details and sample screenshots with the output field descriptions listed under each section.

Instance Information:

This is a quick overview of your system's general properties:

  • NetBIOS Name: NetBIOS name of the SQL Server
  • Server Name: name of the SQL Server
  • Instance Name: name of the SQL Server instance
  • Product Name: product name
  • Edition: product edition
  • Version: product version in the form of 'major.minor.build'
  • Level: level of the version (original, service pack, beta)
  • Clustered: indicates if the instance is configured in a failover cluster
  • Online Since: date since SQL Server last started
  • Uptime: number of days since SQL Server last started
  • Process ID: used to identify which SQLServer.exe belongs to which instance
  • Connections: shows the number of connections (successful or not) since SQL Server was last started
  • Reads: disk reads (not cache reads) since SQL Server last started
  • Writes: disk writes since SQL Server last started
  • Logical CPU Count: logical CPUs on the system
  • Physical CPU Count: physical CPUs on the system
  • Server Memory (MB): total physical memory on the server
  • Windows Version: shows the operating system and version information for the instance

Instance Settings:

This section lists some general system settings that most individuals like to be aware of:

  • Authentication: security authentication mode of the instance
  • Language: language of the instance
  • Locale ID: locale ID of the instance
  • Collation: default collation of the instance
  • Date Format: date format of the instance
  • First Day Of Week: shows what the default first day of the week is on the instance
  • Full-Text Installed: indicates if the full-text component is installed
  • Advanced Options Enabled: indicates if advanced options is enabled
  • CLR Enabled: indicates if CLR user code execution is enabled
  • Compress Backups: indicates if backups are compressed by default
  • Scan For Startup Procs: indicates if the instance scans for startup stored procedures
  • Optimize For Ad Hocs: indicates if the plan cache size has been reduced for single-use ad hoc workloads
  • Command Shell Enabled: indicates if the command shell is enabled
  • SQL Mail Enabled: indicates if SQL mail is enabled (depends on the SQL Server version)
  • Database Mail Enabled: indicates if database mail is enabled
  • Default Trace Enabled: indicates if the default trace is enabled
  • Fill Factor %: default fill factor percentage
  • Minimum Memory (MB): shows the minimum memory allocated to SQL Server
  • Maximum Memory (MB): shows the maximum memory allocated to SQL Server

Fixed Drives Free Space:

Listing of the fixed drives attached to the server, along with the free space available on each:

  • Drive Letter: letter assigned to the drive
  • Free Space (MB): free space remaining on the drive

Database Summary:

Listing of each database's size stats, along with how the space has been distributed / allocated.

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

  • Database Name: name of the database
  • Database Owner: name of the database owner
  • Recovery Model: recovery model the database has been set to
  • Compatibility: SQL Server compatibility behavior
  • Create Date: date the database was created (or renamed)
  • Collation: default collation of the database
  • Case Sensitive: indicates if the database is case sensitive
  • Total Size (MB): total size of the database (includes both data and log files)
  • Unallocated (MB): space in the database that has not been reserved for database objects
  • Reserved (MB): space allocated by objects in the database
  • Data (MB): space used by data
  • Index (MB): space used by indexes
  • Unused (MB): space reserved for objects in the database but not yet used
  • Cached (MB): space used by cached data pages

Database Details:

A more detailed view of the database space allocation, with suppression of duplicates on the Database Name column for easy readability, along with some general database attributes:

  • Database Name: name of the database
  • File Type: type of file (rows, log, full-text)
  • Logical Name: logical name of the database file
  • Drive Letter: drive letter where the physical file is located
  • File Path: file path where the physical file is located
  • File Name: physical name of the database file
  • File Size (MB): size of the file
  • Max Size: maximum size the file can grow to
  • Growth Increment: increment by which the file will grow when an auto-growth operation occurs
  • File Reads: number of reads issued on the file
  • File Writes: number of writes issued on the file
  • File Read (MB): total megabytes read from this file
  • File Written (MB): total megabytes written to this file
  • I/O Wait Time Reads: total time users waited for reads to be issued on the database file
  • I/O Wait Time Writes: total time users waited for writes to be completed on the database file

Last Backup Set Details:

Details surrounding the last set of backups to run for all backup types (will only list databases currently connected to the instance and will also include databases which have never been backed up). Includes suppression of duplicates on the Database Name column for easy readability:

  • Database Name: name of the database
  • Backup Set ID: backup set identification number
  • Backup Type: type of backup performed (database, log, differential, etc.)
  • Database Version: database version number
  • Server Name: name of the server which created the backup
  • Machine Name: name of the machine which created the backup
  • Physical Device Name: physical name of the backup device
  • Backup Start Date: start date of the last backup operation
  • Backup Finish Date: finish date of the last backup operation
  • Duration: time taken for the backup to execute
  • Backup Size (MB): size of the backup set
  • Days Ago: number of days since the backup was last executed

SQL Server Agent Jobs (Last 24 Hours):

List of all SQL Server Agent Jobs executed in the last 24 hours (either scheduled or manually):

  • Job Name: name of the job
  • Job Owner: name of the job owner
  • Date Created: date the job was created
  • Date Modified: date the job was last modified
  • Version: version of the job
  • Category: job category name
  • Class: type of item in the category
  • Type: type of category
  • Last Run Date / Time: date the job was last executed
  • Last Status: last execution status of the job
  • Duration: time taken to execute the job
  • Next Run Date / Time: next scheduled execution of the job (if any)
  • Days Away: number of days until the next scheduled execution of the job (if applicable)

Index Fragmentation:

List all indexes in the database with a fragmentation level of 5% or higher (uses the sys.dm_db_index_physical_stats dynamic management function in LIMITED mode).

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

  • Object Type: indicates the type of object (will either be "U" for User-Defined Table or "V" for View)
  • Database Name: name of the database
  • Schema Name: name of the schema
  • Object Name: name of the object
  • Index Name: name of the index
  • Index Key: list of columns which comprise the index
  • Include Key: list of columns which comprise the include portion of the index
  • Filter Definition: filtered index definition / criteria
  • Fragmentation: average logical index fragmentation percent
  • Index Type: type of index (clustered, nonclustered, heap, etc.)
  • PK: indicates if the index is a primary key
  • Unique: indicates if the index is a unique constraint
  • Recommendation: recommendation based on index fragmentation level (5-30% = Reorganize, > 30% = Rebuild)
  • Row Count: total table / view row count
  • Alter Index Statement: SQL statement to reorganize or rebuild the index

Missing Indexes:

List missing indexes which could help improve query performance. Be very careful when deciding which new indexes to add. In some cases I've had SQL Server suggest adding a ton of columns to the index and a bunch more to the include portion. In the end it's essentially every column from the table. Also, consider the overall impact the index will make, along with the number of unique compiles. Choosing which indexes to implement is truly an art form, so please use caution and common sense.

Lastly, please note that the Create Index Statement output will attempt to name your indexes for you. You can change this to whatever format suits your needs, but be aware that if the attempted name exceeds the maximum length limit a generic value of "<INDEX NAME>" will be substituted and you will need to manually enter a name yourself.

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

  • Object Type: indicates the type of object (will either be "U" for User-Defined Table or "V" for View)
  • Database Name: name of the database
  • Schema Name: name of the schema
  • Object Name: name of the object
  • Unique Compiles: compiles / recompiles that could have benefitted from the missing index
  • User Seeks: number of user seeks that could have used the missing index instead
  • User Scans: number of user scans that could have used the missing index instead
  • Avg User Cost: current average cost of the user queries
  • Avg User Impact: average percent benefit that user queries could experience if the missing index is added
  • Overall Impact: calculation based on (User Seeks + User Scans) * (Avg User Cost * Avg User Impact)
  • Impact Rank: rank (per database) based on Overall Impact
  • Index Key: suggested columns which would comprise the index
  • Include Key: suggested columns which would comprise the include / covering portion of the index
  • Table Column Count: total columns in the table
  • Index Column Count: total columns which would be part of the index
  • Include Column Count: total columns which would be part of the include
  • Index % Of Columns: total percent of columns that would make up the index as compared to the total columns in the table
  • Include % Of Columns: total percent of columns that would make up the include as compared to the total columns in the table
  • Total % Of Columns: total percent of columns that would make up the entire index (index and include) as compared to the total columns in the table
  • Row Count: total table / view row count
  • Create Index Statement: SQL statement to create the index

Unused Indexes:

List of all unused indexes per database. By default, this section of code will not run until at least 7 days have passed since the server was last started (allowing for a decent amount of index statistics to be collected). You can change this number by passing a different value to the @Unused_Index_Days input parameter if you feel this is too low or too high for your particular environment. The output includes "drop index" statements so you can easily update your tables, though each result should be carefully examined before any decision is made to run the code. It only checks indexes on tables which are not MS shipped, are not primary or foreign keys, and are not unique constraints.

NOTE: this only displays for databases which are online, set to read / write mode (not read-only), are not in standby mode, and are not a database snapshot:

  • Object Type: indicates the type of object (will either be "U" for User-Defined Table or "V" for View)
  • Database Name: name of the database
  • Schema Name: name of the schema
  • Object Name: name of the object
  • Index Name: name of the index
  • Index Key: list of columns which comprise the index
  • Include Key: list of columns which comprise the include portion of the index
  • Filter Definition: filtered index definition / criteria
  • Disabled: indicates if the index is disabled
  • Hypothetical: indicates if the index is hypothetical
  • Row Count: total table / view row count
  • Drop Index Statement: SQL statement to drop the index

Any friendly feedback is always welcome. Enjoy!

Total article views: 11435 | Views in the last 30 days: 1482
 
Related Articles
FORUM

Enable/Disable all indexes in all tables at once

Enable/Disable Indexes

FORUM

Index on BIT column

Index on BIT column

ARTICLE

Stairway to SQL Server Indexes: Level 5, Included Columns

Included columns enable nonclustered indexes to become covering indexes for a variety of queries, im...

FORUM

PRACTICAL APPROACH to know On which columns of a table & when we have to apply indexes?

PRACTICAL APPROACH to know On which columns of a table & when we have to apply indexes?

FORUM

Combination of column index

indexing on combination of column, which index clusted or non-clusted

Tags
administration    
database    
details    
indexes    
instance    
monitoring    
properties    
server    
settings    
summary    
uptime    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones