Tag Archives: 4.5.1

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:

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.

Advertisements
Tagged , , , , , ,

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).

Tagged , , , , , , , ,