Your task, model a database that represents a suburban block.  You survey the area, and see the following houses (pictures culled from Wikipedia here)

and

So you look at the houses, start modeling roofs, windows, lawn, driveway, mail boxes, porches, etc etc. You get done, and with your 30+ tables you are feeling great, right? I know I would be. “I knocked this out of the park! We can capture everything about these houses.  I…am…a…superhero database modeler,” I think, “I will get a big promotion or a raise for sure! Shoot, I will get both!”

So you show this model to the client and they say, “you’re fired. Do you even know what business we are in?” Well, no, I suppose not.  That is the lesson for the day.  Perspective.  Unless you realize the perspective of the client, understand the problems they want to solve, you are missing the point.

One of my “mind exercises” I play when travelling in a car, or at a theme park, or at a doctor’s office, etc is to think about how I could make the place better with database technology. During my family’s excessive trips to Disney World, when I am the slightest bit mentally bored I think about how I could fix things. (RFID in every ticket tracking and predicting next moves, stored in a database for reevaluating how to herd people like the lunch crowd at Tech Ed, for example.)  But driving through a neighborhood the other day, it really hit me.  There are many different ways to look at a block of houses, apartments, flats, etc and model them for usage (in addition to basic locator information like address and perhaps longitude/latitude).

• Fire Department – hydrant to home to occupant ratio
• Sales – Number of windows, roof style, lawn, age of home
• Law Enforcement – Name of occupants, previous law enforcement “contact”
• Sanitation Department – Number of trash cans (including serial number for tracking), average amount of trash

And so on, but then I thought, what about the Sanitation Department. Take a side loading trash truck (http://vehiclestoys.guidestobuy.com/mack-granite-side-loading-garbage-truck):

`

What if they weighed the trash as the loaded it, then kept records of how they weight varied. They could predict how much trash they could pick up and change routes accordingly. How else might this information be used? The information might also be interesting to law enforcement. This single family dwelling makes 80% more trash that all of their neighbors.  Is this suspicious? Does it hurt to check it out? Is this getting too big brother? Maybe, depending on how the information was used, but it certainly might be used as evidence in addition to an actual complaint.

Privacy concerns aside (not that you have any privacy in your trash, right?) It will be centuries before two government organizations work together that smoothly. It can take 2 change of address forms per government agency to move from one house to another.

In the end of this technically rather soft blog, the point is that requirements govern the design. Unless you know why the user wants you to model something, you cannot do a good job of building a database to meet their needs.  Sure you can build a cool database that stores everything that you can possibly ever desire about a subject, but does it meet the needs of the user? The only way to know the needs of the user is to study them, understand them, and put yourself in their shoes. Figure out their job and build from there. Adding a few extra bells and whistles to delight the user isn’t horrible sometimes, but missing the point is.

Or any support people for that matter.  I constantly hear people having to support this, support that, wearing the “beeper”, etc etc.  But these people do seemingly love what they do, because I hear this on “non-essential” communications channels, like Twitter, SQL Saturday conversations, etc.  These are people who are doing what they do because they like it.

I have to be honest with you though, about the second time I was awakened by a beeper with the same problem I would be outraged. In fact I was, a long time ago. Luckily though, I worked for a company where I was the dba, programmer, and support for the database stuff. So when I had to do any support, I learned from the experience and fixed it so I wouldn’t have to do it again.

This topic is basically centered around why I had an extra 45 minute this morning to write a blog post when I actually should still be in bed.  I built an database transform/copy SSIS package that runs at 2:00 am. It is (for the time being) reliant on a database that gets restored by 1:00 am… only last night it was done at 2:15. The steps to drop constraints and truncate the table worked marvelously. The ones to reload… not so much…

This is a new process and is currently in working development state (it is more or less done, but the code that uses this data isn’t actually complete yet.  But still, my phone that delivers my corporate email buzzed before 8:00 am, and I noticed that it was an email from another developer asking “where is my data”. So I hit the computer and realized the bug… thankful it wasn’t something else.

Even this little disturbance to my lying in bed getting ready to get up pattern is enough to make me wonder how dbas do it. Buzz…process failed.  Buzz…process failed.  Email… why is my data not ready yet.  And they aren’t usually the one who created the mess.  Often it is the data programmer, or even just programmers that are good with C#, not so good with that easy to learn easy to work with, no brains necessary T-SQL (I am paraphrasing the thoughts of more than a few programmers).

What is the key to all of this… programmer (data and otherwise) care for the other person. When I was coding and supporting the databases, I learned to be mindful of a very good friend of mine, Future-Me. Future-Me is the recipient of everything I do. What I eat, what I do, and what kind of solutions I produce.  Future me also gets pretty perturb as Current me when he thinks back to all of the stupid things he has said.  Some people don’t even think about the future because they have no plans to stay in their current jobs.  Me, well, I might leave some day, but I plan to stay forever.  And a future of well built, well managed systems that always work sounds wonderful (unlikely in many ways, but wonderful.)

I have always said that every programmer should be forced to do the job of the persons they are writing software for, using the software they created. 40 clicks to do a task during development sounds marginally okay, but if you have to do that task 100 times a day, that is 4000 clicks! Sometimes this is referred to as dogfood testing, like having the people who develop dogfood taste their food (since dogs do like ‘em some table scraps!) Frankly I would rather be an actual dogfood tester than need to click the mouse 4000 times for one of my tasks (plus a few thousand for Solitaire and you are bound to start an epidemic at your company of Carpal Tunnel syndrome.)

I honestly should have considered the fact that the data might be delayed and implement something to protect against this. Either by timing, or by basically making the SSIS package check for the status/existence of the database.  You can bet that I will make sure that happens. And if I was a dba that kept getting errors that others created… well, I don’t want to say I would revolt, but I certainly wouldn’t just grin and bear it.

Hence the reason I talk so much about design. Good design begats good support patterns.  Bad design begats something altogether.

A few weeks back (Feb 27) I spoke at the Rocky Mountain Tech Trifecta (http://rmtechtrifecta.pbworks.com/), where I gave the SQL Track keynote, and then did my Database Design session.  Great time and I had a blast giving a keynote. It was especially fun just doing a lightweight session just encouraging folks to do design.

Last week, I spoke virtually for the Minnesota PASS group, giving the same presentation, plus 10% and including my patent pending Lego audience (The Minifiggers) and audience members that provided me with feedback and answers to my questions that I commonly ask during the presentation. Doing a virtual presentation is interesting, because as exciting as my Minifiggers are, they have plastic faces and don’t let me know when I am confusing them, or when they find my jokes funny.

That was the past.. Over the next 4 weeks, I will be speaking at 2 (and probably 3) SQL Saturday events in Birmingham, Richmond, and hopefully Atlanta. Each time I will be doing my database design session (which has changed 40% since last week), and my database design pattern session (which I haven’t started going over again, but it will likely change from the last time I gave it last year.

The abstracts are:

Database Design Fundamentals

In this session I will give an overview of how to design a database, including the common normal forms and why they should matter to you if you are creating or modifying SQL Server databases. Data should be easy to work with in SQL Server if the database has been organized as close as possible to the standards of normalization that have proven for many years. Many common T-SQL programming "difficulties" are the result of struggling against the way data should be structured and can be avoided by applying the basic normalization techniques and are obvious things that you find yourself struggling with time and again (i.e. using the SUBSTRING function in a WHERE clause meaning you can't use an index efficiently).

Database Design Patterns

Beyond database design fundamentals (for example, Normalization) lies the area where you have to create "real" solutions. In this session, I will cover a good number of patterns that we commonly find useful to try to apply to the problem of building a database solution. Ideas like generalization, subclassing, single table domain tables, optional data, and more will be discussed, some of them good, some not so good (don't assume which will be which), but all that are common and/or useful for your database implementations.

After these three stops, I don’t see myself doing much speaking until Devlink (if accepted, if not I will be attending!) and/or PASS (again, if accepted) as well as at least one other opportunity that has not been scheduled yet (all hush hush and whatnot).

If you need a speaker for your user group that is within 6 hours drive from Nashville, I will be happy to come sometime, or anywhere if you provide me transportation or let me present virtually.

So hope to you see you (not you, I was pointing at.. well, ok you too) at one of these stops for a fun talk about database design. Yes, I said fun. And the first step to good performance is Normalization, not Denormalization. Both true statements.

Haven’t posted anything useful in a while, so I figured I should.  This was something I was building for a coworker the other day, and I find the best way to flesh out a solution is to think about how it will work for the thousands who might see this post, and the hundreds that might actually read it. My next post figures to be an updating of my calendar table queries here on my sqlblog.com blog (since I have pretty much abandoned my spaces blog for now.  Another new feature I am going to start is to include a download of the pertinent source code for posts like this. So the create table and insert statement will be in a link at the end of the post.

One of the posts that I am kind of proud of was my initial post on how to build a date table here. I use that script any time I want to create a date dimension for a warehouse, or for some other application. The last use was a time entry system, where I extended it to include work_week, which instead of being Saturday - Sunday, as a normal week is, is defined as Monday - Sunday (Yeah, that way extra time on the weekend is part of the same week.) But I digress.

Recently, the need came up to do groupings of data on the hour of the day, for call center tracking. When I heard the person who would be doing the coding start to say something about using datepart and such, I said, "I know a better way". If you can calculate the number of minutes past midnight, then I could do the rest with relative ease, much like their current experience with the date dimension.

In the end, the hardest part of using the date dimension is the calculating of seconds past midnight. Not hard so much that it is really difficult, but hard to explain.  Basically you use the old stripping the time off of the datetime trick:

DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>))

This is a neat trick, datediff(day,0 returns a number of days since the base date for the type, either smalldatetime or datetime, and dateadd(day,0 converts the integer to a date value. Quite fast, and you end up with a datetype without having to cast the value (I have to explain this to everyone I introduce it to, so if you already got it, I am sorry).  Then use datediff to see how many seconds have passed since midnight:

DATEDIFF(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)

Simple enough, really, just a bit untidy to type.  In reality, if this is a commonly used value for a table (particularly if you have a datetime value in one of your dimension tables), you can just add a computed column to your dimension (probably essential to use the PERSISTED keyword to ensure it is only calculated once).

alter table <tableName>
                add <columnName> as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, <referencedColumnName>)), <referencedColumnName>)) persisted

Now, the basics are a table that has a key of the number of seconds past midnight, with the types of attributes you might typically calculate, as usual, it is very likely that you will have some more you might want, or less, depending on your organization. Like if everyone had a fixed lunch time, you could add lunch_time, and include the minutes of the lunch time in it.  Then you could compare, say internet bytes/second during lunch versus work hours.

First create your table.  I have called it time_of_day, using my typical naming convention of having reporting tables named with underscores. I have commented on each column as to its meaning.

set nocount on
go
create table time_of_day
(
     time_of_day_key smallint primary key,
     hour_of_day_24 tinyint,                --0-23, military/European time
     hour_of_day_12 tinyint,                --1-12, repeating for AM/PM, for us American types
     am_pm char(2),                         --AM/PM
     minute_of_hour tinyint,                --the minute of the hour, reset at the top of each hour. 0-59
     half_hour tinyint,                     --1 or 2, if it is the first or second half of the hour
     half_hour_of_day tinyint,              --1-24, incremented at the top of each half hour for the entire day
     quarter_hour tinyint,                  --1-4, for each quarter hour
     quarter_hour_of_day tinyint,           --1-48, incremented at the tope of each half hour for the entire day
     string_representation_24 char(5),      --military/European textual representation
     string_representation_12 char(5)       --12 hour clock representation sans AM/PM
)
go

Then we load the table from

--digits gives you a set of 10 numbers 0-9
with digits (i) as(
        select 1 as i union all select 2 as i union all select 3 union all
        select 4 union all select 5 union all select 6 union all select 7
        union all select 8 union all select 9 union all select 0)
--sequence produces a set of integers from 0 - 9999
,sequence (i) as (
        SELECT D1.i + (10*D2.i) + (100*D3.i) + (1000*D4.i)
        FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 CROSS JOIN digits AS D4)
insert into time_of_day(time_of_day_key, hour_of_day_24, hour_of_day_12, am_pm, minute_of_hour, half_hour, half_hour_of_day,
     quarter_hour, quarter_hour_of_day, string_representation_24, string_representation_12)

--calculates the different values for the time table
SELECT i as time_of_day_key
      ,datepart(hh, dateval) as hour_of_day_24
      ,datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as hour_of_day_12
      ,case when datepart(hh, dateval) between 0 and 11 then 'AM' else 'PM' end as am_pm
      ,datepart(mi, dateval) AS minute_of_hour
      ,((i/30) % 2) + 1  AS half_hour --note, I made these next 4 values 1 based, not 0. So the first half hour is 1, the second is 2
      ,(i/30) + 1  AS half_hour_of_day  --and for the whole day value, they go from
      ,((i/15) % 4) + 1     AS quarter_hour
      ,(i/15) + 1  AS quarter_hour_of_day
      ,right('0' + cast(datepart(hh, dateval) as varchar(2)),2)+ ':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_24
      ,right('0' + cast(datepart(hh, dateval) % 12 + case when datepart(hh, dateval) % 12 = 0 then 12 else 0 end as varchar(2)),2)+ 
                                                                  ':' + right('0' + cast(datepart(mi, dateval) as varchar(2)),2) as string_representation_12
FROM (    SELECT dateadd(minute,i,'20000101') AS dateVal, i
        FROM sequence AS sequence
        WHERE i between 0 and 1439 --number of minutes in a day = 1440 
       ) as dailyMinutes
Go

You can see the values by using

SELECT top 5*
FROM   time_of_day

returns:

time_of_day_key hour_of_day_24 hour_of_day_12 am_pm minute_of_hour half_hour half_hour_of_day quarter_hour quarter_hour_of_day string_representation_24 string_representation_12
--------------- -------------- -------------- ----- -------------- --------- ---------------- ------------ ------------------- ------------------------ ------------------------
0               0              12             AM    0              1         1                1            1                   00:00                    12:00
1               0              12             AM    1              1         1                1            1                   00:01                    12:01
2               0              12             AM    2              1         1                1            1                   00:02                    12:02
3               0              12             AM    3              1         1                1            1                   00:03                    12:03
4               0              12             AM    4              1         1                1            1                   00:04                    12:04

To use the values, I will create a test table, in this case to simulate a phone call for a call center:

create table testPhoneCall
(
    TestPhoneCallId int identity primary key,
    callType  varchar(10),
    callTime    datetime --the point in time of the call
)

Obviously a real table would have more columns with more descriptive information, but this will do for our purposes.  Using the following insert, I will load the table. Basically I am trying to set about 70 percent of calls to incoming, and then at some random time of day, within 2000 days of 20010101. The go 1000 will load the table with 1000 rows.

insert into testPhoneCall(callType,callTime)
select case when rand()> .7 then 'Incoming' else 'Outgoing' end ,dateadd(minute, rand() * 1439, dateadd(day,(rand() * 2000),('20010101')))
go 1000

Taking a look at the data

select top 10 *
from   testPhoneCall

You should see something along the lines of:

TestPhoneCallId callType   callTime
--------------- ---------- -----------------------
1               Outgoing   2002-10-23 06:46:00.000
2               Outgoing   2004-03-23 14:27:00.000
3               Outgoing   2001-01-13 11:45:00.000
4               Incoming   2002-04-29 03:41:00.000
5               Outgoing   2005-06-30 01:19:00.000
6               Outgoing   2002-06-30 13:15:00.000
7               Outgoing   2005-11-13 06:03:00.000
8               Outgoing   2003-01-01 04:56:00.000
9               Outgoing   2004-09-04 12:26:00.000
10              Outgoing   2005-09-28 08:47:00.000

To use this data with our time_of_day table, we need to get the number of seconds past mighnight the data is.  Using the trick to get the
date without time: DATEADD(DAY, 0, DATEDIFF(DAY, 0, <datevalue>)), I datediff by minutes the timeless date, and the date value with time. You can
do this in a query, or a view, or in my case, I am choosing to add this to the table as a computed column (which is what I am doing with
my data warehouse table that sparked this need:

alter table testPhoneCall
                add callTimeOfDay as (datediff(mi,DATEADD(DAY, 0, DATEDIFF(DAY, 0, callTime)), callTime)) persisted
                                                                            --persisted means it calculates at save time, not run time

Now, we can join to the time_of_day table with our new callTimeOfDay columm, and find out how many calls were in the AM, and which were in the PM

select time_of_day.am_pm, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by time_of_day.am_pm

And since we used a random number, it should be about even, and it seems to be:

am_pm numCalls
----- -----------
AM    494
PM    506

And you can mix in data from your table, like the call type, and then see what percentage of calls were in the which half of the hour
and if they were incoming or outgoing calls:

select testPhoneCall.callType, time_of_day.half_hour, COUNT(*) as numCalls
from   testPhoneCall       
         join time_of_day
                on time_of_day.time_of_day_key = testPhoneCall.callTimeOfDay
group by testPhoneCall.callType, time_of_day.half_hour

This returns:

callType   half_hour numCalls
---------- --------- -----------
Incoming   1         157
Outgoing   1         352
Incoming   2         155
Outgoing   2         336

It is a simple as that. Keep in mind that using a table to do time calculations will not always be faster. Using the functions can be faster, though
whenever you are doing groupings/joins a time/date table will often be faster but will always be more convienient.

To download the code for creating and loading the time_of_day table, follow this link: timeTableCreateAndLoad.sql

Darn it, when I get my reviews back from a conference, I want them to say “Perfect”, “Wonderful”,”The best conference I have ever gone to, bar none”, and no matter how many times I offer to fill in the evaluation forms from people, well, let’s just say that there are always a few people who don’t say these things.  I saw Grant Fritchey’s post, and started mine, then saw Marco Russo and Andy Leonard’s while mine stewed overnight… So I am clearly not the pioneer here.

In reality, this was probably my best set of evals ever. One year, I was the fifth lowest in the list. Fifth…lowest. I was very much on the verge of quitting at the time.  But frankly my friend Wayne Snyder gave me the best advice ever. Who cares what they think, just speak about what you care about. (At least I think it was Wayne, we have had a few memorable conversations over time, and I think we were standing in the front of a bus at the time, but I digress).  As a person of (ahem) alternate shape, I have a lot of self consciousness pent up left over from my high school years, and to be honest, I often get that fear of people laughing at me going around and around in my head. In the end I realized that I wanted to try to end the poor database craptacular explosion that (from the examples I have seen) has been spreading throughout the world. Imagining that the audience is all dressed in bright blue bunny eared parkas doesn’t hurt either.

In the end I know I write better than I speak, and one comment was “'Work on the delivery; otherwise, good content.”.  I am just glad this person wasn’t leaving comments five years ago :)

The session was database design, and on a five scale, the ratings this time were (giving a 1 to “very poor” and a 5 to “excellent”:

Not shabby, and I am certainly happy that the highest score was for knowledge :). A few “poor” ratings, and one very poor (a 1) for the usefulness..Fair enough. Lots of excellent. I am never quite sure what people are expecting from a topic of Database Design, and I generally talk at a high level, briefly cover normalization, give some examples, and “bam!” an hour is gone. I tried to do more in the past, but we always got hung up and never made enough progress. At SQL Saturday events (which I have done two, and am doing the Richmond event Jan 30) I add a patterns session where I talk about the patterns I like to use to solve common problems. Still high level stuff, admittedly.

I have commonly used a lot of jokes/lists to keep the mood light and keep people from leaving the room after they realize they had gone to sleep and were drooling on their conference materials. (That is really not cool!) And to be honest, the old adage of making be laugh as a deflection is quite a useful tool.  But, a couple of the comments were: "'Funny but a lot of stop and go." and "I would get rid of our 'lists.'" So they are gone! Okay, not gone, but I have been thinking of de-emphasizing them for a while. I am possibly doing a keynote later this year and I want to replace some of the lists with a more sermon-esque half-rant about the value of database design. I certainly am not going to lose the humor, but lowering the content will be good.

In any case, thank you to everyone who fills out your evals at conferences in a reasonable manner and leaves useful comments. We do read them, and we listen when they are not shouting/whining/insane. Like the person who commented “I hate to give all 5's, but Louis deserves them.”, that is the kind of constructive criticism a person likes to hear.

In any case, hope to see you in November, or at least sometime soon at an event.

Ever since the early days of laptops, I have looked for the “perfect” device. Something with excellent battery life, very portable, and useful for writing. I like to write, you might have noticed. I also like gadgets. The goal being that whenever I get an idea, I can get it down quick.

My first try was to acquire some form of PDA device. I have tried almost ever version of Pocket PC, including the one the looked a lot  like a PC, though much much smaller (It was made by Phillips, I think it was called a Handheld PC).  None of these has ever exactly hit the mark, though they all have had good points. My current Samsung i760 phone does pretty well, but not great for writing long stuff though it does a very good job at quick note-taking/short writing device because it is with me 99% of the time. I can whip out a few paragraphs, and on rare occasion a full blog post, but no matter how good of a thumb typist I might be, it is generally overly tedious to do more than a page or so on the device. Include the fact that the form factor makes a little text seem much larger than it actually is, and it just isn’t even vaguely a good enough writing device. (I am hoping the next version of Windows Mobile 6.5 or 7 will give me more satisfaction, but who knows.)

On the laptop side, I have always used one as my work computer. The problem there has always been weight to power to battery life ratios, I have a DELL M4400 with a 3 cell internal and a 12 cell slice battery, and I still only get about 4 hours, tops.  All that battery makes it heavy. Really heavy, like Van Gogh with a side of Escher mixed with a bit of Dal.. oh wait, that is a different kind of heavy. I mean it weighs like 9+ pounds. The Dell is a very solid workhorse, dual core, 4GB, 1920X1080 video, but it is just a big old thing to lug around (and no, quad core was just too much when I got it a year ago). The only real feature that makes it a great mobile device is backlit keys. This is the feature I truly love, (and the awesome resolution) but it is just too much trouble to lug this thing around just to write a blog post…It is great when I travel for business, as the 1920X1080 video rivals even decent workstation video, and at home I have a writing tablet that it is attached to that make it a great workstation (plus I use it to remote control a quad core 8GB workstation a lot of the time.)

So recently I resolved to get a machine that is small, powerful enough to run Word, Live Writer and an edition of SQL Server that I could use all day long. So I started looking towards the mini / netbook class machines. When I started this post, I had just purchased a HP MINI, 110-1125NR netbook as the answer. I was pretty impressed with it, but I struggled a bit with the resolution at 1024 x 600. Even the keyboard was quite nice and I got used to it in a few hours (I never thanked my dad nearly enough for making me take typing!) I installed Office 2010 pre-release, SQL Server 2008 Express Edition, Windows Live tools, and it all ran pretty nicely.  I carried it around PASS for the most part, though I didn’t have everything completely set up on the new mini so I had to lug the monster around.  I did do most of my Powerpoint editing on the device which worked pretty nicely.  And this was with 1 GB and Windows 7 Starter edition, which had one limitation that irked me… the inability to change your desktop theme. That is going to annoy many netbook users, as their entire goal is a status symbol thing. I do like having vacation location pictures on my desktop, but I would have lived with that…

When I got home, the wife coveted the new device, and as I was keen to up the resolution, I gave it to her and I got to search again. My theory is that way more than 50% of the delight in any task is the anticipation, and so it was fun.  I narrowed it down to a 11.6’ Gateway machine with an Athlon processor, the same HP device, just upgraded to the HD video, and a Toshiba Satellite T115-S1105 (The Dell machines seemed pretty nice too, but still had to order it custom to get what I wanted.) Battery life on the Gateway was just 5 hours, and I wanted more battery.  The HP had to be ordered, and to get it right made it more like 450 than the original 350, then tax and shipping made it too expensive. The Toshiba won out as it had ridiculous 9 hours + battery, DDR3 memory, 2 GB of ram (with an open slot available for another 2 GB), N wireless, and a 250 GB hard disk, 1366x768 video, HDMI output (excellent for hotels with LCD TVs) and Win 7 Home Premium instead of Starter. All for 479 from Amazon.

The keyboard is very nice, and I didn’t have to get used to it much at all. It has a nice trackpad, a Pentium class processor (single core, low power… A Celeron Edition is available for ~30 less too), and runs quite nicely.  I don’t have any really large databases on the machine, as I mostly test syntax and stuff for forum posts and blogs when I use SQL Server on the machine. The only con is size. The step up in video size makes the machine seem almost laptop sizes rather than a mini PC size. It only weighs 3.5 pounds with a 6 cell battery, and they make a 12 cell…18 hours between charges? Might have to get into that. I am even getting 6 hours with the addition of my USB device from Verizon. The lack of a DVD drive is an expectation of something with 9 hours of battery on 6 cells, and frankly with the excellent MagicCD tool and the fact that everything I need I generally get from the web either in Isos (from MSDN) or installers from the web.

Since a device like this will often need to entertain you as well as let me write (sometimes simultaneously), I have tested a lot of the video sites. It does streaming video great from the Move family of players (ESPN360, ABC), as well as Silverlight (Netflix) in excellent HD quality. Hulu video is decent, but not at the highest quality (low quality is smooth, and looks good enough on the 11.6 screen). With VideoLAN and Zune both, video looks great.

Like all computers, it of course comes with a bunch of weird bloatware apps that almost immediately get removed, but also a few useful ones.. The coolest that is worth sharing is the hard disk protection service. It includes a 3D viewer that lets you watch the heads of the drive move, and shake the PC a bit and the heads/platters move to a safe position:

For any of you who attended the Quest breakfast at PASS (and anyone else really) I wanted to go ahead and post the code and give a little wrap up/explanation. (I will post the stuff on perf counters later. They are cool, but this is really what I wanted to make sure people saw.)

I had a plan for my presentation, but time was less than I expected, and the good stuff kinda fell off. So here is the stuff around the Disk IO DMV section that I really messed up and didn’t show enough. I read a few of the comments that my presentation wasn’t that useful to you, and that is clearly on me.  If the index ones are useful, these are too, and can be used to help spread load around your disks.

So, here is the slide I presented:

By knowing the amount of data written to a database file, you can see how busy the file is, the filegroup is, the database, the disk drive, etc, by aggregating the data in the following query in various way.  The query is pretty simple and will return all files in all databases (there are parms on the function to change that, but check BOL for more info):

--since reboot
SELECT db_name(mf.database_id) as databaseName,
            mf.physical_name, divfs.num_of_reads,
            divfs.num_of_bytes_read, divfs.io_stall_read_ms, 
            divfs.num_of_writes, divfs.num_of_bytes_written,
            divfs.io_stall_write_ms,
            divfs.io_stall,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

The “stall” columns give you how long any processes have waited on your disk drives to serve up data for this file.  This information plus the information in _bytes_written and _bytes_read columns can give you how much data was moved, and how long your CPU had to wait for the data before it could get working on it.  Very powerful stuff, and can really give you a look at opportunities for performance tuning.

That query gives you data from the last reboot of the server, but usually you want to see up to date values. So you have to get a baseline of values, then compare them at a later point.  You can build tools that do this, and you can use the performance data warehouse in 2008, and I will demonstrate the former.  Using a temp table and a WAITFOR DELAY command, I build a quick tool to see waits and stats over 20 seconds. Note that I use a #temp table to allow you to run the last query over and over to see the comparison to the baseline until you want to drop the #temp table.

--over the following delay
declare @delay varchar(10) = '00:00:20'

SELECT db_name(mf.database_id) as databaseName,
       mf.physical_name, divfs.num_of_reads,
       divfs.num_of_bytes_read, divfs.io_stall_read_ms,
       divfs.num_of_writes, divfs.num_of_bytes_written,
       divfs.io_stall_write_ms,
       divfs.io_stall,size_on_disk_bytes,
       getdate() as baselineDate
INTO #baseline
FROM sys.dm_io_virtual_file_stats(null,null) as divfs
            JOIN sys.master_files as mf
                ON mf.database_id = divfs.database_id
                     and mf.file_id = divfs.file_id

WAITFOR DELAY @delay

;WITH currentLine as (  
    SELECT  db_name(mf.database_id) as databaseName,  
            mf.physical_name,num_of_reads, num_of_bytes_read,
            io_stall_read_ms, num_of_writes,
            num_of_bytes_written, io_stall_write_ms,
            io_stall,size_on_disk_bytes,
                getdate() as currentlineDate
     FROM sys.dm_io_virtual_file_stats(null,null) as divfs
                JOIN sys.master_files as mf
                        ON mf.database_id = divfs.database_id
                            and mf.file_id = divfs.file_id)

SELECT  DATEDIFF(s,#baseline.baselineDate,currentlineDate),
        currentLine.databaseName,
      left(currentLine.physical_name,1) as drive,
      currentLine.physical_name
     ,currentLine.io_stall - #baseline.io_stall as io_stall_ms
     ,currentLine.io_stall_read_ms -
               #baseline.io_stall_read_ms as io_stall_read_ms
     ,currentLine.io_stall_write_ms -
              #baseline.io_stall_write_ms as io_stall_write_ms
     ,currentLine.num_of_reads - #baseline.num_of_reads as
                                               num_of_reads

    ,currentLine.num_of_writes - #baseline.num_of_writes
                                            as num_of_writes
   ,currentLine.num_of_bytes_written -
        #baseline.num_of_bytes_written as num_of_bytes_written
FROM currentLine
      join #baseline
          on #baseLine.databaseName = currentLine.databaseName
             and #baseLine.physical_name =
                              currentLine.physical_name
go
--drop table  #baseline

The most interesting part of this query is often the stall columns.  When I see really bad IO, the number of seconds waited can end up being way more than the actual number of seconds. Disk IO is more or less serial (particularly on my mini HP with Win7 Starter Edition), so if you have many queries running concurrently, every one of them needs data from the same IO source, and if one query used the disk for 1 second, the other processes might wait for 1 second each. When wait stats are really low, that means the disk were ready, willing, and able to serve all processes.

On your test server, you can use a query like:

--create database DMV
--go
use DMV
go
create table testDMV
(
    testDMVid int identity,
    bigun     char(8000)
)
go
set nocount on
insert into testDMV(bigun)
select REPLICATE('a',8000)
go 5000

--drop table testDmv

select *
from   testDMV

To create some data.  Note that data will be written to the disk in various counts.  I set all of the rows to take a full data page, and each of the rows is an individual transaction.  But it won’t be a 1-1 match with the number of rows written or read. SQL Server flushes data to the disk in the manner it wants to, and some manner of caching will change the number over time.  Run the select * from testDMV query over and over and you will not see any change in the disk IO, because it is in cache now.

I hope this is better. Sometimes as a presenter I am a good writer, and frankly the lack of bacon really threw me off this morning. Feel free to email me at louis@drsql.org or tweet me at drsql and I will be happy to share more.  All of this will be in our RedGate book that will be coming out before the end of the millennium, at the very minimum.