The DIY guide for local-global aggregation

If you have read the title, you will not be surprised when I tell you that this blog is about a thing called “local-global aggregation”, and on how to do that yourself. So let’s start with the two obvious questions: what the heck is local-global aggregation anyway, and why the heck would you ever want to do it yourself? What is local-global aggregation The term local-global aggregation may sound scary, but it’s not that hard to understand. You might even already know the concept if you have ever heard the arguably most used explanation of parallelism. You have a jar…
Read More

How TOP wrecks performance (part 2)

In my previous post, I showed how the vanilla version of TOP can really help performance by nudging the optimizer into picking an execution plan that is optimized for returning just a few rows instead of the entire result set. But I also showed that adding WITH TIES causes a cardinality misestimate, which may lead to suboptimal or downright terrible plan choices. Luckily, this is fixed in SQL Server 2014 and later, provided you use the new cardinality estimator. In this post I will show how the PERCENT option causes even worse performance, and how you can avoid that performance…
Read More

How TOP wrecks performance (part 1)

2 Comments
The TOP keyword in the SELECT clause may not be ANSI standard, but I am sure we have all used it. But do you know that it comes with two optional keywords? And have you used them? They are PERCENT and WITH TIES. TOP Let’s first look at the based functionality of TOP, and how it affects performance – which in fact, contrary to the title of this post, is usually quite good for performance. Here is a simple example, using the AdventureWorks2012 sample database (which can be downloaded from Codeplex). Let’s start with a very basic example: SELECT   CustomerID,…
Read More

Give speakers feedback. It makes a difference.

4 Comments
I sometimes struggle with finding a catchy title for a blog post. It has to grab the attention, and clarify what the post is about. If you decided to spend time reading my words, I do not want you to finish ending “if I had known this was the content, I would have done something better with my time”. For this post, I think the title just sums up the entire post. If you are short on time, you can stop reading now. Seriously. Feedback from SQL Server Days Last week I received the feedback from my presentation at the…
Read More

Execution plans, laundry, and a giveaway

2 Comments
In just a week from now, SQL Saturday #337 will kick off in Portland, Oregon. And I will be there – the third time already for me to be speaking in Portland. For a European, Portland is not the most logical location. But the organization over there is a pretty smart bunch of people. They figure that being close to Seattle gives them a great opportunity – so whenever they get the chance, they will reserve a Saturday just before the PASS Summit in Seattle for their own event. And then they make sure to organize this event so extremely…
Read More

Database Mail … and then the SMTP Server changed

The database I inherited when I started my current job sends out lots of mails. It does so using Database Mail, and it has lots of mail accounts and mail profiles defined. I know that several of those profiles are used, I suspect that some are not, and I have no idea about the rest – one day I will find the time to clean up, but so far there have always been more pressing matters to attend to. But today the mail administrator told me that due to a change in architecture, SQL Server had to start using a…
Read More

PASS Summit Abstract Feedback

Last week, Adam posted the feedback he received from PASS on the abstracts he had submitted for the PASS Summit 2014. I was very happy he did – not only because the post itself was a good read, but also because I had not seen the PASS announcement on making the feedback available (I did see it in a PASS community mail a few days later, though). I can imagine that not everyone will want to see this feedback, but I do. I love harsh criticism, as long as it’s fair – that’s how I can learn and improve. (The…
Read More

SQLPass NomCom election: Why I voted twice

Did you already cast your votes for the SQLPass NomCom election? If not, you really should! Your vote can make a difference, so don’t let it go to waste. The NomCom is the group of people that prepares the elections for the SQLPass Board of Directors. With the current election procedures, their opinion carries a lot of weight. They can reject applications, and the order in which they present candidates can be considered a voting advice. So use care when casting your votes – you are giving a lot of influence to the people you choose. Because there are three…
Read More

SQL TuneIn Zagreb 2014 – Session material

I spent the last few days in Zagreb, Croatie, at the third edition of the SQL TuneIn conference, and I had a very good time here. Nice company, good sessions, and awesome audiences. I presented my “Understanding Execution Plans” precon to a small but interested audience on Monday. Participants have received a download link for the slide deck. On Tuesday I had a larger crowd for my session on cardinality estimation. The slide deck and demo code used for that presentation will be available through the conference website, but for those who cannot wait, I have also attached them to…
Read More

TechDays 2014 – Session material

Last week in the Hague, I had the honor to present two very different sessions at the Dutch TechDays conference. A deep-dive session on internals of the SQL Server 2012 nonclustered columnstore index, and a very developer-oriented session on the bare basics of performance tuning. To my delight, both times the room was filled with very interested people, asking great questions and, I guess, enjoying my presentations. All sessions were recorded, and I have been told that in due time, all will be available on Channel 9. But what if you can’t stand the wait? What if you just want…
Read More
Menu

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close