MIIS 2003 Capacity Planning Test Summary - SQL Server
Applies To: Windows Server 2003 with SP1
Previous Sections in This Guide
This section presents different tests that evaluated how the location of the SQL database used by MIIS 2003 affected performance.
MIIS 2003 uses a Microsoft SQL Server 2000 database for its central data store. MIIS 2003 can access the database whether it exists on the same server that is hosting MIIS 2003 or a remote SQL server. The goals of this section are to present a set of recommendations for selecting the location of the SQL server that hosts the MIIS 2003 data store and the resulting test data to substantiate those recommendations.
Test Description
The SQL Server tests consisted of staging and synchronizing 100,000 new user objects from two file-based management agents into an MIIS 2003 database hosted on a SQL server. Some tests were performed on servers that had both MIIS 2003 and the SQL Server installed on the same server. Other tests were run on servers where MIIS 2003 and the SQL server were located on separate servers, requiring data to be sent across the network.
The goal of these tests was to determine whether database-intensive operations performed better on servers that host both SQL Server and MIIS 2003 as compared to configurations where SQL Server and MIIS 2003 were installed on separate servers.
The tests were intended to determine whether any performance gain was realized when the SQL database was hosted on a dedicated SQL Server, and if that performance gain offset the latency introduced by the requirement to send the data over the network to a dedicated SQL server. The staging and synchronization processes were used for the tests because they are both database-intensive activities.
To avoid having other factors interfere with database performance during these tests, other elements in the environment were kept as constant as possible. Specifically, the following conditions were maintained throughout the testing cycles:
The same hardware platforms were used for all tests
Storage - All platforms used the same storage array so disk performance would be uniform for all tests.
Network - The tests were performed on an isolated network so no external traffic could interfere.
Data - The same data set was used for each test to maintain a consistent number of objects and to maintain a consistent ratio of objects-to-attributes.
Data Sources - The data sources for the tests were two text files located on the MIIS 2003 servers. This avoided introducing any network-related latency to the staging process and avoided creating network traffic that would conflict with the traffic between the MIIS 2003 server and the remote SQL server.
Expected Results
The configuration of both MIIS 2003 and SQL server on the same server will demonstrate better performance than when they are hosted on different servers. Disk I/O, especially if RAID is implemented, will have less impact on MIIS 2003 performance than latency caused by sending data over the network to a remote SQL server.
Test Result Summary
The results confirm that the configurations that hosted both SQL server and MIIS 2003 on the same server showed much better performance than environments where they were installed on separate servers.
Result highlights:
Processor utilization improves if both the SQL Server and MIIS 2003 are hosted on the same server.
A single server outperforms the separate servers during both staging and synchronization operations, in some tests completing the same amount of work in 40% less time.
Recommended Best Practices
Install the SQL Server that hosts the MIIS 2003 database on the same server that is hosting MIIS 2003. Ensure that no operational factors or policies within your organization prohibit MIIS 2003 and SQL Server 2000 being hosted on the same server and that the solution objectives do not require a high availability configuration using SQL clustering.
Note
When selecting the hardware platform to host MIIS 2003 and SQL Server 2000, follow the recommendations presented in the other sections of this document.
Test Scenario
During this test, a variety of platform configurations completed a series of MIIS 2003 staging and synchronization operations designed to test the performance of database operations. From two file-based management agents, 100,000 user objects were staged and synchronized into the metaverse hosted on a SQL server database. The tests collected performance statistics, such as operations per second and total time to complete the operations, for analysis.
The two scenarios tested were referred to as the same server scenario and the split server scenario. In the same server scenario tests, MIIS 2003 and SQL server were installed on the same server. For the split server scenario tests, MIIS 2003 and SQL server were installed on separate servers connected by 100 Mbps Ethernet. The same series of tests was performed for both scenarios to explore the impact of the different hardware configuration on database performance.
Server Hardware Configuration
Various server configurations were used to perform these tests. This was accomplished by installing both MIIS 2003 and Microsoft SQL Server 2000 on two servers and then using different combinations of the two servers and the software installed on each.
For the purposes of this discussion the server configurations will be referred to using a single letter to indicate the number of processors (D for dual-processor, Q for quad-processor) followed by two digits to indicate clock speed. For example the server designation D32 indicates it is a dual-processor server running at 3.2 GHz, and Q30 is a quad-processor server running at 3.0 GHz. Details of the two server platforms are summarized in the table below.
Table 14: Server hardware configuration
| Server Designation | Model | Description |
|---|---|---|
Q30 |
HP DL580 G2 |
Quad Intel Xeon MP @ 3 GHz Hyper-threading 4 GB System RAM (200 MHz DDR, 400MHz FSB) Internal 5i SCSI controller 4x 34.6 GB Ultra 320 HDD (10k) Windows Server 2003, Enterprise Edition |
D32 |
HP DL380 G3 |
Dual Intel Xeon MP @ 3.2 GHz Hyper-threading 4 GB System RAM (266 MHz DDR, 533 MHz FSB) Internal 5i SCSI Controller 4x 34.6 GB Ultra 320 HDD (10k) Windows Server 2003, Enterprise Edition |
MIIS Configuration
MIIS 2003 with Service Pack 1 was used on the servers being tested. For the purpose of this discussion, the test platforms have been named using a multiletter designation where the first letter, Q or D, indicates the quad-processor server or the dual-processor server respectively, followed by a single letter indicating the software installed on that server (M for MIIS 2003 and S for SQL Server). For example, QMS designates the test platform using the quad-processor server and both MIIS 2003 and SQL Server are installed on that server. DMQS designates the test platform where the dual-processor server has MIIS 2003 installed and SQL Server is installed on the quad processor server. The different test platforms are summarized in the table below.
Table 15: Server software configuration
| Test Platform Designation | Server(s) | Description |
|---|---|---|
QMS |
Q30 |
Same server scenario: MIIS 2003 SP1 and Microsoft SQL Server 2000 SP3a installed on the quad-processor server. |
DMS |
D32 |
Same server scenario: MIIS 2003 SP1 and Microsoft SQL Server 2000 SP3a installed on the dual-processor server. |
QMDS |
Q30 D32 |
Split server scenario: MIIS 2003 SP1 installed on the quad-processor server and Microsoft SQL Server 2000 SP3a installed on the dual-processor server. |
DMQS |
D32 Q30 |
Split server Scenario: MIIS 2003 SP1 installed on the dual-processor server and Microsoft SQL Server 2000 SP3a installed on the quad-processor server. |
The following sections describe additional details of the installation.
Database Configuration
On both Q30 and D32, Microsoft SQL Server 2000 was installed with the SQL data and SQL log files residing on the D: drive created on a RAID 1 array of two 34.6 GB Ultra 320 hard disks.
Management Agent Configuration
Two management agents were installed on MIIS 2003. The following table summarizes the configuration of each one.
Table 16: Management agent configuration
| Management Agent Name | Type | Notes |
|---|---|---|
TXT01 |
Text File |
|
TXT02 |
Text File |
|
Run Profile Configuration
Four run profiles were created for the staging and synchronization operations: a full import for staging and a full synchronization for each management agent. They were configured as full run profiles rather than delta because MIIS 2003 was reset after each test in order to begin each test with an empty connector space and metaverse. Because no objects existed prior to each test, full staging and synchronization operations were required rather than deltas.
During the testing process, all run profiles were executed sequentially. No concurrent operations were tested.
Test Results
The staging and synchronization tests were performed for both same server scenarios and both split server scenarios. The results were recorded and analyzed. The following two charts are a summary of the results.
.gif "Chart: Time to complete")
.gif "Chart: Operations per second")
Observations
Same Server Scenarios
Platform: DMS
This configuration outperformed all the other configurations. This result is consistent with the results of the processor tests presented in "MIIS 2003 Capacity Planning Test Summary - Processor". This is a result of the slightly faster processors, memory, and FSB installed in this platform.
Platform: QMS
This configuration still performed much faster than any of the split scenario tests. There is almost a 40% decrease in the time required to complete the operations when compared to the fastest split scenario configuration.
Split Server Scenarios
Platform: DMQS
The test results show that there is no performance benefit to having the MIIS database hosted on a large SQL server. The latency introduced by the network far outweighs any benefit gained from the use of a dedicated SQL server. In fact, it may result in underutilization of the platform hosting the SQL server.
Platform: QMDS
There is a slight performance increase in this configuration, but it still demonstrates lower levels of performance than those achieved in the same server scenarios.
External Factors and Other Considerations
Based on the administrative and operating policies in your environment, you may not have a choice regarding the location of the SQL server. If the server must be located remotely, make sure that you use the fastest network transport at your disposal. In the scenarios tested for this document, the servers were connected by using isolated 100 Mbps LAN connections. This recommendation is more of a precaution to prevent unexpected problems in the event of an unusually high amount of network traffic. During the testing process, even tests of 100,000 users did not generate significant amounts of traffic on the network segment used to connect the two servers.
Next
See Also
Concepts
Introduction to MIIS 2003 Capacity Planning
MIIS 2003 Capacity Planning Test Summary - Processor
MIIS 2003 Capacity Planning Test Summary - Disk Performance
MIIS 2003 Capacity Planning Test Summary - Memory
MIIS 2003 Capacity Planning Test Summary - Database Size
MIIS 2003 Capacity Planning Test Summary - Network
MIIS 2003 Capacity Planning - Additional Performance Considerations