Prefetching can make a surprising difference to SQL Server query execution times where there is a high incidence of waiting for disk i/o operations, but the benefits come at a cost. Mostly, the Query Optimizer gets it right, but occasionally there are queries that would benefit from tuning.   Read more...

Easy way to setup a comprehensive ETL Performance auditing Solution no matter how complicated your ETL setup.  Read more...

How recompilation does significantly affect on the execution time of SQL query and plummet or degrade the performance?   Read more...

Describes the gains experienced in my environment by impleenting AWE.  Read more...

Performance test of new SQL 2012 functions DateTimeFromParts and Format  Read more...

You need a quiet couple of hours poking about with DMVs, and checking execution plans to get to the bottom of the a query issue, but you can't make a copy the production database either. It's too big, and the data is private....  Read more...

The arrival of the (MAX) data types in SQL Server 2005 were one of the most popular feature for the database developer. At the time, there was a lot of discussion as to whether this freedom from having to specify string length came at a cost. Rob attempts to give a final answer as to any down-side.  Read more...

Don't blame SQL Server if your SQL Statements perform badly! But where do you start to investigate the problem? This article is a primer on were to start looking when your SQL runs slowly.  Read more...

Describes the benefits of publishing instance performance metrics including a script to generate HTML-based Performance Snapshot.  Read more...

A benefit of using computed columns is that they can save developers from having to write calculation logic at the application layer; at the table level, the application can simply reference the computed column. In addition, performance can be improved by persisting a computed column and building an index on it.  Read more...

A SQL expression may look elegant on the page but it is only valuable if its performance is good. Do window functions run quicker than their conventional equivalent code? Fabiano Amorim gives a definitive answer.  Read more...

I’ve been working with execution plans quite a lot in SQL Server 2012. There are a number of changes, most of them associated with new or different functionality. I had not noticed anything really fundamental until recently. I’ve become a huge proponent of always checking the properties of the SELECT statement. There’s so much useful information in there about what’s happened with the plan generation in the optimizer (not low level stuff, but the big picture items) that you should always be checking it first as a fundamental part of your plan examinations.  Read more...

There are a number of ways of measuring and identifying I/O-related performance information for your SQL Server database instances.  Read more...

In this article, Ron Johnson shows how you can use DMVs for proactive real-time SQL Server performance monitoring.  Read more...

Receive-Side Scaling (RSS) was introduced in Windows 2003 to improve Windows scalability to handle heavy network traffic, which is typically the case for SQL Server OLTP workload. This post helps you get the most out of the feature.  Read more...

When healing a sick SQL Server, you must forget the idea that there could ever be a simple correspondence between symptom and disease: The art of troubleshooting is much more the art of discovering, and assembling, the various pieces of the puzzle so that you have a complete understanding of what is going on inside of a server.  Read more...

If your SQL Server’s tempdb database is heavily used by your application(s), consider locating it on an array of its own (such as RAID 1 or RAID 10). This will allow disk I/O to be more evenly distributed, reducing disk I/O contention issues, and speeding up SQL Server’s overall performance.  Read more...

Part 4 of a series from Matt Perdeck on speeding up your database access. This is a great series for developers. This is based on the book ASP.NET Site Performance Secrets.  Read more...

This article describes a utility that is able to report the most common DBCC CHECKDB errors significantly faster than DBCC CHECKDB does itself.   Read more...

This article looks to provide a method that you can use to collect the author's top 10 SQL Server performance metrics automatically over time.  Read more...