Query optimizer pains

As requested by Yavor Angelov from MSFT via Twitter im writing down what seem to to be my biggest pains with QO in SqlServer.

1)
Whenever you have a a join between large tables QO will look at the indexes that exists on the join keys. If some indexes are missing it seems that the default join type chosen is Nested Loops which is OK for fast results but is really slow if the memory is scarce on the server or there is a high load.
Also this is hard to execute in parallel.
Usually in this situation I have to look at the execution plan and suggest the proper plan to QO by creating missing indexes.
I think creating a temporary index if the cardinality of the join tables is high would help a lot to change QP from nested loops to hash joins which can take advantage of multiple processors. Also the time to create the NC index that is missing is also very low.

2)
A lot of times it seems that the QO is not looking at the statistics to apply Selection first on small and limit the resultset that has to be joined with the other tables to a small number of rows.
The QO should calculate QP cost using different methods and then pick the QP with the smallest cost. This does not seem to happen very well especially when there is a mix of small and large tables to be joined.

To test these scenarios you can use a Virtual Server with minimum memory resources and you’ll see that the differences between QPs become very evident.

Automating encryption tasks with PowerShell

Recently I answered a question on how to automate a task that runs a DTS package, encrypts the result with PGP and then transfers the encrypted file to a FTP site. I became interested in this subject since I heard the same request from 2 people in less than a month and realized that this is a standard pattern for a business process. Extract.Encrypt.Transfer.

First person asking me this found the solution in having a .NET application designed to perform these task using existing .NET components.

I think the is a easier solution is a PowerShell script.

All ww have to do is script the following:

#execute DTSPackage
DTSRun /S "(DBServer)" /N "DTSPackage" /A "StringPar":"8"="String Value" /A "IntPar":"3"="1" /W "1" /E
#Encrypt result file
gpg -E Datafile.csv --options
#transfer file over to FTP
function Open-FTPConnection($ftphost, $username, $password) {

[void][Reflection.Assembly]::LoadFrom("C:\IndyNet-Daily-20051007\Indy.Sockets.dll")
$ftp = new-object Indy.Sockets.FTP
$ftp.Disconnect()
$ftp.Host = $ftphost
$ftp.Username = $username
$ftp.Password = $password
$ftp.Connect()
$ftp.Passive=$true;
return $ftp
}
function Close-FTPConnection($ftp) { $ftp.Disconnect(); } function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {     "Uploading {0} into {1}.." -f $sourceFileName, $targetDir;     $ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);     "Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir; } $f = Open-FTPConnection "localhost" "foo" "bar" Upload-FTPFile $f "C:[\Path]\Datafile.pgp" (Get-FTPCurrentLocation $f) "Datafile.pgp" Close-FTPConnection $f

Source code found here:

http://groups.google.com/group/microsoft.public.windows.powershell/msg/6d9b5a4541160b25

Command line GNUPG can be downloaded  from here:

http://www.gnupg.org/

Of course this needs some polishing but … this is can be easily reused


SQL Server 2008 Failover Clustering with High Availability

I’m heading home from the August session of Chicago PASS and SSUG meeting.
First off, this was the biggest meeting in Chicago. Over 80 people signed up and for a very good reason.
After a very interesting Microsoft SQL Server 2008 Administration with Windows PowerShell presentation by The MAK and Yan Pan,  Allan Hirt managed to cram the essentials of the new MS Failover Cluster Manager and he made it in less than an hour!
One very interesting and long awaited feature is Live Migration. Basically for the first time you can failover a MS Cluster without dropping any transaction. This is only happening for planned failovers of course but this is a phenomenal achievement for the MS team. Until now any time you would failover a cluster, the operation was the equivalent of stopping the SQLServer service an restarting it on a different machine. Obviously any pending transactions were dropped. With Live Migration the when a planned failover is initiated data from memory is copied over to the new active node and all transactions are seamlessly ported over as well. There might be some performance decrease but no transactions are dropped achieving true High Availability.
And last but not least all this is free with Windows Server 2008 Enterprise or Datacenter
For more information read this blog from Microsoft.
http://blogs.technet.com/virtualization/archive/2009/05/06/microsoft-hyper-v-server-2008-r2-release-candidate-free-live-migration-ha-anyone.aspx

Thanks Allan!