Rants of an ADO.NET Dev

Tag Archives: .NET

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.

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.

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

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:

Execute* is called, check if the connection is alive (using a similar mechanism as the Connection Pool Resiliency)

If the connection is dead, then check if it is recoverable from both client and server perspective

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

TLDR; Avoid SafeHandles and Native\Managed transitions in scale-up applications (e.g. web servers, middle tiers), such as calls to GetType(). Additionally, you won’t see these types of performance issues until your application has high loads (i.e. when the Garbage Collector kicks in).

All the CPUs!

Recently, the Entity Framework team was investigating a customer’s performance issue, namely that they couldn’t get 100% CPU usage using EF on multiple threads, no matter how many threads they were using. The EF team had verified that the customer was using threads correctly (avoiding shared state, especially DbContext objects) and that the application was not being limited by IO (the test was run against a local SQL Server using a small enough data set that the results would be cached). They gathered some profiles from the customer, and saw that a large amount of time was spent waiting on Critical Sections in System.Data.SqlClient, so they enlisted my help.

As it turned out, the Critical Section was a red herring – a side effect of the way that we implemented synchronous network calls for MARS. However, there was another event that a lot of threads were waiting on which appeared to be reset when Garbage Collection started and set when it completed. Why were these threads waiting for the GC to complete? Because they wanted to disable being preempted by the GC and you can’t do that while the GC is running.

Clearing the table while the customers are still eating

In a managed application the Garbage Collector deals with memory management, however it needs to be able to do this while the program is running in a thread-safe manner. The typical solution to this is to simply have the GC pause all managed threads in the application while it collects and then resume them afterwards. The key in that above sentence is “managed thread”, there are times in a managed threads life that it has to delve into native code, such as when using PInvoke or using Managed C++ to call into Native C++ (as is the case for SqlClient). In this situation the thread needs to tell the runtime that it is switching to being a native thread, and a side effect of this is that the thread can no longer be preempted by the GC.

There is another situation where we don’t want the GC to interrupt us, and that is during a call to SafeHandle.ReleaseHandle(). The documentation for this method states “The garbage collector guarantees… that the method will not be interrupted while it is in progress”, and one of the things that can interrupt it is the GC itself.

Getting to GetType()

Walking further up the call stack, one of the methods that was disabling GC preemption were network read\write calls in SqlClient. However, the default packet size is 8Kb, so that should be enough data to keep the CPU busy between calls – we also reuse buffers, so there isn’t much stress on the GC. So these calls weren’t likely to be the issue.

The other call that kept appearing was GetType() – this was a surprise since I didn’t see why this method needed to disable the GC. As it turns out, every time GetType() is called it creates and disposes a SafeTypeNameParserHandle, which inherits from SafeHandle. Furthermore, there a number of calls to native methods and memory pinning. Put together, this thread puts a lot of stress on the GC and keeps disabling\enabling GC preemption. Even worse, as the customer added more threads, the GC was stressed more, and so the threads had to wait longer for the GC to finish.

Getting past GetType()

When checking type compatibility, there is no need to call GetType() – the as and is keywords are much faster (roughly 20x and 5x respectively, for single-threaded performance), they also take care of inheritance and interfaces. There are only two reasons why you may need to use GetType(): either you are checking that an object exactly matches a given type, or to see if an object has a particular method. For the first of these scenarios, why does it matter that an object is a specific type, or a sub-class? I honestly can’t think of a scenario where it would truly matter, unless you don’t trust the derived class to conform correctly to the contracts set up by the parent, which is a very unusual situation to be in. As for checking the existence of a method on an object, using reflection is probably a bad idea since the same named method doesn’t necessarily have the same purpose, consider Stream.Read vs DbDataReader.Read vs Interlocked.Read; or even more confusing Type.GetType vs Type.GetType – it is a much better practice to cast (via the as keyword) to the interface or class that has the method that you are looking for and use the casted object.

Worst case scenario, if you really can’t avoid it, then at least try to cache and reuse the result of GetType.

(As a side note, if you’re ever interested in seeing how the .Net Framework works, you can download and browse through the Reference Source)

“The concept of a stack in programming is very similar to a stack of plates in real life, except that you can cheat a little – for instance, if you’re willing to accept that plates can float, then you can ignore gravity.”

One of the fantastic things about using a rich framework like .Net is that many of the basic data structures that programmers require already exists in a efficient and easy to use form. We also make sure to update these pre-built data structures with the new features that we introduce in the language, for instance in .Net 2.0 we introduced generics, and so the System.Collections.Generic namespace was created. With.Net 4.5 we are introducing new async APIs and the async\await keyword pair, meaning that programmers will now need to deal with concurrency and multithreading more often especially if their application has any shared data structures. Luckily enough, we already have the appropriate APIs that were introduced in 4.0: the System.Collections.Concurrent namespace.

Stack it. Queue it. Bag it.

The first couple of APIs I’d like to introduce you to is the ConcurrentStack and ConcurrentQueue. These are exactly as they sound: A stack and a queue that permit concurrent operations. One common trap when writing a multithreaded application is the pattern of checking the Count of the collection to see if an item is available, before attempting to take an item – which is fine with single threading, but in a multithreaded environment it is possible to have another thread jump in between your check and getting the item which then takes the last item before you can. Instead, the concurrent collections have the the TryPop and TryDequeue methods, which will atomically check the size of the structure and return an item to you if there is one available.

The other data structure I hinted at is the ConcurrentBag. Unlike the Stack or Queue, the ConcurrentBag has no guarantees about the order of output versus input – it’s an “Any In, Any Out” collection. This allows ConcurrentBag can have a much more efficient implementation when there is contention, since the collection can return any object that it currently holds, rather than having to coordinate with any of the threads accessing it in order to return objects in the correct order. One of the best uses of a ConcurrentBag is for a non-time sensitive resource cache, like a buffer pool – where you want to have the best performance even with contention, but it is ok to not return the most recently used object (as you would want to do with a connection pool).

Performance

One of the issues with writing multithreaded applications is attempting to measure performance, especially when you have a shared resource. If you are running a single threaded test with the above data structures, then you may notice that simply putting a standard Stack or Queue inside of a lock gives better performance than the Concurrent equivalent. However, introduce some contention (i.e. have multiple threads attempting to access the same object), and the Concurrent structures begin to shine. Additionally, you need to be careful when doing multithreaded micro-benchmarks as you may introduce too much contention (since a “real” application is likely to do some work with the object is just obtained, rather than handing it back to the collection).that would then skew your results.

However, unless you have a high-performance single threaded application or a multithreaded application with no shared resources, then a concurrent collection will be your best bet. It may be slower in an application with little load, but it will be much easier to scale it to a larger application if needed.

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.

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 14Consider 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.

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.

First off, a bit of a disclaimer – I admit that I am using Visual Studio 2010 (Beta 2) and Office 2010 (Beta); as such I shouldn’t expect all documentation for them to be complete or everything to being running bug free.

With that said: Begin the rant!

Today I decided to attempt to build an Outlook 2010 add in that would enable me to "block" particular authors on RSS Feeds. The reason I wanted to do this is that I am subscribed to the TechNet, ASP.NET, MSDN and Windows Team combined feeds – these 4 "combined" feeds are a collation of quite a number of blogs by Microsoft. The unfortunate thing is than a number of the blogs are completely irrelevant to me, or in a foreign language. With my Add In I could then block the non-English and irrelevant authors without having to create a-hundred-and-one "rules". So, what went wrong?

First off, there is no "RSS" class. RSS items are of type PostItem or, more specifically, have a MessageClass or IPM.Post.RSS. So while it is possible to tell that an individual Post item is an RSS item (by doing a string comparison), its impossible to filter or check folders. This isn’t a *big* thing, but it certainly is very annoying.

Don’t use the classes, use the interfaces. For every VBA class, there is a .NET Interface AND a class; for instance there is an Outlook.PostItem (Interface) and Outlook.PostItemClass (Class). So which to use? Apparently the interface is the one to use, I think. The classes are apparently "COM coclasses that are required for interoperability with the corresponding COM object" – which to me sounds like an Infrastructure class, which is fine if it is private or internal, NOT public. Also, in case you didn’t notice, to add insult to injury there is a class with the word "Class" in its name that is referring to the fact it is a class (and the interface isn’t prefixed with an "I" – but we’ll forgive that).

The documentation is incomplete. And its not just the 2010 documentation that is incomplete. To use the example above, lets have a look at the PostItem Interface members (Go, click on the link and have a look. I’ll wait right here. Done?). That’s right – they’re empty. But IntelliSense in Visual Studio (and the actual API) tell a different story. Ok, lets assume that (being programmers) they’re lazy. maybe the documentation is in the PostItemClass Class (Again, click on the link…). Hmm…. apparently all of the Properties, Methods and Events do the same thing… which is the same thing as the Class… Oh, and in the name of fairness, reopen one of those links and check the version of Office the documentation is for. That’s right, 2007.