In my blog post on my new sp_helpindex proc (sp_helpindex2), I mentioned that the indexes in my sample were not necessarily a recommended set of indexes - just a test set of indexes. So... in this post, I thought I'd start a series on indexes, limitations and best practices/uses... Especially, why/how to best choose when to use INCLUDE v. having columns in your key. To start, I thought I'd give some background, as well as limitations that exist in various releases from 2005 to 2008 CTP6 (Feb CTP), plus what's expected in the SQL Server 2008 RTM (ah... I did say "expected" so don't come back and yell at me if/when I'm wrong )

First, let's go through a few rules and limitations and background:

SQL Server 2005

• 250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (no, this is not a goal!)
• The index key can be up to 16 columns OR 900 bytes - whichever comes first...
• The leaf level is limited just as a table is limited to 1024 columns (and, all column types are acceptable in the leaf level of an index - even LOB columns)
• Statistics are kept for every index (so, up to 250 index-related stats) and there can also be statistics on columns or sets of columns in addition to the index-related stats. In earlier releases, statistics used index ids and as a result, the number of statistics were limited to 250 total minus the statistics used by indexes... in SQL Server 2005, they changed to having statistics kept/managed separately (use sys.stats to see them). As a result of using sys.stats, you can now have 2000 statistics on a table, in addition to the 250 (total) indexes and their statistics. If you want to test this out (and check it on various versions of SQL Server), use this script to setup a test database, a test table and then use dynamic string execution to loop through (until it errors) with creating nonclustered indexes and statistics: testindexmax.zip (.47 KB)

SQL Server 2008 CTP6

• So far, it seems as though most of the maximums have not yet been lifted...
• 250 total indexes per table: 1 clustered index and up to 249 nonclustered indexes (and this number  - for CTP6 - includes filtered indexes AND spatial indexes too!)
• The index key limit hasn't changed (it can be up to 16 columns OR 900 bytes - whichever comes first)
• The leaf level is still limited just as a table is limited to 1024 columns (and, all column types are still acceptable in the leaf level of an index)
• Statistics in CTP6 seem to be limited to only 2000 stats per table...

SQL Server 2008 RTM (expected/target... no guarantees on this one :)

• 30,000 columns per table (mostly to allow sparse columns)
• 1,000 total indexes per table: 1 clustered index and up to 999 nonclustered indexes. This is also not a goal BUT, it makes sense because of both sparse columns and filtered indexes. Both Paul and I will try to post some entries about sparse columns and filtered indexes in the coming days...
• The index key limit won't change
• The leaf level is will be limited just as a table is limited to 30,000 columns (and, all column types are still acceptable in the leaf level of an index)
• Statistics are also said to be increasing and likely to 30,000... And, for having extra statistics just sitting around and possibly not being used - well, outside of a minimal amount of disk space taken by the stat blob (which does start to get interesting at 1,000s I suppose), even stats that don't get used don't really create much of a problem. So, I'm OK with this one increasing - even significantly - but I have to admit I'm somewhat nervous about the significan't increase in indexes.........

So... you can have A LOT more indexes in SQL Server 2008 but just because you can - DOES it mean that you should?!

And on that - I'll leave you hanging for my next post where I start to talk about WHY they're increasing this (hint: sparse columns and filtered indexes = more columns/more indexes)....

Have fun,
kt

A couple of weeks ago, Paul and I recorded two interviews with TechNet Radio... both are ready for download and in multiple formats! 

Our specific interviews can be downloaded from the following links/formats:
  SQL 2008 Part 1 of 2: Security and Availability WMA | MP3 High | MP3 Low
  SQL 2008 Part 2 of 2: Management, Troubleshooting and Throttling  WMA | MP3 High | MP3 Low
  More TechNet Radio interviews (and *lots* of other shows), can be found on Channel 9.

Enjoy! 
kt

I know that Paul and I recommended that you subscribe to Conor's blog... but have you? He's posted some great details on Partitioning (Part 1 and Part 2) as well as statistics and it always reminds me of how much I can learn from other people's perspectives!

And, just to dove-tail on some of his statistics comments... I, too, have found that as tables get significantly larger AND have non-standard distributions of more than 200 distinct values (and un-even distribution between those values as well), that the optimizer just cannot possibly do a perfect job. The only way an optimizer can be good is when it can "find a good plan fast" (which I first heard from Nigel Ellis (former Development Manager of the Query Processor team) - back when he delivered a Pacific Northwest SQL Server User Group meeting many moons ago). The most important thing to realize is that it's just not possible to waste time to find the absolutely best plan... mathematically analyzing all permutations would be prohibitive - you'd have to take a vacation between query executions (wait, that's not a bad idea... I digress :).

The point:

1. Make sure that statistics are up-to-date (either through the database option: auto update stats OR by manually updating statistics)
2. Consider re-evaluating statistics over large tables (and, when poor performance occurs - look at the estimated rows v. the actual rows - if the estimate/actual are off by a fact of 10, then it could be the statistics). I'd try updating stats first and then if that doesn't work, updating with a fullscan. If neither of those work, I'd also re-evaluate other possible indexes (there are some distributions between tables being joined that just can't show a correct correlation between the values when in multiple indexes... sometimes the best index is a multi-column (ie. composite index)). 
3. Consider breaking very large tables down into smaller chunks (not just table index partitioning but possibly Partitioned Tables AND Partitioned Views) as this can give the optimizer additional details about partiticularly interesting data sets. Even in SQL Server 2008, statistics are still table-level (filtered indexes can provide some, but not complete, relief... I'll give more details in a later post) but I'd often argue that some of the best table designs are not just for a single table. Consider the statistical, locking, and indexing implications for mixed workloads against a single table (and the tremendous amount of blocking that could occur in addition to varying access patterns). And, even while 2008 will offer Partition-level lock escalation, well-designed tables may not need it! I know I've mentioned this before but different perspectives on statistics, optimizers and the fact that a good optimizer has to be efficient in-and-of itself, remind me of some of the most basic things that are also the most common problems contributing to poor query performance.

Returning to the basics and optimizing a system from the ground up always leads to better scalability!

Enjoy!
kt

Actually, I can't even begin to tell you how great it is that Paul loves blogging so much... he does an amazing job filling in everyone (even our family :)) with our travels through his "personal" posts on his blog (and, he always researches the sites/details as well). And, so, with our mostly business event to China over the past couple of weeks, Paul posted a few links on our side travels (which consisted of about 2.5 total days over our 2 week trip). I have to admit that we were originally going to try and stay for Chinese New Year (New Year's Day is Feb 7) and spend even more time tatting about but ended up cutting the trip short for more work (isn't that always the case :). But, having said that, we made every moment count by squeezing in as much site seeing as possible (we had 1 spare day in Shanghai and 1.5 spare days in Beijing). It really was an amazing trip and we hope to get back to China someday and spend even more time there.

So, if you're interested in hearing a bit more about the trip - and don't already read Paul's blog (which is surprising because he really blogs a lot and he's had some great/technical and deep posts on all sorts of stuff!!!), check out these posts on our travels:

In Shanghai the first week: http://www.sqlskills.com/blogs/paul/2008/01/21/PersonalFirstTripOfTheYearChina.aspx
In Beijing the second week: http://www.sqlskills.com/blogs/paul/2008/01/27/PersonalAndOnToBeijingAndTheGreatWall.aspx and http://www.sqlskills.com/blogs/paul/2008/02/03/PersonalFinalDayInBeijingSightseeingLotsOfPhotos.aspx

I hope your year is going well and Xie Xie (thank you!) to all of the folks that made our trip in China really special! I hope that everyone has a Happy Chinese New Year (and can travel safely through the holidays - especially given the terrible weather conditions that are happening all over China right now).

Kimberly

Well, Paul's already announced it here: http://www.sqlskills.com/blogs/paul/2008/01/17/SeeWeCanAllGetAlong.aspx and now, I just can't skip mentioning this either - especially if you're into performance tuning from the QP side of things (rather than just the SE side of things) - and, that's that there's a new blogger on SQLskills...

Conor Cunningham introduces himself here: http://www.sqlskills.com/blogs/conor/2008/01/17/HowdyComeOnIn.aspx and suffice it to say it's a blog that I'll be watching for sure!

Welcome Conor!
kt

I had started to write this blog post when we (Paul and I) were on our way back from Zurich on November 21. We had been in Zurich presenting a TechNet DeepDive on Database Maintenance Best Practices...after presenting at ITForum in Barcelona...after presenting at SQLConnections in Las Vegas (well, we did spend 30 hours at home in between those last two conferences :)). Once we returned to the US on Wednesday night, it was just before Thanksgiving and well, a few personal things prevented me from getting as much work done as planned in December... and then the holidays hit... and then I realized I was horribly behind and so I've been playing catchup ever since. Ah, Happy New Year. 

Now, I'm really back in the swing of things and I wanted to let you know what we've done!

First things first - we've posted almost all of our resources from our conferences in November (we still have a few more to tweak/post). We'll catch up with the remaining scripts this week! Check them out on our Past Events page here: http://www.sqlskills.com/pastConferences.asp.

And, now, here are my thoughts as I wrote them on November 21 - with a few updates along the way in this font:

**********

It's been a great 2.5 weeks with 4 full-day workshops, 17 sessions, 3 interviews and 8 flights to conferences/events in 3 countries. OK, Paul has been with me too and that hasn't stopped him from blogging (I know you're all thinking this :) but, he blogs shorter blogs posts and I go for quality rather than quantity (TOTALLY kidding...lol...I am SO in trouble for that one (update: I'm glad I didn't post this until after the holidays!)). And, well I've also been pushing through a bit of a cold (and, Paul doesn't sleep ;=).

So, here's a long post to catch you up on all the travels and even some of the great questions we've been asked while running around from conference to conference. First, it was a great week in Vegas for SQLConnections. We ended that week with a full-day workshop that was all hands-on on SQL Server 2005. Our 80+ attendees downloaded a VPC image and used that for the base environment for labs on Database Mirroring, Database Snapshots, Partitioning, Partial Database Availability and Online Piecemeal Restore... (Update: We received our evaluations from this session (only 37 evals were submitted) but they really seemed to enjoy it! Our reviews were fantastic (literally 4 of 4) and so, we're planning to do this again for the Spring SQL Connections. More details coming.)

And, speaking of the VPC/DVD... We've had a lot of requests for these resources (the DVD, the lab manual, the utilities, etc.) and we're already working on an updated version of this for SQL Server 2008 CTP5 which just came out on Friday (November 16) (update 1/13/2008: we've finished the November CTP update and it will be available on the SQL Server 2008 Readiness Kit). And, that's just a start! (update 1/13/2008: we've also released the DDM, check out Paul's post here). With 9 labs on the current SQL Server 2005 version, a few new exercises planned for the first 2008 version (the labs include exercises on automatic page repair for Database Mirroring AND updates for Peer to Peer including the Peer Topology Viewer, etc.) AND a second DVD on Manageability (already working on labs for SQL Server Policy-based Managment and Performance Studio (update 1/13/2008: these two are done too and they will also be available on the SQL Server 2008 Readiness Kit). I think we'll have a TON of resources to help you get started with SQL Server 2008 by the time it releases. We're also looking (for the first time!) to put together a way for you to access these resources more directly. I *promise* we'll keep you posted on that as soon as we have the final outcome! (update 1/13/2008: and, that's the Readiness Kit! Now, we just need to find out all of the details on how/when you can access it. We'll let you know as soon as it's available! It's likely that you will receive it as an attendee at a launch event - February 27, 2008. Check out the launch portal here and you can see when/where a launch event is coming to you: http://www.microsoft.com/heroeshappenhere/register/default.mspx. I can't promise that they'll all give out the Readiness Kit but, that's the most likely place (of which I'm aware at this point) where you'll receive one. If I find out any additional information, I'll post it on my blog.)

As for some of the favorites from the labs - people seem to love the Database Mirroring SQLCMD master script that sets up the High Availability Configuration for Database Mirroring. So, I thought that this might be an interesting script to post here: GenericDatabaseMirroringSetup.sql (16.73 KB). And, to make it even more flexible, I have modified this script quite a bit and made almost everything parameterized (PrincipalServer, PrincipalDNS, PrincipalPort, MirrorServer, MirrorDNS, MirrorPort, WitnessServer, WitnessDNS, WitnessPort, Database2Mirror, RestoreWithMove, BackupLocation). Also, you can decide to "move" the database being restored on the mirror (to the mirror instance's default data root) OR keep the database backup directories exactly the same (which is generally the recommended configuration). In our lab, we move the database to the instance's default directory when it's restored on the mirror because all three instances are on the same virtual machine. However, in a real-world database mirroring environment, you want to try and avoid moving the database to a different drive location on the mirror because future changes might cause the mirroring partnership to become broken. So, if you want to play with this script, be sure to modify the parameters at the beginning of the script and then make sure you test this in your environment (and, let me know if you run into any snags... I know this works in our VPC but I just whipped up the modified script relatively quickly so it's a *learning* script more than anything!). And, here are a few tips for successfully implementing a database mirroring partnership:

1. Make sure that the principal and mirror server are either identical to each other (in every way) from disk to memory to CPU, etc. When the principal and mirror are identical, you are more likely to minimize performance problems during a failover AND you're less likely to have additional slowdowns on the principal (in a synchronous mirroring configuration) by slower hardware on the mirror. If you don't have identical hardware then you want to either choose an asynchronous database mirroring configuration OR be sure to thoroughly test your before and after failover configurations to ensure that performance is "good enough" in a failover sitation. Some shops find it acceptable to run slower during a diaster (v. having downtime) and therefore they choose a secondary server (for the mirror) that's not quite as powerful as the principal... However, beware that if you're in a synchronous mirroring configuration then your mirror's performance might affect the performance of the principal.
2. Test BOTH your OLTP activity load as well as your batch load AND be sure to test over an entire business cycle. You might be surprised to find that month end processing might surprise you AND/OR a batch process that runs maintenance (for example an index rebuild OR an index defrag over a heavily fragmented table). We've seen a few customers that had network problems when under a significant load and if not tested, this could compromise your mirroring partnership by not allowing the mirror to stay in sync OR it might cause "throttling" at the principal. If the database mirroring partnership has over 1MB of of unsent log waiting, then performance on the principal will be slowed to try and help the mirror catch up.
3. Be sure not to make any assumptions about the way that database mirroring works - even if you have multiple databases being mirrored on the same server. Database mirroring is always between only ONE "principal" database and it's mirror copy. If you want to mirror multiple databases on the same server - to the same secondary server - that's possible. However, this presents additional problems in that a failover is between ONLY a single database and it's mirror. If you have an isolated database failure that causes one of (let's say four) related databases to failover, then three-part naming will suddently start failing as you have a combination of mirrors and principals on the same server and local (three part naming) won't work against a mirror database as the mirror database(s) cannot be accessed directly. As a workaround, you could create an alert on the WMI event: database_mirroring_state_change which then forces a failover for the remaining databases... Effectively all four would then failover. While this will work - and, the alert will fire relatively quickly after the first database fails over, it's important to realize that alerts (and this WMI event) are all *after* the actual failover. The secondary failovers would be asynchronous, response-based events. As a result, there could be a few transactions that fail between the first database failing over and the remaining databases failing over. In a diaster case however, this might be acceptable and if your application is well designed, you might be able to make this relatively seemless...

Also, within the SQL Server 2005 Always On VPC, we found a bug (yes, I know... the shame of it!! but (and this is our saving grace...) it's fairly minor and only requires a couple of quick changes to database mail configuration options. We decided late in the game (which is always a bad mistake) that we needed wanted to change the Windows Server name (and we all know what a pain that is!). In fact, there have always been problems associated with a server name change (at least problems for SQL Server picking up this change):

• The server's @@servername setting will not be correct. There are actually a few problems that can occur as a result of this but it's also very easy to fix. The steps are quick and Tibor (an MVP in Sweden) already blogged them here: http://www.karaszi.com/SQLServer/info_change_server_name.asp so I won't repeat.
• Jobs will have problems when edited. The problem with jobs is that they have a different "originating_server" than the new (when changed) servername and so when you open a job and try to modify it, SQL Server thinks that the server is a target server in a master/target environment. And, when you're just a target server receiving jobs from a master, those jobs cannot be modified, they can only be executed. As a result, the job cannot be changed. If you're going to change your servername, you need to make sure that your jobs change with it. Tibor also blogged the update to msdb.dbo.sysjobs that's necessary.
• This one is NEW for SQL Server 2005 - The Windows groups that SQL Server creates on non-failover cluster servers (if you're on a failover cluster, you must create the security groups manually - check out this whitepaper for more details on failover cluster setup: http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en) will no longer correspond to the server name (after the change). So far, I have not seen nor heard of a case where this creates a problem - but I won't be surprised if one of you responds with an issue or two! SQL Server 2005 uses security GROUPs to manage security and service/component-level permissions so that service account changes don't require permissions to be removed from that account name (in 2000 they did this and the side-effect was that permissions that had already been granted for some other reason - and just happened to be duplicated with the required SQL Server permissions - were removed when you changed the service account. As part of the service account name change and cleanup, they removed all of the permissions needed to run an instance of SQL Server. In SQL Server 2005, they changed this model solely to put you into the correct server group (like SQLServer2005MSSQLUser$servername$instancename) and take the former service account out.

And, of course, there are potentially a lot of [additional/other] external dependencies when you make a servername change... and, well, that's where we missed one. Part of it is because we also setup a POP3/SMTP mail server inside the VPC and when we changed the servername, we also changed the mailserver name (and then forgot to change the Database Mail settings, Outlook Express Account and SQL Server Agent Operator settings). So, you need to change the references to it. The servername was SQLHASP1 and in the June edition of the Always On DVD, we upgraded to SP2 and changed the servername to SQLHAVPC (notice the more generic name... duh!). For completeness, I'll put a bit more detail here:

Database Mail - Manage account, View existing account, check correct domain and server name
SQL Agent - Ensure operators have correct domain and NET SEND address
Outlook Express - Ensure correct server name + domain name

And, now, you should be able to sucessfully use this POP3 mail server in the lab exercises on WMI Events with Database Mirroring. For more details and references (even if you don't have the lab content) check out the links to all of the Database Mirroring whitepapers here: http://www.sqlskills.com/whitepapers.asp. (update: Paul blogged about a bunch of new whitepapers here and some of these are very new!)

**********

And, that's all I had in my November 21 version of the blog post (see, this is what happens, I write a huge post and then start researching things and put the post on the side to post later... and, later is WAY later in this case.... Sadly, I have a bunch of half-finished posts like this. More posts than time :)

So, a lot more to post over the coming days/weeks as the SQLskills team is heads down in SQL Server 2008 working to create some great content (for example, the labs mentioned above).

And, HAPPY NEW YEAR! (at least I said this before January was even over :)

Cheers,
kt

Over the past couple of years, we've had *tons* of requests for the DDM (the Dual Database Monitor - which is a little utility for testing and checking out a mirroring partnership...) and finally, we've (ha, pretty much only Paul :)) made it more flexible (it was hard-coded with specific insert procs). He's created an ini to allow implicit or explicit client redirection (to see the benefits of Transparent Client Redirect), written a help file, and he'll keep you posted with updates/bug fixes...

If you're interested, Paul's blogged about it here: http://www.sqlskills.com/blogs/paul/2008/01/13/DualDatabaseMonitorNowAvailableToOrder.aspx

Have fun and HAPPY NEW YEAR!
kt 

Every time we're at a conference, we get asked whether we're going to write any books on the kinds of things we talk about. Well, at present the answer is no - we just don't have time unfortunately. And, honestly, we like doing smaller chunks of more timely content rather than a complete book which takes a lot longer to "get to press". However, we have a plan that we'd like to gauge interest in to see whether or not it's worthwhile (Paul already blogged about this).

Basically, there are two full-day workshops that we presented this year, Database Maintenance: From Planning to Practice to Post-Mortem and Disaster Recovery: From Planning to Practice to Post-Mortem. Each of these has 100+ slides in them and we add *a lot* of content while we're presenting...just looking at the slides really isn't enough. However, our plan is to take each of these slide decks and then add comprehensive notes for each slide. Once complete, we'll create a full printout of the entire deck - with notes - for US $99.99. In addition to the printout, we will create a section on our website that includes relevant whitepaper links, KB article links, and blog post links - corresponding with specific slides/topics from the deck. Additionally, as we'd be shipping the book to you anyway, we'll throw in a free copy of the AlwaysOn DVD and the Manageability DVD, with around 20 hours of self-paced labs on them. Shipping and handling would be included for domestic US orders, and US $10 extra for non-US orders.

So, if there's enough interest in this, then we'll go forward and try to make these available for ordering by January/February 2008. So - if you're interested in this (no obligation) please drop Paul a mail.

Thanks,

kt (& pr)

Donald Wayne Tripp
Donald Wayne Tripp was born on August 6, 1944 in Fort Madison, Iowa. After fighting a long and courageous battle with cancer, Don passed away peacefully on November 28, 2007 at home in Redmond, Washington, with his daughter and son-in-law by his side.
 
Don is survived by his daughter, Kimberly L. Tripp; his son-in-law, Paul S. Randal; his grandchildren Kiera and Katelyn Randal; his Mother, Doris McDonnell; his brothers Jon McDonnell and Wayne Tripp; his sisters Linda Fussell and Bonnie Bostick; his brother-in-law, Ted Fussell; his sister-in-law Valerie Tripp; his nephew and many nieces; and his wife of 22 years, Ursula Tripp. He will be greatly missed by these and many other relatives and friends.
 
Don was very charismatic, always quick to lighten the mood with his humor - lately he'd say that "I'm in great shape - besides a little cancer". He was an avid sailor and loved nothing more than being on the water - especially Lake Michigan - where he first sailed "Flutterby", an 18 foot SolCat, and then a variety of sail-boats (and ice-boats in WI) over many years. When not on the water somehow, he loved his local haunts and enjoyed an occasional Meyers-and-tonic with friends.
 
By far his greatest pleasure was introducing friends, family, and even acquaintances to sailing - especially as a Regional Sales Manager for Hunter Yachts - which he gladly took on as his second career after working for Moore Business Forms for almost 30 years. For Moore he received numerous "Achievement Awards" in Sales but he was most rewarded by sales that lead people to the boat of their dreams. He was a graduate of the University of Iowa in 1968, a member of the Army and Air Force Reserves for six years and took his first sky dive in 1962, the year he graduated from Fort Madison High School. He loved adventure and had it not been for a hurricane that washed ashore his 40-foot trimaran in 1978, he would have raced in the (London) Observer's Singlehanded Trans-Atlantic Race in 1980.
 
Over the past few months, Don greatly enjoyed bird watching from the deck of our house. He especially enjoyed torturing the squirrels by spraying “Pam” on the poles of the many bird feeders. Don’t worry; it didn’t really stop them from getting the food – but it did provide for many hours of entertainment as they slid down the pole after lunch or on their way to having lunch.
 
A "Celebration of Life" Memorial Reception is being planned for Memorial Day weekend. Please send email to KimberlyLTripp@hotmail.com for more information.
 
In lieu of flowers, donations should be made to a Cancer charity of your choosing. For example, consider donating to the Cancer Research Foundation at http://www.cancerresearchfdn.org/donate/.