SqlDependency based caching of LINQ Queries
- Understanding how SQL Server executes a query
- An explanation of how SQL Server executes a query. Read this if you are curious about the how SQL Server executes the T-SQL requests from your app, how queries and transactions work, how SQL Server reads and writes data, the query operator tree execution model.
- How to analyse SQL Server performance
- I tried to distill my performance troubleshooting know-how into this article. It covers many areas and goes quite deep into some of them. It presents specific techniques you can use to identify bottlenecks.
- Storing Images and Media on SQL Server with ASP.Net MVC
- A couple of articles that show how to efficiently stream large files stored in the database, like image files, avoiding large memory allocations in the ASP.NEt process by correctly using streaming semantics. The first article Download and Upload images from SQL Server via ASP.Net MVC shows how to use a VARBINARY type column. The follow up article FILESTREAM MVC: Download and Upload images from SQL Server shows how to use a FILESTREAM column.
- Asynchronous procedure execution
- How to reliably invoke procedures asynchronously in your database by means of Service Broker Activation. By leveraging the internal activation mechanism, your client does not have keep a connection open, nor does it have to rely on some sort of helping service process.
- Troubleshooting Conversations
- The original article published in 2005 that started this blog. Step by step advice to troubleshoot Service Broker message delivery. Still valid today.
- Reusing Conversations
- Getting the high message throughput in Service Broker depends on proper conversation management. This article shows a technique of reusing conversations based on session ID. Using this technique you can easily achieve throughputs of thousands of messages per send end-to-end between two sites.
- Read-Write deadlock
- A presentation of a common deadlock scenario between a simple SELECT and an UPDATE. This is common deadlock that happens in content management systems when updating the view count for images and posts.
- Signing activated procedures
- Because of the EXECUTE AS impersonation context activated procedures by default cannot access objects in other databases, like msdb.dbo.sp_send_mail, nor server scoped objects like linked servers. This article shows how to use code signing to overcome these problems.
- The Mysterious Notification
- Are you’re using SqlDependency on your site? If you want to learn more about how this feature works and the underlaying technology this article is good starter. A brief presentation into Query Notifications, SqlNotificationRequest and SqlDependency.
LinqToCache is a library that adds SqlDependency based caching to LINQ queries.
Writing Service Broker Procedures
Originally published in 2006 this article goes over every technique of writing Service Broker activated procedures: single message, cursor, set oriented etc. It shows the basic skeleton of each one accompanied with performance measurements. If you are now only able to dequeue about 50 messages per second, read this article to understand how to get to 5000 messages per second.