GilaMonster (9/21/2012)
Leeland (9/21/2012)
Also, based on BOL, it says that sp_udatestats won't re-update the same stats just updated when a ALTER INDEX - REBUILD command occurs...but I can't find that same claim against UPDATE STATISTICS...Would you say it is safe to assume that the same logic would hold true for UPDATE STATISTICS?
No, it is not.
If that is the case would it be advisable to:
Blanket run UPDATE STATISTICS WITH FULLSCAN, ALL (which I would presume would do a full scan against each column in the table that had NOT already had the stats updated with the index rebuild)
UPDATE STATISTICS WITH FULLSCAN, ALL will update every single statistic regardless of when it was last updated. There's no checks for staleness at all (and all sp_updatestats does is check to see if a single row has changed)
As for what to do, depends on the size of the database and the size of the maintenance windows. If you have the time to blanket rebuild every single index and blanket update every single statistic, go ahead. It's when you don't that you have to get smart about selective rebuilds and selective stats updates.
The last statement is where I am little lost...
I know about managing indexes Clustered / Non-Clustered) selectively, the question for me then comes for managing statistics and how that should be handled...this is something that up until recently I haven't given as much consideration.
As I have learned more I am seeing their importance and want to see what methods are for managing them...
From what I have read and actually tested, the SQL server will auto generate single column statistics on columns used in the predicate of a query...
Why are these system generated statistics created even when there is a covering index?
Example.
CREATE TABLE [dbo].Table_A(
[row_id] [int] IDENTITY(1,1) NOT NULL,
[job_id] [varchar](50) NULL,
[job_name] [varchar](256) NULL,
[job_status] [char](1) NULL,
[transaction_time] [datetime] NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX ci_Table_A ON [dbo].Table_A ([row_id] ASC)
GO
CREATE NONCLUSTERED INDEX nci_Table_A_01 ON [dbo].Table_A ([row_id] ASC) INCLUDE(job_status, job_name)
GO
CREATE NONCLUSTERED INDEX nci_Table_A_02 ON [dbo].Table_A ([row_id], job_status, job_name)
SET IDENTITY_INSERT [dbo].[tb_job_status_checker] ON
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (1, N'F62AC1F0-ED02-449B-934B-0D245FC3DABA', N'job_name test entry Check Refresh', N'N', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (2, N'FCDF6797-498D-44CF-8323-19F0CE065457', N'job_name test entry Check', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (3, N'1EEF0345-6F1E-43F6-9A30-1FE1D4C182FF', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (4, N'A849AC12-A4B8-4C7E-ABD2-3FF3281AE223', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (5, N'611EEBB9-29FD-46CE-A0D4-58EB4B2DDEE2', N'job_name test entry', N'N', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (6, N'4AEC8E61-E57F-4FA6-BF82-60A4147E4998', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (7, N'14094376-1457-4C41-BCDE-8BBEB74A0A7E', N'test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (8, N'45A2771D-6C3D-4632-A36E-9E02979EB47B', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (9, N'0FFD9DD3-A764-43ED-8F85-C1C680C0F244', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (10, N'202419A3-7B3E-4A22-B8DF-D3FA22EB4FF0', N'job_name test entry', N'N', CAST(0x0000A0D600DB3533 AS DateTime))
INSERT [dbo].[tb_job_status_checker] ([row_id], [job_id], [job_name], [job_status], [transaction_time]) VALUES (11, N'ECE28CCE-1856-464E-806F-ECBEE1DF285B', N'job_name test entry', N'Y', CAST(0x0000A0D600DB3533 AS DateTime))
SET IDENTITY_INSERT [dbo].[tb_job_status_checker] OFF
SELECT [row_id]
,[job_status]
,[job_name]
FROM [DB_UTILS].[dbo].[tb_job_status_checker]
WHERE job_status = 'N' and job_name = 'D'
After executing the simple select, two system generated statistics are created...even with a covering index (unless I messed that up.).
Is that normal? Is my test case incorrect?
Regardless, when you speak about selectively managing the indexes and statistics that are the actual problem...
How do you manage the indexes side? Are these manually created statistics or system generated?
What metrics to you measure to decide?
Sorry for the plethora of questions...just a lot of uncertainties for me.
Thanks in advance!
Lee