Environment
Novell ZENworks 10 Configuration Management
Microsoft SQL 2005
Microsoft SQL 2005
Situation
The database is a very important component of ZENworks Configuration Management. The following is a set of guidelines if the database of choice is Microsoft SQL server 2005.
Resolution
Memory Configuration:
It is recommended to allocate as much memory as is possible to the SQL Server to allow plenty of caching. If the databases and log files should happen to page, it's better to page within memory "soft paging" than to page out to disk "hard paging". Ensure the page file is at least Physical RAM x 1.5 for the Operating System.
The Windows Server 2003 Standard Edition does not support more than 4 GB of RAM. You need Windows Server 2003 Enterprise Edition to use the extra memory (4 GB to 64 GB).
To use more than 4GB RAM with SQL Server on a 32Bit operating system, additional settings need to be configured :
a. /PAE switch in the boot.ini file - for the OS (seehttp://support.microsoft.com/kb/283037)
b. AWE Enabled - for the SQL server (see:http://msdn2.microsoft.com/en-us/library/ms190731.aspx)
c. Lock Pages in Memory - Group policy setting for the SQL Server user account (needs to be set prior to AWE) ( see:http://msdn2.microsoft.com/en-us/library/ms190730.aspx)
Disk Configuration:
Large database servers place heavy demands on the disk subsystem, and parallel disk access is key to improving performance. These should be on separate disks - and not simply separate partitions of the same disk.
The recommendation would be to have at minimum three (3) separate spindles:
a. Spindle 1: Operating system, executables and swap file
b. Spindle 2: Database
c. Spindle 3: Transaction log
For heavier loads, consider placing the temp DB on separate drives.
d. Spindle 4: Temp DB
e. Spindle 5: Temp DB trans log
RAID configurations must also be configured for backup purposes.
When creating databases and log files, allocate plenty of disk space at the start, so the databases are not spending too much time writing data and growing the allocation size.
Disk Performance Monitoring:
Use Windows Performance Monitor to make sure the I/O system is appropriately configured. These 'physical disk' guidelines are from: "Troubleshooting Performance Problems in SQL Server 2005" (seehttp://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx)
Set up these perfmon counters:
1) PhysicalDisk > Avg. Disk Queue Length > for all drives
A disk queue length which frequently exceeds a value of 2 during peak usage of the SQL Server, indicates an I/O bottleneck.
2) PhysicalDisk > Avg. Disk sec/read > for all drives
3) PhysicalDisk > Avg. Disk sec/write > for all drives
Avg. Disk Sec/Read (or write) is the average time, in seconds, of a read (or write) of data from the disk. Any number:
Less than 10 ms - very good
Between 10 - 20 ms - okay
Between 20 - 50 ms - slow, needs attention
Greater than 50 ms - Serious I/O bottleneck
4) Physical Disk: %Disk Time
A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
5) Physical Disk: %Idle Time
%Disk Time can exaggerate disk usage because of the details of how it is calculated. 100 - % idle time is a valid measure of disk usage.
Database Settings:
It is recommended to allocate as much memory as is possible to the SQL Server to allow plenty of caching. If the databases and log files should happen to page, it's better to page within memory "soft paging" than to page out to disk "hard paging". Ensure the page file is at least Physical RAM x 1.5 for the Operating System.
The Windows Server 2003 Standard Edition does not support more than 4 GB of RAM. You need Windows Server 2003 Enterprise Edition to use the extra memory (4 GB to 64 GB).
To use more than 4GB RAM with SQL Server on a 32Bit operating system, additional settings need to be configured :
a. /PAE switch in the boot.ini file - for the OS (seehttp://support.microsoft.com/kb/283037)
b. AWE Enabled - for the SQL server (see:http://msdn2.microsoft.com/en-us/library/ms190731.aspx)
c. Lock Pages in Memory - Group policy setting for the SQL Server user account (needs to be set prior to AWE) ( see:http://msdn2.microsoft.com/en-us/library/ms190730.aspx)
Disk Configuration:
Large database servers place heavy demands on the disk subsystem, and parallel disk access is key to improving performance. These should be on separate disks - and not simply separate partitions of the same disk.
The recommendation would be to have at minimum three (3) separate spindles:
a. Spindle 1: Operating system, executables and swap file
b. Spindle 2: Database
c. Spindle 3: Transaction log
For heavier loads, consider placing the temp DB on separate drives.
d. Spindle 4: Temp DB
e. Spindle 5: Temp DB trans log
RAID configurations must also be configured for backup purposes.
When creating databases and log files, allocate plenty of disk space at the start, so the databases are not spending too much time writing data and growing the allocation size.
Disk Performance Monitoring:
Use Windows Performance Monitor to make sure the I/O system is appropriately configured. These 'physical disk' guidelines are from: "Troubleshooting Performance Problems in SQL Server 2005" (seehttp://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx)
Set up these perfmon counters:
1) PhysicalDisk > Avg. Disk Queue Length > for all drives
A disk queue length which frequently exceeds a value of 2 during peak usage of the SQL Server, indicates an I/O bottleneck.
2) PhysicalDisk > Avg. Disk sec/read > for all drives
3) PhysicalDisk > Avg. Disk sec/write > for all drives
Avg. Disk Sec/Read (or write) is the average time, in seconds, of a read (or write) of data from the disk. Any number:
Less than 10 ms - very good
Between 10 - 20 ms - okay
Between 20 - 50 ms - slow, needs attention
Greater than 50 ms - Serious I/O bottleneck
4) Physical Disk: %Disk Time
A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
5) Physical Disk: %Idle Time
%Disk Time can exaggerate disk usage because of the details of how it is calculated. 100 - % idle time is a valid measure of disk usage.
Database Settings:
ALTER DATABASE <databasename> SET READ_COMMITTED_SNAPSHOT ON;
This option allows read access to information in the database while data is being written or modified. NOTE: This should be run when all ZENworks services are stopped on all primary servers. This command can take a long time to complete on large databases.
Additional Information
What might cause the database to grow rapidly?
- Error messages that aren't cleaned up regularly (see ZCC Configuration Message Cleanup).
- Enabling Patch Management which creates a lot of additional bundles, content.
- Using a lot of bundle requirements as the requirements state is stored in the database.
- Fragmentation of the database.