OK, in my quest to blog more often, I realized I have the perfect thing to blog about today... stuff I did that I haven't yet told you about. :) :)

At TechEd US, Paul and I were on a panel created to debunk some of the more common questions/concerns when setting up High Availability. It's an interesting panel and I think there are some particularly interesting moments based on the strange turns the discussions take... but, overall, it's expecially good for people seeking a 100-200 level overview of HA (Clustering, Database Mirroring, Replication, Log Shipping) with a few other related topics (Database Snapshots and Backup/Restore). Here's the direct link to the recording: http://mfile.akamai.com/14853/wmv/microsofttec.download.akamai.com/14853/TechEdOnline/Videos/08_NA_ITP_TEOPanel_57_low.asx. And, here's a general link to everything TechNet so that you can check out TechNet Radio, TechTalks, etc. there's some really great stuff out there: http://technet.microsoft.com/en-us/default.aspx. Have fun!

Also at TechEd US, I hung out for a bit in the bloggers lounge...when it was just about to end, it turned out there was a "Women in Tech" panel starting - in which I was then asked to join in. I have to admit that I'm sometimes reluctant to get involved in these because my general (and this is *very* general) opinion is that it doesn't matter if you're a woman or a man (wait - wait - let me explain!!). What I mean by this is that the key to ANY job is that you have to enjoy it. You need to find something in it that's exciting. If you can do that - then you'll be good at your job... plain and simple. If you don't like it (regardless of whether you're a man or a woman) then it's never going to be satisfying and/or you're never going to get ahead. Having said all of this, I do have to admit that I find it especially exciting when I do find other women that can geek out and really enjoy doing this whole IT thing (like MLB, Kate Gregory, Stacia Misner, Julia Lerman, Kalen Delaney, Eileen Brown). So, I don't really intend on actively trying to get more women into technology but the fun part about this panel was the fact that a lot of us talked about how children these days are already way more into IT - without realizing it (our girls asked what film was recently because we were scanning a lot of negatives... and, can you imagine any of our lives without cell phones??). And, my hope, is that if people find ways of getting more into IT (and computers/digital stuff - in general) at an earlier and earlier age... then maybe more kids (girls as well) will find a passion in IT. Here's a link to the panel from TechEd: http://mfile.akamai.com/14853/wmv/microsofttec.download.akamai.com/14853/TechEdOnline/Videos/08_NA_ITP_TEOPanel_67_low.asx.

And, here are a few resources regarding Women In Technology:

• WITI - Women In Technology International: http://www.witi.com/
• Women in Technology Organization: http://www.womenintechnology.org/
• Womin in Technology (a government funded project): http://www.womenintech.com/
• Girl Geek Bloggers: http://www.geekgirlblogs.com/Home.aspx (which has a lot of links to other orgs - including the City-based "Girl Geek Dinners" like London Girl Geek Dinners, etc.)

And, I think that's it for now...

Thanks for reading,
kt

I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and for SQL2008 it also adds the filter predicate. So, there were two versions of sp_helpindex2 depending on which verison you're using. A lot of folks like the changes to this sp but, alas, it had a bug (or two :) and in fact, I found a few others when I went back over this as well. So, thanks to Josh (who commented here) and to a private email (thanks Vasco!), I have an updated version of sp_helpindex2:

For SQL Server 2005, here's your new sp_helpindex2 script: sp_helpindex2_2005.zip (2.89 KB)

And, here's a simple test script for 2005:

DROP TABLE tbl1
GO

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
GO
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)
go

sp_helpindex2 tbl1
go

index_name	index_description	index_keys	included_columns
ix_1	nonclustered located on fg1	c1	c2
ix_2	nonclustered located on fg1	c1	NULL
ix_3	nonclustered located on fg1	c1	c2, c3
ix_4	nonclustered located on fg1	c1, c3	c2
ix_5	nonclustered located on fg1	c3	c1, c2, c4
ix_6	nonclustered located on fg1	c1, c2	c3, c4

And, here's a simple test script for 2008:

DROP TABLE tbl1
GO

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)

CREATE INDEX ix_1f ON tbl1(c1) INCLUDE (c2)
WHERE c3 IS NOT NULL

CREATE INDEX ix_2f ON tbl1(c1)
WHERE c4 > 2

CREATE INDEX ix_3f ON tbl1(c1) INCLUDE (c2, c3)
WHERE c4 > 2 AND c1 < 50 AND c2 = 12

CREATE INDEX ix_4f ON tbl1(c1, c3) INCLUDE (c2)
WHERE c4 IS NOT NULL AND c1 = 12

CREATE INDEX ix_5f ON tbl1(c3) INCLUDE (c1, c2, c4)
WHERE c1 > 5

CREATE INDEX ix_6f ON tbl1(c1, c2) INCLUDE (c3, c4)
WHERE c4 < 20
go

sp_helpindex2 tbl1
go

index_name	index_description	index_keys	included_columns	filter_definition
ix_1	nonclustered located on PRIMARY	c1	c2	NULL
ix_1f	nonclustered located on PRIMARY	c1	c2	([c3] IS NOT NULL)
ix_2	nonclustered located on PRIMARY	c1	c2	NULL
ix_2f	nonclustered located on PRIMARY	c1	c2	([c4]>(2))
ix_3	nonclustered located on PRIMARY	c1	c2, c3	NULL
ix_3f	nonclustered located on PRIMARY	c1	c2, c3	([c4]>(2) AND [c1]<(50) AND [c2]=(12))
ix_4	nonclustered located on PRIMARY	c1, c3	c2	NULL
ix_4f	nonclustered located on PRIMARY	c1, c3	c2	([c4] IS NOT NULL AND [c1]=(12))
ix_5	nonclustered located on PRIMARY	c3	c1, c2, c4	NULL
ix_5f	nonclustered located on PRIMARY	c3	c1, c2, c4	([c1]>(5))
ix_6	nonclustered located on PRIMARY	c1, c2	c3, c4	NULL
ix_6f	nonclustered located on PRIMARY	c1, c2	c3, c4	([c4]<(20))

Have fun!
kt

YES!!!

OK, well, I guess I should be more specific because as in most things in SQL Server, the real answer is "it depends". And for these two options, it depends mostly on your SQL Server version. Since SQL Server 7.0, the way that auto update works, has changed (much so for the better!!). So, if you're in SQL Server 2005 or SQL Server 2008, I would say most definitely - leave these ON (or if you turned them off - turn them back on!!!)! If you still have problems with a specific index causing your grief, then turn off auto update at the index level NOT at the database level. To turn of auto update at the index level use STATISTICS_NORECOMPUTE in the index (or statistics) definition.

Now, as for why (and how!) this has changed over the versions... here we go:

SQL Server 7.0

• Invalidation: Statistics were *invalidated* when a row modification counter (sysindexes.rowmodctr) was reached. This meant that they could not tell where the modifications were occuring and, if modifications were somewhat isolated to a specific column ALL of the statistics for the TABLE would be invalidated (so, statistics could be invalidated earlier than necessary)
• Updating: Even worse, in SQL Server 7.0, when statistics were invalidated, they were immediately updated. This caused two problems - thrashing at the time of invalidation because all of the stats needed to be updated AND two, if the statistics were not used for awhile then extra work was involved to update them and by the time they were used, they might already be somewhat out of date already.

SQL Server 2000

• Invalidation: Statistics were still invalidated based on a row modification counter.
• Updating: SQL Server 2000 fixed the "updating-potentially-too-often" problem by only updating statistics when they were needed.

SQL Server 2005

• Invalidation: The biggest changes were introduced in SQL Server 2005 where they decided to NO LONGER user the sysindexes.rowmodctr and instead use an internal (and undocumented) columns specific modification counter. Now, statistic invalidation is more isolated to only those columns which are heavily modified. This internal/undoc'ed column is sysrowsetcolumns.rcmodified and can only be seen when connecting to SQL Server using the DAC (Dedicated Admin Connection).
• Updating: Updating didn't really change but, SQL Server 2005 added "Aynch Auto Update" for statistics so that when the QO (query optimizer) encounters an out-of-date (i.e. invalidated) statistic, they can "trigger" the update but not wait for the update (meaning that they'll optimize using the out-of-date statistic). This can be both positive (faster) and negative (might not be the best plan if the statistics have changed drastically). It is off by default and IMO, I'd leave it off in most cases but if you find that auto update events (which can be Profiled) are causing you grief, then you can turn this on at the database level.

SQL Server 2008

Nothing new except "Filtered Statistics" and these are interesting as the density vector is still relative to the table (not the predicate) but the histogram is just over the predicate (OK, I know I'll have to blog a lot more about this one!). Anyway, I'm still playing/learning a lot more about these and they make the most sense with filtered indexes (as opposed to just a filtered statistic) but, just like statistics on secondary columns you will also potentially want statistics on the secondary columns of your indexes. The next question is should they have a filter or not. I've found that sp_createstats doesn't seem to create statistics with filters and I'm going to need to do some testing here but I think statistics with filters (filters that match the non-clustered index) should help to make the stats better (and even allow better usage of filtered indexes) but, I'm really going to need a bunch of time with this - and another post :). As for auto create/auto update - no changes there!

Long story short, if you're using SQL Server 2005 or SQL Server 2008, you should leave auto create/auto update ON.

Thanks for reading!
kt

PS - A few of you have mailed me about a bug in the sp_helpindex2 script(s). OK, that's my next post!!! Possibly with an sp_helpstats2 script as well!

OK, so SQL Server 2008 came out *BEFORE* Visual Studio SP1 but VSSP1 was required (this was a bit annoying and created a lot of confusion/emails/newsgroup posts, etc.) but luckily VSSP1 came out only a couple of days after SQL Server 2008 RTM'ed. As for Data Dude, well, it's not RTM'ing until later this year (I think there will be one more release - probably an RC0 - before RTM) but they've been pushing out various CTPs... so, what works with what and how the heck do you get everything to install?? Oh my!

Really, it's very simple:

1. Install Visual Studio 2008 (with Team System/Database Edition), then, install SP1
2. Install SQL Server 2008
3. Download/Install the latest Data Dude CTP. BOTH CTP15 and CTP16 work with SQL Server 2008 RTM but 16 has a lot of updates.

SQL/VS can really be installed in any order but this (above) is what was recommended as the best order. However, I've not heard any problems with SQL then VS. As for where to go to get more details on Data Dude - check out Gert's "Data Dude" blog for links to the downloads and a lot of tips/tricks.

Have fun!
kt

OK, so, I don't blog very often. I don't know what it is... I think it's that I feel like I always need to blog huge posts and the thought of writing my huge post makes me not want to blog... so, sometimes my time-between-blogging (TBB :) is long. I'm going to turn over a new leaf (no, really... I'm *really* going to try this time!!) and I'm going to try and write smaller posts and more of them. And, yes, don't worry, I'll still write the long in-depth ones but I'll at least try to give you a few tidbits of things that I encounter - more frequently. And, that's partially why I'm posting this entry...

I've been working quite a bit with SQL Server 2008 and yesterday I went to test of a few things with database mail. Normally this gets setup quite early and gets setup with a lot of other things so restarts of the server/agent happen because of saving/tweaking my test VPC. I don't usually go in and setup Database Mail and then immediately try to send mail. Yesterday, I did... and, guess what, it didn't work. I got error [264] An attempt was made to send an email when no email session has been established. So, I felt like I had seen that before... and, with a bit of web searching I landed on Gops Dwarak's blog for a known issue of SQL Server 2005: http://blogs.msdn.com/gopsdwarak/archive/2006/04/25/583434.aspx. And, yes, restarting the SQL Server Agent solved the problem. I'm surprised that this is still a bug in 2008 but it's not entirely the worst I could come upon. However, having said that, I also thought that there was a general initiative for software to stop requiring restarts of services and/or the OS. And, yes, it's *just* an Agent restart so it won't directly impact your server's availability but, I'm still surprised that it wasn't resolved. Has anyone else found a bug (or even a documented "issue") that requires a service to be restarted - and you think it shouldn't?

And, well, that's definitely not the most interesting thing I've learned/found with SQL Server 2008, there's lots more (yes, I know - I need to blog a lot of it :). The most fun I've had has probably been learning sparse columns and filtered indexes. For right now, I want to give you some quick key points about some of the changes around indexes and in particular around sparse columns/filtered indexes:

1. Do NOT create non-clustered indexes on sparse columns without filters (a filter that says WHERE sparsecolumn IS NOT NULL) because indexes do NOT have sparse columns in their definition. So, if you don't use a filter you will end up storing all of the NULL values in the index - which will waste a tremendous amount of space.
2. A non-clustered index on a sparse column (without a filter) will be the same size as a nonclustered index on a non-sparse column (which is essentially the same point as above but, this might help clarify it a bit).
3. Do NOT think that the increase in total indexes (from 250 in 2005 to 1000 in 2008) is because you should have more indexes... it's NOT. It's specifically because you might have a lot more columns (these changed as well from 1024 in 2005 to 30000 in 2008) because even that increase should not be used unless you're using sparse columns... So, you STILL want to use a lot of the best practices we've recommended in past webcasts, whitepapers, etc. and you still want to care about row size (and page density) and therefore work to create narrower tables (in general and relatively speaking - depending mostly on usage patterns). However, if you have a need for *lots* of wider tables BECAUSE you have an interesting set of properties that only some rows will have (i.e. the main reason to use sparse columns) then using sparse columns to handle these columns that will largely be filled with NULLs is a good thing. It's a good thing because a row that has a NULL for a sparse column will take ZERO BYTES. Absolutely no space is used for a sparse column that is NULL. So, this allows your tables to be wide (in definition) but your rows to be narrow (in practice). And, with well defined indexes you can VERY efficiently and effectively search on these properties.

OK, I hope to get some examples posted as well. Between Paul and I and our upcoming events - where we're demo'ing/discussing a lot of these principles, we'll plan to post a demo or two on how effective these really can be. And, I know... some of you will fight back with the thought that sparse columns introduce bad database design practices... I know, you want (instead) tables that have name/value pairs - which are the way most of us did this in all versions prior to 2008. However, name/value pairs tables become fragmented messes that also have fragmented indexes and therfore overall poor performance (for both inserts and queries). Not to mention, they can be difficult to query/join with (because you need to join multiple times to retrieve multiple properties) and the code gets messy quickly. Anyway, sparse columns - while they may not seem quite right at first - can really be a *MUCH BETTER* way to design (and perform!!!) around this problem.

And, speaking of events. We have a bunch of upcoming events... I'll give you the short bulleted list here because I'm sure many of you also read Paul's blog and he's mentioned quite a few of these coming up. Also, if you're interested in learning more details for these, the full abstract/links for many of these can be found on our Upcoming Events page. However, specific links are also listed below!

• Best Practices in Performance and Availability for SQL Server 2005/2008, 1-3 September 2008 in Hatfield, England. You can get more details/register here. 
• Dublin SQL Server User Group, Index Internals and Fragmentation, 4 September 2008, Dublin, Ireland. Bob Duffy blogged about this here and you can get more details/register here.
• Microsoft SQL Academy 2008 - Session 1, 5 September 2008, Dublin, Ireland. You can get more details/register here.
• Indexing for Performance in SQL Server 2000/2005/2008, 8-9 September 2008, Edinburgh, Scotland. You can get more details/register here.
• SQL Connections "Power Workshops Series" Microsoft SQL Server 2008 Overview for Database Professionals (Hands-on — Bring Your Own Laptop), 6-8 October 2008, San Francisco, CA, USA. You can get more details/register here.
• Microsoft Tech Ed EMEA ITPro, 3-7 November 2008, Barcelona, Spain. You can get more details/register here.
• SQL Connections Fall Conference, 9-14 November 2008, Las Vegas, USA. You can get more details/register here.
• PASS Community Summit 2008, 17-21 November 2008, Seattle, WA, USA. You can get more details/register here.

1. Don't microwave the butter... set it out for an hour before you're going to make the cookies. It's definitely different/better when the butter is naturally soft.
2. Definitely take the cookies out when they look like they have a few more mins to go...

But, Paul's birthday was in July... and, it was not the only time we've made these cookies... mostly because *everyone* we introduce to these cookies is addicted - yes, the girls, me, Paul, my Mother, Brian Randell (who was forced to take some "togo" after a BBQ here) and countless of our other friends since this is now our "when-we-entertain" dessert ;-). So, since I've had the opportunity to tweak the recipe myself, here's what I do differently...

1. I add less chocolate - usually 1-1.5 cups of chocolate chips. Sometimes I mix half/half semi-sweet and milk chocolate but I always use closer to 1 cup...
2. I add about 1 cup of coconut - right at the end, with the chocolate chips...
3. I make a single 12x18 cookie sheet "uni-cookie" and then I cut it up like brownies. As for the cook time, I've found that this giant cookie takes right about 14 minutes (but, you'll have to experiment with your oven... I usually take the giant cookie out when it's puffed up and the edges are just starting to go lightly brown). Oh, and you don't have to spread this out perfectly to the edges - it will expand and fill in. Just get somewhat close. The nice thing about this form factor is that it's a lot faster than making 36 individual cookies and then you get to choose the size(s) when you cut up the uni-cookie.

Oh, and don't blame me when you make 10 batches of these things and get all of your friends addicted as well. They really are yummy!!

Next up... installing Data Dude CTP16 with SQL 2008 and Visual Studio... what works with what and what's the best install order!!!

Thanks for reading!
Kimberly