Kalen Delaney

Geek City: Three New Whitepapers

Kalen Delaney — Sat, 13 Jun 2009 14:49:00 GMT

Microsoft has just published 3 new SQL Server 2008 Whitepapers, two of which were authored by SQLBloggers, right here at SQLBlog.com. Ok, I'll admit .... one of the authors is ME. :-)

SQL Server 2008 Management Data Warehouse

http://msdn.microsoft.com/en-us/library/dd939169.aspx

SQL Server 2008 Policy-Based Management

http://msdn.microsoft.com/en-us/library/dd938891.aspx

Understanding and Using PowerShell Support in SQL Server 2008

http://msdn.microsoft.com/en-us/library/dd938892.aspx

Enjoy!

~Kalen

Did You Know? I'll be teaching a public course in Boston!

Kalen Delaney — Tue, 19 May 2009 18:25:00 GMT

I receive a lot of requests for classes on the East Coast, and I am very pleased to announce my first new public training partner in the US in more than 5 years! Since I am drastically cutting back on my travel, this will probably be my last new public training partner. I am excited to be able to offer my SQL Server 2005/2008 Internals and Tuning class in conjunction with SQLBlog's own Adam Machanic at Boston SQL Server Training.

You can find the course description and registration information at the Boston SQL Server Training site.

I appreciate this opportunity to teach in New England and hopefully many of you who have been asking for me to come to your side of the country will be able to attend.

Thanks

~Kalen

Did You Know? I'll be at TechEd!

Kalen Delaney — Fri, 08 May 2009 17:23:00 GMT

I'm delivering a session on Tuesday morning (May 12) called "Controlling Your Query Plans", and for most of the rest of Tuesday, Wednesday and Thursday I'll be at the ATE (Ask the Experts) Area. Feel free to stop by!

~Kalen

Geek City: Controlling Lock Granularity

Kalen Delaney — Sun, 03 May 2009 12:51:00 GMT

In all versions of SQL Server since 7.0, the engine could choose to lock rows, pages or the entire table. In addition, even if it starts out your query execution by obtaining row or page locks, if too many locks are acquired, SQL Server could escalate to a table lock. Each lock uses memory, so when escalation replaces the thousands of fine-grained locks with one table lock, there can be substantial resource savings. On the other hand, once a table is exclusively locked, no other processes can access any data at all from it, which drastically reduces concurrency.

Way back in SQL Server 6/6.5, we had some controls over what percentage of a table had to be locked in order to induce escalation. These controls were implemented as configuration options but they were removed in SQL 7. So what controls do we have now?

Lock escalation occurs in the following situations (taken from "SQL Server 2008 Internals", MS Press 2009):

The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. Currently that threshold is 5000 locks, but it might change in future service packs. The lock escalation will not occur if the locks are spread over multiple objects in the same statement—for example, 3000 locks in one index and 3000 in another.

Memory taken by lock resources exceeds 40 percent of the non-AWE (32-bit) or regular (64-bit) enabled memory and the locks configuration option is set to 0. (In this case, the lock memory is allocated dynamically as needed, so the 40 percent value is not a constant.) If the locks option is set to a non-zero value, memory reserved for locks is statically allocated when SQL Server starts. Escalation will occur when SQL Server is using more than 40 percent of the reserved lock memory for lock resources.

Most of the discussion of escalation that I have seen indicates that people want to prevent escalation, to maximize access to the data. One way to do this is to enable trace flag 1211 on your instance, which prevents lock escalation from occurring under any circumstances. You need to be really careful with this one, because it affects every table in every database on the instance. Another option is to trick SQL Server into thinking the table is in use, because if there is even one row locked by a different connection, escalation cannot occur. So you could add a dummy row to your table and start a process that starts a transaction, updates the dummy rows, and then keeps the transaction open. Although this can keep anyone from acquiring a table lock, this method has its own nasty side-effects. Namely, as long as the transaction is open, the log cannot truncated past that point. But if you can synchronize it with the operation updating the table, and make sure this dummy update gets closed as soon as the 'real' update finishes, you might be ok.

SQL Server 2008 provides us with a bit more control, with a new option to ALTER TABLE:

ALTER TABLE <table_name>
SET (LOCK_ESCALATION = [TABLE | AUTO |DISABLE]);

The default is escalation is TABLE, and that was the only possibility for escalation prior to SQL 2008. If you set the option to AUTO, locks can escalate to a table or to a partition, if the table is partitioned. The third option is to completely disable escalation for this table. This option is much more manageable that my trick of having another transaction lock a single row, and much more fine-grained that disallowing escalation on the entire instance.

But what if you want the opposite behavior? What if you always want to take table locks in order to conserve resources? Yes, there are lock hints available to request TABLOCK or TABLOCKX, but those must be specified in every query. What if there are only a few tables that you want to always lock at the table level.

SQL Server provides an option to ALTER INDEX to disallow ROW and PAGE locks. (In SQL 7 and 2000 you could use the system procedure sp_indexoption to do the same thing.) If the index is a clustered index, this means ROW and PAGE locks will be disallowed for the table. But, if the table is a heap, you can't use this option.

ALTER INDEX <index_name> ON <table_name>
SET (ALLOW_PAGE_LOCKS = OFF);

Also note that if you set ALLOW_PAGE_LOCKS to OFF, you cannot REORGANIZE (defragment) the index.

I usually mention this ALTER INDEX option in my Internals and Tuning class, and also mention that the only time I really had to use it was when clients turned off PAGE or ROW locks inappropriately and ended up with far too many unexplained table locks. Once you check sys.indexes (there are columns called allow_row_locks and allow_page_locks, with possible values of only 0 and 1), the behavior is no longer unexplained. (For versions prior to SQL Server 2005 you can use the INDEXPROPERTY function to check the status of these options.) It was several years ago that I last encountered someone using this option to turn off finer grained locks, and I was considering removing mention of it from my course.

And then, just last week, in my Oslo class, one of students had been asking about why her queries with CTEs ended up getting TABLE locks all the time. I didn't believe it had anything to do with the CTEs, but suggested she wait until we discussed locking, which we do on the fourth day of the class. She paid very close attention, and on Friday morning she came back and told me that the problem had not been the CTEs after all, but that 'someone' had turned off the ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS options!

So I guess I continue to mention this possibility in future classes.

~Kalen

Did You Know? How to tell if a bug has been fixed?

Kalen Delaney — Wed, 22 Apr 2009 18:31:00 GMT

Maybe I should change the topic to "Do you know?". "Did you know" seems to imply that _I_ actually know the answer. It seems very tricky to know for sure if a bug has been fixed, unless you have an actual bug number from Microsoft. If you have a bug numnber, you can check the list of fixes in the KB article that contains the list of fixes. But even then, there are no guarantees. The list seems to only include bugs and fixes for which a KB article has been written. For example, http://support.microsoft.com/?kbid=955706 lists the bugs fixed in SQL Server 2005, Service Pack 3, and includes this disclaimer:

Other fixes that are not documented may be included in the service pack.

If a bug is fixed in a new release (for example, SQL Server 2008), there is no list of the bugs from the previous version that have been fixed. Even if you file (or find) a bug on Connect, the information about the the status is sometimes uncertain. Sometimes the engineers say "Will be fixed", but there is rarely a followup on Connect when that fix is actually included in a release.

I posted about a bug with generating a Deadlock Graph Event back in November 2006, and recently received a comment asking if that bug had been fixed in SP3. The comment indicated that the bug was not being seen in SP3. My first thought was that if you're not seeing it, then it must be fixed. The Connect Item indicated it wouldn't be fixed until SQL 2008, but at the time, SP3 was not being planned, so it made sense to think it had been fixed without the Connect Item being updated. But on rereading the reason in Connect, they said it was "too risky" for SP2. I would think that if it was too risky for SP2 it would be too risky for SP3. If they had said "not enough time", I might have been more likely to believe the fix could have made it into SP3.

So, did what I always like to do before posting... I tried it out for myself. Using the same script that Erland has in the Connect Item to generate a deadlock, I tried to capture Deadlock Graph on SQL Server 2005 SP3, with no filters defined. My Deadlock Graph Event showed up plain as day:

I then stopped the trace, added a filter on Database ID, and ran the same script (after dropping the table first), and no Deadlock Graph was generated. So purely by the evidence, I'd say this bug was not fixed in Service Pack 3.

So now we know about this one. What about all the others?

~Kalen

Geek City: Two Compression Questions and Parameter Metadata

Kalen Delaney — Tue, 10 Mar 2009 17:46:00 GMT

I am teaching the SQL 2008 version of my course for the first time this week. My life is still is disarray and spare time is practically non-existent. During the evenings after I class I am trying to get caught up on several projects that I fell way behind on over the last month when most of my time was spent in the hospital. Blogging has been put on the back burner, but I thought I could share some of the questions that came up in class on the first day. I usually try to track down answers to open questions, and then share them with the students the next day.

1. Why can't the stored procedure sp_estimate_data_compression_savings be available on SQL Server 2008 Standard Edition, instead of just Enterprise, Developer and Evaluation? We realize that compression is an Enterprise only feature, but if non-Enterprise users could see how much space they would save, it might help them determine whether Enterprise Edition is worth while?

(OK, here's a really geeky part... speaking of Enterprise, my #1 son sent me this link yesterday. )

Now I'm not saying that Microsoft should be obnoxious about this and say "See what you could save if you upgraded to Enterprise", and do that whether the user wants to know or not. It would be on request, when the user ran this procedure.

I also understand that the procedure actually applies compression to a sample of pages to compute the savings potential. And it was confirmed last night when I started researching this question that Enterprise features are just turned on and off through a switch so it actually would be easy enough to just not do the check for Enterprise Edition within this stored procedure. Microsoft is looking at possibly making this change, but it looks like it might not happen before the next major release.

2. Since page compression is applied on a page by page basis, what's the point of compressing a read-only table? Each page would be compressed, perhaps into a small amount of space, but if no new rows were added, the empty space on each page wouldn't get used and we would still have the same number of pages in the table.

Although it is true that compression is applied on each page individually, you need to rebuild the table or index in order to compress it, with ALTER TABLE or ALTER INDEX. And rebuilding a table or index moves the data to all new pages. So as the table is being rebuilt, when a page gets full, it is compressed, but then more rows can be added, and when are sufficient number are added, it gets recompressed, and keeps getting recompressed as more rows are added, until there is no chance of any more more rows being added. So during the rebuild process the table really isn't read-only, as the original data is being inserted into the a new copy of the table. You could end up with far few pages.

This is pretty easy to test out, by just compressing a relatively full table of your own into a test table (so no one will update it) and then compressing it. It should take less space.

There are lots of compression scripts, plus a lot more detail about how compression works, in my new book, which is scheduled for release TOMORROW!!

3. Where is the metadata that contains the default values of SQL Server stored procedure parameters?

Uh, the answer appears to be 'None of the above'. There is no metadata, either system views or system tables (viewable using the DAC) that contains this information.

You can read this Connect request for more details (and even vote for it if you like):

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=234143

It does seem strange that this information is not available and it's hard to believe SQL Server parses the procedure definition every time it wants to know the default. It turns out that even if all you want to know is whether or not a default exists is not easy to find. (You might want to know that to be able to determine if a parameter is optional.)

So now you know.

~Kalen

Did You Know? My book is at the printers!

Kalen Delaney — Wed, 18 Feb 2009 20:43:00 GMT

I know it's been a while since my last post, and there is a specific reason for that, which I am going to tell you about. On February 5th, I emailed to my editor the LAST chapter of my new book, after the author reviews and all comments from the editors were answered. I was technically done, except for a bit of work on the Intro, etc. Less than an hour after I sent off that last chapter, I got a call from the Washington State Highway Patrol that my husband had been found at the side of the road; he was semi-conscious after having a major stroke. I have spent most of the last two weeks by his side at Harborview Hospital in Seattle. Although there is WiFi in the rooms and I was able to check email and keep family and close friends informed, there was little inclination or energy for blogging.

Today, after all the final polishing by Microsoft Press, the pages were sent to the printer. The expected 'in print' date is March 11. It will be a week or so after that until the bookstores get it. I don't know how soon Amazon will be able to start shipping the pre-orders.

I'm not going to post my husband's whole story here, but suffice it to say my life will be changing. I will need to cut back on traveling, but I do need to continue working. I may not be offering nearly as many public classes, so if you've ever wanted to take a class from me, I suggest you think about doing it sooner rather than later, because there might not be a later. I will start delivering the 2008 version of my SQL Server Internals course next month. (Probably 90-95% of the course is still relevant to SQL Server 2005.)

Take care .... and in the words of one of my favorite singers/songwriters, James Taylor,

Shower the people you love with love....

~Kalen

Did You Know? I'm Going Down Under Again in June

Kalen Delaney — Wed, 04 Feb 2009 05:16:00 GMT

It's official... and Peter Ward has already blogged about it:

http://wardyit.com/blog/blog/archive/2009/01/29/kalen-delaney-in-australia-in-june.aspx

Anyone registering by March 27th will get a free copy of my new book, SQL Server 2008 Internals:

SQL Server 2008 Internals

But as always, there will be giveaways for more copies of this book and other swag at the end of each seminar.

~Kalen

Did You Know? A New SQL Server 2008 Books Online Update is Available

Kalen Delaney — Tue, 03 Feb 2009 01:45:00 GMT

When I searched for "sql server 2008 books online update", the first non-advertising hit I got was labeled August 2008, and I already have a Books Online download for August.

However, when I clicked on the link I was taken to a page to download a brand new January 2009 Update::

So here's the link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=765433F7-0983-4D7A-B628-0A98145BCB97&displaylang=en

Have fun!

~Kalen

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

Did You Know? Management Studio Got Smarter!

Kalen Delaney — Fri, 23 Jan 2009 22:32:00 GMT

Hopefully you're aware that there is no way using the ALTER TABLE statement to add a new column in a particular ordinal position within a table. I see people in the forums asking for this capability, and they may point out that it is possible to do this using the Table Designer in Management Studio. However, usually someone will point out to them that you're really not altering the table in that case, you're actually creating a whole new table. And if it's a BIG table, it can take a long time, because all the data has to be moved to the table with the new definition, and then all the indexes have to be rebuilt. You can do the same using TSQL statements, but it's not a single quick and fast operation.

I just discovered today that SQL Server 2008 Management Studio is a bit smarter about this. You are actually not allowed to save a change to a table that will require the table be rebuilt. You can, however, change that behavior to allow the change, as in older versions. I also discovered that this new behavior had already been discovered, and blogged about, by the awesome Brian Knight at

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/06/04/sql-server-2008-designer-behavior-change-saving-changes-not-permitted.aspx

Brian shows you the error message, and the the dialog to change the behavior.

So after reading Brian's post, I did some quick tests to verify that after changing the option, I could indeed make changes to a table that required a rebuild.

Then when I was getting ready to blog this, I thought I would generate my own screenshot of the error message. So I used Table Designer to remove the identity property from the AdventureWorks.Sales.SalesOrderHeader.SalesOrderID column and tried to save the table. I received a warning, but not the one I expected. It told me that half a dozen other tables would have to be changed because of my change to SalesOrderHeader. I presumed the message about not be able to actually save the change that required rebuilding the table would come next... so I clicked OK. Oops, it didn't complain. Because I had turned off the option to prevent saving such changes, and I never turned it back on again.

Wasn't there a thread going around a while ago about goofs people had made?

Oh, well. At least I have the ability to re-create AdventureWorks.

Have fun!

~Kalen

Did You Know? Troubleshooting Tools Update

Kalen Delaney — Fri, 23 Jan 2009 17:44:00 GMT

Last May, I blogged about free troubleshooting tools for SQL Server and today I received a comment that one of the tools listed seemed to no longer be available. So I thought I would publish a refresh.

There are dozens of great tools available, and I'm not even pretending that I am going to list them all. This post is really just an update to the tools I listed previously. So here is the list from my May post, with additional comments.

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

RML Utilities for SQL Server

http://support.microsoft.com/kb/944837
http://www.microsoft.com/downloads/details.aspx?familyid=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&displaylang=en

RML Utilities are still around and better than ever. You can read about the CU1 here:

http://blogs.msdn.com/psssql/archive/2008/11/12/cumulative-update-1-to-the-rml-utilities-for-microsoft-sql-server-released.aspx

And some comments from the SQLCAT team here:

http://sqlcat.com/technicalnotes/archive/2008/02/01/precision-performance-for-microsoft-sql-server-using-rml-utilities-9-0.aspx

SQL Nexus

~~http://www.sqlnexus.net/~~

The SQL Nexus tools have moved to codeplex:

http://www.codeplex.com/sqlnexus

And if you want more free tools than the ones listed here, check out everything else available at Codeplex!

PSSDIAG Data Collection Utility

http://www.microsoft.com/downloads/details.aspx?FamilyId=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

(This one is still here.)

SQL Server Performance Analysis Utilities Read80Trace and OSTRESS

http://support.microsoft.com/kb/887057

Although the KB article is still available, the links it points to are broken.

OSTRESS and Read%Trace are are incorporated into RML tools, so see the first link above!

SQL Server Health and History Tool (SQLH2)

http://www.microsoft.com/downloads/details.aspx?FamilyID=EEDD10D6-75F7-4763-86DE-D2347B8B5F89&displaylang=en

The KB articles is still there, but you can also get the Health and History Tool from Codeplex:

http://www.codeplex.com/sqlh2

Performance Dashboard

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

(This one is still here.)

I started out that post by mentioning:

I'm very excited about the new Management Data Warehouse coming in SQL Server 2008 (which was called Performance Studio at one point), but keep in mind there are lots of available tools in the current versions.

And now that I've had a chance to use the MDW a bit, I've even more excited. I think THIS is the reason to upgrade to SQL 2008 (if you haven't found another reason yet.)

Have fun!

~Kalen

Did You Know? I'm speaking at a the SSWUG Virtual Conference!

Kalen Delaney — Tue, 20 Jan 2009 23:00:00 GMT

SSWUG.org is hosting the second Ultimate Virtual Conference! Check it out at http://www.vconferenceonline.com/shows/spring09/sql/

I'll be using this opportunity partly as a test to see if it will be worthwhile to go through SSWUG to record my SQL Server Internals course. But in any case, I'm excited to be going down to Tucson to record two sessions, in the middle of February. The conference will be held April 22-24.

Early bird discounts are available, plus you get an extra discount by using my VIP key when you register: KDELVIP

Check it out! http://www.vconferenceonline.com/shows/spring09/sql/

~Kalen

Geek City: Too Many Indexes!

Kalen Delaney — Sun, 18 Jan 2009 20:37:00 GMT

I wrote about "Too Many Columns" last September, and along with changes in SQL Server 2008 that allow lots of columns, there is also the ability to create more than the old maximum of 249 nonclustered indexes on a table. I knew this fact, but somehow I overlooked it when updating the chapter on table structures in my new book. My omission also got past all the editors, and I just realized my mistake when reviewing the Indexes chapter, which was being updated for SQL 2008 by Kimberly Tripp.

I found that Books Online has not been completely updated (and I have filed a doc bug on this already). This page still says the limit is 249 nonclustered indexes per table:

http://msdn.microsoft.com/en-us/library/ms190197.aspx

However, the page for CREATE TABLE at http://msdn.microsoft.com/en-us/library/ms174979.aspx does say:

Each table can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

You can take my script to create a table with lots of columns, and edit it to create a UNIQUE constraint on every column.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 900;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int UNIQUE,';
    IF (@col % 3) = 1
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) UNIQUE,';
     IF (@col % 3) = 2
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) UNIQUE,';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create
EXECUTE (@create)

After the table is created, you can examine sys.indexes:

SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = object_id('wide900');

You'll definitely see more than 249 indexes! In addition, you might note that there is a gap in the index_id values. The values 251 - 255 are not used, and this is for backward compatibility. In much older versions, index_id (or indid) 255 was reserved for the LOB data belonging to a table, and the values from 251 - 254 were reserved. So that range is still not used. But you can see that I have index_id values from 256 - 905 in this table.

So, thanks Kim~

One more place that Books Online could use a little tweaking is in the section on Programmability Enhancements:

http://msdn.microsoft.com/en-us/library/cc645577.aspx

The section on "Wide Tables" seems to imply that you need to have an XML column set in order to get 1000 indexes. It doesn't actually say that, so it's not really a bug, but it does seem to imply it:

Wide tables are tables that contain one or more column sets. A wide table can contain up to 30000 columns, 1000 indexes, and 30000 statistics.

Because you can only reach the 30000 column limit with a wide table, it seems to imply that the 1000 index limit is only for wide tables. But as my script shows, it is not. You can have up to 1024 columns in a non-wide table, and you can now have indexes on every one of those columns.

But do you want that many? That's for another post, another time...

Have fun!

~Kalen

Did You Know? You, too, can be a Geek!

Kalen Delaney — Wed, 31 Dec 2008 03:33:00 GMT

Danny Gould has a new (beta) version of his Internals Viewer, that you can download from Codeplex at http://www.codeplex.com/InternalsViewer

I've just started playing around with it, and the coolest thing is that it integrates right with your Management Studio!

The codeplex site include a Troubleshooting Guide and a User Guide.

It does look like it can display SQL Server 2008 data pages that have been compressed. For pages with ROW compression you'll be able to see the data, but for PAGE compression, no translation is done.

You can also request to have information from the transaction log displayed, but it's not the same as the 3rd party log reader tools, in that it doesn't give you the complete set of values that were inserted or deleted.

I'm still exploring its nuances, and now you can too!

Have fun!

~Kalen