Revealed: Our recommendations after a SQL Server audit

When a SQL Server Health and Risk Audit is completed, we then go on to produce a full report and explain the findings to the customer. 

We conduct hundreds of SQL Server Health and Risk Audits every year, for businesses of any size, based anywhere in the world. Irrespective of the company we’re working with, a handful of problems seem to pop up time and time again. In no particular order, here are these key issues and our recommendations to fix them:

1. There is only one data file for TEMPDB 

The purpose of multiple data files for TEMPDB is to prevent TEMPDB contention. TEMPDB contention is one of the most common problems that exist in SQL Server instances. It refers to a bottleneck for threads trying to access in-memory allocation pages. 

TEMPDB contention generally occurs in your Global Allocation Map (GAM), Shares Global Allocation Map (SGAM) and Page Free Space (PFS) pages. These pages are used internally by your SQL Server for allocations. Contention occurs in your memory so the TEMPDB data files can sit together on the same disk.  

You need to split these files if you are faced with a disk performance issue in your TEMPDB files. What’s more, creating extra TEMPDB data files will also add more PFS, GAM, and SGAM intervals to further reduce the contention.  

When splitting your TEMPDB files, we’d recommend you: 

• Make sure your TEMPDB data files are the same size. Otherwise, the proportional fill algorithm will insert the least-used file, causing contention. 
• Go for four or eight data files for TEMPDB. We’d usually recommend you use no more than eight, unless recommended otherwise. Again, those files can sit together unless you’re facing a disk performance issue in TEMPDB files. 

Note: Some articles may say “one file per CPU” but this is a myth, as explained by Paul Randal. Too many files may actually affect your performance. For example, on a server with 64 cores, you wouldn’t want to have 64 data files for TEMPDB. 

When a database is created, when a file (data or log) is added, when a file grows, or when a database or a filegroup is restored, the files are usually zeroed out. By ‘zeroed out’, we mean that they are initialised by being filled with zeroes.  

If the size of the files that are created or the size of the increment is too large, it can take an unfeasible amount of time to fill out the files with these zeroes.  

There’s a feature called IFI (Instant File Initialisation) that could help. IFI only applies to data logs (not log files) and skips this phase of zeroing out the files in order to minimise the duration of the operation taking place on the file. What’s more, concurrent users are also blocked when a file is expanding, to further minimise the impact of this issue.  

To use this feature in SQL Server, your SQL Server startup account needs to have a privilege called “Perform volume maintenance tasks”. To turn this on, you need to: 

• In the Local Security Policy Administrative tool, add the SQL Server startup account to the privilege “Perform volume maintenance tasks”.
• Change the startup account of SQL Server and use a normal domain account with the following list of local privileges: 

• • Log on as a service (SeServiceLogonRight) 
  • Replace a process-level token (SeAssignPrimaryTokenPrivilege) 
  • Bypass traverse checking (SeChangeNotifyPrivilege) 
  • Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) 
  • Lock Pages in Memory 
  • Perform maintenance volume tasks

The default value for this setting is 0. As a result, any given operator of an execution plan can be run in parallel by as many threads as the number of logical processors seen by your SQL Server. Some threads are the producer threads reading some portions of the data. Other threads are the consumers dealing with this data.  

This phenomenon has a negative impact on your performance, potentially because too many producer threads are used. Then, these threads spend a lot of time waiting for each other before the data can be consumed. 

You can identify this phenomenon in an execution plan using the operator PARALLELISM: 

• If the CXPACKET wait type is one of the top wait types, we usually recommend that you decrease the Max Degree of Parallelism value. 
• Then, set the Max Degree Of Parallelism (MAXDOP) to a smaller value such as 4,2, or even 1 for pure OLTP SQL Servers, depending on your testing. 

4. Your Vmxnet3 network card is not properly configured 

Vmxnet3 is the latest generation of para virtualised NICs designed for performance. It offers a range of advanced features including multi-queue support, MSI/MSI-X interrupt delivery, IPv4/IPv6 offloads– and Receive Side Scaling.  

However, we often discover that Receive Side Scaling (RSS) is disabled in the SQL Server environment. In a virtual machine, this RSS feature allows the load from a virtual network adapter to be distributed across multiple virtual processors.  

You can enable RSS by following the instructions in the VMWare whitepaper for SQL Server. 

5. Some user tables do NOT have any clustered indexes 

If your small tables do not to have any indexes, this probably won’t affect the health of your SQL Servers.  

However, as your tables scale up, each table should (at the very least) have a clustered index to ensure that fragmentation is optimised to help performance. On top of the clustered index, non-clustered indexes should also be added to further optimise your queries. 

To achieve this, create a clustered index in HEAPs (which is a term we use to describe a table which doesn’t have any clustered indexes). A good clustered key should be unique, narrow, static and incremental. Furthermore, a good clustered index key is also an identity column. 

In conclusion 

At QUBARK, our Microsoft Certified Masters use a vast range of SQL Server auditing tools to provide your business with the right recommendations. If you’d like to find out more, click here to contact one of our expert team.