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)
Thank this author by sharing:
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:
There are 14 optional input parameters:
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!).
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.
This is a quick overview of your system's general properties:
This section lists some general system settings that most individuals like to be aware of:
Fixed Drives Free Space:
Listing of the fixed drives attached to the server, along with the free space available on each:
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:
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:
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:
SQL Server Agent Jobs (Last 24 Hours):
List of all SQL Server Agent Jobs executed in the last 24 hours (either scheduled or manually):
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).
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.
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.
Any friendly feedback is always welcome. Enjoy!
Index on BIT column
Enabling Transparent Data Encryption on Databases in Always On Scenario
Included columns enable nonclustered indexes to become covering indexes for a variety of queries, im...
PRACTICAL APPROACH to know On which columns of a table & when we have to apply indexes?