FAQ

Why publish a script, shouldn't Azure SQL Database have a feature to produce these tips?

We decided to publish a downloadable script as an open source project for the following reasons:

1. This lets us add new tips quickly, in a matter of days, based on specific customer scenarios we encounter, and not waiting for a full engineering cycle to get a tip released. This produces immediate value for our customers.
2. This lets us use community feedback to add new tips and improve existing tips quickly. This GitHub repo serves as a direct channel for our customers and the community to share suggestions and ideas. For those who are interested, it also provides an opportunity to contribute code.
3. Executing a script on demand lets you control when and where (on which database) to get tips to make them most relevant to you. You get tips immediately, and can keep running the script to see if new tips appear as database state changes. For example, you can run the script when a critical workload is running, or when an intermittent performance issue is observed, or when you are testing a new application release or some design/schema changes.

Why add another best practices script?

There are several popular scripts for checking MSSQL configuration, design, and performance best practices. However, most of them are written for SQL Server, and some do not even run in Azure SQL Database. Our script focuses on Azure SQL Database specifics in addition to general MSSQL database engine best practices. Some of the tips our script produces are based on metadata that is only available in Azure SQL Database.

Why did you add a tip for X?

All tips we added so far are to address issues we have commonly seen working directly with Azure SQL Database customers.

Why did you not add a tip for Y?

Possibly because none of the customers we worked with had encountered that issue, and we haven't thought about adding a tip for it yet :). New tip ideas, created as issues in this repo, are always welcome.

Also, we wanted the script to be lightweight and quick. Because of that, it only produces tips based on the database state at execution time. If a potential tip requires historical data not available in system views, or must run for a long time to collect this data, we might have opted not to implement it for those reasons.

I ran the script and didn't get any tips. Is something wrong?

Not necessarily, this could just mean that your database is designed and configured perfectly :). It could also mean that a representative workload wasn't running when you executed the script, or perhaps you ran the script on a smaller test database that is different from the production database. You might have also excluded too many tips, or did not have sufficient permissions to execute all queries to produce tips. In the latter two cases, you will see a warning in the output listing the tips that were skipped.

Some tips are produced only when certain thresholds, defined in the configurable thresholds section in the script, are exceeded. We set default threshold values for scenarios we consider most common. Feel free to experiment adjusting the thresholds, and open an issue or a pull request if you think that the defaults should be changed.

To see all tips the script can possibly produce, regardless of whether they are applicable to your database, set the @ReturnAllTips variable to 1 and rerun the script.

Are you sure that nothing will break if I act on these tips?

No, because any action (or inaction) can break things. We include the confidence_percent column in the script output to show our degree of confidence that a tip is valid and you should act upon it. However, regardless of the confidence_percent value, you should read descriptions on the Azure SQL Database tips wiki page for the tips you get. If you are unfamiliar with the technical background, research the topic enough to understand whether the tip applies to your situation. The links we provide on the wiki page help you do that. The "test and have a rollback plan" rule fully applies if you decide to implement a change based on a tip, just like it applies to any other change to database design, configuration, or operations.

Please keep in mind that tips are based solely on current metadata in the database. The script has no knowledge of other data points that may be important to you, such as changes to schema, design, configuration, or workload that may occur in the near or more distant future, or past experiments you might have done that validate or invalidate a tip produced by the script. You should always consider each tip in your specific context.

Can running the script change my database?

No. The script is read-only, with the exception of using small temporary tables in tempdb. The minimum permissions the script requires do not give it the ability to change anything in the database.

How is this script supported?

For support topics directly related to the script, please open an issue in this repo and describe the problem in detail. Please note that support is provided on best effort basis and without any SLA. This script is not supported by Microsoft Customer Support.

If you need help implementing a tip, or have a general MSSQL technical question prompted by a tip, start by reviewing the links for that tip on the wiki page. If you still have questions, the best way to get answers is to post a detailed question in a community supported forum, such as azure-sql-database on Microsoft Q&A, or on Stack Overflow tagged under azure-sql-database.

I found a bug, what should I do?

If you find a bug, or if you get a tip that seems wrong or not applicable to your situation, or if the wiki description is insufficient or confusing, please open an issue and describe the problem in detail, so we can improve the script and wiki for everyone. You can also open a pull request to propose a code fix.

Does it work on Azure SQL Managed Instance or on SQL Server?

The script was written for Azure SQL Database. While we have not added any tips specifically for Managed Instance, the script will execute there, and may produce tips for the current database. Some tips will never be produced on Managed Instance because they are only applicable to Azure SQL Database, or require system metadata that is not available on Managed Instance.

The script does not work on SQL Server, because several views it uses do not exist in any version of SQL Server, and also because some T-SQL constructs the script uses may not be available in your version of SQL Server. BPCheck is a SQL Server best practices script you can use.

Why did you publish this as a script and not as a stored procedure?

We wanted to avoid creating new objects in customer databases. That would require additional permissions besides the minimum set of permissions the script requires to produce tips. If you want to have this code available as a persisted module in your databases to execute on demand, you can easily create a new stored procedure (suggested name: spGetSqlDbTips) using the code we publish, and update it as new script releases appear.

How can I see the raw data you use to produce a tip?

For many tips, the script outputs additional data in the details column. This is aggregated data, formatted for presentation purposes and augmented with explanatory text. But sometimes you may prefer to see more detailed and granular raw data. In that case, you can find the query we use to produce the tip in the script (use the tip_id numeric value to search script code), remove the part of the query that aggregates detailed data, and run the query separately. You can also write your own queries, using queries in the script as a starting point.