Kalen Delaney : Inside SQL Server

Geek City: SQL Server 2008 Internals is Orderable!

Kalen Delaney — Thu, 23 Oct 2008 22:08:00 GMT

Less than a week ago, I told you about making some major progress on my new book, and that it would "soon" be pre-orderable.

It turns out it already was orderable, as my helpful readers let me know. So here is the link to Amazon, for all of you who want to get the book as soon as it hits the streets!

I just searched through my old blog posts, and realized I've never talked about the new name! The "Inside" name was getting a bit diluted, with all kinds of other Inside books on the market, including some "Inside SQL Server" books from other authors through other publishers. Since I wanted the title to be really clear about what the book was covering, we decided to change the name.

This name change is not without precedent. Some of you may recall that there used to be a book called "Inside Windows" which morphed into "Windows Internals".

I have a terrific group of co-authors, including two people who are (or were very recently) on the SQL Server development team at Microsoft. My co-authors are: Paul Randal, Kimberly Tripp, Conor Cunningham, and Adam Machanic (who wrote the Tracing Chapter for Inside SQL Server 2005: Query Tuning and Optimization)

Thanks!

~Kalen

Geek City: Reading the Transaction Log

Kalen Delaney — Sat, 23 Aug 2008 21:07:00 GMT

Sorry, I'm not actually going to tell you how to read the log. I'm just going to talk about it... and whether it's a good thing to be able to do or not, or whether it's an absolutely crucial feature that MS needs to provide for us immediately, in a hotfix, if not sooner. Forget about fixing bugs, I want to read the log because I forgot to set up a trace beforehand....

You may have noticed that my blogging frequency has fallen off. One or two of you also noticed that I am no longer writing a regular article each month for SQL Server Magazine. Those two facts are related. I have cut back on non-essential activities to try to get my next book out as soon as possible. It looks like I might even finish in time to get the book out on the shelves by early next year. Stay tuned...

Since I couldn't bear to not do anything for SQL Server Magazine, I started writing the commentary in the weekly email newsletter. Actually, I do it every week but the fourth week of the month. If you like, you can sign up for this free newsletter here.

My commentary last Thursday seemed to have rattled some cages. Before I even woke up Thursday morning, there were already two comments on the site, and someone sent me a personal email about what I wrote. By now, there are quite a few more comments. I basically wrote about the need for a log reader tool. It wasn't deeply technical; it's just a commentary after all. You can read it here:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=100076

But boy, did people get upset. They called me bad names... well, if 'mediocre' can be considered a bad name...

So I responded as follows:

Wow... I have never gotten so many comments so quickly about one of my articles. I must really have touched a nerve here!
There is a difference between the actual data rows referenced by the log, and the log format. It's the log format, and giving people full details about what is in the log, that is propriatary information. There is nothing specifically bad about giving people that information. However, calling me names because I don't stand up on a soapbox and DEMAND that MS add this functionality seems a little extreme. There are plenty of other things MS could do with the product and providing a log reader tool is way down on the list.
Yes I realize it is important to some people, but there are many other ways to get this information through tracing etc. If the developer resources are limited at MS, I would much prefer they spend their time on more important stuff. MS knows it's important that people have this information, that's why they added a great deal of additional tracing capabilities in SQL Server 2008.
Also, keep in mind that a log reader tool wouldn't help you debug problems with logic, or with bad reports due to faulty SELECTs. If your WHERE clause was written badly, a log reader tool could tell you which rows were affected, but not WHY. You'd need a tracing tool for that. Vogelm's comment that a log reader tool would help troubleshoot bad queries from 3rd party apps is not true; you need to see the statements for that, not just the affected data.
I do appreciate kbreneman's comment that the real problem is one of perception. MS should make clear that the transaction log is not an audit tool; if you want auditing, you need to set it up on your own, because you're the only one who knows what's important for you to capture.

(The only way to respond to comments is to write a comment of my own, and then the form insists that I rate the article I am responding to. I always feel a bit weird having to rate my own articles.)

Since I wrote the article, I have found out that Lumigent does have a log reader tool for SQL Server 2005, but I have heard less than stellar reviews about its ability to capture some of the more interesting datatype activities that are now possible in SQL Server 2005. And their website still doesn't list any version numbers.

I can't stop thinking about this, so I thought I would open up the issue to a wider audience.

How important do you think it is that Microsoft provide a log reader tool for us?

Thanks!

~Kalen

Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Thu, 03 Apr 2008 19:16:00 GMT

Here is a question I just received from the feedback page on my web site:

I have finished the book <the storage engine> and like it very much. I am now reading <query tuning and optimization>
I know in the READ COMMITTED SNAPSHOT isolation, when a row is being modified in a transaction, it generates an old committed version so another transaction can read it without being blocked.
But I don’t know how SQL Server uses SNAPSHOT isolation to prevent Phantom Read being happening.
In SERIALIZABLE isolation the ranged key or the whole table is locked, but in SNAPSHOT isolation, it can NOT generate any committed version since the row does NOT exist at the moment. So how does it know that the newly inserted data should not be included in the second SELECT statement?

-Tom

Tom is correct in that INSERTS do not generate versioned rows. However, SQL Server is able to keep track of when each change was made under snapshot isolation. Once a database is enabled for snapshots, every rows inserted, deleted or modified gets an additional 14 bytes of overhead added to it. These bytes contain 2 pieces of information. First is a row pointer to the previous committed version of the row, which is stored in the version store in tempdb. This pointer is only used for deleted and updated rows, since there will be no previous values for newly inserted rows.

However, these extra bytes also include a value called XSN, or transaction sequence number, which you can think of like a timestamp for a database. Any database enabled for snapshot keeps an internal XSN value, that is incremented each time any change is made, or any snapshot select is performed. The metadata also keeps track of all active transactions, and what the XSN was when the transaction started. It uses the view sys.dm_tran_active_snapshot_database_transactions for this. So when you are reading data, SQL Server will look at the XSN number in each row, and not return any rows that have an XSN value greater than the XSN value at the time the transaction started.

I hope this helps!

~Kalen

www.InsideSQLServer.com
www.SQLServerDVD.com

Did You Know? My DVD is in Production!

Kalen Delaney — Fri, 08 Feb 2008 22:15:00 GMT

The editing for Lesson 1 is done and the production run of my DVD has started. We're expecting shipping to start in about 2 weeks.

In addition, we've extended the pre-order price for another 5 days, so any orders that come in before Valentine's Day will get the special reduced price.

Because this is taking a bit longer than we hoped, we are going to make the edited DVD available online. If you've pre-ordered, you'll be able to view the online version for free. I'll post the URL as soon as it's ready.

For now, check out http://DVD.KalenDelaney.com

Thanks!

~Kalen

P.S. Lesson 2 has already been recorded, and is now being edited.

Did You Know? I'm Going Down Under Again!

Kalen Delaney — Thu, 29 Nov 2007 20:17:00 GMT

Most of my trips to the Southern Hemisphere have been in my summer, or their winter, which doesn't lend itself to the best weather conditions. Although on my last trip, I was able to take a week holiday with my husband and travel North to the Whitsunday Islands, where the weather was wonderful even in the middle of winter, as you can see:

However, this time I'm going in the middle of our Northern Hemisphere winter, to the middle of the Australian summer!

I will be teaching two classes for WardyIT at the end of February. I'll be in Brisbane 18 - 22 February and Sydney 25 - 29 February. You can read more about the events here.

You can also find the complete course description on my website, as well as my complete schedule.

It will be great fun for me and all the course delegates...

~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

Inside SQL Server... the next book

Kalen Delaney — Tue, 14 Aug 2007 18:28:00 GMT

The last volume of Inside SQL Server 2005 (Query Tuning and Optimization) is now in the hands of MS Press. This is the first time I have worked on a Inside book as a group project, and for some strange reason I thought that meant that I could get the book done more quickly. We were desperately trying to get it done in time to be available by the PASS conference, but as the conference is in September this year, instead of the usual November, it looks like we won't make it.

I would like to extend my heartfelt thanks to my co-authors:

Sunil Agarwal (Chapter 1: A Performance Troubleshooting Methodology)

Adam Machanic (Chapter 2: Tracing and Profiling)

Craig Freedman (Chapter 3: Query Execution and Chapter 4: Troubleshooting Query Performance)

Ron Talmage (Chapter 6: Concurrency Problems)

(I co-wrote Chapter 4, and wrote Chapter 5: Plan Caching and Recompilation)

And of course, I also wish to thank all of my readers, without whom there would be no reason to write!

~Kalen