Performance

Performance Improvements in .NET 6

by

There have been a large number of Performance Improvements in .NET 6 as evidenced in Stephen Toub’s blog post. Most of the time we don’t care about assembly level performance optimisations because the bottleneck is usually accessing some external resource, such as a database or web service.

If you need to benchmark .NET code take a look at a great tool, BenchmarkDotNet and also take a look at the book referenced there, Pro .NET Benchmarking as getting benchmarking correct can sometimes be quite tricky.

If you’re not yet on .NET 6, and you have code in a large loop that you want to squeeze some performance out of it (and it won’t make the code hard to understand and maintain!) here are a couple of simple tips:

  • Testing if n is even: replace (n % 2 == 0) with ((n & 1) == 0)
  • Dividing by 2: replace n / 2 by n >> 1 (but be aware of the unsigned / signed behaviour of right shift)

Tuning SQL Server Queries 101

by

First, get an actual query execution plan. Look for warnings in the query plan.

Basic

  • First look for large scans and lookups: these can often be resolved by creating a new index or extending an existing one with additional included columns. Seeks are usually preferable to scans.
  • Then look for significant variance of Actual versus Estimated row counts: you can provide the optimiser with more accurate information by updating statistics, creating new statistics objects, adding statistics on computed columns, or by breaking the query up into simpler parts. Might be caused by ‘parameter sniffing’.
  • Then look for expensive operators in the query plan, especially those that consume memory such as sorts and hashes. Sorting can sometimes be avoided by altering/adding indexes.

More Advanced

Joe Sack has an excellent walkthrough here: The Case of the Cardinality Estimate Red Herring

SQLFrontline: Snapshot SQL Server Configuration

by

Taking a snapshot of a SQL Server’s configuration, enables you to see what changes over time. It can also provide a record of the date changes were made, so that you can correlate if problems occur and determine if a change might be to blame. It’s also a great way to document any fixes you have made.

An example: some months ago I had generated a SQLFrontline report against a server I had been asked to look at and update to industry best practices. Some time after the work had been done, I re-ran the report and discovered that someone had turned on SQL Server’s ‘Priority Boost’ setting since the previous data collection! (You should never turn this setting on):

“Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.”

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-priority-boost-server-configuration-option

SQLFrontline currently performs 310+ checks looking at reliability, performance, configuration, security, database design and emails you the results, with clear instructions on what needs attention.

SQLFrontline Case study: NUMA Configuration

by

A little while ago, I was doing SQL Server consultancy work for a large organisation here in Perth, Western Australia. They had a sizeable physical SQL Server machine for their business reporting needs: 48 cores (4 sockets of 12 cores each and 384GB of RAM).

I discovered that despite the company having paid for Enterprise licenses for 48 cores, the licensing had not been applied, and so only 40 cores were actually in use! It had been that way for almost 2 years…

In fact, the situation is worse than it first seems: not only were 20% of the cores not being used, but the cores were partitioned into 4 NUMA groups, having 3 groups of 12 cores and one group of 4 cores. With the workload distributed equally over the 4 NUMA groups, this was obviously very unbalanced, and detrimental to the server’s throughput. 

In addition, ‘max degree of parallelism’ was set to its default value, which means that a parallel query that spans the 12 core and 4 core NUMA groups will very likely have threads waiting in the 12 core NUMA group and possibly incur foreign memory accesses.

This is just one of the NUMA configuration checks that SQLFrontline runs.

How can SQLFrontline help me as a DBA, Accidental DBA or Developer?

by

If you’re doing a good job as a DBA you want to have an in-depth knowledge of all the SQL Servers you are responsible for. That includes the SQL Server hardware, configuration and performance. You want to be pro-active and not swamped. You want active notifications of when things change or fail, and ideally you would like this documented, so you can show it to your boss!

Rather than you having to remember to run checks on servers (and when you get busy, it’s easy to miss something), SQLFrontline does this for you on a scheduled basis (usually once per day, but configurable). You don’t have to install any software or configure any local repositories.

As an accidental DBA (despite reading blogs and investing time learning SQL Server) you might not be aware of everything required to make SQL Server reliable and perform well. SQLFrontline embodies SQL Server industry best practices. Not only does it notify you of problems found, but it also explains the problem and shows you how to fix them.

As a developer, you want to be made aware of any design decisions that could affect performance, and/or track down any existing performance/blocking issues.

I use SQLFrontline in my day-to-day SQL Server consultancy business. It generally takes me less than an hour to do what would have previously taken over a week (running and documenting the results of over 300 checks can be time consuming). I recently used SQLFrontline to efficiently diagnose and fix all the SQL Servers for an entire business, making them standardised and industry best practice in the process.

SQLFrontline: a Diagnostic and Monitoring Service for all your SQL Servers

by

If you’re an overworked DBA (or a DBA that wants to be responsible for more servers and databases), an accidental DBA, or a developer wanting to get a better handle on your SQL Server and databases, SQLFrontline can help you do that.

SQLFrontline is a lightweight, low impact, nothing to install diagnostic tool for all SQL Server versions 2008+. It carries out over 300+ checks (more checks are added frequently), across the areas of security, reliability, performance, configuration and database design. Issues are organised by server and by priority (Critical, High, Medium, Info).

It compares and notifies you what was fixed between data collections. It has a built in reminder list for high priority items that haven’t been fixed over time.

It even has a demo mode, so you can try it out for free.

Here’s an excerpt from an example notification email:

Managing Performance Enhancing Indexes for Third Party Databases

by

IT staff are sometimes reluctant to make any changes to third party databases, as they are worried that doing so will invalidate their support agreement. But sometimes it’s essential to create indexes for third party databases to improve performance/blocking.

You might ask, “Why doesn’t my third party database vendor have all the required indexes in place already?”

There are several reasons:

1) The third party’s developers and testers don’t always have access to large enough data sets.
2) End users (you) might not all have the same usage patterns, data distributions or data volumes.
3) End users might not have channels to give the vendor performance feedback.

Assuming we have identified an index (or indexes) that would increase performance, we want to apply these but not interfere with any future upgrades supplied by the vendor.

The simple solution is to create two scripts in source control: one to create required indexes, and another to remove those indexes prior to any vendor upgrades.

After a vendor upgrade, we examine the database’s indexes, and if the vendor still hasn’t created the indexes we require for our workload, we simply re-create our own version again.

SQL Server: Don’t Make the Query Optimiser’s Job More Difficult

by

Part of my job is tuning complex queries: I’ve seen some recently with eye watering complexity. A post from Erik Darling explains how abstraction can be the cause of poor performance in SQL Server:

Sometimes it’s views, CTEs, or derived tables. Sometimes it’s functions. obviously functions can have a weirder set of effects, but the general idea is the same.

If you start chaining things, or nesting them together, you’re making the optimizer’s job harder and likely introducing a lot of overhead.

There’s no “caching” of steps in a query. If you nest a view however-many-levels-deep, each step isn’t magically materialized.

Same goes for CTEs. If you string a bunch together and reference them multiple times, you’ll start to see some very repetitive branches in your query plans.

Now, there are tricks you can play to get what happens inside of one of these steps “fenced off”, but not to get the result set fully materialized.

In addition, as your query becomes complex, the query optimiser eventually gives up and produces a less than efficient query plan because there are too many potential query plans to choose from.

Erik references Grant Fritchey’s post from 2012, The Seven Sins against TSQL Performance, which is still as relevant today.

SQL Server: Poison Waits

by

SQL Server performance tuning often starts by examining your top wait statistics. There are certain wait types where even a small number of occurrences can indicate performance problems. These are termed Poison Waits.

RESOURCE_SEMAPHORE_QUERY_COMPILE
A query was sent to SQL Server, and there wasn’t an execution plan for it in the query plan cache. In order to create an execution plan, SQL Server requests a small amount of memory, but due to memory pressure the requested memory wasn’t available. So SQL Server had to wait for memory to become available before it could even build an execution plan, let alone execute the query. In this situation, cached query plans and small un-cached plans may be able to run depending on how much pressure the server is under, but complex queries will experience memory request waits and feel sluggish.

RESOURCE_SEMAPHORE
SQL Server compiled an execution plan (or retrieved the query plan from cache), but now it needs memory to actually execute the query (a memory grant request). If other queries are already using a lot of memory, then our query won’t be able to start executing because there is insufficient memory available. Similar to the RESOURCE_SEMAPHORE_QUERY_COMPILE wait, smaller queries may be able to execute, but complex ones will be blocked from executing and wait for memory to become available.

THREADPOOL
At startup, SQL Server creates a predefined number of worker threads based on how many logical processors the server has (each worker thread uses 2MB of memory). As queries arrive, they get assigned to worker threads. If enough queries queue up, such as when queries get blocked, you can run out of available worker threads (worker thread starvation). You might be tempted to increase max worker threads (and Microsoft support sometimes makes this suggestion), but then you might simply escalate the problem to a RESOURCE_SEMAPHORE or RESOURCE_SEMAPHORE_QUERY_COMPILE issue. Blocking is the most common culprit of THREADPOOL waits, but it can also be due to a large amount of connections trying to run queries at the same time. If you are unable to connect to SQL Server to troubleshoot because of worker thread starvation, try connecting using the Dedicated Admin Connection.

Whenever any of these poison waits occur, you have to get to the root cause of the problem. For a list and explanation of the various waits: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql