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

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

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

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

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

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

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