Claypole's World - The SQL Server Side

Focus on Fast Track : My SQLBits VI Presentation

James.Rowland-Jones — Sun, 25 Apr 2010 20:01:12 GMT

For those of you interested in the principles of Fast Track I thought it’d be worth putting out my SQLBits VI presentation slide deck on my blog. It will of course be available from the SQLBits website in due course. In about 6 weeks time that will be the better resource as the session recordings will be available at that time. Only then will you be able to truly relive the JRJ and naked Neo experience. I expect both these resources (the slides and the session recording) will be available from here.

For now though you’ll have to make do with the slides. Simon Munro upped the ante with some natty embed from Slide Share. Imitation is still the best form of flattery (even though his slides are way cooler) and so I have gone for something remarkably similar.

Fast track foundations getting serious about sequential io

View more presentations from jrowlandjones.

Finally a big thank you to my friend and SQLBits Committee colleague Allan Mitchell for teaming up with me and providing the SSIS demonstrations that formed the second half of this presentation. He might think I have a slightly perverse interpretation of the word “team” after being subjected to persistent name calling throughout my section of the presentation. Actually I think he rather enjoyed it… Although it’s not often you see Allan struggling for words. :o) See for yourself when the videos are released on www.sqlbits.com!

Cheers, JRJ

Focus on Fast Track : Understanding the –E Startup Parameter

James.Rowland-Jones — Sun, 25 Apr 2010 06:12:42 GMT

One of my focuses at the moment is the work that Microsoft have done with various hardware providers to deliver “Fast Track” – A reference architecture for SQL Server Data Warehousing that delivers predictable blistering performance on prescribed commodity hardware. Sounds great I think you’ll agree. What is even better is that the architecture comes with a series of best practices on how to make the most of your Fast Track system.

As you might expect there are some SQL Server settings you need to change to make. However, I am impressed by the fact that the team have gone further than this. There is also a focus on the build of your solution and how you should load data into a Fast Track Data Warehouse.

It’s inspired me to get blogging again and I’ve decided to write a series called “Focus on Fast Track”. This should follow on nicely for those of you who attended my session at SQLBits VI entitled “Fast Track Foundations : Serious about Sequential I/O”. If you didn’t see this presentation it shouldn’t matter too much – especially as all the sessions are recorded at SQLBits and are made available for free over the internet once all the editing / post production work has been done.

Fast Track is important an important step for Microsoft. They are helping customers get the most from SQL Server and not just from the software perspective. What is being offered is clear deployment solution for data warehousing. Tackling the physical deployment challenge with real hardware specifications is a big step forward. I hope it is the first of many.

In this first post I’d like to talk about one of the recommended settings for SQL Server in a Fast Track System. The use of the –E startup parameter. Actually its very relevant to all SQL Server Data Warehousing scenarios. It has also had a chequered past in terms of how this parameter has been documented. I’d therefore like to try and bring a new insight into what this parameter does but also why it is important.

Life without –E

Before identifying what –E does or doesn’t do I think it would be helpful to clearly state what happens when –E has not been configured on a SQL Server.

Imagine a Database with a Primary and Secondary File Group. The Primary contains a single file and only holds the internal tables etc and the secondary hold the user data. The secondary file group contains multiple files that are evenly sized and spread across multiple LUNs. It would look something like the database below.

CREATE DATABASE [EParam] ON  PRIMARY 
    (NAME = N'EParam'
    ,FILENAME = N'C:\Temp\EParam.mdf' 
    ,SIZE = 51200KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024KB 
    ) 
    ,FILEGROUP [Data]  DEFAULT 
    (NAME = N'EParam1'
    ,FILENAME = N'C:\Temp\EParam1.ndf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024KB
    )
    ,
    (NAME = N'EParam2'
    ,FILENAME = N'C:\Temp\EParam2.ndf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024KB 
    )
    ,
    (NAME = N'EParam3'
    ,FILENAME = N'C:\Temp\EParam3.ndf'
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024KB
    )
    , 
    (NAME = N'EParam4'
    ,FILENAME = N'C:\Temp\EParam4.ndf' 
    ,SIZE = 10240KB 
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 1024KB
    )
LOG ON 
    (NAME = N'EParam_log'
    ,FILENAME = N'C:\Temp\EParam_log.ldf' 
    ,SIZE = 1024KB 
    ,MAXSIZE = 2048GB 
    ,FILEGROWTH = 10%
    )

When a database has been laid out as above SQL Server will use “a round robin proportional fill” algorithm to evenly distribute the data. Every time a table requests an extent from SQL Server it will be satisfied by allocating an extent from the next file in the filegroup. This ensures that the files fill up evenly. Note the round robin is not determined at the table level. We can see this in the following example script.

Use EParam

Create Table NoET1 (ECol1 char(8000)) ON Data;
Create Table NoET2 (ECol1 char(8000)) ON Data;

declare @cnt int = 0

While @cnt <8
BEGIN
    Insert into NoET1
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END
GO
declare @cnt int = 0

While @cnt <8
BEGIN
    Insert into NoET2
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END
GO
declare @cnt int = 0

While @cnt <8
BEGIN
    Insert into NoET1
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END
GO
declare @cnt int = 0

While @cnt <8
BEGIN
    Insert into NoET2
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END

--and so on

I’ll use the excellent Internals Viewer to illustrate what has happened.

Each time a new extent has been required it has been granted from the next data file in the sequence. This has kept the data distribution even.

If prop fill operated at the table level then the NoET1 (blue purple) table would have data in all four files but it does not.

The Gaps (Slight Tangent)

You may be wondering why there is a couple of odd looking things going on in the picture above. Lets look at file 5 as a good example.

Why are the first set of squares empty?
Why are there holes and mixed colours at the start of the allocation process but not at the end

At the beginning of every file is a bunch of pages that define the file. They are the File Header(5:0), PFS(5:1), GAM(5:2), SGAM(5:3), DCM(5:6) and BCM(5:7) pages. The numbers in brackets relate to the (File Number : Page Number). That takes care of the first eight grey squares. (5:4) and (5:5) are not allocated.

The reason the next extent has three colours is because it is a mixed extent. It contains one page from NoET1, six pages from NoET2 and an IAM page.

IAM pages are distributed as needed through files and manage the extents for a 4 GB range in a given database file for a given allocation unit. Each Table can have up to three types of allocation unit associated with it – IN_ROW_DATA, LOB_DATA, ROW_OVERFLOW_DATA. All our Rows are IN_ROW_DATA. Furthermore, you will get one IAM page for each file on which an allocation unit has extents.

So in our case lets quickly look at NoET1. This table only contains IN_ROW_DATA and so is associated with a single allocation unit. However, it contains data in three files so we have 3 IAM Pages for this table.

Abbreviation	Meaning
GAM	Global Allocation Map
SGAM	Shared Global Allocation Map
DCM	Differential Change Map
BCM	Bulk Changed Map
PFS	Page Free Space

Mixed Extents, GAM, SGAM, DCM, BCM and IAM pages are all covered in books online here.

Where is the Confusion?

The functionality of –E I don’t believe has been completely documented. There is a subtlety to how –E works that hasn’t in my view been adequately described. Even the Fast Track paper references an incorrect/out of date KB Article. Let’s just highlight the sources of incorrect info and look at what they say.

Fast Track White Paper

For the clarity of the post I've copied verbatim what the Fast Track White Paper says:

“-E should be added to the start-up options. This increases the number of contiguous extents in each file that are allocated to a database table as it grows. This improves sequential disk access. Microsoft Knowledge Base Article 329526 describes the -E option in more detail.”

What is wrong with this statement? –E does not increase the number of contiguous extents in each file that are allocated to a table. Maybe it should – interesting argument - but it does not.

KB Article 329526

The source: 329526.

“The default allocation mechanism allocates one extent per file, using a round robin proportional fill factor algorithm, before it switches to the next file in the filegroup.
In the SQL Server 64-bit release, you can allocate four consecutive extents per given file, before switching to the next file in the filegroup.”

So what is wrong with this statement? You do not allocate four consecutive extents per given file before switching to the next file in the filegroup. Certainly not on SQL Server 2008. This KB article hasn’t been updated to say it covers 2008 but I don’t believe the code here changed. I would ignore this KB Article completely.

My Blog

The SQL Gods may strike me down for this one. Rule no.1 don’t believe everything you read. I’d referenced 329526 for a series of posts about enterprise options but clearly hadn’t tested this one properly. Naughty Claypole. I would ignore this post if I were you.

Data Loading Performance Guide

The Data Loading Performance Guide says the following about –E :

“When SQL Server bulks load into a table allocated to this filegroup, extents are allocated in a round robin fashion. In the default configuration, SQL Server allocates one extent to each file before moving to the next in the chain. It is possible to increase this to 64 extents using the –E startup flag.”

File	Default Extent Allocation	With –E Startup
0	Extent 1	1-64
1	Extent 2	65-128
0	Extent 3	129-192
1	Extent 4	193-256
etc	etc	etc

This is easily the closest to the actual answer. I’d say the wording “It is possible to increase this to 64 extents using the –E startup flag” is slightly ambiguous and doesn’t deal with a small subtlety which is what I hope to show you in the next section. Suffice it to say that SQL Server does not increase the allocation to 64 extents when –E has been enabled. Remember an allocation means a reservation. If a table received 64 extents every time it received an allocation then the Total pages column in sys.allocation_units would be increased by 512 pages. It does not.

So what does –E do?

-E changes the rate at which the proportional fill occurs. The Data Loading Performance Guide alludes to this – especially in the table. Rather than moving to the next file after a single extent allocation the movement occurs after 64 extents have been allocated. These extent allocations do not need to come from a single table. If I was loading two tables in parallel for example and had –E enabled then both tables would consume those 64 extents in the single file before moving on to the next file.

-E does not alter the fact that SQL Server allocates one extent at a time.

Therefore when –E is enabled you will only see Total Pages increase by 8 pages (1 extent) every time an allocation occurs.

That sounds like I might be splitting hairs but I believe the distinction is important to understand. It is especially important for Fast Track as Fast Track is completely dependent on sequential I/O for its performance. –E is enabled to enhance the sequential scanning performed in a read-ahead so that more data is contiguously available in a single file. If two tables that share the same files/filegroup are loaded in parallel then the advantage of –E is removed. The extents will be fragmented and this will in turn reduce the performance of the read-ahead.

Now to show that I have learned my lesson. Here comes the proof.

Proving –E Functionality

First of all we need to enable the –E startup parameter. You can do this by amending the startup parameters on the SQL Server Service. You should be using SQL Server Configuration Manager to do this.

Step 1 : Enable –E Startup Parameter

I have actually documented setting the –E startup parameter in another post. There are a couple of pitfalls that I thought were worth bringing out and so if you are unsure about setting Startup Parameters I’d urge you to read it. It includes a step by step guide to setting this correctly (yes you can set it incorrectly).

However, if you just want to get on with the show make sure you set your startup parameter with exactly ;-E at the end of your list of Startup Parameters on the Startup parameter property of the SQL Server Service by using SQL Server Configuration Manager.

Step 2 : Create a multi file database

Something like this should be fine.

CREATE DATABASE [EParam] ON  PRIMARY 
    ( NAME = N'EParam'
    , FILENAME = N'C:\Temp\EParam.mdf' 
    , SIZE = 51200KB 
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 1024KB 
    )
, FILEGROUP [Data]  DEFAULT 
    ( NAME = N'EParam1'
    , FILENAME = N'C:\Temp\EParam1.ndf' 
    , SIZE = 10240KB 
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 1024KB 
    )
    , 
    ( NAME = N'EParam2'
    , FILENAME = N'C:\Temp\EParam2.ndf' 
    , SIZE = 10240KB 
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 1024KB 
    )
    , 
    ( NAME = N'EParam3'
    , FILENAME = N'C:\Temp\EParam3.ndf' 
    , SIZE = 10240KB 
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 1024KB 
    )
    , 
    ( NAME = N'EParam4'
    , FILENAME = N'C:\Temp\EParam4.ndf' 
    , SIZE = 10240KB 
    , MAXSIZE = UNLIMITED
    , FILEGROWTH = 1024KB 
    )
     LOG ON 
    ( NAME = N'EParam_log'
    , FILENAME = N'C:\Temp\EParam_log.ldf'
    , SIZE = 1024KB 
    , MAXSIZE = 2048GB 
    , FILEGROWTH = 10%
    )
GO

Step 3 : Create two tables

Create Table ETestTable (ECol1 char(8000)) ON Data;
Create Table ETestTable2 (ECol1 char(8000)) ON Data;

Internals Viewer shows that the database is still empty. As no data has yet been added nothing has been allocated.

Step 4 : Load in 64 extents of data into ETestTable

declare @cnt int = 0

While @cnt <504
BEGIN
    Insert into ETestTable
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END

Internals Viewer shows that all 504 records went into a single file. The round robin has not yet kicked in. We load in 504 records because the first extent is mixed extent and our first 8 page requests will be allocated to mixed extents. The situation gets slightly muddled as one of the pages in the first extent is an IAM page meaning that we spill over into a second mixed extent. Therefore the first TWO extents allocated will be mixed extents. However, the 9th page request will allocate a uniform extent as we can see below.

Step 5 : Load another 64 extents of data into ETestTable

This time we have no issue. We can happily load in 512 records for our 64 extents.

Internals Viewer shows us that EParam3 has allocated all the extents.

Clearly then we can see 64 extents being allocated from one file before the prop fill round robin kicks in.

However, notice that there is no IAM page in the EParam3 file. Where is it?

Well it is actually in the mixed extent on EParam4.

The two pages I have highlighted in the image above are the IAM Pages. The first IAM page on the left handles allocations from IN_ROW_DATA for ETestTable in the EParam4 file and the IAM page on the right handles IN_ROW_DATA extent allocations from ETestTable in the EParam3 data file.

Step 6 : Parallel Load Two Tables

Step 6a : Load 64 Extents into second table

First of all let’s even up proceedings and do an initial load into even things up.

declare @cnt int = 0

While @cnt <512
BEGIN
    Insert into ETestTable2
    Values (REPLICATE('X',8000));
    
    Set @cnt = @cnt +1
END

This time we can load in 512 pages in our first attempt to get to a full 64 extents because the previously allocated mixed extents have consumed 5 of our initial 8 page requests. Also note that ETestTable2 needs 2 IAM pages as the data has now spread into two files.

Step 6b : Parallel load ETestTable and ETestTable2 with 32 Extents each

To simulate the parallel load I’ve inserted a row for each table in the following script and halved the number of loops:

declare @cnt int = 0

While @cnt <256
BEGIN
    Insert into ETestTable
    Values (REPLICATE('X',8000));
    Insert into ETestTable2
    Values (REPLICATE('X',8000));    
    Set @cnt = @cnt +1
END

What are the results?

We have a striped and perfectly interleaved set of extents.

Clearly then –E does not allocate any more than one extent at a time and does nothing to protect this situation from occurring.

For completeness let’s just see where those IAM Pages are:

They are of course in that mixed extent that we used earlier.

Conclusion

So there we have it. Clearly –E does not allocate more than one extent at a time. The change made to SQL Server by adding it as a startup parameter is that round robin behaviour in the proportional fill algorithm has been affected. The round robin does not change files until 64 extents have been allocated from a single file.

Many thanks to Thomas Kejser for his help and to Henk van der Valk for suggesting Internals Viewer as the best way to showcase this behaviour.

In my next post in the Focus on Fast Track series I will look at the implications of –E before moving on to look at Sequential I/O and the impact –E has on it.

Until the next time,

JRJ

Enabling SQL Server Start-up Parameters – A Couple of Pitfalls

James.Rowland-Jones — Fri, 23 Apr 2010 07:55:16 GMT

In SQL Server there are a couple of ways to set startup parameters:

SQL Server Configuration Manager
Command Prompt Net start

Of these only one can be relied upon as a permanent option. That is the Configuration Manager. I have been toying with startup parameters recently as part of my interest in Fast Track Data Warehousing. I have noticed there are a couple of things you really need to be mindful of when setting these values.

To illustrate these little gotchas I thought it would be worth while stepping through a scenario whilst configuring the –E startup parameter.

How To : Enable –E

Below are the steps you need to follow:

Step 1 : Fire Up SQL Server Configuration Manager

Start >> All Programs >> Microsoft SQL Server 2008 >> Configuration Tools >> SQL Server Configuration Manager

Step 2: Right Click on SQL Server Service and Select Advanced Tab

Step 3 : Click on the drop down arrow and update the Start-up Parameter property

To set the –E parameter tack ;-E to the end of the list of startup parameters

Step 4 : Click on Apply and then Clear the warning box

Click Apply

Click OK

Step 5 : Click on the Logon Tab and Restart SQL Server by clicking on the “Restart” button

So What’s the Problem? Where are the gotchas?

Assuming you followed Steps above EXACTLY then nothing will be wrong and –E will work perfectly well for you.

However, gotchas do exist. Look back at Step 3

This is where the gotchas are. The Startup Parameter property box is very sensitive.

If you put your new parameter at the end of the list you must precede your new option with a semi-colon
If you put your parameter at the beginning of the list you must append the option with a semi-colon (documented but not clearly)
You must NOT have any spaces between the semi-colon and the hyphen.

The last one is the real killer

If you put a space between the semi-colon and the hyphen then the parameter will be ignored.

This works:

This does not:

SQL Server interprets the startup parameter as “space-E” not “–E”. It will therefore not have the desired effect.

The SQL Server Log

To compound the matter the SQL Log shows something quite interesting.

With ;-E

2010-04-20 09:27:36.91 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

2010-04-20 09:27:36.91 Server      (c) 2005 Microsoft Corporation.
2010-04-20 09:27:36.91 Server      All rights reserved.
2010-04-20 09:27:36.91 Server      Server process ID is 1456.
2010-04-20 09:27:36.91 Server      System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:27:36.91 Server      Authentication mode is MIXED.
2010-04-20 09:27:36.91 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:27:36.91 Server      This instance of SQL Server last reported using a process ID of 5876 at 20/04/2010 09:27:30 (local) 20/04/2010 08:27:30 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:27:36.91 Server      Registry startup parameters:
     -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
     -E
2010-04-20 09:27:36.92 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

Illustration of ;-E

I will use the great Internals Viewer to demonstrate the impact of setting this parameter.

In the graphic above the blueish boxes represent data pages that have been allocated from three extents. It can be clearly seen that all extents have been allocated from a single file.

Without going into the details here (please see my next post to get the juice on –E) the default behaviour of SQL Server has changed. Proportional fill has not occurred.

With ;space-E

2010-04-20 09:47:19.86 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:11:52
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

2010-04-20 09:47:19.86 Server      (c) 2005 Microsoft Corporation.
2010-04-20 09:47:19.86 Server      All rights reserved.
2010-04-20 09:47:19.86 Server      Server process ID is 1604.
2010-04-20 09:47:19.86 Server      System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:47:19.86 Server      Authentication mode is MIXED.
2010-04-20 09:47:19.86 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:47:19.86 Server      This instance of SQL Server last reported using a process ID of 1456 at 20/04/2010 09:47:13 (local) 20/04/2010 08:47:13 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:47:19.86 Server      Registry startup parameters:
     -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
      -E
2010-04-20 09:47:19.87 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

Illustration of ;space-E

As you can see from the graphic below the purple boxes representing data pages are now spread in three different data files. Three extents have again been allocated and the proportional fill algorithm has spread this allocation evenly across the files.

So what does the SQL Server log show?

What’s the difference in the log? Almost nothing! One could be very easily led to think that the –E startup parameter had been set as it shows in both logs. You have to look pretty closely to see that the second log –E is mis-aligned.

However, the actual difference in behaviour is significant.

In the first instance SQL Server behaviour has been altered. In the second the change has no impact on SQL Server. However, by looking at the log one could be led to believe that –E had in fact been set. Confusing? I think so.

SQL Server Log Conclusions

Discovering this issue has led me to an interesting conclusion.

The SQL Server Log is a statement of intent not a statement of fact.

I was expecting the log to tell me to tell me that –E had not been enabled when I had configured ;space-E. It did not. It tells me that SQL Server was started with space-E.

The log is therefore spitting out the configured values not the running values (the ones that actually have an effect).

What would be better is if the Log told me three things.

What options were configured as Startup parameters
What options had been set – i.e. the Runtime values
What options weren’t recognised and therefore had been ignored

I think it’d be much more helpful (and a lot less confusing) if SQL Server did something like this:

2010-04-20 09:47:19.86 Server      System Manufacturer: 'Dell Inc.', System Model: 'Latitude D830'.
2010-04-20 09:47:19.86 Server      Authentication mode is MIXED.
2010-04-20 09:47:19.86 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2010-04-20 09:47:19.86 Server      This instance of SQL Server last reported using a process ID of 1456 at 20/04/2010 09:47:13 (local) 20/04/2010 08:47:13 (UTC). This is an informational message only; no user action is required.
2010-04-20 09:47:19.86 Server      Configured registry startup parameters:
     -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
      -E

2010-04-20 09:47:19.86 Server      Running registry startup parameters:
     -d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
     -e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
     -l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

2010-04-20 09:47:19.86 Server Unrecognised Registry startup parameters:
-E

It would be even nicer if the log actually explained what these options are. It could even provide a link to the BOL documentation. That’d be useful.

Do you agree? I have posted a connect item up. If you think this is worth changing in SQL Server then please vote!

https://connect.microsoft.com/SQLServer/feedback/details/553439/sql-server-log-and-startup-parameter-logging

You may have noticed that I haven’t really explained in full what the –E parameter is. That is because I want to cover that in my next post and kick of my series entitled "Focus On Fast Track". It’s nearly done so don’t worry you won’t have to wait long!

Until the next time, James

Last Call for SQLSocial London Event 16th March 2010 : Meet Itzik Ben-Gan, Greg Low, Davide Mauri & Bill Vaughn

James.Rowland-Jones — Sun, 14 Mar 2010 12:54:36 GMT

Simon Sabin has organised a SQLSocial event for this coming Tuesday. It’s at the Smithfield Tavern and will start at 18:30.

It features some of the worlds most famous SQL Server celebrities in an informal setting. These guys are over in the UK for DevWeek and have kindly given up an evening to meet you all.

85 people have already registered – so this is very much the last call for any stragglers who have yet to make up their minds.

There aren’t many times you get the opportunity to just walk up to the likes of Bill, Greg or Itzik and ask them a question so I’d urge you to take advantage. You might even want to ask Davide about his plans for DTLoggedExec – his free SSIS logging tool that’s available now on codeplex. Failing that you can always ask Simon about what’s going to happen at SQLBits VI.

The event is free and your first beer is also free so there really is no reason to miss this.

Head to http://www.sqlsocial.com/Events/10-02-16/Evening_with_Itzik_Ben-Gan_Greg_Low_and_Davide_Mauri.aspx for registration.

Agenda

6:30 Welcome, network, eat, post your question topic
7:00 Small session on something interesting
7:15 Q&A panel with Itzik, Greg, Davide and Bill
8:30 just socialise

Address

Smithfield Tavern
105 Charterhouse Street
London
EC1M 6HR

Unfortunately I won’t be able to be there as I am working out in the Dam at the moment. However, I hope you all have fun.

Cheers, James

P.S. If your mind goes blank ask about enhancements to the OVER clause… Almost guaranteed to generate a comment from a certain someone :o).

PASS Nederland Chapter Meeting 17/11/2009

James.Rowland-Jones — Wed, 18 Nov 2009 07:38:56 GMT

For those of you who aren’t aware I am currently working out of the Netherlands. That’s one of the joys of being a consultant for a global organisation – you can literally end up anywhere! However, last night I had the please of speaking to the PASS Chapter here in the Netherlands http://www.sqlpass.nl/. I covered some of the Data Warehousing Features in SQL Server 2008. It was a lot of fun and great to speak to a completely new audience for me. Some people may have become wary of my presentation “style” after seeing me at SQLBits but here I had 30 or so willing victims who had no knowledge of me or my pop quizzes :o). If you are in the UK and would like to know more about these features I will presenting this session on Friday 20th November at the SQLBits 2008 & R2 day. It’s the last session of the day but what else are you going to do? Sit in traffic on the M4? Surely it’ll be less painful than that :o)…

I’d like to thank every one who came along and look forward to attending next months session. If I wasn’t greatly mistaken (my Dutch is still a bit basic to say the least) Marcel van der Holst will be presenting. Personally, I’d come over from the UK for that kind of session. Marcel is THE MAN and has helped me out with my book and also in understanding the curious case of the dubious deadlock and the not so logical lock.

A couple of things came up last night that people have asked me to post links to that I included in my presentation.

The excellent Data Loading Performance Guide - http://msdn.microsoft.com/en-us/library/dd425070.aspx
Stuart Ozer’s ManagePartition.exe - http://sqlpartitionmgmt.codeplex.com/

I will post the slides and the presentation up onto the SQLBits website once I’ve presented it this week...

Big thanks to André Kamman (not just another SQL DBA) for the invite and to Compu’Train for hosting the event.

See you all at SQLBits! I think Andrew Fryer still has a few tickets left so if you want to get to SQLBits on the Friday for FREE then send him a mail.

Cheers, James

@jrowlandjones

P.S. Are you interested in becoming a BI consultant for EMC Consulting? If the answer is YES then please feel free to Contact Me and let’s start that discussion…

SQLBits V : Friday Ticket Give-away!

James.Rowland-Jones — Mon, 16 Nov 2009 16:43:45 GMT

Andrew Fryer, Microsoft IT Pro evangelist extraordinaire, has 25 free tickets to give away for this Friday’s SQLBits SQL Server 2008 and R2 day! Head over to his blog here to find out how you can claim a ticket and bag yourself a great geeky day out. You are also most welcome to stay over and enjoy the community day on the Saturday although you will need to foot the bill for your own accommodation. There are limits you know :o).

Look forward to seeing you all there!

Cheers, James

@jrowlandjones

My new SQL 2008 Internals Book!!!

James.Rowland-Jones — Fri, 13 Nov 2009 11:15:10 GMT

I say my but really of course I mean “our” :o). It’s been a fantastic experience and I have got to know some great people and had a lot of fun, stress heartache and now joy. Big thanks to the team at Wrox, especially Ami and of course my fellow authors and contributors, Jonathan Kehayias and Cindy Gross.

Not much more to say than it’ll be available in all good bookshops in January but don’t let that stop you getting your pre-orders in now :).

So in the style of Murray Walker “Buy! Buy! Buy!”

Cheers, James

@jrowlandjones

Bob Muglia demonstrates 192 core SQL Server 2008 R2 – with record performance

James.Rowland-Jones — Tue, 03 Nov 2009 17:15:16 GMT

Today is the beginning of PASS 2009. I am currently sat in the key note and have just seen Bob Muglia demonstrate a SQL Server 2008 R2 running 192 cores.

I think that’s worth sharing. As I am sat next to Simon Sabin I thought I’d share this using pictures rather than bang on for pages of text (he hates it when I do that ).

However, if you want to see what can be done with a mere 96 cores why not check out Henk van der Valk’s awesome work on YouTube here. If you want to meet Henk and see 96 cores in action at SQLBits 2009. Henk works in the European Performance Center for Unisys.

Finally some new benchmarks announced today…

Check out the new SQL Server 2008 R2 record for TPC-E. Of interest to me is that R2 is not only the fastest 2012.77 as opposed to 1,568.22 but also at lower cost Price/tpsE (958.23 USD compared with 1180.01 USD).

Also of interest on the benchmarking front is the new SQL 2008 TPC-H benchmark. TPC-H is a data warehousing benchmark. SQL Server 2008 R2 has featured rather well in the price/performance for 10,000 GB results. Yes that is SQL Server in the no.1 slot :o). Whilst the QphH isn’t the same as some of the others it clearly demonstrates SQL Server scales way beyond common misconceptions. It is also delivering scale at a much keener TCO.

I think that’s quite enough for now. Simon is starting to hurt me (too many words).

Cheers, James

Get Mugged at #SQLPASS 09

James.Rowland-Jones — Tue, 03 Nov 2009 08:05:26 GMT

Tomorrow I am hosting a table at the Birds of a Feather lunch and am following this up with my stint in the Ask The Experts Area (Data Warehousing). Please feel free to come by and say hi. I know of two people now that have stumbled upon my blog so please don’t be shy. I rarely bite and the UK hasn’t had a case of rabies since November 2002. My aim is to get this up to double figures this week. By way of inducement, I’ll have plenty of SQL bucks to give out and also should have a stack of SQLBits mugs to distribute too. You won’t believe the effort we went to to get them at PASS this year. I am still walking a little awkwardly…

So if you fancy getting your hands on one these highly sought after collectors items then all you need to do is make sure you are

In Seattle
Attending #sqlpass 09
At the Birds of a feather lunch or
Lurking around the Ask the Experts area

Please don’t be shy. I’d rather not be sat there like Billy no mates if it’s all the same to you. Ok starting to sound a bit desperate so let’s have a look at what you could win…

I should point out that not all the mugs made it on one piece and so those that survived are now extremely collectable. Mugs will be offered on a first come first served basis – until I decide otherwise. One mug per mug/person. When they are gone they are gone. Caution contents of mug may be hot when filled with contents that are hot. Referee’s (my) decision is final. A SQLBits mug will however make you more attractive to the opposite sex (after all you now own a collectors item). Tests on improved levels of attractiveness to the same sex are on-going. Early tests are encouraging.

I look forward to meeting you. I have a lot of mugs.

Cheers, James

@jrowlandjones

MVP 2.0

James.Rowland-Jones — Fri, 02 Oct 2009 13:52:16 GMT

Yesterday I was delighted to find in my junk mail (for some reason it’s always there) that I have been renewed as an MVP for the first time. I’ll confess to have been quite nervous about whether this was going to be forthcoming or not; ultimately it’s all in the lap of the gods. Thankfully they favoured me and so it’s game on for another year.

Last year my project pals (one in particular) surpassed themselves with a beautifully photoshopped picture of me whilst others littered my desk with more “MVP” post-it notes than I care to remember. This year I rather suspect the injury to my pride will be completely self-inflicted. My MVP Lead recorded a video with me at the recent and very successful SQL Data Management Conference (#sqldmc for tweeps) on my MVP program experience. I rather suspect this will appear on Facebook very shortly. The MVP program has really embraced social media in recent times and can also be found on twitter (#mvp). I expect it’ll come across as rather gushing and so I will probably be watching it from behind a cushion cringing ever such a lot when its published.

That of course is to take nothing away from the MVP program which has been very good to me in the last year. The community spirit amongst fellow MVPs, especially in the SQL newsgroup, is just amazing. I just can’t stand to see myself “on telly” nor listen to my voice. It is at these times I realise what everyone else has to listen to when I am talking. I pity you all.

I’d like to thank fellow UK MVPs and SQLBits committee members Simon Sabin, Chris Webb, Allan Mitchell, Darren Green and Martin Bell for their continued support. My MVP lead for 2008/2009, Vicki, has been amazing and I’d like to thank her for all her efforts and sterling work. I’d also like to make a special mention to Christian Bolton (another UK MVP) for the opportunity to write some chapters in his upcoming book “Professional SQL Server 2008: Internals and Performance Tuning” where I am collaborating with Justin Langford, Brent Ozar, a new SQL MVP (congratulations Brent), Steven Wort and seasoned MVPs Jonathan Kehayias and Cindy Gross.

It’s been a great year and I look forward to seeing all 4 of you who read my blog at SQLBits in November. Roll on October 2010.

Cheers, James

@jrowlandjones

SQLBits V Mugshot Competition meets the Lying Down Game

James.Rowland-Jones — Thu, 27 Aug 2009 17:56:12 GMT

To help get everyone into the SQLBits spirit I’d like to remind everyone that we have started our own very cool competition – The Mugshot. Those of you who went to SQLBits IV will have received a kickass SQLBits mug that we hoped you proudly use for ever more. That very mug can now win you a free ticket and accommodation to the whole of SQLBits V!!! All you need to do is take a picture of you with your mug some place interesting and post it up onto the site. If your photo is selected as the overall winner then prizes a plenty are yours!

However, if you didn’t make it to SQLBits IV and therefore don’t have a genuine® SQLBits mug then have no fear. You can create your own! We have created the DIY mug. Print out the logo from here and affix it to your current substandard mug (I found one of my daughters hair-bands worked a treat) and off you go.

To help give you some inspiration I have posted up a picture. However, there are no prizes for guessing which one is me. I think my sartorial elegance gives the game away far too easily for that. As a committee member I have no chance of winning so please vote for other options.

Best of luck! Check out the other offerings and post your efforts here. Get creative - it could save you £££ (yes at least three pound signs).

Cheers, James

SQLBits V is open for Business – 19th – 21st November

James.Rowland-Jones — Wed, 26 Aug 2009 15:38:48 GMT

It's that time of year again! SQLBits registration is now open and this time it is going to be bigger and better than ever!

For the first time SQLBits will be held during the week and at the week-end as we have grown the event to span three days.

It’s also going to be held at the world famous Celtic Manor hotel – home to the 2010 Ryder Cup!

Day 1 – Training Day – Thursday 19th November

All day seminars from

Chris Webb – Introduction to MDX
Donald Farmer (yes the Donald Farmer) – Self Service Business Intelligence – making it real
Jasper Smith – Practical Performance Tuning and Monitoring for SQL Server
Niels Berglund - A Day of SQL Server 2008 for Developers
Simon Sabin – XML Master Class – Using XML with SQL Server

Day 2 – SQL 2008 & R2 Day – Friday 20th November

This is a new day for us and is specifically targeting the 2008 version of SQL Server. What it is, how you get there and what it will be in R2 are the themes for the day. Donald will also be giving a key note presentation and we will also have Thomas Kejser from the SQLCAT team present. Thomas has written some great papers and his presentation at PASS 2008 on breaking the ETL world record was the highlight of the event for me. Definitely not one to be missed.

The full agenda for this day hasn’t been concluded but expect topics from the list below to feature.

Business Intelligence:
- Gemini and self-service BI
- New functionality in Reporting Services for SQL Server 2008 and R2
- Master Data Services
- Project Madison
DBA
- Multi-server management
- Data-tier application control
- Enhancements in the MDW
- Policy-based management
- Compression and Encryption
- Mirroring enhancements, Compressible log stream and page level repair
- Scalability – Going to 256 cores
- Handling large data sets with partitioning management and query improvements
- Improve large data operations with new bulk logged operations
Developer
- Enabling integration with 3rd party systems with Change Data Capture
- Managing replication to 100s of devices using Change tracking and Sync Services
- Spatial Data, Integrated Full text search and XML data
- New data types and programming improvements

Day 3 – Community Day – Saturday 21st November

This is SQLBits “old skool”. If you have been to SQLBits before this will be very very familiar to you. We are still accepting sessions for this. If you fancy submitting a session then please go right ahead. SQLBits is all about providing new opportunities to aspiring speakers and we are always on the look out for new talent. However I won’t be able to promise any smug nipple rubbing at this talent show :o).

Interested?

Why not seize the moment and register yourself here. Spaces are capped and it is first come first served.

If you have any questions about the event or if you think your company would be interested in sponsoring the event then please get in touch with me.

Look forward to seeing you there!

Cheers, James

How To Tell: If connections to SQL Server are pooled (or not)

James.Rowland-Jones — Sat, 25 Jul 2009 20:33:37 GMT

Have you ever wondered if the applications connecting to your SQL Servers are using pooled connections or not? If the answer is yes then read on. However, if the answer is no then you should! Opening and closing connections is an expensive process. When applications don’t use a connection pool then each request needs to establish its own connection before the query can be executed. It then has to close it. A pooled connection is one which is kept open by an application for other requests to re-use.

The question therefore remains how do you actually find out if an application is using connection pooling or not? Well SQL Trace can tell you. If you execute the following statement you will see exactly which category/event and column you need to select to get at this information.

SELECT  cat.name            AS category_name
        ,evt.name           AS event_name
        ,col.name           AS column_name
        ,sub.subclass_name 
FROM    sys.trace_subclass_values sub
JOIN    sys.trace_columns col           ON  sub.trace_column_id = col.trace_column_id
JOIN    sys.trace_events evt            ON  sub.trace_event_id = evt.trace_event_id
JOIN    sys.trace_categories cat        ON  cat.category_id = evt.category_id
WHERE   sub.subclass_name like '%pool%'

Clearly then we need to open up the Security Audit category and select the audit Login event. To get at this information we must ensure to pick the EventSubClass column. If we were using Profiler to build this trace it would look something like this

However, being a good tracer I’d actually want to set my trace up server side and drop the results to a file. To do this I’d export the definition I have just created using File | Export | Script Trace Definition | For SQL Server 2005 – 2008…

I’d then have a script that looked something like this:

/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 25/07/2009 20:52:50 */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 23, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 64, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 21, @on --EventSubClass
exec sp_trace_setevent @TraceID, 14, 41, @on
exec sp_trace_setevent @TraceID, 14, 49, @on
exec sp_trace_setevent @TraceID, 14, 57, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 66, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 51, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 60, @on
exec sp_trace_setevent @TraceID, 20, 7, @on
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 31, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 21, @on --EventSubClass
exec sp_trace_setevent @TraceID, 20, 49, @on
exec sp_trace_setevent @TraceID, 20, 57, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 26, @on
exec sp_trace_setevent @TraceID, 20, 30, @on
exec sp_trace_setevent @TraceID, 20, 3, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 35, @on
exec sp_trace_setevent @TraceID, 20, 51, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 60, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec
sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 180a71e3-2916-4eb5-b5cf-cb625d702f39'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

Note you still have to make some changes to this definition Notably you might want to make the file roll over and you’ll definitely want to change the filename. However, to all intents and purposes you are now good to go and your trace file will generate you something like this:

Hopefully that’s given you enough of a taster to look into this yourself. If you want to know more you might be unsurprised to know that I am writing the SQL Trace chapter for this new SQL Internals book I am working on. I got quite excited the other day as it appeared on www.amazon.co.uk for the first time. My name isn’t up in lights just yet (I think they need to do another refresh from the publishers as I joined the project late) however it is very exciting.

Till the next time,

James

When Books Online Fails… Try the US Patent Office!

James.Rowland-Jones — Sun, 19 Jul 2009 17:30:10 GMT

As those of you who managed to get to the bottom of my recent deadlocking post may have seen I am busy writing a book with Christian Bolton, Justin Langford, Brent Ozar, Steve Wort, Cindy Gross and Jonathan Kehayias. Right now I am working on the SQL Trace and Profiler chapter and am currently describing the catalog views available for SQL Trace. Most of these are reasonably straight forward but there were a couple of columns on sys.traces_columns that piqued my interest.

I was looking at Books Online for some inspiration as I wasn’t sure what the is_repeatable and is_repeated_base columns meant. You can view the article for yourself here http://msdn.microsoft.com/en-us/library/ms180067.aspx. Unfortunately I didn’t really get an answer from BOL. The columns and their BOL descriptions can be found below.

Column_name	Data Type	Description
is_repeatable	bit	Indicates whether the column can be referenced in the “repeated column” data
is_repeated_base	bit	Indicates whether this column is used as a unique key for referencing repeated data

As I say, I didn’t find myself any closer to knowing what these columns indicated. I thought I’d have a look at the data to see what the columns had these bit values set to see if that shed any light on proceedings.

SELECT  trace_column_id
        ,name
        ,type_name
        ,max_size
        ,is_filterable
        ,is_repeatable
        ,is_repeated_base 
FROM    sys.trace_columns
WHERE   is_repeatable = 1
OR      is_repeated_base = 1

Returned

Hmm nothing on the face of it. Nothing that described what was meant by “repeated data”. Time to Bing! it. Ta Da! Whaddya know the answer lies at the US Patent Office. My search uncovered this article http://www.patents.com/System-monitoring-performance-a-server/US7155641/en-US/. It’s the patent assignment to Microsoft for SQL Trace. Actually, it’s a great read and I thoroughly recommend you download the pdf so you get to see the drawings in conjunction with the available text. However, for the purposes of “repeated data” the bits you want to read are these.

The "trace repeated data" column indicates a desired repetition of data from a preceding event. The "trace repeated data" column is preferably followed by a range of column identifiers in which to insert the repeated data. Such repetition is available only for columns specifically defined as repeatable columns.

…

Repeatable element 630 provides an indication of whether column 610 is repeatable. Server 220 desirably need not resend data corresponding to a repeatable column in a subsequent trace. If a repeatable column is selected to be repeated, then it is indicated in the "trace repeated data" trace special column set forth above.

So there we have it. The metadata for repeatable columns has been exposed in the sys.traces_columns catalog view. It means that the columns marked as is_repeatable can derive their values from earlier events with the same SPID. I can say this because SPID is the only column to have the is_repeated_base flag set to true. If we look at the columns that are marked as is_repeatable this all begins to make sense. The data we’d expect to see in repeatable columns are static in nature and so it makes sense to try and optimise the trace by deriving this event info from a previous event rather than re-send this additional column data.

What does this all mean? Well, to me it means that we should be able to add these columns to our trace without fear of it causing performance degradation. That’s a theory of course. However, now I am going to see how I can work this into my chapter….

Cheers, James

Calculating the ROI of DRY SQL vs FLY SQL

James.Rowland-Jones — Wed, 15 Jul 2009 15:53:00 GMT

My colleague Jamie Thomson posted an intriguing article on DRY SQL which you can read all about here. It raised a number of questions for me so I thought I’d post my own response to it.

Here is a list of some of these questions that sprung to my mind.

Did the code change come up with the same query plan?
Were the tables indexed in the same way? Do both have an index on asset class? Does it cover the query? (You’ll see why I ask below)
Had the performance changed in anyway (for the better or worse)?
Had the compilation time gone up or down?

I hope you can get the general drift of my thoughts. Ostensibly they could be boiled down to two things.

Was this really a re-write rather than a re-factoring?
Was it worth it?

Jamie’s illustration was an example based on some work he was doing for a client. Therefore he wasn’t really in a position to really answer all of these questions. I therefore came up with my own AdventureWorks based example which I’d like to take you through.

Before starting to cut any code I thought I’d set up my own hypothesis by answering the my own questions

Yes I am now performing one sort and one aggregation instead of two of each.
Have no idea – possibly both were indexed on asset class but maybe not covered
Depends. Two separate sorts for the group by might be better than a single sort of one big dataset. On the other hand one operation for smaller volumes might be better.
Might have gone down (SQL more easily to shred/understand to come up with the good enough plan)

Scenario

This is the code I used to create the scenario. Before each run I cleaned out the cache and buffers and issued a check point for any dirty pages.

USE AdventureWorks;

SET STATISTICS TIME ON;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
CHECKPOINT;

/* WET SQL */
SELECT   LastName
        ,FirstName
        ,SUM(ContactID)
FROM    Person.Contact
WHERE   LastName = 'Zhou'
GROUP BY LastName
        ,FirstName
UNION ALL 
SELECT   LastName
        ,FirstName
        ,SUM(ContactID)
FROM    Person.Contact
WHERE   LastName = 'Edwards'
GROUP BY LastName
        ,FirstName;

DBCC FREEPROCCACHE ;
DBCC DROPCLEANBUFFERS;
CHECKPOINT;

/* DRY SQL */
SELECT   LastName
        ,Firstname
        ,SUM(ContactID)
FROM
(   SELECT   LastName
            ,FirstName
            ,ContactID
    FROM    Person.Contact
    WHERE   LastName = 'Zhou'
    UNION ALL
    SELECT   LastName
            ,FirstName
            ,ContactID
    FROM    Person.Contact
    WHERE   LastName = 'Edwards'
) a
GROUP BY LastName
        ,FirstName;

/*

CREATE NONCLUSTERED INDEX JRJ_NNCI_Contact_01

ON Person.Contact(LastName);

CREATE NONCLUSTERED INDEX JRJ_NNCI_Contact_02

ON Person.Contact(LastName,FirstName);

DROP INDEX Person.Contact.JRJ_NNCI_Contact_01;
DROP INDEX Person.Contact.JRJ_NNCI_Contact_02;

*/

Run 1

Let’s just run that sql as is to answer a fundamental question. Is this a refactoring or is this a material change and therefore a re-write?

To me straight away two things are very obvious.

The plans are different
The costs are nigh on identical

Why are the plans different? Well because in the first instance (WET SQL) we asked them to be. We asked sql server to do the group by and then bring the results together. Now you might see something like this on a distributed platform where the contact table was distributed. An example might be on Madison when it comes out. Processing the Group BY locally would work MUCH better than pulling all the data together and then issuing the group by.

So is this refactoring? That depends on your definition of the word. If your definition is based on whether the inputs and the output are the same only the guts have changed then yes this is a re-factoring. However, if your definition of re-factoring is whether you are making the same request but formatted differently for maintainability then no this is a re-write. Fundamentally this is a different request. The SQL might now be DRY and it does return the same data based on the same inputs but it is achieving this in a very different way. Whether 1 is better than 2 will depend on data volumes amongst other things.

Irrespective of the change however, the performance is pretty much bang on the same. At this level the ROI of making the SQL DRY doesn’t look like it’s paying off. So rather than looking at making the SQL DRY how about making the SQL FLY?

As our baseline the subtree and compile costs of the WET SQL and the DRY SQL were as follows

	WET SQL	DRY SQL
Sub tree cost	1.76	1.75
Compile Time (CPU/Elapsed) ms	16/63	7/7

As we saw the Sub tree costs are nigh-on identical but the compile time is reduced. Tick in the box for the hypothesis step 4.

Let’s look at how investing in indexing might make more of a difference.

Run 2 - Index on Last Name only

Let’s add an index and see what happens

CREATE NONCLUSTERED INDEX JRJ_NNCI_Contact_01 ON Person.Contact(LastName);

This index won’t cover the query but it will help it allowing SQL Server to cut into the data more effectively:

The index above has been added to AdventureWorks and the plan above has clearly picked it up. The difference between the two is now at least noticeable (but not much) in favour of the DRY SQL now but we are doing a lookup of the first name. However, what has happened to the actual performance?

	WET SQL	DRY SQL
Sub tree cost	0.503	0.492
Compile Time (CPU/Elapsed) ms	0/40	0/8

A significant drop in both sub tree costs and a drop in compile time for the WET SQL! However, we can take this further by covering the query.

Run 3 – Covered Index

New index this time covering the query by including first name as well as last name.

CREATE NONCLUSTERED INDEX JRJ_NNCI_Contact_02 ON Person.Contact(LastName,FirstName);

Wow! Look at that SQL Server has now chosen the same plan for both queries. By any definition of re-factoring the DRY SQL hasn’t altered the query. The Covering Index has forced order on the data so the group by is no longer necessary. All that is left is to aggregate the data.

Notice DRY vs WET is not making a heap of difference to performance again with both Query costs weighing in at 50%. However, the lookups have been eliminated. Methinks a performance optimisation is in the offing….

	WET SQL	DRY SQL
Sub tree cost	0.0069	0.0069
Compile Time (CPU/Elapsed) ms	14/62	16/68

There we have it a truly massive improvement in performance! 0.0069 vs an original baseline of ~1.76. However, notice that the DRY SQL compile time has now shot up! I ran this query several times just to be sure and every time the compile time was over 60ms. SQL Server was definitely taking longer to find this plan.

Conclusion

No one can under-estimate the importance of manageable, maintainable code in production. Pages of SQL obscured by layer upon layer of view definitions is horrendous to have to unpick when there is an issue. However, investing time in making SQL DRY may not give you any real performance benefit. In fact in so doing you might alter the way in which SQL Server, or any RDBMS for that matter, interprets your request. This might give you a performance improvement but it might also have little or no impact whatsoever. Just remember that it could and it could also make things worse. The ROI of attempting to make your SQL DRY will therefore heavily depend on how maintainable the code was in the first place.

However, a little look at making your SQL FLY with a spot of index tuning might be just the ticket! The ROI in the examples above are pretty clear to me…

Cheers, James