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.

PASS Summit 2011 - Zen and the Art of Workspace Memory - Demos

What a rush. Standing on the stage in an almost-full 1,000-person room, I (very) momentarily wondered what I'd been thinking when I submitted a 500-level talk for the biggest SQL Server conference in the world. But despite a rough start--my laptop crashed and I had to reboot it two minutes into the talk--I found my rhythm and the entire 90 minutes went by in a flash. I wish I'd been able to take 90 more!

The scene? PASS Summit 2011. Friday, October 14, 10:15 a.m. (Room 6E, to be exact.) The last day of one of the best PASS Summits I've had the pleasure of attending.

The topic? A fairly obscure area of SQL Server, called workspace memory. Here's the abstract for the talk:

Query Tuning Mastery: Zen and the Art of Workspace Memory

As SQL Server professionals, we often think of memory in vague, instance-level terms: buffer pool, procedure cache, Virtual Address Space, and so on. But certain tasks require a more in-depth focus, and query tuning is one of them. Large, complex queries need memory in which to work--workspace memory--and understanding the how's, when's, and why's of this memory can help you create queries that run in seconds rather than minutes. This session will teach you how to guide the query processor to grant enough memory for top performance, while also keeping things balanced for the sake of concurrency. You will learn advanced monitoring techniques, expert-level application of specialized query hints, and the memory internals needed to put it all together. If you work with large queries and are serious about achieving scalability and consistently great performance, you owe it to yourself to attend this session.

If you were in the audience, I thank you for choosing my session over the many others that were running concurrently. I had a great time, and I hope you did too.

The demos for the talk are attached to this post. Apologies, but I am not sharing the deck at this time as I'm going to be integrating it into a larger course that I hope to start delivering next year. (Through Data Education, naturally!)

Enjoy! And as always, let me know in the comments if you have any questions.

Published Sunday, October 16, 2011 9:23 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



Michael said:

Great session by the way! One question, I didn't catch the command you were using to monitor the allocated memory for a process. i.e. In your 01_WorkspaceMemoryBasics.sql you would run a query and then monitor it. hmmm after some googling was it perhaps

select * from sys.dm_exec_query_memory_grants



October 17, 2011 12:24 PM

Adam Machanic said:

Hi Michael,

Yes, I was using a combination of that and DBCC MEMORYSTATUS. Glad you enjoyed the session!


October 17, 2011 12:32 PM

Kendra Little said:

Fantastic handling of the rough start, btw--- only the slightest of pauses as you handled the crash, and you were off to the races as if nothing had happened. Thanks very much for sharing the demos!

October 17, 2011 2:48 PM

TheSQLGuru said:

Thanks for sharing your knowledge with the community Adam!

October 17, 2011 2:51 PM

Jennifer McConnell said:

Loved the workspace memory session at PASS, Adam...thanks for sharing the demos with us! A lot of good information and ideas to put to work this week now that PASS is over. Hope to hear you speak again.

October 17, 2011 4:11 PM

Dave Lafayette said:

Adam, it was a pleasure to sit in both this class as well as your pre-conference class: "No More Guessing! An Enlightened Approach to Performance Troubleshooting".

I've downloaded your queries and started pulling in baseline information on our system.  I wonder if you could answer one question regarding your baseline_queries.sql script.

In the script you are returning the AVE of r.MetricValue:

SELECT TOP(2147483647)



AVG(r.MetricValue) AS AvgMetricValue,

r.MetricValue appears to be the SUM of all samples over a 15 minute period (from the FifteenMinuteGroup view).  Shouldn't this be divided by r.CountOfRows? Something like:

SELECT TOP(2147483647)



AVG(r.MetricValue / r.CountOfRows) AS AvgMetricValue,

The results I'm getting back are averages of the sum of the 15 minute blocks. Did you leave the results this way on purpose or am I miss interpreting something?

Thanks Adam

October 17, 2011 7:13 PM

Angela said:

I was so impressed by the fact that the content of the talk you gave was evidently 500 level and not something a person could come across everyday or in a standard class.

Our server is under utilized memory-wise and now I know how to adjust it.

I have been able to come back to my job and apply what you've said to the real world spikes we deal with at least once a week.

My boss thanks you.

October 17, 2011 8:10 PM

Mladen said:

It was a great session! i just wish i didn't have to go out early. :(

October 18, 2011 9:00 AM

Tracy Bradley said:

Great session. I attended both of your sessions and, as always, I'm impressed.

I'm disappointed that you decided to not share the slide deck with the attendees. I understand that you don't want to give away the deck to those who did not attend.  I'm required by my organization to write up a trip report.  If you could at least  password protect it using the same password as the PreCon materials that would be great.

October 18, 2011 9:46 AM

Adam Machanic said:

Kendra, SQLGuru, Jennifer, Dave, Angela, Mladen, and Tracy: Thank you again for attending!

Tracy: Again, I am sorry, but this is my policy with regard to new presentations that I haven't given too many times. There are a number of reasons that I don't share these decks; it's not as simple as who attended and who didn't. What kind of info do you need for your report? I'm happy to help fill in some blanks in your mind. Drop me a line privately via the "Email" link at the top of this post and let me know what you need.

Dave: You're right, there probably should have been some division in there. But I'm not sure that it matters. As long as the number of collections is consistent, you're doing relative comparison across numbers on the same basis so everything should more or less tie out. But it is a great point none the less, especially if you're reporting on those numbers instead of just using them for comparison.

October 18, 2011 11:19 AM

Chris Wood said:

Greatly enjoyed your session Adam. I have attended 7 PASS Summits now and never fail to discover some very useful information that I would have never discovered.

I wonder how you will be able to top the presentation next year?



October 18, 2011 3:19 PM

Adam Machanic said:

Chris: top it next year?! Given the headache this one gave me, I'd say that topping it would require blowing myself up on stage. No thanks :-)

October 18, 2011 5:38 PM

Mark Holmes said:

Adam, your session was amazing.  Definitely a 500 to 700 level dive into keeping query memory predictable.  Your presentation quality was excellent, and it's obvious that you understand what you're presenting.  However, there's one disappointment I will share:

I was hoping to see your slides after the fact, in some form, so that I could refer to the information to help explain "why" something works a certain way.  In fact, I had assumed they would be available, so I didn't take detailed notes.  Personally, I can't keep up with the speed of a high level presentation enough to take notes with detail.  I can only hope that the slide content is present on the DVDs when they're released in a few months.  Without the slides, my personal retention of the content will be under 10% - just enough to remember keywords to Google/Bing/#sqlhelp, and not enough to ask you to fill gaps by email.

Maybe my expectations are unrealistic; this was my first PASS Summit and first time seeing most of the speakers in person, so I don't have a baseline established yet.

October 20, 2011 12:34 PM

Adam Machanic said:

Mark et al,

I've had a change of heart. Slides are now posted.


October 20, 2011 2:09 PM

Mike said:


    Do you have any recommendations/suggestions on autocreate statistics ON. We recently had a huge performance issue in our data transfer routine with a bad autocreate stats.Currently couple of our tables are with over 30 autocreated stats (table with 40 columns).

November 5, 2011 2:47 PM

Adam Machanic said:

Hi Mike,

I'm not entirely sure what you're asking. Did you have issues due to too many autostats getting created? Or not enough autostats? Having 30 stats on a 40 column table is neither good nor bad -- it all depends on how the data is being used.

November 8, 2011 1:00 PM

Fabricio Catae said:

Thanks for sharing the scripts! This was the most amazing session at PASS 2011. It was above level 400. :)

January 16, 2012 8:08 AM

Adam Machanic said:

For the second year in a row, I was asked to deliver a 500-level "Query Tuning Mastery" talk in room

November 11, 2012 11:13 PM

Karthik S Kola said:

Hi Adam,

I just watched the session offline, It was amazing, many thanks to you.

Also I found a little trick that I want to share with you i.e in order to find DBCC MemoryStatus results easily, see below steps, it made simple.

1. Choose (Results to Text) option from SSMS.

2. Run DBCC MemoryStatus.

3. Press Ctrl + F (Search window), then we can search the below list easily. Let's say "Buffer Pool" in this case.

Let me know if this tip make sense.


April 26, 2015 5:24 AM

Adam Machanic said:


Thanks for the tip. Glad I didn't know it at the time as it wouldn't have made for a very good joke in the session :-)


April 26, 2015 5:33 PM

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