Browse by Tags

There’s a technique that I’ve been using for some time now, and been looking for going even further back, which has become immediately available to everyone who can see a query plan (version permitting). Some years back (ok, it was 2010), I started to Read More...

I just wrote a post about Live Query Statistics . Let me show you how this technology in SQL Server 2014 can be used for some amazingly cool (nerdy cool, at least) stuff. Behind the scenes, LQS uses a DMV called sys.dm_exec_query_profiles. When you run Read More...

One of the coolest things in SQL Server 2016 is Live Query Statistics (LQS). But did you know that it’s available in SQL 2014? (Edit: Needs SP1) The thing is that we haven’t been able to view it effectively before now, before SQL Server Management Studio Read More...

Even with the right indexes in place, spatial queries in SQL Server are often too slow – but they needn’t be. Two of the most commonly found patterns of query in the spatial world are when you’re looking for the nearest thing to where you are (which I’ve Read More...

Performance tuning in regular SQL Server can be addressed in a number of ways. This can involve looking at what’s going on with the disk configuration, the memory configuration, the wait stats, the parallelism settings, indexing, and so much more. But Read More...

Quite often, people have filtered indexes but find they’re not being used as often as they’d like. I was reminded of this recently when I read a good post by Kendra Little from brentozar.com about filtered indexes . In it, Kendra talks about how the WHERE Read More...

SQL 2012 was a big release for working out the median in SQL Server, with the advent of the function PERCENTILE_CONT() . It’s a very elegant way of working out the median (hint, that’s the 0.5 point), even though it’s not actually an aggregate function Read More...

This is a white paper that I put together recently about APS / PDW Query Optimisation. You may have seen it at http://blogs.technet.com/b/dataplatforminsider/archive/2014/11/14/aps-best-practice-how-to-optimize-query-performance-by-minimizing-data-movement.aspx Read More...

If you’ve ever done spatial work with SQL Server, I hope you’ve come across the ‘nearest’ problem. You have five thousand stores around the world, and you want to identify the one that’s closest to a particular place. Maybe you want the store closest Read More...

There is no equivalent to the SSIS Lookup transformation in T-SQL – but there is a workaround if you’re careful. The big issue that you face is about the number of rows that you connect to in the Lookup. SQL Books Online (BOL) says: If there is no matching Read More...

A post I’ve been meaning to write for a while, good that it fits with this month’s T-SQL Tuesday , hosted by Joey D’Antoni ( @jdanton ) Ever since I got into databases, I’ve been a fan. I studied Pure Maths at university (as well as Computer Science), Read More...

CONVERT_IMPLICIT isn’t the only problem with getting data types wrong. You might have the right type, but what if the length is wrong? This post will look at both getting the type wrong and getting the length wrong too. Let’s do some testing. We’ll need Read More...

Infinitely better. 100% of the reads removed. Roughly 4000 (okay, 3890), down to zero. None. Let me explain... Obviously if there’s data being returned, there are reads needed. And obviously there is some CPU needed for SQL to be given a query and do Read More...

Sometimes I only blog for T-SQL Tuesday. I don’t want to break my run of blogging for each one just yet, but I do wonder sometimes. The reason I’m reflecting is that what we’ve done in the past will so often affect what we do in the future. The musician Read More...

It’s very easy to get in the habit of imagining the way that a query should work based on the Logical Order of query processing – the idea that the FROM clause gets evaluated first, followed by the WHERE clause, GROUP BY, and so on – finally ending with Read More...