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

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

Michael — Mon, 17 Oct 2011 16:24:50 GMT

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

Thanks,

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

Adam Machanic — Mon, 17 Oct 2011 16:32:54 GMT

Hi Michael,

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

--Adam

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

Kendra Little — Mon, 17 Oct 2011 18:48:15 GMT

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!

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

TheSQLGuru — Mon, 17 Oct 2011 18:51:12 GMT

Thanks for sharing your knowledge with the community Adam!

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

Jennifer McConnell — Mon, 17 Oct 2011 20:11:07 GMT

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.

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

Dave Lafayette — Mon, 17 Oct 2011 23:13:50 GMT

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)

m.MetricId,

m.MetricName,

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)

m.MetricId,

m.MetricName,

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

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

Angela — Tue, 18 Oct 2011 00:10:13 GMT

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.

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

Mladen — Tue, 18 Oct 2011 13:00:37 GMT

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

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

Tracy Bradley — Tue, 18 Oct 2011 13:46:47 GMT

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.

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

Adam Machanic — Tue, 18 Oct 2011 15:19:49 GMT

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.

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

Chris Wood — Tue, 18 Oct 2011 19:19:49 GMT

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?

Thanks

Chris

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

Adam Machanic — Tue, 18 Oct 2011 21:38:14 GMT

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 :-)

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

Mark Holmes — Thu, 20 Oct 2011 16:34:07 GMT

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.

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

Adam Machanic — Thu, 20 Oct 2011 18:09:56 GMT

Mark et al,

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

--Adam

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

Mike — Sat, 05 Nov 2011 18:47:38 GMT

Adam:

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).

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

Adam Machanic — Tue, 08 Nov 2011 18:00:12 GMT

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.

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

Fabricio Catae — Mon, 16 Jan 2012 13:08:22 GMT

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

Query Tuning Mastery at PASS Summit 2012: The Demos

Adam Machanic — Mon, 12 Nov 2012 04:13:11 GMT

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