9.2 Monitoring and Tuning

Microsoft SQL Server has a Maintenance Plan Wizard in the SQL Server Management Studio. This tool should be readily available to customers who utilize Microsoft SQL Server in their data centers.

Maintenance tasks that can be scheduled include the following:

  • Check database integrity

  • Shrink database

  • Reorganize index

  • Rebuild index

  • Update statistics

  • Backup database

A good overview of the Maintenance Plan Wizard is found on the Microsoft website: Sample Maintenance Plan.

These tasks should be thoroughly understood before performing them on a live system that is hosting the ZENworks Configuration Management database.

Microsoft recommends the following best practices for managing Microsoft SQL Server 2008 R2:

  • Backups should be performed daily.

  • A Database Integrity Check should be performed every 14 days.

  • Reorganizing or rebuilding indexes should be done when fragmentation is excessive. If fragmentation is greater than 30 percent, an index should be rebuilt. To determine the fragmentation of the indexes in your database, use the dynamic memory view sys.dm_db_index_physical_stats. Micro Focus recommends that rebuilding indexes should be done at least once a week, because the clustered indexes will be fragmented over 75 percent within a few days of the insert or update activity. This is a contributing factor to lag, escalating locks, and eventual deadlocks.

The Microsoft SQL Server Tuning Wizard makes suggestions about indexes that you might want to add to the database. However, it uses the term missing indexes, which is misleading to anyone who might interpret this as a mandate. Each of these suggestions must be analyzed, balancing the performance trade-offs between inserting or updating data in a given table versus the variety of queries that might be made against a table. Indexes slow inserts and updates, while benefiting specific queries. There are a number of ways this analysis can be performed. The tuning wizard is just a first step. You should use SQL tracing tools and analyze the SQL demands that ZENworks Configuration Management is making on the database while it performs various functions such as registration, bundle creation and deployment, policy enforcement, inventory, and so forth. After you have accurate information on what kind of load ZENworks Configuration Management is placing on the database, you can then add your indexes.

9.2.1 Managing Large Transaction Logs

Large and growing transactions are an indication that a backup plan could be optimized. For example, it is not desirable to have transactions logs larger than the database. Large transaction logs are typically caused by three factors:

  1. Old transactions not being disregarded as part of the backup. When configuring the backup, it is important that old transactions from the backup are not included, because they have already been committed into the database.

  2. Infrequent backups.

  3. Initial transaction log size set too low and the auto-grow size percentage is not set large enough.

For more information, see the following links and excerpts:

In rare cases, you might need to shrink the transaction log, because of a performance issue or otherwise. Run the following commands:

--Shrink the transaction log a lot!!USE <database_name>;GODBCC SHRINKFILE(<database_name>_log, 1)BACKUP LOG <database_name> WITH TRUNCATE_ONLYDBCC SHRINKFILE(<database_name>_log, 1)GO

Where <database_name> is the name of the database on which you want to shrink the transaction log.

For more information, see:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

9.2.2 Index Fragmentation

Data modification operations (INSERT, UPDATE, or DELETE statements) will increase index fragmentation. Fragmented index data can cause the SQL Server to perform unnecessary data reads and switching across different pages. This can cause severe query performance issues against a heavily fragmented table.

sys.dm_db_index_physical_stats view can be used to identify the fragmentation levels of each index.

For more information, see http://technet.microsoft.com/en-us/library/ms188917.aspx.

To reduce the fragmentation, rebuild the indexes or reorganize the indexes based on the fragmentation percentage. Micro Focus recommends that you defragment indexes every two weeks. You can do this by creating scripts or by using third-party utilities to easily defragment the entire database. You can use the following scripts to rebuild indexes and detect fragmentation in the MS SQL server:

DBCC SHOWCONTIG DBCC DBREINDEX

If a table or index is more than 50% fragmented, you should re-index it to increase performance.

For more information, see:

In Advanced concepts, a custom script is provided to rebuild the indexes. This script should be included in the weekly maintenance plan.

9.2.3 ERRORLOG Location

Microsoft SQL Server stores all the server-related errors or information messages in the ERRORLOG file.

The ERRORLOG file location details can be gathered using the following SQL:

SELECT SERVERPROPERTY('ErrorLogFileName')

To view the SQL Server error log:

  1. In Object Explorer, expand a server, expand Management, then expand SQL Server Logs.

  2. Right-click a log and click View SQL Server Log.

    (OR)

    EXEC sp_readerrorlog 0

    GO

For more information, see http://msdn.microsoft.com/en-us/library/ms187885.aspx.

9.2.4 Backing Up Microsoft SQL Databases

Micro Focus recommends that you use Microsoft SQL Server Management Studio to manage backups. Alternatively, you can create automatic scripts to do regular backups. MS SQL allows the following types of backups:

  • Full

  • Differential

  • Transaction Log

Micro Focus recommends the following:

  • Test backups by restoring periodically.

  • Store copies of backups in a safe, off-site location in order to protect them from potentially catastrophic data loss.

  • A network drive can be used as a direct backup destination.

  • Transaction Log backup is used to do a Point in time restore, so if someone accidentally deletes all the data in a database, you can recover the database to the point in time just before the delete occurred.

  • Take a backup of particular tables before applying a patch.

Creating a Backup

The following screen shots show the process of starting the backup process of a Microsoft SQL database using Microsoft SQL Server Management Studio.

For more information on backup best practices for Microsoft SQL Server, see http://msdn.microsoft.com/en-us/library/ms175477.aspx.

During the backup configuration, check the ownership of tables and procedures to ensure that ZENworks tables are owned by dbo and not DB Owner. Ownership can change if you authenticate as a user other than the ZENworks user, and then make changes.

9.2.5 SQL Server Profiler

Microsoft SQL Server Management Studio provides the SQL Server Profiler application as shown below. This program will allow the administrator to see what SQL statements are being made in real-time and can be a useful tool in debugging database issues.

NOTE:Micro Focus recommends that you consult with Micro Focus Support before using the Database Engine Tuning Advisor for additional indexing of the ZENworks database.

In some instances, the Profiler GUI tool can reduce the database server performance. For such cases, a customized T-SQL script can be executed to run a trace in the background.In Advanced Concepts, a custom script is provided to run the SQL Profiler in the background.