SQL Server knowledge center

Parallelism in Index Scan

Kasim.Wirama — Thu, 26 Jun 2008 08:51:00 GMT

Parallelism in Index Scan

By : Kasim Wirama, MCDBA

Previous articles, I post some information about internal of parallelism. Now take a look parallelism in index scan. Is that bad if you have parallelism in index scan? Not really, I think. If the query needs to return thousands of records, this index scan will be beneficial to query performance when it is compensated by fast I/O subsystem and SQL Server has some available threads to process data pages. The more process threads and supported by fast I/O, the faster execution time will be.

Now focus on parallelism in index scan. The good thing parallelism in index scan is that SQL Server is able to do load balancing among threads when those threads has different speed in processing rows, so it is expected that slow threads would not dominate total execution time.

Share this post: | | | |

Internal of Parallelism

Kasim.Wirama — Thu, 26 Jun 2008 08:50:00 GMT

Internal of Parallelism

By : Kasim Wirama, MCDBA

If you notice in execution plan, probably you would find out parallelism in some operators of an execution plan. Let’s look inside parallelism behaviour in general.

Parallelism of an operator is made up at least 2 threads. For example here, there is 2 threads, thread 1 and thread 2. Thread 1 itself is made up from thread producer and thread consumer, so does thread 2. Data from producer is pushed into consumer, so it provides load balancing when some consumers runs slowly, so producer can choose available producers to process next data. The way data exchange between consumers and producers is called partitioning.

So I can say that parallelism has a set of consumer and also a set of producer. Based on number of consumer and producer, there are 3 kinds of parallelisms:

gather streams parallelism

It consists of a number of threads at producer and only one consumer thread.

repartition streams parallelism

It consists of a number of threads at producer and a number of threads at consumer.

distribute streams parallelism

It consists of a number of threads at consumer and only one producer thread.

Data is partitioned during parallelism process taking place, but it is valid only for repartition and distribute streams kind of parallelism. There are 4 types of way how parallelism partitions data into multiple consumers:

broadcast partitioning

This type of partitioning is called when producer send all data to all consumers.

hash partitioning

This type of partitioning is called when determining hash value for row that would be sent to particular consumers.

round robin partitioning

This type will send data to consumer in sequence manner.

demand partitioning

This type is pull type partitioning because producers pull data from consumers. It happens on distributed partitioned id on partitioned table.

range partitioning

This type will partition data by evaluating value of a column of a row with range function to determine which producer will receive the data.

Regarding to parallelism, one of issue that might happen is parallel deadlock. Potential query that has parallel deadlock is on order-preserving query. Example of order preserving query is ORDER BY, or if you check of text based execution plan, you will find out parallelism operator along with its attribute like this : “Parallelism(Gather Stream, ORDER BY:<some table>.<one or more column>)”. Words that is marked as bold, it is the property of parallelism that is order-preserving. You can check whether parallel deadlock might exist at your SQL Server by querying sys.dm_os_waiting_tasks (dynamic management view), notice at wait_type for same session. If you find entry CXPACKETS, it gives you indication that parallel deadlock exists at your SQL Server. Solution for removing CXPACKETS is to reduce max DOP.

Final thing that parallelism might happen is about the number of rows in a table. When a table size is huge, for example containing 300,000 rows, parallelism will more potentially happen compared with table with size 10,000 rows.

Share this post: | | | |

SQL Internal Viewer Tool

Kasim.Wirama — Sun, 22 Jun 2008 02:10:00 GMT

You would like to know internal structure of your SQL Server? Now, you can view how fragmented your database visually or other information regarding to allocation map through freeware tool for SQL Server, namely SQL Internal Viewer (http://www.sqlinternalsviewer.com/).

In this website, you can also learn from blogs published by the author.

I recommend this tool for anyone who are interested with internal structure of SQL Server.

Enjoy!

Share this post: | | | |

Table Spool in Equality Correlated Subquery

Kasim.Wirama — Sun, 22 Jun 2008 02:00:00 GMT

Table Spool in Equality Correlated Subquery

By : Kasim Wirama, MCDBA

Recent posting, I examine spooling characteristic in nonequality correlated subquery. In nonequality correlated subquery, optimizer generate index spool which created index on the fly spool operation and cache execution result from immediately previous execution. They are called eager index spool and lazy index spool. Let’s take a look spooling behavior in equijoin correlated subquery.

Given the example of equijoin correlated subquery below :

select

o1.OrderID, o1.Freight

from dbo.Orders as o1

where o1.Freight >

(

select AVG(o2.freight)

from dbo.Orders as o2

where o2.CustomerID = o1.CustomerID

);

The query displays order information for each customers whose freight cost is above average for themselves. Notice that predicate in inner query uses equality (=) instead of nonequality in my recent posting. Execution plan for this correlated subquery is :

|--Nested Loops(Inner Join)

|--Table Spool

| |--Segment

| |--Sort(ORDER BY:([o1].[CustomerID] ASC))

| |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o1]), WHERE:([Northwind].[dbo].[Orders].[CustomerID] as [o1].[CustomerID] IS NOT NULL))

|--Nested Loops(Inner Join, WHERE:([Northwind].[dbo].[Orders].[Freight] as [o1].[Freight]>[Expr1004]))

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1012]=(0) THEN NULL ELSE [Expr1013]/CONVERT_IMPLICIT(money,[Expr1012],0) END))

| |--Stream Aggregate(DEFINE:([Expr1012]=COUNT_BIG([Northwind].[dbo].[Orders].[Freight] as [o1].[Freight]), [Expr1013]=SUM([Northwind].[dbo].[Orders].[Freight] as [o1].[Freight])))

| |--Table Spool |--Table Spool

First optimizer does scanning all records in Orders table, then sorts the rows based on CustomerID. Next operator is Segment operator. Segment operator will break all records into groups partitioned by CustomerID column. For each groups will be insert into Table Spool operator. Output of table spool is one row which will be processed by topmost Nested Loops operator. Now optimizer will count average freight from second Table Spool operator. Average function is representated by Compute Scalar operator and Stream Aggregate operator, so the function return 1 average freight for current customerid and it compares each rows of customerid from third Table Spool. After the current group of customerid has been processed, table spool is truncated and next group of customerid will be inserted into table spool operator. Do second and third table spool operators refer to first table spool operator? The answer is yes, at graphical execution plan you can check Primary Node ID property for second and third one is same as Node ID property for first Table Spool operator.

If you compare in what extents rewinds between table spool and index spool. Rewinds rate of index spool is higher than that of table spool. So for data that contains many duplicates, index spool performs better than table spool.

Share this post: | | | |

Parallelism Overview

Kasim.Wirama — Sun, 22 Jun 2008 01:59:00 GMT

Parallelism Overview

By : Kasim Wirama, MCDBA

When you observe execution plan for some query that processes many rows, you will find operator with small arrow at graphical execution plan. For example, you have Index Scan operator with double small arrow icon at the icon. It shows that the operator has been parallelized in its scanning. Parallelism would potentially happen in hyperthreading, multi core, or multi processor environment.

Parallelism usually is used to process query with many rows (bigger query) with same amount of time as smaller query does. Though the response time is maintained short with parallelism, but it gives overhead to CPU, thus it is not preferable in high concurrent OLTP system because it will lower down database throughput.

In lower level, optimizer will partition input data into required and available CPU to output the result as quick as possible. Each CPU gives one thread and each thread will execute same aggregate function, join, etc . They executes independently among another.

Optimizer choose between serial and parallel plan based on cheaper cost. Here are configuration settings that acts as cost guideline for optimizer :

1. Affinity Mask setting that allows optimizer to use multi processor. By default the value is 0, it means optimizer can use all available processor.

2. Max degree of parallelism setting. By default the value is zero or more than one, it means optimizer can generate parallelism. If the value is one, only serial plan could be generated by optimizer.

3. Cost threshold for parallelism setting. The higher the value, the less likely optimizer will choose parallel plan if the serial plan has cost less than cost threshold.

You can find these settings by running this SQL command at SQL Server Management Studio :

EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;

Share this post: | | | |

Degree of Parallelism, Max Degree of Parallelism, Affinity Mask and Thread Allocation

Kasim.Wirama — Sun, 22 Jun 2008 01:59:00 GMT

Degree of Parallelism, Max Degree of Parallelism,

Affinity Mask and Thread Allocation

By : Kasim Wirama, MCDBA

You can view parallelism in your execution plan by viewing either graphically or textually. Optimizer decides between parallelism and serial plan on cost-based. Parallelism itself is determined by SQL Server advanced configuration setting and number of processor of SQL Server resides. One of advanced configuration item relating to parallelism is Max Degree of Parallelism and Affinity Mask.

If you don’t specify MAXDOP N query hints, by default max degree of parallelism is equal to number of processor that is allowed to give the thread to run corresponding parallelism operator. The number of allowed processor is determined through Affinity Mask in advanced configuration setting. If you specify MAXDOP N, it will used N DOP, but if you put MAXDOP 0, the max DOP will be equal to number of allowed processor in Affinity Mask.

DOP reflects number of thread that will execute on an operator. Even though you have max DOP more than allowed processor, it doesn’t mean that you can have all thread on the max DOP. It depends on the available thread which is specified in Max Worker Thread (found in advanced configuration setting). Max Worker Thread itself depends on number of processor and kinds of platform (32 bit or 64 bit). You can view how much query request takes number of thread through querying to Sys.dm_os_tasks. The information difference between sys.dm_os_tasks and parallelism in execution plan is that sys.dm_os_tasks gives information all threads in a query while parallelism in execution plan gives information about how much thread taken by each operators. Generally parallelism among operators could be executed at the same time, but exception applies here is that, if you found that there is blocking operator(s), the execution between operator before and after blocking operator is not done at the same time, so those operators can share resources such as memory and threads.

Share this post: | | | |

Decorrelating Correlated Subquery

Kasim.Wirama — Sun, 22 Jun 2008 01:57:00 GMT

Decorrelating Correlated Subquery

By : Kasim Wirama, MCDBA

I have written posting about correlated and noncorrelated subquery. Now let’s take a look correlated subquery but optimizer generated noncorrelated query-like execution plan.

Here is query to display order for each customer who lives in Madrid

select orderid

from orders as o

where exists

(

select *

from Customers as c

where c.CustomerID = o.CustomerID

and c.City = 'Madrid'

)

Execution plan for the correlated subquery is :

|--Nested Loops(Inner Join, OUTER REFERENCES:(.[CustomerID]))

|--Index Seek(OBJECT:([Northwind].[dbo].[Customers].[City] AS ), SEEK:(.[City]=N'Madrid') ORDERED FORWARD)

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[CustomerID] AS ), SEEK:(.[CustomerID]=[Northwind].[dbo].[Customers].[CustomerID] as .[CustomerID]) ORDERED FORWARD)

The execution above is same as execution plan for the following query :

select orderid

from orders as o

join Customers as c

on o.CustomerID = c.CustomerID

where c.City = 'Madrid'

Second example here is query returning freight and its average from all orders within same customer.

select o1.OrderID, o1.Freight,

(select AVG(freight) from orders as o2

where o2.CustomerID = o1.CustomerID

) as AverageFreight

from dbo.orders as o1

Its execution plan is :

|--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]))

|--Hash Match(Right Outer Join, HASH:([o2].[CustomerID])=([o1].[CustomerID]), RESIDUAL:([Northwind].[dbo].[Orders].[CustomerID] as [o2].[CustomerID]=[Northwind].[dbo].[Orders].[CustomerID] as [o1].[CustomerID]))

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014]/CONVERT_IMPLICIT(money,[Expr1013],0) END))

| |--Stream Aggregate(GROUP BY:([o2].[CustomerID]) DEFINE:([Expr1013]=COUNT_BIG([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight]), [Expr1014]=SUM([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight])))

| |--Sort(ORDER BY:([o2].[CustomerID] ASC))

| |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o2]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o1]))

The item immediately below Hash Match operator is scanning all orders and sort by customerid before they are computed for its average freight with grouping by CustomerId. To get freight column, optimizer decides to do clustered index scan, if you remove freight column, optimizer will choose index scan rather than clustered index scan, which is more efficient. There is possible that one customer may have more than one orders. For example, a customer has 5 orders. In this case optimizer will calculate average freight once for the customer, not 5 times for the customer. It is reflected with calculation of aggregation first before matched with the inner execution of clustered index scan of alias O1. The more orders for each customers, the more efficient execution plan, because it just calculates average value once for each customers.

Most cases, noncorrelated subquery is more efficient than correlated subquery. If you have options to change correlated subquery into non correlated subquery, it is better to do so.

Share this post: | | | |

Skip Stored Procedure Recompilation

Kasim.Wirama — Thu, 19 Jun 2008 00:07:00 GMT

Skip Stored Procedure Recompilation

By : Kasim Wirama, MCDBA

Stored procedure (and also function) is preferable to be implemented in SQL Server because its compilation code is cached, so next execution will take the compilation form in cache. Imagine that you have a complex stored procedure and you found that it recompile for each time it is called through SP:Recompile and SQL:StmtRecompile. In this case SQL Server have some options that you might to consider to avoid the problematic stored procedure from being recompiled.

If you want to make your query got cached in cache store, there is some ways to achieve it :

make your tables which are involved in query to read only.
turn off automatic update statistics, it is applicable when there is little database update activity that impacted on index update. But it is recommended that you turn on auto update statistics.
if your column is unique, make it unique constraint or unique index and your select query use the column in WHERE clause.
if your column is not unique, you can consider use query hints : Option (Keepfixed Plan), it is available on SQL Server 2005.

For overall recompilation monitoring for SQL Server, performance monitor give 2 relevant counters under performance object : MSSQLServer : SQL Statisctics, i.e. SQL Re-compilation/sec and SQL:Batch Requests/sec. If ratio between them is high, your SQL Server has recompilation issue that might degrade database performance.

Share this post: | | | |

Rebind and Rewind on Non Equal Correlated Subquery

Kasim.Wirama — Tue, 17 Jun 2008 09:42:00 GMT

Rebind and Rewind on Non Equal Correlated Subquery

By : Kasim Wirama, MCDBA

This posting, I would show other execution plan operator that has caching capability regarding to non correlated subquery that involved non equality relationship between inner and outer query.

What is the behaviour difference for optimizer to generate plan between correlated and noncorrelated subquery? In noncorrelated subquery, inner query doesn’t depend on outer query thus optimizer compute inner query first then outer query, whereas in correlated subquery, optimizer compute every row in outer query then to inner query because inner query depends on value of outer query.

Example of nonequality-correlated subquery is :

SELECT

orderid

FROM orders AS o1

WHERE freight <

(

SELECT avg(freight)

FROM orders AS o2

WHERE o2.orderdate < o1.orderdate

);

Execution plan for this query is :

|--Filter(WHERE:([Northwind].[dbo].[Orders].[Freight] as [o1].[Freight]<[Expr1004]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([o1].[OrderDate]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o1]))

|--Index Spool (SEEK:([o1].[OrderDate]=[Northwind].[dbo].[Orders]. [OrderDate] as [o1].[OrderDate]))

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012]/CONVERT_IMPLICIT(money,[Expr1011],0) END))

|--Stream Aggregate (DEFINE:([Expr1011]=COUNT_BIG ([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight]), [Expr1012]=SUM([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight])))

|--Index Spool(SEEK:([o2].[OrderDate] < [Northwind].[dbo]. [Orders].[OrderDate] as [o1].[OrderDate]))

|--Clustered Index Scan (OBJECT:([Northwind].[dbo]. [Orders].[PK_Orders] AS [o2]))

First index spool is lazy spool and second one is eager spool (look at logicalOp column). For first access into inner query, optimizer scan orders table then create eager index spool (second spool). Eager index spool is also called as index on the fly spool because it eagerly build temporary index on entire outer input on first access. On subsequent access from outer query, average aggregation is handled by computer scalar and stream aggregate operator and then it is cached by first index spool (lazy index spool). It is called lazy index spool, because it cache result from immediate previous execution. When next input row has the same orderdate, then the result is taken from lazy index spool only. This behaviour is called rewind. But if next input row has different orderdate, then the result is taken from accumulated value in lazy index spool or if it is not in lazy index spool, the operator request input from stream aggregate and computer scalar operator. Such behaviour is called rebind because it rebinds new value into lazy index spool.

If you check number of rows of rebind and rewind at property of lazy index spool, their total will be equal to total number of rows in orders table.

Optimizer tends to create index spool operator when there are many duplicate value on orderdate column and there are a lot of input rows from outer query. So if you reduce the number of input column, index spool operator will be disappear. Look at this example and its execution plan below :

SELECT

orderid

FROM orders AS o1

WHERE freight <

(

SELECT avg(freight)

FROM orders AS o2

WHERE o2.orderdate < o1.orderdate

)

AND shipcity = 'Caracas';

Execution plan of the query is :

|--Filter(WHERE:([Northwind].[dbo].[Orders].[Freight] as [o1].[Freight]<[Expr1004]))

|--Nested Loops(Inner Join, OUTER REFERENCES:([o1].[OrderDate]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o1]), WHERE:([Northwind].[dbo].[Orders].[ShipCity] as [o1].[ShipCity]=N'Caracas'))

|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012]/CONVERT_IMPLICIT(money,[Expr1011],0) END))

|--Stream Aggregate(DEFINE:([Expr1011]=COUNT_BIG([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight]), [Expr1012]=SUM([Northwind].[dbo].[Orders].[Freight] as [o2].[Freight])))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders] AS [o2]), WHERE:([Northwind].[dbo].[Orders].[OrderDate] as [o2].[OrderDate]<[Northwind].[dbo].[Orders].[OrderDate] as [o1].[OrderDate]))

Observe that there is no index spool operator.

Share this post: | | | |

Execution Plan for Noncorrelated Query

Kasim.Wirama — Tue, 17 Jun 2008 09:01:00 GMT

Execution Plan for Noncorrelated Query

By : Kasim Wirama, MCDBA

This posting, I would like to show various possible execution plan for non correlated query and possible action to rewrite query for better execution plan. Let’s run the query at Northwind database that displays list of orderid whose freight cost is under average freight cost of all orders

SELECT orderid FROM dbo.orders

WHERE freight < (SELECT AVG(freight) FROM dbo.orders)

Execution plan for the non correlated and scalar query is :

|--Nested Loops(Inner Join, WHERE:([Northwind].[dbo].[Orders].[Freight]<[Expr1006]))

|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1013]=(0)

| |--Stream Aggregate(DEFINE:([Expr1013]=COUNT_BIG([|

|--Clustered Index Scan (OBJECT:([Northwind].[dbo].

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))

The above execution plan calculate average value of freight and then verify all records in orders database which has freight value below average value of freight. It’s straightforward description from the execution plan. Now let’s look into another noncorrelated subquery

SELECT

orderid

FROM orders

WHERE customerid =

(

SELECT customerid

FROM customers

WHERE contactname = 'Antonio Moreno'

);

Execution plan for the query is :

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008]))

|--Assert(WHERE:(CASE WHEN [Expr1007]>(1) THEN (0)

| |--Stream Aggregate(DEFINE:([Expr1007]=Count(*), [Expr1008]=ANY([Northwind].[dbo].[Customers].[CustomerID])))

| |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Customer WHERE:([Northwind].[dbo].[Customers].[ContactName]=N'Antonio Moreno'))

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[CustomerID]), SEEK:([Northwind].[dbo].[Orders].[CustomerID]=[Expr1008]) ORDERED FORWARD)

The query above could probably returns error because inner query might return non scalar value (more than 1 row) and contactname is not unique, so probably 1 contactname serves several customerid. The way optimizer checking whether inner query returns more than 1 rows is by adding ASSERT operator. If it is true, then the query raises error.

Another noticeable operator is ANY operator. It is internal operator that counts the number of record for each customerID. But because Stream Aggregate expects customerid to be aggregated or to be put in GROUP BY clause, so it requires ANY as internal aggregate operator, so the query :

SELECT COUNT(*), customerid

FROM customers

WHERE contactname = 'antonio moreno'

is not parsed by optimizer except you define GROUP BY for customerid.

As long as, you are aware that contactname could have more than one customerid, the following query is valid and it is considered valid to previous non correlated query :

SELECT

orderid

FROM orders JOIN customers

ON orders.customerid = customers.customerid

WHERE customers.contactname = 'Antonio Moreno'

Execution plan is :

|--Nested Loops(Inner Join, OUTER REFERENCES:([Northwind].[dbo].[Customers].[CustomerID]))

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), WHERE:([Northwind].[dbo].[Customers].[ContactName]=N'Antonio Moreno'))

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[CustomerID]), SEEK:([Northwind].[dbo].[Orders].[CustomerID]=[Northwind].[dbo].[Customers].[CustomerID]) ORDERED FORWARD)

This execution plan is simpler than previous one.

Now create unique index on contactname column at customers table and execute previous non correlated subquery, the execution plan is :

|--Nested Loops(Inner Join, OUTER REFERENCES:([Northwind].[dbo].[Customers].[CustomerID]))

|--Index Seek(OBJECT:([Northwind].[dbo].[Customers].[ix_1]), SEEK:([Northwind].[dbo].[Customers].[ContactName]=N'Antonio Moreno') ORDERED FORWARD)

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[CustomerID]), SEEK:([Northwind].[dbo].[Orders].[CustomerID]=[Northwind].[dbo].[Customers].[CustomerID]) ORDERED FORWARD)

For this case, proper index selection will remove some operator and execution plan will be simpler and more efficient because I tell optimizer that there is only one contactname for each customerid, so optimizer decides to choose index seek to get customer record with contactName = ‘Antonio Moreno’ and optimizer stops searching to other customerid with same contactName.

Share this post: | | | |

Index Union Analysis

Kasim.Wirama — Sun, 15 Jun 2008 12:22:00 GMT

Index Union Analysis

By : Kasim Wirama, MCDBA

When there are 2 or more where criteria which are joined with OR, each columns is different among others, they are indexed and search selectivity is high, optimizer could consider multiple indexes and join the end result with concatenation or merge join concatenation operator. Such a pattern of execution plan is called index union. Let’s explore what possibilities of execution plan generated from index union of a query statement.

Here is the query to get any ordered which is shipped or created between 1 January 2007 and 7 January 2007.

SELECT orderid
FROM orders
WHERE shippeddate BETWEEN '19970101' AND '19970107'
OR orderdate BETWEEN '19970101' AND '19970107';

Because there is index for shippeddate and orderdate column respectively, optimizer decides to use each indexes to get result from shippeddate BETWEEN '19970101' AND '19970107' and orderdate BETWEEN '19970101' AND '19970107'. Execution plan for the query is :

|--Sort(DISTINCT ORDER BY:([Northwind].[dbo].[Orders].[OrderID] ASC))

|--Concatenation

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShippedDate]), SEEK:([Northwind].[dbo].[Orders].[ShippedDate] >= 1997-01-01 00:00:00.000' AND [Northwind].[dbo].[Orders].[ShippedDate] <= 1997-01-07 00:00:00.000') ORDERED FORWARD)

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]), SEEK:([Northwind].[dbo].[Orders].[OrderDate] >= 1997-01-01 00:00:00.000' AND [Northwind].[dbo].[Orders].[OrderDate] <= 1997-01-07 00:00:00.000') ORDERED FORWARD)

Notice that result from 2 index seeks is joined with concatenation operator. Because the result might be duplicate, optimizer implement SORT DISTINCT operator to remove duplication. The query above could be rewritten as :

SELECT ordered
FROM orders
WHERE shippeddate BETWEEN '19970101' AND '19970107'
UNION
SELECT ordered
FROM orders
WHERE orderdate BETWEEN '19970101' AND '19970107';

Query rewrite above is valid because orderid is primary key which is also considered as unique key.

Now consider another query below :

SELECT orderid
FROM orders
WHERE shippeddate = '19970101' OR orderdate = '19970101';

Query plan for the query is :

|--Nested Loops(Inner Join, OUTER REFERENCES:([Northwind].[dbo].[Orders].[OrderID]))

|--Stream Aggregate(GROUP BY:([Northwind].[dbo].[Orders].[OrderID]))

| |--Merge Join(Concatenation)

| |--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShippedDate]), SEEK:([Northwind].[dbo].[Orders].[ShippedDate]='1997-01-01 00:00:00.000') ORDERED FORWARD)

| |--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]), SEEK:([Northwind].[dbo].[Orders].[OrderDate]='1997-01-01 00:00:00.000') ORDERED FORWARD)

|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), SEEK:([Northwind].[dbo].[Orders].[OrderID]=[Northwind].[dbo].[Orders].[OrderID]) LOOKUP ORDERED FORWARD)

Notice that stream aggregate and merge join concatenation appears instead of concatenation and SORT DISTINCT operator. Why does optimizer choose merge join and stream aggregate? It’s because that the query uses equality instead of inequality. Recall that merge operator will probably appear when a predicate contains at least one equality operator. From efficiency of resource utilization perspective, merge join and stream aggregate is better than sort operator because they do not require memory rather than SORT operator that might introduce data spill out to tempdb database when memory is under pressure.

The query above could be written as :

SELECT orderid
FROM orders
WHERE shippeddate = '19970101'
UNION
SELECT orderid
FROM orders
WHERE orderdate = '19970101';

Query plan for this query is :

|--Merge Join(Union)

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShippedDate]), SEEK:([Northwind].[dbo].[Orders].[ShippedDate]='1997-01-01 00:00:00.000') ORDERED FORWARD)

|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]), SEEK:([Northwind].[dbo].[Orders].[OrderDate]='1997-01-01 00:00:00.000') ORDERED FORWARD)

Merge join (Union) operator replaces Stream Aggregate and Merge join (concatenation).

Query rewriting is always generated from query plan analysis, and the process is fun and rewarding.

Share this post: | | | |

Peer-to-Peer Replication in SQL Server 2008

Kasim.Wirama — Sun, 15 Jun 2008 11:23:00 GMT

Peer-to-Peer Replication in SQL Server 2008

By : Kasim Wirama, MCDBA

One of interesting enhancements made in SQL Server 2008 February CTP is in area of replication, peer to peer replication particularly. Now with SQL Server 2008 February CTP, a SQL Server 2008 new node could join to existing peer-to-peer existing topology without having to stop database activity on the topology, and also visual designer is introduced in this upcoming SQL Server version. With visual designer, I just only to visually design, implement and review peer-to-peer easily especially when I have more than 3 nodes to be implemented, which are more complex peer-to-peer topology.

Peer-to-peer replication is located under transactional replication. By default, transactional replication turns off peer-to-peer replication. After you have set up publication with transactional replication, you need to open the publication property, and set value to true for enable peer-to-peer replication. Once you set it to true, you couldn’t revert back to transactional replication (by setting the value from true to false), the only way is to re-create the publication. Not like with other type of replication, peer-to-peer replication doesn’t need snapshot publication, uniqueidentifier column and trigger created on underlying table of an article. It doesn’t support re-initialization and timestamp type column because timestamp column is generated automatically by SQL Server so it couldn’t replicate to its peer. Another restriction of peer-to-peer replication is that it doesn’t support horizontal and vertical filtering. Besides that, peer-to-peer replication is not designed to handle conflict resolution even though it is able to able to detect conflict by turning on conflict detection. When conflict happens, data would not replicate to its peer node, thus manual intervention is needed to manually resolve conflict resolution. If you would like to automate conflict resolution with possible sophisticated scenario, merge transactional replication is the best replication option rather than peer-to-peer replication.

The least performance impact is belonged to peer-to-peer replication, and for high availability, better fault tolerance, and read performance enhancement in OLTP scenario, it is one of the viable options besides log shipping, and database mirroring.

Share this post: | | | |