Welcome to MSDN Blogs Sign in | Join | Help

Browse by Tags

Tuesday, October 07, 2008 2:44 PM

Random Prefetching

In my last post , I explained the importance of asynchronous I/O and described how SQL Server uses sequential read ahead to boost the performance of scans. In this post, I'll discuss how SQL Server uses random prefetching. Let's begin with a simple example
Posted by craigfr | 0 Comments
Filed under: , ,
Tuesday, September 23, 2008 1:45 PM

Sequential Read Ahead

Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize hardware utilization. SQL Server includes two asynchronous I/O mechanisms - sequential read ahead and random prefetching - that are designed to address this
Posted by craigfr | 3 Comments
Filed under: ,
Friday, August 22, 2008 3:42 PM

Dynamic Partition Elimination Performance

In this post on partitioned tables, I mentioned that SQL Server 2008 has a much more efficient implementation of dynamic partition elimination as compared to SQL Server 2005. In response, a reader posted this comment asking how much dynamic partition
Posted by craigfr | 1 Comments
Filed under:
Tuesday, August 05, 2008 11:33 AM

Partitioned Indexes in SQL Server 2008

In my last post , I looked at how SQL Server 2008 handles scans on partitioned tables. I explained that SQL Server 2008 treats partitioned tables as tables with a logical index on the partition id column and that SQL Server 2008 implements partition elimination
Posted by craigfr | 1 Comments
Tuesday, July 15, 2008 2:03 PM

Partitioned Tables in SQL Server 2008

In this post , I introduced how SQL Server 2005 implements query plans on partitioned tables. If you've read that post or used partitioned tables, you may recall that SQL Server 2005 uses a constant scan operator to enumerate the list of partition ids
Posted by craigfr | 6 Comments
Friday, June 27, 2008 2:44 PM

Subqueries in BETWEEN and CASE Statements

Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT) CREATE TABLE T2 (A INT, B INT) SELECT * FROM T1 WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs to be evaluated
Posted by craigfr | 5 Comments
Filed under:
Thursday, June 05, 2008 10:15 AM

Implicit Conversions

In my last couple of posts, I wrote about how explicit conversions can lead to errors. In this post, I'm going to take a look at some issues involving implicit conversions. SQL Server adds implicit conversions whenever you mix columns, variables, and/or
Posted by craigfr | 1 Comments
Filed under:
Thursday, May 15, 2008 9:39 AM

Query Processing Presentation

Last week, I had the opportunity to talk to the New England SQL Server Users Group . I would like to thank the group for inviting me, Adam Machanic for organizing the event, and Red Gate for sponsoring it. My talk was an introduction to query processing,
Posted by craigfr | 0 Comments
Attachment(s): QPTalk.pdf
Tuesday, May 06, 2008 11:54 AM

Conversion and Arithmetic Errors: Change between SQL Server 2000 and 2005

In this post from last week, I gave an example of a query with a conversion where the optimizer pushes the conversion below a join. The result is that the conversion may be run on rows that do not join which could lead to avoidable failures. I ran this
Posted by craigfr | 0 Comments
Filed under: ,
Monday, April 28, 2008 4:23 PM

Conversion and Arithmetic Errors

Let's take a look at a simple query: CREATE TABLE T1 (A INT, B CHAR(8)) INSERT T1 VALUES (0, '0') INSERT T1 VALUES (1, '1') INSERT T1 VALUES (99, 'Error') SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1 There is no way to convert the string "Error" into
Posted by craigfr | 4 Comments
Filed under: ,
Monday, March 31, 2008 2:04 PM

Ranking Functions: RANK, DENSE_RANK, and NTILE

In my previous post , I discussed the ROW_NUMBER ranking function which was introduced in SQL Server 2005. In this post, I'll take a look at the other ranking functions - RANK, DENSE_RANK, and NTILE. Let's begin with RANK and DENSE_RANK. These functions
Posted by craigfr | 0 Comments
Wednesday, March 19, 2008 4:28 PM

Ranking Functions: ROW_NUMBER

SQL Server 2005 introduced four new functions, ROW_NUMBER, RANK, DENSE_RANK, and NTILE that are collectively referred to as ranking functions. These functions differ from ordinary scalar functions in that the result that they produce for a given row depends
Posted by craigfr | 1 Comments
Filed under:
Wednesday, February 27, 2008 2:05 PM

Halloween Protection

In a prior post , I introduced the notion that update plans consist of two parts: a read cursor that identifies the rows to be updated and a write cursor that actually performs the updates. Logically speaking, SQL Server must execute the read cursor and
Posted by craigfr | 2 Comments
Filed under:
Wednesday, January 30, 2008 1:03 PM

Maintaining Unique Indexes with IGNORE_DUP_KEY

A few months ago, I wrote a post describing how SQL Server maintains unique indexes while avoiding false uniqueness violations. In this post, I'm going to look at how SQL Server maintains unique indexes that were created with the WITH IGNORE_DUP_KEY clause.
Posted by craigfr | 3 Comments
Filed under:
Friday, January 18, 2008 11:12 AM

Partial Aggregation

In some of my past posts, I've discussed how SQL Server implements aggregation including the stream aggregate and hash aggregate operators. I also used hash aggregation as an initial example in my introductory post on parallel query execution . In this
Posted by craigfr | 1 Comments
Filed under: ,
More Posts Next page »
 
Page view tracker