Improving async performance for SqlDataReader

In addition to the Idle Connection Resiliency feature that was added in .NET 4.5.1, the other major “feature” was a massive boost in async performance for SqlDataReader. From our own internal testing we have seen from a 50% performance improvement (for default command behavior) to 180% (for sequential access). This brings async performance to within 20-30% for the speed of sync methods, except that our performance tests are using the async methods wrong – and using it the right way can make async faster than sync.

Making the right calls

In our internal performance testing when we test our async methods, we use async for everything – opening the connection, executing the command, reading rows and get the value for each column. In the official post I wrote for .NET 4.5 I had said that this was not recommended since calling ReadAsync in default command behavior buffers the entire row into memory, so subsequent async calls to read column values is a waste of CPU. Just by switching the calls from GetFieldValueAsync to GetFieldValue I was able to get our performance test running faster with async calls than sync.

This greatly simplifies the rules that I had previously written – always call NextResultAsync, ReadAsync and either call GetFieldValue for default command behavior or GetFieldValueAsync for sequential access

Checking ahead

If you have a look at the code for ReadAsync you can see that there is a lot of “infrastructure” that needs to be set up in order to do an async call, and yet it will never be used if all of the data to read that row is currently available. So, in .NET 4.5.1, we introduced an internal method called WillHaveEnoughData that checks if we are guaranteed to have enough data in the current buffer to satisfy the next request (be it reading a header, column or entire row). In order to do this, we have to make a few assumptions:

• Columns are not null
• Variable sized columns are the maximum possible size that they can be
• Columns that can have large sizes or have additional data (NTEXT, TEXT, IMAGE, Table Value Parameters (TVPs) and User-Defined Types (UDTs)) will never fit in a single packet

Full speed ahead

If you read through the code of WillHaveEnoughData you can see the full set of assumptions, checks and optimizations that have been made. To summarize the code, the way to get the best performance out of the improvements made to SqlDataReader is to ensure the following:

• Use SQL Server 2008 R2 or later – this introduced a feature called Null Bitmap Compression (NBC) which allows the server to specify which columns are null for a row in the row header instead of setting the column to null in the column’s header.
• Avoid using NTEXT, TEXT, IMAGE, TVP, UDT, XML, [N]VARCHAR(MAX) and VARBINARY(MAX) – the maximum data size for these types is so large that it is very unusual (or even impossible) that they would happen to be able to fit within a single packet.
• Keep the maximum size of variable column as small as possible – as I mention above, we assume that variable sized columns will be the maximum size permitted by that column (so a VARCHAR(50) is assumed to always be 50 characters). This can make a huge difference in your applications performance – reducing a column from a VARCHAR(8000) to a VARCHAR(250) or VARCHAR(50) can be the difference between always creating and disposing the async infrastructure and rarely creating it.
• If you are moving large amounts of data, consider increasing the Packet Size specified in the connection string (NOTE: This will increase the amount of memory required for each connection).
• For sequential access, make sure to read each column in its entirety before moving to the next column (or next row), especially when reading large columns (otherwise the check for the amount of data needed will include the amount of data left for the current column and, for [N]VARCHAR\VARBINARY(MAX) types, if there is any leftover data at all then we will assume that it will not fit in a single packet)

Hopefully this gives you a good idea of the performance improvements made in .NET 4.5.1 and how to get the most out of them.

Connection Resiliency in ADO.Net

Despite “reliability” being one of the core features of TCP, network connections are typically unreliable, especially when being routed via the internet. This is even more of a problem with cloud computing, since most connections do route via the internet, and backend machines can go down at any moment for any reason. With this in mind, we have been making changes to ADO.Net to adjust from its client\server origins (with always-up enterprise hardware) to the cloud world (with commodity hardware).

Connection Pool Resiliency

In .Net 4.0 Reliability Update 1 we added a new feature referred to as “Connection Pool Resiliency” (or, as I liked to call it, “Check Connections in the Pool”). This feature would ensure that connections being retrieved from the connection pool were still alive before handing them back to the caller. We achieved this by asking TCP what the state of the connection was and, if TCP reported the connection to be disconnected, then we would create a new physical connection and return that to the caller.

Ensuring that only live connections were returned from the pool covered most low-traffic scenarios, since connections typically spend most of their time in the pool rather than being used. However, it provides little assistance for high-traffic scenarios (where connections do not remain in the pool for very long) or for code where connections are held open for a long time (which we strongly recommend against – but is inevitable in applications like SQL Server Management Studio).

One thing to be aware of for this type of resiliency is that we can only detect failures in the physical connection – if the connection’s state has become corrupt (e.g. commands always time out), then we will not be able to detect it and so we can not recover the connection. (In situations like these you can either abandon the pool by changing your connection string, or take the nuclear option and call ClearPool)

Idle Connection Resiliency

ADO.Net 4.5.1 combined with Azure SQL Database or SQL Server 2014 introduced a new feature to provide resiliency for more scenarios: Idle Connection Resiliency. With this feature, we can recover connections even if they were opened at the time that they failed. One of the difficulties with doing this is that SQL connections (unlike, say, a HTTP connection) have state – you may have changed the database you were connected to, or the collation that you were using, or the level of transaction isolation. Either way, when the connection is recovered, we need to ensure that it resumes whatever state it had previously (i.e. it should be completely transparent that you are now using a new physical connection). To do this we introduced a new TDS token called SESSIONSTATE (to allow the server to hand the client its current state as a blob) and a new Feature Extension called SESSIONRECOVERY (to resume the state on a recovered connection).

Let’s assume that you’ve attempted to execute a command on a dead connection. The process to recover it would look like this:

1. Execute* is called, check if the connection is alive (using a similar mechanism as the Connection Pool Resiliency)
2. If the connection is dead, then check if it is recoverable from both client and server perspective
3. If recoverable, dispose the old connection and attempt to open a new connection with the state of the old connection – we will retry a given number of times and sleep between each retry for a given time
4. Connection is recovered, resume the normal process

There are a few things to note from this. Firstly, the entire process is async – even if you are executing synchronously, we run the process as sync-over-async. Secondly, there are a number of situations where we can not recover the connection. From the client side, the connection must be idle – for most connections this is guaranteed since we perform the check just before execution, but for MARS connections, it is possible that there is another open session (it doesn’t have to be actively in use by another thread – just being open is enough). From the server side there are a number of situations where the state will not fit, or can not be represented in the SESSIONSTATE blob, for instance a temporary table.

Retry Logic

Even though we continue to improve ADO.Net’s ability to handle unreliable connections, this does not eliminate the need to have your own retry logic in your code – our connection recoveries are best effort and can only detect a limited set of faults. If you want a Microsoft supported implementation of generic retry logic, then I would recommend the Transient Fault Handing Application Block.

Bonus chatter: .Net 4.5.1 is now being pushed out via Windows Update, or you can grab it straight from the Download Center (if you’re running Windows 8.1 – and why wouldn’t you be? – then you already have 4.5.1).

I also added used a few links above to the revamped .Net Reference Source site – this contains the managed source code for most of .Net (including System.Data.dll).

Layers of Abstraction

One of the questions I often hear when I tell someone that I work on ADO.NET is “Is that still alive?” And, while ADO may be long gone, ADO.NET is today one of the most popular ways to connect to SQL Server and is the underlying layer beneath many (if not all) .Net Object Relational Mappers (ORMs) like Entity Framework (EF). However, it is because of these ORMs that you never hear about ADO.NET – it is no longer the “new, sexy” thing (and we don’t have product names like “Magical Unicorn Edition”). This does not mean that ADO.NET is simply becoming a framework to build ORMs, the world is a bit more complicated than that…

One thing that ORMs do well is to allow developers to quickly and easily get a database up and running, and then to get access to the data that is sitting there. For instance, the tooling in EF allows you to quickly create a model, deploy that to a database, code some LINQ (with its IntelliSense goodness) to grab some data, and then manipulate that data as an object. ORMs also make prototyping and rapid development easier as the model they generate can be updated from the database and any changes in the database schema will then show as compiler errors since the corresponding object in that model has now changed as well. One of the problems with EF, and ORMs in general, is that the extra layer of abstraction adds a performance cost, and while EF has been working to reduce that, you can see from the graph below that hand-coded ADO.NET is vastly quicker – so, what to do?

Opportunity Cost

As with any technology choice, you need to carefully considered what each option offers, and what each options costs. EF (and ORMs) offer quick and easy coding at the cost of the application’s performance. For some projects this may be fine – the developers may be too busy to consider hand-coding, or the cost of additional servers may be much less that finding, hiring and onboarding additional developers. Alternatively, ADO.NET may be chosen if the application is large enough that any percentage performance increase is a significant cost savings, or that the turn around time for a single database transaction is critical and every microsecond counts. Just to emphasize the point – remember that ADO.NET is an abstraction from the TDS protocol. There may be developers for which they performance is ultra-critical and they would rather hand-code TDS packets and communicate with SQL Server that way (which is entirely possible, since TDS is a documented protocol).

At the end of the day, you need to choose what makes sense to you and to your business plan – whether that means hand-coding ADO.NET, utilizing an ORM or a mix in between (like starting with an ORM and then hand-coding performance critical queries).

(As a side note: .Net 4.5 Beta has arrived! And ADO.NET has some new features that I will be posting about soon – in the meantime download the Beta and get coding, and be sure to log any bugs or suggestions on the Connect site or the Forums)

Thread safety

One of our focuses for .Net 4.5 was on async and improving support for doing ADO.NET asynchronously. A side effect of this is that we did a lot of work improving our thread-safety story. With .Net 4.0 and prior, we simply had a blanket statement that multithreaded access to any ADO.NET object was not supported, except for cancellation (i.e. SqlCommand.Cancel). Even then, there were some unusual corner cases with cancellation that resulted in unexpected circumstances. With 4.5 we could have maintained this stance, but we realized that the use of async makes mistakes with accessing objects on multiple threads much more likely (e.g. when manually calling ContinueWith or if you forget the ‘await’ keyword).

Pending Operations

With the .Net 4.5 Developer Preview, if you try to call any operation on an ADO.NET object while it has an asynchronous operation pending (including the old Begin\End methods), then we throw an InvalidOperationException. While this isn’t the nicest of behaviors, it is much better than the 4.0 and below behavior (which was undefined, although typically resulted in NullReferenceExceptions and data corruption). The reason that we opted for an exception instead of doing something ‘smarter’ (like waiting for the operation to complete), is that a secondary call to an object is typically a coding mistake (e.g. forgetting the ‘await’ keyword) and that anyone who needs to do multiple operations should schedule the second operation via await or ContinueWith.

However, if there is a synchronous operation in progress, we still do not support starting another operation on that object. And, by ‘not supported’, I mean that we have no checks in place and no guarantees on the behavior. Unfortunately, there is no easy way to detect multi-threaded access to an object (even from within a debugger), so you need to make sure that your code is correct. The simplest way to do this is by never sharing an ADO,NET object between multiple threads. This means that if you have a shared ‘Data Access Layer’ in your application, you should be opening a new connection per call (and closing it afterwards) or, if you have something like a singleton logger, you may want to consider a Consumer\Producer pattern such that there is only one thread performing the logging.

Cancellation

As I mentioned previously, cancellation is the only operation that we have always supported from another thread. In .Net 4.5 we have done a lot of work to ensure that cancellation is still supported, and we have also dealt with quite a few of the corner cases. For instance, any time there is a fatal error on a connection (e.g. the network has gone down) then we close the current connection. While this may seem reasonable, it means that cancelling an operation could result in the connection being closed while another operation (i.e. the one being cancelled) was running. While we haven’t changed this behavior in .Net 4.5, we have made sure that any other operation can handle the connection be closed due to an error, even if it means throwing an InvalidOperationException.

Multi-threaded MARS

In SQL Server 2005, we introduced a feature called "Multiple Active Result Sets", or MARS, which allowed multiple commands to be executed on a single connection. In .Net 4.0 and prior this had the caveat that you could not execute multiple commands or use multiple readers simultaneously, which greatly limits the usefulness of MARS. In .Net 4.5 we have done a lot of work to try to enable this scenario for SqlClient and, although we are not yet officially supporting it, it is something that we would like for people to try out as a part of their testing of the Developer Preview and async. As a side note, there is a performance overhead for enabling MARS, so it may be worth also investigating if you can disable the feature instead.

Issue 14

(Rather than just reiterating the new features in ADO.NET that we announced for //Build/, I figured that I’d do a series of posts covering various features in depth – although this first "feature" shipped a bit earlier than the 4.5 Developer Preview)

What’s in a fix

If you remember last month’s Patch Tuesday, the first Reliability Update for .Net 4.0 was release, including a bug fix for System.Data.dll. However, those of you who read the support article would have been greeted by this cryptic message:

---

Issue 14
Consider the following scenario

• You use the .NET Framework Data Provider for SQL Server (SqlClient) to connect to an instance of Microsoft SQL Azure or of Microsoft SQL Server.
• An established connection is removed from the connection pool.
• The first request is sent to the server.

In this scenario, an instance of SqlException is encountered, and you receive the following error message:
A transport-level error has occurred when sending the request to the server.

---

So given that description, can you tell what the original bug was, or what we fixed?
No? Neither can I – and I wrote the fix…

Historical Perspective

To explain "Issue 14", we’ve first got to look back at the history of ADO.NET, back to the 2.0 (or, possibly, 1.0) days. In the original design of the Connection Pool it was decided that, if there was a catastrophic failure of a connection, then the entire pool should be cleared. This was a reasonable assumption, since being unable to communicate with the server typically means that either the server is down (or restarted), the client network connection has died or failover has occurred – in any of these circumstances, it is unlikely that any other connection in the pool had survived.

Fast forward to today, and some of the original assumptions of the connection pool are no longer valid. Due to the increased popularity of cloud computing and connected devices, connections to SQL Servers are might not be going over ultra-fast and ultra-reliable links inside a data center. Instead, they may be going over the internet, which means that they are unreliable and could drop at any time. This, combined with SQL Azure’s policy of dropping connections that have been idle for over 30 minutes, meant that we could have one dead connection in the pool, but the rest would be ok.

Check Connections

So now we’re connecting over unreliable connections and still clearing the pool when it’s possible that only one of the connection had died. On top of that, we don’t know the connection is dead until someone tries to execute a command on the connection (and then gets an error, despite the fact that they had just opened the connection). So what to do?

Firstly, we are now checking the state of the connection when we remove it from the connection pool and, if its dead, giving you a new connection. This greatly decreases the likelihood that you will be trying to execute on a bad connection (although its still possible, as we are relying on Windows to know about the state of the underlying TCP connection, and since there is a race condition between us checking and you executing on the connection).

Secondly, we no longer clear the pool when there is a fatal error on a connection – so we’re no longer dropping (hopefully) good connections just because one connection is bad. Conversely, since we are checking connections before using them, we are still responsive to events like failover or network disconnects.

Best Practices

If you read the last section carefully, you would have noticed one of the caveats of this feature: "there is a race condition between us checking and you executing on the connection", which leads to my first recommendation:

Follow the Open-Execute-Close pattern

Every time you need to interact with SQL Server, you should open a fresh connection, execute your command (and deal with the data reader if you open one) and then close the connection (since SqlConnection, SqlCommand and SqlDataReader are all implement IDisposable, the best way to do this is with a ‘using’ statement). If you need to expose the data reader to a higher level API, and don’t want to cache the data in the data reader, then you should wrap the connection, command and reader inside a new class that implements IDisposable and return that instead.

My second recommendation relates back to my previous post on connection strings:

Use our connection pool

Despite the connection pooling code being rather old, it is extremely fast, reliable and it works. Opening connections from the pool and returning them afterwards is incredibly quick, especially when compared to opening a fresh connection or executing a command on a connection. Additionally we have the ability to introduce features like this which custom pooling code can’t.

Finally, this improvement is no replacement for proper retry code.

Improvements in 4.5

In the .Net 4.5 Developer Preview, we’ve made this feature more scalable, especially for high-throughput application servers where connections do not sit idle in the pool for very long.

As a final note, if you haven’t already upgraded to .Net 4.5, then you should make sure that you’ve installed the 4.0 Reliability Update.

Connection Strings: The smaller, the better

Today I’d like to talk about the wonderful and magic things that are Connection Strings. If you’re not familiar with connection strings, they are the way that a developer informs ADO.NET which server to connect to and connection options to use.

A Simple Rule

The problem with connection strings (actually, there are quite a few problems, but I’ll stick to the point of this post) is that there are far too many options to choose from, but let me simplify everything for you:

If you don’t need to change an option, or don’t know what it does, then don’t specify it.

Default Values

The default values for connection string options are there for a reason, so unless your application has some unusual requirements, you should stick to the default values. However, you shouldn’t re-specify the default values in your connection string either, unless you heavily rely on the behavior provided by that default value. The reason for this is that we may change the default at a later date if we make code changes (which make a different value more optimal), or we introduce a new value that is better for most developers. The most common case I see here is setting the "Max Pool Size" value – for the vast majority of applications the default size of 100 is reasonable, however you shouldn’t specify 100 in case we (or SQL Server) make modifications to our network code and so are able to increase the maximum, or perhaps we’d have different values for client and server applications*. Either way, you’d want to be able to get this benefit for ‘free’ by not specifying a value, rather than having to modify all of your deployed config files with the new values (because, of course, you are using config files, and don’t have the connection string hard-coded in your application).

Optional Extras

Alternatively. the thought "I don’t know what it does, but I might use it later" may also lead to included unneeded connection string options. If there was some beneficial feature that didn’t have any negative side effects, then we would enabled that option be default. The fact that a connection string options is disabled by default should indicate that there is some other side effect of turning it on (typically a performance hit, but possibly other things). If you don’t know what an option does, then you probably aren’t taking advantage of it, and if you aren’t taking advantage of it, then you don’t need it. A good example of this is "Multiple Active Result Sets" (aka MARS). This is a feature introduced in SQL Server 2005 that permits multiple commands to be executed on a single connection simultaneously**. This may sound great, but most applications don’t really have a need for it, they can simply open another connection. However, if you turn it on because you "may need it", then you will be taking a performance hit and possibly hiding errors in your code (since having MARS off ensures that you dispose a SqlDataReader before trying to open a new one on the same connection).

Final Caveat

Before you go ahead and start removing options from your connection strings, there is one thing you should be aware of: since you had these options specified, there may be parts of your code that rely on the non-standard behavior. For instance, if you turn off MARS, any part of your code that created multiple readers on the same connection will start throwing exceptions, or reducing the Max Pool Size may reveal a connection leak that was previously hidden (resulting in more exceptions in your code). So be very careful when changing connection string options and ensure that you run all of your tests (which, of course, you have) and have a rollback strategy to deploy the old connection string if something goes wrong.

---

*These are just examples and are not necessarily in our current plans. But if you like these ideas, or have some of your own, feel free to post the on Connect
**Technically speaking, we don’t support multithreaded access to the same connection, even with MARS turned on (unless you are cancelling a Command). So, you would still need to synchronize each of the Command\Reader executions\reads on the same connection.

Size of MAX != Max of Size

How’s that for a title?

What I’m actually referring to here is the VAR* data types in SQL Server (i.e. VARBINARY, VARCHAR and NVARCHAR). For these data types you need to specify a maximum size for that column, such as VARBINARY(20) (which would be a binary array that is, at most, 20 bytes long). The largest maximum size permitted is 8000 for VARCHAR and VARBINARY and 4000 for NVARCHAR. You can also specify a size of ‘MAX’ (e.g. VARCHAR(MAX)), however this does not set the maximum size to 8000 or 4000, rather it sets the maximum for the column to 2^31-1 bytes.

Hence, the Size of MAX (2^31-1) != Max of Size (4000 or 8000)

Pick a size, any size

So, the question then becomes “Why not just use MAX for everything?” A few reasons: Firstly is performance, from a connectivity point of view (since that’s where I work), MAX data types need to be sent in chunks, meaning that we need to read additional metadata concerning the size of each chunk (although this is likely to be quite small compared to the total amount of data being sent). From an storage point of view, if the data is larger than 8000 bytes then it is stored “out of row”, meaning that a pointer to the data is stored in the row storage and must be dereferenced in order to read the data. This also means that the query engine* can not simply assume that all of the data it requires is in row storage, nor can it assume that it can load the all of the data from the column into memory (since they may be up to 2Gb of data per MAX column per row).

In terms of maintenance, you can not do online index operations on MAX columns. Additionally, if you have a lot of data that increases over time to be above the 8000 byte limit and is taken “out of row” or shrinks to below 8000 bytes and is taken into the row, then this will greatly increase the amount of fragmentation your database has.

However, the most important reason to limit the size of VAR* columns is for security. For instance, imagine that you are running a website and permit users to create accounts, but you also allow them to change their username once they are registered. You also decide that you will have the ‘username’ column in your database to be NVARCHAR(MAX), and that you will limit the size of the username in your business logic. All of this would be fine, so long as your code is bug free. If, however, you have a bug that allows a user to bypass your business logic and set a username of any size, then it becomes quite easy for a malicious user to stage a denial of service attack on your website – they can simply create a few users with very long usernames and fill up your database (remember that SQL Azure only allows 50Gb database size, which is 25 completely filled MAX columns). If you also have a page that displays usernames (e.g. for high scores, list of users online, searches) then your other users won’t be able to use those pages as they will be attempting to download the attacker’s massive username (and the bandwidth that is used in the process may be costly as well). So, while the correct response to this scenario is to fix the bugs in your website, you should also be following the “Defense in Depth” principle and have protections all the way from client-side scripting through to the business logic and underlying database schema.

There are, however, some places where limiting to 4000 bytes may be unreasonable, for instance blog posts, forums or content stored in a CMS. But, where possible, try to choose an actual size for your VAR* columns.

Note on legacy types

You may also notice that there are the IMAGE, TEXT and NTEXT types in SQL Server, these are legacy types and you should be using VARBINARY, VARCHAR and NVARCHAR types instead.

* I have not seen the engine’s code so I can’t confirm that it does make these assumptions, although there is some evidence to suggest that specifying a size does help performance.