13

I noticed a relatively long running (20 min+) auto update statistics operation in a daily datawarehouse build. The table involved is

CREATE TABLE [dbo].[factWebAnalytics](
    [WebAnalyticsId] [bigint] IDENTITY(1,1) NOT NULL,
    [MarketKey] [int] NOT NULL CONSTRAINT [DF_factWebAnalytics_MarketKey]  DEFAULT ((-1)),
    /*Other columns removed*/
 CONSTRAINT [PK_factWebAnalytics] PRIMARY KEY CLUSTERED 
(
    [MarketKey] ASC,
    [WebAnalyticsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [MarketKeyPS]([MarketKey])
) ON [MarketKeyPS]([MarketKey])

This is running on Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) so writable columnstore indexes are not available.

The table contains data for two distinct Market keys. The build switches out the partition for a specific MarketKey to a staging table, disables the columnstore index, performs necessary writes, rebuilds the columnstore, then switches it back in.

The execution plan for the update statistics shows that it pulls out all rows from the table, sorts them, gets the estimated number of rows badly wrong and spills to tempdb with spill level 2.

enter image description here

Running

SELECT [s].[name] AS "Statistic",
       [sp].*
FROM   [sys].[stats] AS [s]
       OUTER APPLY sys.dm_db_stats_properties ([s].[object_id], [s].[stats_id]) AS [sp]
WHERE  [s].[object_id] = OBJECT_ID(N'[dbo].[factWebAnalytics]'); 

Shows

enter image description here

If I explicitly try and reduce the sample size of that index's statistics to that used by the others with

UPDATE STATISTICS [dbo].[factWebAnalytics] [PK_factWebAnalytics] WITH SAMPLE 897667 ROWS

The query runs for 20 minutes+ again and the execution plan shows that it is processing all rows not the 897,667 sample requested.

The statistics generated at the end of all this aren't very interesting and definitely don't seem to warrant the time spent on a full scan.

Statistics for INDEX 'PK_factWebAnalytics'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_factWebAnalytics             Jan 22 2016 11:31AM             420072086                       420072086                       2                               0                               12                              NO                                                              420072086                       

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.5                             4                               MarketKey                       
2.380544E-09                    12                              MarketKey, WebAnalyticsId       

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                               0                               3.441652E+08                    0                               1                               
2                               0                               7.590685E+07                    0                               1                               

Any ideas why I am encountering this behaviour and what steps I can take other than using NORECOMPUTE on these?


A repro script is here. It simply creates a table with a clustered PK and a columnstore index and tries to update the PK stats with a low sample size. This does not use partitioning - showing that the partitioning aspect is not required. However the use of partitioning described above does make matters worse as switching out the partition and then switching it back in (even without any other changes) will increase the modification_counter by double the number of rows in the partition thus practically guaranteeing that the statistics will be considered stale and auto updated.

I've tried adding a non clustered index to the table as indicated in KB2986627 (both filtered with no rows and then, when that failed, a non filtered NCI also with no effect).

The repro did not show the problematic behaviour on build 11.0.6020.0 and after upgrading to SP3 the issue is now fixed.

10

The first thing I would try is updating the SQL Server instance from the SP1 CU16 with QFE that you have right now, to SP3 CU1 (current 2012 build) then retesting to see if the behaviour is the same.

For example:

FIX: UPDATE STATISTICS performs incorrect sampling and processing for a table with columnstore index in SQL Server

...first released in SP2 CU2 may be relevant.

That said, I'm not sure if 2012 columnstore supported tablesample, required for sampled statistics. I'll update this answer once a repro is available in the question.

  • 1
    (Regarding the last paragraph) SELECT WebAnalyticsId, MarketKey from [dbo].[factWebAnalytics] TABLESAMPLE (897667 ROWS) ORDER BY MarketKey, WebAnalyticsId runs in less than 30 seconds for me. It doesn't use the columnstore index though. It uses the clustered index. – Martin Smith Jan 22 '16 at 13:36
  • 2
    Yes it definitely looks like something fixed in later versions. I produced a simple repro here pastebin.com/7f4TwmKW and on a test server running 11.0.5343.0 found my request for a sample size of 10,000 rows was ignored and all 8,000,000 rows sampled i.stack.imgur.com/DbbjZ.png (plan much the same as the one in the question) - But I don't encounter this on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (rows sampled there is 274,649 which is pretty close to the estimated number of rows in the earlier build and the plan uses the CI rather than the columnstore.) – Martin Smith Jan 22 '16 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.