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.

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 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 articleDownload and Upload images from SQL Server via ASP.Net MVCshows how to use a VARBINARY type column. The follow up articleFILESTREAM MVC: Download and Upload images from SQL Servershows how to use a FILESTREAM column.

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.

The original article published in 2005 that started this blog. Step by step advice to troubleshoot Service Broker message delivery. Still valid today.

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.

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.

Because of the EXECUTE AS impersonation context activated procedures by default cannot access objects in other databases, like

, nor server scoped objects like linked servers. This article shows how to use code signing to overcome these problems.

Are youre 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.

is a library that adds SqlDependency based caching to LINQ queries.

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.

SQL Injection: casting can introduce additional single quotes

The cost of a transactions that has only applocks

SQL Server 2014 updateable columnstores Q and A

WindowsXRay is made public as Media eXperience Analyzer

Services running under domain account should add dnscache service as dependency

Understanding how SQL Server executes a query

SQL Server Clustered Columnstore Indexes at TechEd 2013

Registry bloat after SQL Server 2012 SP1 installation

How to enable Selective XML indexes in SQL Server 2012 SP1

Handling exceptions that occur during the RECEIVE statement in activated procedures

Inside the SQL Server 2012 Columnstore Indexes

Adding a nullable column can update the entire table

Show all index and heap access operators in the plan cache

Understanding Hash, Sort and Exchange Spill events

T-SQL functions do no imply a certain order of execution

Online Index Operations for indexes containing LOB columns

How to Multicast messages with SQL Server Service Broker

Online non-NULL with values column add in SQL Server 2012

How to update a table with a columnstore index

Scale out SQL Server by using Reliable Messaging

How to determine the database version of an MDF file

Erlands Sommarskog adds another gem to his treasure chest

FILESTREAM MVC: Download and Upload images from SQL Server

How to pass a NULL value in a message to a queue in SQL Server

Download and Upload images from SQL Server via ASP.Net MVC

This server supports version 662 and earlier…

TRY CATCH THROW: Error handling changes in T-SQL

AlwaysOn: High-Availability and reads Scale-Out

High Volume Contiguos Real Time Audit and ETL

Effective Speakers at Portland devsat and sqlsat27

SQL Server 2008 R2 Express database size limit: 10GB

How to change database mirroring encryption with minimal downtime

Performance comparison of varchar(max) vs. varchar(N)

System pagefile size on machines with large RAM

MySpace Uses SQL Server Service Broker to Protect Integrity of 1 Petabyte of Data

Fix slow application startup due to code sign validation

%%lockres%% collision probability magic marker: 16,777,215

stackoverflow.com: how to execute well on a good idea

A fix for error Cannot find the remote service SqlQueryNotificationService-GUID

Using XSLT to generate Performance Counters code

Service Broker Whitepaper on MSDN: the 150 trick

Things I know now: blogging can get you into a email ponzi scheme

Replacing Service Certificates that are near expiration

Event ID 833: I/O requests taking longer than 15 seconds

Replacing Endpoint Certificates that are near expiration

How does Certificate based Authentication work

Service Broker Administration, Monitoring and Troubleshooting

Is Service Broker in SQL Server 2008 compatible with the one in SQL Server 2005?

Remove pooling for data changes from a WCF front end

Service Broker leaked target conversation endpoints fix ships in Cumulative Update for SQL Server 2005 SP2

Fire and Forget: Good for the military, but not for Service Broker conversations

Call a procedure in another database from an activated procedure

How long should I expect ALTER DATABASE … SET ENABLE_BROKER to run?

Why does feature not work under activation?

Interested in SQL Server monitoring and configuration management?

SQL Injection: casting can introduce additional single quotes

The cost of a transactions that has only applocks

Interested in SQL Server monitoring and configuration management?