What is it?

SQLFacts is a FREE suite of 26 (and counting) tools for SQL Server database development, database administration, and performance tuning. The toolkit is all T-SQL code, so nothing is hidden and everything can be reviewed. The tools do not modify the server or databases in any way. They are strictly read-only (except for the monitoring system).

Why was it created?

SQLFacts was created to solve a recurring problem. It's very typical for a SQL Server database professional to have a large library of scripts for their work. In many cases, the scripts come from online articles and blog posts. Unfortunately, the scripts were written by many different authors using many different styles. The authors had different design goals, used different formatting, followed different conventions, and applied different levels of attention to detail. The authors were often trying to make a technical point. Their scripts were designed for that specific purpose. They were not creating a general purpose routine. The specificity explains why a large library is necessary. There's a script for this, a script for that, and a script for the other thing.

Frankly, it's often more work than it's worth to find the right (very specific) script in a large library and then modify it for your situation. It's often less work to write your own script instead of trying to adapt a script written by a different person for a different reason in a different environment. Inevitably, time constraints prevent making a general purpose routine, so you end up creating yet another script for a very specific purpose. Eventually you begin sharing your scripts with others, often through online articles and blog posts, and the cycle repeats.

The goal of SQLFacts is to end the cycle, or at least substantially reduce the repetition. It's a lofty goal, but it has many benefits.

What does it do?

SQLFacts provides the functionality of hundreds of very specific scripts combined. The functionality is neatly organized into tools for various tasks. The tools were designed and developed to be part of the suite. They were written by one author (me) using consistency in T-SQL coding style. The T-SQL code is meticulously formatted. All the tools follow the same conventions with great attention to detail. They are much more robust and extensive than slapped together, piecemeal, blog post scripts. The tools were intended to provide all the necessary information for a certain task, instead of providing bits and pieces of information that must be assembled to be useful.

What is it like?

There are several well-known free tools available for working with SQL Server. Each one provides all the necessary information for a certain task. They were written by very smart people who are well known in the SQL Server community. However, those tools are still at a level between blog post scripts and SQLFacts. Each one serves the intended purpose, but each one was written by a different person using different software design patterns. SQLFacts includes the functionality of those tools AND a lot of other functionality. You get it all with one download. It all comes with consistency in style, conventions, and software design patterns.

How did it begin?

The ideas behind SQLFacts have been in the works for 20 years. I started working with SQL Server in 2001. I have been in several different roles with many different organizations, but certain tasks were common across many of my positions. Those tasks were often best performed by writing SQL statements to query system catalog views (SCVs) and Dynamic Management Views (DMVs). I found that I was writing the same SQL statements over and over and over, with only small variations for the current need.

The repetition seemed like an inefficient approach, but everybody else was doing essentially the same thing. In almost every position I have held, everybody was maintaining their own library of scripts. A few people were also using some of the well-known free tools mentioned above. It was hard to compare notes while researching problems because everybody was looking at different subsets of information and/or the information was in different forms.

Why is it better than what I'm doing now?

SQLFacts lets you avoid the lost productivity of maintaining your own library of scripts. It also improves coordination among a team of people because they are using a standard set of tools. It's much easier to communicate thoughts and ideas when everybody has the same information in the same form.

SQLFacts is also a great way to learn more about how SQL Server operates. You can examine the T-SQL code of the tools themselves to learn about SCVs and DMVs. Again, that's MUCH easier to do when the all the tools have a consistent look and feel. It's even more revealing to execute simple T-SQL statements against your database(s) and see how they change the result sets of various tools.

The SQLFacts tools consist of T-SQL code. There is no GUI, other than SSMS itself. If you prefer a GUI for these operations then a set of commercial tools would be a better choice for you.

SQLFacts (http://www.SQLFacts.com), the whole suite of tools, is absolutely FREE with no strings attached! It's my way to "pay it forward" to the SQL Server community. I have learned from the published work of others over the years and now I would like to return the favor for newer SQL Server database professionals.

Here's a VERY brief description of each tool in the suite:

Mostly for database development...

SQLFacts is the name of the suite, but it's also the name of a tool. The SQLFacts tool returns almost 50 result sets by itself. The result sets provide vast information about a single database. Some of the result sets are detailed lists of what exists. Some of the result sets contain a lot of analysis. There are several result sets to identify many "SQL Code Smells" and database architecture smells. There's a tiny utility application to export all the result sets to HTML pages, with a table of contents page, to serve as documentation for the database.

The Browse tool and the References tool dig deep into specific tables and routines (stored procedures, functions, views).

The Search tool allows you to search the T-SQL code of routines for a specified string. You can choose between maximum speed and using LIKE wildcards. You can optionally exclude code comments. The result set is the actual matching lines with line numbers.

The GenerateKeys tool and the GenerateSQL tool generate T-SQL code for database objects.

Mostly for database administration...

The BACKUP tool provides information about BACKUP history. The RESTORE tool takes a BACKUP_ID from the BACKUP tool and generates a complete RESTORE statement.

The MetricsHistory tool is a complete monitoring system. It monitors many performance counters, wait stats, file stats, and settings. The tool keeps a history to analyze patterns and trends. It includes alerting for abnormal conditions. The MetricsNow tool is an on-demand form of the MetricsHistory tool.

The Auditor tool provides information about server/database audits.

The SQLAgent tool provides information about SQL Server Agent jobs, including a somewhat visual representation of schedules.

The Bufferin tool provides information about the contents of the SQL Server buffer cache.

The Databases tool provides information about your databases, including size and usage details. The information is provided at different levels (database, filegroup, and file).

The Sessions tool provides information about what's currently running, including CPU usage, RAM usage, tempdb usage, and transaction log usage.

The Resources tool is for on-demand monitoring of RAM usage and tempdb usage.

The Blocking tool is for on-demand monitoring of blocking issues.

The Locksmith tool provides information about current locks held by sessions.

The Deadlocks tool provides information about recent deadlocks.

The AGLatency tool provides information about delays in propagating data with Availability Groups.

Mostly for performance tuning...

The Statistics tool provides information about index statistics objects.

The QueryHistory tool provides information about resource usage for ad hoc SQL statements and stored procedures.

The IndexHistory tool provides information about indexes, including definitions, usage statistics, and potential redundancy.

The IndexActivity tool provides information about indexes, including low-level performance statistics to analyze access patterns and potential issues.

The IndexNeeds tool provides information about "missing" index suggestions from SQL Server itself.

The IndexNeedsPlus tool provides information about which stored procedures contributed to the "missing" index suggestions, which is not otherwise available in versions prior to SQL Server 2019.