Data maintenance plan for MSSQL 2005

[Former Member]

Hi,

Are there any recommendations to schedule Data maintenance plan for MSSQL 2005.

I am mainly concern about step Rebuild Index.

Is there any supporting SAP note for this.

Regards

Rahul

[William_Laverde · Accepted solution]

If you rebuild indexes from maintenance plans it will take forever depending on the size of your database. I prefer rebuilding few indexes every week, like top used, top growing tables,indexes with high usuage. you can come up with what suits your needs.

This can be done using T-SQL scripts. Take a look at this script http://2pttechnology.com/web/forums/thread/18.aspx,

This is also a good source of information http://ola.hallengren.com/

Never shrink your data files on SQL Server. It has lot of negative impact on your database, and to fix it you have rebuild every index on the database which may be painful task on large DB's.

check dis out: http://blogs.msdn.com/b/temenosonsql/archive/2010/03/16/why-you-should-not-shrink-your-database.aspx

This is also a useful read

Top 10 Best Practices for SQL Server Maintenance for SAP

http://sqlcat.com/top10lists/archive/2007/09/12/top-10-best-practices-for-sql-server-maintenance-for...

Here are some SAP notes that may be of interest to you as well

62988 Service Packs for MS SQL Server

159316 Reorganizing tables under SQL Server

327494 Configuration Parameters for SQL Server 2000

542468 Activate manual statistics generation on infocubes in BW

600027 Installing the correct MS SQL Server Code Page

652634 FOR ALL ENTRIES performance with Microsoft SQL Server

666805 MS SQL Server: Migration from 32-Bit to 64-Bit

683447 SAP Tools for MS SQL Server

767691 Migration of SAP Systems to/from MS SQL Server

799058 Setting up Microsoft SQL Server 2005

879941 Configuration Parameter for SQL Server 2005

905634 SAP Release Planning for SQL Server 2005

924288 Installation of 6.x based SAP systems on MS SQL Server 2005

965145 Installation of 46C Systems on MS SQL Server 2005

965908 SQL Server Database Mirroring and SAP Applications

[Former Member]

Good Morning, you can use "sys.dm_db_index_physical_stats" in order to check the fragmentation level of your indexes.

Please have a look at BOL for proper syntax.

Microsoft rules are:

< 5% do nothing

> 5% and < 30 % reorganize

> 30% rebuild

Please also consider a "PageCountLevel"  of 1000.

Best Regards.

[Former Member]

Hi Franco,

yes, this is the general recommendation from Microsoft, but SAP uses objects in a SQL Server database in a very specific way. Which benefit do you expect to get by reorganizing objects whenever they exceed 5% of logical defragmentation... considering the I/O load that reorganizing will cause?

Regards,

Beate

[Former Member]

Hi Beate, you very well know that the answer always is....it depends .

I just was answering a question related to "Maintenance Plan", my point is that it's more accurate if Rahul will write his own t-sql scripts in order to maintain indexes and that is why I suggested to him to have a look at DMV.

With that he can have an idea of fragmentation and decide to do reorganization based on % and PageCountLevel.

With Maintenance Plan he will have no control at all and the index reorg is always done on all the indexes in the db.

Best Regards.

Franco

[Former Member]

Hi Rahul,

SAP recommends not to reorganize or rebuild any objects on a regular basis - for this reason it's not recommended to schedule any maintenance plans which do this.

Please see:

SAP Note 159316 - Reorganizing tables on SQL Server

Regards,

Beate

[Nibu]

HI Rahul,

In MS SQL , typically you need to use SQL scripts to rebuild indexes. In SAP perspective, you must use DB02 , tab is there to view and recreate missing indexes .

Regards,

Nibu Antony

[William_Laverde]

.

Edited by: William Laverde on Dec 8, 2010 2:13 PM

[Former Member]

are you looking for technet.microsoft.com/en-us/library/cc966521.aspx