Aaron Bertrand, Author at SQLPerformance.com

Potential enhancements to ASPState

Many people have implemented ASPState in their environment. Some people use the in-memory option (InProc), but usually I see the database option being used. There are some potential inefficiencies here that you might not notice on low volume sites but that will start to affect performance as your web volume ramps up.

Aaron Bertrand ◆ January 11, 2013

14

Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

SQL Sentry's Aaron Bertrand (@AaronBertrand) digs into the left anti semi join, comparing NOT IN, NOT EXISTS, OUTER APPLY, EXCEPT, and OUTER JOIN methods.

Aaron Bertrand ◆ December 27, 2012

27

SQL Server 2012 Service Pack 1 & Cumulative Update 1

Recently, Microsoft pushed out Service Pack 1 for SQL Server 2012, and they quickly followed up with Cumulative Update 1 for Service Pack 1. The reason for this is that the service pack – due to longer development and regression testing cycles – did not contain any of the fixes from RTM Cumulative Updates 3 & 4. Since many people have waited – based on mostly speculative bias at this point – to even start testing SQL Server 2012 until Service Pack 1 was released, I thought it might be useful to cover a couple of the scenarios you might come across.

Aaron Bertrand ◆ December 12, 2012

6

Minimizing the impact of DBCC CHECKDB : DOs and DON'Ts

Aaron Bertrand (@AaronBertrand) discusses several aspects of DBCC CHECKDB and offers suggestions to minimize the impact this crucial operation can have.

Aaron Bertrand ◆ November 29, 2012

27

An important change to Extended Events in SQL Server 2012

As you have most certainly heard elsewhere, SQL Server 2012 finally offers a version of Extended Events that is a viable alternative to SQL Trace, in terms of both better performance and event parity. There are other enhancements such as a usable UI in Management Studio – previously your only hope for this was Jonathan Kehayias' Extended Events Manager.

Aaron Bertrand ◆ November 6, 2012

4

Bad cardinality estimates coming from SSMS execution plans

I have a few people to thank for a recent update to Plan Explorer. Brooke Philpott (@Macromullet) and Greg Gonzalez (blog | @SQLsensei), of course, for R & D and for digging into the code and sorting it out. But also to Paul White for being persistent in helping us validate the fixes.

Aaron Bertrand ◆ November 2, 2012

0

Beware misleading data from SET STATISTICS IO

My co-worker Steve Wright (blog | @SQL_Steve) prodded me with a question recently on a strange result he was seeing. In order to test some functionality in our latest tool, SQL Sentry Plan Explorer PRO, he had manufactured a wide and large table, and was running a variety of queries against it. In one case he was returning a lot of data, but STATISTICS IO was showing that very few reads were taking place.

Aaron Bertrand ◆ October 30, 2012

11

Trimming time from datetime – a follow-up

Aaron Bertrand follows up on an earlier post about trimming time from datetime, this time examining the performance characteristics of various methods without data access.

Aaron Bertrand ◆ October 25, 2012

8

Is the sp_ prefix still a no-no?

SQL Sentry's @AaronBertrand shows several reasons why you shouldn't use the sp_ prefix on stored procedures in SQL Server, including a performance hit.

Aaron Bertrand ◆ October 15, 2012

17

Checking if a non-LOB column needs to be updated

Occasionally I see people try to "optimize" their update statements to avoid writing the same value to a particular column. My understanding has always been that if you're going to update a row, assuming all of the values are in-row, the costs of locking the row are much higher than the incremental cost of updating one, two or all columns in that row.

Aaron Bertrand ◆ October 3, 2012

4

Hit-Highlighting in Full-Text Search

Hit-highlighting is a feature that many people wish SQL Server's Full-Text Search would support natively. This is where you can return the entire document (or an excerpt) and point out the words or phrases that helped match that document to the search. Doing so in an efficient and accurate manner is no easy task, as I found out first hand.

Aaron Bertrand ◆ September 24, 2012

4