November 13, 2011 at 11:49 am
I have a 1.3TB database. The previous guy didn't do any maintenance and now the server is about to crash. The tables needed to be partitioned the indexes needed to be dropped and created. I have done the maintenance on a copy of production but I need to somehow get the newer data from production onto my cleaned database. Production can go down for a very small window. Any ideas how to do this?
November 13, 2011 at 8:07 pm
Alan Naylor (11/13/2011)
I have a 1.3TB database. The previous guy didn't do any maintenance and now the server is about to crash. The tables needed to be partitioned the indexes needed to be dropped and created. I have done the maintenance on a copy of production but I need to somehow get the newer data from production onto my cleaned database. Production can go down for a very small window. Any ideas how to do this?
Why is the server "about to crash"?
If the only maintenance you need to do is drop/create some indexes and do some partitioning, then why can't you do that on the production database? ( and not have production down at all.)
Is your cleaned database on the same server? Different server?
What exactly are you trying to accomplish?
November 13, 2011 at 8:24 pm
What is preventing you from doing this maintenance on the actual prod server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2011 at 8:33 pm
Performance is terrible at this point. I have one table that has 1.8 billion records and several others that are pretty big but not that big. The indexes are fragmented 100%. I tried running dbcc reindex but it takes forever and the performance is degraded. I have the fixed database on another server but there isn't any issue with putting it on the same server. I just didn't want to use any production resources on the work we had to do.
November 13, 2011 at 8:37 pm
The primary keys are fragmented too and can't drop them without dropping constraints and that's not a good idea to do on live production.
November 13, 2011 at 8:46 pm
Alan Naylor (11/13/2011)
The primary keys are fragmented too and can't drop them without dropping constraints and that's not a good idea to do on live production.
You don't need to drop them to defrag though. You defrag the clustered index (or NC index) that is on the primary key.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2011 at 8:54 pm
Tried to reindex them but they were still over 50%.
November 13, 2011 at 8:59 pm
So on this other system which did you do?
dbcc dbreindex (deprecated way to rebuild indexes)
alter index rebuild
alter index reorganize
and which did you attempt on the prod?
Just curious.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 13, 2011 at 9:48 pm
On the new system we dropped and recreated all the indexes. On the production I tried a script that checks fragmentation levels and decides based on that whether or not to reindex or reorganize.
November 13, 2011 at 10:27 pm
Instead of drop and recreate, just rebuild the index. It's cheaper.
I would check out the index usage stats, and compare usage against fragmentation, you might be able to identify some low hanging fruit by disabling indexes that are only inserted or updated, making the related clus rebuilds cheaper.
November 13, 2011 at 11:32 pm
The tables needed to be partitioned the indexes needed to be dropped and created.
It’s not required if you are not changing their definition (big change).
Try rebuild or reorganize.
ALTER INDEX { index_name | ALL }
ON <object>
{ REBUILD
[ [PARTITION = ALL]
[ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_index_option>
[ ,...n ] )
]
]
]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ( <set_index_option> [ ,...n ] )
}
[ ; ]
Production can go down for a very small window.
It’s not required. You may rebuild indexes online.
<rebuild_index_option > ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
November 13, 2011 at 11:42 pm
The indexes are fragmented 100%.
Are you sure it's fragmentation not the fill factor.
FILLFACTOR specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. fillfactor must be an integer value from 1 to 100. The default is 0.
Note: Fill factor values 0 and 100 are the same in all respects.
Can you please post the results of following?
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
sys.dm_db_index_physical_stats (Transact-SQL)
November 14, 2011 at 10:28 am
Tried rebuilding. Fragmentation didn't change very much. The drop and recreate gave us 0% fragmentation.
November 14, 2011 at 10:31 am
Do you want me to run the stats on the production box on the one that is already fixed?
November 14, 2011 at 10:55 am
Read that whole thread. Especially the end.
http://www.sqlservercentral.com/Forums/Topic1154939-146-1.aspx
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply