SQLServerFast

The sniffing database

Hugo Kornelis
1 Comment
Your SQL Server instances, like people with hay fever that forget to take their antihistamines during summer, is sniffing all the time. Sniffing is a trick employed by the optimizer in an attempt to give you better execution plans. The most common form of sniffing is parameter sniffing. Many people know about parameter sniffing, but there are a lot of misconceptions about this subject. I have heard people describe parameter sniffing as a bad thing, and I know people who claim that parameter sniffing is mostly good with some exceptions that they then call “bad parameter sniffing”. Neither of these…
Read More

Upcoming presentations. And an offer.

Hugo Kornelis
No Comments
It’s not my habit to announce my future presentations on this blog. But I sometimes make exceptions to this rule, and today is a good day for such an exception. Especially since I have been allowed to offer some money off for one of my upcoming presentations. I’ll fly to Copenhagen tomorrow, for SQL Saturday Demark, where I will present two sessions: a 60-minute talk (“T-SQL User-Defined Functions, or: Bad Performance Made Easy”) about how T-SQL user-defined functions can wreck your performance, and a 10-minute lightning talk (“Managing Execution Plans”) about the similarities between execution plans and my coworkers. Three…
Read More

The DIY guide for local-global aggregation

Hugo Kornelis
5 Comments
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)

Hugo Kornelis
No Comments
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)

Hugo Kornelis
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.

Hugo Kornelis
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

Hugo Kornelis
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

Hugo Kornelis
2 Comments
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

Hugo Kornelis
4 Comments
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

Hugo Kornelis
6 Comments
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

Hugo Kornelis

Married, father of 2 children.

Awarded 13x MVP (2006-2016; 2019-now), in the areas SQL Server and Data Platform.

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Archives

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