Some enterprises have moved their SQL Server off of their Deployment Solution Server or their Notification Server to increase the performance of their Altiris Infrastructure. This solution doesn't always bring the desired results. This could be due to a variety of factors from a bottleneck in server hardware to bad SQL server configuration choices or even network issues. We'll examine how the network performance can be monitored to see if this could be preventing the desired results of having SQL server off-box.
Since all of the network traffic between the Altiris server and the SQL Server is in the form of SQL queries, the best way to measure network performance is using the same benchmark. A standard ping gives you some indication but other factors could be contributing to the slowdown that would not be apparent in a regular ping.
Actually running a SQL query might work for this, however depending upon the query it could be hard to measure the performance in any meaningful terms or it could put an excessive load on your SQL server just making matters worse.
This is likely why Altiris has provided the SQL Ping tool to measure the response time of an actual SQL query from a Altiris server to a off-box SQL server. This tool works with Notification Servers, Deployment Server and Recovery Servers. Technically you could even use this tool for non-Altiris servers as long as you have dot net 1.1 installed.
No CPU Performance Impact
This tool measures the round-trip time response time so it is easier to appraise the network communication between the Altiris server and the off-box SQL server. At First glance it might seem to solve the issue of measuring performance but still leave the issue of increased load on the SQL server. However this SQL Ping creates a small temp table on the off-box SQL server and then requests the contents of that table every so many seconds, depending on what you have configured. Since the reoccurring operation is simply a read operation it should put a zero CPU load on the SQL Server.
To prove that no additional CPU load is put on the SQL Server we can run a SQL Server Profiler trace and look at the CPU usage column when the SQL query is performed.
As you can see in the attached image SELECT * FROM #SqlPing is performed and the CPU usage is measured at 0. However you will see that there is some read performance associated with it, but it is minimal.
While not having a CPU performance impact might not seem that big of a deal if you are using it for a short period of time, it is useful when running for longer periods of time and using it with performance monitor.
Downloading and Setup
To begin using this tool, download it from the Altiris Knowledgebase, article 28978. Next we need to extract the EXE and copy it to your Altiris server. Just run the executable and it will automatically fill in your Notification Server database settings from the registry. If you are using another server type you may have to enter the SQL server, Database and authentication type manually. If you are using SQL authentication as your SQL authentication type you will need to enter the SQL password. The default polling interval is 3 seconds and you can adjust this as needed. A faster polling interval will probably not be helpful, while a longer polling interval is useful when gathering data over an extended period of time.
In the top right corner you will see the ping button, just click it and the tool will create the 64 kilobyte temp table and start requesting data every so many seconds depending on the specified polling interval. For quick checks you can run it for a few minutes and check the average time. You can also execute other tasks and see how your performance is affected by those tasks. Under results you also have the ability for basic filtering of current results, full results and failure results. The full results are cleared once you begin a new ping session.
The best results would show response time between 0 and 15 milliseconds with no network failures. This tool will show a good response time for anything less than 100 milliseconds, a warning for anything between 100 milliseconds and 400 milliseconds, and a bad for anything great than 400 milliseconds. Failures are counted when a network communicated problem is encountered. This is most easy replicated with an unplugged cable or a disconnected NIC in the OS. When you have a congested network it will cause delays in sending packets between servers, including re-sending of the same requests. High Latency requests are a known cause of poor performance, especially with a Notification server.
Logging
You may find that performance varies by time of day and you want to log performance over an extended period of time. For this you could check the enable logging box and save the file locally to be reviewed later. After you check the box you can start the ping trace as normal and the output will be saved to a tab-delimited log file that can be viewed the easiest in Microsoft Excel or also in notepad or another text viewer. When there is an error such as network failure the first 100 characters of the error message are included in the last column of the tab delimited log file. When using this method you might want to increase the polling interval to 6 seconds. You can then take the file and create pivot tables to get information such as bad times and average response times.
Windows Performance Monitor
This tool also has advanced options for usage with the Windows Performance Monitor. It has built in counters that can be used. To use this in performance monitor make sure you have the SQL ping tool running in memory and then open perfmon.msc on your Altiris server. Expand the "Performance Logs and Alerts" setting on then right-click on "Counter Logs" and choose "New Log Settings". When prompted for a name, call it "SQL Ping". Click the "Add Objects" button. Find the "SQL Ping" performance object in the scroll list and click "Add". Click OK and you should see that the counter log is now running by its green appearance. After you have collected data for hours or days you can right click on SQL ping and stop the log and then use the BLG file it created to assess performance.
If you want to view data from the BLG file you just created you can open performance monitor by running perfmon.msc on your Altiris server and the click on System Monitor. Then click the "View Data Log" Icon (4th icon from the left). Select the "Log Files" radio button under the "Source" tab and then click the "Add" button. Find the BLG file created earlier. It should begin with SQL Ping if that's what you named your counter. Then click open and you can view the history in graph format.
You can also view the data in real time using the performance monitor. In order to do this, open the perfmon.msc on your Altiris server and click on the System Monitor. Then click the first icon called "New Counter Set". At the bottom of your screen right click on the blank space under the columns and select "Add Counters". Select "SQL Ping" from the performance object drop down list and then "SQL Read operation" from the next list. Then click "Add" and the "Close". You will see a real-time graph that includes the Last, Average, Minimum and maximum response times. Once again you must have the SQL Ping running for these steps to work correctly.
Additional Tips
If you try to run the SQL Ping tool from a UNC share it will fail with an error message of "Application has generated an exception that could not be handled". Just copy the file locally to your Altiris server and you should have no problems.
When you are finished using the tool just close it out and the temp table should clean itself up on the SQL server. Other than that there isn't much cleanup to be done with this tool except deleting it and any logs you may have created in testing.
Hopefully using this tool should help you see if you have a bottleneck on your network performance between your Altiris server and your off-box SQL server. If you don't see any significant issues when using the tool, look for other issues such CPU, Memory, DISK R/W times or SQL performance. Most people find that the best off-box SQL performance is found when you are running a 64-bit SQL Server.