Press "Enter" to skip to content

Category: Performance Tuning

High Memory Grants in SQL Server

Published 2022-09-30 by Kevin Feasel

Ajay Dwivedi tracks down memory grants:

Very often, specially on mixed workload enviroment where server deals with oltp & olap queries together, concurrent sessions allocated with High Memory Grants cause entire SQL Server workload to suffer.

To detect such a situation, I like to watch my critical metrics dashboards. For example, dashboard Monitoring – Live -All Servers of free SQLMonitor tool highlights all these metrics.

Click through for an example of the dashboard as well as a diagnostic query which helps find high-memory grant queries.

Comments closed

Performance on Azure SQL DB Standard Tier

Published 2022-09-26 by Kevin Feasel

Reitse Eskens continues a series on performance comparisons for Azure SQL DB:

This tier is more expensive than the basic, but starting at 12 euro’s per month up to 3723 euro’s you have a wider range of spending your money and with that a wider performance range. The standard tier is suited for general purpose workloads and can be compared with the general purpose tier whereas the latter works with cores. Standard tier works with DTU’s. The concept of a DTU is a difficult one as the documentation states it’s a blend of CPU, Memory, reads and writes. If you hit a limitation, you’ll be throttled. You can read more about the DTU model here.

One thing I wish Reitse had done in the images was to show them in log scale—there’s a consistent L curve for each (which is good) but makes it hard to see anything after about Standard S4.

Comments closed

Improving SQL Server Backup Performance

Published 2022-09-23 by Kevin Feasel

Glenn Berry makes some recommendations:

Does making your SQL Server database backups twice as fast sound interesting? SQL Server 2022 has new options to help you improve SQL Server database backup performance. If you are on an older version of SQL Server, you still have options for Improving SQL Server Database Backup Performance.

In order to improve your database backup performance, you need to understand what is happening during a database backup and what your bottleneck(s) are.

Read on for an overview of the key considerations.

Comments closed

SQL Server Health Checks with SQLMonitor

Published 2022-09-19 by Kevin Feasel

Ajay Dwivedi performs a server health check:

Working as a DBA, I often get pulled into issues where application teams complain about “database server is slow”.

This general statement “server is slow” requires an equally robust approach that can help us figure out if there is an issue with CPU, memory, io, or regressed query. At this point, I prefer to use my SQLMonitor dashboard. This tool is entirely free and open source, and can be deployed on SQL Server 2014+ irrespective of any environment or edition.

Read on to see how it works and check out the GitHub repo as well as a one-off script you can run if you don’t have SQLMonitor set up.

Comments closed

Updates to Intelligent Query Processing in SQL Server 2022

Published 2022-09-16 by Kevin Feasel

Derek Wilson and Kate Smith dive into IQP updates:

SQL Server 2022 introduces a handful of new members to the growing Intelligent Query Processing (IQP) family. These additions range from expanding and improving various query performance feedback mechanisms to adding two new features—parameter sensitive plans (PSPs) and optimized plan forcing. In this blog, we give a general overview of these additions, with detailed feature-specific blogs to follow.

Read on to see what they have in store.

Comments closed

Inverted Indexes for Full-Text Search

Published 2022-09-09 by Kevin Feasel

Maria Zakourdaev twists some text inside-out:

Sometimes there are properties in the document with unstructured text, like newspaper articles, blog posts, or book abstracts. The inverted index is easy to build and is similar to data structures search engines use.

Such document structures can help in various complex search patterns, like common word detection, full-text searches, or document similarity searches, using humming distance or l2distance algorithms. Inverted indexes are useful when the number of keywords is not too large and when the existing data is either totally immutable or rarely changed, but frequently searched.

This post and Maria’s MSSQLTips post both cover the high-level concept, focusing on tradeoffs between different data models. I like this sort of idea a lot and like telling people that sometimes, the right answer in a relational database involves thinking backwards.

Comments closed

Sources of Query Plan Duplication

Published 2022-08-26 by Kevin Feasel

Eitan Blumin has a script for us:

Running the above script with the parameter @RCA set to 0 will output a list of the top query hashes (sorted based on their size in bytes so that you’d see the most impactful queries first) that have multiple different query plan hashes cached for them.

Running the script with the parameter @RCA set to 1 will output the same list, with additional columns that will help you do deeper root cause analysis.

Unlike the similar script provided by Brent, my script counts the number of query plan hashes rather than the query plan handles per each query hash. I found that this results in far fewer “false positives”. But you can still set the parameter @CountByPlanHandleInsteadOfPlanHash to 1 if you want it to count based on query plan handles instead.

Click through for the script, details on how it works, and various classes of reason why you might get to this outcome.

Comments closed

A Critique of Parameter Sensitive Plan Optimization

Published 2022-08-26 by Kevin Feasel

Brent Ozar is not amused:

Parameter sniffing is a notorious problem for Microsoft SQL Server because it tries to reuse execution plans, which doesn’t work out well for widely varying parameters. Here’s a primer for the basics about how it happens.

SQL Server 2022 introduces a new feature called Parameter Sensitive Plan optimization. I’m not really sure why Microsoft capitalized the first three letters sometimes and not others, or why they only abbreviate it as “PSP optimization” – as long as we’re abbreviating, why not call it PSPO? Optimization is the word in there with the most syllables – why the hell would we leave that one as the one that isn’t abbreviated?

Because I make things go faster for a living, we’re gonna call it PSPO. You’ll see how I pronounce it later, and why.

Erik Darling has also spent a lot of time talking about this. And at this point, I don’t know that how many (if any) of Brent’s critiques get fixed before RTM.

Comments closed