|
|
|
|
A technical SQL Server blog from New Zealand.
Browse by Tags
-
This is the third part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Read More...
|
-
This is the second part in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Read More...
|
-
This is the first in a series of posts based on the content of the Query Optimizer Deep Dive presentations I have given over the last month or so at the Auckland SQL Users’ Group and the SQL Saturday events in Wellington, New Zealand and Adelaide, Australia Read More...
|
-
There are interesting things to be learned from even the simplest queries. For example, imagine you are given the task of writing a query to list AdventureWorks product names where the product has at least one entry in the transaction history table, but Read More...
|
-
Most people know that a LIKE predicate with only a trailing wildcard can usually use an index seek: SELECT p.Name FROM Production.Product AS p WHERE p.Name LIKE N 'D%' ; As the execution plan shows, SQL Server determines a covering range (which depends Read More...
|
-
This post is for SQL Server developers who have experienced the special kind of frustration, which only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan. This situation often occurs when making Read More...
|
-
I came across a SQL Server bug recently that made me wonder how on earth I never noticed it before. As the title of this post suggests, the bug occurs in common JOIN and GROUP BY queries, and while it does not cause incorrect results to be returned, Read More...
|
-
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 Read More...
|
-
In this post, I show you how to determine exactly which statistics objects were used by the query optimizer to produce an execution plan. Trace Flags We will need three undocumented trace flags. The first one ( 3604 ) is well-known – it redirects Read More...
|
-
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 . Read More...
|
-
The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the Read More...
|
-
In my last post , I showed how using a index unique could speed up equality seeks by around 40%. For today’s entry, I’m going to use the same tables as last time (single BIGINT column, one table with a non-unique clustered index, and one table with a Read More...
|
-
A little while back, I posted a short series on seeks and scans, and one of the things I highlighted was the difference between a singleton seek and a range scan. You can find that post here , if you want a refresher. Anyway, the broad point is Read More...
|
-
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 Read More...
|
-
Question: Can a parallel query use less CPU than the same serial query, while executing faster? The answer is yes; and to demonstrate, I'll use the following two (heap) tables, each containing a single column typed as INTEGER: Let’s load the first table Read More...
|
|
|
|
|
|