Kalen Delaney : SQL Server 2008

Did You Know? I gave two presentations last week

Kalen Delaney — Mon, 14 Jun 2010 15:05:00 GMT

Even though I didn't make it to TechEd this year, it didn't mean I was quiet last week. On Wednesday, I was in Colorado, giving a talk for the new Colorado PASS User Group, which is a joint venture between 3 different existing groups from Colorado Springs,...(read more)

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? 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

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? I just submitted my last chapter!

Kalen Delaney — Fri, 05 Dec 2008 18:38:00 GMT

Less than an hour ago, I sent my last chapter of my next book "SQL Server 2008 Internals" to my editor!

Note that I said "my" last chapter, not "the" next chapter, as two of my co-authors still need to complete their chapters. But I've finished my chapters! Now this is still more to do, in addition to the waiting for the other two chapters to arrive. There are author reviews, and some rewrites, and Introduction, etc, to write, but after all these years, I know that stuff is minor.

So it's really going to happen... one more book!

Although Amazon indicates it will be available February 18, it probably will be out in mid-March.

Thanks for all your support.

~Kalen

Did You Know? You can search the web-only Books Online

Kalen Delaney — Wed, 12 Nov 2008 01:22:00 GMT

You might be aware (from other bloggers on this site) that there is a new web-only Books Online for SQL Server 2008 available at http://msdn.microsoft.com/en-us/library/dd239407.aspx.

Like Tibor, I prefer a downloadable BOL, partly because I do a lot of research and writing while traveling and I don't always have an internet connection, like at 42,000 ft (~14000m) above the Atlantic Ocean.

However, I need the latest documentation while I am finishing up my book, so I decided to start using it today, now that I am back from my travels to the UK. However, I discovered another reason while I like the desktop copy of BOL better and that is the searching functionality. The web-only BOL only has the search box to search the ENTIRE MSDN site, and it's not nearly as convenient as the search capabilities in the local documentation.

To my delight, I just found out from Alan Brewer, from the SQL Documentation Team, that Microsoft has a nice search macro to help limit your search on MSDN to just SQL Server 2008 topics:

http://search.live.com/macros/sql_server_user_education/sql2008bolsearch/

It's still not as nice as the local searching, but since web-only is all we have for now, for the most recent BOL, it's definitely a good thing.

There is also a 2005 online search macro at:

http://blogs.msdn.com/sqlserverue/archive/2007/10/01/eliminate-extranneous-search-results.aspx

Have fun!

~Kalen

Geek City: Clearing a Single Plan From Cache in SQL Server 2008

Kalen Delaney — Fri, 31 Oct 2008 23:43:00 GMT

I just today found out about something that was in plain sight in the Books Online... but the documentation is so vast, I haven't discovered everything yet.

I blogged about clearing a single plan from cache in SQL Server 2005 a while back, and mentioned a rumor that there would be a new way to do this in SQL Server 2008. And then just today, someone posted a comment on that blog post, asking about the new 2008 method.

One of my contacts on the SQL Server team at Microsoft just sent me an email telling me about new features related to plan caching, as I am starting to work on updating that chapter for my 2008 book. He mentioned that DBCC now takes parameters, and one of the parameters is a plan handle!

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle | pool_name } ) ] [ WITH NO_INFOMSGS ]

There is full example in the Books Online, so I won't repeat it here. If you don't have a local copy of BOL handy, you can read about the new option to DBCC FREEPROCCACHE online here:

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

Have fun!

~Kalen

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

Geek City: Too Many Columns!

Kalen Delaney — Sun, 21 Sep 2008 01:52:00 GMT

As I am working on my new SQL Server 2008 internals book, I am finding many test situations in which I need to create a table with LOTS of columns. First of all, you're probably aware of the new SPARSE column feature that allows you to have up to 30,000 columns in a table! I did some testing with sparse columns, but just today I realized I had never actually created a table with more than the old limit of 1024. In addition, the new row compression had some special tricks for dealing with lots of columns, and groups columns into clusters of 30 columns each. (You'll have to wait for the new book to get the details.)

Yesterday, when writing about row compression, I realized I needed an easier way to create wide tables, so I wrote a script that allowed me to specify the number of columns, and also get a little creative with the datatypes, lengths and properties of the columns I was creating. I'll show you that script in just a minute!

Then just today, I read a blog entry by Simon Sabin, who indicated that even with sparse columns, you could not create a table with more than 1024 columns. In the CREATE TABLE statement, you had to limit yourself, but then could use ALTER TABLE to add more columns. Although I hadn't tested this yet, I was intrigued, and realized I could use my new script to test out this claim.

So here is my original script. You can replace the 100 (for the value of @numcols) by the number of columns you'd like, but the script will create one more, because it starts with a ID int identity column. So the script as is will create a table with 11 columns. In the loop that adds columns to the creation string, I had three different datatypes of columns that I can use. One third of the columns will be type int with a default of 0, one third will be char(5) with a default of 'hello' and one third will be varchar(25) with a default of 'this is a longer message'. You can modify this however you like. You can change the datatypes within the loop, or add more conditionals and change expression to use modulo of some other number.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 100;
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 default 0,';
    IF (@col % 3) = 1
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) default ''hello'',';
     IF (@col % 3) = 2
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) default ''this is a longer message'',';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create
EXECUTE (@create)
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);

To use this script to test the creation of a table with more than 1024 columns, I knew some (or most?) of the columns would need to be sparse. In addition, I found this Connect entry which indicated that if you do have more than 1024 columns, the table must have a column set, so it was easy enough to make that the last column that is added to the create string after the loop.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 1200;
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 sparse,';
    IF (@col % 3) = 1
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(5) sparse,';
     IF (@col % 3) = 2
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) sparse,';
       SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' xml column_set for all_sparse_columns);';
PRINT @create
EXECUTE (@create)
EXEC ('INSERT INTO '+ @tabname + ' DEFAULT VALUES');
EXEC ('SELECT * FROM '+ @tabname);

Running this script, I was able to create a table with 1200 columns.

And you can too, if you're using SQL Server 2008.

Have fun!

~Kalen

Did You Know? It's Official...I'm a Geek!

Kalen Delaney — Tue, 16 Sep 2008 17:08:00 GMT

I almost didn't respond to the request for an interview with Richard Morris of Simple Talk. There were a few strange things in the request that made me think it might be spam or phishing. But I asked some trusted colleagues, and found out it was for real, and you can read the result here:

http://www.simple-talk.com/content/article.aspx?article=563

Thanks, Richard!

~Kalen

Did You Know? My First Talk on SQL 2008

Kalen Delaney — Thu, 03 Apr 2008 00:13:00 GMT

I gave my first SQL Server 2008 presentation yesterday, and also spoke at a Launch Event for the first time. It was a terrific experience, primarily because it forced me to really start digging into SQL Server 2008. I had been doing so much 2005 training recently, I just really hadn't had a chance to really USE 2008, although I have dutifully installed all the CTPs.

My one-hour talk covered 3 main areas:

Policy-based Management
Performance Studio
Multi-server Management

In my prep the evening before, I had been able to go through the whole thing in about 55 minutes, and I usually end up talking faster when in front of an audience. So figured I had time to show a few extra things in the demos, as well as show off the new ZoomIt presentation tool that I talked about in my previous post.

I have no idea what happened, but I was just barely finished with the first demo, and I looked at the clock and realized 45 minutes had gone by! I have absolutely no idea how that happened, and I would really like to apologize to all the people in the audience for having to deal with me rushing through the Performance Studio demo and not being able to say much at all about Multi-server management. I must have just been having too much fun with policies, but that is really no excuse.

I will be giving the same talk at our local (Puget Sound) .NET Developers Association Meeting on April 21, so if you want to hear the rest of the story, and see the rest of my demos, come on by!

Thanks!

~Kalen