Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Maint. Question...Alter Index THEN UPDATE STATISTICS FULLSCAN, COLUMNS? Expand / Collapse
Author
Message
Posted Wednesday, September 19, 2012 1:21 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 416, Visits: 1,056
I have probably a silly question but I wanted to throw it out there anyways...

In developing a methodology of reindexing / index defragging...I have come up with a couple of questions.


For example

If I have a table and the table has a Clustered Key and 1 or more NC'd keys...

The Clustered key needs to be rebuilt
One NC key needs to be rebuilt
and the rest of the NC key's need to be defragged


Now the way I understand it and I am pretty sure i am right (SQL 2005 and above) if I run an ALTER INDEX WITH REBUILD that will not only rebuild the index but will update the statistics on the associated columns with a FULL SCAN.

Any columns that are NOT part of an index will not have their statistics updated during this process.

If I wanted to updated the non indexed columns statistics I would need to run a manual UPDATE STATISTICS (with either a SAMPLE or a FULL SCAN), and then define if it would be ALL, COLUMN, INDEX


QUESTION

Is it assumable, that I could run through a database and do the following in the specific order listed

1. Alter Index REBUILD all indexes that meet requirements for rebuilding
2. Alter Index REORG all indexes that don't meet the requirements for rebuild but for reorg

Once done with the index maint. the REBUILT index table columns should have stats updated with 100% sample rate

In Order to get the non-indexed columns as well as the indexed columns that were only reorg'ed to have updated column stats could I run the following to cover all the bases regarding statistics and index maintenance?



UPDATED STATISTICS </insert table name/> WITH FULLSCAN, COLUMNS

My thought is that the indexed columns are already updated...and this should go through the non-indexed columns and update their stats with 100% same size...


I have been looking and cannot find an answer online that supports my thought so I guess I am trying here next to figure out what the most complete method is to database maintenance regarding index maintenance and maintaining statistics.

Any insight is appreciated.


Thanks in advance,

Lee
Post #1361590
Posted Thursday, September 20, 2012 7:31 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 416, Visits: 1,056
Looking for ANY comment...any at all :)
Post #1362393
Posted Friday, September 21, 2012 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 6:58 AM
Points: 11, Visits: 51
Yes, if you rebuild an index, then the stats for the associated columns do get automatically updated in a manner equivalent to fullscan. (Although if your only goal is to update stats themselves, then doing an update stats with fullscan usually incurs much more overhead than necessary; and unless you have some "unique" situation with some of your queries, the sp_updatestats command is sufficient in most cases to keep your stats current.)

However, to your other point, SQL Server maintains NO statistics for columns that are not part of any index, unless you create your own custom statistics with the "CREATE STATISTICS" command. Check Books Online, etc. for details.
Post #1362666
Posted Friday, September 21, 2012 3:00 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 416, Visits: 1,056
2ndHelping (9/21/2012)
Yes, if you rebuild an index, then the stats for the associated columns do get automatically updated in a manner equivalent to fullscan. (Although if your only goal is to update stats themselves, then doing an update stats with fullscan usually incurs much more overhead than necessary; and unless you have some "unique" situation with some of your queries, the sp_updatestats command is sufficient in most cases to keep your stats current.)

However, to your other point, SQL Server maintains NO statistics for columns that are not part of any index, unless you create your own custom statistics with the "CREATE STATISTICS" command. Check Books Online, etc. for details.



2ndHelping (9/21/2012)
Yes, if you rebuild an index, then the stats for the associated columns do get automatically updated in a manner equivalent to fullscan. (Although if your only goal is to update stats themselves, then doing an update stats with fullscan usually incurs much more overhead than necessary; and unless you have some "unique" situation with some of your queries, the sp_updatestats command is sufficient in most cases to keep your stats current.)

However, to your other point, SQL Server maintains NO statistics for columns that are not part of any index, unless you create your own custom statistics with the "CREATE STATISTICS" command. Check Books Online, etc. for details.


Thank you for the clarification...I guess I was looking at the process as how do I get the best bang for the buck from a limited maintenance window.

For a given table...

I determine which indexes need to be rebuilt then the stats are updated as well with 100% sample size...for the given columns of the said indexes.

To your point all it looks like sp_updatestats does is actually execute UPDATE STATISTICS ALL under the covers with either the default sample size or a defined sample size.

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?


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)

OR

Figure out what the names are of the system generated statistics and then build a process to roll through them and update each one individually with a FULLSCAN?

update statistics [dbo].[table_A](_WA_Sys_00000001_3F954511) with fullscan



I suppose the answer is...it DEPENDS lol...


Any input is appreciated!
Post #1363006
Posted Friday, September 21, 2012 3:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
2ndHelping (9/21/2012)
However, to your other point, SQL Server maintains NO statistics for columns that are not part of any index, unless you create your own custom statistics with the "CREATE STATISTICS" command. Check Books Online, etc. for details.


That's not completely true. If auto create statistics is on, SQL can automatically create statistics on columns that it needs distribution info on. Those stats won't be updated when indexes are rebuild.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363010
Posted Friday, September 21, 2012 3:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
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.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363011
Posted Monday, September 24, 2012 1:08 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 416, Visits: 1,056
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
Post #1363692
Posted Monday, September 24, 2012 2:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
Ignoring the stats question for a moment...

Your indexes are redundant and unnecessary. You've got a clustered index on a column, then two nonclustered indexes on the same column. The nonclustered indexes aren't necessary at all there.

As for the stats...

The query you posted will likely result in stats being created on the job_status and job_name columns, because there is no index on either column, Ok, without an index there's no way other than a scan to process that query, but still...

Stats is a tough question because there's no easy way to manage them selectively and efficiently, hence many people will just update all stats when they do maintenance. If the maint window is large enough, that works. It's when the DB is too large and the maint window too small that it becomes difficult and, unlike indexes, there's no good guidelines as to when the stats should be updated because it's too dependant on data distribution and queries. Hence if you have time to just update them all, just update them all.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363713
Posted Monday, September 24, 2012 3:18 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 416, Visits: 1,056
GilaMonster (9/24/2012)
Ignoring the stats question for a moment...

Your indexes are redundant and unnecessary. You've got a clustered index on a column, then two nonclustered indexes on the same column. The nonclustered indexes aren't necessary at all there.

As for the stats...

The query you posted will likely result in stats being created on the job_status and job_name columns, because there is no index on either column, Ok, without an index there's no way other than a scan to process that query, but still...

Stats is a tough question because there's no easy way to manage them selectively and efficiently, hence many people will just update all stats when they do maintenance. If the maint window is large enough, that works. It's when the DB is too large and the maint window too small that it becomes difficult and, unlike indexes, there's no good guidelines as to when the stats should be updated because it's too dependant on data distribution and queries. Hence if you have time to just update them all, just update them all.


Hi Gail,

Sorry if that was a little confusing, I guess I was doing trial and error to see what would work as far as NOT creating the system generated stats...

I had thought that if the query (which has the two filters in the WHERE) had a covering NC index that it would bypass the creation of those system generated stats...

It didn't work and the stats were still created...

What I am getting out of your comment is that the only way to avoid having those system generated stats is to have a NC index for each column in the predicate...is that correct?


Thanks for the comments on stats management, I guess I was looking for something like that which detailed that there is no cut and dry way of managing them.

In your experience with updating stats...how much of an impact is it in comparison to running something like a index rebuild?

I would presume that if you are running stats update...and a process hits the table / column that is being updated it will be blocked until it is stats update is completed.

Thanks again and I will play around with that simple process above to get a better understanding of what works and what is redundant.

Lee
Post #1363736
Posted Monday, September 24, 2012 3:32 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
Leeland (9/24/2012)
I had thought that if the query (which has the two filters in the WHERE) had a covering NC index that it would bypass the creation of those system generated stats...


Except that neither of the indexes you created is at all useful for that query. Technically covering, not none were seekable.

What I am getting out of your comment is that the only way to avoid having those system generated stats is to have a NC index for each column in the predicate...is that correct?


Yes, but that's not a very good indexing strategy. Why are you trying to avoid those stats? There's little downside in having them

In your experience with updating stats...how much of an impact is it in comparison to running something like a index rebuild?


Impact, not all that much usually. Depends on the settings though (sampled vs full scan)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1363738
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse