November 6, 2013 at 1:25 pm
Hi everyone,
We plan to implement a custom maintenance plan that will rebuild tables and indexes based on the information that we can get from the sys.dm_db_index_physical_stats table valued function.
I have two concerns about this custom maintenance plan:
1- The sys.dm_db_index_physical_stats table valued function does not analyze heap tables. How could we integrate these tables in our treatment? We have few of them but we would like to treat them all as well.
2- I am not really aware on how specifically SQL Server store the data (IAM and all other structures). I know that dropping a column or modifying a column can lead to waste allocated space in the object because dropping a column is a metadata only operation. How can we detect these tables that have "dropped columns" or tables that uses more row space than what is actually needed? We would like to rebuild them as well...
Best regards.
Carl
November 6, 2013 at 1:34 pm
I am not sure that heaps are a particularly good target for defragmentation. by their nature, they are not ordered in any way, so page splits should not be happening. If you are deleting significant amounts of data every so often, you can build a clustered index on the table, and drop it, but I doubt there wll be a noticeable benefit to that, as any insert will happily use up any space left by deleted rows.
As for the second question, do you drop columns that often? I suspect not, so a special (manual) rebuild could be called for. Alternatively, any script that drops a column could include a rebuild of the clustered index. just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for the regular off hour run of the rebuild process for any especially large table.
November 6, 2013 at 1:41 pm
Heaps do appear in sys.dm_db_index_physical_stats, but since you can't really rebuild them there's not much point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 1:41 pm
Matt Crowley (11/6/2013)
just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for the regular off hour run of the rebuild process for any especially large table.
It does not. Rebuilding the clustered index just rebuilds the clustered index, doesn't touch the nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 1:46 pm
Hi Matt,
Thank's for your quick answer.
Let's say I really need to dynamically detect these tables : "... tables that have "dropped columns" or tables that uses more row space than what is actually needed? "
How can we do that? Any reference would be helpfull.
Best regards.
Carl
November 6, 2013 at 1:55 pm
GilaMonster (11/6/2013)
Matt Crowley (11/6/2013)
just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for the regular off hour run of the rebuild process for any especially large table.It does not. Rebuilding the clustered index just rebuilds the clustered index, doesn't touch the nonclustered indexes.
Is that new behavior, or am I relying too much on my SQL 2000 days?
November 6, 2013 at 2:09 pm
Matt Crowley (11/6/2013)
GilaMonster (11/6/2013)
Matt Crowley (11/6/2013)
just keep in mind that a rebuild of the clustered index will involve rebuilds of the non-clustered indexes as well, so you may want to wait for the regular off hour run of the rebuild process for any especially large table.It does not. Rebuilding the clustered index just rebuilds the clustered index, doesn't touch the nonclustered indexes.
Is that new behavior, or am I relying too much on my SQL 2000 days?
New as in 'consistently since SQL 2005 and inconsistently in SQL 2000 and earlier'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 2:12 pm
Carl B. (11/6/2013)
Let's say I really need to dynamically detect these tables : "... tables that have "dropped columns" or tables that uses more row space than what is actually needed? "How can we do that? Any reference would be helpfull.
Run daily jobs that checks the columns in the tables against the state the previous time the job ran, that's about it, or DDL triggers or auditing maybe.
What do you mean by 'use more row space than actually needed'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 6, 2013 at 2:12 pm
Ahh. Thanks, Gail.
November 6, 2013 at 3:59 pm
1. The only way to rebuild heaps is to add a clustered index; you can then drop it. Since, however, adding or dropping a clus index requires rebuilds of all non-clus indexes, the full process is this:
(a) drop non-clus index(es)
(b) build clus index, typically with FILLFACTOR = 100
(c) drop clus index
(d) build non-clus index(es)
If you have a heap(s) with non-clus index(es), you should also determine if making one of those indexes the clus index permanently is a better option.
2. That's an extremely tricky thing to try to do. You could try to analyze the avg_fragmentation_in_percent, max_record_size_in_bytes and avg_record_size_in_bytes values from sys.dm_db_index_physical_stats, but it will still be a complex process.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 7, 2013 at 5:48 am
GilaMonster (11/6/2013)
What do you mean by 'use more row space than actually needed'?
Hi Gail,
I mean table that contains dropped column(s) and/or table that contains modified column(s) (e.g. to increase its capacity).
Thank's for your help.
Best regards.
Carl
November 7, 2013 at 5:55 am
ScottPletcher (11/6/2013)
2. That's an extremely tricky thing to try to do. You could try to analyze the avg_fragmentation_in_percent, max_record_size_in_bytes and avg_record_size_in_bytes values from sys.dm_db_index_physical_stats, but it will still be a complex process.
Good morning Scott.
We will try to implement something mainly based on the sys.system_internals_partitions and the sys.system_internals_partitions_columns views.
We do not want to rebuild every index because its a waste of time.
Best regards.
Carl
November 7, 2013 at 6:23 am
Carl B. (11/7/2013)
I mean table that contains dropped column(s) and/or table that contains modified column(s) (e.g. to increase its capacity).
DDL triggers to log schema changes or record and compare the table schema in a job
Do your tables change definitions so often for this to be the primary concern and trigger for rebuilding indexes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2013 at 6:36 am
GilaMonster (11/7/2013)
Do your tables change definitions so often for this to be the primary concern and trigger for rebuilding indexes?
Hi Gail,
Yes. The contexte is the following :
Application upgrade with a lot of database conversion scripts to execute. At the end of the database upgrade, that may contains several thousand conversion scripts, we want to rebuild the index that need to be rebuild.
Best regards.
Carl
November 7, 2013 at 6:52 am
Carl B. (11/7/2013)
that may contains several thousand conversion scripts.
is it ? :pinch:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply