SQL Server Assessment Using SSMS and SSRS

Over the years I have been asked to complete assessments on SQL Servers many times.  The need for these assessments have varied from gathering information needed to address a current performance issue to gathering evidence for and audit to just wanting to know more information about a particular server.

I have found some great information at a number of really amazing blogs.  This is in addition to getting a great deal of information from Microsoft documentation.  I took this information and collected them in several scripts that I used for a while to gather information.  However, I was looking for something just a little bit easier to work with.  With this in mind, I took the script and developed a number of SSRS reports and can be opened as Custom Reports in SQL Server Management Studio.

Why did I do this?  Well my initial reason was to help my team get information needed to answer the questions that typically come when there is a performance issue. The questions I typically get are below.

        • Is there blocking?
        • Any deadlocks?
        • Any issues with CPU utilization?
        • Are there any open transactions?
        • What does memory utilization look like?
        • Have there been any recent changes that could be causing the issue?
        • Are any jobs running?
        • What is the state of Index Fragmentation?
        • Any issues with Statistics?

Once I created the initial page I thought, well more information can be added.  So I just kept creating reports and before I knew there we over 100 reports.  Some you may never use, but hopefully some will be very useful.  As I creating reports, it also gave me a great opportunity to learn.

While my initial motivation was to make my job easier, by sharing these I am hoping they may help other DBA professionals as well.

Add reports to get information about Security Policies on July 15, 2020. You can download the reports here: Custom Reports.

Once you download them you can put them on your computer and then open them in SSMS.  To do so, find the server or database you are looking to get the information on and right click and go to Custom Report, just as in the image below.

You then navigate to the _LaunchingPage.rdl file. Once opened it will detect the version of SQL Server and will enable buttons based on the version. While we are looking for the _LaunchingPage.rdl, you can also open any of the reports as well.  Just keep in mind not all reports will work on all versions.

In the image below there are four buttons.  The top three will take you to different locations depending on the version.  There are three options, 2012 or newer, 2008r2 or older and Azure SQL Database.

When you click the SQL Server 2012 or newer you will be taken to a report that will return a great deal of information that will be useful when doing an assessment.  This is very similar to the report you will be taken to if you click the 2008r2 or older button.  Since many of these data points are not options in an Azure SQL DB, that button will take you to the Report Library.

Below are the 21 data points that will be returned in just a few seconds.  A number of these will be helpful when doing an assessment for performance reasons.  The boxes will also change color to yellow or red if issues are found, just like the image above.

      • Date of Last restart of the service and the number of days since.  If more than 60 days, the color will change to either yellow or red.
      • The server name and database name
      • Current CPU utilization
      • CPU utilization for the past 30 minutes
      • Current Page Life Expectancy
      • Percent of memory being used
      • Amount of available memory
      • Drive size and space left for all the drives on the system that have SQL Server data or log files on them
      • Is the server in a windows cluster?
      • Is the server part of an availability group?
      • Are there any backups missing?
      • How many job failures in the past 24 hours
      • Are there any jobs running at this time?
      • Amount of memory on the server
      • Is there a pending restart of the service
      • Number of encrypted databases
      • Number of blocked processes
      • Number of open transactions
      • Current settings for Cost Threshold for Parallelism and Max Degree of Parallelism

In the upper right corner, you will see, “Click to see Report Library”.  By clicking this you will be taken to the Report Library. As example of what it looks like is below.  As you can see there are a number of reports, 138 to be exact. A complete list can be seen at the end of this post.  These links will be enabled or disabled based on the version of SQL Server.

When you click a report link, the report will look like this. You can sort the data by most of the columns.  In addition, the report can easily be exported to Excel by right clicking anywhere on the report.  There is also options to export to Work or PDF, but the reports do no export nicely to these two formats.

Every report will have a set of links near the top.  These links will take you one of three places.  The names are pretty descriptive of where the links will take you.

The “Click Here to see the query” will take you to another report that will show you the query that was used to run the report.  An example is below.

Sadly, SSMS does not allow for the copying of the code.  To do this, export this report to Word and you can copy it there.

A few of the reports, Index Fragmentation and Statistics information could take a long time if it is a large database.  Because of this I have added a warning page.

This a list of all the report that includes a description and rdl file name.  This can be found on the Report Library page at the bottom of the third column.

I have testing these on SQL Server 2008 or newer, and some reports will also work on 2000 or 2005.  The version of SSMS just needs to be one of the later versions.  As for database compatibility level, it must be set to 90(2008) or higher.

In order to run these reports you will need to be a member of the Sysadmin server role.  Some will still work if you are not.  Ideally, being a member of the local Administrators group in Windows, however there are only a few reports that may not work if you are not.

The database you are connected to cannot be changed once the report is open.  If you are looking for information about a specific database, you will need to right click on that database then open the _LaunchingPage.rdl file.

I have had several people ask if these can be used as a monitoring tool.  The answer is, not really.  These were not designed to be a monitoring tool, just something you can you to capture information about your server.  There is a refresh setting in SSRS that I tried.  It just didn’t seem to work as I had hoped.  There are many great tools out there for monitoring and not all cost money.

An unexpected benefit of these reports is training.  These reports I think point out things that a DBA might look at.  This is especially true for the MainReport.  If you have team member or you are new to the DBA world, these reports could be a guide to training.  My suggestion is to research each of the data points in the reports.  Remember, there are 21 data points on the main report.  This is where I would start.

One very important thing to remember, TEST these in a non-production environment first.

Also, you are free to make any changes as you see fit to make these reports more useful in your environment.

Thanks for visiting my blog and I hope you find these reports useful.

Report Name Purpose Of The Report
Additional Memory Counters More memory related counters such as Free List Stalls and Lazy Writes
Alert Information General information about the Alerts, including the last occurence.
All Permissions On The Database Returns the permissions on the database.  This excludes the system database roles, the Public role and the Guest account
Authentication Information Windows or Mixed authentication.  How is the server configured?
Auto Delete Jobs Are there any jobs configured to delete automatically? This report will tell you.
Availability Group Information What Availability Groups exist and what databases are involved
Azure Database Resource Stats Returns Performance related information on an Azure SQL DB
Azure SQL DB Service Tiers You can use this report to find the service level for each Azure SQL DB on your instance.
Backup (All Other Types) Information History of all other types of backups
Backup (TLog Only) Information History of transaction log backups
Backup Speed Rate The MB\Sec for each backup
Backup(Full and Differential) Information History of Full and Differential backups, including the device backed up to
Buffer Pool Extension Information Returns the file path, size and number of pages in the bufferpool extension
CDC and Change Tracking Status Returns the status of CDC and Change Tracking
CDC Tables What tables are using Change Data Capture
Change Tracking Tables (D) What tables are configured for Change Tracking
CheckDB Status Returns the date of the last successful CheckDB
Cluster Node Information Is the server in a cluster?
Column List for Current Databases  (D) Simple list of all the columns in each table
Column Store Index Information Returns information about the Column Store indexes includes type, size and row count
Columns with BLOB data types Lists all columns in the current database that use XML, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), IMAGE and TEXT data types
Compressed Objects   (D) Compression status of each object
Computed Columns in Database (D) Location and definition of each computed column in the current database
Connection Count and Memory Used Many connections to the server and how much memory is being consumed
Connection IP Addresses IP addresses of existing connections
CPU Utilization CPU Utilization by minute for the last few days.  Returns CPU usage for both “SQL Server” and “Other Processes”
Credential Information Are there any credentials on the server?
Currently Running Jobs What jobs are currently running.
Custom Error Messages Looks for custom error messages, anything with a number above 50,000
Database Audit Specification Information Database Audit information, including what Server Audit each is using
Database Auto Settings Returns setting status for “Auto Update Stats”, “Auto Create Stats” and a few others
Database Auto Tune Settings Returns current status of the Auto Tune settings
Database File Information Location of each database related file
Database Filegroup Information Filegroup information for each database
Database Functions   (D) Any user defined functions?
Database Information(Stats Settings\Compat Level) Compatibility level and addition settings for each database
Database Mail Status and History Returns information about the history of database mail, including who it was sent to
Database Role Members Returns the members of each database role for all the databases
Database Settings Settings on the databases that might be useful to know
Database Snapshot Information Are there any snapshots?
Database Triggers   (D) Find those hidden triggers with this report
Database Users Who is in what databases
Database Virtual Log Files Virtual Log File count for each database.  Will provide a warn if the count is too high
Databases Not in Offline\Online State State of each database is on Offline or Online
Default Constraint Info.  (D) Default constraints and where they are being used
Duplicate Indexes Returns all indexes that have a duplicate on the same table
Dynamic Masked Columns Lists the columns that are masked
Email In Job Steps Email addresses found in job steps.
Enabled Trace Flags Currently enabled trace flags
Encrypted Database Information Any encrypted databases?
Error Log File Size Returns the size of each of the archived error log files, as well as the current error log
Extended Events Information What Extended Event Sessions exist
Extended Events Session Status Returns the running status for each of the Exended Event sessions
Failed Logins What failed logins are happening.  Only looks at the current log
File Size\Space Used The name, size and free space of each file for all the databases.
Forwarded Records   (D) The number of forwarded records since the service was last started and the number of forwarded records in tables
Full Text Index Info.  (D) Full text indexes….are there any?
General Information General information about the SQL Server and Windows Server
Guest Account Permissions and Status (D) Status of the Guest account for the current database
Guest Account Status All Databases Status of the Guest account for each database
Index Fragmentation   (D) Index fragmentation percentage
Index Fragmentation for an Object   (D) This is the code if you want to check the fragmentation of indexes on a specific object
Index Information  (D) Name, type and location of each index in the database
Index To Column Ratio by Table  (D) The ratio of Indexes to Columns.  Could help us identify if there are too many indexes
Index Usage Returns user seeks, scans, lookups and updates for all indexes in the database
Indexes That are unused Returns indexes where there are not any user seeks, scans and lookups in the database
Job Failure Rate Past 90 Days This returns the percentage of failures for each job for the past 90 days.
Job Failures Last 30 Days Returns information about job failures for the past 30 days.
Job Information General information about each job, including schedules and notifications.
Job Step Information Provides some detail about each jobs step, including what type of step
Jobs Using Schedules What schedules are being used and what jobs are using them
Jobs with No Configured Notification If there are jobs that do not have any notifications configured, this report will help you find them.
Linked Server Information Information about all the linked servers, most importantly is it configured to use Self credentials
Log Shipping Databases Databases with log shipping configured
Login Information Information about each login on the server
Mail Configuration Information about the mail confiuration including profiles and mail servers
Mail Configuration Parameters Returns the parameter configuration for database mail
Memory Dump File Information Location, when created and size of each memory dump file.
Memory Grants Pending Returns the SPIDs with pending memory grants with the query
Memory Optimized Tables Returns information about all the memory optimized tables in all the databases.  This also includes the allocated and used sizes
Memory Settings and Counters Configuration of important memory settings
Mirrored Database Information Mirrored Databases, do they exist?
Missing Backups Any database without a full backup in past few days or no log backup as well
Nonsargable Searches in Stored Procedures Returns the list of stored procedures that have Nonsargable searches.  This include functions in the WHERE clause and leading wildcard searches
Objects with Schemabinding Returns a list of all the objects, with code, that have schemabinding
Open Transactions Information about open transactions on the server.
Operator Information Information about each operators, including email address
Orphan Database Users Returns database user accounts that do not have a matching login, either by SID or by name
Partitioned Tables Report on what tables are partitioned
Plan Guides Returns the query defined in the plan guide, if any exist
Plans In Cache with Implicit Convert Returns the TOP 100 query plans based on execution count that have an Implicit Convert
Procedures With Execute As   (D) Returns any procedure that has the keywords “Execute As” in the definition
Processes Lists all the user processes and identifies blocking information.
Processor Information How many processors on the server?
Proxy Information General information about each proxy
Public Role Permissions  (D) Permissions for the public role
Queries with High Logical Reads  (D) Returns the top 10 queries based on the number of logical reads
Query Store Status and Settings Query store status
Read Write Latency Returns Read\Write latency for each file for all databases.  This includes both data and log files.
Replication Information Any replication?
Restore Information Any restores of databases?
SA Audit and Password Review Will tell you if any accounts have security issues, passwords same as account name or no password.  Will also return settings of the SA account
Security Policy Information Returns information about the security policies in the database
Server Audit Specification Information Server Audit information
Server Permissions other than Connect Logins that have permissions at the server level other than connect
Server Role Information and Membership Who is in each server role
Server Triggers Returns information about server triggers if any exist
Service Account Information What account is being used to run the services.  Also will return the status of the service
Spills To TempDB Executes sys.dm_exec_query_stats to get the number of spills to TempDB for completed queries that still have a plan in the cache
Spinlock Information Returns information about spinlocks including backoffs
Start up Parameters Definition of each of the start up parameters
Statistics Information  (D) Information about statistics, including last updated date, sample rate and modification counts
Stored Procedures that have a “Select *” Returns all stored procedures that have a “Select *” in the code somewhere
Stored Procedures that have CURSOR” Returns all stored procedures that have a CURSOR in the code somewhere
Stored Procedures that start with SP_ Returns any stored procedure that starts with SP_.  Proces with this naming convention can present issues if MS comes out with a proc of the same name.
Stored Procs Created or Modified Last 90 Days  (D) Stored procs created or modified in past 90 days
Stored Procs with Deadlock Priority Set Returns the list of stord procedures that have the DEADLOCK_PRIORITY set
Stored Procs with NOCOUNT Set to OFF Returns the list of stord procedures that have NOCOUNT set to OFF or not set at all
Stored Procs with Recompile Returns all the stored procedures that have the keyword recompile in the definition
Stretch Database Information Returns information about the stretch tables in the database
Synonym Information  (D) Checks for the presence of Synonyms and the definition of each
Sys.Configurations Returns the contents of the Configurations table.  Will identify which settings are causing a pending reboot
Table Row Counts and Size  (D) Returns row counts for all tables in the database as well as the size
Tables Created or Modified Last 90 Days  (D) List of tables that were created or modified in the past 90 days
Tables That are Heaps   (D) Where are those heaps….use this report to find them
TempDB  Information How many files in the TempDB including file size
Temporal Table Information Returns all temporal tables as well as the create date and retention period
Trace Flags Being Used This report reviews the past three logs looking for any trace flag that was turned on.  This does not return current status of enabled trace flags.
Trace Information Any traces running?
User Defined Data Type Usage Returns the columns the are using User Defined Datatypes
User Defined Data Types   (D) What user defined database exist in the database and where are they used
Users DMLRights other than SELECT (D) Who can change data or object definition
Views Created or Modified Last 90 Days   (D) Views created or modified in the past 90 days
Wait Information Information about some of the common important wait types.
What is Using the TempDB with Code Returns what processes are currently using the TempDB.

 

Ambiguous column name error when only using one Table

Every once in a while I see something in SQL Server that has what I think are “Unexpected Results”.  This is the case recently.  I received the dreaded ambiguous column error when only using one table.  My first thought was how can that be when I am only using one table.  Just a bit of background, Ambiguous column errors are usually something we see when joining two tables and there is a column with the same name as both.

If you run the below code against the AdventureWorks2014 database it will run just fine.

SELECT Name
, ProductNumber
, Color
, ReorderPoint
FROM production.product

And when I run this, it also works just fine.  No issues.

SELECT Name
, ProductNumber
, Color
, ReorderPoint
FROM production.product
ORDER BY name

However, during the development process I needed to get the list of columns to help me determine if there are any additional columns that I need to add.  I know there are several ways to get the columns, but sometimes I find it easier to just at the “*” to return all columns.  Just a note:  this is in development on a development server.  The “*” will not make it’s way into production code.

When I added the “*”, this is where I received unexpected results.  All I did was add the “*”.  Looking at the code below, you can see SQL Server is having issues with the Name column in the ORDER BY.

And sure enough, when I executed the code I did indeed get an error.  Below is that error.

Msg 209, Level 16, State 1, Line 8
Ambiguous column name ‘name’.

I found this to be a bit odd.  When I run the code without the ORDER BY, it runs just fine.  Although I do get two columns with the same name,  Name.  Well, I still needed to get the list of columns.  So I had a choice, I could use one of the other methods to get the list of columns, Object Explorer or T-SQL using the system objects.

I decided to try something before going to those, something just a bit easier.  I decided to try using a table alias.  Similar code is below.

SELECT p.Name
, p.ProductNumber
, p.Color
, p.ReorderPoint
, p.*
FROM production.product p
ORDER BY p.name

Once the alias was added, all was good.  The query successfully ran and I was able to get the information I was looking for.  Of course, the “*” never made it into production code.

Just a small tidbit I ran across today.

Thanks for visiting my blog.