Kalen Delaney : SQL Server Magazine

Did You Know? It's really easy to convey the wrong message when writing

Kalen Delaney — Tue, 28 Sep 2010 20:17:00 GMT

And it's also really easy to procrastinate. I meant to blog about a big misunderstanding right after last month's SQL Server Magazine UPDATE commentary appeared, but now I'm just finishing up this month's commentary and only now realized I forgot to write...(read more)

Did You Know? Dave Campbell is So Cool!

Kalen Delaney — Thu, 29 Jan 2009 19:15:00 GMT

And this picture on the cover of the latest issue of SQL Server Magazine is awesome!
The content of the interview isn't bad either.

http://www.sqlmag.com/Articles/ArticleID/101093/101093.html

This is very interesting timing, because I just wrote about Dave in my SQL Server Magazine UPDATE commentary a few weeks ago:

http://www.sqlmag.com/Article/ArticleID/101185/sql_server_101185.html

I know, I sound like a groupie, but so be it... I'm just waiting for the action figure now.

~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: Q & A on my Plan Cache Sizing article in SQL Server Magazine

Kalen Delaney — Sun, 16 Dec 2007 03:00:00 GMT

My December article in SQL Server Magazine, called Managing Plan Cache Size, expands on the information I talked about in a post last month on the limits of plan cache.

A reader of the article ask some questions on the SQL Server Magazine web site, and I have decided to use my blog to post the answers.

Q: Could you give me an illustration how to calculate target memory please? Let say there is a 64 bit system (64 bit hardware, windows 2003 64 bit enterprise edition, SQL Server 2005 Enterprise edition 64 bit, memory 30 GB), max server memory (in sp_configure) is between 16 (min value) - 30 GB (max value)).

A: Target memory refers to the maximum physical memory that can be committed to the buffer pool and ideally is the lesser of the values you’ve configured for “max server memory” and the total amount of visible physical memory available to the OS. On a 64bit system, all your memory is visible, and your max server memory is the same as your physical memory, so target ideally will be 30GB. You can see the target memory value in the metadata view sys.dm_os_sys_info (where it's called bpool_commit_target), along with the value for visible memory (called bpool_visible).
However, there are other factors that can affect the target value. The BOL entry for sys.dm_os_sys_info defines bpool_commit_target as follows:
"Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters."
The exact formula is not published. It's best to just use sys.dm_os_sys_info to see what your system's target memory value is.

Q: In your article, you give an example how to calculate plan-cache pressure limit, for example for SQL Server 2005 RTM and SP1, it calculates for on 28 target memory as follows : (.75 x 8 GB) + (.5 x 20 GB) = 6 GB. Then you add to 10 GB so pressure limit is 16 GB. Where does 10 GB come from?

A: You have rephrased the expression incorrectly. This is what the article says:
(.75 x 8 GB) + (.5 x 20 GB) = 6 GB + 10 GB = 16 GB
If you do the math, you’ll see that the first term in parentheses (.75 x 8 GB) is equal to 6 GB and the second term (.5 x 20 GB) is equal to 10 GB, so we add the 6 and the 10 together.

Q. You mention "plan-cache pressure limit". Does it mean that when , let's say pressure limit of 28 GB is 16 GB, 16 GB is reached then SQL Server couldn't remove plan cache to make it less than 16 GB, is my understanding right?

A: No, in the above example, we calculated the pressure limit to be 16 GB. That means when your SQL Server hits 16 GB of memory for plan cache, it will start removing plans from cache to keep cache from getting too big.

Q: You show how to remove a prepared query by creating a plan guide with recompile hint; is it possible to remove non-prepared query plan on the following sample:

EXEC sp_create_plan_guide @name = N'RemovePlan1', @stmt = N' SELECT * FROM Sales WHERE ContactID < 3', @type = N'SQL', @module_or_batch = NULL, @params = N', @hints = N'OPTION(RECOMPILE)';

and I issue query twice as below shown SELECT * FROM Sales WHERE ContactID < 3

When I check in syscacheobjects with the query:
SELECT usecounts as uses, sql FROM sys.syscacheobjects WHERE dbid = db_id('AdventureWorks');

It shows that the non-prepared query plan is reused with count equal to 2.

A: Althought it is possible to create a plan guide for an adhoc (non-prepared) query, your @stmt parameter must be absolutely exactly the same as the query you will run. In your plan guide creation, you have an extra space in front of the SELECT, and your @module_or_batch parameter is incorrect; it should be NULL. A correct plan guide can be used for adhoc queries, but it will NOT remove the adhoc plan from cache when you drop the plan guide, like a plan guide for a prepared query will. You will have to wait for the next version (SQL Server 2008) to get that behavior.

I hope this helps!

~Kalen