SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL
On the move

Its been a fair few years since I started blogging on SQLBlogCasts and the time has now come to find a new home.

16600796_s

From now on (along with a few others of the UK SQL Server community) I shall be blogging on dataidol.com.  My page will be at http://dataidol.com/davebally , please update any feeds etc and I hope to see you there.

Sub query pass through

Occasionally in forums and on client sites I see conditional subqueries in statements. This is where the developer has decided that it is only necessary to process some data under a certain condition.  By way of example, something like this :

Create Procedure GetOrder @SalesOrderId integer,
                          @CountDetails tinyint
as
Select SOH.salesorderid , 
       case when @CountDetails = 1 then 
        (Select count(*) 
            from Sales.SalesOrderDetail SOD 
            where SOH.SalesOrderID = SOD.SalesOrderID) end
  from sales.SalesOrderHeader SOH
 where SalesOrderID = @SalesOrderID

The count of details are only required when @CountDetails = 1.

If you where to execute the above stored procedure, in the execution plan you would see the ‘Pass Through’ specified on the nested loop operator thusly:

image

As you can see, or at least visualise, this specifies that the code branch is only executed upon the condition of @CountDetails = 1.

This bears out nicely when running the procedure with ‘SET STATISTICS IO ON’,  firstly ‘EXEC GetOrder 43663,1’, ie specifying that we wish to ‘count details’

Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

and secondly ‘EXEC GetOrder 43663,0’ , dont ‘count details’.

Table 'SalesOrderDetail'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So the pass through has done its job and when not required that data is not read,  quite a good cost saving.  However, is this method always a good idea ?.   Well no, im afraid not.  The pass through requires a nested loop join to operate even when a hash or merge would be more optimal.

Consider this stored procedure

Create Procedure GetAllOrders @CountDetails tinyint
as
Select SOH.salesorderid ,
       case when @CountDetails =1
            then (Select count(*)
                    from Sales.SalesOrderDetail SOD
                  where SOH.SalesOrderID = SOD.SalesOrderID) end
  from sales.SalesOrderHeader SOH
  Option(maxdop 1)

Now let us execute with ‘EXEC GetAllOrders 1’.  As Ive stated, in the the plan we will see a nested loop operator joining SalesOrderHeader and SalesOrderDetail, the nested loop is required to process a ‘pass through’.

image

As we need all rows to logically satisfy our query, a hash would have been more optimal.

Lets compare the IO between the above and a statement that does a hash join

First the procedure:

Table 'SalesOrderDetail'. Scan count 31465, logical reads 95887, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

and now for the statement :

Select SOH.salesorderid , 
        (Select count(*)
           from Sales.SalesOrderDetail SOD
          where SOH.SalesOrderID = SOD.SalesOrderID)
  from sales.SalesOrderHeader SOH

 

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

That is a massive cost difference and one that certainly should be avoided.

if it were me, in this case my stored procedure would now be :

Create Procedure GetAllOrders @CountDetails tinyint
as
if(@CountDetails = 0) begin
    Select SOH.salesorderid ,
           0 as CountDetails
      from sales.SalesOrderHeader SOH
end else begin
    Select SOH.salesorderid , 
            (Select count(*)
               from Sales.SalesOrderDetail SOD
              where SOH.SalesOrderID = SOD.SalesOrderID)
      from sales.SalesOrderHeader SOH
end

So be careful with your pass throughs and make sure that you are not inappropriately looping due to them.

 


 

SQL Saturday 194

On the weekend of the 9th March 2013, the second SQL Saturday in the UK occurred.  A massive thank you must go to all the sponsors of the event, FusionIO, Confio, Nexus, Purple Frog, Redgate, Idera and Big Bang Data. Additionally big congratulations to Jonathan and Annette Allan, Thomas Rushton and Mark Price-Mayer for putting on such a great event,  everyone had a whale of a time. 

At the event,  I was honoured to present my first pre-con with my co-presenter Dave Morrison and I think our style and material complement each other well.  Together, we looked at many aspects of TSQL and Internals and received some great feedback from the attendees who, hopefully, will be able to look at their systems and working practices with much more knowledge about SQL Server than previously.

If you attended the pre-con then the slides and examples from the day are available from here.  These are password protected so you will only be able to gain access if you attended, sorry.

If this sounds like the sort of thing that you could also benefit from,  I am also delivering a two day course in London on the 19th and 20th of March looking at Advanced TSQL.  More details on this course and full itinerary can be found here.

In addition to the pre-con I also delivered a new presentation of “Estimation, Statistics and Oh My!”.  This was also well received and I got some very positive feedback.  If you attended this session the slides are available from here.

It was great to catch up with friends and #SQLFamily from home and abroad, and am now looking forward to SQLBits where I shall be again presenting,  see you there Smile

A year of SQL Lunchs

Time does fly, and happy birthday SQL Lunch UK.

9956870_s

It has been a year since the first SQLLunch UK, and over the year there have been 21 Lunchs with a total of 537 attendees.  OK, some of those will be the same people but in ‘conference speak’ each registration counts, but in any case i’m delighted by the success and looking forward to repeating this over the coming year.  The first few presented some interesting technical challenges, those have been licked and now with the scaling out to Cardiff we have a few more but nothing that is insurmountable.

To ‘celebrate’ this occasion it is only fitting that Patrick LeBlanc (b|t) will be presenting a session for us on the 26th of Feb.  Full details will be on SQL Server FAQ soon,  so hope to see you in either London or Cardiff.

A massive thank-you to the Attendees for coming along twice a month for a pint and SQL knowledge and an even bigger one to all of the speakers many of who have stayed up late (12:30am in New Zealand )  or up early (4:30am in Seattle) ,  your efforts are very much appreciated.

Deploying SSRS artefacts using Powershell ( Simply)

As ive stated in the past,  i’m presently part of a team on an ALM project for a major UK based online retailer.  The aim of this project is to have fully automated app and database code and schema deployment, through development, UAT and finally live.

Most parts of this equation we have pretty much got licked, SSDT is used to generate diff scripts, a custom powershell script copies SSIS packages and install relevant sql agent jobs. One major missing part though was SSRS deployments. 

Bingling around there are a few options,  RS.EXE is a vbscript language for the job, custom MSBUILD tasks, or RSS scripter.  None of these felt quite right to me, or as intuitive and extensible as I personally like things to be.

In a previous life I have written a few jobs for emailing SSRS reports directly, so was quite aware that SSRS does has a feature rich WSDL layer underneath it.  However,  since at that time my language of choice was C/C++ the WSDL was a PITA to work with and didn't really enjoy the experience.  Now using powershell though they work like a dream and can easily be exploited.

Apologies in advance for the overuse of the word “simple” from here on, but it really is simple.  I will call out the important parameters but wont spend a lot of time describing each one.

So, to the details.  The WSDL we will be using is ReportService2010,  this can be found at the url …

http://YOURHOST/ReportServer_YOURINSTANCE//ReportService2010.asmx?wsdl

naturally replacing YOURHOST and YOURINSTANCE with your host server and instance

The class ReportingService2010 is documented here.

Connecting to it and creating the service in powershell is simple:

$ReportServerUri = "http://server/ReportServer_INSTANCE//ReportService2010.asmx?wsdl"
$global:proxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential ;

Notice that extra credentials can be supplied here,  to keep things simple im going to use default. 

The first thing that we would like to do would be to create a menu structure:

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Property')
            
$property =New-Object ($datatype);
$property.Name = “NewFolder”
$property.Value = “NewFolder”
            
$numproperties = 1
$properties = New-Object ($datatype + '[]')$numproperties 
$properties[0] = $property;
     
$newFolder = $proxy.CreateFolder(“NewFolder”, “/”, $properties);

Things to note in this code : The name of the object type created in the New-Object command is the full namespace of the wsdl. The CreateFolder function creates a folder called NewFolder in the root directory.  Check the full documentation for other properties that can be added.

Finding the children of a menu node is equally simple:

$Children =$proxy.ListChildren("/",$false)

Nice, our menu structure is now defined, lets do something useful:  Lets add an RDL file, here comes the tricky part. No not really, its simple

$stream = Get-Content 'MyAwesomeReport.rdl' -Encoding byte
$warnings =@();
$proxy.CreateCatalogItem("Report","My Awesome Report","/",$true,$stream,$null,[ref]$warnings)

Here “My Awesome Report” is the name of the report and “/” is the path to create it.

Creating a datasource is equally as simple, we have a few more bits of data to set up but nothing to taxing:

$Name = "MyDSN"
$Parent = "/"
$ConnectString = "data source=(local);initial catalog=AdventureWorks"
$type = $Proxy.GetType().Namespace
$DSDdatatype = ($type + '.DataSourceDefinition')
$DSD = new-object ($DSDdatatype)
if($DSD -eq $null){
      Write-Error Failed to create data source definition object
}
$CredentialDataType = ($type + '.CredentialRetrievalEnum')
$Cred = new-object ($CredentialDataType)
#     $CredEnum = ($CredentialDataType).Integrated
$Cred.value__=2    ##   .Integrated
      
$DSD.CredentialRetrieval =$Cred
$DSD.ConnectString = $ConnectString
$DSD.Enabled = $true
$DSD.EnabledSpecified = $false
$DSD.Extension = "SQL"
$DSD.ImpersonateUserSpecified = $false
$DSD.Prompt = $null
$DSD.WindowsCredentials = $false
      
$newDSD = $proxy.CreateDataSource($Name,$Parent,$true,$DSD,$null)

This is one part I havent worked out yet though, the securitytype is a enum and I cant find the correct way to use that.  Anyone care to help me here ?, i’m no powershell guru.

So, by now we can build a menu structure, add datasources and reports all with a few simple lines of powershell code.

But, in the real world, we dont just want anyone to be able to run any report, so lets add some security on.  This is simple :

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Policy')


$NewPolicies = @();
$NewPolicy = New-Object ($datatype);
$NewPolicy.GroupUserName = "MYDOMAIN\MYUSER";

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Role')
$NewRole = New-Object ($datatype);
$NewRole.Name = "Content Manager"
$NewPolicy.Roles+=$NewRole

$NewPolicies += $NewPolicy
$datatype = ($type + '.Policy')
$NewPolicy = New-Object ($datatype);
$NewPolicy.GroupUserName = "MYDOMAIN\MYGroup";

$type = $Proxy.GetType().Namespace
$datatype = ($type + '.Role')
$NewRole = New-Object ($datatype);
$NewRole.Name = "Browser"
$NewPolicy.Roles+=$NewRole
$NewPolicies += $NewPolicy

$proxy.SetPolicies("/My Report",$NewPolicies)

Hope that you have found this post useful, and now you can proceed with automated deployments of SSRS projects.  To my mind this post covers 90% of the work that you will need to do and you now have a good lead on the remainder, Oh, and did I mention, its simple.

This post has be part of #TSQL2sDay, hosted by Wayne Sheffield (b|t)

Photo cataloguer

If you are anything like me, by know you will have a few thousand digital images spread across multiple directories and heaven knows how many of those are duplicates!

Here’s a little powershell script I use to try and keep them in order,  it recursively scans a directory (hardcoded to D:\photodrop) and interrogates any .jpg file for its taken date and time and moves the file to an appropriately named folder /CCYY/MM/DD.

Naturally, I cannot be held responsible if your precious memories get lost by using this script ;)

Let me know how you get on…

[reflection.assembly]::loadfile( "C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll")

#http://archive.msdn.microsoft.com/PowerShellPack

function CreatePath ($folderPath){

    $Path1 = $folderpath.Split("\")
    $BuildPath = ""
    for($x=0;$x -lt $Path1.count;$x+=1){
        $BuildPath = $BuildPath += $Path1[$x] +"\"
        if($objFSO.FolderExists($BuildPath) -eq $FALSE){
            $objFSO.CreateFolder($BuildPath) | Out-Null
         }
    }

}

function MakeString {
    $s="" ;
    $x = $args[0]
    for ($i=0 ; $i -le $args[0].value.length; $i ++)
    {
        $s = $s+ [char]$args[0].value[$i]
    }
    return $s
}
$objFSO = New-Object -ComObject Scripting.FileSystemObject
           
$LastFilePath=""
foreach($File in Get-ChildItem -Path D:\PhotoDrop -Filter *.jpg -Recurse ){
    $ImgBlob = New-Object -TypeName system.drawing.bitmap -ArgumentList $File.FullName
    $Date=""
    $Date = MakeString($ImgBlob.GetPropertyItem(36867))
    $ImgBlob.Dispose()
   
    try {
        $Dir = $Date.Substring(0,10)
        $Dir = $Dir.Replace(":","\")
        $folderPath = "D:\Photos\"+$Dir
        #check to see if it is missing
        if($LastFilePath -ne $folderPath){
            CreatePath $folderPath
            $LastFilePath = $folderPath
        }
       
        $NewFileName = $folderPath+"\"+$File.Name
        if($objFSO.FileExists($NewFileName) -eq $FALSE){
            $File.MoveTo($NewFileName)
        }
       
           
    }
    catch{
        $err=$Error[0]
         $err
 

    }
}

Scaling out SQLLunch UK

As you may be aware, I have been running a lunch time user group ‘SQLLunch UK’  having stolen drawn inspiration from Patrick Leblanc (t|b) ’s SQLLunch webcasts.  These however are not traditional webcasts, as although we use remote speakers,  to watch you have to get away from your desk for lunch and come down to the venue.  I know that many people would like to watch from their desk but there are many webcasts and I think that this formula does offer something different.

We have been London based since inception and now the opportunity has arisen to scale it out to another location.   Steph Locke, who run the Cardiff user group, has accepted the challenge and will now be holding SQLLunch’s in Cardiff at the Buffalo Bar 11 Windsor Place, Cardiff CF10 3BY.  Having now had a fair bit of experience, im hopeful that there wont be to many extra technical challenges presented by two venues.

Our first SQLLunch with this format is on the 22nd of Jan with Conor Cunningham (b) of Microsoft.

Signup for both the London and Cardiff events is at www.sqlserverfaq.com

Hope to see you there

SSDT – TSQL Unit testing in multiple environments

Late in December, one the long awaited portions of functionality inside SSDT was released to an eager public, that being unit testing.  There has been a lot of noise generated about sql unit testing as a concept recently and hopefully you have decided that that is now the way to go.  Bill Gibson of Microsoft has blogged at quick beginners guide to SSDT unit testing here.

This is not a post about unit testing per se or the operation of SSDT unit testing, but what you need to do to run the same unit tests across multiple environments.  Even the smallest shop will have multiple SQL servers that you will wish to run tests against (DEV, UAT, integration etc) and if you use SSDT for more than 5 minutes you will discover that there is no easy way to aim the tests at these servers.

Jamie Thomson(b|t) has previously blogged about this inside datadude (SSDTs’ predecessor) and what a pain it can be.  Well, it is still a pain and the supported route is still via this method and it does work in SSDT.  I will say two things though,  firstly the config override file has HAS to be called machinename.sqlunittest.config or username.sqlunittest.config nothing else will do, and secondly the file is very sensitive to formatting.  I had a single leading whitespace in mine, which meant that the file couldn’t be processed correctly ! Also note that the element naming has changed to SQLUnitTesting_VS2010 ( im not running VS2012 to see if it is still the same there)

As I say, this does work, but is not very clear and quite limiting in scope.  I can only have effectively one override file and what if i have a centralised CI build server I wish to run our tests from against multiple environments ?  Well, without more confusing workarounds (that I can think of but haven't tried) you cant.

So, do we have any other options ?

Our friends in the application development world have faced and solved this problem,  though I don't believe their problems are exactly analogous, but this is probably the simplest route to solve it.  Using a VS plugin called Slow Cheetah the app.config file can be transformed at build time to support multiple environments.  Each build configuration (Debug / Release) can have a transform for app.config, after installing the plugin and R-Clicking on the app.config, you should see an ‘Add Transform’ option.

image

After selecting that, for each of your build configurations (you can always add more) you will see an app.buildname.config file.

image

The transform to be added to files is:

<?xml version="1.0" encoding="utf-8" ?>

<!-- For more information on using transformations

     see the web.comfig examples at http://go.microsoft.com/fwlink/?LinkId=214134. -->

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">

  <SqlUnitTesting_VS2010>

    <ExecutionContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />

    <PrivilegedContext Provider="System.Data.SqlClient" ConnectionString="Data Source=Server;Initial Catalog=DataBase;Integrated Security=True;Pooling=False" CommandTimeout="30"

           xdt:Transform="Replace" />

  </SqlUnitTesting_VS2010>

</configuration>

When you now build and run your tests Slow Cheetah will apply the transforms to app.config and you will connect to the specified server.  Slow Cheetah includes a ‘Preview Transform’ option to aid with development so you don't have to guess Smile  To me this seems like a much more preferable route that the mstest overriding method.

This is all well and good, but what if you want even more control. 

Disclaimer: I have not tried this any more that detailing it here, I cannot vouch for long term (or even short term) stability.  You are on your own if it all goes wrong.

There is a method by which you can specify exactly which instance/ database to use.  You can programmatically override the connection.  If, in Visual Studio, you select ‘View Code’ on one of your C# unit test modules,  you will see a member called TestInitialize.  Inside this member we can close the existing connection and open one to the server we really wish to test against like so:

        public void TestInitialize()

        {

            base.InitializeTest();

            base.ExecutionContext.Connection.Close();

            base.ExecutionContext.Connection = new System.Data.SqlClient.SqlConnection("Data Source=Server;Initial Catalog=Database;Integrated Security=True;Pooling=False");

            base.ExecutionContext.Connection.Open();

        }

Obviously I have hardcoded the connection string here,  but you could have it read from an environment variable or topology file or somewhere else.

Have fun with any of these methods and any one should get you closer to the goal of more reliable code.

Do you know of a more user friendly and workable solution ? I’d love to hear it.

When is a whitespace not a whitespace ?

As I'm sure I must have mentioned in the past, I’m presently involved in a large ALM project at a client.  Part of this project is using Sql Server Data Tools (SSDT) to aid the developer experience and to establish the schema under source control as “The Truth”.

SSDT, has the ability to compare a schema under development to a schema on a server to produce diff scripts and therefore enabling automated deployments,  this is done using SQLPackage.exe .  A big part of this is naturally establishing trust in the software ie SSDT.  We have to be 110% confident that the scripts produced are the right scripts.  Recently though, we had a spurious change appear that took some explaining.

A stored procedure was appearing in a diff script that apparently had no changes, when comparing the project to the schema inside SSDT rather that SQLPackage.exe, I could indeed see that a change had been detected though what that change was, wasn't immediately apparent.  Here is a similar “non-change” :

image

The developer has tidied up the formatting of the statement ever-so slightly but we have the “IgnoreWhiteSpace” setting enabled.  This change should have been ignored and indeed testing proved that when you change tabs to spaces or add spaces etc ( ie whitespace changes) then the change would be ignored,  so something else was happening.

Loading up a hexeditor and comparing the two I spied another difference that had been overlooked,  the comment had a TAB changed to spaces.  But as I had ignore white spacing enabled therefore shouldn't have been seen.  Testing that this was the culprit was simple enough by turning on the “IgnoreComments”  option, and sure enough, no change.

This ,to me, appears to be a bug,  whitespacing is not ignored in comments when the “IgnoreWhiteSpace” option is set.  For us using “IgnoreComments” is not an option, so we had to live with this change being made to unify the production schema with source control (The Truth).

I did raise a connect item here : https://connect.microsoft.com/SQLServer/feedback/details/774098/ssdt-whitespace-in-a-comment-is-not-ignored-when-using-the-ignorewhitespace-option

but this was closed as “By Design”,  not sure I agree with that assessment but what can you do, eh ?

Hope that this at least saves someone some head scratching on the same issue.

SQL Saturday 194 - Exeter

Many kudos goes to Jonathan and Annette Allen and the others on the team for confirming SQL Saturday 194 in Exeter on the 8th and 9th of March.  The event home page is here http://www.sqlsaturday.com/194/eventhome.aspx and I delighted that myself and Dave Morrison will be presenting a full day pre-con on the 8th on favourite subjects “TSQL and Internals”.

Here is the full abstract :

TSQL and internals - When faced with performance issues there are many lines of attack. Tuning the engine itself can get you so far, however for maximum effect you need to understand how the engine and how it translates SQL statements into performable actions. This is not a simple task, it is a massive task to deal with a multi-table join and the number of permutations can be immense.

To back up this knowledge, we can create better performing TSQL and understand the impact that is has upon the engine and recognize the pitfalls and gotcha’s that exist in SQLServer. Ultimately, there is no ‘best way’ to perform a single task only many variations of ‘it depends’ , but now we can pick the most appropriate option for the required dataload.

Over the years, there have been many myths and misconceptions have grown around the product, some have basis in older versions and some are just wrong. Continuing to build on the knowledge given so far these issue will be explored and broken down and proved or disproved. Finally we will look to the future and explore SQL Server 2012 and the new functionality that that brings and some of the common uses that we will be able to address.

After completion of this days pre-con, attendees will have a more complete knowledge of execution plans, and how they relate to the physical and logical actions that SQLServer will be executing on their behalf. The attendees will also have a more rounded and fuller knowledge of TSQL and the implications of incorrectly defining a query.

Dave is a fountain of knowledge on execution plans and optimizer internals and ,though i may flatter myself, I’m no shrinking violet when it comes to TSQL and such matters.  I hope that if you cant join us, then there are other pre-cons available from other experts in their fields that may ‘float you boat’ too.  The pre-con page is http://sqlsouthwest.co.uk/SQLSaturday_precon.htm

Also, excitingly, this pre-con day is sponsored by Fusion-IO which is a great boon for the day.

If you want a more of this then i am offering a 2 day TSQL course starting on the 19th of March. More details on this are available here

More Posts Next page »