After having excellent responses to my quiz – "Why SELECT * throws an error but SELECT COUNT(*) does not?" I decided to ask another puzzling question to all of you.
I am running this test on SQL Server 2008 R2. Here is the quick scenario about my setup.
- Create Table
- Insert 1000 Records
- Check the Statistics
- Now insert 10 times more 10,000 indexes
- Check the Statistics – it will be NOT updated
Note: Auto Update Statistics and Auto Create Statistics for database is TRUE
.
Expected Result – Statistics should be updated – SQL SERVER – When are Statistics Updated – What triggers Statistics to Update
Now the question is why statistics are not updated?
The common answer is – we can update the statistics ourselves using:
UPDATE STATISTICS TableName WITH FULLSCAN, ALL
However, the solution I am looking for is where statistics should be updated automatically based on algorithm mentioned here.
Now the solution is to ____________________.
Vinod Kumar is not allowed to participate here as he is the one who has helped me to build this puzzle.
I will publish the solution next week. Please leave a comment and if your comment consists of the valid answer, I will publish it with due credit.
Here is the script to reproduce the scenario which I mentioned.
CREATE DATABASE SampleDB
GO
USE SampleDB
GO
CREATE TABLE ExecTable (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
sp_helpstats N'ExecTable', 'ALL'
GO
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City = 'New York'
GO
sp_helpstats N'ExecTable', 'ALL'
GO
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
INSERT INTO ExecTable (ID,FirstName,LastName,City)
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 3 THEN 'Los Angeles'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 7 THEN 'La Cinega'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 13 THEN 'San Diego'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%20 = 17 THEN 'Las Vegas'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
SELECT FirstName, LastName, City
FROM ExecTable
WHERE City = 'New York'
GO
sp_helpstats N'ExecTable', 'ALL'
GO
DBCC SHOW_STATISTICS('ExecTable', _WA_Sys_00000004_7D78A4E7);
GO
DROP TABLE ExecTable
GO
USE MASTER
GO
ALTER DATABASE SampleDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE SampleDB
GO
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.