Category Archives: Performance tuning

MAX DOP 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:

If you are not familiar with NUMA nodes, logical CPUs or CPUs at all this can be a little bit confusing …

NUMA nodes, CPU specs, logical CPU’s, wait …. WHAT???

I understand, we live in a very busy world where we need to do things quick and make everyone’s work easier. Just imagine having to make this calculation every time you want to change the MAXDOP configuration, you truly need something to make your life easier… enter MAXDOP calculator.

The concept of a MAXDOP calculator was created by a Microsoft field engineer long time ago, the article helps you to set the right value for the MAXDOP configuration, the problem I noticed with this tool is that requires a lot of manual intervention. Anytime you would need some help calculating the correct MAXDOP value you will have to visit the website and provide the input from a couple of queries, there is also an Excel version but I do see the same problem. Don’t get me wrong I think this is a wonderful idea but I do personally don’t like manual stuff.

Based on this need, I have created the same calculator but this time in T-SQL. The same concept but in runs in a SQL environment directly, returning the correct MAXDOP value without using any input as the original approach.

Here is an example of the output:

MAXDOP recommendation at Instance level for also T-SQL statement suggestion to re-configure value

At you can see, it provides you the current and recommended MAXDOP configuration also including the syntax to change it in case it is wrong. For SQL Server 2016 or major, it also returns the MAXDOP value configured at database level.

MAXDOP recommendation at DB and Instance level for SQL Server 2016 (and major) also T-SQL statement suggestion to re-configure MAXDOP

If you want to give try it, you can download the T-SQL script from my GitHub repository.

The requirement to execute this T-SQL script is to have SQLCMD mode enabled in SSMS, you can take a look at my previous post to learn how to enable it.

Please feel free to provide me feedback about this tool, stay tuned for more DBA mastery tips!

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.

MSDB Tuning by purging backup and restore history

Have you ever had problem with your backup jobs taking more time than usual overlapping your maintenance window? It is the disk IO throughput fast enough? or it is your SQL Server not performing well? what could be affecting the performance to avoid your backup job to completely in timely fashion?

The answer to all these questions is not that difficult to find, in most of the cases there is a common point of failure for backup jobs taking more time with the past of the time. Your databases are bigger and your backup and restore history has been retained forever (since the SQL instance was created).

Most of the DBAs in the wild doesn’t know about this very important performance tuning aspect, they completely forgot about paying attention to the MSDB database. Probably because this is a system database there is an assumption that does not requires maintenance as any other regular database.

According to Microsoft docs you simply have run the sp_delete_backuphistory stored procedure in MSDB database, providing a date parameter to be used by this process as your oldest date to retain data to …. but guess what it  does not work well and is not that simply.

Before you go for it, you must consider the following aspects:

  • Purging this backup and restore history leads to blocking into MSDB
  • Running the in sp_delete_backuphistory stored procedure causes odd problems to your ongoing or scheduled SQL Agent jobs
  • The sp_delete_backuphistory it’s not optimal and takes a long time to run

So what are your options?

  • Understand what the sp_delete_backuphistory stored procedure is doing and write your own

Probably not the best option, because it will require you to invest time doing research, analysis, design and testing

  • Look for missing indexes in all the MSDB tables purged by the sp_delete_backuphistory stored procedure (Best option due the amount of time you have to invest creating a new process)
  • Schedule a job to purge the backup history during short periods of time after all your daily jobs are completed

This is a good option, because we have identified those indexes beforehand for you and also there is no development effort to re-create a process that already exists. Also you probably can schedule a job to run every weekend to purge 2 weeks of data at the time so the MSDB is not compromised by the purging effort.

Based on our experience adding the missing indexes to all the tables involved in the purge process, we noticed an improvement of 60% of the sp_delete_backuphistory stored procedure. So go ahead and try it and let us know how everything works for you 🙂

Happy purging, stay tuned for more DBA mastery tips.

Note:

  • This indexes works starting from SQL Server 2008 R2 SP3
  • You can find the MSDB Missing indexes script here

I’m very experienced multi platform Database Administrator (Oracle, SQL Server, MySQL), working for large scale US based companies and some other companies around the world.

I have multiple Microsoft SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), and currently taking the Microsoft Data Science program.