Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in my database that we use as a filestore, the file itself is stored in a varbinary column, which all seemed to work well until recently, we noticed one of our instances of this table had essentially "jammed" on an insert statement.

Checking sys.dm_os_waiting_tasks showed that the insert statement had triggered a statistics update, and that this statistics update was taking a very long time. (17 minutes).

Here's the statement we found running:

SELECT StatMan([SC0], [LC0]) FROM 
   (SELECT TOP 100 PERCENT CONVERT([varbinary](200), 
        SUBSTRING ([Data], 1, 100)++substring([Data], 
        case when LEN([Data])<=200 then 101 else 
        LEN([Data])-99 end, 100)) AS [SC0], 
        datalength([Data]) AS [LC0] 
    FROM [FileSystem].[FileData] WITH 
    (READUNCOMMITTED) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL

There are roughly 2000 rows in this table, here's what it looks like:

CREATE TABLE [FileSystem].[FileData]
(
[Id] [uniqueidentifier] NOT NULL CONSTRAINT [DF__FileData__Id__09DE7BCC] DEFAULT     (newsequentialid()),
[Data] [varbinary] (max) NULL,
[FileHash] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileSize] [bigint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [FileSystem].[FileData] ADD CONSTRAINT [PK_FileData] PRIMARY KEY     CLUSTERED  ([Id]) WITH (STATISTICS_NORECOMPUTE=ON) ON [PRIMARY]
GO

We're aware this table is pretty weird, and we are careful to only every address it with a clustered index seek.

For the moment, we've simply disabled automatic statistic generation for this table, but I'm wondering if that's really best practice. Will performance eventually become a problem without up-to-date statistics (bear in mind, this table is only every addressed by it's clustered index)?

Update:
Okay I'm pretty sure we've worked out what was causing the statistic to be generated:

GO CREATE PROCEDURE [FileSystem].[FileData_AppendNewData_Easy] 
    (@fileDataId  uniqueidentifier ) 
WITH EXECUTE AS CALLER AS 
BEGIN 
   declare @testValue varbinary(max); 

   set @testValue = 0xabcedf012439; 

   Update FileSystem.FileData 
   set Data.Write(@testValue, null, null) 
   where Id = @fileDataId ;
END

This is a simplified version of the procedure that was causing the issue, it seems Data.Write causes some kind of implicit predicate on the data column? Well that solves that mystery, although I'm still not sure what impact disabling the statistics for this table might have, can anyone comment on that?

share|improve this question

migrated from stackoverflow.com Dec 9 '12 at 12:34

This question came from our site for professional and enthusiast programmers.

2  
AFAIK the insert statement itself wouldn't have triggered a statistics update. This would have happened when a query was (re)compiled that would find statistics on that column useful. Do you have any queries with Data in the WHERE clause? –  Martin Smith Dec 6 '12 at 15:47
    
Don't understand how statisticsnorecompute on ID fixed this. The statistic was on Data. AUTO_CREATE_STATISTICS would effect this and is at a database level. And it should not auto create a statistic unless DATA was a predicate of a query. msdn.microsoft.com/en-us/library/bb522682(v=SQL.100).aspx –  Blam Dec 6 '12 at 17:29
    
Here's the actual code used to disable stats for the table: sp_autostats 'filesystem.filedata' [PK_FileData] OFF 2012-09-24 06:52:07.403 [_WA_Sys_00000002_08EA5793] OFF 2012-10-15 10:39:30.090 –  Chris Mawman Dec 7 '12 at 9:54
    
We just switched them all off, on the Id and on the data column, the stat on the data column is "_WA_Sys_00000002_08EA5793". That seems to of had the effect of adding that norecompute option to the table creation statement. Sorry for the red-herring. Thanks for all the help so far, I'm currently scouring statement level traces for anything predicating that data column. I'll post back when i figure it out :) –  Chris Mawman Dec 7 '12 at 9:58
    
EXEC SP_AUTOSTATS '[FileSystem].[FileData]', 'OFF' disabled stats for the table even –  Chris Mawman Dec 7 '12 at 10:12
show 1 more comment

3 Answers 3

Disabling auto-updating statistics for this table in this situation should be just fine, since you only address each record by its unique clustered index. Since the selectivity of the index is perfect, and it never changes, the optimizer should never have a need to look at other query plans based off of changing statistics.

share|improve this answer
add comment

For the moment, we've simply disabled automatic statistic generation for this table, but I'm wondering if that's really best practice.

According to the Microsft comment on this Connect item, yes it is.

In SQL Server 2012, the behaviour was changed so that UPDATE Table SET x.WRITE no longer triggers statistics creation. Other types of queries may still result in statistics being created, so disabling automatic statistics may still be the right thing to do.

My favourite way to very quickly create blank statistics that will not be automatically updated is:

CREATE STATISTICS stats
ON dbo.Test (target_column)
WITH
    SAMPLE 0 ROWS, 
    NORECOMPUTE;
share|improve this answer
add comment

This can be addressed in two additional ways:

  1. Enable Asynchronous Auto Update Statistics and the database level (not my preference). If your production is stuck because due to this issue then this is the quick fix as it is instantaneous but it globally affects the database and can produce sub-par query plans since the plan is generated prior to the stats update.

    ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
    
  2. Remove the auto generate stat and replace it with a manually created one with no-recompute. The downside is that, while this is a surgical approach, it takes some time to create the statistic and will cause blocking.

    DROP STATISTICS [dbo].[TableName].[_WA_Sys_00000004_7D78A4E7]
    GO
    CREATE STATISTICS [_Manual_Stat_Name] ON [dbo].[TableName]([Column]) WITH NORECOMPUTE;
    GO
    

When we were forced to deal with a similar issue we went with option 1 first and later option 2. During a "green zone" we reverted the database wide setting and then replaced the column's statistic with the manually created NORECOMPUTE one.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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