An Open Letter to Microsoft

Dear Microsoft,

I love you guys. I really do. I’ve spent most of my professional life working with your products. I love SQL Server so much, I read about and blog about it in my free time. I’ve even started a PASS chapter. I own 3 PC’s, plus an X-Box 360. I’ve owned 2 Windows Mobile devices. I program my websites in ASP.NET instead of PHP. Heck, I’ve even developed a Windows CE application. I think it’s safe to say I’m a big fan of yours.

So I have to ask… what the frilly heck happened with Vista? I mean, seriously.

I’ve tried. I really have. I first tried the 64-bit edition. Epic fail. I couldn’t install anything. What I did install, mostly didn’t worked. After only a couple of days, I gave up. I don’t think the world, or at least the average software vendor, is ready for 64-bitness. So I settled for 32-bit, which at least let me install most of my essential applications.

Now I’m trying to install SQL Server 2008 Developer Edition on my Vista laptop. I’ve installed SQL Server many times, but never before have I had so much trouble! I downloaded the ISO image from MSDN. Vista froze in the middle of the download, so I rebooted and tried again. Success. Now for the tricky part: mounting the ISO image as a virtual drive. I’ve always relied on your Win XP Virtual CD Control Panel in the past. It’s never once failed, until I tried to use it with Vista. Now, I realize I could have burned the image to a DVD, but the truth is, I didn’t feel like it. Call me lazy, but I’ve always liked the convenience of virtual drives. And really, if you’re providing an ISO image and you’re pushing Vista, you really should update your tool.

I then looked at your website, and you pointed me to Daemon Tools. This also did not work for me. You offered me a couple of other choices, but I didn’t feel like paying for what I used to have for free. Call me cheap, if you like. So instead, I had to Google for a solution. That’s right, I Google’d. I’d explain why, but I don’t really have the time to get into the issues with Live Search right now. Anyway, two wrong choices later, and I finally discover Virtual CloneDrive. This, at last, does what I need, and I proceed to install SQL Server.

First, the .NET Framework needs to be installed. No problem. I click “Install” and proceed to catch up on last season’s Heroes. When I next look at my laptop, it’s unresponsive. So, another cold reboot. I try again, and now it installs the latest patches with no problem. At this point, I finally get to the SQL Server installation screen, but it’s late, so I decide to come back tomorrow.

The next day, I boot up the laptop, configure my instance, and start the installation. And then… did you guess it? The computer froze. Again. So I cold reboot, and try again. This next time, the installation goes through smoothly.

I now have SQL Server installed on my Vista machine. So far, SQL Server appears to be running just fine. But I have to ask, did it need to be so painful?

I’ve tried everything I know. I’ve installed the latest patches, and upgraded all of my drivers. But I need to know, Microsoft… is it me? Did I do something wrong? Please let me know so I can fix it. Until I figure it out, I think I’m going to hold off on upgrading my other 2 PC’s to Vista.

Just so you know, I forgive you for wasting my time. And I still love you.

Michelle

Source: http://sqlfool.com/2009/02/an-open-letter-to-microsoft/

What was your first computer and what were some of your favorite games?

Denis Gobo tagged me in his post, “What was your first computer and what were some of your favorite games?” Tom LaRock, aka SQLBatman, also tagged me. Ah, memories…

Packard Bell

Packard Bell

My first computer was a Packard Bell 286 in 1992. Packard Bells would later go on to receive the dubious honor of worst PC ever, but I only have fond memories of that first computer. My father brought it home one day, quite unexpectedly; after setting it up, he handed my brother and me several games, including Ultima Underworld, Aces of the Pacific, and Sid Meier’s Civilization, and told us to figure out how to install them if we wanted to play. We figured it out pretty quickly and were immediately hooked; I’m pretty sure this began my lifelong love of computers and gaming.

Ultima Underworld

Ultima Underworld

Ultima Underworld was my favorite game, hands down. This was my first introduction to the world of Britannia and to Avatar. UU was an RPG game, and apparently the first 3D RPG game ever made. It went on to win a buttload of well-deserved awards.

Civilization

Civilization

Sid Meier’s Civilization was my 2nd favorite game. You basically begin the game back in the dawn of civilization (4,000 BC), and lead a tribe to take over the world, either through conquest or culture. It really cast ancient civilizations in a different (and perhaps not entirely accurate) light, and made history class a lot more interesting for me. My entire family used to take turns at the game, seeing who could do the best.

Aces of the Pacific

Aces of the Pacific

Aces of the Pacific was a WW2 flight simulator game. At the time, I thought the graphics were really impressive. It took me a little while to get a hang of the controls, but I then spent hours bombing bases and attacking the Japanese.

Thanks, Denis, for prompting that little trip down memory lane! :)

Now I’m tagging:

Source: http://sqlfool.com/2009/02/first-computer/

Digging Around in SQL Internals – View Page Data

So lately I’ve been geeking out on SQL internals. My most recent find involves a couple of undocumented toys, DBCC Page and sys.system_internals_allocation_units.

DBCC Page will actually display the contents of a page. I found a blog post by Paul Randal, back when he still worked for Microsoft, describing the DBCC Page command. Here’s a summary:

dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

The filenum and pagenum parameters are taken from the page IDs that come from various system tables and appear in DBCC or other system error messages. A page ID of, say, (1:354) has filenum = 1 and pagenum = 354.

The printopt parameter has the following meanings:

* 0 – print just the page header
* 1 – page header plus per-row hex dumps and a dump of the page slot array (unless its a page that doesn’t have one, like allocation bitmaps)
* 2 – page header plus whole page hex dump
* 3 – page header plus detailed per-row interpretation

The per-row interpretation work for all page types, including allocation bitmaps.

We’ll come back to actually viewing a page in just a minute. Now that we know we can view page contents, how do we find out which pages contain the data we want to look at? This is where sys.system_internals_allocation_units can help. The sys.system_internals_allocation_units DMV is just like sys.allocation_units, except it has a few additional columns of interest: [first_page], [root_page], and [first_iam_page]. The query below will return the filenum and pagenum values, for use in the DBCC Page command, for a specific table.

Select Object_Name(p.object_id) As 'tableName'
    , i.name As 'indexName'
    , p.partition_number
    , au.type_desc
    , Convert (varchar(6),
      Convert (int, SubString (au.first_page, 6, 1) +
         SubString (au.first_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.first_page, 4, 1) +
         SubString (au.first_page, 3, 1) +
         SubString (au.first_page, 2, 1) +
         SubString (au.first_page, 1, 1))) As 'firstPage'
    , Convert (varchar(6),
      Convert (int, SubString (au.root_page, 6, 1) +
         SubString (au.root_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.root_page, 4, 1) +
         SubString (au.root_page, 3, 1) +
         SubString (au.root_page, 2, 1) +
         SubString (au.root_page, 1, 1))) As 'rootPage'
    , Convert (varchar(6),
      Convert (int, SubString (au.first_iam_page, 6, 1) +
         SubString (au.first_iam_page, 5, 1))) +
   ':' + Convert (varchar(20),
      Convert (int, SubString (au.first_iam_page, 4, 1) +
         SubString (au.first_iam_page, 3, 1) +
         SubString (au.first_iam_page, 2, 1) +
         SubString (au.first_iam_page, 1, 1))) As 'firstIAM_page'
From sys.indexes As i
Join sys.partitions As p
    On i.object_id = p.object_id
    And i.index_id = p.index_id
Join sys.system_internals_allocation_units As au
    On p.hobt_id = au.container_id
Where Object_Name(p.object_id) = 'ProductReview'
Order By tableName;

Note: the conversion code was borrowed from one of Kimberley Tripp’s posts, see the References section for the link.

Running the preceding query in the AdventureWorks database will produce the following:

tableName  indexName                           partition_number type_desc    firstPage  rootPage  firstIAM_page
---------- ----------------------------------- ---------------- ------------ ---------- --------- --------------
ProductRev PK_ProductReview_ProductReviewID    1                IN_ROW_DATA  1:770      1:773     1:771
ProductRev IX_ProductReview_ProductID_Name     1                IN_ROW_DATA  1:911      1:914     1:912

Great, now we have a starting place! Let’s now take DBCC Page out for a spin and see what it can do. I’m going to use the [firstPage] value for the IX_ProductReview_ProductID_Name index. Remember, the value preceding the colon (:) is your file number, and the value after it is your page number.

DBCC TraceOn (3604);
DBCC Page (AdventureWorks, 1, 911, 3);
DBCC TraceOff (3604);

You should get back something like the following. (Note: for brevity’s sake, I’m only displaying part of the results)

Allocation Status
 
GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            
FileId PageId      Row    Level  ProductID (key) ReviewerName (key)                                 ProductReviewID (key) Comments
------ ----------- ------ ------ --------------- -------------------------------------------------- --------------------- -------------------------------------------------------------------------
1      911         0      0      709             John Smith                                         1                     I can't believe I'm singing the praises of a pair of socks, but...(79025483b74e)

Let’s take a moment to consider why we’re seeing this. You’ll notice there’s 3 columns with (key) next to the name: [ProductID], [ReviewerName], and [ProductReviewID]. There’s one column without (key): [Comments]. This is exactly what I was expecting to see. Why? If you take a look at the details of IX_ProductReview_ProductID_Name, you’ll notice it’s a non-clustered index on only [ProductID, ReviewerName], with an included column, [Comments]. You’ll also notice the clustered index on Production.ProductReview is [ProductReviewID]. Remember, the composition of a non-clustered index includes the clustered index value as a sort of row pointer.

Not sure how to view the details of an index, like included columns? Check out my Index Interrogation Script.

I was interested in the ordering of the data. I wasn’t sure whether I’d find [ProductReviewID] first or last. But after looking at several of these examples, I’ve always found the clustered index to be nestled behind the non-clustered index columns and before the included columns (as exampled above).

That’s all I have for now. Try it out, play with the different printopt values, look at different types of pages, and have fun. Oh, and if anyone has a link to a blog post or a book that explains each of the items in the page header, I’d greatly appreciate it. :)

Happy Digging!

Michelle

References:

Source: http://sqlfool.com/2009/02/view_page_data/

Estimating Rows per Page

Ever wonder how many rows you store per page? Me too. So here’s the query I use to investigate this:

Select object_name(i.object_id) As 'tableName'
    , i.name As 'indexName'
    , i.type_desc
    , Max(p.partition_number) As 'partitions'
    , Sum(p.rows) As 'rows'
    , Sum(au.data_pages) As 'dataPages'
    , Sum(p.rows) / Sum(au.data_pages) As 'rowsPerPage'
From sys.indexes As i
Join sys.partitions As p
    On i.object_id = p.object_id
    And i.index_id = p.index_id
Join sys.allocation_units As au
    On p.hobt_id = au.container_id
Where object_name(i.object_id) Not Like 'sys%'
    And au.type_desc = 'IN_ROW_DATA'
Group By object_name(i.object_id)
    , i.name
    , i.type_desc
Having Sum(au.data_pages) > 100
Order By rowsPerPage;

What does this tell you? Well, the more rows you can fit on a page, the less IO you need to consume to retrieve those rows. It’s also a good way to improve your buffer cache hit ratio (i.e. retrieve data from memory instead of disk, which is more efficient). So take a good look at those first few rows… do you have a small number of [rowsPerPage] but a large number of [rows]? If so, it may be time to look at re-designing your tables.

Happy Coding!

Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/estimating-rows-per-page/

Things You Know Now…

Mike Walsh tagged me in his blog post, Things You Know Now…, and asked, “It doesn’t have to be DBA skills, but what do you wish you knew when you were starting?” This is a really great question, and I’ve given it some pretty serious consideration. Here’s my top 3:

Just because you don’t know everything doesn’t mean you know nothing.
It’s taken me a few years to figure this one out. I’ve come to the hard realization that, no matter how much I know about something (*coughsqlservercough*), I will never know everything. There will always be some new feature, or some new language, that you just won’t have the time or need to learn. And while there’s value in being a Jack-or-Jill-of-all-trades, there’s probably more value in knowing a few things really well. Pick what you love and dive into it wholeheartedly. Make sure you stay current on technologies, so you don’t get outdated, but do so within your specific area. You’ll love your job and be a more valuable employee for it.

Give back to the community, and don’t be afraid of looking stupid.
Whether you’re afraid to ask a question or provide an answer, don’t be. I’ve found that, as long as you approach it intelligently and politely, you’ll be fine. Eleanor Roosevelt once said, “You gain strength, courage, and confidence by every experience in which you really stop to look fear in the face.” I believe this wholeheartedly. For me, starting a blog wasn’t nearly as scary as the decision to put my real name on it. And responding to questions on the MSDN forum? I was pretty nervous that some MVP or Microsoft employee would ridicule my suggestions. But you know what? It hasn’t happened yet, and I gain more confidence with every blog post, every forum response, and every published article. And if I don’t have the right answer? Then I read up on the correct answer, which is just another opportunity to learn.

Work yourself out of a job.
That old adage, “work smarter, not harder,” definitely has merit. Write good, thorough code. Plan for growth. And automate the hell out of everything (without losing quality). Afraid you’ll lose your job? It’s certainly possible, but not likely. Any boss worth his salt will notice you get more work done in less time than your co-workers. This will lead to better reviews, increased responsibility, and possibly even promotions (side note on this: if you like code, don’t do it! I learned the hard way, meetings are boring. :) ).

Tag! You’re It.

I’m calling these guys out to post their own responses to Mike’s question:

Regards,

Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/things-you-know-now/

View (and Disable) SQL Agent Jobs with TSQL

Recently, I wanted to find a list of all SQL Agent Jobs running on various servers. I was able to view this in SSMS, of course, but I wanted to be able to copy/paste and toss this into a spreadsheet. So instead of using SSMS, I wrote the script below, with significant help from the sysschedules entry in BOL, to show me the same information, using T-SQL. I also include a script to disable the job, because that’s just how I roll.

Declare @weekDay Table (
      mask      int
    , maskValue varchar(32)
);
 
Insert Into @weekDay
Select 1, 'Sunday'  Union All
Select 2, 'Monday'  Union All
Select 4, 'Tuesday'  Union All
Select 8, 'Wednesday'  Union All
Select 16, 'Thursday'  Union All
Select 32, 'Friday'  Union All
Select 64, 'Saturday';
 
With myCTE
As(
    Select sched.name As 'scheduleName'
        , sched.schedule_id
        , jobsched.job_id
        , Case When sched.freq_type = 1 Then 'Once' 
            When sched.freq_type = 4 
                And sched.freq_interval = 1 
                    Then 'Daily'
            When sched.freq_type = 4 
                Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
            When sched.freq_type = 8 Then 
                Replace( Replace( Replace(( 
                    Select maskValue 
                    From @weekDay As x 
                    Where sched.freq_interval & x.mask <> 0 
                    Order By mask For XML Raw)
                , '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') 
                + Case When sched.freq_recurrence_factor <> 0 
                        And sched.freq_recurrence_factor = 1 
                            Then '; weekly' 
                    When sched.freq_recurrence_factor <> 0 Then '; every ' 
                + Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks' End
            When sched.freq_type = 16 Then 'On day ' 
                + Cast(sched.freq_interval As varchar(10)) + ' of every '
                + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' 
            When sched.freq_type = 32 Then 
                Case When sched.freq_relative_interval = 1 Then 'First'
                    When sched.freq_relative_interval = 2 Then 'Second'
                    When sched.freq_relative_interval = 4 Then 'Third'
                    When sched.freq_relative_interval = 8 Then 'Fourth'
                    When sched.freq_relative_interval = 16 Then 'Last'
                End + 
                Case When sched.freq_interval = 1 Then ' Sunday'
                    When sched.freq_interval = 2 Then ' Monday'
                    When sched.freq_interval = 3 Then ' Tuesday'
                    When sched.freq_interval = 4 Then ' Wednesday'
                    When sched.freq_interval = 5 Then ' Thursday'
                    When sched.freq_interval = 6 Then ' Friday'
                    When sched.freq_interval = 7 Then ' Saturday'
                    When sched.freq_interval = 8 Then ' Day'
                    When sched.freq_interval = 9 Then ' Weekday'
                    When sched.freq_interval = 10 Then ' Weekend'
                End
                + Case When sched.freq_recurrence_factor <> 0 
                        And sched.freq_recurrence_factor = 1 Then '; monthly'
                    When sched.freq_recurrence_factor <> 0 Then '; every ' 
                + Cast(sched.freq_recurrence_factor As varchar(10)) + ' months' End
            When sched.freq_type = 64 Then 'StartUp'
            When sched.freq_type = 128 Then 'Idle'
          End As 'frequency'
        , IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) + 
            Case When sched.freq_subday_type = 2 Then ' seconds'
                When sched.freq_subday_type = 4 Then ' minutes'
                When sched.freq_subday_type = 8 Then ' hours'
            End, 'Once') As 'subFrequency'
        , Replicate('0', 6 - Len(sched.active_start_time)) 
            + Cast(sched.active_start_time As varchar(6)) As 'startTime'
        , Replicate('0', 6 - Len(sched.active_end_time)) 
            + Cast(sched.active_end_time As varchar(6)) As 'endTime'
        , Replicate('0', 6 - Len(jobsched.next_run_time)) 
            + Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
        , Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
    From msdb.dbo.sysschedules As sched
    Join msdb.dbo.sysjobschedules As jobsched
        On sched.schedule_id = jobsched.schedule_id
    Where sched.enabled = 1
)
 
Select job.name As 'jobName'
    , sched.scheduleName
    , sched.frequency
    , sched.subFrequency
    , SubString(sched.startTime, 1, 2) + ':' 
        + SubString(sched.startTime, 3, 2) + ' - ' 
        + SubString(sched.endTime, 1, 2) + ':' 
        + SubString(sched.endTime, 3, 2) 
        As 'scheduleTime' -- HH:MM
    , SubString(sched.nextRunDate, 1, 4) + '/' 
        + SubString(sched.nextRunDate, 5, 2) + '/' 
        + SubString(sched.nextRunDate, 7, 2) + ' ' 
        + SubString(sched.nextRunTime, 1, 2) + ':' 
        + SubString(sched.nextRunTime, 3, 2) As 'nextRunDate'
      /* Note: the sysjobschedules table refreshes every 20 min, 
        so nextRunDate may be out of date */
    , 'Execute msdb.dbo.sp_update_job @job_id = ''' 
        + Cast(job.job_id As char(36)) + ''', @enabled = 0;' As 'disableScript'
From msdb.dbo.sysjobs As job
Join myCTE As sched
    On job.job_id = sched.job_id
Where job.enabled = 1 -- do not display disabled jobs
Order By nextRunDate;

Not sure what I’m doing with the @weekDay table? Then check out my post on bitwise operations in T-SQL.

Happy Coding!

Michelle Ufford (aka SQLFool)

PS: I haven’t tested this with every possible setting, just the ones I use. If I missed something, please let me know so I can correct it.

PPS: the sysjobschedules table only refreshes every 20 min, so the nextRunDate value may be a little out of date.

Source: http://sqlfool.com/2009/02/view-sql-agent-jobs-tsql/

T-SQL Bitwise Operations

I’ve seen bit-product columns from time-to-time, mostly in SQL Server 2000 system tables, but it’s never been something I’ve had to work with. And when I’ve needed to, I’ve known how to figure out which options are selected, i.e. a bit product of 9 means options 8 and 1 are selected. If you’ve ever taken a look at the [status] column on the sysdatabases table (SQL 2000), you’ll know what I’m talking about.

What I’ve never known how to do, until recently, was calculate these options programmatically. That’s why, when I noticed the [freq_interval] on the sysschedules table was a bit-product column, I decided to spend a little time figuring it out. Fortunately for me, a couple of my awesome co-workers, Jeff M. and Jason H., have worked with this before and were able to explain it to me. And, it turns out, it’s actually quite easy.

Let me back up a few steps in case you’re not familiar with this topic. If you check out the Books Online entry for the sysschedules table (2005), you’ll notice the following statement:

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

When I looked at the actual value in the table, the schedule has a [freq_interval] value of 42, which is the sum of the bit values for the days selected.

If there were more than 7 options, the bit values would continue to double, i.e. 128, 256, etc. And regardless of how many bit values you select, you’re guaranteed one and only one possible answer, as the sum of all previous bit values will never exceed the next bit value:
1 + 2 = 3
1 + 2 + 4 = 7
1 + 2 + 4 + 8 = 15

Knowing this, I’m able to retrieve the values manually: I start with the highest bit value that does not exceed 42, then subtract it; I repeat until I’m left with 0.

So…
42 – 32 = 10
10 – 8 = 2
2 – 2 = 0

That means my job is scheduled to run on Friday’s (32), Wednesday’s (8), and Monday’s (2).

Now how do I do this with T-SQL? SQL Server provides an operator specifically for this task: the bitwise AND operator (&). For now, I’m going to skip the “why” behind this and just get to the practical application. If you’re interested in the “why,” let me know and I’ll write a follow-up post on binary and logical AND and OR operations.

For example, to use the bitwise AND to find out which days are selected…

Select 42 & 1 As 'Sunday'
    , 42 & 2 As 'Monday'
    , 42 & 4 As 'Tuesday'
    , 42 & 8 As 'Wednesday'
    , 42 & 16 As 'Thursday'
    , 42 & 32 As 'Friday'
    , 42 & 64 As 'Saturday';

… will return …

Sunday      Monday      Tuesday     Wednesday   Thursday    Friday      Saturday
----------- ----------- ----------- ----------- ----------- ----------- -----------
0           2           0           8           0           32          0

If the result is not equal to zero, then that day is selected. Easy as key lime pie, right?

Now let’s take it a step further and create our own working example. Let’s say we’re going to track the characteristics of various objects in a single bit-product column (note: this is not necessarily the best way to accomplish this in the real world, but it’s a good illustration). First, set up a table to use in our example. This table will have a column, [attributes], which will hold the sum of our bit values.

Create Table myTable
(
      id            int identity(1,1)
    , item          varchar(10)
    , attributes    int
);
 
Insert Into myTable
Select 'Broccoli', 200 Union All
Select 'Tomato', 193 Union All
Select 'Car', 276 Union All
Select 'Ball', 292;

Next, we’re going to create a table variable that holds characteristics and their values. We’ll then join these two tables together to see which attributes exist for each item.

Declare @statusLookup Table
(
      attribute int
    , value     varchar(10)
);
 
Insert Into @statusLookup
Select 1, 'Red' Union All
Select 4, 'Blue' Union All
Select 8, 'Green' Union All
Select 16, 'Metal' Union All
 
Select 32, 'Plastic' Union All
Select 64, 'Plant' Union All
Select 128, 'Edible' Union All
Select 256, 'Non-Edible';
 
Select a.item, b.value
From myTable a
Cross Join @statusLookup b
Where a.attributes & b.attribute <> 0
Order By a.item
    , b.value

You should get this result:

item       value
---------- ----------
Ball       Blue
Ball       Non-Edible
Ball       Plastic
Broccoli   Edible
Broccoli   Green
Broccoli   Plant
Car        Blue
Car        Metal
Car        Non-Edible
Tomato     Edible
Tomato     Plant
Tomato     Red

Great, now we know broccoli is edible! Let’s apply a little XML to clean up the results…

Select a.item
    , Replace( Replace( Replace(( 
        Select value 
        From @statusLookup AS b 
        Where a.attributes & b.attribute <> 0 
        Order By b.value For XML Raw)
        , '"/><row value="', ', '), '<row value="', ''), '"/>', '') 
        As 'attributes'
From myTable a
Order By a.item;
item       attributes
----------------------------------------
Ball       Blue, Non-Edible, Plastic
Broccoli   Edible, Green, Plant
Car        Blue, Metal, Non-Edible
Tomato     Edible, Plant, Red

Voila! There you have it, how to use the bitwise AND (&) operator to retrieve multiple values from a bit-product column. Pretty neat stuff!

Special thanks to Jeff M. and Jason H. for their assistance. :)

Happy Coding!

Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/bitwise-operations/

Ramblings on Super Bowl and PASS

Super Bowl 2009

As many of you know, I’m a DBA at GoDaddy.com, which had 2 commercials in this year’s Super Bowl. If you saw the commercials during the game or went to our website for the “internet only” versions, let me know; I have no control over the content of the ads, but I’m still interested in your opinions. But comments on ad content aside, the commercials continue to prove very effective for driving traffic to our website and, in turn, generating income. (Don’t believe me? Read this and this article on finance.yahoo.com).

We typically get some pretty large spikes the minutes immediately following a commercial airing, and this year was no exception! We spent quite a bit of time throughout the year tuning our systems to support Super Bowl traffic, especially in the few weeks preceding the big game. By all accounts, this year’s efforts have paid off; our database servers exceeded expectations. I don’t think I’m allowed to go into specifics, but I can mention some server stats. During the spikes, my primary server reached 27k transactions per second, no timeouts, and very good response times. In fact, I estimate we decreased our recovery time by around 80% compared to last year.

Why do I mention all of this? Well, there’s the bragging aspect, of course :) . But more importantly, I bring it up to give credence to some of the performance tuning articles I’ve written in the past, like:

Keep in mind, there’s rarely a “magic bullet” for performance tuning, and what worked for me may not work for you. If you have any questions, please feel free to leave me a comment or send me an e-mail, and I’ll do my best to respond.

If you’re interested in more information on effective performance tuning, make sure to check out the Performance Tuning Section on SQLServerPedia.com.

I380 PASS

I’ve been pleasantly surprised with the number of inquiries I’ve received regarding the I380 PASS Chapter (serving the East Iowa area of Cedar Rapids and Iowa City), so I’ll continue to post updates to my blog.

As I’ve mentioned before, we’re now officially a PASS Chapter, and we’re currently in the planning stages of our first meeting. We have one confirmed key sponsor, Quest Software (woot!), and we’re speaking with a couple of other possible sponsors. Side note: if you’re interested in sponsoring our group, I’d love to hear from you! E-mail me at michelle @ sqlfool dot com.

We’re currently planning to have meetings on the second Tuesday of every month, with our first meeting on Tuesday, March 10th 2009. We have a confirmed speaker but not a confirmed topic, and we’re actively working on a meeting location. Please keep in mind that all of these details are subject to change. ;)

If you’re in the area and would like to attend, or know someone who should attend, please drop me a line!