Kalen Delaney : UK

Did You Know? It's SQL Know How!

Kalen Delaney — Fri, 26 Sep 2008 16:05:00 GMT

I am very excited to be part of a new SQL Server Training Group, which was organized in the UK by 4 SQL Server MVPs (current and former). I know this is a shameless plug, but I am not apologizing. I am actually going to the UK for the benefit of the SQLKnowHow guys, and I'm just trying to help them out even more by telling you all about their events!

SQLKnowHow has been offering one day seminars and multi-day training events since last summer, and my first event with them will in early November.

This is, of course, not my first trip to the UK, or my first time speaking at an event organized by my friend Tony Rogerson, founder of the (in)famous and awesome UK SQL Server Community.

The venue is in Harpenden, Hertfordshire, which I happen to know well, because that is where Tony lives, along with his wonderful wife and his amazing little boy. In truth, I am actually looking forward to seeing Tony's son even more than Tony. (After all, I can see Tony at conferences, and can communicate by email whenever I need to. Plus (sorry Tony), his son is much cuter.)

Now that the previous event with Paul Nielsen has completed, seats are starting to fill fast for my seminars. I am presenting two one-day seminars and a three-day course:

3 November: SQL Server Data Storage Formats: Internals, Performance and Best Practices
(I have just finished writing the chapter for my next book on all the cool new storage, with compression, sparse columns and filestream, and I am really excited about this topic!)

4 November: SQL Server Concurrency Control: Locking, Blocking and Row Versioning
(This is always one of my favorite topics, because it is crucial to good performance, but is frequently overlooked in tuning discussions.)

5-7 November: SQL Server Data Internals and Tuning
(This 3-day seminar is a condensed version of my 5-day course which I have taught all over the world. We will start where the Data Storage Formats seminar leaves off, and look at how your storage structures can affect query performance. We'll go deeper into the structure of indexes and then talk about how your choice of indexes affects your query performance. We'll look at query tuning techniques, and plan caching, including new behaviors in SQL Server 2008.)

The great guys at SQLKnowHow are offering an early bird discount, too. Plus swag... plus great food....what else could you want?

See you there!

~Kalen

Did You Know: My Fall Travel Plans

Kalen Delaney — Mon, 01 Sep 2008 00:48:00 GMT

If you check my schedule, you'll see my summer and early Fall are spent mostly close to home. This is a good thing, because I have a book to write!

But starting in October, I'll be gone at least every other week, with two trips to Europe and one to the East Coast of the US.

In early October, I'll be presenting at a multi-city Summit for the Stockholm-based training company, Cornerstone. Although I've been to Stockholm several times, the Summit will take me to 3 other Swedish cities which I have never visited. And of course, one of the best parts of the trip is that I'll get to spend time with my good friend Tibor.

Following that is a private class in Connecticut, where I have several very good friends, including MVP Roy Harvey.

In early November, I am very excited to be presenting 3 seminars in the UK for SQLKnowHow. This is a collaboration of UK-based SQL Server consultants and trainers who are putting together a whole suite of events. I'll be presenting two one-day seminars and one three-day seminar, and getting to work with my friend Tony, as well as his partners Simon Sabin, Allan Mitchell Darren Green.

I'll get back from the UK in time to go to Colorado Springs to help my mother celebrate her 90th birthday.

Near the end of November is the big PASS conference, at which I'll be delivering a pre-conference seminar, as well as a spotlight session.

In early December, I finish out my year's travels with a 5-day public class at my oldest partner, SQL Soft+. It's in Portland, Oregon, which is about a 3.5 hour drive for me, so I can take my own car, and not worry about those silly airline baggage restrictions!

I hope to see many of you at some point in my travels!

~Kalen

Did You Know? SP2 does NOT limit the amount of plan cache you can have

Kalen Delaney — Mon, 05 Nov 2007 02:39:00 GMT

As I am preparing my talk for the UK SQL Server Users Group on November 16, I was going through other presentations on plan cache management. At the PASS Conference in Denver in September there was a session on management of cache, and I was surprised to find a slide that indicated there was a hard upper limit for plan cache size, and that the limit was actually decreased as of SQL Server 2005 SP2. This is not true. There is no upper limit to plan cache size, other than any upper limit you might have on total SQL Server memory. But usually we don't want to use too much memory for query plans, because it will reduce the memory available for data cache. In fact, there is a situation called 'plan cache bloat' where memory gets filled with very cheap adhoc plans that aren't reused, but take up memory space that could be much more effectively used for other purposes. It is to reduce this plan cache bloat that the caching management algorithm changed in SP2.

The changes do NOT enforce an upper limit. The change actually computes a plan cache size at which SQL Server recognizes that there is memory pressure, and it will start removing plans from cache. The plans to be removed are the cheap plans that have not been reused, and this is a GOOD THING.

The following explanation is based on a section in Chapter 5 of Inside SQL Server 2005: Query Tuning and Optimization, used by permission of the author. :-)

---------------------------------------------------------------------

When discussing memory pressure, we refer to the term ‘visible’ memory. Visible memory is that which is directly addressable in the SQL Server process’s virtual address space. On a 32-bit SQL Server instance, the maximum value for the visible memory is either 2 GB or 3 GB, depending on whether you have the /3GB flag set in your boot.ini file. Memory with addresses greater than 2 GB or 3 GB is only available indirectly, through AWE-mapped-memory. On a 64-bit SQL Server instance, ‘visible’ memory has no special meaning, as all the memory is directly addressable. In any of the discussion below, if we refer to visible target memory greater than 3 GB, keep in mind that is only possible on a 64-bit SQL Server. The term ‘target’ memory refers to the maximum amount of memory that can be committed to the SQL Server process. Target memory is the lesser of the value you have configured for ‘max server memory’ and the total amount of physical memory available to the operating system. So ‘visible target memory’ is the visible portion of the target memory. Query plans can only be stored in the non-AWE-mapped memory, which is why the concept of ‘visible’ memory is important. You can see a value for visible memory, specified as the number of 8-KB buffers, when you run the DBCC MEMORYSTATUS command. The section called ‘Buffer Counts’ displays values for Visible memory as well as Target memory.

SQL Server defines a ‘plan cache pressure limit’ value which varies depending on the version you’re running, and the amount of visible target memory. The formula for determining the plan cache pressure limit changed in SQL Server 2005, Service Pack 2. The table below shows how to determine the plan cache pressure limit in SQL Server 2000 and 2005, and indicates the change in Service Pack 2, which reduced the pressure limit with higher amounts of memory. Be aware that these formulas are subject to change again in future service packs.

As an example, assume we are on SQL Server 2005, Service Pack 1, on a 64-bit SQL Server instance with 28 GB of target memory. The plan cache pressure limit would be 75% of 8GB plus 50% of the target memory over 8 GB (or 50% of 20GB), which is 6GB + 10GB or 16 GB.

On SQL Server 2005, Service Pack 2, on the 64-bit SQL Server instance with 28 GB of target memory, the plan cache pressure limit would be 75% of 4GB plus 10% of the target memory over 4 GB (or 10% of 24GB), which is 3GB + 2.4GB or 5.4 GB.

---------------------------------------------------------------

There are more details about different caches and global vs local pressure, that I'm not going to go into detail about here. But to simplify, when SQL Server detects that a cache has reached 75-80% of the cache pressure limit, it will consider the SQL Server instance to be under memory pressure. When memory pressure is detected, all zero cost plans will be removed from cache and the cost of all other plans is reduced by half.

For the rest of the details, you can read the Query Tuning and Optimization book. The point I wanted to make is that there is no hard limit and having memory pressure kick in is a good thing to prevent cache bloat. I have actually heard of people not upgrading to SP2 because they thought it would mean they would have less space for plans in cache. Even under memory pressure, plans that are regularly reused will NOT be removed from cache, so all you'll be doing is getting rid of the clutter, and have more room left for the important stuff.

Have fun!

~Kalen

Did You Know? A P.S. to my PASS presentation

Kalen Delaney — Thu, 27 Sep 2007 20:51:00 GMT

This is hot off the presses!

I will be presenting for the UK Server Server UG in Reading, November 16 at the Microsoft campus. The presentation will be similar to my preconference session at PASS, with a few enhancements and extra details.

You can read about it here:

http://sqlserverfaq.com?eid=103

If you're UK-based, I hope to see you there!

~Kalen