More Useful MVA Training Options

I find many of the MVA courses great for quickly getting across concepts or areas that I haven’t worked with before.

This month, the local MVP program has asked me to highlight the following MVA courses. The first two in particular look useful for SQL folk. The third one provides good background:

Azure IaaS Technical Fundamentals

Deep Dive into Networking Storage and Disaster Recovery Scenarios

Embracing Open Source Technologies to Accelerate and Scale Solutions

If you watch them, let me know what you think.

Azure Machine Learning Course–First Melbourne Offering

Hi Folks, we’ve been working hard on a new Azure Machine Learning course.

Come and spend a solid day finding out why Azure Machine Learning should be part of your arsenal.

Our first Melbourne offering of Azure Machine Learning Core Skills is 31st July. I’d love to see you there:

http://www.sqldownunder.com/Training/Courses/25

Out of Memory Errors in SSIS When Loading 32 bit DLLs

Was speaking with a customer today about an issue where they were receiving “Out of Memory” exceptions when trying to load a 32 bit PostgreSQL ODBC driver from within an SSIS package.

When the package was run from the command line using Dtexec, all was fine. When the package was run from within the SSIS Catalog, the same package refused to run. They had presumed it was some issue to do with 32 bit vs 64 bit drivers. The customer resolved it by installing the latest 64 bit PostgreSQL ODBC drivers.

However, it’s important to know that when you see an “Out of Memory” error on attempting to load a 32 bit DLL, it usually doesn’t mean anything about memory at all.

Under the covers, in 32 bit Windows, loading an accessing a function in a DLL was performed by:

1. Making an API call to LoadLibrary() – this brought the DLL into memory if it wasn’t already present

2. Making an API call to GetProcAddress() – because the DLL could be located anywhere in memory, there was a need to locate the actual memory address of the procedure in the DLL in its loaded location

3. Making a call to the address returned by the GetProcAddress() call.

With my previous developer hat on, there are several places where I’ve seen this go wrong.

One is that people don’t check the return address from GetProcAddress(). It can return null if the procedure isn’t found. So someone who writes code that just immediately calls the address returned without checking if it is null, would end up generating the previous infamous “this program has performed an illegal operation and will be shut down” message that we used to see.

The less common problem was that LoadLibrary() had its own qwerks. The worst was that if it could not locate the DLL, the error returned was “Out of Memory”. I always thought that was one of the silliest error messages to ever come out of Windows, but it’s entirely relevant here.

When you see an “Out of Memory” error when attempting to load a 32 bit DLL, it’s time to check whether the DLL can be located by the process. The easiest (although not the cleanest) would be to make sure the DLL is in the GAC (global assembly cache).

Invoke-SqlCmd4 – A Superior SQL Commandlet

Recently, I wrote about one of the issues with the Invoke-SqlCmd commandlet where it sets the ApplicationName when you use the parameter that should set the host.

Fellow MVP Ben Miller sent me a copy of the Invoke-SqlCmd3 that they were using. It was much better.

I then realized that there were many other options missing from these commandlets (such as options for Database Mirroring and Availablity Groups) and so I set about improving it.

Ben has posted it to the PowerShell Code Repository. You’ll find Invoke-SqlCmd4 here:

http://poshcode.org/5810

This version of the commandlet will allow setting many other parameters (and it sets the ApplicationName and HostName correctly). The parameters are:

  1. [Parameter(Position = 0, Mandatory=$true)] [string]$ServerInstance,

  2. [Parameter(Position = 1, Mandatory = $false)] [string]$DatabaseName,

  3. [Parameter(Position = 2, Mandatory = $false)] [string]$Query,

  4. [Parameter(Position = 3, Mandatory = $false)] [string]$UserName,

  5. [Parameter(Position = 4, Mandatory = $false)] [string]$Password,

  6. [Parameter(Position = 5, Mandatory = $false)] [Int32]$QueryTimeout = 600,

  7. [Parameter(Position = 6, Mandatory = $false)] [Int32]$ConnectionTimeout = 30,

  8. [Parameter(Position = 7, Mandatory = $false)] [string]$ApplicationName = "PowerShell SQLCMD",

  9. [Parameter(Position = 8, Mandatory = $false)] [string]$HostName,

  10. [Parameter(Position = 9, Mandatory = $false)] [ValidateSet("ReadOnly", "ReadWrite")] [string] $ApplicationIntent,

  11. [Parameter(Position = 10, Mandatory = $false)] [ValidateScript({test-path $_})] [string]$InputFile,

  12. [Parameter(Position = 11, Mandatory = $false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$OutputAs = "DataRow",

  13. [Parameter(Position = 12, Mandatory = $false)] [string]$FailoverPartnerServerInstance,

  14. [Parameter(Position = 13, Mandatory = $false)] [bool]$IsMultiSubnetFailover = $false

Hope that helps someone.

Optimizing Dynamic Search Screens – Fixing the Performance of Common Code Paths

One of the common challenges that arises in many applications is around how to optimize the SQL Server performance of dynamic search queries. For example, we've all seen applications with screens like this:

clip_image002

Often, there will be many, many more fields to be searched. What I often hear from developers is that there is no way to optimize the query because you don't know in advance which options the user will choose. While this is partly true, it assumes that human behaviour is much more random than it really is. Let me explain:

A few years back, I was doing performance tuning work at a company that books flights. They had a stored procedure that took a single parameter of a BookingNumber, and returned details of a customer's booking. It took around ½ second to execute.

Eventually, one of the users said "I'd love to be able to look up the booking by LastName". So they changed the procedure to have an optional parameter to allow for that. Later another user asked for EmailAddress. And so on, and so on. When I arrived, they had a 4000 line procedure that allowed 14 optional parameters. Unfortunately, it now took 90 seconds to execute every time.

What was truly sad, was that in a week of testing, I never saw any value passed except the BookingNumber. So they had created a situation where all the users all day long were experiencing glacial response times, for a situation that almost never occurred.

I see this sort of scenario played out time and again. When confronted with a screen that allows the users to pick and choose search criteria, developers always seem to assume that people will choose a wide variety of options. Yet that isn't what happens. Generally, there will be a small set of options that they will choose again and again, and only rarely will they make other choices.

Knowing that, you realise that even though you can't optimize for every possible code path, it's critical that the common paths are optimized. If necessary, provide alternate code paths for the high volume query types.

Finally, for the atypical choices made by users, what do you do? A Swedish friend of mine (and fellow SQL Server MVP) Erland Sommarskog has some awesome articles at his web site www.sommarskog.se. One in particular is on Dynamic Search Conditions. I recommend reading it if you have never done so.