Search results matching tags 'SQLBits' and 'sql'

New PASS Summit speakers that deserve votes

rob_farley — Thu, 19 May 2011 03:35:25 GMT

I’m not going to ask you to vote for the abstracts that I submitted for the PASS Summit. I’m not even going to mention the abstracts that Roger and Ashley submitted. I figure if you’re reading this, you may have already read the post I wrote about them, back before SQLPASS had said that there’d be voting.

Instead, I’m going to pick a few people that I recommend you vote for.

The people I’m going to pick are Paul White, Chris Testa-O’Neill and Erin Stellato. Three very different people, even from different countries. But I don’t think any of them have spoken at the PASS Summit before, and it would be good if they got accepted.

Paul White (NZ) is a fellow blogger at sqlblog.com. If you haven’t read his stuff, you’re seriously missing out. He spends an inordinate amount of time researching the behaviour of SQL Server, and has discovered all kinds of amazing things. He recently gave a presentation at the Boston SQL Saturday, and there was a lot of good feedback about it.

Chris Testa-O’Neill (UK) is one of the organisers of the SQLBits conferences in the UK, and has also appeared on many of the eLearning training for Microsoft. He hails from Manchester but has family living in Adelaide. He visited over Christmas and did an excellent job of speaking at the Adelaide SQL Server User Group.

Erin Stellato (US) blogs at erinstellato.com (I guess that makes sense). I met her briefly at the last PASS Summit, but over recent months have had a few conversations with her about some of the things she writes about on her blog. She submitted an abstract for 24 Hours of PASS event, but didn’t manage to get enough votes to speak. Despite this, I know she knows her stuff, and would do an excellent job presenting.

So there’s three people that I recommend you vote for. You can easily see the abstracts they’ve submitted on the site, to help in your decision. There are plenty of other people you should vote for too, such as the Scottish Jen Stirrup, the Aussie Darren Gosbell, or Seattle’s own Kendra Little (I think they haven’t spoken at the PASS Summit before either) – but please have a look through the names and pick some that are not the usual suspects.

@rob_farley

Using SQL Execution Plans to discover the Swedish alphabet

rob_farley — Mon, 03 Jan 2011 05:54:00 GMT

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%';

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)%';";
[xml] $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)
{
$c=$pl.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan.RelOp.IndexScan.SeekPredicates.SeekPredicateNew.SeekKeys.EndRange.RangeExpressions.ScalarOperator.ScalarString.Replace("'","");
$alphabet += $c;
}

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

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

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.

Footnote:

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 あ, ぃ, ぅ, ぇ, ぉ, ...

Visualising data a different way with Pivot collections

rob_farley — Wed, 29 Dec 2010 02:50:00 GMT

Roger’s been doing a great job extending PivotViewer recently, and you can find the list of LobsterPot pivots at http://pivot.lobsterpot.com.au

Many months back, the TED Talk that Gary Flake did about Pivot caught my imagination, and I did some research into it. At the time, most of what we did with Pivot was geared towards what we could do for clients, including making Pivot collections based on students at a school, and using it to browse PDF invoices by their various properties. We had actual commercial work based on Pivot collections back then, and it was all kinds of fun. Later, we made some collections for events that were happening, and even got featured in the TechEd Australia keynote.

But I’m getting ahead of myself... let me explain the concept.

A Pivot collection is an XML file (with .cxml extension) which lists Items, each linking to an image that’s stored in a Deep Zoom format (this means that it contains tiles like Bing Maps, so that the browser can request only the ones of interest according to the zoom level). This collection can be shown in a Silverlight application that uses the PivotViewer control, or in the Pivot Browser that’s available from getpivot.com. Filtering and sorting the items according to their facets (attributes, such as size, age, category, etc), the PivotViewer rearranges the way that these are shown in a very dynamic way. To quote Gary Flake, this lets us “see patterns which are otherwise hidden”.

This browsing mechanism is very suited to a number of different methods, because it’s just that – browsing. It’s not searching, it’s more akin to window-shopping than doing an internet search.

When we decided to put something together for the conferences such as TechEd Australia 2010 and the PASS Summit 2010, we did some screen-scraping to provide a different view of data that was already available online. Nick Hodge and Michael Kordahi from Microsoft liked the idea a lot, and after a bit of tweaking, we produced one that Michael used in the TechEd Australia keynote to show the variety of talks on offer.

It’s interesting to see a pattern in this data: The Office track has the most sessions, but if the Interactive Sessions and Instructor-Led Labs are removed, it drops down to only the sixth most popular track, with Cloud Computing taking over.

This is something which just isn’t obvious when you look an ordinary search tool. You get a much better feel for the data when moving around it like this.

The more observant amongst you will have noticed some difference in the collection that Michael is demonstrating in the picture above with the screenshots I’ve shown. That’s because it’s been extended some more.

At the SQLBits conference in the UK this year, I had some interesting discussions with the guys from Xpert360, particularly Phil Carter, who I’d met in 2009 at an earlier SQLBits conference. They had got around to producing a Pivot collection based on the SQLBits data, which we had been planning to do but ran out of time. We discussed some of ways that Pivot could be used, including the ways that my old friend Howard Dierking had extended it for the MSDN Magazine. I’m not suggesting I influenced Xpert360 at all, but they certainly inspired us with some of their posts on the matter

So with LobsterPot guys David Gardiner and Roger Noble both having dabbled in Pivot collections (and Dave doing some for clients), I set Roger to work on extending it some more. He’s used various events and so on to be able to make an environment that allows us to do quick deployment of new collections, as well as showing the data in a grid view which behaves as if it were simply a third view of the data (the other two being the array of images and the ‘histogram’ view).

I see PivotViewer as being a significant step in data visualisation – so much so that I feature it when I deliver talks on Spatial Data Visualisation methods. Any time when there is information that can be conveyed through an image, you have to ask yourself how best to show that image, and whether that image is the focal point. For Spatial data, the image is most often a map, and the map becomes the central mode for navigation. I show Pivot with postcode areas, since I can browse the postcodes based on their data, and many of the images are recognisable (to locals of South Australia).

Naturally, the images could link through to the map itself, and so on, but generally people think of Spatial data in terms of navigating a map, which doesn’t always gel with the information you’re trying to extract. Roger’s even looking into ways to hook PivotViewer into the Bing Maps API, in a similar way to the Deep Earth project, displaying different levels of map detail according to how ‘zoomed in’ the images are.

Some of the work that Dave did with one of the schools was generating the Deep Zoom tiles “on the fly”, based on images stored in a database, and Roger has produced a collection which uses images from flickr, that lets you move from one search term to another. Pulling the images down from flickr.com isn’t particularly ideal from a performance aspect, and flickr doesn’t store images in a small-enough format to really lend itself to this use, but you might agree that it’s an interesting concept which compares nicely to using Maps.

I’m looking forward to future versions of the PivotViewer control, and hope they provide many more events that can be used, and even more hooks into it.

Naturally, LobsterPot could help provide your business with a PivotViewer experience, but you can probably do a lot of it yourself too. There’s a thorough guide at getpivot.com, which is how we got into it. For some examples of what we’ve done, have a look at http://pivot.lobsterpot.com.au. I’d like to see PivotViewer really catch on a data visualisation tool.

SARGability isn’t about the left-hand side

rob_farley — Tue, 12 Oct 2010 22:10:12 GMT

This month’s T-SQL Tuesday has me caught slightly unawares. I’m in the UK, having come over for SQLBits, and I’m left writing this with only a few hours to go.

The theme this time is on misconceptions in SQL, and this fits nicely with SARGability – one of the topics that I presented on at SQLBits a week and a half ago. I often see people refer to SARGability as making sure that the left-hand side of the predicate is untouched. In other words, suggesting that something like this would be fine:

WHERE someColumn = someFunction(someValue)

whereas something like this would not:

WHERE someOtherFunction(someColumn) = someValue

As with most misconceptions, there’s an element of truth to this, but it definitely doesn’t tell the whole story.

If you have an index on someColumn, then great – the first predicate should let you find the value you want in that index nicely. Then you have other questions about whether the index is suitable or not, but that’s for a different post. On the other hand, if you don’t have an index, then the system is going to scan the table (or at least some less suitable index) in both scenarios. Applying the function to each column is painful in the second one, but SARGability is about producing seeks instead of scans, and these would both cause scans.

But how often do you write predicates like this. Normally, we have something more like:

ON x.someColum = y.someOtherColumn

…and this naturally causes a question. Do we want to be looking up the values in x, or in y? What needs to be SARGable here, the left or the right? It’s very easy to assume the left, but is it really going to be best for you? SQL Server doesn’t care which way around you’ve written your predicate, so don’t hold onto the ‘left-hand side’ idea longer than the end of this post. In fact, think about the way you want your query to be executed, and ask yourself where your SARGability should actually be looking.

It’s all a matter of perspective…

24 Hours of PASS – sargability resources

rob_farley — Fri, 17 Sep 2010 00:33:42 GMT

I just did a talk for 24 Hours of PASS on Sargability. I’ve done this talk before (about five months ago at the user group I run and at the PASS AppDev Virtual Chapter).

There are a number of useful resources out there about sargability. I’ve blogged about it before, Brent Ozar has blogged about it, and so on. One thing that I don’t believe has been mentioned much is the use of inverse predicates in a view to hope that one of the sides is treated as a Seek. You can see that being handled in my script, and I’ll try to write a blog post about it some time during October.

I’m also giving this talk on Friday Oct 1 at SQLBits in York, UK. I hope you can come to this – the line-up of speakers is just amazing this time.

Anyway, the script I used is now in a SkyDrive folder at http://cid-32a3bd12bf98b682.office.live.com/browse.aspx/24HOP%20-%20Sargability

And some other useful links that I showed during my talk are listed below

Aaron's blog post with the great picture:
http://sqlvariant.com/wordpress/index.php/2010/09/1433/

Erin's blog post about SQL not being able to add indexes on expressions:
http://erinstellato.com/index.php/component/content/article/1-latest-news/99-desperately-seeking-seeks

Connect items of interest:
https://connect.microsoft.com/SQLServer/feedback/details/566418/indexes-on-expressions
https://connect.microsoft.com/SQLServer/feedback/details/526431/make-more-functions-sargable

Thanks. Let me be the first to wish you a Happy Christmas and a Sargable New Year. See you at the PASS Summit in November I hope!

Something for everyone at the SQLBits Training Day

rob_farley — Fri, 03 Sep 2010 05:03:30 GMT

Of course, the one not to miss is the one I’m doing, on Fixing Queries with Advanced T-SQL Techniques, but actually, they’re all excellent.

For example, Simon’s just blogged that Buck Woody’s seminar topic has changed. Instead of being on career development, it’s now on DBA skills for the non-DBA. This is a seminar that every .Net developer and Windows admin should be on. I can think of many sysadmins and helpdesk operators who should be sitting in the room listening to Buck explain this material. It’s the type of event that whole teams should attend, and I hope that dev shops from all around the UK (and beyond) will take advantage of this.

The Virtualisation day from Brent Ozar is amazingly relevant for just about everyone as well, considering that everyone wants to know about whether virtualisation is worth embracing or not. I’ve had plenty of clients ask me just that in the past year or two, and I would happily send people to Brent’s day to learn from one of the best.

Another topic of massive interest has to be Chris Webb’s day on tuning SSAS. I taught Chris’ MDX course in Adelaide recently (and will do so again in the not-too-distant future), and I can happily attest to Chris’ expertise in this area. With an increasing number of people venturing into the BI space, this will be a popular talk – as will Chris Testa-O’Neill’s seminar on Reporting Services. Chris is the owner of the Manchester accent heard on the Microsoft eLearning courses about SSRS, and at least if you’re in the room and don’t understand what he’s saying you can put your hand up and ask! (Only joking – Chris’ accent is perfectly understandable. He’s also going to be in Adelaide speaking to my user group in December, which will be a great time!)

Continuing the fantastic quality, the top-ranked precon from TechEd North America this year is being repeated. Maciej Pilecki’s seminar about SQLOS goes into SQL internals to a depth that few in the world can match. If you’re a DBA wanting to get deeper, then this is for you.

For database developers, Klaus Aschenbrenner is delving into SQLCLR and Service Broker – areas which I find are still very underappreciated.

And of course, my seminar, looking at the way that various query constructs translate into plans, and how to improve the way they’re handled. We’ll be looking at all kinds of things, leaving you wishing that you weren’t registered for the rest of SQLBits (because you’ll be wanting to connect back to work to fix up some of your queries). In fact, if you’re coming along to my stuff, feel free to bring some queries, and I’ll see if I can use them in some of my examples. That’ll get you some consulting time thrown into the price of the event.

So I really think there’s something for everyone. If you’re a database developer and not interested in my one, then do Klaus’, or one of the Chrisses’. If you’re a DBA, then get yourself onto Maciej’s or Brent’s, and get your non-DB colleagues onto Buck’s (and as many of them as you can). Just make sure you’re hanging around for the Friday and Saturday as well for one of the best SQL events in the world.

I’ll see you there!

PS: Did I mention that you can get a discount if you register today?

Q&A about my SQLBits precon

rob_farley — Tue, 31 Aug 2010 12:42:05 GMT

I received an email from someone who’s trying to decide whether or not to register for my precon at SQLBits 7 next month. He’s already coming to the event (which should be a given for anyone who can make it), but he was trying to work out a few things about my precon, which is called “Fixing Queries through Advanced T-SQL Techniques”.

He wrote:

Hi Rob,
I'm trying to decide on my SQLBits 7 choices and am looking at your Precon Thursday session hence I had a few questions.
I've (mostly) administered and developed in sql server for 10 years hence trying to gauge the level / suitablility.
How much do you go into execution plans?
Will there be any printed material for the course?
I find I learn from repetition hence dont want to do a course and forget it all by the next day!
Is there any reading you suggest?
Sorry to be fussy, paying from my own pocket not a company training budget you see!

I don’t think he’s very fussy at all. Paying for training out of your own pocket can be scary. Last thing you want is to feel like it’s a waste. So I replied:

Hi,

Execution plans will be a major part of the day. Just about every aspect of the day will look at the effect of various constructs on the plan, as the plan is where many performance issues can be seen.

As far as printed material is concerned - I wasn't planning on giving much out before the event, because the day will be so focussed on queries and seeing how things are reflected in the plans (and I find this can be much more easily expressed verbally and with hand-waving than in written form). I will certainly provide the queries that are used, but was looking to focus on making you think, not read. The key points will be inserted as comments in the queries, so I do expect that you'll have plenty of material to remind you what was seen once you get back to work - hopefully this will be more useful than giving you notes ahead of time. Plus, I'll always answer emails about the content.

For reading material, I'd make sure you're familiar with basic execution plan concepts, as that will probably give you an advantage over many of the people in the class. My hope is that you'll leave the day with the skills to be able to evaluate the plans that your queries are producing, and have learned ways to improve them. There is an excellent chapter on Execution Plans in the book "Inside Microsoft® SQL Server(tm) 2005: Query Tuning and Optimization" - but I will also be explaining the key points for those less familiar with plans.

To really work out if this precon is for you, watch the video from SQLBits V at http://sqlbits.com/Agenda/event5/Designing_for_simplification/default.aspx. If this is stuff that interests you, and you want more (and in particular, looking at real queries), then definitely come along.

Hope this helps,

Rob

And if you (the reader) are trying to decide whether or not to do a precon (particularly mine), then I hope this helps you too. I can’t answer tonight’s questions for the other precon presenters, I can only comment on the type of things that I’ll be covering, but I can thoroughly recommend all of the things on offer. The list of presenters is tremendous, and I think this really helps demonstrate that SQLBits is very much a world-class conference.

SQLBits pre-con – Fixing Queries with Advanced T-SQL

rob_farley — Wed, 28 Jul 2010 11:50:00 GMT

I’m giving a pre-con seminar at SQLBits 7 on September 30 this year in York (the Old one, in Yorkshire – similar to the New one, but with different songs). Let me quickly explain a few things about it – to help you persuade your boss to let you attend (and when you’ve done the persuading, go here and pick the “Full Conference” option).

It’s on the topic of Advanced T-SQL, but from a very practical perspective. I’m not going to be going into the uses of ranking functions or recursive CTEs as I have done with courses I’ve written in the past. Instead, I’ll be spending time looking at a bunch of things that I find most people don’t know about their queries, and show how various things can impact those queries’ execution plans. So it’s not so much Advanced features, but deeper aspects of the T-SQL you already know.

For example, I’ll look at a query that Denny Cherry and I discussed a while back, and consider how various things could be addressed using aspects of T-SQL such as sorting by aggregated fields with GROUP BY. By considering the impact of something like this, seeing where it can be used safely and where it can’t be used safely, hopefully you will be able to make better decisions about your T-SQL code and the methods you choose to solve problems.

Why do I want to speak on this topic? After all, it can be a lot of fun to talk about ranking functions or recursive CTEs – that’s functionality that a lot of people don’t really leverage, and it can help solve a lot of problems. But the things that I’m going to cover will help your overall understanding of T-SQL, and give you practical ways to improve a lot of the queries that you’ve written in the past. I will show you how to make your queries simpler, but better.

I remember one of the first times I explained some of these things to someone. They said “Wow, you’re making me want to go back and reconsider every query I’ve ever written.” That’s the kind of feedback that I’m hoping to get from the attendees.

Some of the points I’ll make will overlap will things that I covered in my SQLBits talk from last November (as reviewed by Phil Nolan – it was also Ian Russell’s favourite talk), and things that I covered in the 24 Hours of PASS in May (as reviewed by HanSQL). They were both popular talks, and hopefully people who attend the whole day of material will also get a lot out of it.

Being a pre-con, it does have a cost associated with it (the free event is on the Saturday). But I guess it comes down to the value. If you have a budget for training this year and want to find something to spend it on, then the paid portions of SQLBits (including my seminar of course!) are definitely worthwhile. If you don’t write T-SQL, then maybe one of the Chris’ (Webb or Testa) talks on SSRS or SSAS, or Brent Ozar’s session on Virtualisation could be for you (but be careful – he spells Virtualisation with a Z), or Buck Woody’s session maybe. There are seven to choose from (the others are Klaus and Maciej), and I’m sure they’ll all be excellent.

So get yourself to sqlbits.com and start planning a trip to Yorkshire.

I’ve also submitted some sessions for the main conference, so if you’re keen to see more, get up to York for the whole three days of Sep 30 to Oct 2 and be prepared to learn something. Having been a part of SQLBits V in South Wales (also the Old one), I can assure you this is one of the world’s top SQL events and shouldn’t be missed. It’ll also be a highlight for me as my latest year of being an MVP ends on September 30. Hopefully October 1 will bring a renewed award, but if it doesn’t, then this pre-con will be the last thing I do as an MVP.