A Fine Slice Of SQL Server
Matthew McGiffen never promised us there would be no math:
Before using values from the statistics, the Optimizer will check to see if the statistics are “stale”, i.e. the modification counter exceeds a given threshold. If it does, SQL will trigger a resampling of the statistics before going on to form an execution plan. This means that the plan will be formed against up to date statistics for the table.
Read on for some experimentation on when stats updates kick in.
1 CommentAt first glance, the question of how many rows are estimated from a table variable is easy.
But, is it really that simple? Well, not really. To dig into the why, first we need to identify why table variables estimate 1 row. The obvious answer is because they don’t have statistics. However…
Read on to learn the real answer.
Comments closedErik Darling compares cardinality estimators:
Look, I’m not saying there’s only one thing that the “Default” cardinality estimator does better than the “Legacy” cardinality estimator. All I’m saying is that this is one thing that I think it does better.
What’s that one thing? Ascending keys. In particular, when queries search for values that haven’t quite made it to the histogram yet because a stats update hasn’t occurred since they landed in the mix.
Read the whole thing.
Comments closedBrent Ozar lays out an important point:
You might say, “But SQL Server has statistics on those columns, and it knows what the top values are!” Well, that’s true, but…data can change without the statistics being updated. For example, say that one user logs in right now, and then we run the MAX query again:
Statistics tell the engine what they learned at the time they were ran. If you need guarantees, that’s what constraints are for.
Comments closedIn Synapse Analytics, several database objects (such as, compiled procedure, plan, …) will be cached in some conditions.
For instance, CCI tables (see my previous post “Azure Synapse Analytics : Choose Right Index and Partition” for CCI) will locally cache the recently-used columnstore segments on distributed compute nodes, which is called columnar cache. The local disk-based cache is used on Gen2 caching.
You cannot manually control these caching activities. (These are automatically applied to improve performance in Synapse Analytics.) See team blog “Adaptive caching powers Azure SQL Data Warehouse performance gains” for underlying architecture which improves caching in Gen2.
Dedicated SQL Pool behavior is close enough to on-premises SQL Server that it’s easy to expect everything to be the same, but there are some nuances.
Comments closedLonny Niederstadt shares an interesting scenario:
Consider a snapshot database which is created daily. The purpose is to provide analytics reporting access while maintenance or data loads take place in the source database. In the snapshot database, analytics reports have no locking concerns from the activity in the underlying source database. And the temporary statistics provided by SQL Server, combined with the statistics inherited from the source database, provide a lot of information to the optimizer for query plan selection.
But what if significant fact tables are queried in the snapshot and leave a situation like col2 in stats_test? A column which generates an auto-created stat in the snapshot, but never gets a statistic created in the source database. Each day, the cost of creating that statistic and every statistic like it will be paid as part of the workload. Even if the underlying table is a now-stable dimension.
Click through for the demonstration.
Comments closedJohn Sterrett takes us through a bug in SQL Server:
When a bug jumps out and surprises me I like to share it so others do not run into the same unexpected result. I don’t think there is any reason for a bug to bite multiple people in the butt. Therefore, I want to show you why persist sample percent IS NOT PERSISTED!
Read on for the repro and upvote the Feedback item.
Comments closedJoey D’Antony takes us through stats management with the serverless tier of Azure SQL Database:
One of the only things platform as a service databases like Azure SQL Database do not do for you is actively manage column and index statistics. While backups, patches, and even integrity checks are built into the platform services, managing your metadata is not. Since Azure SQL Database lacks a SQL Sever Agent for scheduling, you have to use an alternative for job scheduling.
Read on to learn about techniques as well as a few gotchas.
Comments closedDimitri Furman announces a change in Azure SQL Database:
In Azure SQL Database and Azure SQL Managed Instance, the background process that updates statistics asynchronously can now wait for the schema modification lock on a low priority queue. This improves concurrency for workloads with frequent query plan (re)compilations.
New behavior is enabled with the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration. This feature is currently in public preview.
Dimitri does a good job of explaining what this means and how it can make life a little better for people querying tables with statistics updates.
Comments closedDeborah Melkin shows us how to see if a particular statistic is in use:
You know those tweets that you see once but can never find again? I remember seeing one a while ago where someone tweeted to #sqlhelp asking if the internal inserted and deleted tables had statistics or if they were like table variables, which didn’t.
This is a great question in general. But then it got me thinking – how do you prove this? I wanted to know the answer as well so I decided to look into this. And I went down the wrong sort of rabbit hole trying to figure this out. Eventually I talked to a friend about this and got pointed in the right direction…
And the answer to how you find which statistics are used is…?
Read on for the answer and several examples.
Comments closed