THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Browse by Tags

All Tags » Performance » Indexing » sql   (RSS)
  • String length and SARGability

    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 a table with indexes. I’d normally use one of the AdventureWorks versions for this, but as ...
    Posted to Rob Farley (Weblog) by rob_farley on September 22, 2013
  • Ultimate query tuning

    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 anything at all. Luckily for me, performance of a query is typically evaluated using the ...
    Posted to Rob Farley (Weblog) by rob_farley on August 19, 2013
  • The SQL Query Optimizer – when Logical Order can get it wrong

    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 whatever is in the SELECT clause. We even get in the habit of creating indexes that focus on ...
    Posted to Rob Farley (Weblog) by rob_farley on December 30, 2012
  • Covering, schmuvvering – when a covering index is actually rubbish

    Take a look at this query plan. Yes, that arrow indicates a single row. This is an Index Seek, returning a single row. And yet it’s rubbish. That’s right – it’s rubbish! In fact, I had to provide a hint for it to use this index. A table scan would’ve been better, and this is what happens without the index hint. Let’s look at the query. I ...
    Posted to Rob Farley (Weblog) by rob_farley on May 19, 2011
  • Function Invertability for SARGability

    My good friend Simon Sabin used the term ‘invertability’ on a Connect item he logged today. Essentially, Simon’s noticed that there are lots of people that use year(someDate), but that the system doesn’t understand that this function doesn’t affect the order of the items in the index. month(someDate) does, but if you’re already using ...
    Posted to Rob Farley (Weblog) by rob_farley on March 23, 2011
  • Probe Residual when you have a Hash Match – a hidden cost in execution plans

    No, this post has nothing to do with airport security, and nothing to do with marijuana. Being honest, this post is only half about Hash Matches and Probe Residuals. It’s more about the types of predicates you can see mentioned in a SQL Server Execution Plan (or Query Plan, but I prefer to call them Execution Plans) – but you may well see some ...
    Posted to Rob Farley (Weblog) by rob_farley on March 21, 2011
  • The SSIS tuning tip that everyone misses

    I know that everyone misses this, because I’m yet to find someone who doesn’t have a bit of an epiphany when I describe this. When tuning Data Flows in SQL Server Integration Services, people see the Data Flow as moving from the Source to the Destination, passing through a number of transformations. What people don’t consider is the Source, ...
    Posted to Rob Farley (Weblog) by rob_farley on February 17, 2011
  • Inverse Predicates - look both ways before you cross

    When I’m at the PASS Summit in Seattle this week, I will need to remember that I can’t just step onto the road if I’m walking along the footpath on the right-hand side. In the UK and Australia, where we drive the correct side, it’s fine (just don’t tell my kids), because the cars that are nearest me are coming towards me – except of course, the ...
    Posted to Rob Farley (Weblog) by rob_farley on November 8, 2010
  • Table? No such thing…

    No really – hear me out. Of course you create tables, and you query tables, and we say that data is stored in tables. The table is (rightly) a fundamental part of relational theory. But I find that when I think about queries and how they run, I need to approach the system thinking about the indexes that I’m querying, not the tables. When you ...
    Posted to Rob Farley (Weblog) by rob_farley on September 13, 2010
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement