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?

I was hoping to have a case of the Mondays the other day.  Whenever one hopes for “the Mondays”, Monday will always deliver.  I was using a new PC at a client and installing some tools for an assessment that I was working on.

After some download and installing, Best Practices Analyzer 2005 was installed and ready to go.  I got the tool fired up and pointed it at the server I needed to look at and pressed go.

And then it stopped…  I had an unhandled exception error.

Not a big deal.  I can handle exception error because I can read.  After opening up the details window, the following was presented to me:,

See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************System.NullReferenceException: Object reference not set to an instance of an object.   at Microsoft.WindowsServerSystem.BestPracticesAnalyzer.UserInterface.BPALink.LinkClicked(Object sender, EventArgs e)   at System.Windows.Forms.Control.OnClick(EventArgs e)   at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)   at System.Windows.Forms.Control.WndProc(Message& m)   at System.Windows.Forms.Label.WndProc(Message& m)   at System.Windows.Forms.LinkLabel.WndProc(Message& msg)   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

************** Loaded Assemblies **************mscorlib    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll----------------------------------------SqlBPA2005    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA2005.exe----------------------------------------System.Windows.Forms    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll----------------------------------------System    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll----------------------------------------System.Drawing    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll----------------------------------------BPA.UserInterface    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.UserInterface.DLL----------------------------------------BPA.Common    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/BPA.Common.DLL----------------------------------------SqlBPA.Shared    Assembly Version: 9.0.242.0    Win32 Version: 9.00.1009.00    CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server%202005%20Best%20Practices%20Analyzer/SqlBPA.Shared.DLL----------------------------------------System.Xml    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll----------------------------------------System.Configuration    Assembly Version: 2.0.0.0    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll----------------------------------------************** JIT Debugging **************To enable just-in-time (JIT) debugging, the .config file for thisapplication or computer (machine.config) must have thejitDebugging value set in the system.windows.forms section.The application must also be compiled with debuggingenabled.

For example:

<configuration>    <system.windows.forms jitDebugging="true" /></configuration>

When JIT debugging is enabled, any unhandled exceptionwill be sent to the JIT debugger registered on the computerrather than be handled by this dialog box.

Great.  I can read – but not this.  I like error messages that are a little more direct that this.

Fortunately, after a little digging I found a similar issue over at MSDN forums.  To solve this issue I needed to install both of the following SQL Server 2005 components:

• Microsoft SQL Server Native Client
• Microsoft SQL Server 2005 Management Objects Collection

I hadn’t expected this issue since I had SQL Server client tools installed on the PC that I was using.  The catch was though that I had the SQL Server 2008 tools installed, not the SQL Server 2005 tools.

Where, oh where would I get these components?  Alas!  They are available in the SQL Server 2005 Feature Pack.

Now that I’ve got BPA installed it’s time to use it.  My question for you is do you use BPA?

This project has been on CodePlex for a while, but I thought I would give it a plug.  It’s a really good way to generate a load against a SQL Server without a lot of effort.  I’ve used it quite a number of times with presentations and use it when testing different code choices to see what happens when I run it often.

I was looking around for a method to print a web page as a PDF and found this cool tool that does it online.  So far everything I’ve thrown at it has worked very well.

I’ve been using SQL Prompt 2 for a while now and really like having intellisense added into Management Studio.  I’ll definately be taking advantage of this offer from Red-Gate…

Red Gate Software will be offering an introductory price of $99 per license on orders for SQL Prompt 3 placed on or before September 28th 2006. The full price of the product after this date will be $195.

In addition, anyone who purchased a PromptSQL license from Atadore prior to Red Gate’s acquisition of PromptSQL will have the cost of this license discounted from the purchase price of SQL Prompt 3. If multiple PromptSQL licenses were purchased the cost of one license will be deducted from the cost of each SQL Prompt 3 license purchased up to the number of PromptSQL licenses originally purchased.

For more details please contact sales@red-gate.com or call us on one of the numbers listed at http://www.red-gate.com/about/contact.htm (calls are toll free from UK and US).

Here is their description:

Red Gate Software has purchased SQL Prompt, an addictive add-in that provides Intellisense forQuery Analyzer, Visual Studio 2005, VS 2003 and Management Studio. For the summer months, while SQL Prompt undergoes further development, feature tuning and relentless testing, the tool as it is now will be made available to everyone, for free. You can download it here:
http://www.red-gate.com/products/SQL_Prompt/index.htm.

I gave it a try and while I wasn’t a fan of the program running constantly in my system tray.  It did prove to be quite useful while I as coding.  I definitely won’t be uninstalling it anytime soon and expect it’ll end up on the other machines I use by the end of the day.

Well, contrary to the title you can’t…

I felt empty inside the other day and it was all because I kept adding queries to my SQL Server Management Studio (SSMS) project and they weren’t sorting at all.  They just sat there in the order in which I added them to the project.  The continuing problem is that you cannot sort the queries in SSMS.

Now this empty feeling is being shared by a few others, and what is probably an easy enhancement still waits for that full feeling of accomplishment.  Please if you also have the time and the same empty feeling… vote on it!

Until then the guys over at SQLDBATips have created an external tool for sorting project files.  So I guess I’ll only have a half empty feeling now.

If you are ever looking for a quick cut-n-paste tool; check out Cropper in C#.  I’ve used it quite a bit in documentation, reporting errors, delivering snapshots of query results, and for a multitude of other uses.

From the site:

Cropper is a screen capture utility written in C# on the Microsoft .Net platform. It makes it fast and easy to grab parts of your screen. Use it to easily crop out sections of vector graphic files such as Fireworks without having to flatten the files or open in a new editor. Use it to easily capture parts of a web site, including text and images. It’s also great for writing documentation that needs images of your application or web site.

The files are saved straight to a folder on your desktop in the format you specify. No more ‘Print Screen’… open image editor… paste from clipboard… crop… export. Just double-click the form or press enter, and whatever is visible below the form is captured.