THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

TechEd 2011 - Performance Tuning and Optimization in SQL Server 2008 R2 and SQL Server Code Named "Denali"

Thanks to everyone who took the time out of their conference experience to join Mike Wachal and me for yesterday's session on SQL Server performance tuning!

For those who weren't there, we focused in on troubleshooting techniques, highlighting some of the key DMVs and new Extended Events features that will help with proactive diagnosis of problems. My section, in particular, was a bit of a taste of some of the sessions I've submitted for this fall's PASS summit: I showed a brief demo of troubleshooting using my No More Guessing methodology, and applied it to the problem of workspace memory contention. The sessions for PASS cover these topics in detail, so if you find this area interesting please vote here.

The demo script I showed yesterday is attached, along with a copy of the latest versions of my Who is Active stored procedure and the SQLQueryStress tool. Feel free to leave a comment below or drop me a line via e-mail if you have any questions.

Published Wednesday, May 18, 2011 1:05 PM by Adam Machanic


Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Gustavo Ayala said:

I liked your top(@i) trick to lower the memory grant.Overall it's a clean solution.

I'm looking for the opposite thing.

Fooling the optimizer into taking a greater memory grant (so as to no spill to tempdb).

Unfortunately (well maybe not) the query optimizer is smart enough to know that top with a greater value will not "invent rows" out of thin air if the stream below sort (or top actually) doesn't produce them.

So top won't raise the memory grant. I've tried with union all with something with the same schema and adding varchar columns.

All ugly solutions (like if there was such thing as a clean solution for this stuff fooling the query optimizer...)

Do you know a not-so-ugly one ?


August 15, 2011 9:56 AM

Adam Machanic said:

Hi Gustavo,

You're on the right track already, and you're correct that there are not many "not-so-ugly" solutions. Another option is to try OPTIMIZE FOR on something other than the TOP to mess with the seek predicates.

I'll be covering all of this in gruesome detail in my session at the PASS conference this year, if you'll be attending.



August 15, 2011 10:17 AM

Leave a Comment


About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog


Privacy Statement