Press "Enter" to skip to content

Category: Statistics

Finding Row Counts in SQL Server

Published 2024-04-03 by Kevin Feasel

Kevin Wilkie breaks out the abacus:

Today, I was working with SQL Server to get row counts from several tables so I thought I’d be smart and work with some functions in SQL Server to make it smarter / easier.

Now, if I am truly only getting “straight” row counts from these tables, I would be able to create a query like the below that would provide the answers with no problem:

Read on for the normal approach, as well as a more complicated approach made necessary due to some business logic requirements.

Comments closed

Finding Duplicate Statistics in SQL Server

Published 2024-04-02 by Kevin Feasel

Jose Manuel Jurado Diaz searches for clones:

Some time ago, we encountered a support case where a customer experienced significant delays in updating auto-created and user-created statistics. I would like to share the insights gained from this experience, including the underlying causes of the issue and the potential solutions we identified and implemented to address the problem effectively.

Read on for a demo to set up the scenario and the cause of the problem, as well as how to fix it.

Comments closed

The Risk of Query Failure on Readable Secondaries in SQL Server

Published 2024-02-22 by Kevin Feasel

Kendra Little explains a problem:

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.

This has been the case for a few years, and it’s unclear if Microsoft will fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Read on for Kendra’s thoughts. I haven’t run into this before, myself, but I also don’t tend to make very heavy use of readable secondaries.

Comments closed

Metadata-Based Counting and Filtered Indexes

Published 2023-12-21 by Kevin Feasel

Aaron Bertrand counts more efficiently:

That’s great when you want to count the whole table without size-of-entire-table reads. It gets more complicated if you need to retrieve the count of rows that meet – or don’t meet – some criteria. Sometimes an index can help, but not always, depending on how complex the criteria might be.

For me, counting more efficiently typically means I take off my shoes.

One other note is, if you just need a guesstimate, or if the cardinality of that column you’re splitting by is fairly low, you could also look at the histogram, especially if there’s a statistic on the column (or columns) you’re interested in. It’s rare that I think to go that way, but it is one of the tools the optimizer itself uses, so it’s fair game.

Comments closed

Statistics and Ascending Keys in SQL Server

Published 2023-07-24 by Kevin Feasel

Matthew McGiffen talks stats:

The Ascending Key Problem relates to the most recently inserted data in your table which is therefore also the data that may not have been sampled and included in the statistics histograms. This sort of issue is one of the reasons it can be critical to update your statistics more regularly than the built-in automatic thresholds.

We’ll look at the problem itself, but also some of the mitigations that you can take to deal with it within SQL Server.

Read on to see how this behaved prior to SQL Server 2014’s new cardinality estimator and what has changed since then.

Comments closed

When Statistics Updates Happen

Published 2023-06-28 by Kevin Feasel

Matthew McGiffen gives us the numbers:

SQL Server has had the ability to automatically update statistics since version 7.0. Nonetheless for a long part of my career working with SQL Server, whenever a performance issue raised its head everyone’s knee-jerk response would be “Update Statistics!” In most cases though the people shouting that didn’t really understand what the “Statistics” were, or what mechanisms might already be in place for keeping them up to date.

Of course SQL Server isn’t perfect and sometimes it is helpful for human intelligence to intervene. But to provide intelligent intervention one has to understand how things work.

Read on to learn what triggers automatic stats updates in various versions of SQL Server.

Comments closed

An Overview of Statistics in SQL Server

Published 2023-06-22 by Kevin Feasel

Matthew McGiffen has a primer for us:

Statistics are vitally important in allowing SQL Server to find the most efficient way to execute your queries. In this post we learn more about them, what they are and how they are used.

Read on for plenty of information about stats. One thing I’d emphasize in this is that, for the most part, auto-generated stats are fine. The only time I’ve ever seen any value in creating my own stats is in multi-column statistics (which auto-generated stats doesn’t do), and even that’s of fairly limited value, at least in my experience. I’m sure that there’s a point in which hand-crafting your own statistics makes a tiny but noticeable marginal difference, but the systems I’ve worked with tend not to be anywhere near that point. There are usually much bigger fish to fry.

Comments closed

Troubleshooting High I/O Usage on Azure SQL DB

Published 2023-01-13 by Kevin Feasel

Etienne Lopes troubleshoots a strange issue:

After the downsizing (to GeneralPurpose: Standard-series (Gen5), 2 vCores) occasionally there were timeouts in the application for a very specific task (the command timeout property in the application was set to 30 seconds). Other times the very same task would execute immediately, as it should always, since the underlying query was actually quite simple: a SELECT to a single, although large table (58 GB) but with a predicate that would always result in a perfect index seek to return never more than 300 rows. Furthermore each time there were timeouts, there were also momentary I/O spikes up to 100%:

Read on to learn more about what caused this problem and how Etienne was able to resolve it.

Comments closed

Auto-Drop Stats in SQL Server 2022

Published 2022-12-20 by Kevin Feasel

Dennes Torres takes out the trash:

Auto-Create statistics is a well-known feature critical for the database performance. When one statistic is auto created, changing the table structure is not blocked by the presence of the statistic. An auto-created statistics is also dropped automatically when a schema change happens.

On the other hand, if the statistic is created by the user, any schema change will be blocked by the presence of the statistic.

The Auto-Drop setting on a statistic is a new SQL Server 2022 feature to change this behaviour.

Click through for a demonstration.

Comments closed