SQL Scripts Manager includes the following scripts, written by recognized SQL Server experts:

Back up database

Backs up one or more databases (requires the Ola Hallengren Maintenance Pack to be installed)

You may need to run Install Ola Hallengren Maintenance Pack before running Back up database.

This script is useful when you need to perform an ad hoc backup, and provides a much simpler form to complete than the equivalent 'Back Up' dialog in SQL Server Management Studio.

Script options

You can specify a single database to back up, multiple named databases, groups of databases (ALL_DATABASES, USER_DATABASES, SYSTEM_DATABASES), and can also use wild cards ('%') and exclude specific databases ('-'). More help and examples are available from the Back up database form.

This script supports the three basic backup types (Full, Differential ('DIFF'), and Transaction log ('LOG')), and options for verifying your backup, or performing a copy-only backup.

More help on these options is available from the Back up database form.

Author: Ola Hallengren

Backups in last 'x' days

Lists backups of all types completed in the last 'x' days, for a specified database

This script is useful for checking the recent sequence of backups on a particular database. You can quickly see whether a particular type of backup is missing or delayed, and then investigate further if necessary.

If you need to run an ad hoc backup, you can use the Back up database script. To see the most recent successful backup for every database on an instance, use the Last good backup script.

Author: Rodney Landrum

Blocking transactions

Lists blocked and blocking transactions, including information about SQL statements, logins, and session IDs

This script is useful if you suspect that a transaction is being blocked by locks that another transaction has on a database object. This situation can easily occur on a busy database, particularly if long-running INSERT, UPDATE, or DELETE statements are being processed.

The script results include a row for each database object that is subject to blocking. Values for Resource Type, Lock Type (also known as 'request_mode' in SQL Server), Request Status, and Wait Type, provide detailed technical information about exactly what is locked, and the nature of the lock. Refer to your SQL Server documentation (for example, SQL Server Books Online) for detailed information about these values.

Excessive blocking can have many possible causes; refer to your SQL Server documentation for more information.

Author: Tim Ford

Buffer cache hit ratio

Shows the buffer cache hit ratio (%) for an instance

The buffer cache is a pool of memory that SQL Server uses to store data pages from data files. If a data page is required to run a statement (for example, a SELECT query), SQL Server attempts to read the page from the buffer cache. Reads from the buffer cache are much quicker than reads from disk, so efficient use of the buffer cache is generally good for your SQL Server's performance.

The buffer cache hit ratio, shown by this script as a percentage, measures the proportion of pages read from the buffer cache ('hits'), out of all page reads. This is an aggregate figure for every database on the instance. Pages that aren't in the buffer cache must be fetched from disk.

An acceptable value for the buffer cache hit ratio depends on the nature of the applications running on your instance.

For example, OLTP (Online Transaction Processing) applications are likely to benefit from using the buffer cache to a greater extent than OLAP (Online Analytical Processing) applications. Therefore, higher values for the buffer cache hit ratio (above 90%) might be expected to indicate a healthy OLTP-type instance, whereas values below 90% for an OLAP-type instance might be acceptable.

If your instance hosts both OLTP and OLAP-type applications, the buffer cache hit ratio may vary significantly at certain times of the day (while batches of reports are being generated, for example). You will need to take into account these characteristics of your system before using the buffer cache hit ratio to guide performance tuning.

Refer to your SQL Server documentation (for example, SQL Server Books Online) for more detailed information about the buffer cache, and the buffer cache hit ratio.

Author: Tracy Hamlin

CPU-intensive queries

Lists the queries that have used the most CPU time

If you are trying to reduce CPU usage by SQL Server queries, concentrate your tuning efforts on the queries listed by this script.

Data is aggregated for multiple runs of each query.

CPU usage statistics for the entire server (including non-SQL Server tasks) are listed by the Server CPU utilization script.

Author: Louis Davidson

Database file I/O waits

Lists wait statistics for reads, writes, and total input/output for each database file in a database

This query shows you which database files are waiting the longest for disk I/O. Results are ranked by Average I/O Wait (ms).

This information can help you to decide where to locate individual files based on the disk resources you have available, and can also help you pinpoint disk bottlenecks for particular files.

Author: Glenn Berry

Database files - size and space used

Lists the database files that comprise a single database, including their size, and space used

You can use this script to:

• see all the data files and log files that the database uses, in a single report
• check which file group each data file is in
• check the logical and physical (OS) file names for each database file
• check the total size of each database file, and the amount of space currently used (in Megabytes, and as a percentage)
• check the growth settings for each database file (maximum size, and autogrowth increment)

This information can help you understand how your database is utilizing disk space, and to assess how much growth is possible.

Author: Dan MacClain

Execution plan cache

Lists the contents of the execution plan cache for an instance, ranked by average running time for each statement

The execution plan cache shows how statements were executed by SQL Server. By analyzing the execution plans for statements that take longer than expected to run, you can plan and test changes to your database to improve a statement's performance. For example, you may decide to add or modify an index.

You should refer to your SQL Server documentation (for example, SQL Server Books Online) for detailed information about how to interpret and act on execution plans.

Author: Gail Shaw

Export 'INSERT' templates

Exports 'INSERT' templates for a single database

SQL Server Management Studio (SSMS) 2005 and 2008, support templates - SQL scripts for tasks that you perform on a regular basis.

This script analyzes the tables in a database you specify, and then creates a set of templates you can use to run INSERT statements against these tables, with all the necessary parameters.

The exported templates are stored within your profile folder on the local machine, so that they are available within SSMS.

Author: 'Phil Factor'

Export 'Procedure call' templates

Exports 'Procedure call' templates for a single database

SQL Server Management Studio (SSMS) 2005 and 2008, support templates - SQL scripts for tasks that you perform on a regular basis.

This script analyzes the stored procedures in a database you specify, and then creates a set of templates you can use to execute these procedures with all the necessary parameters.

The exported templates are stored within your profile folder on the local machine, so that they are available within SSMS.

Author: 'Phil Factor'

Export 'SELECT' templates

Exports 'SELECT' templates for a single database

SQL Server Management Studio (SSMS) 2005 and 2008, support templates - SQL scripts for tasks that you perform on a regular basis.

This script analyzes the tables in a database you specify, and then creates a set of templates you can use to run SELECT statements against these tables, with all the necessary parameters.

The exported templates are stored within your profile folder on the local machine, so that they are available within SSMS.

Author: 'Phil Factor'

Export 'Table-valued function' templates

Exports 'Table-valued function' templates for a single database

SQL Server Management Studio (SSMS) 2005 and 2008, support templates - SQL scripts for tasks that you perform on a regular basis.

This script analyzes the table-valued functions in a database you specify, and then creates a set of templates you can use to call these functions, with all the necessary parameters.

The exported templates are stored within your profile folder on the local machine, so that they are available within SSMS.

Author: 'Phil Factor'

Export 'UPDATE' templates

Exports 'UPDATE' templates for a single database

SQL Server Management Studio (SSMS) 2005 and 2008, support templates - SQL scripts for tasks that you perform on a regular basis.

This script analyzes the tables in a database you specify, and then creates a set of templates you can use to run UPDATE statements against these tables, with all the necessary parameters.

The exported templates are stored within your profile folder on the local machine, so that they are available within SSMS.

Author: 'Phil Factor'

Index fragmentation

Lists index fragmentation data for a single database

Index fragmentation occurs when data modifications are made to a table, and means that the fullness of each page in an index can vary over time. Queries that scan part of an index may require additional page reads due to index fragmentation, with a subsequent reduction in performance.

You can reduce the fragmentation of an index with the ALTER INDEX REORGANIZE or ALTER INDEX REBUILD T-SQL statements. There are many factors that you should consider carefully before deciding whether, and how, to reduce the fragmentation of an index. Refer to your SQL Server documentation (SQL Server Books Online, for example) for detailed information about index fragmentation data and index management.

Results are ranked by Average Fragmentation (%).

Fragmentation data is shown only for those indexes with Average Fragmentation (%) and Pages greater than the values you specify.

This is a resource-intensive script, so you should be careful not to set these values too low. You may prefer to run this script when there is lower than normal activity on your database.

Author: Tim Ford

Index maintenance

Run index maintenance on one or more databases (requires the 'Ola Hallengren Maintenance Pack' to be installed)

You may need to run Install Ola Hallengren Maintenance Pack before running Index maintenance.

Running this 'Index maintenance' script may place a considerable load on your SQL Server instance, and can make important database objects temporarily unavailable. You should ensure that this is acceptable for your environment, and consider running the script during periods of low activity.

Overview

Indexes may require periodic maintenance to reduce fragmentation. Fragmentation occurs when cumulative INSERT, UPDATE, and DELETE operations cause the logical ordering of data in an index's pages to differ from the physical ordering of data inside the data file. Excessive fragmentation can affect query performance, and slow down your database applications.

How the script works

This script analyzes your databases' indexes to identify those that would benefit from maintenance by being rebuilt or reorganized. It then performs the most appropriate maintenance action on each index, guided by measures of each individual index's fragmentation, and the contents of the index (whether it contains large object(LOB) data).

Script settings

You can specify the exact maintenance action to perform for each degree of index fragmentation (for indexes with or without LOB data). Embedded help on the Index maintenance form explains each maintenance action in detail. If you are unsure about whether to adjust these settings, you should leave them at their default values.

You can also adjust the percentage values that define low, medium, and high fragmentation, and a number of other settings that control how the script will perform index maintenance. Again, embedded help on the Index maintenance form explains each setting in detail. If you are unsure about whether to adjust these settings, you should leave them at their default values.

Author: Ola Hallengren

Install Ola Hallengren Maintenance Pack

Installs a helper database containing the Ola Hallengren Maintenance Pack

The helper database (named 'RedGateSqlScriptsManager' by default) provides stored procedures and functions for use by the following scripts:

• Back up database (by Ola Hallengren)
• Index maintenance (by Ola Hallengren)
• Integrity check (by Ola Hallengren)

You must run this script to install the helper database before using any of the scripts listed above.

If the script reports that SMO is not installed, refer to your SQL Server documentation (SQL Server Books Online, for example) for detailed information on obtaining and installing SMO.

Author: Ola Hallengren

Integrity check

Perform an integrity check on one or more databases (requires the 'Ola Hallengren Maintenance Pack' to be installed)

You may need to run Install Ola Hallengren Maintenance Pack before running Integrity check.

Running this 'Integrity check' script may place a considerable load on your SQL Server instance. You should ensure that this is acceptable for your environment, and consider running the script during periods of low activity.

This script runs integrity checks on your databases, using SQL Server's DBCC CHECKDB function. You can choose from several different types of integrity check, each of which performs different levels of logical and physical integrity checking on all objects in the databases you select.

You can perform an integrity check on a single database, multiple named databases, and groups of databases (ALL_DATABASES, USER_DATABASES, SYSTEM_DATABASES).

Further help is available from the Integrity check form.

Author: Ola Hallengren

Last good backup

Lists the date and time of the last good backup of a particular type, for each database on an instance

This script is useful for checking the progress of backups for all databases on an instance. You can quickly see whether a particular type of backup is missing or delayed, and then investigate further if necessary.

If you need to run an ad hoc backup, you can use the Back up database script.

Author: Rodney Landrum

Object read and write statistics

Lists read and write statistics for objects in a single database

Read and write statistics for individual database objects can give you a useful overview of the types of activity prevalent in various parts of a database. For example, high write activity (maintenance) on an index that is rarely used for queries, could be a good candidate for deletion.

This script shows the proportion of reads, proportion of writes, and total number of reads and writes on objects in a database that you specify.

Read and write statistics are most likely to be meaningful when your database has been running for some time, and has been performing 'everyday' tasks.

Author: Louis Davidson

Partitioned table count

Lists the number of partitioned tables in each database on an instance

Partitioned tables are often used to improve the performance of large databases, particularly when querying tables with many rows.

This script gives you a quick overview of partitioned table usage across all databases on an instance.

Author: Tracy Hamlin

Performance counter hit ratios

Lists SQL Server performance counters with ratio values, for an instance

SQL Server maintains a number of performance counters. This script identifies those performance counters that form part of a ratio value, and combines them to present ratio values directly in the results.

Most of the performance counters listed, represent cache hit ratios of various types. A cache hit ratio measures the proportion of requests for a piece of data (for example, a cursor, or an execution plan) that SQL Server met by returning the data from an in-memory cache.

In general, higher values for cache hit ratios (closer to 1.00) suggest better performance of your SQL Server instance. However, cache hit ratios can vary substantially as the types of activity running on your instance vary over time.

The combination of Performance object, Counter instance, and Counter name, identifies the meaning of each Value listed. You should refer to your SQL Server documentation (for example, SQL Server Books Online), for detailed information about these fields, and SQL Server performance counters in general.

Author: Louis Davidson

Ping multiple machines

Checks IP network connectivity to one or many machines at the intervals you specify

This script uses the standard 'ping' command line utility, to check whether there is basic IP network connectivity between this machine and the list of machines you specify.

You may find it useful to add all SQL Server machines of interest to the Machines to ping list, and then click Save preset to store these settings. You can then easily get a quick report on IP connectivity to all important machines, by running this single script.

Ping response times that are longer than the Max. acceptable response time you specify, are shown with status 'Slow' (rather than 'OK'). If a machine can't be contacted, the status is shown as 'Failed'.

Author: Red Gate

Server CPU utilization

Lists CPU utilization statistics for SQL Server and other processes, for the last 'x' minutes

CPU utilization statistics are shown as a percentage of the total CPU resource available on the server, for each minute during the Time span you specify.

This script shows the historical % CPU utilization for the SQL Server process, for other processes running on the server (such as management software), and for the System Idle process.

Author: Glen Berry

Tables without a clustered index

Lists all user tables without a clustered index, for a single database

In SQL Server, a clustered index enforces the physical order of data in a table.

Clustered indexes can be useful on columns that are regularly used for range searches, and can also provide efficient access to specific rows when the indexed value is unique.

You can create only one clustered index per table. Often, a table's primary key is appropriate for a clustered index.

The results from this script may be a useful starting point when you're investigating performance issues with a database. In many cases, a clustered index is better than no index at all.

Always test any changes you make to your database's indexes, to ensure that overall performance is acceptable.

Author: Tracy Hamlin

Top resource waits

Lists the longest resource waits for a server instance, helping you to locate bottlenecks

Wait statistics are cumulative, and calculated since the last SQL Server restart or statistics reset.

SQL Server collects data for many different wait types; refer to SQL Server Books Online for more information about each wait type.

For example, if the top wait types are disk I/O related, you could use Dynamic Management Views (DMVs) such as sys.dm_io_pending_io_requests and sys.dm_io_virtual_file_stats to analyze the problem in more detail.

You can specify a Wait time limit (%) before you run the script (default 1%). Only wait types with values higher than the percentage you specify will be listed.

Author: Glen Berry

User login audit

Lists user login attempts to a SQL Server (failed and successful login attempts)

This script is useful for security audits (helping you gather information about failed login attempts), and for checking recent activity by a particular login (before you delete a login, for example).

The script lists all failed login attempts by a user, including the IP address of the computer from which the login attempts were made. The last successful login for a user is also listed.

Author: Rodney Landrum

Version and uptime for an instance

Lists the version of the database instance and the number of days it has been running

If the instance restarts, Days Online is reset to zero.

Author: Tracy Hamlin

Who is running what?

Lists each session running on an instance, including login, query, and status information

This script provides a quick overview of who is currently running commands on a SQL Server instance. The exact command being run is listed (for example, a SELECT, UPDATE, INSERT, or BACKUP DATABASE statement), with the associated user login.

Additional information includes the session status (Background, Running, Runnable, Sleeping, or Suspended), and the database name.

The Session ID is also shown; this is useful if you need to terminate a user process using the KILL command.

Author: Tim Ford