Microsoft SQL Server Development Customer Advisory Team

Helping to make Hadoop easier by going Metro!

Denny Lee [MSFT] — Wed, 14 Dec 2011 18:27:45 GMT

We are proud to announce that the community technology preview (CTP) of Apache^TM Hadoop^TM-based Services for Windows Azure (or Hadoop on Azure) is now available. As noted in on the SQL Server Data Platform Insider blog, the CTP is by invite only http://blogs.technet.com/b/dataplatforminsider/archive/2011/12/14/availability-of-community-technology-preview-ctp-of-hadoop-based-service-on-windows-azure.aspx

While Hadoop is important to our customers for performance, scalability, and extreme volumes - as noted in our blog What’s so BIG about “Big Data”? – it is a slight paradigm shift for our us in the SQL community. Therefore, one of the great things about our new Hadoop on Azure service is that we’ve made Hadoop go all Metro – that is a Metro UI with a Live Tile implementation.

What is even cooler is that you can interact with with Hadoop, Hive, Pig-Latin, Hadoop Javascript framework, Azure DataMarket, and Excel using the Hadoop on Azure service – check out the Channel 9 video for Hadoop on Azure at: http://video.ch9.ms/ch9/6dc7/9022c2d7-e779-453c-bd82-9fb7017a6dc7/HadoopOnWindowsAzure_med_ch9.mp4 for more information.

We’re really excited about this CTP – so if you want an invite code, please check out the fill out the Connect survey: http://connect.microsoft.com/SQLServer/Survey/Survey.aspx?SurveyID=13697 (you may have to copy/paste this link). Invite codes will be given out based on a selection criteria basis so please be patient.

As well, check out all of the great documentation and content so you can keep up to date on Hadoop on Azure:

Apache Hadoop on Windows Resources: http://social.technet.microsoft.com/wiki/contents/articles/apache-hadoop-on-windows.aspx

Microsoft Hadoop Distribution Documentation Plan: http://social.technet.microsoft.com/wiki/contents/articles/microsoft-hadoop-distribution-documentation-plan.aspx

Apache Hadoop-based Services for Windows Azure How-To and FAQ Guide: http://social.technet.microsoft.com/wiki/contents/articles/apache-hadoop-based-services-for-windows-azure-how-to-and-faq-guide.aspx

Thanks!

Isotope PG and CX teams

A Computed Column Defined with a User-Defined Function Might Impact Query Performance

Kun Cheng — Mon, 28 Nov 2011 22:15:00 GMT

Author: Kun Cheng

Reviewers: Shaun Tinline-Jones, Silvano Coriani, Steve Howard, Thomas Kejser, Sanjay Mishra

A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators, but the expression cannot be a subquery. A simple example of a computed column is:

Col1	Col2	Computed_Col=(Col1+Col2)
100	100	200

A benefit of using computed columns is that they can save developers from having to write calculation logic at the application layer; at the table level, the application can simply reference the computed column. In addition, performance can be improved by persisting a computed column and building an index on it (see “Creating Indexes on Computed Columns” at http://msdn.microsoft.com/en-us/library/ms189292.aspx). In real-world scenarios (unlike the simple example above), a common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic. For example, the script below uses an XQuery expression to extract a summary description as a computed column out of the XML data, which is stored in another column of the same table. Once the computed column is persisted to the physical storage of the table, we can build an index on top of it to speed up queries that access the computed column.

CREATE FUNCTION udf_compcol(@contents XML) 
 RETURNS nvarchar(255)
 WITH SCHEMABINDING 
 BEGIN
 RETURN @contents.value('(/item/description/text())[1]', 'nvarchar(255)')
 END 
Go
ALTER TABLE Books
 ADD summary_compcol AS udf_compcol(Contents) PERSISTED
Go
CREATE INDEX ix_Books_summary ON Books(summary_compcol)
Go

In this example, we are promoting the “description” element out of the XML data to a relational column for performance gain, because querying against XML semi-structured data is slower than querying against structured data. However, the computed column might behave in an unexpected way that can impact query performance. One example of this unexpected behavior is discussed in the blog post “Query Plan for Persisted Computed Column Query” at http://sqlcat.com/sqlcat/b/msdnmirror/archive/2011/09/01/quoted-identifier-causes-unexpected-query-plan-for-persisted-computed-column-query.aspx.

In this blog post, I will describe another unexpected behavior of computed column that occurs in a different scenario. On a multi-core server, which is typical for a SQL Server environment, the following XQuery generates a parallel plan as it scans the XML data.

SELECT title FROM Books WHERE Books.Contents.exist(N'/item/isbn[.=7646309]') = 1

With the addition of the computed column defined by the UDF udf_compcol, the same query generates a sequential plan, which is identical to the original plan except it is not parallel.

The reason that the query generates a sequential plan in this case is that when SQL Server compiles a query plan, it loads all referenced column expressions and determines if there is a UDF associated with any computed column(s). If there is, SQL Server disables the parallel plan and generates only a sequential plan. This is what happened with our query, which referenced a table containing a computed column defined with a UDF. Note the behavior is the same regardless of whether computed column is persisted or not.

In this case, we were able to tune the query by promoting the “/item/isbn” element out of the XML data to another relational column so the query didn’t need to scan the table in the first place.

Here are some general recommendations:

Use computed columns wisely, and evaluate their performance impact with comprehensive testing against an established baseline.
If possible, use Transact-SQL instead of UDFs to define computed columns.
When promoting elements out of XML data to computed columns, a UDF is required. Consider tuning the impacted queries with XML indexes or promoting the referenced elements to relational column(s).

Writing New Hash Functions for SQL Server

Thomas Kejser — Mon, 28 Nov 2011 14:21:44 GMT

Author: Thomas Kejser

Contributors/Reviewers: Alexei Khalyako, Jerome Halmans, Fabricio Voznika, Sedat Yogurtcuoglu, Mike Ruthruff, Tobias Ternstrom and Steve Howard

In this blog, I will explore ideas for extending SQL Server with new, fast hash functions. As will be shown, the high speed, built in functions CHECKSUM and BINARY_CHECKSUM are not always optimal hash functions, when you require the function to spread data evenly over an integer space. I will show that it can be useful to extend SQL Server with a new CLR User Defined Function (UDF).

In distributed workloads, hashing a columns in the data and evenly distributing the hash value into buckets, is often a good way to evenly scale work over multiple scale units (either machines or NUMA nodes). In such an architecture, each scale-unit will be responsible for handling a subset of the total buckets. Achieving a good distribution in hash buckets will depend on the skew of the input data, but also on how well the hash function behaves on the input.

In this blog, I will use a simple input table to test the properties of different hash functions. For my example, let us imagine that the input values is 10M customer keys that have been generated either by a SQL Server 2010 SEQUENCER or an IDENTITY(1,1) column with no holes in the sequence. This table can be created like this:

CREATE TABLE CustKey (SK INT NOT NULL)

INSERT INTO CustKey WITH (TABLOCK) (SK)
SELECT n FROM dbo.fn_nums(10000000)

(The fn_nums function can be found here)

In this test, the input has no skew, and I will assume that we want to split the input into 65536 approximately equal sized hash bucket, spread all over the 16-bit integer space, as the desired output of the hash function. This means that ideally, the output will have 152 values (10M / 65536) in each bucket.

In other words: we are trying to achieve a mapping between a sequence of values in the 32-bit integer space to an evenly spread out, 16-bit integer space like this:

For convenience, I will use 64K = 65536 and 32K = 32768 below.

Using CHECKSUM and BINARY_CHECKSUM

The built in functions CHECKSUM and BINARY_CHECKSUM both operate on any input and return a 32-bit, signed integer (SQL type: int). By integer dividing the output by 64K we can split the integer space into the 64K buckets we want.

With the following query, we can get a rough idea of the distribution in the buckets:

WITH Input (bucket, bucket_count) AS
(
SELECT BINARY_CHECKSUM(SK) / 65536 as bucket
, COUNT(*) as bucket_count
FROM CustKey
GROUP BY BINARY_CHECKSUM(SK) / 65536
)
SELECT MIN(bucket_count)
, MAX(bucket_count)
, COUNT(*) filled_buckets
, SUM(bucket_count)
FROM Input

Running this for both BINARY_CHECKSUM and CHECKSUM, I get:

This does not look promising, we would expect the min/max to linger around 152 and the number of filled buckets to be 64K. Books Online does state:

[…] we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

So, it seems we should use HASHBYTES.

Using HASHBYTES

HASHBYTES with MD5 returns a 160 bit value. We can turn this into 64K buckets by taking modulo 32K on the output. We can now write

WITH Input (bucket, bucket_count) AS
(
SELECT HASHBYTES('MD5', CAST(SK AS VARBINARY)) % 32768 as bucket
, COUNT(*) as bucket_count
FROM CustKey
GROUP BY HASHBYTES('MD5', CAST(SK AS VARBINARY)) % 32768
)
SELECT MIN(bucket_count)
, MAX(bucket_count)
, COUNT(*) filled_buckets
, SUM(bucket_count)
FROM Input

As I run this, I notice that it take very long to run. But the output is quite good compared to BINARY_CHECKSUM and CHECKSUM:

To benchmark the speed of the hash function, we need to write this little script that minimizes the transmission time to the client:

SET STATISTICS TIME ON

SELECT MAX(bucket)
FROM(
SELECT HASHBYTES('MD5', CAST(SK AS VARBINARY)) % 32768 as bucket
FROM CustKey
) AS input
OPTION (MAXDOP 1)

On my machine, the CPU time is around 14 seconds, about one millisecond per hash calculation. Note that MD5 has cryptographic properties that we may not need. Are we paying too high an overhead for this? I tested with the other hash functions in HASHBYTES, and all of them take a lot of CPU per hash calculation

Building a SQLCLR function on Binary data

At this point, it seems natural to ask if there is a way to write our own hash function. Fortunately, SQL server provides a great way to extend the engine with new functionality: SQLCLR.

In order to explore this option, I wanted to quantify the cost of marshaling data from SQL to CLR, this simple function was my initial test:

Running the performance test above, I saw a shocking 56 seconds, just to return an integer!

At times like these, it is very useful to run xperf, I ran the following command line before starting the test again:

xperf –on latency –stackwalk profile

After about one minute, when the test was done, I ran:

xperf –d C:\dumps\nohash_trace.etl

This allows me to quantify all the CPU time spend in SQL server, the result was very interesting. On my 4 core machine, the single threaded execution (25% of the CPU) breaks down like this:

With a properly configured symbol path, we can actually zoom into sqlservr.exe and see what is going on (you can do this too, it does not require source code access – see this link on how to get started)

UDFInvokeExternal sounds like the call that wraps my CLR. But wait a minute. What are those UrtReadLob, UrtGetLobLength and ExecUdfLobAccess in there? They add up to more than the time taken for the highest CPU consumer (GetNextRowValuesInternal).

Looking up SqlBytes on MSDN, we find:

“Represents a mutable reference type that wraps either a Buffer or a Stream.”

This doesn’t sound like the type I wanted: buffer and streams – perhaps this is where LOB’ish calls are from? My next attempt replaces SqlBytes with SqlBinary:

Runtime with the new data type: 7 seconds. A factor 8 improvement! This is also faster than the HASHBYTES run. If I could come up with a hash function that has a good spread, but which is cheaper than MD5…

Testing CRC32 and CRC16

Fortunately, the field of computer science is full of work about hash functions and you can pick one that fits your needs. Two easy to implement functions are CRC32 and CRC16. You can find reference implementation in Wikipedia.

CRC32 returns a 32-bit integer, and we can turn it into the 64K buckets by dividing with 64K. CRC16 on the other hand, returns exactly what we want.

Without further ado, here are the results I obtained by writing my own hash CRC16/32 function:

Conclusion

If you need hash functions that spread data evenly over an integer space, you have to choose the hash function carefully. The built in hash functions BINARY_CHECKSUM and CHECKSUM, while very fast, do not provide a good spread over a 16 bit integer space. The built in SQL Server HASHBYTES offers good spread over the the space, but comes a high computation cost.

If you do not need the cryptographic properties of the HASHBYTES function, you can consider writing your own SQLCLR function to calculate hash values. With a properly chosen hash algorithm, and by avoiding the SqlBytes data type, you can hash values faster than HASHBYTES, while maintaining the benefits of a good spread over the integer space.

What’s so BIG about “Big Data”?

Denny Lee [MSFT] — Tue, 15 Nov 2011 05:54:00 GMT

As announced during the PASS Summit 2011 Day One Keynote, we are diving deeper into the world of Big Data by embracing and contributing to the open source community and Hadoop.

We’ve had a lot of good coverage on this topic with some examples below.

Openness – yes, we’re serious about it!

A key aspect is openness and our commitment to give back to the Open Source community. While this is just a small start, we made a first step forward with SQL Community ambassadors Lara Rubbelke (@sqlgal) and Bronwyn McNutt (@MissBronwyn) leading the effort at one of the ultimate open source conference – ApacheCon North America 2011.

Hadoop on Azure / Windows Server Founder and General Manager Alexander Stojanovic (@stojanovic), Dave Vronay (@davevr), and I had also joined the festivities to introduce ourselves and listen to the Apache community (and we learned a lot!). Luis Daniel (@luisdans) from our product planning team also joined – this is just the beginning but should show our commitment to openness! More to come on that in future blog posts.

@dsfnet had appropriately tweeted “@dennylee You are really taking this Hadoop/Apache thing to heart :)”.
Yes – we are!

BTW, for more thoughts on the openness aspect, please reference my own personal blog on the topic: The aggressive optimism of Hadoop.

But why is Hadoop important to us?

But from a CAT perspective, why is Hadoop important? The straightforward answer is that it is important for our customers.

As you may know from our team’s blog posts, technical notes, and whitepapers; we work on some of the most complex Tier-1 Enterprise SQL Server implementations. The last few years, we’ve seen more and more customers talk about their Big Data problems. Buck Woody (@buckwoody) described these problems the best as “Big data is defined as a large set of computationally expensive data that is worked on simultaneously” in his post Big Data and the Cloud - More Hype or a Real Workload?

As Alexander Stojanovic, Founder and General Manager of Hadoop on Azure / Windows Server, noted during the ApacheCon 2011 North America Meetup:

It’s not just your “Big Data” problems, it’s about your BIG “Data Problems”

A great example of one of these BIG “Data Problems” is the Yahoo! 24TB Analysis Services cube – the largest known cube! The cube’s data source is 2PB from a huge Hadoop cluster.

Following the 4 V’s of Big Data, Yahoo! not only processes a massive volume of campaign web analytics data, but they have to quickly make decisions based on the incoming events (i.e. velocity). Both the variety (many different formats of data) and variability (many different ways of interpreting this data) of the data coming in can be intense hence the importance of using Hadoop. For fast interactive querying of the data, Yahoo! is using Analysis Services to pivot against all of this interesting data.

Another great posting on this very topic from Klout: Big Data, Bigger Brains
…Yes, you read it right. We use a Windows based multi-dimensional (MOLAP) product from Microsoft to load 350 million rows of Hive data per day and achieve an average query response time of under 10 seconds on 35 billion rows of data

For more information, you can also refer to the PASS 2010 Summit Day One Keynote and/or the PASS 2011 Session “SQLCAT: Tier-1 BI in a world of Big Data”. And yes, we are working on a case study as we speak!

Enjoy!

Denny

Successfully execute an INSERT, UPDATE and DELETE against a Database Snapshot

shauntj-us — Tue, 18 Oct 2011 02:06:00 GMT

Author: Shaun Tinline-Jones

Reviewers: Mike Ruthruff, Sanjay Mishra, Alexei Khalyako

Not too long ago an ISV that developed solutions using SQL Server as the RDBMS, asked me how they could query a database as at a point in time. This was a relatively easy answer, thanks to the Database Snapshot feature. I was however surprised at the next question “Can we update the database snapshot?”

A reactive response is “No. You cannot update a Database Snapshot”

Msg 3906, Level 16, State 1, Line 1
 Failed to update database "Orig_Snapshot" because the database is read-only.

A creative answer is a tentative “….well maybe…depending on what the objective is?”

This blog demonstrates that it is possible to run an INSERT, UPDATE or DELETE against a Database Snapshot. This will not update the snapshot, but rather the database that has a "Database Snapshot" associated to it.

Imagine a scenario where a reconciliation of data at a point in time must be carried out. Database Snapshot provides the ability to present the data as at a point in time, however the common understanding is that any compensating modifications requires a second connection or a USE statement. The USE statement is not permitted in a database module:

Msg 154, Level 15, State 1, Procedure testSP, Line 4
a USE database statement is not allowed in a procedure, function or trigger.

Listing 1 creates the objects needed to prove it is possible to successfully execute DML statements against a Database Snapshot.

    IF DB_ID('Orig') IS NOT NULL
        DROP DATABASE [Orig]
    GO
    
    CREATE DATABASE [Orig]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig.mdf')
    GO
    USE [Orig]
    GO
  
    IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
        DROP TABLE dbo.TestTable
    GO
    
    CREATE TABLE dbo.TestTable (Col1 int)
    GO
    
    IF OBJECT_ID('dbo.UpdView', 'V') IS NOT NULL
        DROP VIEW dbo.UpdView
    GO
    
    CREATE VIEW dbo.UpdView
    AS
    SELECT Col1 FROM Orig.dbo.TestTable
    GO
    
    INSERT INTO dbo.TestTable (Col1) VALUES (1)
    GO
    
    IF DB_ID('Orig_Snapshot') IS NOT NULL
        DROP DATABASE [Orig_Snapshot]
    GO
    
    CREATE DATABASE [Orig_Snapshot]
     ON PRIMARY ( NAME = N'Orig', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL02\MSSQL\DATA\Orig_Snapshot.ss')
     AS SNAPSHOT OF [Orig]
    GO
    
    USE [Orig_Snapshot]
    GO
    
    SELECT * FROM dbo.UpdView
    GO

After running the above code, you should have a database and an accompanying snapshot of that database. Within the database, you’ll have a table with at least 1 row in it and a view that simply returns the contents of that table. This view is a fully qualified name of the original database, this creates 2 scenarios. One is that it becomes possible to view the originating database from within the snapshot, and the second is the ability to run DML statements against the source database from within the database snapshot.

Listing 2

    INSERT INTO dbo.UpdView VALUES (2), (3);
    UPDATE dbo.UpdView SET Col1 = 99 WHERE Col1 = 3;
    DELETE FROM dbo.UpdView WHERE Col1 = 1;
    SELECT * FROM dbo.UpdView
    GO

Running the code in listing 2, you should have a result set as shown below:

In conclusion, the error message that informs us the database is read-only, while identical to the message returned when writing to a Read-Only database, is only partially accurate in the context of a Database Snapshot. Questions that come to mind are:

Is it a bug?
Would this be considered a good practice?
What does the code management look like?
What useful scenarios could leverage this insight?

Let me know if you think it’s a bug. From my perspective, this makes sense as the database snapshot is actually a read/write database, persisting older values as records change. Additionally, we are not actually updating the Database Snapshot, the changes are still made directly to the main database.

As far as been a good or recommended practice, my reservations about using this in a design are:

Views must be created for each table that can be updated from the database snapshot
Separate objects/statements must be created for DML operations that already exists for the main table
It isn’t intuitive in terms of troubleshooting code. In fact, if you start down this road you may end up with one of those horrendous applications that have layers upon layers of views ultimately leading to poor performance.

It does however reduce the cost of creating and managing a new connection, especially if the DML statements are a result of a query that originated from the snapshot. Additionally, the logic could reside in the same SP as the query, allowing for conditional logic.

Can you think of other scenarios, pitfalls or benefits of updating the main database via a database snapshot?

SQLPASS 2011 SQLCAT Track

Denny Lee [MSFT] — Sat, 08 Oct 2011 21:05:00 GMT

The SQLPASS 2011 summit is upon us again and all of our flocking to our fair city Seattle for our yearly technical-and-karoke fest! If you are up for some technical deep dives, here is the list PASS sessions presented by SQLCAT! And don’t forget to check out the Birds of Feather event – the tech talk luncheon.

Session Code	Session Name	Speakers
DBA-414-M	Are You Smarter Than An MCM?	Cindy Gross, Jimmy May, Lara Rubbelke, Pam Lahoud, Robert Davis,
DBA-323-M	Improving Your PowerShell Productivity	Dan Jones,
AD-313-M	SQL Server Engine Team - Unplugged	Alex Verbitski, Ashit Gosalia, Cihan Biyikoglu, Cipri Clinciu, Dana Kaufman, Eric Kang, Gus Apostol, Justin Erickson, Rohan Kumar, Sunil Agarwal, Susan Price,
AD-405-M	SQLCAT: Building Highly Scalable Cloud Analytics Solutions with SQL Azure and StreamInsight (Project Austin)	Mark Simms,
BIA-409-M	SQLCAT: Configuring and Securing Complex BI Applications in a SharePoint 2010 Environment with SQL Server Code Name "Denali"	Carl Rabeler, Chuck Heinzelman,
DBA-413-M	SQLCAT: Enterprise SQL I/O Optimization Best Practices	Jimmy May, Mike Ruthruff, Thomas Kejser,
DBA-332-M	SQLCAT: HA/DR Customer Panel -- SQL Server Code Name "Denali" AlwaysOn Deployment Consideration	Ayad Shammout, David Smith, Michael Steineke, Sanjay Mishra, Thomas Grohser,
DBA-324-M	SQLCAT: Optimize SQL Server for Private Cloud part2: Best Practices and Lessons Learned	Alan Cranfield, Lindsey Allen, Xin Jin,
DBA-328-M	SQLCAT: SQL Server Consolidation at Travelers – How we did it and Lessons Learned	Bob Crowley, Prem Mehra,
DBA-325-M	SQLCAT: SQL Server HA and DR Design Patterns, Architectures and Best Practices using SQL Server Code Name "Denali" AlwaysOn	Sanjay Mishra,
AD-406-M	SQLCAT: SQL Server Tier-1 Mission Critical Application at Progressive – Architecture Deployed and Lessons Learned	Brian Durkin, David Wilson, Prem Mehra,
BIA-408-M	SQLCAT: Tier-1 BI in world of Big Data	Denny Lee, Thomas Kejser,
AZ-302-M	SQLCAT: What are the Largest Azure Projects in the World and how do they Scale	Kevin Cox, Michael Thomassy,
DBA-326-M	SQLCAT: What are the Largest SQL Server Projects in the World and how do they Scale	Kevin Cox, Nicholas Dritsas,

Enjoy!

Maximizing SQL Server Throughput with RSS Tuning

Kun Cheng — Tue, 27 Sep 2011 02:55:00 GMT

Author: Kun Cheng

Reviewers: Thomas Kejser, Curt Peterson, James Podgorski, Christian Martinez, Mike Ruthruff

Receive-Side Scaling (RSS) was introduced in Windows 2003 to improve Windows scalability to handle heavy network traffic, which is typically the case for SQL Server OLTP workload. For more details about RSS improvement on Windows 2008, please check out the whitepaper - http://msdn.microsoft.com/en-us/windows/hardware/gg463253.aspx and the blog - http://sqlcat.com/sqlcat/b/msdnmirror/archive/2008/09/18/scaling-heavy-network-traffic-with-windows.aspx.

I was recently working with a partner to test a high scale SQL load against a DL980 server with 8 sockets, 80 physical cores running Windows 2008 R2 sp1. The SQL box had 4 NIC (1Gbps) cards to handle network traffic between SQL and application servers. To our surprise, we observed during the test only 2 out of 80 CPU cores maxed out with almost all of it come from privileged/DPC % time. We knew Windows 2008 R2 by default reserves up to 4 CPU cores as designated RSS handlers (see above white paper). Why would it only use 2 in our environment? Note DPC time could be driven by device drivers such as network, storage drivers. You can use Windows XPerf tool (http://msdn.microsoft.com/en-us/performance/cc752957.aspx) to trace and identify the main consumer(s) of DPC time at driver level. For example, in our case, most of it was consumed by NDIS.sys, which falls under network driver.

Suspecting that the above behavior might be just Windows bug, I explicitly set the MaxNumRssCpus registry value as 8 in HLKM\System\CurrentControlSet\Services\Ndis\Parameters (see above white paper) hoping it would give us extra CPUs to handle network packet interrupts. Well, that didn’t make a difference. I reached out to our friends at Windows networking group seeking answer to the puzzle. While waiting for a response, I asked my partner to try to connect application servers to all 4 NIC cards (we divided evenly the instances of the applications to communicate to each separate IP address). Bingo, we see now 8 cores with expected high DPC % CPU time.

So based on our try/error testing, it appeared that each NIC/IP can only use 2 CPUs for RSS. What were we missing? Windows networking team responded later and gave us suggestion to check “RSS rings” setting for each NIC. Note the term varies between different NIC vendors. Some refer to “RSS Queues”, which is the same thing. Essentially, each CPU core that a NIC uses will need an associated ring when RSS is enabled. In other words, the number of RSS CPUs on a system would be capped by both MaxNumRssCpus registry key and “RSS rings” NIC property setting whichever is the lowest. Once we went to check the NIC setting (properties – advanced tab), it’s indeed set as 2, which explained why we saw only 2 CPU cores being utilized for RSS when single NIC card was used. Remember the setting of “RSS rings” varies between hardware. In our case, each NIC supported up to 4 “RSS rings” so we can’t go above 4 RSS CPUs for each card (see figure 1 below). Also keep in mind RSS CPUs can only be assigned to the first K group on Windows 2008 R2 or prior. Check out this blog about DL980 configuration including K group - http://blogs.msdn.com/b/saponsqlserver/archive/2011/06/10/customer-proof-of-concept-on-new-hp-dl980.aspx

Figure 1

In the end, we continued to use our “workaround” of scaling network load out to 4 NIC cards, which gave us enough network bandwidth as well as RSS CPUs to handle the heavy network traffic. You can certainly use more powerful 10Gbps NIC, but remember to configure “RSS rings” to a proper value.

In summary, follow these RSS tuning rules to configure a SQL Server box to handle high network traffic:

At Windows level, configure number of max RSS CPUs (and optional “starting RSS CPU”) by following http://msdn.microsoft.com/en-us/windows/hardware/gg463253.aspx
At individual NIC level, configure “RSS Rings” (“RSS Queues”) to proper value so the sum would match the value of MaxNumRssCpu of Windows setting. Note the max configurable value of the NIC setting varies between different vendors. You might have to upgrade or install additional cards to support a heavy network workload.

“QUOTED_IDENTIFIER” causes Unexpected Query Plan for Persisted Computed Column query

shauntj-us — Wed, 31 Aug 2011 16:09:00 GMT

Author: Shaun Tinline-Jones

Technical Reviewers: Thomas Kejser, Steve Howard, Jaime Alva Bravo, Kun Cheng, Jimmy May

Note: Validation for this post was performed in the SQL CAT Customer Lab on an HP Proliant DL580 G7, Intel Xeon Nehalem E7-4870 2.40 GHz 4 socket, 10 physical cores, 20 logical cores for a total of 40 physical cores, 80 logical cores; 1TB RAM. SQL Server 2008 R2 was installed on a Fusion-io ioDrive Duo 1.28TB MCL using driver version 2.3.1

We recently engaged a Tier 1 Global ISV in our labs, where the objective was to achieve the highest Business Transactions for their application.

During the testing and optimizing we encountered an interesting behavior where we kept incurring a clustered index scan; irrespective of applying known techniques to generate an index seek. In this blog, I will share the reproducible steps that reveal the following observation:

“If the table has a persisted computed column*, the query optimizer will choose a clustered index scan over a clustered index seek.”

*This applies to the case, which is most common, where the compute expression doesn’t produce a constant.

Here is the code to create the table & SPs that we’ll refer to for describing this behavior:

IF OBJECT_ID('dbo.testComputedCol') IS NOT NULL
DROP TABLE dbo.TestComputedCol
GO

IF OBJECT_ID('dbo.GetResults01') IS NOT NULL
DROP PROC dbo.GetResults01
GO

IF OBJECT_ID('dbo.GetResults02') IS NOT NULL
DROP PROC dbo.GetResults02
GO

CREATE TABLE dbo.TestComputedCol (Col1 int PRIMARY KEY CLUSTERED, Col2 int)
GO

CREATE NONCLUSTERED INDEX NDX_TestComputedCol_Col2 ON dbo.TestComputedCol (Col2)
GO

SET NOCOUNT ON

DECLARE @Count int = 1;

WHILE @Count < 1000
BEGIN
INSERT INTO dbo.TestComputedCol (Col1, Col2) VALUES (@Count, @Count*10)

SET @Count += 1
END
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC dbo.GetResults01
AS
SELECT * FROM dbo.TestComputedCol WHERE Col1 = 10
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC dbo.GetResults02
AS
SELECT * FROM dbo.TestComputedCol WHERE Col1 = 10
GO

To baseline the scenario we are describing, we run both SPs and confirm that we experience the index seek operation.

EXEC dbo.GetResults01

EXEC dbo.GetResults02

In our scenario we recognized the need to partition the data to avoid the last page insert contention issue and therefore implemented a computed column, similar to the below statement. While creating the column it is documented that the Quoted Identifier must be ON in order to create the computed column.

Side Note: For resolving the Last Page Insert Contention challenge, we are left with two choices, troubleshoot the reason for the differing behavior or change the insert behavior to include setting a value for the partition bucket. We chose to troubleshoot the issue. This blog is not striving to optimize the partition hash; however it’s important to recognize that resolving the Last Page Insert Contention through a computed column is not optimal.

In its literal sense QUOTED_IDENTIFIER is required to parse quoted strings as identifiers. A recommended practice is to rather use square brackets “[” as an identifier. Therefore it’s not intuitive that the QUOTED_IDENTIFIER setting influences queries against computed columns.

NOTE: When a table is created, the QUOTED_IDENTIFIER option is always stored as ON in the metadata for the table, irrespective of the current setting. However when creating a computed column, the setting must be set to ON.

SET QUOTED_IDENTIFIER ON
GO

ALTER TABLE dbo.TestComputedCol
ADD ComputedCol AS CAST(Col1%10 AS tinyint) PERSISTED NOT NULL

In our solution, prior to adding the computed column, a frequently called SP generated a query plan very similar to what you see below:

EXEC dbo.GetResults01

However after the computed column was added, executing the same SP generated the following query plan.

At first this may seem unexpected, and frustrating. However, some may infer that this is expected behavior, as described in article Creating Indexes on Computed Columns. Take note that this is an inference or a source of data points, as we did not add an index to the computed column. Does this happen if the SELECT and WHERE columns are not referencing the computed column? We modified the SP to follow the recommended practice of explicitly specifying only the required columns,

SELECT Col1, Col2 FROM dbo.TestComputedCol WHERE Col1 = 10

Unfortunately, you cannot shake the scan this way!

The only way to resolve the issue is to ensure the QUOTED_IDENTIFIER to ON during the execution of the batch. The challenge is finding the right place to ensure the effective setting at runtime.

Side Note: In this scenario a computed column will be the partitioning column, which requires a PERSISTED property. The above issue doesn’t occur if the computed column is not persisted.

The default value for QUOTED_IDENTIFIERS is ON; however it can be changed at a variety of points. Therefore you may not encounter the above issue, yet when it does occur it may not be intuitive to check the QUOTED_IDENTIFIER setting or even at which point along the path it has been changed.

The setting can be applied:

As a connection attribute
As a database option
As an object attribute
As a runtime SET option

In our case, we unexpectedly started incurring the index scan. We identified these index scans when the table partitioning strategy didn’t produce the benefits we sought. Troubleshooting revealed that when we ran the code (from the SP) as an isolated batch in a Query Analyzer window, we got the seek behavior. If we ran the SP in the same Query Analyzer window but in a separate batch, we got the scan behavior. When we ran the code using the osql utility, we got scan behavior for both batch executions. If we changed the computed column to a constant value, then we got the seek behavior.

The clue to finding a resolution is in the differences between the osql.exe and SSMS outcomes. These utilities have different defaults for connection attributes. The resolution lies in creating the stored procedure with QUOTED_IDENTIFIERS set to ON. An interesting observation is that it’s redundant to set the quoted identifier setting within the stored procedure; the setting at creation time will take precedence.

Let me share with you how ALM (Application Lifecycle Management) practices of the ISV caused this issue to slither its way into the solution.

NOTE: Keep in mind that there are many variations of what I’m about to describe

A developer creates a module, such as an SP, using a tool such as SSMS, VSTS, osql, sqlcmd, and others but most often SSMS. When the developer is done unit testing, a script file is checked into the source control system, below is an example of such a file.

CREATE PROC dbo.GetResults01
AS
SELECT * FROM dbo.TestComputedCol WHERE Col1 = 10
GO

The Build Process then generates a deployment script. There are a variety of ways that deployment scripts can be generated and run against the target SQL Server and database instance. The point in the above scenario is that the QUOTED_IDENTIFIER setting for this object is dependent on the current connection attribute of the deployment script generator.

If the deployment script does not specify QUOTED_IDENTIFIER setting for the objects, then the setting will be dependent on the connection settings when the script is run. The Build Process for the ISV leverages the osql.exe utility, which has a default setting of OFF. It’s not uncommon for build processes to use the SQLCMD.exe, which would cause the same behavior.

NOTE: If the connection does not set this attribute, then the database setting will take precedence. By default, ODBC and OLEDB set the option to ON. For DB-Library the default is OFF. Therefore it is very unlikely that the database default is ever enforced.

Tool	Default Quoted Identifier Setting
osql.exe	OFF
sqlcmd.exe	OFF
SQLCMD Scripts	ON
SSMS	ON

Below are T-SQL queries to identify current QUOTED_IDENTIFIER settings:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsQuotedIdentifiersEnabled')
SELECT * FROM sys.databases WHERE is_quoted_identifier_on = 0
SELECT quoted_identifier, * FROM sys.dm_exec_sessions WHERE session_id = @@SPID

They each have cases where they are useful, though I particularly like:

SELECT * FROM sys.objects
WHERE OBJECTPROPERTYEX(object_id, 'IsQuotedIdentOn') = 0 AND TYPE <> 'S'

This last query reveals those objects that deviate from the recommended standard of having QUOTED_IDENTIFIER set to ON.

In conclusion…there are connection settings that can influence the behaviors of the queries and output. Therefore it’s important to standardize on a setting, the more aligned it is with the industry defaults the easier it is to adhere to the standard.

Historically, prior to computed columns, quoted identifiers were used merely to distinguish strings from literals. It is recommended that literals are identified by square brackets, and strings are defined by single quotes. It is further recommended to keep quoted identifier setting to ON in all cases, even if it means explicitly adding this to script files before objects are created.

For our scenario, we conclude that it’s not intuitive that the reason behind the index scan is related to a quoted identifiers setting, therefore we standardize that the setting be ON and run the above queries to validate all appropriate objects have this setting to ON as part of validating a build deployment.

SSAS Maestros August 2011 Update

Denny Lee [MSFT] — Tue, 23 Aug 2011 18:02:14 GMT

As noted in our blog post Announcing SSAS Maestros v1.2, we had a great Analysis Services Level 500 deep dive sessions in Redmond and Madrid back in June/July 2011. Saying this, we do have some quick updates on the SSAS Maestros current status:

Annoucing the first set of SSAS Maestros:

We have completed the evaluations of the v1.0 Maestros (Feb/Mar 2011) attendees and we will be notifying all of those attendees within the next few weeks - best of luck!

SSAS Maestros v1.2 evaluations

We are currently working on the logistics behind the SSAS Maestros evaluations taught by SQLCAT and our partners - Chris Webb [blog], Marco Russo [blog | twitter], Stacia Misner [blog | twitter], and Adam Jorgensen [blog | twitter].

SSAS Maestros gets a great boost!

As well, we're glad to have the intrepid Robert Davis (@sqlsoldier) helping us with the logistics surrounding SSAS Maestros - more to come shortly!

Thanks from your friendly SQLCAT SSAS Maestros

- John Sirmon [blog | twitter], Thomas Kejser [blog | twitter], and Denny Lee [blog | twitter]

Unintended Consequences of Scalar-Valued User Defined Functions

ChuckHeinzelman — Fri, 24 Jun 2011 21:05:09 GMT

Author: Chuck Heinzelman

Reviewers: Kevin Cox, Dan Jones, Lara Rubbelke

During a customer engagement, we noticed a query that was taking a long time to execute. We saw that the query had a scalar-valued User Defined Function (UDF) encapsulating functionality in the SELECT clause. To understand why this could be a problem, you need to understand how SQL Server deals with functions in the SELECT clause. Let’s take a quick dive into how this works in SQL Server.

Assume that I have a table with the following structure:

CREATE TABLE dbo.Numbers

(

Number integer NOT NULL PRIMARY KEY CLUSTERED

);

Now assume that this table has 100,000 rows in it – numbered 1 to 100,000. As an aside, I like to have a table like this with about 1,000,000 rows in it for occasions where I need to duplicate data. You never know when you will need a table of numbers!

If I execute the following SQL Statement, what will I get?

SELECT Number, GetDate() AS CurDate

FROM dbo.Numbers

WHERE Number <= 1000;

If you answered 1000 rows with the numbers 1 - 1000 and the exact same value for the CurDate column in every row, you would be correct. This is because SQL Server only executes the GetDate() function once for the entire statement.

Now assume that I create the following function:

CREATE FUNCTION dbo.GetDateFunction()

RETURNS DateTime

BEGIN

RETURN GetDate();

END

If I execute the following SQL Statement, what will I get?

SELECT Number, dbo.GetDateFunction() AS CurDate

FROM dbo.Numbers

WHERE Number <= 1000;

If you said that my results would be the same as in the last example, you would be incorrect. Since we encapsulated the GetDate() function in our own user-defined function, SQL Server had to execute it for every row returned, not just once for the entire query. This knowledge is fundamental to understanding the performance of the queries that you write as well as ensuring that you get the results that you expect.

Unfortunately, our problem was not this simple. We were dealing with encryption and decryption functions. Take the following query as an example:

SELECT RowID, DecryptByKey(EncryptedColumn)
FROM dbo.TableWithManyRows;

In this case, even though the DecryptByKey function is a built-in function, it will be called once per row (each individual value needs to be decrypted). Given that, what would be the harm of having this function embedded in a user defined function like this:

CREATE FUNCTION dbo.DecryptByKeyFunction(@Data varchar(255))

RETURNS varchar(255)

BEGIN

RETURN DecryptByKey(@Data);

END

And using it like this:

SELECT RowID, dbo.DecryptByKeyFunction(EncryptedColumn)
FROM dbo.TableWithManyRows;

Implementing it this way would allow you to easily swap out the encryption functionality for your entire system by changing a single function rather than changing every query that uses this logic.

The problem is that while the inline DecryptByKey function is called for each row in the query, the key is only opened once for the entire statement. If you put the DecryptByKey function in your own user defined function, the key will need to be opened for each function call rather than once for the entire query. In our scenario, this caused a huge performance issue.

Summary

Knowing the behavior of built-in and User Defined Functions is extremely important to understanding the performance and output of the queries that you write. I am not trying to tell you to never use UDFs – just be sure to test your solutions thoroughly and understand all of the “unintended consequences” of encapsulating logic inside a UDF before rolling your solution out – even to your developers.

Why can't I attach a database to SQL Server 2008 R2?

Kevin Cox SQL Server — Mon, 20 Jun 2011 14:41:49 GMT

Recently, a problem was presented to me by a DBA using SQL Server 2008 R2. He could not attach a database whose files
had been copied from another server. This blog talks about the situation and the solution.

Here are the steps he went through to arrive at his situation.

DBA #1 logs into a remote server #1 using SSMS from his desktop and detaches a database.
DBA #2 copies the files to SQL Server #2 and using SSMS on his desktop attempts to attach that database.
DBA #2 gets an error saying he does not have permissions to attach the database. (Did not capture the exact error message).

Other pertinent information:

Both DBAs are in the sysadmin role on both SQL Server machines.
Both DBAs are in the local admin group on both SQL Server machines.
Neither person is a domain admin.
Did not run SSMS with the “run as administrator” option.
UAC is turned on for the Windows 7 client running SSMS on both of the DBA machines.
Both SQL Servers are running Windows 2008 R2 with latest service pack and SQL Server 2008 R2 with latest service pack.
The SQL Servers are in different domains (big clue #1).
The domains are not trusted. (big clue #2).
There are no certificates involved.
SQL Server service accounts are Local accounts but are not in the local administrators group.

My first thought is that because both DBAs are local admins and sysadmins the attach database command should succeed. A quick
check of Books on Line (BOL) brings this explanation of why it did not work: http://msdn.microsoft.com/en-us/library/ms189128.aspx.

The reason this did not work initially is because two different people did the detach and attach operations as explained in the BOL link. So the files were owned by DBA 1. However, DBA 2 was a local administrator so he had access to the directory
where the files were stored, so he could copy the files. But the attach failed on server 2 because DBA 2 was not the file owner.

To get this to work, we went into the copied file properties for the .mdf and .ldf files and changed them to be owned by the local SQL Server service account. Then the attach worked. It would also have worked to change the file owner to DBA 2, the one issuing the attach command.

To test the scenario where the same person does the entire process, we created a sample database on server 1 and used the same person to detach, copy and attach the databases. The same servers involved in the original problem were used. This time the attach operation worked.

We noticed something interesting during this test. After creating the sample database, the mdf and ldf files (while still attached) are owned by the SQL Server service account. Use Windows Explorer and properties of the file to see the owner. Then we detach the files, the owner becomes the person who did the detach command. After copying the files to server 2, the ownership remains the same; i.e. the person who issued the detach command. After the attach command is issued on server 2, the file owner becomes the new SQL Server service account.

Impersonation is important

This test worked because the same person did the entire process. Whether you use SQL authentication or Windows authentication to log into the database via SSMS, the process works as long as the same person does the entire process and as long as the connecting account can be impersonated.

Conclusion

File ownership can be a bit confusing. It makes a difference whether the two servers are in the same or different domains and it makes a difference if the same person does the entire process. It all comes down to knowing whether your
account is being impersonated on either server involved.

Author:
Kevin Cox (SQLCAT), Cindy Gross (Microsoft Premier Field Engineering)

Contributors: Chuck Spencer (Pearson/eCollege)

Reviewers: Sanjay Mishra, Shawn Tinline-Jones, James Podgorski

Unexpected Consequences of Multiple Result Sets

ChuckHeinzelman — Tue, 31 May 2011 18:24:31 GMT

Author: Chuck Heinzelman

Reviewers: Kevin Cox, Kun Cheng, Michael Thomassy

In a recent customer engagement, I was presented with a problem that I have seen in the past and am surprised that I don’t see more often.

Take the following table as an example:

CREATE TABLE dbo.ResultSetTest

(

ID integer IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

TestData varchar(255) NOT NULL

);

This is a simple table with an Identity column – I’m sure most of us have a table like this (with more columns, of course) in the systems that we work with.

The customer had code to insert a new row into the table and return to the calling application the value assigned to the ID column. The code was similar to the following:

INSERT INTO dbo.ResultSetTest

(TestData)

VALUES
(‘Test’);

SELECT @@Identity AS ID;

Given this T-SQL batch, how many result sets would you expect to receive? If you answered 1 I would not be surprised – as this is what my customer was expecting. In fact – depending on the driver and execution method used (we were using the Microsoft JDBC Driver for SQL Server) – this T-SQL batch can generate 2 result sets (which is what my customer was seeing). The first result set (from the INSERT statement) was empty, and the second result set (from the SELECT statement) contained the value of the ID column.

So, how do you handle a situation like this? I can think of several ways:

Change Your Code – You could change the code to move to the second result set in the collection of result sets that are returned. This would allow you to get the value of the ID column without changing your T-SQL batch. You can find an example of how to accomplish this with the Microsoft JDBC driver on the Microsoft JDBC Driver Team Blog (http://blogs.msdn.com/b/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx).
Use a Stored Procedure – You could use a stored procedure to output the value of the ID column as either the return value (provided it is an integer type) or through an output parameter. Using stored procedures is a long-standing best practice and it allows you to have more control over the results being returned. Also, consider including SET NOCOUNT ON in your stored procedure definition to exclude extraneous rows affected messages.
Change Your T-SQL Batch – By rewriting the batch into a single statement, you can work around the multiple result sets without any other code changes. The new statement looked something like this:

INSERT INTO dbo.ResultSetTest

(TestData)

OUTPUT Inserted.ID

VALUES
(‘Test’);

More information about the OUTPUT clause can be found in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms177564.aspx.

Conclusion

When writing T-SQL statements and batches, you need to be aware of the clients that your data consumers will be using to retrieve the data. Different drivers can act differently, and you need to be prepared when you get calls saying “The statement you wrote is not doing what it should be doing!”

Announcing SSAS Maestros v1.2

Denny Lee [MSFT] — Thu, 12 May 2011 00:42:00 GMT

We are proud to announce that SQLCAT will continue with the SSAS Maestros course in June and July in Redmond and Madrid.

SSAS Maestros 1.2 Courses

Join us for a five-day deep-dive course on Analysis Services 2008 R2 UDM and join the SSAS Maestro Program. Prepared and presented by SQLCAT, top industry experts and the SQL Server Analysis Server team, this intensive 500-level course gives top SSAS professionals the education and hands-on experience needed to deliver highly complex and highly scalable OLAP solutions using Analysis Services 2008 R2. Registration requires screening and approval of qualified attendees. Click on the links below for more details.

SSAS Maestro Program - Redmond, WA- June 13-17

SSAS Maestro Program - Madrid, Spain - July 18-22

The v1.2 of the SSAS Maestros course has been updated based on our learnings from the v1.0 course. How well did people like SSAS Maestros v1.0? Linked is Vidas Matelis' [blog | twitter] opinion of the course: SSAS Maestro program – my experience so far.

We are also proud to announce the first set of SSAS Maestros Instructors that will be leading the v1.2 effort as well.

In Redmond, we are fortunate to have BI industry experts

Stacia Misner [blog | twitter]
Adam Jorgensen [blog | twitter]
Denny Lee [blog | twitter] and John Sirmon [blog | twitter] will be the SQLCAT representatives.

And in Madrid, we are fortunate to have the BI gurus:

Marco Russo [blog | twitter]
Chris Webb [blog]
Thomas Kejser [blog] will be the SQLCAT representative

To keep current on SSAS Maestros, don’t forget to follow the hash tag #SSASMaestro

SSAS Maestro v1.0 Update

But how about all of the folks who completed Round 1 of SSAS Maestros in Redmond, Hong Kong, and London? Apologies for the delay but it has taken a bit longer than we had originally anticipated to complete the evaluations.

Our partner Solid Quality, CSS, and us are currently going through the evaluation process and we believe the evaluations will be completed by June/July time frame.

So stay tuned!! (another great way to stay tuned is to follow @sqlcat and/or search #SSASMaestro)

SQL Server Migration Assistant (SSMA) v5.0 just released!

Mike Weiner - MSFT — Sat, 30 Apr 2011 21:06:00 GMT

Exciting news as earlier this week the SQL Server Product Group announced the release of SQL Server Migration Assistant (SSMA) v5.0! A product with the goal of further simplifying the user experience in automating the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL Server or SQL Azure.

In this release, SSMA family of products has been improved to further to reduce the cost and the risk of migration from Sybase, Oracle, MySQL and Microsoft Access. Additional functionality includes:

Migrating to SQL Server "Denali",
Globalization support for migrating non-English databases to SQL Server,
Support for installation of an extension pack on clustered SQL Server environments
A number of enhancements focused on Sybase and Oracle migration,
Improved scale and performance of the migration itself.

For full details about the release and free product downloads please access the SSMA Team Blog

Optimize Recursive CTE Query

shauntj-us — Thu, 28 Apr 2011 20:14:00 GMT

Author: Shaun Tinline-Jones

Reviewers: Lubor Kollar; Conor Cunningham; Steve Howard; Kun Cheng; James Podgorski; Jimmy May; Joseph Sack; Welly Lee; Neeraj Joshi; Keith Bauer

Introduction

We recently assisted a global ISV to address a performance issue related to a poor performing recursive CTE (Common Table Expression). The ISV wanted the query that was running in excess of 3 minutes to run in less than 15 seconds on their servers. The end result of our efforts was a 3,600% performance improvement.

Note: Validation for this post was performed in the SQL CAT Customer Lab on an HP Proliant DL380 G7, Westmere-EP X5680 3.33GHz 2 socket, 6 physical cores, 12 logical cores for a total of 24 cores; 144GB RAM.

A CTE (http://msdn.microsoft.com/en-us/library/ms186243.aspx), amongst other things, is a very effective single statement for handling hierarchial data, and more specifically, parent-child relationships. A CTE is a reasonable choice when the intention is to iterate through the data, particularly in cases where the schema cannot be changed but recursive queries are required. If you can design a different schema layout, there are often much faster ways to execute queries over hierarchies. CTEs are also a natural choice when converting a CONNECT BY statement from alternative RDBMS platforms (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm).

There is a scenario where using a CTE construct is significantly less efficient than the traditional approach to traversing the Parent-Child construct. This blog will describe how and when a WHILE loop performs better than the CTE. The characteristics that can contribute to CTEs being a less than optimal choice are:

Non-Unique Parent/Child Key
Complex Predicates

In our scenario the ISV was attempting to extract all the items that adhered to a certain set of characteristics. An item could exist on its own or be a component (child) within a larger item (parent) and there were no constraints to the number of parents a child could have. For this scenario, the query was recursively traversing the hierarchy in a non-unique fashion.

Below is the original CTE code:

WITH CTE(ItemID, StartPt, StartID, EndPt, EndID, level)
AS
(    -- Anchor member definition
    SELECT anc.ItemID, anc.StartPt, anc.StartID, anc.EndPt, anc.EndID,0
    FROM dbo.CTEIssue AS anc
    WHERE anc.StartPt = -1512034855
        AND anc.StartID = 1809069910
        AND anc.Category IN(-379491138,-379518300,-379520626,472917509,-379494005,-379506005,-2134852210,-379515627,-379515134,-379484415)
    UNION ALL
    -- Recursive member definition
    SELECT chi.ItemID, chi.StartPt, chi.StartID, chi.EndPt, chi.EndID, par.level+1
    FROM
        dbo.CTEIssue AS chi
        INNER JOIN
        CTE AS par ON(par.EndPt = chi.StartPt AND par.EndID = chi.StartID)
    WHERE
        chi.Category IN(-379491138,-379518300,-379520626,472917509,-379494005,-379506005,-2134852210,-379515627,-379515134,-379484415)

)
SELECT DISTINCT cti.*
FROM
    dbo.CTEIssue AS cti
    INNER JOIN
    CTE AS cte ON(cti.ItemID = cte.ItemID)
WHERE (cti.EndPt <> -1512034855 OR cti.Category = -379484415)
GO

Identifying the issue

Firstly, we understand that there are cases where an inefficient query is acceptable, provided that it meets the intended objectives. It only becomes an issue when the intended objectives are not attained. In this case, the inefficient CTE meets the expected response times for small data sets or simple predicates and only became an issue once the data set became large and the throughput increased. Another concept to appreciate is that there are cases where a slow operation for small data sets is significantly faster when dealing with larger data sets. For example, nested joins are great for small sets of data but are typically no match for hash joins when the data is large.

It's relatively easy to recognize that using CTEs is not the most efficient approach when the statement immediately following the CTE expression relies on the DISTINCT of all the columns. Essentially this implies that the recursive logic doesn't have a primary key in the anchor, and thereby allowing each recursive member to not be unique. This creates a scenario where a huge number of duplicate rows are generated.

Another way to recognize that a CTE is inefficient is to compare the estimated query plan statistics to the actual statistics. The below table highlights the difference between the query plan Estimates versus Actuals.

Estimate Rows	Estimate Executions	Rows	Executes	TruncatedOperatorText (First 60 characters)
183.53	NULL	3	1	WITH CTE(ItemID, StartPt, StartID, EndPt, EndID, level) AS
35.22541	1	3	1	\|--Nested Loops(Inner Join, OUTER REFERENCES:([Recr1014]
35.22541	1	299197	1	\|--Hash Match(Aggregate, HASH:([Recr1014]), RESIDUAL:
1240.829	1	4129317	1	\| \|--Index Spool(WITH STACK)
1240.829	1	4129317	1	\| \|--Concatenation
1	1241.829	0	0	\| \|--Compute Scalar(DEFINE:([Expr1021]=(
1237.773	1	0	0	\| \| \|--Compute Scalar(DEFINE:([Expr10
1237.773	1	1723	1	\| \| \|--Nested Loops(Inner Join,
1237.773	1	1723	1	\| \| \|--Index Seek(OBJECT:([
1	1237.773	1723	1723	\| \| \|--Clustered Index Seek
1.002469	1241.829	4127594	1	\| \|--Assert(WHERE:(CASE WHEN [Expr1023]>
1.002469	1241.829	4127594	1	\| \|--Nested Loops(Inner Join, OUTER
1	1241.829	0	0	\| \|--Compute Scalar(DEFINE:([E
1	1241.829	4129317	1	\| \| \|--Table Spool(WITH STA
3.055948	1240.829	0	0	\| \|--Compute Scalar(DEFINE:([E
3.055948	1240.829	4127594	4129317	\| \|--Nested Loops(Inner J
3.055948	1240.829	4127594	4129317	\| \|--Index Seek(OBJE
1	3791.91	4127594	4127594	\| \|--Clustered Index
1	35.22541	3	299197	\|--Clustered Index Seek(OBJECT:([MCC2].[dbo].[CTEIssu

As you can see - in some cases the estimated rows versus actual rows were significantly skewed.

Improving the performance

The objective of that query was to identify unique rows and remove them from the recursive iteration. If this can be done in the CTE, then that would be ideal, otherwise a more performant alternative is to convert the query into WHILE loop. The CTE is far more efficient than WHILE loop if there is a unique parent/child key. However let me emphasize that when you have non-unique parent/child keys, estimations used to create the query plan is likely to be significantly skewed.

Below is the code that meets the objective of been more restrictive during each iteration. It is followed by an explanation of the reasoning behind the construction of the query, and why it performs better:

SET NOCOUNT ON

DECLARE @StartPt int = -1512034855;
DECLARE @StartID int = 1809069910;
DECLARE @Counter tinyint = 0;
DECLARE @MaxRecursion tinyint = 50;

DECLARE @CategoryList table(Category int NOT NULL)

DECLARE @PtIDPair table
(
    Pt        int        NOT NULL 
    ,ID        int        NOT NULL
    ,lvl    tinyint    NOT NULL
    ,PRIMARY KEY CLUSTERED (lvl, Pt, ID)
)

CREATE TABLE #Item
    (
      ItemID     int NOT NULL
      ,Category     int NOT NULL
      ,StartPt int NOT NULL
      ,StartID     int NOT NULL
      ,EndPt     int NOT NULL
      ,EndID     int NOT NULL
      ,lvl         tinyint NOT NULL
    )

CREATE CLUSTERED INDEX CDX_#Item_Lvl ON #Item ([lvl])
CREATE NONCLUSTERED INDEX NDX_#Item_ItemID ON #Item (ItemID)

INSERT INTO @CategoryList 
VALUES
    (-379491138)
    ,(-379518300)
    ,(-379520626)
    ,(472917509)
    ,(-379494005)
    ,(-379506005)
    ,(-2134852210)
    ,(-379515627)
    ,(-379515134)
    ,(-379484415);

--Gather the anchors
-- There are a set of ItemID values, which happens to contain a common
-- Pt/Id combination.  These are level 0 (Anchors)
INSERT INTO #Item
    (ItemID, Category, StartPt, StartID, EndPt, EndID, lvl)
SELECT
    ItemID, cti.Category, StartPt, StartID, EndPt, EndID, @Counter
FROM    
    dbo.CTEIssue AS cti
    INNER JOIN
    @CategoryList AS lst ON(lst.Category = cti.Category)
WHERE
    StartPt = @StartPt
    AND StartID = @StartID

-- We want to remove the duplicates, from the final result set, so we do our filtering to create a unique set of anchor pairs
INSERT INTO @PtIDPair(Pt, ID, lvl) 
SELECT EndPt, EndID, @Counter
FROM #Item
WHERE lvl = @Counter
GROUP BY EndPt, EndID

WHILE @@ROWCOUNT > 0
 BEGIN
    SET @Counter += 1
    
    IF @Counter = 50
     BEGIN
        RAISERROR('Max Recursion reached', 16, 1)
        BREAK
     END
    
    INSERT INTO #Item(ItemID, Category, StartPt, StartID, EndPt, EndID, lvl)
    SELECT    cti.ItemID, cti.Category, cti.StartPt, cti.StartID, cti.EndPt, cti.EndID, @Counter
    FROM    
        dbo.CTEIssue AS cti
        INNER JOIN
        @CategoryList AS lst ON(lst.Category = cti.Category)
        INNER JOIN
        @PtIDPair AS pr ON(cti.StartPt = pr.Pt AND cti.StartID = pr.ID)
        LEFT OUTER JOIN
        #Item AS itm ON(itm.ItemID = cti.ItemID)
    WHERE
        itm.ItemID IS NULL
        AND pr.lvl = @Counter - 1

    INSERT INTO @PtIDPair(Pt, ID, lvl) 
    SELECT itm.EndPt, itm.EndID, @Counter
    FROM 
        #Item AS itm
        LEFT OUTER JOIN
        @PtIDPair AS pr ON(itm.EndPt = pr.Pt AND itm.EndID = pr.ID)
    WHERE
        pr.ID IS NULL
        AND itm.lvl = @Counter
    GROUP BY
        itm.EndPt, itm.EndID
 END

SELECT
    cti.*
FROM 
    dbo.CTEIssue AS cti
    INNER JOIN
    #Item AS itm ON(cti.ItemID = itm.ItemID)
WHERE 
    cti.EndPt <> -1512034855
    OR cti.Category = -379484415
GO

Side Note: Our investigations revealed that in this case table variables significantly outperformed local temp tables.

The first step was to declare variables and create a temporary table to store the equivalent of the CTE result set. A table variable was used to store the unique JOIN clause rows generated during the iterations. Just before entering the WHILE…END, we created the first record (a.k.a the Anchor Member). In the WHILE…END loop we found all the children based on the JOIN criteria, further filtered by the predicates, and inserted them into the list of unique records. The next iteration would not collect the same children again. This was the most important distinction.

Improving the CTE Query Plan

Compared to the CTE, the WHILE loop is a fair amount of coding. Prior to re-writing the CTE as a WHILE loop, we attempted to optimize the query plan itself, within the constraint of not being able to generate unique values in the join. We modified the CTE to use a table variable versus using an IN clause. We created indexes and updated the statistics, and achieved a 50% performance improvement, but unfortunately and more importantly missing the objective of improving by at least a factor of 12.

Below are the STATISTICS IO and TIME demonstrating the improvement of adding the TVP and indexes:

Original

(3 row(s) affected)

Table 'CTEIssue'. Scan count 41,592,377, logical reads 137,303,190, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 24,576,797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 209197ms, elapsed time = 217344ms.

with TVP

(3 row(s) affected)

Table 'CTEIssue'. Scan count 4,428,524, logical reads 41,930,102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 2, logical reads 24,575,641, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '#6EF57B66'. Scan count 1, logical reads 19,007,602, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 143646ms, elapsed time = 151268ms.

Side Note: Table variables do not maintain statistics and are generally not recommended when there are cost-based plan choices that can result in significant performance variability; one would typically leverage a local temporary table in that case. We used a TVP here because the ISV application was creating an IN clause that could reach hundreds of values, and a table variable data type was more robust, and in some cases more efficient.

When we compare the query plans between the CTE and the WHILE loop, we realized that each iteration of the WHILE loop, has the luxury of a different query plan. This is unlike the CTE which only executed against one query plan.

A significant contribution to the long running nature of the CTE query is this portion of the query plan that estimates less than 10 rows on the outer and inner query, but ends up having to iterate through millions of rows on both sides of the query. For a table that only has a few hundred thousand rows, this is a function of the lack of unique join criteria.

In Conclusion…

The Common Table Expression (CTE) is a powerful syntax in its ability to address the historically challenging objective of recursive queries using T-SQL. It is a natural and often times appropriate syntax when migrating from the CONNECT BY syntax. Recursive CTE queries do have a reliance on the unique parent/child keys in order to get the best performance. If this is not possible to achieve, then a WHILE loop is potentially a much more efficient approach to handling the recursive query.

Our final results completed this frequently run query within 4 seconds from 144 seconds, a 3600% performance improvement!!!

32- and 64-Bit Connectivity from the Same Machine

ChuckHeinzelman — Thu, 31 Mar 2011 15:59:00 GMT

Author: Chuck Heinzelman

Reviewers: Steve Howard, Carl Rabeler, Shaun Tinline-Jones, Mike Weiner, Murshed Zaman

I was recently involved in a customer lab testing the Microsoft Business Intelligence stack. I noticed an interesting behavior when working on one of my test servers.

Here’s the environment that I was working with:

· Windows Server 2008 R2 (64-bit)

· SQL Server 2008 R2 (64-bit)

· SharePoint 2010 (64-bit)

· Excel 2010 with PowerPivot Add-In (64-bit)

· Visual Studio 2010 (32-bit)

See the problem yet? I had a mix of 32-bit and 64-bit applications on the same machine.

Many developers and database professionals are running 64-bit operating systems and tools these days. For the work that we do, the larger address space that 64-bit processing provides allows us to work with larger data sets and do our jobs better. To facilitate this, companies that produce DBMSs and other data sources are shipping 64-bit drivers and connectivity tools to connect to their systems.

In a mixed-mode environment like the one outlined above, connectivity can prove to be quite tricky. You will need to have both a 32-bit and a 64-bit driver loaded on your machine to make this work properly. In the software scenario outlined above, you would need:

· 32-bit driver to work with the data from within the Visual Studio environment

· 64-bit driver to work with the data from within PowerPivot and SharePoint

This problem is not limited to the applications listed above. I have seen it crop up in the following arenas as well:

· SSIS – When running in the BIDS designer (a 32-bit application), you need to have 32-bit drivers installed to use the design-time tools. SSIS packages can be run using either the 64-bit or the 32-bit SSIS engine (64-bit is default, but this can be changed by setting the Run64BitRuntime property to false). If you do not have 32-bit drivers installed on your machine, you will not be able to connect to your data sources at design-time. Also, if you deploy a solution where only 32-bit drivers exist to a 64-bit server, you will need to install the 32-bit drivers on the server and explicitly schedule the package to run using the 32-bit SSIS runtime to use the 32-bit drivers. More information can be found in the article 64 bit Considerations for Integration Services on MSDN (http://msdn.microsoft.com/en-us/library/ms141766.aspx).

· Report Builder – Report Builder is a 32-bit application, and the design-time experience will require 32-bit drivers. When you preview a report, your experience will vary depending on if you are connected to a report server or not. If you are connected to a report server, the preview will run in the context of that server and you will need to have the appropriate bit-level drivers installed on the server. If you are running disconnected, Report Builder itself will do the rendering using the 32-bit drivers.

If you are using SQL Server as your data source, the 64-bit SQL Native Client installer will install both the 64-bit and 32-bit drivers side-by-side. If you are extracting data from a non-Microsoft database platform, you will need to check with the database manufacturer to determine how to install their drivers side-by-side. You might also run into this issue when extracting data from data sources such as Microsoft Excel – which up until Microsoft Excel 2010 did not have a 64-bit version.

In some cases, you might not be able to install both the 32-bit and 64-bit drivers side-by-side. In this case, your options are fairly limited. The easiest way to get around this is to install all of your client tools at the same bit-level – which could mean running the 32-bit versions of all of your tools on your 64-bit machine.

Conclusion

Each data source provider is going to have different drivers and different configuration methods, so I cannot go into depth here into how to configure both the 32-bit and 64-bit drivers on the same machine for each provider. You might even run into scenarios where the installation procedures are different from one version to the next from the same provider. The easiest way to get around this problem is to try to run everything at the same bit-level, but that is not always possible. If you cannot run everything at the same bit-level, you will need to consult the manufacturer of your data source for their best practices on side-by-side driver installation.

Additional Resources

64 bit Considerations for Integration Services - http://msdn.microsoft.com/en-us/library/ms141766.aspx

Data Sources Supported by Reporting Services (SSRS) - http://msdn.microsoft.com/en-us/library/ms159219.aspx

Data Access Technologies Road Map - http://msdn.microsoft.com/en-us/library/ms810810.aspx

How to get a x64 version of Jet? - http://blogs.msdn.com/b/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx

SSIS: 64-bit Story - http://social.technet.microsoft.com/wiki/contents/articles/ssis-64-bit-story.aspx

Linked Servers to SQL Azure

Kevin Cox SQL Server — Tue, 08 Mar 2011 02:51:00 GMT

Authors: Kevin Cox & Michael Thomassy

Contributors: Lubor Kollar

Technical Reviewers: Shaun Tinline-Jones, Chuck Heinzelman, Steve Howard, Kun Cheng, Jimmy May

Overview

Connecting directly to a SQL Azure database from a reporting tool (like Microsoft Excel and PowerPivot, or SQL Server Reporting Services) from your desktop or local data center is possible using a data source that looks like:

[YourAzureServr@database.windows.net].[YourDatabase].[YourSchema].[YourTable]

This is useful if you have a single database on SQL Azure that you need to query. What if you have reached the 50GB limit that is currently the top size of a SQL Azure database? The solution is to split your database into many databases containing the same schema. See this paper on database sharding with SQL Azure.

Expecting the users to always define multiple data sources to connect to multiple databases is cumbersome and may not perform well. For example, a PowerPivot user would have to open a separate link to every SQL Azure database individually. This paper describes a solution using a local SQL Server that can ease the access to all these databases and will usually give better performance. The solution is called Partitioned Views and is implemented using Linked Servers. Since customers don’t usually like to expose their databases to the internet, we are proposing a separate server that contains no actual data and only contains the views necessary for applications to get an internet link to SQL Azure databases. This is not the only solution because you can implement stored procedures using OPENROWSET or OPENQUERY for solutions that need more logic.

A read-only solution is the easiest to implement. If modifications must be made to the SQL Azure tables via the Distributed Partitioned Views, you must read the restrictions in SQL Server Books Online. It also helps to read about how to implement a partitioned view. Using the check constraints on the tables as shown in the examples in this link is essential if you want the optimizer to only touch the right tables when you use the constrained columns in a WHERE clause in your queries.

How does this work?

This concept relies on the linked server feature of SQL Server. Since ADO.NET, ODBC, PHP and JDBC are the only providers currently supported by SQL Azure, ODBC data source names (DSN) are required.

Setting up an ODBC DSN

Run odbcad32.exe to setup a system DSN using SQL Server Native Client. Or go into Control Panel\System and Security\Administrative Tools and click on Data Sources (ODBC). Each database you create on SQL Azure needs a separate DSN and a separate linked server definition. Use TCP, not named pipes because you will be communicating to the SQL Azure servers through the internet.

Scale the creation of ODBC connections to multiple databases by modifying & importing a .REG file into the registry (regedit.exe) patterned after this sample:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Azure_ODBC1]

"Driver"="C:\\Windows\\system32\\sqlncli10.dll"

"Server"="YourAzureServer"

"LastUser"="YourLogin"

"Database"="YourDatabaseName"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]

"Azure_ODBC1"="SQL Server Native Client 10.0"

SQL Server Linked Server

An ODBC DSN and an associated Linked Server need to be created for each Database that will be queried. Here are two sample T-SQL commands that will create a linked server and associated login:

EXEC master.dbo.sp_addlinkedserver @server = N'Azure_ODBC1',@srvproduct=N'Any', @provider=N'MSDASQL', @datasrc=N'Azure_ODBC1'

/* For security reasons the linked server remote logins password is changed to ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Azure_ODBC1',

@useself=N'False',@locallogin=NULL,@rmtuser=N'yourlogin@YourAzureServer',@rmtpassword=’#####'

SQL Azure Limits

By default, SQL Azure allows 149 databases per server. To exceed this threshold, either 1) create databases in other servers, or 2) contact the Azure business desk and request that your limit be increased from the default threshold. Each database can be a maximum of 50GB, giving a default total 7.45TB of storage per Server. Remember to factor in index sizes when planning your total data needs. The 50GB limit has to cover both data and index space. Log space is considered separate from the 50GB limit.

Distributed Partitioned View

In order to make all your SQL Azure databases appear as one data source, you need to create a distributed partitioned view (DPV) in your local, on-premise SQL Server database. This database must be at least Enterprise edition because DPVs are not supported in Standard Edition or less.

This example statement creates the view with two SQL Azure databases on a local SQL Server:

CREATE VIEW dbo.dpv_Test AS

SELECT * FROM Azure_ODBC0.test0.dbo.fact

UNION ALL

SELECT * FROM Azure_ODBC1.test1.dbo.fact;

This is an example of a simple select using a four-part name. More complex statements are allowed, such as adding joins and where clauses. Joins should use four part names in all table references to keep the join work on the same machine. The worst performance occurs when cross server joins are implemented.

The simple example above will return all rows from these tables in all databases. This may be your intention if, for example, you want to bring all the data into PowerPivot for aggregations and pivoting. Be mindful of the cost for bandwidth of returning all the rows. To estimate cost, go to the main Windows Azure pricing site. The appendix provides a query that can be used at any time to get the current billing estimate. Be mindful that the billing values reflect current pricing which you are likely to be modified as Azure matures.

Notes: It is not the view that is partitioned in a distributed partitioned view, it is the data the view points to that is partitioned. The data does not have to be partitioned, this view can point to one database and can contain one select. The tables being referenced in the view can exist in both SQL Azure and local databases as long as the correct permissions are granted for the appropriate users.

Use Linked Server to SQL Azure

There is a simple method for running a query against a single table directly on a SQL Azure server from your application (without using the DPV). As an example, if you open a query window on your local SQL Server Management Studio, while connected to a local instance, you can follow the sample:

SELECT * FROM [YourAzureServer@database.windows.net].[YourDatabase].[YourSchema].[YourTable]

To query all the tables on your Azure server from a local server, simply use the view created above:

SELECT * FROM dpv_Test

Write to a table through the view:

INSERT INTO dpv_Test VALUES (1, ‘Test’, 1)

The first time the view is invoked, either through a select or insert/update/delete command, it will cache the table constraints on the local server. This gives the view some information about where the insert should be directed and it will only touch that database. And if the constraints are used in the WHERE clause of a query, the optimizer will do partition elimination and only touch the databases it needs to touch.

Note: Beware that distributed partitioned views have several restrictions that are well documented in B Online. (This link is one of many to read, use it as a launch point for the other related pages.)

Linked Server Properties

Linked Servers are only available in SQL Server Enterprise Edition or higher.

If your view spans multiple Azure data centers, you need to be aware of one more thing before you get started. On your local server you need to set the Lazy Schema Validation option to TRUE so that the query is not sent to every server for every command. The schema check will defer to run time and may fail if the result set formats are incompatible. :

EXEC sp_serveroption 'LocalServerName', 'lazy schema validation', true

EXEC sp_serveroption ‘RemoteServerName’, ‘lazy schema validation’, true

Another server level setting that can help performance is to ensure that the collation settings are compatible on all servers. This is an optimization option and is not required. What can happen if the collations are not compatible is the filters supplied by the WHERE clauses do not get applied on the remote server. Instead, all data is returned by the query and the WHERE clause gets applied on the local machine.

EXEC sp_serveroption 'LocalServerName', ‘collation compatible’, true

EXEC sp_serveroption ‘RemoteServerName’, ‘collation compatible’, true

Downsides and potential pitfalls

This solution is not perfect and at times it can be a struggle. Most of the difficulties have been mentioned in this blog, but the two main ones are summarized here for convenience. The blog contains the solutions/workarounds to these problems so it is worth reading.

1. Optimizer sometimes ignores your WHERE clause and sends the query to every server, every database. The solution is to put constraints on your SQL Azure tables so the optimizer can determine which shard to touch and which to eliminate.

2. Non-remotable query where all data comes back to the local server without being filtered by the remote database.

What if one database is not available?

If the view needs to touch all the databases and one is not available, the query will fail. If the view has determined that it can do partition elimination and does not need the unavailable database, then the query will succeed. The only exception to this is the first time a DPV is executed after a restart of your local SQL Server, it needs to touch every destination database to get the constraints and store them locally (in memory). Remember to turn on lazy schema validation so that every query after the first one does not get sent to each database just to check the schema.

Leveraging Constraints for Partition Elimination

How does SQL Server know which databases to touch? To test this scenario, create the different databases on SQL Azure so that your main table contains data from each month of a certain year. Perhaps create the databases called AppDB2010_01 and AppDB2010_02 to make it easier for you to remember what the database contains. These names are meaningless and have no bearing on what you actually store in the database. Create a table, perhaps call it FACT and make sure it has a DATE or DATETIME (or any other date oriented data type). The key to making this solution work efficiently is to create a constraint on this DATE field so that SQL Server knows it only contains data within a certain range. In this example, it will only contain data for a certain month of a certain year; i.e. January 2010. This constraint is what gets cached on your local server after the first use of the DPV so that SQL Server knows which tables to touch during a query. Now test to confirm using a query such as:

SELECT * FROM dpv_Test WHERE datefield = ‘2010-01-15’.

How do you know a query did not touch a certain database? Since SQL Profiler does not work in SQL Azure, the only way to know which queries ran on a certain server is to use certain DPVs to see what plans have been generated. The following query will return a list of recently generated statistics and plans for a server:

SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) ORDER BY last_execution_time DESC;

Summary

It is possible to use a local SQL Server to simplify access to a multiple SQL Azure database. The performance may be better than individual links from a front end tool when partition elimination is used.

Appendix: SQL Azure Billing Summary

Thanks to Lubor Kollar for creating these queries.

SELECT * FROM sys.Database_Usage

SELECT * FROM sys.Bandwidth_Usage

SELECT SKU,

SUM ( CASE WHEN USAGE.SKU = 'Web'

THEN (Quantity * 9.99/31)

WHEN USAGE.SKU = 'Business'

THEN (Quantity * 99.99/31)

END ) AS CostInDollars

FROM sys.Database_Usage USAGE

WHERE datepart(yy, TIME) = datepart(yy, GetUTCDate())

AND datepart(mm, TIME) = datepart(mm, GetUTCDate())

GROUP BY SKU

SELECT USAGE.Time_Period,

USAGE.Direction,

CASE WHEN USAGE.Direction = 'Egress'

THEN 0.15 * USAGE.BandwidthInKB/(1024*1024)

WHEN USAGE.DIRECTION = 'Ingress'

THEN 0.10 * USAGE.BandwidthInKB/(1024*1024)

END AS CostInDollars

FROM (

SELECT Time_Period,

Direction,

SUM(Quantity) AS BandwidthInKB

FROM sys.Bandwidth_Usage

WHERE datepart(yy, TIME) = datepart(yy, GetUTCDate())

AND datepart(mm, TIME) = datepart(mm, GetUTCDate())

AND class = 'External'

GROUP BY Time_Period, Direction

) AS USAGE

Comparing Different Results with RCSI & Read Committed

Kun Cheng — Thu, 03 Mar 2011 21:26:00 GMT

Author: Kun Cheng
Reviewers: Sunil Agarwal, Steve Howard, Shaun Tinline-Jones, Prem Mehra, Sanjay Mishra, Michael Thomassy, Mike Ruthruff, Howard Yin, Jimmy May

During a recent ISV partner engagement, we decided to enable RCSI (Read Committed Snapshot Isolation) on a SQL Server 2008 R2 instance with the objective of minimizing blocking. As expected, we did not encounter any significant blocking. However, we did find different behavior when we compared the results using RCSI to those we got using SQL Server’s default RC (Read Committed) isolation level. In a nutshell, blocking in the SQL Server database may have different data results when a query reads and changes multiple tables at the same time using RCSI compared to using RC isolation level.

The remainder of this blog describes this behavior in greater detail, along with a basic code for illustrative purposes:

Let’s start with the default RC isolation level:

-- set up test database and tables t1 and t2

CREATE DATABASE testRCSI;

USE testRCSI

CREATE TABLE dbo.t1(id int NOT NULL PRIMARY KEY, bid int NOT NULL)

CREATE TABLE dbo.t2(id int NOT NULL PRIMARY KEY)

INSERT INTO dbo.t1(id,bid) VALUES(1,1)

INSERT INTO dbo.t2(id) VALUES(1)

Time	Connection 1	Connection 2
T1	BEGIN TRAN -- with RC isolation level INSERT INTO t1(id,bid) VALUES(2,2) INSERT INTO t2(id) VALUES(2)
T2		DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)
T3		Blocked
T4	COMMIT
T5		Two rows deleted

The results were:

SELECT id FROM dbo.t1

(none)

SELECT id FROM dbo.t2

After we enabled RCSI, the result were very different:

-- Set isolation level as read_committed_snapshot and reset tables

ALTER DATABASE testRCSI SET READ_COMMITTED_SNAPSHOT ON;

USE testRCSI

TRUNCATE TABLE t1

TRUNCATE TABLE t2

INSERT INTO t1(id,bid) VALUES(1,1)

INSERT INTO t2(id) VALUES(1)

Time	Connection 1	Connection 2
T1	BEGIN TRAN -- with RCSI level INSERT INTO t1(id,bid) VALUES(2,2) INSERT INTO t2(id) VALUES(2)
T2		DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)
T3		Blocked
T4	COMMIT
T5		One row deleted

The results were:

SELECT id FROM dbo.t1

SELECT id FROM dbo.t2

How do we explain the difference in the results? At time T3, when DELETE was blocked by INSERT, sys.dm_tran_locks showed that the DELETE was waiting for a key lock on the newly added row 2. Once the INSERT was committed, the DELETE was unblocked on row 2. So why wasn’t the row deleted?

Explaining Results when RCSI Is Enabled

To understand this behavior, it is important to note that even when RCSI is enabled on the entire database, the snapshot effect is only applicable to the read operations on a table. The snapshot effect does not apply to DML (data manipulation language) operations.

In the query above, the DELETE operation occurred on table t1, so SQL Server took a blocking lock. Table t2, however, was accessed by the read part of the DELETE query, so a snapshot of the data in t2 was obtained and it did not block. It retrieved one row (value 1) because it was the only row committed at the beginning of the transaction. Once the DELETE operation was unblocked, it found only one qualifying row based on the join and it deleted that row.

So how do you resolve the differences between the two tables and achieve the same result as achieved using the default RC isolation level? You can use READCOMMITTEDLOCK query hint to make the read operation (against table t2) comply with the rules for the RC isolation level. In fact the query hint would make queries behave the same as under RC isolation level. This is exactly what my ISV partner wanted for the scenario and implemented in their application.

DELETE t1 FROM t1 JOIN t2 (READCOMMITTEDLOCK) ON (t1.bid=t2.id)

-- this would delete both rows from t1

Results:

SELECT id FROM dbo.t1

(none)

SELECT id FROM dbo.t2

Alternately, if you just want to obtain the same results when RSCI was enabled, you can use snapshot transaction isolation for the DELETE query:

ALTER DATABASE testRCSI SET ALLOW_SNAPSHOT_ISOLATION ON;

SET transaction ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

DELETE t1 FROM t1 JOIN t2 ON (t1.bid=t2.id)

-- this is not blocked by insert

COMMIT

Results:

SELECT id FROM dbo.t1

SELECT id FROM dbo.t2

In summary, blocking in the SQL Server database might have different results when a query reads and changes multiple tables at the same time using RCSI than it does when using the default RC isolation levels. However, you can take the actions this post describes to get the results you expect.

For more information about row versioning-based isolation levels, please see “Understanding Row Versioning-Based Isolation Levels” (http://technet.microsoft.com/en-us/library/ms189050.aspx).

Reference:

Recommend reading of Sunil’s series of concurrency blogs:

“Concurrency Series: My application was running fine yesterday buy why is it blocking today?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-my-application-was-running-fine-yesterday-buy-why-is-it-blocking-today.aspx

“Concurrency Series: Minimizing blocking between updaters”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-minimizing-blocking-between-updaters.aspx

“Concurrency Series: Why do I get block when no one has locked the row(s) being queried?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-why-do-i-get-block-when-no-one-has-locked-the-row-s-being-queried.aspx

“Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-why-do-i-get-blocking-when-i-use-read-uncommitted-isolation-level-or-use-nolock-hint.aspx

“Concurrency Series: Basics of Transaction Isolation Levels”

http://sqlcat.com/msdnmirror/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

Why the obsession with random I/O within the context of SSAS?

Denny Lee [MSFT] — Tue, 01 Mar 2011 16:15:50 GMT

by Denny Lee

As many of you know from the various blogs, whitepapers, and conferences from SQLCAT, there is a big obsession or compulsion toward I/O by various members of SQLCAT. If you’re not already familiar with this topic, definitely reference Mike Ruthruff’s whitepaper Predeployment I/O Best Practices.

But as could be seen from the SSAS Maestros session in Redmond this week (for more information, check out What is the SSAS Maestros?), there is even an obsession for IOps even within the context of Analysis Services as noted in the papers below

So why is the obsession?

As noted in the SSAS Maestros course, if you think I’m bad, ping my counterpart Thomas Kejser who is even more obsessed and all-knowing about IOps. The reason for this focus is because for many queries within Analysis Services (especially for enterprise scale cubes), there are a lot of threads that hit the storage engine. This means that the threads will be hitting disk – i.e. the disk I/O subsystem. And even if the each individual thread causes only sequential I/O, the cumulative effect of all of those threads is random I/O. And as noted in the below graphic - which is from Michael Anderson’s excellent Scalable Shared Database Part 5 post – both the IOps and MBps is substantially slower when the system is random instead of sequential.

So what can we do about it?

As noted by the our posts Analysis Services Distinct Count Optimization Using Solid State Devices as well as the paper: REAL Practices: Performance Scaling Microsoft SQL Server 2008 Analysis Services at Microsoft adCenter, the solution is to utilize NAND devices which have superior random IOps performance.

Note, you can still get great random IOps performance using regular spinning media provided you have enough spindles for your LUNs and you stripe and/or short-stroke the disks.

Or…you can make sure you buddy up with your friendly systems engineer whom can take care of this for you!

Enjoy!

Concurrency Series: My application was running fine yesterday but why is it blocking today?

Sunil Agarwal — Sun, 20 Feb 2011 22:32:00 GMT

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, And Kevin Liu

Have you ever encountered a situation where a query was running fine yesterday but today it is running a lot slower? I am sure most of us have seen a situation like that which can be quite frustrating. While there can be many reasons but there are two common cases as described below:

· First is that the query plan might have changed due to stale statistics, or missing indexes (accidental drop of an index) or by passing an uncommon parameter value leading to an inefficient query plan for other parameter values.

· Second is due to blocking. In this blog, I will focus on the blocking that is caused specifically by lock escalation.

Before we proceed further, I would like point out that the best way to troubleshoot performance problems is to have a baseline of your workload when it was running with acceptable performance. You can then use the baseline compare it with current situation to get clues of what could be going wrong. I will recommend that you read the white paper to get an idea about general methodology http://msdn.microsoft.com/en-us/library/dd672789(SQL.100).aspx

Now, let us go back to the problem. In SQL Server, a lock can be taken at a row, page, or table level but there is a trade-off. If the lock is taken at a lower granularity say at ROW level, it will lead to more concurrency at the expense of higher number of locks. Similarly, if the lock is taken at a higher granularity say at TABLE level, it will lead to lower concurrency but at much reduced overhead of locks. To take an extreme example, if you have a table with 10 million rows and you need to read 10% of the rows at ‘repeatable read’ isolation level, the SQL Server, assuming it chose ROW locking granularity, will need to acquire 1 million locks, hold them in memory (approx. 100 MB storage) and then release 1 million locks at the end of the transaction. This is a significant overhead compared to 1 lock to be taken at table level with TABLE locking granularity. SQL Server uses an internal heuristic to choose the locking granularity when executing a SQL statement. You can override this locking granularity using Alter Index statement to control (ALLOW_PAGE_LOCKS or ALLOW_ROW_LOCKS). Additionally, SQL Server uses the lock escalation mechanism to promote the lock from lower granularity to TABLE level using an internal threshold. Please refer to http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx for details. Starting with SQL Server 2008, you can also optionally disable lock escalation at TABLE level.

Let us look at some example to understand lock escalation behavior.

-- create the table

create table t_escalation (c1 int, c2 int)

-- create a clustered index.

create clustered index ci_t_escalation on t_escalation (c1)

-- insert 10000 rows into the table. Note, there will NOT be any lock -- escalation in this case even though we are inserting 10000 rows as -- part of single transaction. The lock escalation threshold in SQL

-- Server is defined at a statement level rather than at transaction

-- level

begin tran

declare @i int = 0

while (@i < 10000)

begin

insert into t_escalation values (@i, @i)

set @i = @i + 1

end

commit

The table t_escalation has 10000 rows. Let us update all rows in a single UPDATE statement. In this case, we will expect the lock escalation to occur as we are modifying > 5000 rows and there are not a concurrent transaction accessing the table t_escalation to prevent it.

-- Update all 10000 rows

declare @escalation_counter_before_test bigint;

select @escalation_counter_before_test=cntr_value

from sys.dm_os_performance_counters

where counter_name = 'Table Lock Escalations/sec'

begin tran

update t_escalation set c2 = c2 + 1

declare @escalation_counter_after_test bigint;

select @escalation_counter_after_test=cntr_value

from sys.dm_os_performance_counters

where counter_name = 'Table Lock Escalations/sec';

-- check to see if the lock escalation has occured

print 'lock escalation count ' +

convert( varchar(100), @escalation_counter_after_test-

@escalation_counter_before_test);

-- commit the transaction

commit

Here is the output of running the batch above. As expected, we got the lock escalation.

(10000 row(s) affected)

lock escalation count 1

Now, let us consider a variation of this example where instead of updating all the rows in the table, we only update the rows that qualify the predicate (C1 < 4000). Since 4000 rows is less than the threshold of 5000 rows, there will be no lock escalation.

-- update only 4000 rows. In this case, the SQL Server

-- chose ROW locking granularity.

begin tran

update t_escalation set c2 = c2 + 1 where c1 < 4000

Concurrently, we run the following query in a different session.

-- Session-2: Select a row that was not a target of

-- update in session-1.

select * from t_escalation where c1 = 5000

You will note that this SELECT runs without blocking because the query plan for this query shows it is using the index scan as follows

We now commit the transaction in session-1

-- commit the transaction

Commit

Next step is to insert another 3000 rows in between the range (1 to 4000) as follows

declare @i int = 0

while (@i < 3000)

begin

insert into t_escalation values (@i, @i)

set @i = @i + 1

end

If we run the same update command again in session-1, you will notice that this time, the lock indeed got escalated because the number of rows updated from 4000 to 7000 causing it to cross the lock escalation threshold.

-------------------------------------------------------

-- Show the the last update causes lock to get

-- escalated.

------------------------------------------------------

declare @escalation_counter_before_test bigint;

select @escalation_counter_before_test=cntr_value

from sys.dm_os_performance_counters

where counter_name = 'Table Lock Escalations/sec'

begin tran

update t_escalation set c2 = c2 + 1 where c1 < 4000

declare @escalation_counter_after_test bigint;

select @escalation_counter_after_test=cntr_value

from sys.dm_os_performance_counters

where counter_name = 'Table Lock Escalations/sec';

print 'lock escalation count ' +

convert( varchar(100),

@escalation_counter_after_test -

@escalation_counter_before_test);

-- Here is the output

(7000 row(s) affected)

lock escalation count 1

Now in session-2, if we run the same SELECT, it will get blocked because the locks taken by session-1 have been escalated to table level. This is one example where a query that was running without blocking yesterday but has suddenly started seeing blocking.

-- Session-2: Run the SELECT query

select * from t_escalation where c1 = 5000

Solution: You can choose to disable lock escalation using the following command

-- disable lock escalation to remove the blocking

alter table t_escalation set (lock_escalation=DISABLE)

Thanks

Sunil Agarwal

Concurrency Series: Minimizing blocking between updaters

Sunil Agarwal — Sun, 20 Feb 2011 22:24:30 GMT

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, And Kevin Liu

When a transaction T1 modifies a row, it takes an X lock on the row for the duration of the transaction. If another concurrent transaction T2 wants to modify the same row, it gets blocked waiting for T1 to complete. It is easy to understand why this blocking is necessary. SQL Server does not know the fate of T1 until it completes so it forces T2 to wait. However, there are situations where a transaction updating a row gets blocked by a transaction updating a totally different row. Let me illustrate this using the following example:

-- create a simple table for our examples

create table basic_locking (c1 int, c2 int, c3 int)

-- insert 500 rows into the table

declare @i int = 0

while (@i < 500)

begin

insert into basic_locking values (@i, @i+ 100, @i+10000)

set @i += 1

end

-- Session-1: locks the data rows that satisfies the

-- predicate C1 = 1. Based on the data inserted, there

-- is only one row that qualifies

begin tran

update basic_locking set c2 = 100 where c1 = 1

-- Session-2: The update below wants to update a different row

-- but gets blocked on the X lock held by session-1.

update basic_locking set c2 = 1000 where c1 = 2

To understand, why it is blocked, let us first take a look at the query plan for the update statement in session-2. The query plan shows that the query optimizer has chosen a table scan for the udpate because there is no index on column C1. With the result, the UPDATE statement in session-2 gets blocked while acquiring a UPDLOCK on the row with C1 =2.

The trick of using NOLOCK does not work here because update statement forces the use of UPDLOCK and it will generate the error as follows:

update basic_locking with (NOLOCK)

set c2 = 1000 where (c1 = 2)

Msg 1065, Level 15, State 1, Line 15

The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.

The second trick of using RCSI does not work either and the update gets blocked behind X lock by session-1 on row with (C1 = 1). The reason is that the following UPDATE statement executes at a higher isolation level than read committed.

update basic_locking set c2 = 1000 where c1 = 2

Here is the output showing the blocking.

Solutions: Here are two choices to address this issue

1) Create an index on column C1 and make sure it gets used either using index hint or using a plan guide. When the index on column C1 is chose, the update statement will directly access the qualifying row with (C1 = 2) and therefore will not get blocked.

2) You can execute the update in session-2 under snapshot isolation level as follows

-- set isolation level to SNAPSHOT

set transaction isolation level snapshot

begin tran

-- enable Snapshot Isolation and now update the row

-- without blocking on session-1

update basic_locking set c2 = 1000 where c1 = 2

The transaction under SI does not take UPDLOCK and therefore it does not get blocked on session-1 and can successfully updates the qualifying row with C1 = 2. However, like under any other transaction, an X lock is acquired on the row before modifying it.

You may wonder what happens if we update the same row that was modified under session-1? Will it lead to lost update for the changes done under session-1? Here is the new UPDATE statement in session-2

-- set isolation level to SNAPSHOT

set transaction isolation level snapshot

begin tran

-- enable Snapshot Isolation and now update the row

-- this will not block

update basic_locking set c2 = 1000 where c1 = 1

In this case, the transaction under SI will get blocked while acquiring the X lock on the row as it has been concurrently modified by session-1. When the lock is ultimately acquired (i.e. the transaction in session-1 completes), a check is made if the same row was modified by a concurrent transaction that had committed and in that case, the concurrent change under the SI transaction is aborted by raising the following error. The update could have succeeded in session-2 if the update transaction in session-1 was aborted.

Msg 3960, Level 16, State 4, Line 4

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.basic_locking' directly or indirectly in database 'locktest' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Thanks

Sunil Agarwal

Concurrency Series: Why do I get blocked when no one has locked the row(s) being queried?

Sunil Agarwal — Sun, 20 Feb 2011 22:20:00 GMT

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu

Recently I was working with a customer who was surprised to see blocking when accessing a data row which was not locked? Let me describe the scenario using an example.

Example:

-- create a simple table for our examples

create table basic_locking (c1 int, c2 int, c3 int)

-- insert 500 rows into the table

declare @i int = 0

while (@i < 500)

begin

insert into basic_locking values (@i, @i+ 100, @i+10000)

set @i += 1

end

-- Session-1: locks the data rows that satisfies the

-- predicate C1 = 1. Based on the data inserted, there

-- is only one row that qualifies

begin tran

update basic_locking set c2 = 100 where c1 = 1

-- Session-2: Set the isolation level to default

set transaction isolation level read committed

-- select a different row and show that it blocks

select c3 from basic_locking where c1 = 2

The customer wondered why session-2 is seeing blocking even though no other transaction has locked the row satisying the predicate c1=2? To understand this, let us look at the query plan for the SELECT operation

Since there is no index on this table, the optimizer chooses the table scan to execute the query in session-2. As part of this plan, the predicate (C1 = 2) is applied to each row (including the one that has C1 set to 1). Since the data row with (C1 = 1) is already exclusively locked (X) by the transaction in session-1, it will block session-2. I strongly recommend customers to look at query plans while troubleshooting blocking issues.

Solutions: You have multiple choices here.

1) Use NOLOCK locking hint

select c3 from basic_locking with(NOLOCK)where (c1 = 2).

However, it requires an application change

2) Enable RCSI (read committed snapshot). This provide non blocking access to the data row with (c1 = 2). This will work and will not require application change.

3) Enable SI (Snapshot Isolation) but it will require application change.

4) Create an index on column C1. The blocking will be eliminated if optimizer chooses the index to execute SELECT statement in session-2.

create nonclustered index basic_locking_nci on basic_locking(c1)

-- force the access through an index to remove

-- blocking

select c3

from basic_locking with (index(basic_locking_nci))

where c1 = 2

Here is how the query plan looks like

There is a catch here. If optimizer does not choose this index, you may still get blocking. In that case, you will need to change your application for force the index hint or use plan-guide to direct optimizer to choose this index. Note, with the new index, the DML operations (Insert, Delete, and Update) will incur overhead of maintaining additional index.

Thanks

Sunil Agarwal

Concurrency Series: Why do I get blocking when I use Read Uncommitted isolation level or use NOLOCK hint?

Sunil Agarwal — Sun, 20 Feb 2011 22:12:00 GMT

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu

When using NOLOCK hint on the table or using read uncommitted isolation level, customers sometimes get surprised when they experience blocking. Let me explain this using the following example

Example: Concurrent DDL operation

-- create a simple table for our examples

create table basic_locking (c1 int, c2 int, c3 int)

-- insert 500 rows into the table

declare @i int = 0

while (@i < 500)

begin

insert into basic_locking values (@i, @i+ 100, @i+10000)

set @i += 1

end

-- In session-1, do an update in a transaction

-- It will acquire X lock on all the rows in the table

-- or possibly an X lock on the table.

begin tran

update basic_locking set c2 = 1000 where c1 = 1

-- script to show blocking and locks

select

t1.request_session_id as spid,

t1.resource_type as type,

t1.resource_database_id as dbid,

t1.resource_description as description,

t1.request_mode as mode,

t1.request_status as status

from sys.dm_tran_locks as t1

where t1.request_session_id = @@SPID

Here is the output of the locks taken using the following query. Since only one row is being updated, you see a X lock on the data row and the IX lock on the data page (i.e. 153)containing the row and IX lock on the table.

-- Session-2 use read uncommitted to access the data. Since the query –- is executed with NOLOCK hint or read uncommitted isolation level,

-- it will run without getting blocked

begin tran

-- As expected, this select will not block

-- and will return 100 which ‘dirty’ data

select top 1 c1 from basic_locking with (NOLOCK)

Now,let us execute the DDL operation in session-1 as follows:

-- In session-1, add a column to the table under the same

-- transaction

alter table basic_locking add c4 int

-- Session-2 use read uncommitted to access the data

-- This statement will block while waiting to to acquire

-- SCH-S lock on the table.

select c2 from basic_locking with (NOLOCK) where c1 = 1

-- use the following script to show blocking and locks

select

t1.request_session_id as spid,

t1.resource_type as type,

t1.resource_database_id as dbid,

t1.request_mode as mode,

t1.request_status as status,

t2.blocking_session_id

from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address

WHERE t1.request_status = 'WAIT'

Here is the output that shows that session -1 (SPID-51) is blocking session-2(SPID-54) on SCH-S lock request.

The reason for this blocking is that during the execution of a SQL statement, the SQL Server requires a schema stability lock (i.e. SCH-S) on objects referenced in the statement to protect against concurrent DDL. For the simple example above, if this lock was not taken, the question will be should the returned rows have 3 column or 4 columns? Or the rows that were returned before DDL operation committed show 3 columns while the remaining rows show 4 columns?

In fact, if you use RCSI mode or snapshot isolation level, the query will get blocked similarly. Since DDL is a not a common operation in production environment, this kind of blocking should happen rarely.

Thanks

Sunil Agarwal

Concurrency Series: Basics of Transaction Isolation Levels

Sunil Agarwal — Sun, 20 Feb 2011 21:13:00 GMT

Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu

In this series of blogs, I want to show some common concurrency questions that we get asked by customers. Before we look into common concurrency issues seen by customers, it will be good to review the transaction isolation levels provided in SQL Server.

Isolation Levels: SQL Server supports all four isolation levels as defined in ANSI SQL standard. The ANSI standard does not dictate how these need to implemented but SQL Server has implemented them using locks with the exception of RCSI/SI that I will discuss later. These isolation levels are:

· Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s). For example, if a concurrent transaction T2 updates a row R1, it can still be read under T1 even though T2 can potentially roll back later.

o Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.

o Cons: Data is not guaranteed to be transactionally consistent.

o Usage: It is typically used in queries/applications where data inconsistency can be tolerated. For example, computing average salary of employees.

· Read Committed: A transaction T1 executing under this isolation level can only access committed data. For example, if a concurrent transaction T2 updates a row R1, it cannot be accessed under T1, in fact T1 will get blocked until T2 either commits or rolls back. The S (i.e. Share) lock is held when accessing the data and is released once the access is complete. So for example, if T1 reads R1, R2, and R3 in a SQL statement, T1 acquires/releases S lock on R1 and then acquires/releases lock on R2. In some cases, the ‘S’ lock is not released until the end of the statement but for now we will not discuss that.

o Pros: Good compromise between concurrency and consistency.

o Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.

o Usage: Very commonly used isolation level. In fact, this is the default isolation level in SQL Server.

· Repeatable Read: A transaction T1 executing under this isolation level can only access committed data with an additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction. SQL Server achieves it by holding an S lock for the duration of the transaction. However, it does not protect phantoms, new data rows that qualify the query predicate can appear.

Example: Assume there is a table ACCOUNTS that stores customer’s account information along with the money balance.

Ø Start a transaction (T1) under repeatable read isolation level. Query the ACCOUNTS table with predicate (account_balance > 1000). Let us say it returns 10 rows

Ø Another transaction (T2) inserts a new row in the ACCOUNTS table with account_balance = 1020 and commits.

Ø Query the ACCOUNTS table again with the same predicate (account_balance > 1000) and it will return 11 rows. The reason is that transaction T1 only locked the 10 qualifying rows but did not lock the predicate range. With the result, the transaction T2 could insert a new row in the same predicate range.

o Pros: Higher data consistency.

o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency. It does not protect against phantom rows.

o Usage: Not very common.

· Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicate column) for the duration of the transaction.

o Pros: Full data consistency including phantom protection. Serializable isolation level guarantees transactions will end up with one possible serial order with an appearance that concurrent transactions did not interfere with each other. For example, if T1, T2, T3 are running under serializable isolation level, the possible serial orders are (T1, T2, T3), (T1, T3, T2), (T2, T1, T3), (T2, T3, T1), (T3, T1, T2), (T3, T2, T1).

o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.

o Usage: It is used in cases where data consistency is an absolute requirement.

In addition to these isolation level, SQL Server, starting with SQL Server 2005 release, added two changes as follows

· Read-Committed-Snapshot (RCSI): This is not a new isolation level but a new implementation of read committed isolation level that does not take any S lock on the data. The word snapshot stems from the fact that query under RCSI sees the snapshot of the database as of the beginning of the statement. It is a better alternative for applications that must access only committed data but without taking locks. SQL Server implements it using row versioning that has some overhead. You can map blocking implementation of read committed isolation level to RCSI by enabling READ_COMMITTED_SNAPSHOT option at the database level without forcing any changes to applications.

o Pros: Non-blocking access to transactionally consistent data.

o Cons: Some overhead of maintaining row versions.

o Usage: To minimize reader/writer blocking and to replace ‘read uncommitted’ or NOLOCK access to data with transactional consistent data

· Snapshot Isolation (SI): This is new proprietary isolation level that provides non-blocking access for read operations. The transactions under SI see the snapshot of the database as of the beginning of the transaction. There is no automatic mapping of transaction isolation levels to SI so you must change your application to access data under SI isolation level.

o Pros: Non-blocking access to transactionally consistent data. Higher consistency than repeatable read.

o Cons: Some overhead of maintaining row versions.

o Usage: To get non-blocking access to consistent data across multiple statements within a transaction.

As an aside, you can explicitly set isolation levels by executing ‘set transaction isolation level <isolation>’ or you can override it at an object level in a statement using locking hints.

Thanks

Sunil Agarwal

What is the SSAS Maestros?

Denny Lee [MSFT] — Mon, 31 Jan 2011 04:07:19 GMT

by Denny Lee (SQL Customer Advisory Team), Daniel Yu (SQL Marketing)

Over the last few weeks, there has been a lot of questions, tweets, and rumors about the SSAS Maestros program – some notable blogs include Kasper de Jonge’s Do you have what it takes to become SSAS Maestro and Vidas Matelis’ SSAS White Paper List.

Background Information

We had created the SSAS Maestros program as a way to broadcast the lessons learned from some of SQLCAT’s more complex Analysis Services UDM enterprise customers – such as Yahoo!’s 12TB Analysis Services cube that was announced during the PASS Summit 2010 Day One Keynote with Ted Kummert. The deep technical learnings from names like Akshai Mirchandani, Thomas Kejser, John Sirmon, Denny Lee, and more! have been incorporated together by our good friends from Solid Quality Mentors into a three-day deep dive course in:

Redmond (fully booked now, though we’re planning a second US session shortly)
London (fully booked now, though we’re looking into a second London session)
Hong Kong: still available!

We will have more courses planned but this is the initial batch of courses as we will be working out the kinks and improving the course over time. Currently, we’ve made this a 11 module course with labs and exams.

Benefits

As noted in the above MS events course link as well as Kasper’s blog, the key benefit will be of course the material that you learn. But some of the other great things include:

Be one of the few part of the elite SSAS Maestros (more on this later)
The SSAS Maestros list will be published on a Microsoft site for your customers to reference.
When SQLCAT needs someone to help with a complex SSAS engagement or needs to refer to a partner – we will refer to a SSAS Maestro.
SQLCAT and the Analysis Services team will be creating SSAS Maestros-only webcasts and Q&A sessions - This will be an exclusive forum to discuss the latest best practice and share critical knowledge

As you can see, there is a lot of great benefits to this great course!

But…what’s the catch?

Due to its complexity and our dive into deep technical learnings, this is not going to be an easy course. Some examples of this include:

Following technical conference conventions of 400 level sessions, we consider this a 500 level course.
There is an application to do this course and entrants are accepted based on showcasing their deep technical experience with Analysis Services.
During the three-day course, there will be a number of labs which will have very little guidance (on purpose).
Upon completion of the course, there will be a take-home exam project that will need to be completed within thirty (30 days).

We have been drawing inspiration from the SQL Server MCM course – which many of you know is extremely tough!

Discussion

As this is the first year of the course, we will be very strict and limit the number of applicants into the course. This allows us to work out the kinks in the course as well as figure out logistics around the webcasts and exams / re-examinations. But due to its popularity, we are already rapidly working on planning V2 of the course! So stay tuned!!