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 » query plans   (RSS)
  • Halloween Protection – The Complete Series

    I have just published a four-part series for SQLPerformance.com on the Halloween Problem. Some of you will never have heard of this issue, and those that have might associate it only with T-SQL UPDATE queries. In fact, the Halloween problem affects execution plans for INSERT, UPDATE, DELETE and MERGE statements. This is a topic I have been ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on February 20, 2013
  • Why Doesn’t Partition Elimination Work?

    Given a partitioned table and a simple SELECT query that compares the partitioning column to a single literal value, why does SQL Server read all the partitions when it seems obvious that only one partition needs to be examined? Sample Data The following script creates a table, partitioned on the char(3) column ‘Div’, and populates it with ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on September 11, 2012
  • Compute Scalars, Expressions and Execution Plan Performance

    The humble Compute Scalar is one of the least well-understood of the execution plan operators, and usually the last place people look for query performance problems.  It often appears in execution plans with a very low (or even zero) cost, which goes some way to explaining why people ignore it. Some readers will already know that a Compute ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on September 4, 2012
  • Parallel Execution Plans Suck

    Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP. There was an interesting question asked by Mark Storey-Smith on dba.stackexchange.com back in October 2011.  He was looking at the execution plan for a query that counts a million rows from a virtual auxiliary table of ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on May 2, 2012
  • Is Distinct Aggregation Still Considered Harmful?

    Back in 2008, Marc Friedman of the SQL Server Query Processor Team wrote a blog entry entitled “Distinct Aggregation Considered Harmful”, in which he shows a way to work around the poor performance that often results simply from adding the keyword DISTINCT to an otherwise perfectly reasonable aggregate function in a query.  This post is an ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on December 3, 2011
  • Why generalizations are dangerous

    A couple of years ago, John Sansom wrote a blog post comparing the performance of two different ways to get the maximum value from a column: MAX() and TOP (1). http://www.johnsansom.com/performance-comparison-of-select-top-1-verses-max/ In the conclusion, he states:  When a clustered index is present on the table & ...
    Posted to Aaron Bertrand (Weblog) by AaronBertrand on September 15, 2011
  • Can a SELECT query cause page splits?

    Books Online has this to say about page splits: When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row.  This reorganization is known as a page split.  A page split makes room for new records, but can take time to perform and is a resource ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on August 29, 2011
  • Join Performance, Implicit Conversions, and Residuals

    You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when ...
    Posted to Paul White: Page Free Space (Weblog) by Paul White on July 18, 2011
  • PASS Performance Virtual Chapter: Parallelize Your Queries!

    Last month a new PASS Virtual Chapter was introduced, one with a theme that is near and dear to much of what I like to work on: performance. Tomorrow, Tuesday August 3rd at noon Eastern time, the chapter will have its second meeting, and I will be doing the presentation. The topic is Parallelism and Performance. Here's the abstract:In ...
    Posted to Adam Machanic (Weblog) by Adam Machanic on August 2, 2010
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement