SSMS hanging without error when connecting to SQL

Scary day for me last Thursday.

I had gone up to Brisbane, and was due to speak at the Queensland SQL User Group on Thursday night. Unfortunately, disaster struck about an hour beforehand.

Nothing to do with the recent floods (although we were meeting in a different location because of them). It was actually down to the fact that I’d been fiddling with my machine to get Virtual Server running on Windows 7, and SQL had finally picked up a setting from then.

I could run Management Studio, but it couldn’t connect at all. No error, it just seemed to hang.

One of the things you have to do to get Virtual Server installed is to tweak the Group Policy settings. I’d used gpupdate /force to get Windows to pick up the new setting, which allowed me to get Virtual Server running properly, but at the time, SQL was still using the previous settings. Finally when in Brisbane, my machine picked up the new settings, and caused me pain.

Dan Benediktson describes the situation. If the SQL client picks up the wrong value out of the GetOverlappedResult API (which is required for various changes in Windows 7 behaviour), then Virtual Server can be installed, but SQL Server won’t allow connections. Yay. Luckily, it’s easy enough to change back using the Group Policy editor (gpedit.msc). Then restarting the machine (again!, as gpupdate /force didn’t cut it either, because SQL had already picked up the value), and finally I could reconnect.

On Thursday I simply borrowed another machine for my talk. Today, one of my guys had seen and remembered Dan’s post. Thanks, both of you.

24 Hours of PASS scheduling

I have a new appreciation for Tom LaRock (@sqlrockstar), who is doing a tremendous job leading the organising committee for the 24 Hours of PASS event (Twitter: #24hop).

We’ve just been going through the list of speakers and their preferences for time slots, and hopefully we’ve kept everyone fairly happy.

All the submitted sessions (59 of them) were put up for a vote, and over a thousand of you picking your favourites. The top 28 sessions as voted were all included (24 sessions plus 4 reserves), and duplicates (when a single presenter had two sessions in the top 28) were swapped out for others. For example, both sessions submitted by Cindy Gross were in the top 28. These swaps were chosen by the committee to get a good balance of topics.

Amazingly, some big names missed out, and even the top ten included some surprises. T-SQL, Indexes and Reporting featured well in the top ten, and in the end, the mix between BI, Dev and DBA ended up quite nicely too.

The ten most voted-for sessions were (in order):

Jennifer McCown – T-SQL Code Sins: The Worst Things We Do to Code and Why
Michelle Ufford – Index Internals for Mere Mortals
Audrey Hammonds – T-SQL Awesomeness: 3 Ways to Write Cool SQL
Cindy Gross – SQL Server Performance Tools
Jes Borland – Reporting Services 201: the Next Level
Isabel de la Barra – SQL Server Performance
Karen Lopez – Five Physical Database Design Blunders and How to Avoid Them
Julie Smith – Cool Tricks to Pull From Your SSIS Hat
Kim Tessereau – Indexes and Execution Plans
Jen Stirrup – Dashboards Design and Practice using SSRS

I think you’ll all agree this is shaping up to be an excellent event.

On technical talent

In honour of the regular T-SQL Tuesday blogging, the UnSQL theme started, looking at topics that were not directly SQL related, but nevertheless quite interesting. This is the brainchild of Jen McCown, who posted the second of these recently.

I’m actually a bit late in responding, as I haven’t got it in my head to look for these posts yet. Still, Jen says I can still contribute now, hence this post.

The theme this time is on Tech Giants.

I could list people all day for those I admire in the SQL Server space, and go on even longer if I branch out to other areas. But I actually want to highlight four guys that I admire so much for their skills, integrity and general awesomeness that I hired them.

Yes – the guys that work for me at LobsterPot Solutions, being Ben McNamara, David Gardiner, Roger Noble and Ashley Sewell. I admire them all, and they present the company with a platform on which to grow.

When a problem is resolved

This month’s T-SQL Tuesday is hosted by Jen McCown, and she’s picked the topic of Resolutions. It’s a new year, and she’s thinking about what people have resolved to do this year. Unfortunately, I’ve never really done resolutions like that. I see too many people resolve to quit smoking, or lose weight, or whatever, and fail miserably. I’m not saying I don’t set goals, but it’s not a thing for New Year.T-SQL Tuesday

The obvious joke is “1920×1080” as a resolution, but I’m not going there.

I think Resolving is a strange word. It makes it sound like I’m having to solve a problem a second time, when actually, it’s more along the lines of solving a problem well enough for it to count as finished. If something has been resolved, a solution has been provided. There is a resolution, through the provision of a solution. It’s a strangeness of English. When I look up the word resolution at, it has 12 options, including “settling of a problem”. There’s a finality about resolution. If you resolve to do something, you’re saying “Yes. This is a done thing. I’m resolving to do it, which means that it may as well be complete already.”

I like to think I resolve problems, rather than just solving them. I want my solving to be final and complete. If I tune a query, I don’t want to find that I’m back in there, re-tuning it at some point. Strangely, if I re-solve a problem, that implies that I didn’t resolve it in the first place. I only solved it. Temporarily.

We “data-folk” live in a world where the most common answer is “It depends.” Frustratingly, the thing an answer depends on may still be changing in the system in question. That probably means that any solution that is put in place may need reinvestigating at some point later.

So do I resolve things? Yes.

Am I Chuck Norris, and solve things so well the world would break first? No.

Do these two claims happily sit beside each other? No, unfortunately not. But I happily take responsibility for things, and let my clients depend on me to see it through. As far as they are concerned, it is resolved.

And so I resolve to keep resolving, right through 2011.

Visually stunning maps and PivotViewer

One of the things about PivotViewer is that it runs in the Silverlight platform and can be extended recently. One of my guys at LobsterPot, Roger Noble, has used this to incorporate a Bing Maps layer, showing items which have  Latitude and Longitude values there. We’re already talking to a hospital about using this to allow them to browse their patient data, including showing the patients on a map according to which bed they’re in. Interesting times – this will involve having custom tiles instead of the ones from Bing Maps, but the idea is similar. Of course, we’ll be using Bing Maps to show where the patients live.

I should also mention that this is a work-in-progress still. Figuring out how to use PivotViewer isn’t trivial, and we’ve done quite a lot of experimenting to see how to get things working. If you find bugs, please feel free to let me know (rob_farley at hotmail will usually reach me), and we’ll add them to our list.

Here are some screenshots that I made recently using the collection at – by selecting a tag, you can get a new bunch of images.

A couple of images that were taken in Iceland.


Some from St Mary’s Lighthouse near Newcastle, UK.


And some from around Big Ben in London.


I’d recommend using either Firefox or Internet Explorer if you choose to browse this yourself. It seems the Chrome browser support for Silverlight doesn’t quite handle things as nicely as we’d all like.

I imagine that at some point, we may enhance the Flickr collection, to be able to search on more than tags, but as a sample collection, it seems to work quite well.

Using SQL Execution Plans to discover the Swedish alphabet

SQL Server is quite remarkable in a bunch of ways. In this post, I’m using the way that the Query Optimizer handles LIKE to keep it SARGable, the Execution Plans that result, Collations, and PowerShell to come up with the Swedish alphabet.

SARGability is the ability to seek for items in an index according to a particular set of criteria. If you don’t have SARGability in play, you need to scan the whole index (or table if you don’t have an index). For example, I can find myself in the phonebook easily, because it’s sorted by LastName and I can find Farley in there by moving to the Fs, and so on. I can’t find everyone in my suburb easily, because the phonebook isn’t sorted that way. I can’t even find people who have six letters in their last name, because also the book is sorted by LastName, it’s not sorted by LEN(LastName). This is all stuff I’ve looked at before, including in the talk I gave at SQLBits in October 2010.

If I try to find everyone who’s names start with F, I can do that using a query a bit like:


SELECT LastName FROM dbo.PhoneBook WHERE LEFT(LastName,1) = 'F';

Unfortunately, the Query Optimizer doesn’t realise that all the entries that satisfy LEFT(LastName,1) = 'F' will be together, and it has to scan the whole table to find them.


But if I write:


SELECT LastName FROM dbo.PhoneBook WHERE LastName LIKE 'F%';</EM>

then SQL is smart enough to understand this, and performs an Index Seek instead.


To see why, I look further into the plan, in particular, the properties of the Index Seek operator. The ToolTip shows me what I’m after:


You’ll see that it does a Seek to find any entries that are at least F, but not yet G. There’s an extra Predicate in there (a Residual Predicate if you like), which checks that each LastName is really LIKE F% – I suppose it doesn’t consider that the Seek Predicate is quite enough – but most of the benefit is seen by its working out the Seek Predicate, filtering to just the “at least F but not yet G” section of the data.

This got me curious though, particularly about where the G comes from, and whether I could leverage it to create the Swedish alphabet.

I know that in the Swedish language, there are three extra letters that appear at the end of the alphabet. One of them is ä that appears in the word Västerås. It turns out that Västerås is quite hard to find in an index when you’re looking it up in a Swedish map. I talked about this briefly in my five-minute talk on Collation from SQLPASS (the one which was slightly less than serious).

So by looking at the plan, I can work out what the next letter is in the alphabet of the collation used by the column. In other words, if my alphabet were Swedish, I’d be able to tell what the next letter after F is – just in case it’s not G.

It turns out it is… Yes, the Swedish letter after F is G. But I worked this out by using a copy of my PhoneBook table that used the Finnish_Swedish_CI_AI collation. I couldn’t find how the Query Optimizer calculates the G, and my friend Paul White (@SQL_Kiwi) tells me that it’s frustratingly internal to the QO. He’s particularly smart, even if he is from New Zealand.

To investigate further, I decided to do some PowerShell, leveraging the Get-SqlPlan function that I blogged about recently (make sure you also have the SqlServerCmdletSnapin100 snap-in added. To do that, run get-pssnapin -reg to see if it's on the machine, and get-pssnapin -reg | add-pssnapin to make the cmdlet available).

I started by indicating that I was going to use Finnish_Swedish_CI_AI as my collation of choice, and that I’d start whichever letter came straight after the number 9. I figure that this is a cheat’s way of guessing the first letter of the alphabet (but it doesn’t actually work in Unicode – luckily I’m using varchar not nvarchar. Actually, there are a few aspects of this code that only work using ASCII, so apologies if you were wanting to apply it to Greek, Japanese, etc). I also initialised my $alphabet variable.


$collation = 'Finnish_Swedish_CI_AI'; 
$firstletter = '9'; 
$alphabet = '';

Now I created the table for my test. A single field would do, and putting a Clustered Index on it would suffice for the Seeks. [Edit: You may need to insert some rows if you find that it's producing an Index Scan. Theoretically it should prefer to Scan, knowing there's no rows, but my tests showed it would always Seek]


Invoke-Sqlcmd -server . -data tempdb -query "create table dbo.collation_test (col varchar(10) collate $collation primary key);"

Now I get into the looping.


$c = $firstletter; 
$stillgoing = $true; 
while ($stillgoing) 

I construct the query I want, seeking for entries which start with whatever $c has reached, and get the plan for it:


$query = "select col from dbo.collation_test where col like '$($c)%';"; 
 $pl = get-sqlplan $query "." "tempdb";

At this point, my $pl variable is a scary piece of XML, representing the execution plan. A bit of hunting through it showed me that the EndRange element contained what I was after, and that if it contained NULL, then I was done.


$stillgoing = ($pl.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan.RelOp.IndexScan.SeekPredicates.SeekPredicateNew.SeekKeys.EndRange -ne $null);

Now I could grab the value out of it (which came with apostrophes that needed stripping), and append that to my $alphabet variable.


if ($stillgoing) 
    $alphabet += $c; 

Finally, finishing the loop, dropping the table, and showing my alphabet!


Invoke-Sqlcmd -server . -data tempdb -query "drop table dbo.collation_test;"; 

When I run all this, I see that the Swedish alphabet is ABCDEFGHIJKLMNOPQRSTUVXYZÅÄÖ, which matches what I see at Wikipedia. Interesting to see that the letters on the end are still there, even with Case Insensitivity. Turns out they’re not just “letters with accents”, they’re letters in their own right.


I’m sure you gave up reading long ago, and really aren’t that grabbed about the idea of doing this using PowerShell. I chose PowerShell because I’d already come up with an easy way of grabbing the estimated plan for a query, and PowerShell does allow for easy navigation of XML.

I find the most interesting aspect of this as the fact that the Query Optimizer uses the next letter of the alphabet to maintain the SARGability of LIKE. I’m hoping they do something similar for a whole bunch of operations.

Oh, and the fact that you know how to find stuff in the IKEA catalogue.


If you are interested in whether this works in other languages, you might want to consider the following screenshot, which shows that in principle, it should work with Japanese. It might be a bit harder to run this in PowerShell though, as I’m not sure how it translates. In Hiragana, the Japanese alphabet starts あ, ぃ, ぅ, ぇ, ぉ, …