![]() ![]() · I am new to SQL Server statistics. Could you please describe what are the benefits of using the Auto Create Statistics and Auto Update Statistics options? Is there any possibility to disable auto creating statistics on specific table in database, without disabling auto creating statistics for entire database? I have a. · To re-enable the AUTO_UPDATE_STATISTICS option behavior. When OFF, the statistics tree is dropped and SQL Server re-computes the statistics. Should I disable “auto update statistics. auto update statistics in a data warehousing scenarios. SELECT has to fall outside of what SQL Server has. How Automatic Updates to Statistics Can Affect Query Performance. In my previous post, I explored different methods to track automatic updates to statistics to determine if they were affecting query performance. In the latter half of the post I included options, one of which was to enable the Auto Update Statistics Asynchronously database setting. In this post, I want to look at how query performance changes when the automatic update does occur prior to query execution, and what happens to performance if the update is asynchronous. The Set Up. I started with a copy of the Adventure. Works. 20. 12 database, and then created a copy of the Sales. Sql Server 2012 Latest UpdateSql Server 2016 CuStatistics are used by the query optimizer to estimate. SQL Server 2005 can auto create statistics on the. In SQL Server 2000, statistics update was. Order. Header table with over 2. The table has a clustered index on Sales. Order. ID, and a nonclustered index on Customer. ID, Order. Date, Sub. Total. [Note: if you are going to do repeated tests, take a backup of this database at this point to save yourself some time]. After loading the data and creating the nonclustered index, I verified row count and calculated how many rows (approximately) would need to be modified to invoke an automatic update. SELECTOBJECT_NAME([p].[object_id])[Table. Name],[si].[name][Index. Name],[au].[type_desc][Type],[p].[rows][Row. Count],([p].[rows]*. Update. Threshold],[au]. Page. Count],(([au].[total_pages]*8)/1. Total. GB]FROM[sys].[partitions][p]JOIN[sys].[allocation_units][au]ON[p].[partition_id]=[au].[container_id]JOIN[sys].[indexes][si]on[p].[object_id]=[si]. WHERE[p].[object_id]=OBJECT_ID(N'Sales. Big_Sales. Order. Header'); Big_Sales. Order. Header CIX and NCI Information. I also verified the current statistics header for the index: DBCCSHOW_STATISTICS('Sales. Big_Sales. Order. Header',[IX_Big_Sales. Order. Header_Customer. ID_Order. Date_Sub. Total]); NCI Statistics: At Start. I then created the stored procedure that I would use for testing. It’s a straightforward procedure that queries Sales. Big_Sales. Order. Header, and aggregates sales data by Customer. ID and Order. Date for analysis: CREATEPROCEDURE Sales. Get. Customer. Stats. Customer. IDINT. @Start. Date. DATETIME. @End. Date. DATETIMEASBEGINSETNOCOUNTON. SELECT Customer. ID,DATEPART(YEAR, Order. Date),DATEPART(MONTH, Order. Date),COUNT([Sales. Order. ID])as Computed. FROM[Sales].[Big_Sales. Order. Header]WHERE Customer. ID = @Customer. IDAND Order. Date BETWEEN @Start. Dateand @End. Date. GROUPBY Customer. ID,DATEPART(YEAR, Order. Date),DATEPART(MONTH, Order. Date)ORDERBYDATEPART(YEAR, Order. Date),DATEPART(MONTH, Order. Date); ENDFinally, before executing the stored procedure, I created an Extended Events session so I could track query duration using sp_statement_starting and sp_statement_completed. I also added the auto_stats event, because even though I did not expect an update to occur, I wanted to use this same session definition later. CREATEEVENTSESSION[Stats. Update_Query. Perf]ONSERVERADDEVENT sqlserver. ADDEVENT sqlserver. SET collect_statement=(1)),ADDEVENT sqlserver. ADDTARGET package. SETfilename=N'C: \temp\Stats. Update_Query. Perf. WITH(MAX_MEMORY=4. KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3. SECONDS,MAX_EVENT_SIZE=0. KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF); GOThe Test. I started the Extended Events session, and then executed the stored procedure multiple times, using different Customer. IDs: ALTEREVENTSESSION[Stats. Update_Query. Perf]ONSERVERSTATE=START; GO. EXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get. Customer. Stats. 17. GOEXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get. Customer. Stats. 15. GOEXEC Sales. usp_Get. Customer. Stats. 29. GOEXEC Sales. usp_Get. Customer. Stats. 15. GOI verified the execution count, and the plan, by querying the procedure cache: SELECTOBJECT_NAME([st].[objectid]),[st].[text],[qs].[execution_count],[qs].[creation_time],[qs].[last_execution_time],[qs].[min_worker_time],[qs].[max_worker_time],[qs].[min_logical_reads],[qs].[max_logical_reads],[qs].[min_elapsed_time],[qs].[max_elapsed_time],[qp].[query_plan]FROM[sys].[dm_exec_query_stats][qs]CROSSAPPLY[sys].[dm_exec_sql_text]([qs]. CROSSAPPLY[sys].[dm_exec_query_plan]([qs]. WHERE[st].[text]LIKE'%usp_Get. Customer. Stats%'ANDOBJECT_NAME([st].[objectid])ISNOTNULL; Plan Cache: At Start. Query Plan for Stored Procedure, using SQL Sentry Plan Explorer. I could see that the plan was created at 2. With the plan in cache, I stopped the Extended Events session: ALTEREVENTSESSION[Stats. Update_Query. Perf]ONSERVERSTATE=STOP; Next I added about 4. After adding the data, I verified the number of rows in the table: Big_Sales. Order. Header CI: After Data Load. Before I re- ran my stored procedure, I checked the plan cache to make sure nothing had changed, and verified that statistics had not yet updated. Remember, even though the statistics were invalidated at this point, they will not update until a query that uses the statistic is executed (for reference: Understanding When Statistics Will Automatically Update). For the final step, I started the Extended Events session again, and then ran the stored procedure multiple times. After those executions, I checked the plan cache again: Plan Cache: After Data Load. The execution_count is 8 again, and if we look at the create_time of the plan, we can see it’s changed to 2. If we check the plan, we can see that it is the same, even though the plan was recompiled: Query Plan for Stored Procedure, using SQL Sentry Plan Explorer. Analysis of Extended Events Output. I took the first Extended Events file – before data was loaded – and opened it in SSMS, then applied a filter so that only statements from the stored procedure were listed: Extended Events Output: After Initial SP Execution. You can see that there are eight (8) executions of the stored procedure, with query durations that vary slightly. I took the second Extended Events file – after data was loaded – opened it SSMS, and filtered again so that only statements from the stored procedure, as well as auto_stats events, were listed: Extended Events Output: SP Execution After Data Load. The output is truncated, as it is not all needed to show the main result. The blue highlighted entries represent the first execution of the stored procedure, and note that there are multiple steps – the update to statistics is part of the execution. The SELECT statement starts (attach_activity_id. In our example, we actually have updates to three statistics. Once the last update completes (attach_activity_id. Interestingly enough, there is a second sp_statement_starting event for the stored procedure (presumably the first one is disregarded), so the total duration for the stored procedure is calculated without the update to statistics. In this scenario, having statistics automatically update immediately – that is, when a query that uses invalidated statistics executes – causes the query to run longer, even though query duration based on the sp_statement_completed event is still less than 1. The end result is that there is no benefit to query performance, as the plan is exactly same before and after the statistics update. In this scenario, the query plan and execution duration do not change after more data is added to the table, so the update to statistics only hinders its performance. Now let’s see what happens when we enable the Auto Update Statistics Asynchronously option. The Test, Version 2. We start by restoring to the backup that I took before we started the first test. I recreated the stored procedure and then changed the database option to update statistics asynchronously: USE[master]; GOALTERDATABASE[Adventure. Works. 20. 12_Big]SETAUTO_UPDATE_STATISTICS_ASYNCONWITHNO_WAITGOI started the Extended Events session, and again executed the stored procedure multiple times, using different Customer. IDs: ALTEREVENTSESSION[Stats. Update_Query. Perf]ONSERVERSTATE=START; GO. EXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get. Customer. Stats. 11. GOEXEC Sales. usp_Get.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |