Yesterday I talked about installing the Best Practices Analyzer 2005 (BPA) for a client that I was working with. After some initial installation issues, I was ready to check out their SQL Server environment.
While the tool was running, I started checking out the SQL Server event logs to see if there was anything interesting in the logs. Of course there was, otherwise this would be a very short and uninteresting blog. Counting successful backup messages in the log is how I roll.
In the SQL Server event log there were a few interesting messages. One in particular looked a lot like the following:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: XX seconds. Working set (K:B) XX, committed (K:B) XX, memory utilization: XX%.
Ah man, this doesn’t look good. The message is talking about paging and performance degradation. That always sound bad. Of course, I started digging into the server to figure out what was causing these performance issues. I found some promising information on this issue here and here.
While I was checking this information out, the Best Practice Analyzer finished its work. I decided to wrap up the BPA work before getting too deep into this issue. As I was exporting the report results, the first item in the list read the following:
There are known issues that exist in Windows and various Device Drivers which can trigger conditions that result in working set of all processes currently running on the machine to be trimmed. This will cause excessive paging and result in drastic reduction in performance of all applications on the machine (specifically SQL Server).We found that TCPIP.SYS present with version [5.2.3790.4318] and BXVBDA.SYS is also present. So we recommend you to disable TCP Chimney.
This issue tracks back to the issue that I found in the SQL Server error log at the beginning of this post. As a result of using the BPA to check out the client’s SQL Server instance a potentially difficult issue to resolve may be resolved with next to no effort.
The client is currently implementing the recommendation and I should know shortly whether this resolved the issue. I am fairly confident that this will resolve the issue for them but will follow up on this post when I know for certain.
The lesson here is that BPA identified and offered a resolution for an issue that could have taken a lot of effort to resolve. The question now is have you run BPA on your SQL Server environment to validate that your environment is up to snuff?