Category Archives: Tips

MAXDOP Calculator

Every time I conduct a SQL Server health check, it’s pretty common for me to find wrong configurations at instance level. But one of the most common mistakes I see on the wild, is SQL instances having the “max degree of parallelism” (MAXDOP) set to “1” (single core) , probably this behavior is related to a misconception of how parallelism works or because some DBAs thinks this parameter reduces the “CXPACKET” wait types, who knows …

In order configure MAXDOP properly, DBA’s should read and understand what Microsoft’s has as best practice guideline in the KB# 2806535. This article has a table with the explanation on how to calculate the right value for this configuration: Continue reading

Cleaning up Query Store data after database restore

Query Store is a very powerful tool for performance tuning, if you are not using it already you should!! It will save you a lot of time and effort because it makes the performance troubleshooting easy for DBA’s. It helps you to quickly find performance differences caused by query plan change or a query regression after an upgrade.

One of the caveats of using this tool is that uses space from the PRIMARY file group when enabled, that means if the database is restored to another environment the Query Store data will be restored as well making your database restored version a little bit bigger and containing information that is probably not relevant for a development environment (unless you are doing a troubleshooting). Continue reading

Enable SQLCMD Mode on SSMS

I really like Microsoft philosophy nowadays, they are putting a lot of time developing good open source tools like SQLCLI, SQLOps and Visual Studio Code, those tools has something in common “a built-in terminal” a multi-platform and code geek like me really appreciate those details. It is pretty neat to use the built-int terminal to do stuff without leaving your development environment. This functionality is also available on SSMS (in some way), it is called SQLCMD mode. Continue reading