The SQL Server query optimizer makes use of statistics during query compilation to help determine the optimal query plan. By default, if the optimizer notices a statistic is out-of-date because of too many changes to a table, it will update the statistic immediately before query compilation can continue (only the statistics it needs, not all the statistics for the table).

Note that “too many” is non-specific because it varies by version and whether trace flag 2371 is enabled – see the AUTO_UPDATE_STATISTICS section of this page for details.

The Problem With Synchronous Statistics Updates

Synchronously updating statistics before compilation obviously introduces a delay and makes the query take longer to compile and execute. Just how big of a delay depends on several factors, including:

• How many tables involved in the query have reached the “too many changes” threshold
• How many statistics for each of those tables have to be updated because they’re needed for compilation
• How many rows there are in the tables involved
• The options specified when each statistic was created (e.g., FULLSCAN and PERSIST_SAMPLE_PERCENT=ON)

So, there can be a seemingly random delay, which might cause problems in some scenarios, especially if an application has a very low query timeout set.

Avoiding Synchronous Statistics Updates

There are various ways to avoid synchronous statistics updates, such as:

• Setting AUTO_UPDATE_STATISTICS to OFF, which turns off all automatic updates and means you’ll need to perform your own statistics maintenance to avoid the possibility of sub-optimal query plans from out-of-date statistics.
• Setting AUTO_UPDATE_STATISTICS_ASYNC to ON, so when the optimizer notices a statistic needs to be updated, it continues with compilation, and a background task updates the statistic a little bit later. This only works if you also have AUTO_UPDATE_STATISTICS set to ON.
• Perform regular statistics maintenance, so automatic synchronous or asynchronous statistics updates don’t happen at all.

There’s a lot of debate in the SQL Server community around whether to enable asynchronous statistics updates. I asked my lovely wife, Kimberly L. Tripp, what her opinion is, and she always recommends enabling it, and she’s forgotten more about statistics than I’ll ever know, so I believe her.

Tracking Synchronous Statistics Updates

There has never been an obvious way to tell whether a query was taking a long time because it was waiting for a synchronous statistics update. You could tell *after* the statistics update had completed if you had an Extended Event session already running watching for the auto_stats event and filtering on the async column being set to 0. However, that column in the event output was only added in SQL Server 2017, and you’d also have to configure an action that captured something to identify the query involved.

Now in SQL Server 2019, there’s the WAIT_ON_SYNCHRONOUS_STATISTICS_UPDATE wait type, and at first glance it seems like it would easily allow you to see if a query is waiting for a synchronous statistics update by just looking in sys.dm_os_waiting_tasks to see what the query is currently waiting for.

Unfortunately, that’s not the case.

The term “waiting” is a bit misleading here as in this case the thread isn’t actually waiting. This new wait type is an example of what’s called a “preemptive” wait, where the thread switches to a mode where it remains on the processor until it’s finished its work. Most preemptive waits are when a thread makes a call outside of SQL Server (e.g., to get security information from a Domain Controller), but sometimes a thread is doing something inside SQL Server and needs to complete it before potentially being forced to yield the processor because its 4ms thread quantum has expired. Neither of those things are what’s happening here. In this case, the thread registers the start of a preemptive wait with the new wait type and then does the statistics update, probably incurring other *real* waits like PAGEIOLATCH_SH along the way. It’s not until the statistics update has completed that the preemptive wait ends and is accounted for in the wait statistics metrics.

Why is this a big deal? Well, the DMV sys.dm_os_waiting_tasks shows the wait types for all the threads that are *really* waiting, i.e., on the waiting tasks list of a scheduler, so if the synchronous statistics update thread isn’t waiting for WAIT_ON_SYNCHRONOUS_STATISTICS_UPDATE, that wait type will not show up in the output of the DMV. The new wait type cannot be used to see whether a query is currently waiting for a statistics update.

You can easily prove this to yourself by doing the following:

• Create a table with a few hundred thousand rows
• Create a statistic on a table column, and specify FULLSCAN and PERSIST_SAMPLE_PERCENT = ON as options, forcing the entire table to be read every time the statistic is updated
• Update twenty thousand rows
• Checkpoint the database and execute DBCC DROPCLEANBUFFERS
• Do a SELECT statement with a WHERE clause on the column with the statistic you created
• Look in sys.dm_os_waiting_tasks DMV for the session ID of the SELECT, and you’ll see it’s likely waiting for PAGEIOLATCH_SH as the statistics update reads through the table

With that disappoint aside, there’s a trick to be able to see if a query is waiting for a synchronous statistics update. When a statistics update happens, a command called STATMAN runs, and you can see it happening in the output from sys.dm_exec_requests: the status will be “suspended” (even though the thread is running, as I described above), and the command will be “SELECT (STATMAN).”

What Use Is the New Wait Type?

Although the new wait type can’t be used as an immediate way to tell a query is waiting for a synchronous statistics update, if it shows up in your regular wait statistics analysis, you know some queries in the workload may be suffering from these delays. But that’s about the limit of its usefulness as far as I’m concerned. Unless the average wait time shows up as a concerning percentage of your average query execution time or you’re continually capturing waits over small periods of time to allow proper analysis, you don’t know for sure whether there’s a problem.

This is a wait type where the wait time may vary wildly, depending on the factors I mentioned earlier. Therefore, I would use just the presence of this wait type to be alerted to potential problems, and I’d want to implement an Extended Event session as described above to capture instances of synchronous statistics updates to see if their duration is long enough to merit taking some corrective action.

Summary

I’m not sure that the addition of the WAIT_ON_SYNCHRONOUS_STATISTICS_UPDATE wait type is going to change whether people configure asynchronous statistics updates or simply do all the statistics maintenance themselves, but at least now you’ll be able to tell whether queries are waiting for synchronous statistics updates and take some further action.

Until next time, happy performance troubleshooting!

The post Tracking Synchronous Statistics Updates appeared first on SQLPerformance.com.

For many years I’ve found users making the same mistakes. I call them mistakes however, in most cases, it is more just things not being done properly because the people managing the environment don’t know any better. Here are some of the more critical items that anyone installing and supporting SQL Server should know about:

• Backups
• DBCC CHECKDB
• Memory settings
• Statistics
• Index maintenance
• MAXDOP and cost threshold for parallelism
• SQL Server Agent alerts

Backups

I always check backups first when looking at a new system. Having proper backups to meet recovery objectives is critical. Data loss can be detrimental to an organization. When looking at backups, I check for recovery model and the current history of backups for each database. I usually find a combination of the following:

• No backup at all – no record of any backup for the database
• Missing backups – no log backups for a database using the full recovery model
• No recent backups – last backup was weeks/months/years old

Misconfigured backups are detrimental to an organization when a recovery situation comes up. Working with and having to tell customers that they’ve lost data is never fun or easy. Having proper backups to meet SLAs should be any organizations top priority in addition to making sure there are copies of these backups stored in a secondary location offsite.

This situation applies to on-premises SQL Server and IaaS. Azure SQL Database and Azure Managed Instance have managed backups.

DBCC CHECKDB

Database corruption happens unfortunately. Without regularly checking for corruption, customers can find themselves in a bad place by not having backups in order to recover when that corruption affects the physical data. To check for corruption, DBCC CHECKDB should be run against each database on a regular basis. What I find is very similar to backups:

• No DBCC CHECKDBs performed at all
• DBCC CHECKDBs being performed only on select databases
• DBCC CHECKDBs last performed months or years ago

Worst case is a job scheduled reporting failed DBCC CHECKDBs

It is never pleasant finding corruption or having a customer reach out with a corruption issue when the corruption is a heap or clustered index and there are no backups prior to the corruption occurring. In these cases, the corruption is the actual data and starting the restore from before the corruption is in most cases, the only option. In cases where the corruption is a non-clustered index, rebuilding the index is the fix.

In a few situations, I’ve had to work with customers who have nasty corruption without proper backups where I’ve been able to script out the database and manually copy all the usable data into a newly created database. These costly situations can be easily avoided by running DBCC CHECKDB and having proper backup retention.

I advise customers to run DBCC CHECKDB on-premises, IaaS, Azure SQL Database, and Azure SQL Managed Instance. Azure does a great job checking for physical corruption; however, I feel that consumers need to be checking for logical corruption.

Memory Settings

A default installation of Microsoft SQL Server has minimum memory value set to 0 and maximum server memory value set to 2147483647 MB, which is 2 Petabytes. Prior to SQL Server 2012, the maximum server memory value only applied to the bufferpool, so customers needed to limit the amount of memory the bufferpool could use to save memory for the operating system and other processes. SQL Server 2012 introduced a memory manager rewrite so that the maximum server memory value applies to all SQL Server memory allocations.

It is highly advisable to set a maximum value for your SQL Server instance. Jonathan Kehayias has written an blog post How much memory does my SQL Server actually need, with a formula that helps establish the baseline for the maximum memory value. In cases of a shared SQL Server, I recommend my clients to set the minimum value to 30% of the memory on the server.

In situations with multiple instances or where the server is used for SQL Server, SSIS, SSAS, or SSRS, you need to evaluate how much memory those other systems need and reduce the maximum server memory value to allow adequate memory for the OS and the other services.

This issue is valid for on-premises, IaaS, and partially for Azure SQL Managed Instance. Managed Instance sets a max server memory value based on the deployed tier, however when I tested resizing the environment, the max memory value was not dynamically changed. In that situation, you would need to manually update the value. This issue does not apply to Azure SQL Database.

Statistics

The query optimizer uses statistics to build execution plans. This means SQL Server needs statistics to be up to date so that the query optimizer has a better chance of building a good execution plan. By default, statistics are updated after 20% +500 rows of data have been modified. That can take a long time on larger tables. Beginning with compatibility level 130, the threshold for statistics updates for large tables have been lowered. For SQL Server 2008R – 2014, you could lower this threshold using trace flag 2371.

I regularly find that customers are not manually updating statistics and even with the lower threshold, I’ve found that manually updating makes an environment more stable.

I recommend that customers use a third-party script to update statistics. Ola Hallengren has published a widely used Maintenance Solution for SQL Server. Part of that process is his Index Optimize procedure, which can take additional parameters to update statistics.

@UpdateStatistics 
    ALL     = update index and column statistics
    INDEX   = update index statistics
    COLUMNS = update column statistics
    NULL    = Do not perform statistics maintenance (this is the default)

@OnlyModifiedStatistics
    Y = Update statistics only if rows have been modified since most recent stats update
    N = Update statistics regardless of whether any rows have been modified

I’ve found that customers who are using third party products or scripts to perform index maintenance based upon the fragmentation level of the index are not considering that reorganizations do not update statistics like rebuilds do. Many of these third-party applications have options for updating statistics just like Ola’s Index Optimize procedure, you just need to turn it on.

Updating statistics applies to on-premises, IaaS, Azure SQL Database, and Azure SQL Managed Instance.

Index Maintenance

Performing index maintenance by removing fragmentation from your indexes is still important. Some retired documentation from Microsoft stated that index fragmentation can have a negative impact from 13-460% depending on the size of the environment and the level of fragmentation. While hardware such as intelligent SANs, Solid State Disk, and other advancements have helped speed things up, wasted space in index can translate to wasted space in the buffer pool as well as wasting more I/O.

Fragmentation occurs through regular operations such as inserts, updates and deletes. To remediate this, proper index maintenance of rebuilding or reorganizing your indexes is needed. I again turn to Ola Hallengren, for his Index Optimize script. Ola’s script provides the ability to specify to rebuild or reorganize based on the level of fragmentation and minimum pages. Many third-party tools offer the same logic. SQL Server Database Maintenance plans prior to SQL Server 2016 only allowed to rebuild or reorganize all indexes. Beginning with SQL Server 2016, you can now specify similar logic based on fragmentation levels. Don’t forget those statistics though if you are using smart logic based on fragmentation levels.

I like Ola’s script and third-party tools that log to a table. I can then query the table to see if I have any index hot spots where fragmentation is constantly occurring at high levels and troubleshoot why fragmentation is so prevalent and can anything be done.

There are exceptions to every rule or best practice. Some patterns of data access lead to constant fragmentation. The cost of constantly rebuilding/reorganizing those tables may not be worth it and can be excluded from maintenance. Those situations should be evaluated on a case by case basis.

This applies to on-premises, IaaS, Azure SQL Database, and Azure SQL Managed Instance.

MAXDOP

I find that max degree of parallelism and cost threshold for parallelism are typically left at the default values on the client servers. For MAXDOP the default value is zero which means an ‘unlimited’ number of CPUs could be used to execute a parallel region of a query. Technically up to 64 processors unless you enable a trace flag to use more.

A decade ago, when processors had lower core counts, this value was acceptable. Today, with high core density and multi-socket servers, an unlimited number of CPUs for parallelism isn’t so good. Microsoft has given guidance on what values to use for MAXDOP.

If you are on SQL Server 2008 – SQL Server 2014, for a single NUMA node with less than 8 logical processors, keep MAXDOP at or below the number of logical processors. If you have more than 8 logical processers, keep MAXDOP at 8. If you have multiple NUMA nodes with less than 8 logical processors per NUMA node, keep MAXDOP at or below the number of logical processors per NUMA node. Greater than 8, keep MAXDOP at 8.

SQL Server 2016 introduced soft-NUMA nodes. During service startup, if the Database Engine detects more than 8 physical cores per NUMA node or socket, soft-NUMA nodes are created automatically. The engine takes care of placing logical processors from the same physical core into different soft-NUMA nodes. For that reason, we have slightly different guidance for MAXDOP for SQL Server 2016 onwards.

If you are on SQL Server 2016 and up, for a single NUMA node with less than 16 logical processors, keep MAXDOP at or below the number of logical processors. If you have more than 16 logical processers, keep MAXDOP at 16. If you have multiple NUMA nodes with less than 16 logical processors per NUMA node, keep MAXDOP at or below the number of logical processors per NUMA node. Greater than 16, keep MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16.

If you are mostly virtualized on machines with 8 or fewer logical processors with a default MAXDOP, you’re probably in OK. If you have big physical hardware with defaults, then you should look at optimizing MAXDOP.

All the figures above are guidelines, not hard truths. Your workloads vary and consideration should be taken when you determine what value is most optimal for your workload.

Configuring MAXDOP applies to on-premises, IaaS, and Azure SQL Managed Instance. However, there is a database scoped configuration that can be applied per database starting with SQL Server 2016, and this applies to Azure SQL Database.

Cost Threshold for Parallelism

Cost threshold for parallelism has a default value of 5. The history of this number goes back to the early days of SQL Server and the workstation that workload testing was performed on. With modern hardware, the cost estimation of 5 is outdated. Testing has shown that increasing the number from 5 to a higher value will keep shorter-running queries from having a parallel plan. I tend to recommend increasing this value to a higher number after examining the Plan Cache. In many cases I end up starting with a value of 25 and then monitor further and adjust from there, if needed. For more information about tuning cost threshold for parallelism, Jonathan Kehayias wrote: Tuning ‘cost threshold for parallelism’ from the Plan Cache.

This applies to on-premises, IaaS, and Azure SQL Managed Instance.

SQL Server Agent Alerts

Everyone should be leveraging SQL Agent alerts unless they have a third-party application monitoring for the same error conditions. Configuring alerts is easy and free, and having them configured will give you critical information when your servers are having problems.

I wrote an article titled SQL Server Agent Alerts, providing step-by-step instructions on how to create alerts for severity 19-25 errors and error 825. Enabling these alerts is easy: enable database mail, create a mail operator and then create the alerts. This can be accomplished using the GUI or with T-SQL. I encourage my everyone to script out this process using T-SQL and make it part of your standard server build.

This applies to on-premises, IaaS, and Azure SQL Managed Instance.

Summary

As you can see, there are many settings that should be modified from the defaults after installing SQL Server. This is not a comprehensive list; however, it does cover many of the more critical and performance impacting issues I find, and that I have lumped under my "SQL Server mishaps" category.

The post Common SQL Server Mishaps appeared first on SQLPerformance.com.

Note: This post is most applicable to SQL Server 2012 and higher, but some detail for prior releases is included for reference (and fun).

SQL Server 7.0

• The number of steps in a histogram is limited to 300. In SQL Server 6.5 and earlier a histogram would have the number of steps that could fit on a 2K page, based on the size of the first column in the key.
• The ‘automatically update statistics’ database option is introduced; previously statistics were only updated manually.

SQL Server 2000

• The number of steps in the histogram is reduced from 300 to 200 (technically 201, if you include the step for NULL, assuming the first column in the key allows NULLs).

SQL Server 2005

• Updates to statistics that use FULLSCAN can run in parallel.
• Trace Flags 2389 and 2390 are introduced in SP1 to help with the Ascending Key issue, described in the post, Ascending Keys and Auto Quick Correct Statistics. A detailed example of this scenario is provided in my post Trace Flag 2389 and the new Cardinality Estimator.
• The instance option ‘Automatically Update Statistics Asynchronously’ is introduced. Note that in order for this to be in effect, the option ‘Automatically Update Statistics’ must also be enabled. If you’re not clear on what this option does, review the documentation in ALTER DATABASE SET Options. This is a setting that Glenn recommends (as noted in his post referenced below), but I think it’s important to be aware of potential issues, as noted in How Automatic Updates to Statistics Can Affect Query Performance.
   
  Note: There is a memory leak related to this setting in SQL Server 2008 through SQL Server 2012; please see Glenn’s post Important Hotfix for SQL Server 2008 for more details.

SQL Server 2008

• Filtered statistics are introduced, and these can be created separately from a filtered index. There are some limitations around filtered indexes with regard to the Query Optimizer (see Tim Chapman’s post The Pains of Filtered Indexes and Paul White’s post Optimizer Limitations with Filtered Indexes) post, and it’s important to understand the behavior of the counter that tracks modifications (and thus can trigger automatic updates). See Kimberly’s post Filtered indexes and filtered stats might become seriously out-of-date for more details, and I also recommend checking out her stored procedure that analyzes data skew and recommends where you can create filtered statistics to provide more information to the Query Optimizer. I’ve implemented this for several large customers that have VLTs and skewed distribution across columns frequently used in predicates.
• Two new catalog views, sys.stats and sys.stats_columns, are added to provide easier insight into statistics and included columns. Use these two views instead of sp_helpstats, which is deprecated and provides less information.

SQL Server 2008R2 SP1

• Trace Flag 2371 is made available, which can be used to reduce the number of modifications required for automatic updates to statistics to occur. As a reminder, I’m a fan of updating statistics on a regular basis through a scheduled job and leaving the auto update enabled as a safety.

SQL Server 2008R2 SP2

• The function sys.dm_db_stats_properties is included, which provides the same information found in the header of DBCC SHOW_STATISTICS, as well as a modification column that could be used to track changes and programmatically determine if an update was needed. Remember my preference for using a job to update stats? That job just got a lot smarter with this DMF…now I can look to see how much data has been modified and ONLY update statistics if a certain percentage of data has changed. Slick.

SQL Server 2012

• Updating statistics will not cause plans to be invalidated IF no rows have changed. This is one that surprises a lot of people, and Kimberly has a fun post, What caused that plan to go horribly wrong – should you update statistics?, that walks through her adventure in figuring this out.

SQL Server 2012 SP1

• DBCC SHOW_STATISTICS only requires the SELECT permission – previously it required a user to be a member of sysadmin, or a member of the db_owner or db_ddladmin database role. This can be globally disabled with trace flag 9485.
• Includes sys.dm_db_stats_properties (see 2008R2 SP2 note above)

SQL Server 2012 SP2

• Cumulative Update 1 introduces a fix related to ascending keys not being properly identified even with trace flags 2389 and 2390 in use. This requires trace flag 4139 in addition to CU1, as noted in KB 2952101.

SQL Server 2014

• The new Cardinality Estimator is introduced, implemented by setting the database compatibility mode to 120, or by using trace flag 2312. If you haven’t read anything about the new CE I recommend starting with the Cardinality Estimation documentation and then reading Joe Sack’s whitepaper, Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator, for in-depth details.
• The behavior from Trace Flags 2389 and 2390 for ascending keys is now implemented via the database compatibility mode. If your databases compatibility mode is set to 120 (or higher in later releases), you do not need to use Trace Flags 2389 and 2390 for SQL Server to identify statistics that have ascending keys.
• Incremental statistics are introduced for partitions, and can be viewed through the new DMF sys.dm_db_incremental_stats_properties. Incremental statistics provide a way to update statistics for a partition without updating them for the entire table. However, the additional statistics information from the incremental statistics is not used by the Query Optimizer, but it is folded into the main histogram for the table.
• CU2 includes the same fix mentioned above for SQL Server 2012 SP2 that also requires trace flag 4139.

SQL Server 2014 SP1

• Trace flag 7471 is back-ported to CU6, originally available in SQL Server 2016 as noted below.

SQL Server 2016

• Trace flag 2371 is no longer needed to reduce the threshold for automatic updates to statistics if the database compatibility mode is set to 130. See Controlling Autostat (AUTO_UPDATE_STATISTICS) behavior in SQL Server.
• Updates to statistics can run in parallel when using SAMPLE, not just FULLSCAN. This is tied to the compatibility mode (must be 130), but can potentially provide faster updates and thus decrease maintenance windows. Aaron Bertrand talks about this enhancement in his post, A potential improvement for statistics updates: MAXDOP.
• Trace flag 7471 is introduced in CU1 to allow multiple UPDATE STATISTICS commands to run concurrently for a single table and Jonathan provides some great examples of how this can be used in his post Improved Support for Parallel Statistics Rebuilds.

SQL Server 2016 SP1

• The query hint option ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS is introduced, along with the FOR HINT argument, which is the equivalent of trace flag 4139.
• The DMF sys.dm_db_stats_histogram is exposed in CU2, which is an alternative to the histogram output from DBCC SHOW_STATISTICS. The information in both is the same, use what’s easier for you or better fits the problem you need to solve.
• The option PERSIST_SAMPLE_PERCENT is introduced in CU4, which can be used to force the same sampling rate to be used every time a statistic is updated going forward, and examples of this behavior can be found in the post, Persisting statistics sampling rate.

SQL Server 2017

• The PERSIST_SAMPLE_PERCENT option is available in CU1 (see 2016 SP1 entry for additional info).
• The attributes StatsInfoType and OptimizerStatsUsageType are added to the Query Plan, which lists statistics used during query optimization. This is available in CU3 and is tied to the CE version (120 and higher). This is pretty cool! I haven’t had a chance to play with this yet, but to get this information previously you had to use undocumented trace flags.
• CU3 also introduced the MAXDOP option for CREATE STATISTICS and UPDATE STATISTICS, which can be used to override the MAXDOP value for the instance or database.
• One more addition in CU3: the UdfCpuTime and UdfElapsedTime attributes can be found in the Query Plan, which represent execution statistics for scalar-valued, UDFs.

Summary

If you are looking to upgrade to a newer release, or if you’ve recently upgraded, take note as to how these changes impact your solution. We’ve had many clients contact us after upgrading from 2005/2008/2008R2 to 2014 or 2016, complaining of performance issues. In many cases, adequate testing was not completed prior to the upgrade.

This is something we really focus on when we’re helping a client upgrade. Beyond the steps to move a production instance from one version to another with little downtime, we want to make sure that the day after the upgrade is a boring one for DBAs and developers.

We don’t simply test the upgrade process, we test what the system looks like after the upgrade. Are the same trace flags from the old environment needed in the new one? What database settings need to be adjusted? Does query performance change – for better or worse? If you don’t know the answers to those questions before you upgrade production, then you’re setting yourself up for one to many days of fire-fighting, Sev 1 calls, meals at your desk, not enough sleep and who knows what else.

Take the time up front to understand the impact of the new features and changes in functionality listed above, plan the upgrade, and test as much as possible.

The post UPDATEs to Statistics appeared first on SQLPerformance.com.

The Basics

I started out by creating a clone of the AdventureWorks2014 database and running a query against the source database and then the clone database:

DBCC CLONEDATABASE (N'AdventureWorks2014', N'AdventureWorks2014_CLONE');
GO

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO
SET STATISTICS XML ON;
GO

USE [AdventureWorks2014];
GO

SELECT *
FROM [Sales].[SalesOrderHeader] [h]
JOIN [Sales].[SalesOrderDetail] [d] ON [h].[SalesOrderID] = [d].[SalesOrderID]
ORDER BY [SalesOrderDetailID];
GO

USE [AdventureWorks2014_CLONE];
GO

SELECT *
FROM [Sales].[SalesOrderHeader] [h]
JOIN [Sales].[SalesOrderDetail] [d] ON [h].[SalesOrderID] = [d].[SalesOrderID]
ORDER BY [SalesOrderDetailID];
GO

SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO
SET STATISTICS XML OFF;
GO

If I look at the I/O and TIME output, I can see that the query against the source database took longer and generated a lot more I/O, both of which are expected as the clone database has no data in it:

If I look at the execution plans, they are the same for both databases except for the actual values (the amount of data that actually moved through the plan):

Query Plan for AdventureWorks2014 database

Query Plan for AdventureWorks2014_CLONE database

This is where the value of DBCC CLONEDATABASE is apparent – I can get an empty copy of a database to anyone (Microsoft Product Support, my fellow DBA, etc.) and have them recreate and investigate an issue, and they don't need potentially hundreds of GB of disk space to do it. Melissa’s July T-SQL Tuesday post has detailed information about what happens during the clone process, so I recommend reading that for more information.

Is that it?

But… can I do more with DBCC CLONEDATABASE?  I mean, this is great, but I think there are a lot of other things I can do with an empty copy of the database.  If you read the documentation for DBCC CLONEDATABASE, you’ll see this line:

My first thought was, “query optimizer – hmm… can I use this as an option for testing upgrades?”

Well, the cloned database is read-only, but I thought I’d try to change some options anyway. For example, if I could change the compatibility mode, that would be really cool, as then I could test CE changes in both SQL Server 2014 and SQL Server 2016.

USE [master];
GO

ALTER DATABASE [AdventureWorks2014_CLONE] SET COMPATIBILITY_LEVEL = 110;

I get an error:

Hm.  Can I change the recovery model?

ALTER DATABASE [AdventureWorks2014_CLONE] SET RECOVERY SIMPLE WITH NO_WAIT;

I can.  That doesn’t seem fair.  Well, it’s read-only, can I change that?

ALTER DATABASE [AdventureWorks2014_CLONE] SET READ_WRITE WITH NO_WAIT;

YES!  Before you get too excited, let me leave this note from the documentation right here:

I'm going to repeat this line from the documentation, and bold it and put it red as a friendly but extremely important reminder:

Well that’s fine with me, I definitely wasn’t going to use this for production, but now I can use it for testing!  NOW I can change the compatibility mode, and NOW I can back it up and restore it on another instance for testing!

USE [master];
GO

BACKUP DATABASE [AdventureWorks2014_CLONE]
  TO  DISK = N'C:\Backups\AdventureWorks2014_CLONE.bak'
  WITH INIT, NOFORMAT, STATS = 10, NAME = N'AW2014_CLONE_full';
GO

/* restore on SQL Server 2016 */


RESTORE DATABASE [AdventureWorks2014_CLONE]
FROM  DISK = N'C:\Backups\AdventureWorks2014_CLONE.bak' WITH
MOVE N'AdventureWorks2014_Data' TO N'C:\Databases\AdventureWorks2014_Data_2684624044.mdf',
MOVE N'AdventureWorks2014_Log' TO N'C:\Databases\AdventureWorks2014_Log_3195542593.ldf',
NOUNLOAD,  REPLACE,  STATS = 5;
GO

ALTER DATABASE [AdventureWorks2014_CLONE] SET COMPATIBILITY_LEVEL = 130;
GO

THIS IS BIG.

In my last post I talked about trace flag 2389 and testing with the new Cardinality Estimator because, friends, you need to be testing with the new CE before you upgrade. If you do not test, and if you change the compatibility mode to 120 (SQL Server 2014) or 130 (SQL Server 2016) as part of your upgrade, then you run the risk of working in a fire-fighting mode if you run into regressions with the new CE. Now, you could be just fine, and performance may be even better after you upgrade. But… wouldn’t you like to be certain?

Very often when I mention testing before an upgrade, I’m told that there is no environment in which to do the testing.  I know some of you have a Test environment. Some of you have Test, Dev, QA, UAT and who knows what else. You’re lucky.

For those of you that state you have no test environment at all in which to test, I give you DBCC CLONEDATABASE. With this command, you have no excuse to not run the most frequently-executed queries and the heavy-hitters against a clone of your database. Even if you don’t have a test environment, you have your own machine.  Backup the clone database from production, drop the clone, restore the backup to your local instance, and then test.  The clone database takes up very little space on disk and you won’t incur memory or I/O contention as there’s no data.  You will be able to validate query plans from the clone against those from your production database. Further, if you restore on SQL Server 2016 you can incorporate Query Store into your testing! Enable Query Store, run through your testing in the original compatibility mode, then upgrade the compatibility mode and test again. You can use Query Store to compare queries side by side! (Can you tell I'm dancing in my chair right now?)

Considerations

Again, this shouldn't be anything you would use in production, and I know you wouldn't do that, but it bears repeating because in its current state, DBCC CLONEDATABASE is not fully complete.  This is noted in the KB article under supported objects; objects such as memory optimized tables and file tables are not copied, Full-text is not supported, etc.

Now, the clone database isn’t without drawbacks. If you inadvertently run an index rebuild or an update to statistics in that database, you’ve just wiped out your test data.  You will lose the original statistics which is what probably you really wanted in the first place.  For example, if I check statistics for the clustered index on SalesOrderHeader right now, I get this:

USE [AdventureWorks2014_CLONE];
GO
DBCC SHOW_STATISTICS (N'Sales.SalesOrderHeader',PK_SalesOrderHeader_SalesOrderID);

Original statistics for SalesOrderHeader

Now, if I update statistics against that table, I get this:

UPDATE STATISTICS [Sales].[SalesOrderHeader] WITH FULLSCAN;
GO

DBCC SHOW_STATISTICS (N'Sales.SalesOrderHeader',PK_SalesOrderHeader_SalesOrderID);

Updated (empty) statistics for SalesOrderHeader

As an additional safety, it's probably a good idea to disable auto updates to statistics:

USE [master];
GO
ALTER DATABASE [AdventureWorks2014_CLONE] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT;

If you do happen to update statistics unintentionally, running DBCC CLONEDATABASE and going through the backup and restore process isn’t that hard, and you’ll have it automated in no time.

You can add data to the database. This could be useful if you want to experiment with statistics (e.g. different sample rates, filtered statistics) and you have enough storage to hold a copy of the table’s data.

With no data in the database, you’re obviously not going to get reliably representative duration and I/O data. That’s ok. If you need data about true resource usage, then you need a copy of your database with all the data in it. DBCC CLONEDATABASE is really about testing query performance; that’s it. It’s not a replacement for traditional upgrade testing in any way – but it is a new option for validating how SQL Server optimizes a query with different versions and compatibility modes. Happy testing!

The post Expanding the uses of DBCC CLONEDATABASE appeared first on SQLPerformance.com.

I had a discussion recently with a client about using this trace flag, and it came up because of this type of scenario:

• You have a large table that has an INT as the primary key, and it’s clustered.
• You have a nonclustered index that leads on a DATETIME column.
• The table has about 20 million rows in it, and anywhere from 5,000 to 100,000 rows are added each day.
• Statistics are updated nightly as part of your maintenance task.
• Auto-update statistics is enabled for the database, but even if 100,000 rows are added to the table, that’s way less than the 4 million rows (20%) needed to invoke an automatic update.
• When users query the table using the date in the predicate, query performance can be great, or it can be awful.

That last bullet almost makes it sounds like a parameter sensitivity issue, but it’s not.  In this case, it’s a statistics issue.  My suggestion to the client was using TF 2389, or updating statistics more frequently throughout the day (e.g. via an Agent Job).  Then I thought I’d do some testing, since the client was running SQL Server 2014.  This is where things got interesting.

The Setup

We’re going to create the aforementioned table for testing in the RTM build of SQL Server 2016, within the WideWorldImporters database, and I’m going to set the compatibility mode to 110 initially:

USE [master];
GO
RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD, REPLACE, STATS = 5;
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;
GO

USE [WideWorldImporters];
GO

CREATE TABLE [Sales].[BigOrders](
[OrderID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalespersonPersonID] [int] NOT NULL,
[PickedByPersonID] [int] NULL,
[ContactPersonID] [int] NOT NULL,
[BackorderOrderID] [int] NULL,
[OrderDate] [date] NOT NULL,
[ExpectedDeliveryDate] [date] NOT NULL,
[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
[IsUndersupplyBackordered] [bit] NOT NULL,
[Comments] [nvarchar](max) NULL,
[DeliveryInstructions] [nvarchar](max) NULL,
[InternalComments] [nvarchar](max) NULL,
[PickingCompletedWhen] [datetime2](7) NULL,
[LastEditedBy] [int] NOT NULL,
[LastEditedWhen] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Sales_BigOrders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA] TEXTIMAGE_ON [USERDATA];

Next we’re going to load about 24 million rows into BigOrders, and create a nonclustered index on OrderDate.

SET NOCOUNT ON;

DECLARE @Loops SMALLINT = 0, @IDIncrement INT = 75000;

WHILE @Loops < 325 -- adjust this to increase or decrease the number of rows added
BEGIN
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + @IDIncrement,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];

CHECKPOINT;

SET @Loops = @Loops + 1;
SET @IDIncrement = @IDIncrement + 75000;
END

CREATE NONCLUSTERED INDEX [NCI_BigOrders_OrderDate]
ON [Sales].[BigOrders] ([OrderDate], CustomerID);

If we check the histogram for the nonclustered index, we see the highest date is 2016-05-31:

DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);

Statistics for the NCI on OrderDate

If we query for any date beyond that, note the estimated number of rows:

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';

Plan when querying for a date beyond what's in the histogram

It’s 1, because the value is outside the histogram.  And in this case, that’s ok, because there are no rows in the table beyond May 31, 2016.  But let’s add some and then re-run the same query:

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25000000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-01',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';

Plan after adding rows past May 31

The estimated number of rows is still 1.  But this is where things get interesting.  Let’s change the compatibility mode to 130 so that we use the new Cardinality Estimator and see what happens.

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130
GO

USE [WideWorldImporters];
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-01';

Plan after adding rows for June 1, using the new CE

Our plan shape is the same, but now our estimate is 4,898 rows.  The new CE treats values outside of the history differently than the old CE.  So…do we even need trace flag 2389?

The Test – Part I

For the first test, we’re going to stay in compatibility mode 110 and run through what we would see with 2389.  When using this trace flag you can either enable it as a startup parameter in the SQL Server service, or you can use DBCC TRACEON to enable it instance-wide.  Understand that in your production environment, if you use DBCC TRACEON to enable the trace flag, when the instance restarts the trace flag won’t be in effect.

With the trace flag enabled, a statistic has to be updated three (3) times before the optimizer will brand it as ascending.  We’ll force four updates for good measure and add more rows in between each update.

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 110;
GO

DBCC TRACEON (2389, -1);
GO

USE [WideWorldImporters];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25100000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-02',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy]
[LastEditedWhen]
)
SELECT
[OrderID] + 25200000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-03',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25300000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-04',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];

If we check statistics again, and use the trace flag 2388 to display additional information, we see that the statistic is now marked as Ascending:

DBCC TRACEON (2388);
GO

DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);

NCI on OrderDate marked as ASC

If we query for a future date, when statistics are fully up-to-date, we see that it still estimates 1 row:

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan after TF 2389 enabled, but no rows beyond histogram

Now we’ll add rows for June 5^th and run the same query again:

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25400000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-05',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan after TF 2389 enabled, 70K+ rows added beyond histogram

Our estimate is no longer 1, it’s 22,595.  Now, just for fun, let’s disable the trace flag and see what the estimate is (I’m going to clear procedure cache, as disabling the trace flag won’t affect what’s currently in cache).

DBCC TRACEOFF (2389, -1);
GO

DBCC FREEPROCCACHE;
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan after TF 2389 is *disabled*, 70K+ rows added beyond histogram

This time around I get an estimate of 1 row again.  Even though the statistic is branded as ascending, if trace flag 2389 is not enabled, it only estimates 1 row when you query for a value outside the histogram.

We’ve demonstrated that trace flag 2389 does what we expect – what it always has done – when using the old Cardinality Estimator.  Now let’s see what happens with the new one.

The Test – Part II

To be thorough, I’m going to reset everything. I will create the database again, set the compatibility mode to 130, load the data initially, then turn on trace flag 2389 and load three sets of data with stats updates in between.

USE [master];
GO

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\Backups\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.mdf',
MOVE N'WWI_UserData' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_UserData.ndf',
MOVE N'WWI_Log' TO N'C:\Databases\WideWorldImporters\WideWorldImporters.ldf',
MOVE N'WWI_InMemory_Data_1' TO N'C:\Databases\WideWorldImporters\WideWorldImporters_InMemory_Data_1',
NOUNLOAD, REPLACE, STATS = 5;
GO

USE [master];
GO

ALTER DATABASE [WideWorldImporters] SET COMPATIBILITY_LEVEL = 130;
GO

USE [WideWorldImporters];
GO

CREATE TABLE [Sales].[BigOrders](
[OrderID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalespersonPersonID] [int] NOT NULL,
[PickedByPersonID] [int] NULL,
[ContactPersonID] [int] NOT NULL,
[BackorderOrderID] [int] NULL,
[OrderDate] [date] NOT NULL,
[ExpectedDeliveryDate] [date] NOT NULL,
[CustomerPurchaseOrderNumber] [nvarchar](20) NULL,
[IsUndersupplyBackordered] [bit] NOT NULL,
[Comments] [nvarchar](max) NULL,
[DeliveryInstructions] [nvarchar](max) NULL,
[InternalComments] [nvarchar](max) NULL,
[PickingCompletedWhen] [datetime2](7) NULL,
[LastEditedBy] [int] NOT NULL,
[LastEditedWhen] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Sales_BigOrders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [USERDATA]
) ON [USERDATA] TEXTIMAGE_ON [USERDATA];
GO

SET NOCOUNT ON;

DECLARE @Loops SMALLINT = 0;
DECLARE @IDIncrement INT = 75000;

WHILE @Loops < 325 -- adjust this to increase or decrease the number of rows added
BEGIN
INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + @IDIncrement,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];

CHECKPOINT;

SET @Loops = @Loops + 1;
SET @IDIncrement = @IDIncrement + 75000;
END

CREATE NONCLUSTERED INDEX [NCI_BigOrders_OrderDate]
ON [Sales].[BigOrders] ([OrderDate], CustomerID);
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25000000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-01',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

DBCC TRACEON (2389, -1);
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25100000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-02',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25200000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-03',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];
GO

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25300000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-04',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

UPDATE STATISTICS [Sales].[BigOrders] [NCI_BigOrders_OrderDate];

Ok, so our data is completely loaded.  If we check statistics again, and use the trace flag 2388 to display additional information, we see that the statistic is again marked as Ascending:

DBCC TRACEON (2388);
GO

DBCC SHOW_STATISTICS ('Sales.BigOrders',[NCI_BigOrders_OrderDate]);

NCI OrderDate statistic marked as ASC with TF 2389 and compatibility mode 130

Ok, so let’s query for June 5^th again:

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan with new CE, no rows beyond what's in histogram

Our estimate is 4,922.  Not quite what it was in our first test, but definitely not 1.  Now we’ll add some rows for June 5^th and re-query:

INSERT [Sales].[BigOrders]
( [OrderID],
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
[OrderDate],
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
)
SELECT
[OrderID] + 25400000,
[CustomerID],
[SalespersonPersonID],
[PickedByPersonID],
[ContactPersonID],
[BackorderOrderID],
'2016-06-05',
[ExpectedDeliveryDate],
[CustomerPurchaseOrderNumber],
[IsUndersupplyBackordered],
[Comments],
[DeliveryInstructions],
[InternalComments],
[PickingCompletedWhen],
[LastEditedBy],
[LastEditedWhen]
FROM [Sales].[Orders];
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan with new CE, with 70K+ rows beyond what's in histogram

The estimate is the same. So now, what if we turn off trace flag 2389?

DBCC TRACEOFF (2389, -1);
GO

DBCC FREEPROCCACHE;
GO

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-05';

Plan with new CE but TF 2389 is NOT enabled, with 70K+ rows beyond what's in histogram

The estimate changed slightly, to 4,930, but it changed. This tells me that trace flag 2389 has some effect on the estimate, but how much is unknown.

The Test – Part III

I ran one final test, where I restored the database, set the compatibility mode to 130, loaded all the data again, updated statistics multiple times, but did NOT enable trace flag 2389.  The code is the same as Part II, except for using DBCC TRACEON to enable 2389.  When I queried for June 5, both before and after adding the data, the estimated number of rows was 4,920.

What does it mean?

To summarize, when using compatibility mode 110 or below, trace flag 2389 works like it always has.  But when using compatibility mode 120 or higher, and thus the new CE, the estimates are not the same compared to the old CE, and in this specific case, are not that different whether using the trace flag or not.

So what should you do?  Test, as always.  I haven’t found anything documented in MSDN that states that trace flag 2389 is not supported with compatibility mode 120 and higher, nor have I found anything that documents a change in behavior.  I do find it very interesting that the estimates are different (in this case much lower) with the new CE.  That could potentially be an issue, but there are multiple factors in play when it comes to estimates, and this was a very simple query (one table, one predicate).  In this case, the estimate is way off (4920 rows versus the 22,595 rows for the June 5 date).

If I re-run the query for a date that has the same number of rows that is within the histogram, I get a similar plan, but it runs in parallel:

SELECT CustomerID, OrderID, SalespersonPersonID
FROM [Sales].[BigOrders]
WHERE [OrderDate] = '2016-06-02';

Plan for a query that uses a date within the histogram (new CE, no TF)

The estimate is also more accurate (68,318).  The plan doesn’t change significantly in this case, but the cost is obviously higher.  At some point, depending on the number of rows that would be returned, this could tip to a table scan.

The best guidance at this time if you’re running 2014 or higher and compatibility mode 120 or higher, and you have leading columns in statistics that are ascending, is to test.  If you find that the new Cardinality Estimator does not provide as good of an estimate as the old CE, then I would recommend filing a Connect item so the product team is aware of it.  There are always one-off and unique cases, but if many customers (read: YOU) consistently find the same behavior – and it’s not ideal – then it’s important to let the development team know about it.

This is another important item to consider when upgrading to 2014 or 2016 – and a reminder to not neglect your testing (and oh by the way, Query Store would be extremely useful here with 2016).  Get to it friends.

The post Trace Flag 2389 and the new Cardinality Estimator appeared first on SQLPerformance.com.

But that's only part of the story (and to be clear, DATEFROMPARTS() isn't technically required to get a seek, it's just cleaner in that case). If we zoom out a bit, we notice that our estimates are far from accurate, a complexity I didn't want to introduce in the previous post:

This is not uncommon for both inequality predicates and with forced scans. And of course, wouldn't the method I suggested yield the most inaccurate stats? Here is the basic approach (you can get the table schema, indexes, and sample data from my previous post):

CREATE PROCEDURE dbo.MonthlyReport_Original
  @Year  int,
  @Month int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Start date = DATEFROMPARTS(@Year, @Month, 1);
  DECLARE @End   date = DATEADD(MONTH, 1, @Start);

  SELECT DateColumn 
    FROM dbo.DateEntries
    WHERE DateColumn >= @Start
      AND DateColumn <  @End;
END
GO

Now, inaccurate estimates won't always be a problem, but it can cause issues with inefficient plan choices at the two extremes. A single plan might not be optimal when the chosen range will yield a very small or very large percentage of the table or index, and this can get very hard for SQL Server to predict when the data distribution is uneven. Joseph Sack outlined the more typical things bad estimates can affect in his post, "Ten Common Threats to Execution Plan Quality:"

There are others, too, like memory grants that are too large or too small. He goes on to describe some of the more common causes of bad estimates, but the primary cause in this case is missing from his list: guesstimates. Because we're using a local variable to change the incoming int parameters to a single local date variable, SQL Server doesn't know what the value will be, so it makes standardized guesses of cardinality based on the entire table.

We saw above that the estimate for my suggested approach was 5,170 rows. Now, we know that with an inequality predicate, and with SQL Server not knowing the parameter values, it will guess 30% of the table. 31,645 * 0.3 is not 5,170. Nor is 31,465 * 0.3 * 0.3, when we remember that there are actually two predicates working against the same column. So where does this 5,170 value come from?

As Paul White describes in his post, "Cardinality Estimation for Multiple Predicates," the new cardinality estimator in SQL Server 2014 uses exponential backoff, so it multiplies the row count of the table (31,465) by the selectivity of the first predicate (0.3), and then multiplies that by the square root of the selectivity of the second predicate (~0.547723).

So, now we can see where SQL Server came up with its estimate; what are some of the methods we can use to do anything about it?

1. Pass in date parameters. When possible, you can change the application so that it passes in proper date parameters instead of separate integer parameters.
    
2. Use a wrapper procedure. A variation on method #1 - for example if you can't change the application - would be to create a second stored procedure that accepts constructed date parameters from the first.
    
3. Use OPTION (RECOMPILE). At the slight cost of compilation every time the query is run, this forces SQL Server to optimize based on the values presented each time, instead of optimizing a single plan for unknown, first, or average parameter values. (For a thorough treatment of this topic, see Paul White's "Parameter Sniffing, Embedding, and the RECOMPILE Options."
    
4. Use dynamic SQL. Having dynamic SQL accept the constructed date variable forces proper parameterization (just as if you had called a stored procedure with a date parameter), but it is a little ugly, and harder to maintain.
    
5. Mess with hints and trace flags. Paul White talks about some of these in the aforementioned post.

I'm not going to suggest that this is an exhaustive list, and I'm not going to reiterate Paul's advice about hints or trace flags, so I'll just focus on showing how the first four approaches can mitigate the issue with bad estimates.

1. Date Parameters

CREATE PROCEDURE dbo.MonthlyReport_TwoDates
  @Start date,
  @End   date
AS
BEGIN
  SET NOCOUNT ON;

  SELECT /* Two Dates */ DateColumn
    FROM dbo.DateEntries
    WHERE DateColumn >= @Start
      AND DateColumn <  @End;
END
GO

2. Wrapper Procedure

CREATE PROCEDURE dbo.MonthlyReport_WrapperTarget
  @Start date,
  @End   date
AS
BEGIN
  SET NOCOUNT ON;

  SELECT /* Wrapper */ DateColumn
    FROM dbo.DateEntries
    WHERE DateColumn >= @Start
      AND DateColumn <  @End;
END
GO

CREATE PROCEDURE dbo.MonthlyReport_WrapperSource
  @Year  int,
  @Month int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Start date = DATEFROMPARTS(@Year, @Month, 1);
  DECLARE @End   date = DATEADD(MONTH, 1, @Start);

  EXEC dbo.MonthlyReport_WrapperTarget @Start = @Start, @End = @End;
END
GO

3. OPTION (RECOMPILE)

CREATE PROCEDURE dbo.MonthlyReport_Recompile
  @Year  int,
  @Month int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Start date = DATEFROMPARTS(@Year, @Month, 1);
  DECLARE @End   date = DATEADD(MONTH, 1, @Start);

  SELECT /* Recompile */ DateColumn
    FROM dbo.DateEntries
      WHERE DateColumn >= @Start
      AND DateColumn < @End OPTION (RECOMPILE);
END
GO

4. Dynamic SQL

CREATE PROCEDURE dbo.MonthlyReport_DynamicSQL
  @Year  int,
  @Month int
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @Start date = DATEFROMPARTS(@Year, @Month, 1);
  DECLARE @End   date = DATEADD(MONTH, 1, @Start);

  DECLARE @sql nvarchar(max) = N'SELECT /* Dynamic SQL */ DateColumn
    FROM dbo.DateEntries
    WHERE DateColumn >= @Start
    AND DateColumn < @End;';

  EXEC sys.sp_executesql @sql, N'@Start date, @End date', @Start, @End;
END
GO

The Tests

With the four sets of procedures in place, it was easy to construct tests that would show me the plans and the estimates SQL Server derived. Since some months are busier than others, I picked three different months, and executed them all multiple times.

DECLARE @Year  int = 2012, @Month int = 7; -- 385 rows
DECLARE @Start date = DATEFROMPARTS(@Year, @Month, 1);
DECLARE @End   date = DATEADD(MONTH, 1, @Start);

EXEC dbo.MonthlyReport_Original      @Year  = @Year, @Month = @Month;
EXEC dbo.MonthlyReport_TwoDates      @Start = @Start,  @End = @End;
EXEC dbo.MonthlyReport_WrapperSource @Year  = @Year, @Month = @Month;
EXEC dbo.MonthlyReport_Recompile     @Year  = @Year, @Month = @Month;
EXEC dbo.MonthlyReport_DynamicSQL    @Year  = @Year, @Month = @Month;

/* repeat for @Year = 2011, @Month = 9  --    157 rows */

/* repeat for @Year = 2014, @Month = 4  --  2,115 rows */

The result? Every single plan yields the same Index Seek, but the estimates are only correct across all three date ranges in the OPTION (RECOMPILE) version. The rest continue to use the estimates derived from the first set of parameters (July 2012), and so while they get better estimates for the first execution, that estimate won't necessarily be any better for subsequent executions using different parameters (a classic, textbook case of parameter sniffing):

Note that the above is not *exact* output from SQL Sentry Plan Explorer - for example, I removed the statement tree rows that showed the outer stored procedure calls and parameter declarations.

It will be up to you to determine whether the tactic of compiling every time is best for you, or whether you need to "fix" anything in the first place. Here, we ended up with the same plans, and no noticeable differences in runtime performance metrics. But on bigger tables, with more skewed data distribution, and larger variances in predicate values (e.g. consider a report that can cover a week, a year, and anything in between), it may be worth some investigation. And note that you can combine methods here - for example, you could switch to proper date parameters *and* add OPTION (RECOMPILE), if you wanted.

Conclusion

In this specific case, which is an intentional simplification, the effort of getting the correct estimates didn't really pay off - we didn't get a different plan, and the runtime performance was equivalent. There are certainly other cases, though, where this will make a difference, and it is important to recognize estimate disparity and determine whether it might become an issue as your data grows and/or your distribution skews. Unfortunately, there is no black-or-white answer, as many variables will affect whether compilation overhead is justified - as with many scenarios, IT DEPENDS...

The post Paying Attention to Estimates appeared first on SQLPerformance.com.

• Query Optimizer Additions in SQL Server 2016

(The documentation has also been updated, both the Compatibility Level topic and the UPDATE STATISTICS topic.)

Wouldn't it be nice, though, to be able to specify how many CPUs can actually be used for these operations (other than just allowing the cap of 16)? I think that being able to limit this to 4 or 8 would be an obvious and logical thing to support. Especially for customers running systems with 16 or fewer cores, or multiple instances on a box, who can't rely on Enterprise features like Resource Governor (which most Enterprise Customers couldn't be bothered using either, IMHO).

The business justification for this would be the same as the justifications used for adding MAXDOP support REBUILD, DBCC CHECKDB and its family of maintenance operations, etc. You want to prevent this type of activity from taking over all the cores, without doing something as drastic as turning off auto-updates or using instance-wide MAXDOP – because not everybody has the luxury of maintenance windows.

And in this case, instance-wide MAXDOP won't help anyway, because SQL Server 2016 RTM has a bug where MAXDOP is ignored for sampled statistics updates. A fix is forthcoming, but I thought you should know; if this is causing you an issue, one option is to use a lower compatibility level.

But I will reiterate something I say often: Compatibility level is getting far too crowded. If I want parallel sampled stats on my database but I have enough cardinality estimation regressions to require the old CE, I have to pick one or the other.

And another thing: Resource Governor is overkill for this use case, and limiting core usage from statistics updates shouldn't really be an Enterprise feature (just like the REBUILD and CHECKDB mentioned above). Please don't tell me that RG is an acceptable alternative, because it's only possible for users with Enterprise Edition *and* workload classifications that should be constrained by MAXDOP all the time. I should be able to limit this by specific operation (or, say, for only my biggest/problem tables), not by constraining a login's entire session.

How I wish they would do it

Ideally, we would be able to set this at the database level, using the new DATABASE SCOPED CONFIGURATION option, and at the statement level, using the familiar OPTION (MAXDOP n) syntax. Statement level would win, and any sample mode statistics updates (including automatic) without an explicit MAXDOP hint would fall back to the database level setting. This would allow me to set a MAXDOP of 4, for example, for all automatic statistics updates that happen at unpredictable times, but 8 or 16 for manual operations in known maintenance windows. As one example.

If you want to vote for this, please see the following Connect item, and add a business justification for this (a la Michael Campbell):

• Connect #628971 : Add MAXDOP parameter to Update Stats

Of course, that item has been there since 2010, so there is no mention at all about the DATABASE SCOPED CONFIGURATION avenue, which is why I left a comment, too.

In the meantime, if you want to disable parallelism for sample mode, there is a trace flag to effectively return to older behavior (you can also do this by reverting to a compatibility level less than 130, but I don't recommend this because it affects a lot of other things). I will update this space when I've been given the okay to disclose the trace flag publicly, but right now, Microsoft is holding it tight to their chest.

The post A potential improvement for statistics updates : MAXDOP appeared first on SQLPerformance.com.

Cumulative Update 6 for SQL Server 2014 Service Pack 1 introduced a new trace flag, 7471, that changes the locking behavior of UPDATE STATISTICS tasks in SQL Server (see KB #3156157). In this post we’ll look at the difference in locking behavior and where this trace flag might be useful.

To set up an appropriate demo environment for this post, I used the AdventureWorks2014 database and created an enlarged version SalesOrderDetail table based on the script available on my blog. The SalesOrderDetailEnlarged table was enlarged to 2GB in size so that UPDATE STATISTICS WITH FULLSCAN operations could be executed against different statistics on the table concurrently. I then used sp_whoisactive to examine the locks being held by both sessions.

Behavior without TF 7471

The default behavior of SQL Server requires an exclusive lock (X) on the OBJECT.UPDSTATS resource for the table whenever an UPDATE STATISTICS command is executed against a table. You can see this in the sp_whoisactive output for two concurrent executions of UPDATE STATISTICS WITH FULLSCAN against the Sales.SalesOrderDetailEnlarged table, using different index names for the statistics to update. This results in blocking of the second execution of the UPDATE STATISTICS until the first execution completes.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged]
    ([PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]) WITH FULLSCAN;

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged]
    ([IX_SalesOrderDetailEnlarged_ProductID]) WITH FULLSCAN;

The granularity of the lock resource being on OBJECT.UPDSTATS prevents concurrent updates of multiple statistics against the same table. Hardware enhancements in recent years have really changed the potential bottlenecks that are common to SQL Server implementations, and just as changes have been made to DBCC CHECKDB to make it run faster, changing the locking behavior of UPDATE STATISTICS to allow concurrent updates of statistics on the same table can significantly reduce maintenance windows for VLDBs, especially where there is sufficient CPU and I/O subsystem capacity to allow concurrent updates to happen without impacting end-user experiences.

Behavior with TF 7471

The locking behavior with trace flag 7471 enabled changes from requiring an exclusive lock (X) on the OBJECT.UPDSTATS resource to requiring an update lock (U) on the METADATA.STATS resource for the specific statistic that is being updated, which allows concurrent executions of UPDATE STATISTICS on the same table. The output of sp_whoisactive for the same UPDATE STATISTICS WITH FULLCAN commands with the trace flag enabled is shown below:

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged]
    ([PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]) WITH FULLSCAN;

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged]
    ([IX_SalesOrderDetailEnlarged_ProductID]) WITH FULLSCAN;

For VLDBs, which are becoming much more common place, this can make a big difference in the time it takes to perform statistics updates across a server.

I recently blogged about a parallel maintenance solution for SQL Server using Service Broker and Ola Hallengren’s maintenance scripts as a way to optimize nightly maintenance tasks and reduce the time required to rebuild indexes and update statistics on servers that have plenty of CPU and I/O capacity available. As a part of that solution, I forced an order of queuing tasks to Service Broker to try and avoid having concurrent executions against the same table for both index rebuild/reorganize and UPDATE STATISTICS tasks. The aim of this was to keep the workers as busy as possible until the end of the maintenance tasks, where things would serialize in execution based on blocking of concurrent tasks.

I did some modifications to the processing in that post to just test the effects of this trace flag with concurrent statistics updates only, and the results are below.

Testing Concurrent Statistics Update Performance

To test the performance of only updating statistics in parallel using the Service Broker configuration, I started out by creating a column statistic on every column in the AdventureWorks2014 database using the following script to generate the DDL commands to be executed.

USE [AdventureWorks2014]
GO

SELECT *, 'DROP STATISTICS ' + QUOTENAME(c.TABLE_SCHEMA) + '.' 
  + QUOTENAME(c.TABLE_NAME) + '.' + QUOTENAME(c.TABLE_NAME 
  + '_' + c.COLUMN_NAME) + ';
GO
CREATE STATISTICS ' +QUOTENAME(c.TABLE_NAME + '_' + c.COLUMN_NAME) 
  + ' ON ' + QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME) 
  + ' (' +QUOTENAME(c.COLUMN_NAME) + ');' + '
GO'
 FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.TABLES AS t 
    ON c.TABLE_CATALOG = t.TABLE_CATALOG AND 
       c.TABLE_SCHEMA = t.TABLE_SCHEMA AND 
       c.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
 AND c.DATA_TYPE <> N'xml';

This is not something that you would typically want to do, but it gives me plenty of statistics for parallel testing of the impact of the trace flag on updating stats concurrently. Instead of randomizing the order in which I queue the tasks to Service Broker, I instead just queue the tasks as they exist in the CommandLog table based on the ID of the table, simply incrementing the ID by one until all of the commands have been queued for processing.

USE [master];

-- Clear the Command Log
TRUNCATE TABLE [master].[dbo].[CommandLog];

DECLARE @MaxID INT;
SELECT @MaxID = MAX(ID) 
FROM master.dbo.CommandLog;

SELECT @MaxID = ISNULL(@MaxID, 1)

---- Load new tasks into the Command Log
EXEC master.dbo.IndexOptimize
  @Databases = N'AdventureWorks2014',
  @FragmentationLow = NULL,
  @FragmentationMedium = NULL,
  @FragmentationHigh = NULL,
  @UpdateStatistics = 'ALL',
  @StatisticsSample = 100,
  @LogToTable = 'Y',
  @Execute = 'N';

DECLARE @NewMaxID INT
SELECT @NewMaxID = MAX(ID) 
FROM master.dbo.CommandLog;

USE	msdb;

DECLARE @CurrentID INT = @MaxID
WHILE (@CurrentID <= @NewMaxID)
BEGIN
	-- Begin a conversation and send a request message
	DECLARE @conversation_handle UNIQUEIDENTIFIER;
	DECLARE @message_body XML;

	BEGIN TRANSACTION;

	BEGIN DIALOG @conversation_handle
		 FROM SERVICE [OlaHallengrenMaintenanceTaskService]
		 TO SERVICE N'OlaHallengrenMaintenanceTaskService'
		 ON CONTRACT [OlaHallengrenMaintenanceTaskContract]
		 WITH ENCRYPTION = OFF;

	SELECT @message_body = N''+CAST(@CurrentID AS NVARCHAR)+N'';

	SEND ON CONVERSATION @conversation_handle
		 MESSAGE TYPE [OlaHallengrenMaintenanceTaskMessage]
		 (@message_body);

	COMMIT TRANSACTION;

	SET @CurrentID = @CurrentID + 1;
END

WHILE EXISTS (SELECT 1 FROM OlaHallengrenMaintenanceTaskQueue WITH(NOLOCK))
BEGIN
	WAITFOR DELAY '00:00:01.000'
END

WAITFOR DELAY '00:00:06.000'

SELECT DATEDIFF(ms, MIN(StartTime), MAX(EndTime)) FROM master.dbo.CommandLog;
GO 10

Then I waited for all tasks to complete, measured the delta in start time and end time of the task executions, and took the average of ten tests to determine the improvements just for updating statistics concurrently using the default sampling and full scan updates.

The test results show that even with the blocking that occurs under the default behavior without the trace flag, sampled updates of statistics runs 6% faster and full scan updates run 16% faster with five threads processing the tasks queued to Service Broker. With trace flag 7471 enabled, the same sampled updates of statistics runs 38% faster and full scan updates run 45% faster with five threads processing the tasks queued to Service Broker.

Potential Challenges with TF 7471

As compelling as the test results are, nothing in this world is free and in my initial testing of this I encountered some issues with the size of the VM that I was using on my laptop that created workload problems.

I originally was testing parallel maintenance using a 4vCPU VM with 4GB RAM that I setup specifically for this purpose. As I began to increase the number of MAX_QUEUE_READERS for the activation procedure in Service Broker I began to encounter issues with RESOURCE_SEMAPHORE waits when the trace flag was enabled, allowing parallel updates of statistics on the enlarged tables in my AdventureWorks2014 database due to the memory grant requirements for each of the UPDATE STATISTICS commands that were running. This was alleviated by changing the VM configuration to 16GB RAM but this is something to monitor and watch for when performing parallel tasks on larger tables, to include index maintenance, since memory grant starvation will also affect end user requests that may be trying to execute and need a larger memory grant as well.

The product team has also blogged about this trace flag and in their post they warn that deadlock scenarios may occur during concurrent updating of statistics while statistics are also being created. This is not something I have run into yet during my testing, but it is definitely something to be aware of (Kendra Little warns about it too). As a result of that, their recommendation is that this trace flag only is enabled during parallel maintenance task execution and then it should be disabled for normal workload periods.

Enjoy!

The post Improved Support for Parallel Statistics Rebuilds appeared first on SQLPerformance.com.

SQL Server Books Online (BOL), whitepapers, and many other sources will show you how and why you might want to update statistics on a table or index. However, you only get one way to shape those values. I will show you how you can create the statistics exactly the way you want within the bounds of the 200 steps available.

In our application, the Scheduling user is regularly reading and writing data that represents events for tomorrow and the next couple of days. Data for today and earlier is not used by the Scheduler. First thing in the morning, the data set for tomorrow starts at a couple hundred rows and by midday can be 1400 and higher. The following chart will illustrate the row counts. This data was collected on the morning of Wednesday November 18, 2015. Historically, you can see that the regular row count is approximately 1,400 except for weekend days and the next day.

For the Scheduler the only pertinent data is the next few days. What is happening today and happened yesterday isn’t relevant to his activity. So how does this cause a problem? This table has 2,259,205 rows which means the change in row counts from morning to noon will not be enough to trigger a SQL Server initiated statistics update. Furthermore, a manually scheduled job that builds statistics using UPDATE STATISTICS populates the histogram with a sample of all the data in the table but may not include the relevant information. This row count delta is enough to change the plan. However, without a statistics update and an accurate histogram, the plan will not change for the better as the data changes.

A relevant selection of the histogram for this table from a backup dated on 11/4/2015 might look like this:

The values of interest are not reflected accurately in the histogram. What would be used for the date of 11/5/2015 would be the high value 1/4/2016. Based on the graph, this histogram is clearly not a good source of information for the optimizer for the date of interest. Forcing the values of use into the histogram isn’t reliable, so how can you do that? My first attempt was to repeatedly use the WITH SAMPLE option of UPDATE STATISTICS and query the histogram until the values I needed were in the histogram (an effort detailed here). Ultimately, that approach proved to be unreliable.

This histogram can lead to a plan with this type of behavior. The underestimate of rows produces a Nested Loop join and an index seek. The reads are subsequently higher than they should be because of this plan choice. This will also have an effect on statement duration.

What would work much better is to create the data exactly how you want it, and here’s how to do that.

There is an unsupported option of UPDATE STATISTICS: STATS_STREAM. This is used by Microsoft Customer Support to export and import statistics so they can get an optimizer recreate without having all the data in the table. We can use that feature. The idea is to create a table that mimics the DDL of the statistic we want to customize. The relevant data is added to the table. The statistics are exported and imported into the original table.

In this case, it is a table with 200 rows of not NULL dates and 1 row that includes the NULL values. Additionally, there is an index on that table that matches the index that has the bad histogram values.

The name of the table is tblTripsScheduled. It has a non-clustered index on (id, TheTripDate) and a clustered index on TheTripDate. There are a handful of other columns, but only the ones involved in the index are important.

Create a table (temp table if you want) that mimics the table and index. The table and index looks like this:

CREATE TABLE #tbltripsscheduled_cix_tripsscheduled(
       id INT NOT NULL
       , tripdate DATETIME NOT NULL
       , PRIMARY KEY NONCLUSTERED(id, tripdate)
);

CREATE CLUSTERED INDEX thetripdate ON #tbltripsscheduled_cix_tripsscheduled(tripdate);

Next, the table needs to be populated with 200 rows of data that the statistics should be based on. For my situation, it is the day-of through the next sixty days. The past and beyond 60 days is populated with a "randomish" selection of every 10 days. (The cnt value in the CTE is a debug value. It does not play a role in the final results.) The descending order for the rn column ensures that the 60 days are included, and then as much of the past as possible.

DECLARE @date DATETIME = '20151104';

WITH tripdates
AS 
(
  SELECT thetripdate, COUNT(*) cnt
  FROM dbo.tbltripsscheduled
    WHERE NOT thetripdate BETWEEN @date AND @date
    AND thetripdate < DATEADD(DAY, 60, @date) --only look 60 days out GROUP BY thetripdate
    HAVING DATEDIFF(DAY, 0, thetripdate) % 10 = 0
  UNION ALL
  SELECT thetripdate, COUNT(*) cnt
  FROM dbo.tbltripsscheduled
    WHERE thetripdate BETWEEN @date AND DATEADD(DAY, 60, @date)
    GROUP BY thetripdate
), 
tripdate_top_200
AS 
(
  SELECT *
  FROM 
  (
    SELECT *, ROW_NUMBER() OVER(ORDER BY thetripdate DESC) rn
    FROM tripdates
  ) td
  WHERE rn <= 200
)
INSERT #tbltripsscheduled_cix_tripsscheduled (id, tripdate) 
SELECT t.tripid, t.thetripdate
FROM tripdate_top_200 tp
INNER JOIN dbo.tbltripsscheduled t ON t.thetripdate = tp.thetripdate;

Our table is now populated with every row that is valuable for the user today and a selection of historical rows. If the column TheTripdate was nullable, the insert would have also included the following:

UNION ALL
SELECT id, thetripdate
FROM dbo.tbltripsscheduled
WHERE thetripdate IS NULL;

Next, we update the statistics on the index of our temp table.

UPDATE STATISTICS #tbltrips_IX_tbltrips_tripdates (tripdates) WITH FULLSCAN;

Now, export those statistics to a temp table. That table looks like this. It matches the output of DBCC SHOW_STATISTICS WITH HISTOGRAM.

CREATE TABLE #stats_with_stream
(
       stream VARBINARY(MAX) NOT NULL
       , rows INT NOT NULL
       , pages INT NOT NULL
);

DBCC SHOW_STATISTICS has an option to export the statistics as a stream. It is that stream that we want. That stream is also the same stream that the UPDATE STATISTICS stream option uses. To do that:

INSERT INTO #stats_with_stream --SELECT * FROM #stats_with_stream
EXEC ('DBCC SHOW_STATISTICS (N''tempdb..#tbltripsscheduled_cix_tripsscheduled'', thetripdate)
  WITH STATS_STREAM,NO_INFOMSGS');

The final step is to create the SQL that updates the statistics of our target table, and then execute it.

DECLARE @sql NVARCHAR(MAX);
SET @sql = (SELECT 'UPDATE STATISTICS tbltripsscheduled(cix_tbltripsscheduled) WITH
STATS_STREAM = 0x' + CAST('' AS XML).value('xs:hexBinary(sql:column("stream"))',
'NVARCHAR(MAX)') FROM #stats_with_stream );
EXEC (@sql);

At this point, we have replaced the histogram with our custom-built one. You can verify by checking the histogram:

In this selection of the data on 11/4, all the days from 11/4 onwards are represented, and the historical data is represented and accurate. Revisiting the portion of the query plan shown earlier, you can see the optimizer made a better choice based on the corrected statistics:

There is a performance benefit to imported stats. The cost to compute the stats are on an "offline" table. The only downtime for the production table is the duration of the stream import.

This process does use undocumented features and it looks like it could be dangerous, but remember there is an easy undo: the update statistics statement. If something goes wrong, the statistics can always be updated using standard T-SQL.

Scheduling this code to run regularly can greatly help the optimizer produce better plans given a data set that changes over the tipping point but not enough to trigger a statistics update.

When I finished the first draft of this article, the row count on the table in the first chart changed from 217 to 717. That is a 300% change. That is enough to change the behavior of the optimizer but not enough to trigger a statistics update. This data change would have left a bad plan in place. It is with the process described here that this problem is solved.

References:

• UPDATE STATISTICS (Books Online)
• SQL 2008 Statistics Whitepaper
• Tipping Point Search

The post Custom Built Statistics appeared first on SQLPerformance.com.

(Note that comments hold much more weight than mere vote counts, so please state your business case, if you have one that is sharable.)

MAXDOP hint for UPDATE STATISTICS

SQL Server 2016 has added a MAXDOP hint for DBCC CHECK commands, so why not for stats updates? On partitioned tables this can have a great impact on the rest of the workload. We should also be able to override the system-defined MAXDOP for automatic stats updates, but for now I'd be happy with more control over manual statistics management. The request is captured in the following Connect item:

• Connect #628971 : Add MAXDOP parameter to Update Stats

Let the query optimizer see partition-level stats

Erin Stellato has blogged about the benefits of incremental stats here, but really hit the nail on the head about its problems in this post: Incremental Statistics are NOT used by the Query Optimizer. Please read through that and then vote and comment on the item I just created (I can't believe I never noticed that a DCR didn't already exist for this):

• Connect #2010834 : Optimizer should actually *use* per-partition stats

Auto-stats should consider the number of rows in a filtered index/stat

Currently, relying on automatic updates to filtered indexes and statistics is like Waiting for Godot – the algorithm uses the number of rows in the table when determining the churn threshold, not the number of rows in the index. This means that most filtered indexes – and indeed the most useful filtered indexes – will never be updated automatically. (I talk about this here, and Kimberly Tripp talks about it here and here. I'm sure others have blogged about it too.) I think it's time for this to change – if you agree, please vote and comment on Joe Sack's item (the title indicates filtered statistics, but it really relates to both):

• Connect #509638 : Suggesting change to filtered statistics updates

The post Please help improve SQL Server statistics! appeared first on SQLPerformance.com.