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?