Maintenance job basically comprises of two aspects – one is to deal with space management and remove fragmented space and second is to update the statistics with data change so that histogram reflects the latest data and plan selection could be optimized.
Lets understand the first type of maintenance job which starts with reorg, as per official documentation – “Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.” We need to see the practical aspect of these options and their feasibility.
reorg rebuild – This is the most effective one arranging all of the data in the order of clustered index and recreated all the non-clustered index, this could be performed on table having any locking mechanism, be it datarows or datapages or even allpages. In order to perform this operation, the free space at default segment of database should be more than the largest table in the database(at least 120%). While performing this operation, you may experience locking of the table and hence any DML operation(insert, update and delete) will be under blocking at different phases. Scheduling this on weekly basis is not very much practical in Production environment if the load is across the clock however can be scheduled based on operational hours and business model. Now a days, most of the organization perform DR(Disaster Recovery) drill and keeps their business up and running for few days from DR site, that could be the best time to schedule and plan it.
reorg compact – This is the best possible and effective maintenance job which is practical to implement on periodic(weekly) basis without locking the table and hence blocking could be avoided however consideration should be made while scheduling it. If this is scheduled in working hour, the pages undergoing either reclaim space or forwarded rows could cause blocking or even deadlock in selective case. reorg compact job should be scheduled on Friday’s night or Saturdays night based on the operational load. Please note that this puts a shared lock on table and could be performed only on those tables which are having locking mechanism as datarows or datapages. It can’t be performed on the table whose locking mechanism is set as default i.e. allpages. reorg compact comprises of reclaim space and forwarded rows operation internally.
Now, lets talk about statistics update, statistics is generally updated in three ways:
Update Statistics – First method is to run this command and it would update statistics of leading column of index(es). It does update histogram of leading column of the index(es) and also densities based on default ratio. This can be run at table level or at index level as below:
update statistics table_name — Table Level
update statistics table_name index_name — Index level
Update Index Statistics – Second method is to update statistics of all columns of index(es). It updates histogram and density of all the columns which is part of the index. This would consume more time than previous one and will be more resource intensive, should be planned only in non-working hour or when there is light traffic on the server. It could also cause locking since some operation requires shared lock. Below is the command:
update index statistics table_name — Table Level
update index statistics table_name index_name — Index level
Update All Statistics – Third method is to update statistics of all columns of tables and indexes. It updates histogram and density of all the columns in table. This will be the slowest of all and would require more resource than previous one and could cause I/O contention as well as CPU to operate data sort, could use named and default cache as well as procedure cache, could adversely affect other processes if executed at working hour. This should be planned once in a while and doesn’t require to be scheduled periodically unless its really required.
Please note that these maintenance jobs don’t get replicated to warm standby or through MSA(multi site availability) and hence if you are having a reporting server then you need to have maintenance job on that server separately along with production server.
I have covered these maintenance job in my video here
Please leave your comment if you liked this post or have any feedback.