As I mentioned in my last post, one of the sessions I presented last weekend at SQLSaturday #118 was on statistics, and an interesting topic that came up toward the end of the session was the Auto Update Statistics option.
I’ve blogged about this option before, and whether or not you leave it enabled for a database is not as cut and dried as to whether you leave the Auto Create Statistics option enabled (I’ve yet to personally find a reason to disable Auto Create Statistics). The Auto Update Statistics option exists for the benefit of performance – when statistics are out of date, the engine will update them for you. The engine knows when statistics are out of date because it tracks modifications to your data. The method for tracking modifications changed between SQL 2000 and SQL 2005, and where the modifications are tracked changed between SQL 2005 and SQL 2008. For this post, I’m just going to discuss SQL 2008 and higher.
Data modifications are tracked at the column level in SQL 2008 and higher in the hidden and undocumented sys.sysrscols system table, specifically in the rcmodified column. During my statistics session last Saturday, one attendee asked if modifications were still tracked if the Auto Update Statistics was disabled for a database. In short: Yes.
I admit, I didn’t know the answer off the top of my head – but my logic when I answered the question was that the setting exists at the database level and the engine is tracking modifications for tables and indexed views across numerous databases. I didn’t expect that the engine would check the database Auto Update Statistics option for each modification, that’s just way too much overhead. But then I wanted to know for sure so I came home and tested it. The necessary statements are below, and the DAC is utilized (so if you don’t have it enabled, you’ll need to do that first).
Now, as I wrote this up, I wondered why the engine really continued to track it. I don’t want to assume it is simply overhead, but it makes a lot of sense. You can flip the Auto Update option at will, and you can also disable automatic updates at the table, index and statistic level. If the engine had to reset the rcmodified column for a statistic, index, table or all tables in a database every time one of the settings changed, it could take time and would generate some level of overhead. Ultimately, it’s just less work to continually track them, and presumably the engine is very efficient at tracking that information.
The script below can be used to show that data modifications are still tracked, even if the Auto Update Statistics option is disabled. Realize that you’re playing with an undocumented system table and the DAC. You’re off the grid, proceed with caution, and realize that the behavior you’re seeing could change in a future version. If you want more details about sysrscols, I highly recommend Paul Randal’s post, How are per-column modification counts tracked? I admit, I used his query for sysrscols, but I did ask for permission first 🙂
-- create a database for testing
CREATE DATABASE [NCI_Corruption]
ON PRIMARY (
NAME = N'NCI_Corruption',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption.mdf' ,
SIZE = 4096KB ,
FILEGROWTH = 1024KB )
LOG ON (
NAME = N'NCI_Corruption_log',
FILENAME = N'C:\Databases\SQL2008R2\NCI_Corruption_log.ldf' ,
SIZE = 1024KB ,
FILEGROWTH = 1024KB );
ALTER DATABASE [NCI_Corruption] SET RECOVERY SIMPLE;
USE NCI_Corruption;
GO
-- create a table
CREATE TABLE MovieInfoHistory (
MovieName VARCHAR(800),
ReleaseDate SMALLDATETIME,
Rating VARCHAR(5)
);
-- create an index
CREATE NONCLUSTERED INDEX NCI_MovieName ON dbo.MovieInfoHistory (MovieName);
-- connect with the DAC if not already...
-- check rcmodified - it should be 0
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');
-- add some data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Caddyshack', '1980-07-25', 'R'),
('Bill & Ted''s Excellent Adventure', '1989-02-17 00:00:00', 'PG'),
('Apollo 13', '1995-05-30 00:00:00', 'PG'),
('The Hunt for Red October', '1990-03-02 00:00:00', 'PG'),
('A Few Good Men', '1994-12-11 00:00:00', 'R'),
('The Natural', '1984-05-11 00:00:00', 'PG'),
('The Truman Show', '1998-06-05 00:00:00', 'PG-13'),
('All The President''s Men', '1976-04-09 00:00:00', 'R'),
('The Right Stuff', '1983-10-21 00:00:00', 'PG-13');
CHECKPOINT;
GO
-- verify that rcmodified has increased
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');
-- turn off auto update stats
ALTER DATABASE NCI_Corruption SET AUTO_UPDATE_STATISTICS OFF;
-- add some more data
INSERT INTO dbo.MovieInfoHistory (
MovieName, ReleaseDate, Rating
)
VALUES
('Miracle', '2004-02-06 00:00:00', 'PG'),
('Walk the Line', '2005-11-18 00:00:00', 'PG-13'),
('Bull Durham', '1988-06-15 00:00:00', 'R'),
('Field of Dreams', '1989-04-21 00:00:00', 'PG'),
('G.I. Jane', '1997-08-22 00:00:00', 'R'),
('St. Elmo''s Fire', '1985-06-28 00:00:00', 'R'),
('Goonies', '1985-06-07 00:00:00', 'TV14'),
('The Shawshank Redemption', '1994-10-14 00:00:00', 'R'),
('The Bad News Bears', '1976-04-07 00:00:00', 'PG');
CHECKPOINT;
GO
-- verify that rcmodified has increased again
SELECT
p.[object_id],
p.[index_id],
rs.[rscolid],
rs.[rcmodified]
FROM sys.sysrscols rs
JOIN sys.partitions p ON rs.[rsid] = p.[partition_id]
WHERE p.[object_id] = OBJECT_ID ('MovieInfoHistory');
-- clean up
USE master;
GO
DROP DATABASE NCI_Corruption;
