![]() ![]() colmodctr is not transactionally consistent which means that is not affected by the rolled-back changes i.e if a transaction inserts 10 rows in a table and then roll-back, the counter will still report 10 changes. : ) colmodctrĬolmodctr is an ever-increasing counter that tracks the changes made on tables (a counter per column excluding the non-persistent computed columns). The following examples assume the default settings for the Sql Server’s options related to the statistics:Ī bit of theory first before proceeding with the tests. WHERE i.id IN (OBJECT_ID(N'dbo.SalesOrderDetail'), (OBJECT_ID(N'dbo.Products'))) ,i.indid -If statistics correspond to an index, the stats_id value in the sys.stats is the same as the index_id WHERE s.object_id IN (OBJECT_ID(N'dbo.SalesOrderDetail'), (OBJECT_ID(N'dbo.Products'))) OUTER APPLY sys.dm_db_stats_properties (s.object_id,s.stats_id) AS sp , = sp.stats_id - If statistics correspond to an index, the stats_id value is the same as the index_id value in the sys.indexes. Information about the statistics/indexes on the tables can be retrieved using the queries below. USE masterĭROP TABLE IF EXISTS dbo.SalesOrderDetailĪDD CONSTRAINT PK_SalesOrderDetailID PRIMARY KEY CLUSTERED (SalesOrderDetailID) ĬREATE NONCLUSTERED INDEX IX_SalesOrderDetail_ProductIDĪDD CONSTRAINT PK_ProductID PRIMARY KEY CLUSTERED(ProductID)ĬREATE NONCLUSTERED INDEX NCI_Products_ListPriceĭROP PROCEDURE IF EXISTS dbo.TestQueryExecution ![]() The version provides easy access to the information about the interesting statistics saved with the query plan (SSMS – SELECT Plan Operator, Properties,OptimizerStatsUsage).īelow is the set of SQL Server object definitions used for the testing. Set AdventureWorks DB compatibility level to 140 – SQL Server 2017. I’ve used the AdventureWorks database to set up the test environment and MS Profiler to capture various Events relevant to the experiments. The idea is to create a set of experiments that will capture the behavior of a stored procedure through the different phases of the query compilation/recompilation process, particularly those related to the statistics that are used to generate the execution plan.įigure 1, Batch Compilation/Recompilation diagram To begin with, let’s analyze the batch compilation/recompilation diagram ( By Arun Marathe, Jul 2004, Batch Compilation, Recompilation and Plan Caching Issues in SQL Server 2005). The topic is related to Temporary tables statistics when used in stored procedures. This blog attempts to explain what are the “interesting statistics”, when they are updated and how the statistical information relates to the query recompilation process. These are statistical information used by the Query optimizer when constructing a good enough query execution plan. Some of the most important elements used by the Query optimizer when constructing a good plan are the “Interesting statistics”. The query optimization process sometimes requires an understanding of how the SQL Server’s Query engine compiles, re-compiles, and executes SQL batches. ![]()
0 Comments
Leave a Reply. |