Overview
Here is a collection of stored procedures I developed and have been using to monitor and optimize SQL Server databases. These scripts provide insights into various aspects of database performance, identify areas for improvement, potential issues such as inefficient resource utilization, missing or unnecessary components, costly queries, and more. They expose the problems and their root causes within your SQL Server, providing clear guidance to optimize the database.
sp_sqlins_help
Provides detailed information about the procedures in SQLInspector. It takes an optional parameter, @proc_name, which represents the name of the specific procedure you want to learn about.
If specified, outputs the description of the said procedure, any parameters with their descriptions, as well as the output results produced by the procedure along with explanations.
If the optional parameter is not specified, lists all the procedures with short descriptions of what they do.
sp_backup_info
Retrieves and displays detailed backup information for each database on the SQL Server instance.
Output
| Column | Description |
|---|---|
| database_id | The unique identifier for each database in the SQL Server instance. |
| name | The name of the database. |
| state_desc | The current state of the database (e.g., ONLINE, OFFLINE, RECOVERING). |
| recovery_model_desc | The recovery model of the database (e.g., FULL, SIMPLE, BULK_LOGGED). |
| data_size | The total size of all data files in the database, measured in megabytes (MB). |
| log_size | The total size of all log files in the database, measured in megabytes (MB). |
| full_last_date | The date and time of the last full backup for the database. |
| full_size | The size of the last full backup for the database, measured in bytes. |
| log_last_date | The date and time of the last transaction log backup for the database. |
| last_log_backup_size | The size of the last transaction log backup for the database, measured in bytes. |
sp_table_sizes
Displays detailed information about table sizes in the database.
Parameter
@schema - Specifies the schema for filtering the tables. If NULL, returns results for all schemas.
Output
| Column | Description |
|---|---|
| schema_name | The name of the schema containing the table. |
| table_name | The name of the table. |
| row_counts | The number of rows in the table. |
| total_space_kb | The total allocated space for the table, in kilobytes. |
| used_space_kb | The space actively used by the table, in kilobytes. |
| unused_space_kb | The allocated but unused space for the table, in kilobytes. |
sp_current_locks
Provides information about the current locks held by sessions on various database resources.
Output
| Column | Description |
|---|---|
| session_id | The ID of the session holding or requesting the lock. |
| database_name | The name of the database where the lock is held. |
| resource_associated_entity_id | The ID of the resource associated with the lock (e.g., object, partition). |
| entity_name | The name of the resource associated with the lock (e.g., table or index). |
| index_id | The ID of the index associated with the lock, if applicable. |
| resource | The type of resource being locked (e.g., OBJECT, KEY, PAGE, EXTENT). |
| description | Additional details about the locked resource (e.g., page ranges, key details). |
| mode | The lock mode (e.g., SHARED, EXCLUSIVE), which indicates the type of operation requiring the lock. |
| status | The status of the lock request (e.g., GRANTED, WAITING). |
sp_active_tran
Provides details about active transactions within the SQL Server instance, offering insights into transaction metadata, associated sessions, and their recent execution plans.
Output
| Column | Description |
|---|---|
| session_id | The ID of the session associated with the transaction. |
| login_name | The login name of the user initiating the session. |
| database_name | The name of the database involved in the transaction. |
| begin_time | The timestamp when the database transaction began. |
| log_bytes | The number of log bytes used by the database transaction. |
| log_bytes_reserved | The number of log bytes reserved for the transaction. |
| sql_text | The SQL statement most recently executed in the session. |
| last_plan | The execution plan associated with the most recent SQL query in the session. |
sp_index_usage
Retrieves index usage statistics, showing which indexes are frequently used, as opposed to unused or rarely used ones.
Parameter
@database_name - The name of the database to display index usage information for, if NULL, retrieves indexes for all databases.
Output
| Column Name | Description |
|---|---|
| table_name | The name of the table where the index resides. |
| index_name | The name of the index being analyzed. |
| user_seeks | Number of seek operations performed on the index. |
| user_scans | Number of scan operations performed on the index. |
| user_lookups | Number of lookup operations performed on the index. |
| user_updates | Number of updates to the index. |
| last_user_seek | Timestamp of the last seek operation. |
| last_user_scan | Timestamp of the last scan operation. |
| last_user_lookup | Timestamp of the last lookup operation. |
| last_user_update | Timestamp of the last update operation. |
sp_missing_indexes
Returns costly missing indexes in a given database or all databases. Missing indexes are ranked by their potential performance benefit, calculated based on the user impact and query cost.
Parameters
@database_name - Specifies the name of a database to analyze. If NULL, returns missing indexes for all databases.
@is_current - If set to 1, returns missing indexes for the current database only.
Output
| Column | Description |
|---|---|
| group_handle | The unique identifier for the missing index group. |
| index_advantage | A calculated metric that estimates the advantage of creating the missing index. |
| avg_total_user_cost | The average cost of queries that would benefit from the missing index. |
| avg_user_impact | The estimated percentage improvement in query performance if the missing index is created. |
| user_seeks | The number of times queries attempted to use an index for seek operations but found it missing. |
| user_scans | The number of times queries attempted to use an index for scan operations but found it missing. |
| database_name | The name of the database where the missing index was identified. |
| table_name | The name of the table where the missing index is suggested. |
| equality_columns | The columns used in equality comparisons in the queries benefiting from the index. |
| inequality_columns | The columns used in inequality comparisons in the queries benefiting from the index. |
| included_columns | The columns that should be included in the index to cover queries effectively. |
sp_index_frag
Provides insights into index fragmentation across one or more databases. Highlights indexes with fragmentation above the specified threshold.
Parameters
@database_name - The name of the database to analyze. If NULL, analyzes all eligible databases.
@min_frag - The minimum fragmentation percentage for filtering indexes.
@min_table_size - The minimum table size (in pages) to filter the results.
@print_command - If set to 1, prints the dynamic SQL commands for debugging purposes. Default: 0
@execute_command - If set to 1, executes the generated SQL commands to analyze fragmentation. Default: 1
Output Columns
| Column | Description |
|---|---|
| database_name | The name of the database containing the index. |
| object_name | The name of the table or view containing the index. |
| index_name | The name of the index being analyzed. |
| schema_name | The schema of the table or view containing the index. |
| avg_fragmentation_percent | The average fragmentation percentage of the index. |
| index_type_desc | The type of index (e.g., CLUSTERED, NONCLUSTERED). |
| allocation_unit_type | The type of allocation unit (e.g., IN_ROW_DATA, LOB_DATA). |
| has_large_data_type | Indicates whether the table contains large data types (e.g., LOB, XML, or MAX types). |
| table_size | The size of the table in pages. |
sp_long_queries
Identifies long-running queries across all databases in a SQL Server instance using Query Store data.
Parameters
@days_back - Filters queries executed within the specified number of days back from the current date. Default: 5
@min_duration_ms - Filters queries with a maximum execution duration (in milliseconds) greater than this value. Default: 10,000
@include_unnamed_objects - If 1, includes queries from unnamed objects (e.g., ad hoc queries). If 0, excludes unnamed objects. Default: 1
Output Columns
| Column | Description |
|---|---|
| database_name | The name of the database where the query was executed. |
| execution_type | The type of execution (e.g., stored procedure, ad hoc query). |
| query_id | The unique identifier for the query within the Query Store. |
| object_id | The object ID of the query (e.g., stored procedure ID). |
| max_duration_ms | The maximum duration (in milliseconds) of the query execution. |
| set_options | The SET options in effect when the query was executed. |
| name | The name of the object containing the query (e.g., stored procedure name). |
| total_duration_ms | The total execution time (in milliseconds) across all executions of the query. |
| query_sql_text | The SQL text of the query. |
sp_long_proc_xe
Retrieves details of long-running stored procedure calls or queries captured by Extended Events. Provides insights into query performance by filtering based on duration, database name, client hostname, or SQL statement patterns.
Parameters
@xe_session - The name of the Extended Events session to retrieve data from. Default: 'Proc_Calls'.
@database_name - Filters results to include only events from a specific database. Default: NULL (no filtering).
@duration - Filters results to include only events with a duration greater than this value (in microseconds). Default: NULL (no filtering).
@hostname - Filters results to include only events generated by a specific client host. Default: NULL (no filtering).
@statement - Filters results to include only events containing a specific SQL statement or pattern. Default: NULL (no filtering).
Output
| Column | Description |
|---|---|
| timestamp | The time the query was executed. |
| database_name | The name of the database where the query was executed. |
| duration | The duration of the query in microseconds. |
| statement | The SQL statement executed. |
| hostname | The client machine that executed the query. |
sp_job_info
Retrieves detailed information about SQL Server Agent jobs. Identifies jobs that failed based on historical data. Drills down into specific job steps to troubleshoot issues.
Parameters
@start_date - Filters jobs by execution start date. If NULL, no filter is applied.
@job_owner - Filters jobs by the owner’s name. If NULL, no filter is applied.
@run_status - Filters jobs by their execution status. Example values: 0 (Failed), 1 (Succeeded). If NULL, no filter is applied.
@step_name - Filters jobs by the name of a specific step. If NULL, no filter is applied.
Output
1. Jobs Sorted by Execution Start Time
| Column | Description |
|---|---|
| job_name | The name of the SQL Server Agent job. |
| description | A description of the job. |
| start_execution_date | The date and time the job started execution. |
2. Jobs with Their Owners
| Column | Description |
|---|---|
| job_name | The name of the SQL Server Agent job. |
| job_owner | The name of the user who owns the job. |
3. Jobs That Failed Execution
| Column | Description |
|---|---|
| job_name | The name of the SQL Server Agent job. |
| step_name | The name of the specific step in the job. |
| sql_severity | The severity level of the SQL error, if any. |
| message | The error message associated with the job or step failure. |
| run_date | The date the job was run. |
| run_time | The time the job was run. |
4. Jobs with Specific Step Name
| Column | Description |
|---|---|
| job_name | The name of the SQL Server Agent job. |
| step_name | The name of the specific step in the job. |
| sql_severity | The severity level of the SQL error, if any. |
| message | The error message associated with the job or step. |
| run_date | The date the job was run. |
| run_time | The time the job was run. |
sp_long_jobs
Detects long running SQL Server Agent jobs by comparing current execution duration against thresholds derived from historical averages or default limits. If specified sends email notification for detected jobs.
Input Parameters
@deviation_times - Multiplier for historical average duration to define a threshold. Default: 3.
@default_max_duration_minutes - Fallback duration threshold (in minutes). Default: 15.
@from_address - Email address used for sending notifications. Optional.
@recipients - Email addresses of recipients (comma-separated). Optional.
Output
1. Currrently Running Jobs
| Column Name | Description |
|---|---|
| job_id | Unique identifier for the job. |
| job_state | State of the job (e.g., running). |
2. Detected Long Running Jobs
| Column Name | Description |
|---|---|
| job_name | The name of the SQL Server Agent job. |
| execution_date | The start date and time of the job execution. |
| avg_duration | Historical average execution duration (in seconds). |
| max_duration | Threshold duration (in seconds) for detecting long-running jobs. |
| current_duration | Current execution duration (in seconds). |