You can master engineering for your SQL Server databases, on premises or in the cloud, with a FREE toolkit!

SQL Server engineering tasks can be challenging in many ways, and some tasks just seem harder than really necessary. You can make those tasks much easier with SQLFacts, a FREE suite of tools for SQL Server database engineers. Please refer to an earlier article for a general description of the SQLFacts toolkit.

SQLFacts provides many tools for SQL Server professionals.

• A previous article covered the index-related performance tuning tools in SQLFacts.

• A previous article covered the query-related performance tuning tools in SQLFacts.

• A previous article covered the monitoring and status tools in SQLFacts.

• A previous article covered the database development tools in SQLFacts.

This article will focus on the many new tools added to SQLFacts after the articles above were written. Be sure to see the README file in the SQLFacts toolkit download for more information about all the tools.

A little background about SQLFacts:

SQLFacts began as recreations of some basic tools I had been using for several years in various roles. I kept adding new tools (all T-SQL code) to the suite as needs and ideas occurred to me. My background includes a lot of database development and basic database administration, so I knew the tools needed for those tasks. I have also done a lot of performance tuning over the years, and I noticed a lack of tools for those purposes. There are slapped together, piecemeal, blog post scripts all over, but it's very hard to use them for getting things done. The needed functionality is in bits and pieces, all with different coding styles, formatting, and conventions.

SQLFacts lets you avoid the hassles of gathering, maintaining, and organizing a vast library of blog post scripts. There are other free tools, of a nature similar to SQLFacts, available for SQL Server engineers. Some of them are very good and very well-known. SQLFacts offers the functionality of all of them combined, plus a bunch more, and with meticulous consistency across the whole toolkit. SQLFacts was always intended to be a broad suite, serving as a standard set of tools for SQL Server engineers.

I have continued to improve and expand SQLFacts, with many releases in the last several months. There are now 14 additional tools in the suite, for a total of 41 (and counting).

• There are 5 new tools for database development.

• There are 3 new tools for database administration.

• There are 6 new tools for performance tuning.

There's new functionality for everybody!

New tools for database development:

PrimaryKeys

The PrimaryKeys SQL file returns information about existing primary key constraints and unique constraints.

• Result set 1 contains a list of primary key constraints (PK).

• Result set 2 contains a list of unique constraints (AK).

The Messages tab will contain generated SQL (DDL) statements to DROP/ADD all primary key constraints and unique constraints.

ForeignKeys

The ForeignKeys SQL file returns information about existing foreign key constraints.

The ON_DELETE column and the ON_UPDATE column show the referential actions for each foreign key constraint, which are typically nothing (no action).

The Messages tab will contain generated SQL (DDL) statements to DROP all foreign key constraints, TRUNCATE all tables (which is disabled as a precaution), and ADD all foreign key constraints.

Trimmer

The Trimmer SQL file returns information about a specified table and all descendants of the table. The schema and name of a table is entered near the start of the SQL code.

The Layer column in the result set defines the order in which INSERT/DELETE operations must be performed, ascending order for INSERT, descending order for DELETE. The specified table is always Layer 0 (zero).

The Messages tab will contain generated SQL (DML) statements to DELETE one or more rows in the specified table and all subordinate rows in all descendants of the specified table.

Features

The Features SQL file returns information about some less-commonly-used features in different versions of SQL Server. The result sets are populated if the current database is using the associated features.

There are four variants of the Features SQL file:

• Features_2005 returns information about features introduced with SQL Server 2005.

• Features_2008 returns information about features introduced with SQL Server 2008.

• Features_2014 returns information about features introduced with SQL Server 2014.

• Features_2016 returns information about features introduced with SQL Server 2016.

Logger

The Logger SQL file creates objects for logging certain activities.

There are three parts to the Logger tool:

• Logger_SPModel is for logging stored procedure failures and/or executions.

• Logger_DDL_Server is for logging DDL (CREATE/ALTER/DROP) activity on the server.

• Logger_DDL_Database is for logging DDL (CREATE/ALTER/DROP) activity in the database.

New tools for database administration:

Snooper

The Snooper SQL file returns information about memory configuration and memory usage.

The Snooper SQL file returns information about tempdb configuration and tempdb usage.

There are three variants of the Snooper SQL file:

• Snooper_memory examines memory configuration and memory usage.

• Snooper_tempdb examines tempdb configuration and tempdb usage.

• Snooper_tempdb2 examines various types of tempdb usage, especially Version Store.

WaitingRoom

The WaitingRoom SQL file returns information about SQL Server waits. The information is very useful for diagnosing performance problems, especially sudden performance problems.

• Result set 1 is for general information about existing sessions, including idle sessions.

• Result set 2 is for waits observed in the moment, when the tool was executed.

• Result set 3 is for waits accumulated by session. It's aggregated by session.

• Result set 4 is for waits accumulated by session. It's the detail for the previous result set.

• Result set 5 is for waits accumulated by session. It's aggregated by type of wait. It's a simple way to see what factors are preventing SQL Server from completing tasks faster.

Planner

The Planner SQL file returns details about what's in the SQL Server execution plan cache for stored procedures. The information is aggregated in several differernt ways to reveal patterns. The information is useful for examining how plans are being used, how many times they were used, how long they ran, and how many logical reads they performed.

• Result set 1 is for counts of how many plans were cached during a specified time period and how many plans were last executed during the same time period. It covers the last 60 days.

• Result set 2 is for counts of how many plans were cached during a specified time period and how many plans were last executed during the same time period. It covers the last 60 hours.

• Result set 3 is for counts of how many plans were cached during a specified time period and how many plans were last executed during the same time period. It covers the last 60 minutes.

• Result set 4 is for counts of how many plans fell within a specified range for number of executions, seconds of CPU time or duration (run time), and number of logical reads.

• Result set 5 is for basic aggregations of plans by database and schema.

• Result set 6 is for basic aggregations of plans by database.

• Result set 7 is for basic aggregations of plans.

New tools for performance tuning:

Threshold

The Threshold SQL file returns details about what's in the SQL Server execution plan cache for stored procedures. The information for individual T-SQL statements is aggregated into percentiles based on CPU time or duration (run time). The individual T-SQL statements that meet specified thresholds (see below) are listed with performance details and an indication of whether the estimated cost of the statement (from the execution plan) exceeds the threshold for parallelism.

The Threshold tool provides a way to evaluate whether the threshold for parallelism should be adjusted.

If most of the T-SQL statements at the 50th percentile and above meet the threshold then the threshold could be increased.

If most of the T-SQL statements at the 90th percentile and above do not meet the threshold then the threshold could be decreased.

• Result set 1 is for percentiles of CPU time or duration (run time) for all T-SQL statements in all stored procedures.

• Result set 2 is for individual T-SQL statements that meet any, or all, of the average/minimum/maximum thresholds. It includes the cost estimate for the statement from the execution plan and an indication of whether it exceeds the threshold for parallelism.

QueryTracker

The QueryTracker SQL file returns information for a recently executed stored procedure and the SQL statements within it.

When researching an execution plan for a stored procedure, the costs (relative to the batch) are not always accurate or informative. This tool provides very useful performance metrics for the stored procedure itself, but also for each individual SQL statement within the stored procedure. It allows you to quickly narrow your focus to the SQL statement(s) needing the most attention.

• Result set 1 is for the stored procedure itself.

• Result set 2 is for each SQL statement within the stored procedure.

• Result set 3 is for the parameters of the stored procedure and the values observed when an execution plan was created.

QueryIndexes

The QueryIndexes SQL file returns information about routines and indexes. It reveals which stored procedures are taking advantage of which indexes.

The QueryIndexes tool analyzes execution plans to extract references of nonclustered indexes by stored procedures. The information is beneficial when researching proposed nonclustered index changes. The information also points out potential tuning opportunities, such as when the query references more columns from the table than the index contains (ColumnsFound < ColumnsQuery). If the difference is minimal (few columns and/or narrow columns) then it might make sense to add columns to an INCLUDE clause.

The two result sets include query schema/object, query execution statistics, table schema/object, column counts, and index name.

• Result set 1 is ordered by query schema/object.

• Result set 2 is ordered by table schema/object and index name.

INCLUDE

The INCLUDE SQL file returns information about routines and indexes. It suggests potential extensions to indexes to make them covering indexes and improve the performance of routines (views, stored procedures, functions, or triggers).

The INCLUDE tool analyzes the columns referenced by routines and compares them to the columns contained in existing nonclustered indexes. The lead column in an index key must be referenced by the routine in order to be paired. However, the tool does not know how the lead column is used in the routine, so it's possible for a pairing to be inappropriate when the lead column is not used for joining or filtering.

The suggestions provided by the INCLUDE tool can be an effective strategy for handling many cases (not all cases) of parameter sniffing issues.

The two main result sets include query schema/object, table schema/object, column counts, index name, index columns, and suggested additional columns for an INCLUDE clause.

• Result set 1 is ordered by query schema/object.

• Result set 2 is ordered by table schema/object and index name.

• Result set 3 is for existing covering indexes (ColumnsFound = ColumnsQuery). It's initially disabled.

Indexer

The Indexer SQL file returns information about existing indexes. It also generates SQL statements to (re)create or maintain existing indexes.

There are five variants of the Indexer SQL file:

• Indexer_Examine provides details on potential issues with indexes.

• Indexer_Columns provides a list of all columns used in indexes.

• Indexer_Formats lists indexes in less-commonly-used formats.

• Indexer_DBAdmin assists with maintaining typical indexes.

• Indexer_SQLCode generates scripts for typical indexes.

Storage

The Storage SQL file returns information about database storage configuration and consumption.

• Result set 1 includes information about size/usage for database files and the volumes where they are stored.

• Result set 2 includes aggregated information about size/usage for data files by filegroup.

• Result set 3 includes information about how many tables/indexes are located, in whole or in part, in each filegroup.

• Result set 4 includes information about partition schemes and partition functions.

• Result set 5 includes information about any tables/indexes with partitions.

• Result set 6 includes information about any tables/indexes with ROW/PAGE compression.

• Result set 7 includes information about any tables/indexes in columnstore format.

• Result set 8 includes information about any tables/indexes with storage for any LOB data.

• Result set 9 includes information about any tables/indexes with storage for any row-overflow (ROV) data.

• Result set 10 includes information about index storage consumption, ordered by amount of space consumed.

• Result set 11 includes aggregated information about storage consumption by table.

On a personal note:

The SQLFacts toolkit is a labor of love for me. I have always enjoyed creating technical tools. SQLFacts is the magnum opus of my SQL Server career, simply because it contains all the database tools I wish had existed for the last 20+ years. The tools are carefully designed to provide all the information you need to properly perform various tasks. Those tasks are in the areas of database development, database administration, and performance tuning.

I'm especially pumped over the tools for performance tuning. Sure, there are many unique features in the tools for database development and database administration. However, the tools for performance tuning are truly loaded with useful functionality that I have never seen from any other source, free or paid.

My current work role involves a lot of performance tuning of stored procedures. I use parts of the SQLFacts toolkit every day. They are critical to my productivity. The tools I use the most right now are IndexHistory, Indexer_SQLCode, IndexNeeds, IndexNeedsPlus, QueryHistory, QueryTracker, Threshold, and Sessions. They allow me to quickly find performance tuning opportunities. They also guide me as I make changes to indexes and/or queries.

The powerful features of SQLFacts can increase your productivity as well. Download it now, and master engineering for your SQL Server databases. It's FREE and it's awesome!