Category: Administration

Randolph West explains that you should set a couple switches on SQL Server installation:

Did you just say to yourself that you don’t install SQL Server on Windows using a configuration file? Do you click through every option and set it manually every time? Please don’t do that. Use a configuration file instead.

Randolph gives great advice here.

Milos Radivojevic is excited about OPTIMIZE_FOR_SEQUENTIAL_KEY:

The results show that the feature should be used for tables, where latch convoys already happen or are about to happen. In a not-so-heavy workloads, it brings a small overhead. You can see that inserts with 100 parallel sessions are 16% slower for a table with the optimized sequential key. However, when you hit the convoy, the feature definitely helps.

This graf is critical: if you don’t have high enough concurrency on the table, insertion can be a little slower than otherwise, so don’t go setting this for every table.

John McCormack has three things new DBAs should learn first:

Help, the SQL Server is on fire and my really important process is not working. Why is the server so slow?

This scenario will happen to you. Invariably, the person (often a manager) will stand over you and expect you to knock out one or two lines of t-sql wizardry to get things running along smoothly again. First of all, I should say that in a perfect world, you will ask them to raise a ticket and you will work on it according to its priority against your other tasks. Then you can let them know what the issue was and what can be done to prevent it happening again. But we rarely work in a perfect world. In this scenario, you need one primary line of t-sql to get started.

John’s three points are a really good starting point.

Aaron Bertrand looks at a couple things you can configure on installation with SQL Server 2019:

One of the long-standing defaults in SQL Server has been the maximum degree of parallelism (MAXDOP), which has always been 0, meaning use (up to) all cores when the engine believes that will help. For many OLTP workloads, 0 is not the optimal setting, and you may want to use a different number depending on the behavior of your workload. I don’t want to belabor the thought process here, but this will be based on settings like the number of cores exposed to SQL Server, whether they are divided into NUMA nodes, and if there are other instances, applications, or services running on the same Windows Server. Microsoft’s guidelines are published here.

Aaron shows us how to set MAXDOP as well as min and max server memory for our new instance. It’s nice to see these types of additions to the setup process—that makes it a bit more likely that the DBA who installs instances only occasionally doesn’t forget to set these afterward.

Mike Hays points out that setting the number of error log files is something which resets on SQL Server upgrade:

This morning when working with one of my SQL Server servers, I went looking for an error log from last August.  After finding that I was only able to go back six days, I checked the directory that stored SQL Server’s error logs,  and noticed a gap in the logs from October to last March.

When I checked the configuration for the error logs in SQL Server Management Studio, I saw that the setting was configured at the default setting of 6 error logs for history.

Read on to understand why this happened.

Adrian Buckman has a new stored procedure:

Sometimes you just want to get a quick row count for a specific table – other times you may want to see a list of tables order by size or row count or perhaps you are monitoring table row counts, whatever it is the chances are you have a script or various scripts stashed away and maybe like me you end up re writing parts of them to suit your requirements.

I got bored of that game so I decided to write a stored proc to make things easier.

Read on to see the procedure in action and then check it out in the Undercover Toolbox.