Way to go!

The slate of candidates for the Professional Association for SQL Server’s Board of Directors will be announced the week of September 29, 2009.  I am not at liberty to say who is currently being considered, but want to make sure that when the call goes out to vote next week that you please do.  Unlike prior years, the election will not take place at the annual PASS Summit, but rather beforehand.  This will afford the new Board members and current Board members the ability to meet and get a jump-start on the agenda for the coming year.  It will also free those who are candidates from having to devote time to the election process while at the Summit when that time could be better served elsewhere.

There are many challenges facing the PASS Board of Directors in an economy such as what we are contending with now.  I would like to commend those who submitted applications for their candidacy and for being willing to sacrifice their own time in order to serve us in the capacities they hope to be elected into.

Kudos!

On the heels of The Professional Association For SQL Server (PASS)’s 24 Hours Of PASS comes Quest Connect 2009.  This free event takes place October 21 for 24 hours, comprised of approximately 60+ different sessions by many SQL Server Professionals you know and love (yes, including your’s truly.)

It’s another opportunity to learn something new from some of the SQL Rockstars in the community (and even a @SQLRockstar.)  Heck, for some reason they even included me in the mix this year. Take a look at the website for the complete list of presenters and sessions.  The session are a mix of live and pre-recorded, but each will be followed by an opportunity for live Q & A.  My session on SQL Administration for Server Admins has been recorded with Brent Ozar and Tom LaRock.  We will be available live after the presentation to take your questions. 

I look forward to hearing from you on October 21, 2009!

 I’ve taken it as a goal to have my laptop all pimped out in time for my first in-person presentation at SQL Richmond on 10/8. Since I ran across my old Star Wars trading cards in my attic last month I thought I’d see if there were any of those old stickers in the cache as well.  Lo and behold there were and now they’ve made an appearance on my big 17″ (laptop that is.)

Still some landscape available.

 Yes, in the lower right hand corner, hovering around the seedy part of my laptop is that sly space pirate himself courtesy of the vintage sheet of 1980 Star Wars Trading Cards I found in my childhood home’s attic a month or so back. 

I know how it can be:  you’re in a strange town, you have no idea of what lies around you in terms of dining choices, bars, attractions.  You don’t know what areas of the city are safe to walk at night.  (More about how to get around Seattle in the wee hours when we disucss transit options in a future post.)  You find yourself relying on chain restaurants and room service to fuel you through the long days of training.  Perhaps you venture down to the hotel bar.  In this case I recommend that at the either the start of the evening, after the laptops, backpacks, and swag has been safely tossed onto that extra queen bed in your hotel room or at the end of the evening in getting prepared to get some rest and face the next 8-10 hour day in sessions, pre-cons, vendor exhibition hall, or networking.  You’ll find plenty of other attendees, speakers, and PASS volunteers all staging their plans for the evening or unwinding after a night out in the Sheraton lobby bar. 

What I plan to do here and in future posts is provide you and your cohorts at the Summit with options you may not know about so you don’t find yourself eating at The Cheesecake Factory across from the convention center on a nightly basis.  Wisconsin doesn’t need that much cheese and neither do you.  I know Wisconsin, I live next to Wisconsin.  You, dear reader, are no Wisconsin.

This post will focus on dining options.  I’ll look at nightlife, attractions, and getting around town in future posts.  In no order I give you my top feature dining options.  All opinions expressed are mine, based upon my experiences.  Past performance is no indicator of …..blah……blah…..

Matt’s In The Market

Recently expanded, this high-end restaurant will be for those with either a high (or no) per-diem, but it is well worth the trip or at least eating the continental breakfast in the hotel and the lunch provided by PASS at the conference center.  The menu is narrow, at last visit there is a handful of entrees offered nightly; it has expanded a bit since then.  The wine options are broad and the culinary skill of the kitchen crew match the range of the wine list.  I’ve yet to eat anything at Matt’s that I did not enjoy and I’ve been exposed to dishes I’ve never thought I would try.  Frankly, they have an advantage over their competition being located at the Pike Place Market and having access to the freshest seafood, vegetables, and fruits you’ll ever stuff down your gullet.  The visual experience at Matt’s is pleasant as well.  The windows at Matt’s (located at the Pike Place Market) look over the market entrance and Elliot Bay.  A warning though, it may be difficult to get in.  I suggest making reservations a week in advance.  LINKS:  Matt’s In the Market on Bing  Matt’s In the Market Website

Dick’s Drive In

Okay, I’m inducing culinary whiplash with positioning Matt’s and Dick’s together, but sometimes you just want a good, greasy cheeseburger with the best fast food fries and real ice cream shake you’ll have just about anywhere.  Real fries.  Hand-dipped shakes.  You don’t survive 55 years in the fast food business without (A) being one of the big chains or (B) providing quality comfort food, fast and at all hours.  Dicks has 5 different locations and if you have a car or better yet, use public transport (more on that in a future post) Dick’s should be a destination.  I personally recommend their 45th Ave location in Wallingford.  It’s a decent bus ride on (if I remember) the #26 from downtown.  Grab a bag of grub and hop the next bus back downtown or better yet head into Fremont a decent walk (or really short 5 minute bus ride) from Dick’s for our next stop.  Dick’s Drive-In on Bing Dick’s Drive-In Website 

Kwanjai Thai

The best Thai food I’ve ever had.  Simply put.  Served out of a house.  Order anything on the menu and you’ll walk away full and happy.  Kwanjai Thai on Yelp

Uwajimaya

Want cheap, quick Asian food?  Want a great assortment of options all in one location?  Want to be able to see live squid, abby-normal animal brains in jars (available for purchase I may add) and watch people buy groceries?  Uwajimaya grocery store is your best bet.  The place is huge.  Think your local mall’s food court only with real (edible) food and some odd combination of Trader Joes-meets-Frankenstein’s lab.  You can even pick up herring roe to nosh on back at the hotel later in the evening.  Korean, Japanese, Hawaiian, Chinese.  All pacific rim bases are covered.  Uwajimaya is located in the International District a nice walk or quick bus ride from the conference center and Sheraton hotel.  Uwajimaya’s website

Le Panier French Bakery

Don’t want that continental breakfast at the hotel or at the conference center?  A short 10 minute walk down to the Pike Street Market puts you at a great French bakery.  Grab some pastry (or pastries) and very good coffee (forgo the neighboring original Starbuck’s IMHO) and have a seat to people watch.  A good, but fattening start to a long day in conference hall chairs.  Le Panier on Bing  Le Panier’s website

Local Color Coffeehouse

 Speaking of Coffee… my favorite resides just down the sidewalk from Le Panier.  I try to get down to Local Color at least once or twice on my visits to the Emerald City.  Grabbing a coffee, sitting out by the sidewalk and taking aim with my camera in between sessions is something I look forward to each time I’m in town.  Local Color serves Caffe Vita coffee (short of Water Street Coffee Joint in Kalamazoo I’ve yet to taste better beans).  You never know what you may see when you sit down to a table or couch at Local Color.  One thing you’re assured of though is an amazing cup of bean water.  Local Color’s website

 Coastal Kitchen

Coastal Kitchen is one of those all-around solid (and hard to define) restaurants.  Located on Capitol Hill (again, a short bus ride or share a cab or rental car) this “fish-forward” restaurant also has an abundance of comfort food.  Their chicken and mashed potatoes dinner is something I remember from one of my first trips to Seattle over a decade-and-a-half ago.  Coastal Kitchen on Bing  Coastal Kitchen’s website

Ivar’s

Ivar’s is to seafood (fish and chips primarily) what Dick’s is to cheeseburgers and fries:  The Seattle Standard.  Ivar’s offers multiple locations, but the closest, within walking distance to the convention center resides bayside by the Seattle Aquarium, just west of The Pike Street Market and just East of fishing crabs out of your wet pants.  I recommend the halibut and chips, but salmon or cod are also available.  Don’t forget the chowder too.  71 years in business means you know what you’re doing and it shows.  The menu is far broader and higher-caliber than Dick’s so don’t make the assumption you’ll only get fish-n-chips.  See for yourself on Ivar’s website.

Be sure to also check out:

Wild Ginger Asian Restaurant and Satay Bar (great asian cuisine and an interesting ginger martini)

Tulio Ristorante (Italian Italian Italian)

Dahlia Lounge (seafood, and yes, made-to-order doughnuts for dessert)

Andaluca (for the paella, breakfast, or tapas)

This list only nicks the surface.  Seattle is a Foodie paradise.  I’ll be back soon to give you my wish list of places I’ve wanted to dine at but yet have had an opportunity to hit.  I have a feeling I’ll be able to leverage Brent Ozar’s gastronomical weak spot and convince him to accompany me to some of these destinations while there.  (I can only hope.)  I’d also be curious to hear what your recommendations are.  Please comment on those here and perhaps we’ll meet up in November!

I’ve run across an interesting (and by interesting I mean annoying) issue as of late with persisting data from the sys.dm_db_index_operational_stats.  This DMF (Dynamic Management Function) returns information concerning how existing indexes are being utilized by the SQL Server 2005 and newer instance.  It contains granular I/O locking and latching metrics for each database, object, index, and partition thereof on the instance.  The combination of these columns should be unique.  What I’ve discovered however is that sometimes, in some manners, this depends.

I persist this data in a physical table four times throughout the day in order to ensure that I have a decent pool of metadata for analysis of index usage whenever the cache storing this information is cleared, as is the case whenever the SQL Server services are restarted.  I do so through a stored procedure called via a scheduled SQL Server Agent job running on the instance.  I’ve highlighted this process on MSSQLTips.com here.

Just recently the job began to fail:

Msg 2627, Sev 14, State 1, Line 193 : Violation of PRIMARY KEY constraint 'PK_dm_db_index_operational_stats'. Cannot insert duplicate key in object 'MetaBot.dm_db_index_operational_stats'. [SQLSTATE 23000]

I created a clustered index on the combined fields of database_id, object_id, index_id, and partition_number for the physical table, as these values together should be unique IN ALL CIRCUMSTANCES.  However, as you’ll soon see that is not always the case.

Take for example the situation I encounter when I run the following query against sys.dm_db_index_operational_stats() using parameters to drill into the specific database, object, index, and partition_number (respectively) for which I am encountering this issue:

SELECT * FROM sys.dm_db_index_operational_stats(99,13, 1, 1)

Now take a look at the result set when I run the following query:

SELECT * 
FROM sys.[dm_db_index_operational_stats](NULL, NULL, NULL, NULL) 
ORDER BY database_id, OBJECT_ID, index_id, partition_number

I have been able to replicate this issue with two different index partitions as you can see above.  I’ve included some of the additional columns in my image so you can see that in addition to returning two rows for partition number 1 of index 1 on both object_id 13 and 15, the metrics for these two index partitions also differ.

I can also replicate the issue when filtering the results of the function to all rows in the specific database:

SELECT * 
FROM sys.[dm_db_index_operational_stats](99, NULL, NULL, NULL) 
ORDER BY database_id, OBJECT_ID, index_id, partition_number 

It’s not until I specify the object_id that the results appear to aggregate or disregard the mutliple row results.

SELECT * 
FROM sys.[dm_db_index_operational_stats](99, 13, NULL, NULL) 
ORDER BY database_id, OBJECT_ID, index_id, partition_number 

This is also occuring with one additional database on the instance, and is occuring for both object_id 13 and 15 as well.  These databases do share similarities.  They were both recently added to the instance - migrated from a SQL 2000 instance and upgraded for the SQL 2005 instance, but still running in 8.0 compatibility mode.  These objects, it should also be noted, are system objects:  object_id 13 is the syshobtcolumns table and object_15 is the syshobts table.  Both these objects are hidden from the end user.  Both store information, as the names suggest, concerning hobts (Heaps Or B-Tree) objects in your SQL instance.  For further clarification on the nature of hobts (pronounced hobbits) I leave that to Aaron Alton (aka Arron the Hobt, as well as Kalen Delaney and Paul Randal (neither of which are from Middle Earth, though Paul has the accent down pat.) 

Kalen Delaney, SQL Server Magazine, Managing Data Space (June 2006)

Paul Randal, SQL Server Storage Engine Blog, Under the Covers: I AM Chains and Allocation Units in SQL Server 2005 (unfortunately it appears this link may no longer be available, but Michelle Ufford aka @SQLFool on Twitter, makes mention of the contents of that post on SQLServerPedia.)

Since I have no desire to attempt to performance tune either of these system tables I will need to either do a check to exclude these tables from the collection process I perform or I will need to remove the primary key constraint from my persisting table and allow duplicate records.  I am not happy with either option as it adds another layer of complexity into my solution and it also allows for mis-represented data in the results set.  I can not truly call this a bug with collection of index operational data via the DMF when the source db is running in 8.0 compatibility, as I have other databases in the same compatibility mode on this instance and have not encountered the situation.  The two databases in question both were restored from the same SQL 2000 instance and they serve data to the same turnkey application so structural/logical structures could be the same and therefore result in both databases presenting this to my tool.

What I am more concerned about though is that the sys.dm_db_index_operational_stats DMF is returning different results for the same object depending upon whether you filter the parameters.  Considering the underlying rows should be unique, this should never be an issue.

I have an interesting meeting tomorrow with one of the Server Jockeys in our organization. 

Our Server Administration Team, like many others, has an assortment of databases they wish to aggregate and query against in the fashion of a typical dashboard.  There is no issue with doing so per-se; I encourage my colleagues to better-acquaint themselves with the data, meta-data, and mega-meta-mondo-data that they rely on constantly, but usually in a passive (or even dismissive) state.  The issue I have comes with their tool of choice in developing this functionality: Microsoft Access.

I really would like to orchestrate a move towards SQL Server Reporting Services.  To my knowledge there is no ancillary (lookup table) data that will need to be created and stored into MS Access tables - the data is complete across the five databases they are planning on querying against.  

Functional, surely, but if SQL Server is hauling this around it is going to slow down a bit.

MS Access is like that package of chicken ramen noodles that has taken up residence in your desk drawer.  It is there when all better options are exhausted.  Not that there is anything wrong with ramen noodles, it’s the food that fuels the developing minds of our future scientists, economists, engineers, exotic dancers (every one you meet is working her way through college it seems), and all other university students at one point or another.  It indeed satisfies the immediate hunger or qualms the hangover, mission accomplished style.  Ramen as your core dietary source of nutrition?  Not recommended.  The same goes for MS Access.  It’s fine here and there when you perhaps need a quick fix when all else fails or when the means to better accomplish the task at hand is unavailable, but as the long-term solution to dashboarding against Tier-1 applications for an enterprise of 15,000 users?  No thank you.  I’ve seen this in play before.  Someone begins using MS Access for a simple solution to a small issue they’re confronting.  One, two, or even five years later they’ve cobbled together something functional, but monstrous at the same time. 

 How have others overcome this dilema and introduced SQL Server Reporting Services to an audience that has not been exposed to it and don’t have technical knowlege to quickly embrace its functionality?  Be so kind to please share your experiences here.  Not just for me, but for others to learn from as well.

It’s not often that I take an extended period of time off.  But seeing almost 300 hours of PTO banked on my last paycheck was a wake-up call to get away and detoxify myself from my professional responsibilities.  We can not uphold our commitments to our customers without first caring for our personal fulfillment.  Families, friends, mental and physical exercise, those quiet spaces in between everything else; those are all critical components of keeping us sane enough to cope with the mental (and sometimes physical) exertion we in Information Technology must expend in order to meet the requirements of our positions.  I realize now that I forgot about that though.  Work became life.  This is a risk of telecommuting.  I’ve often found myself extolling the benefits of remote working and stressing that there are also risks inherent in doing so - this being one. 

I learned a few things on my week away.  None of them having to do with SQL Server, databases, technology, or anything else I normally focus my attention on.  I forgot there is an entire other world out there:

• I’ve learned that if you want fast food in South Dakota you better get it within the city limits of Rapid City or Sioux Falls.
• I’ve learned that a civilized people will build a monument to anything from corn to mythical jackrabbit/antelope hybrids.
• I’ve learned that bison and tofu are both alternatives to beef.  However, tofu has never been described as “gamey”.
• I’ve learned that you never, ever, drive through Chicago unless you absolutely have to.  Oprah has one helluva entourage and they all have cars.
• I’ve learned that there is a store in Wall, SD that will sell anything - an there is at least one purple stretch pant clad grandma who is there to buy it.
• I’ve learned that Hollywood depictions of Minnesota and Wisconsin are way off the mark; those states are not that glamorous.
• I’ve learned that every exit along Interstate 90 in Wisconsin will provide you with a cheese shop.
• I’ve learned that all those newspapers no one reads are being used as toilet paper in hotels servicing the states of South Dakota, and Iowa.
• I’ve learned that despite all those things you will have fun by simply experiencing the unfamiliar with those you love.

So, as the summer rapidly winds down I wanted to take a moment to remind you that there is still time to take a few days off; step away from the keyboard, back off the mouse, flee from the flourescents and go outdoors.  Just be sure to carry your own toilet paper and a well-stocked cooler.

Your family and your customers will thank you.

I will be speaking on the topic of my Top 10 Dynamic Management Views (DMVs) and Functions (DMFs) next Thursday (8/20) at 11:00 am Eastern Time.  I’ll identify my picks for the most-coolest of the cool and whackest of the whack DMOs and how to use them to make yourself a more productive DBA.  The webcast is free, so please vist the link below to register and heckle me.

Details are available at Quest.com/events.

Just shy of three weeks ago I ranted-on about issues surrounding a third-party application running against SQL that we had to ship-off to the vendor for execution of a stored procedure we could not successfully complete in our production environment.  That post, Just How Long is Too Long, has some even more developments that make the initial issues seem utterly sensical.  The crux of the initial post was that it took the vendor two weeks to run this stored procedure.  The crux of this one:  it was all wronged-up and den’ some.

Two weeks and we were finally instructed that the database was zipped-up and ready for us to download.  After about a half dozen attempts at restoring or restoring just the header of the backup file and I finally asked a question that I knew the answer to, but still needed to hear with my own ears…

Queasy DBA:  “Did you by chance upgrade the database to SQL 2008 from SQL 2005?”

Vendor Support Contact:  “Yes”

Queasy DBA:  “Why?”

My New Nominee for Worst Person on Earth:  “I am only doing what <REDACTED, NAME OF VENDOR PRESIDENT> told me to do.”

Yes, they took it upon themselves to upgrade the database to a version of SQL Server that is not consistent with where this database is being hosted, and we have no shared SQL 2008 instance we can port this db to.  You are also unable to restore a SQL 2008 DB back to a SQL 2005 instance.  (For the record you can not restore a SQL 2008 DB running in 9.0 compatibility mode on a SQL 2005 instance either.)  They gave us back a brick. 

After the requisite apologies and excuses the support staff kicked off the process once again on a restored copy of our backup usinig SQL 2005 in their environment.  This time it took only three days; ran over a weekend.  This leads me to believe that the initial process was not truly run over the two week period, though to this day they will not admit otherwise.  They posted a zip file to the FTP server for us to download and we were able to unzip it and reinstall it.  This leads to the next, and still currently unresolved issue - we were unable to connect to the database from the application.  The vendor support staff, once again citing that he was only doing what he was told to do by the President of the company, had upgraded the database to the most-current version of their product, thus rendering the current application unusable for our company.  We’re now thrust into a situation where we need to rush through an application upgrade in order to use the application.

Does anyone know what the opposite of that over-used word, Awesome, is?  I ask, because this final issue is so full of Anti-Awesome that it may require its own new term.  The initial reason for engaging the vendor was that this product causes massive, prolonged I/O and CPU issues for one of our shared SQL nodes.  The vendor has determined that they can not properly fix the database so they have instructed our application team to have the DBA (moi) restore two copies of the database.  The application will be used to delete (not truncate) delete all data in one of the databases so it can be repopulated and used for production.  They want me to leave the other database up and intact to use for archive.  Because of their lack of knowledge on how to solve the issue, or lack of willingness to invest the time to do so we now need to incur additional administration time, additional storage space, and additional backup tape and disk space (as well as backup system capacity) to maintain two shizzy databases instead of one.

The good news (for me) is that this is all going down while I am at Devlink in Nashville.  The bad news is that it even needs to be done at all.  It’s quite a shame too, the users tell me this application is fantastic and handles all their needs with ease in spite of the problems the DBA is shouldering.  However, at some point an organization needs to ask ”just how much is too much?“ I’ll be drafting requirements for the vendor to meet, if they can’t meet the requirements we will take our business elsewhere.  That is a great thing about a free market. 

Horror story closed?  Doubt it.

So Chris Shaw is at it once again, keeping the SQL Server community on the balls of it feet as opposed to the other way around I hope!  by making us think about what others think we do.  He has challenged us to explain Database Mirroring to the non-IT folk out there.  He also asked us our opinion on whether we thought our SAN solution was solid.  Well gee, first someone needs to explain SAN to me in common-folk wordlie-doos!  He tagged Brent Ozar, who in turn tagged myself, Tom LaRock, and Jason Massie, the old Bacon Bits and Bytes crew.

Seriously though which means absolutely no seriousness will follow I feel my SAN solution is as good as I’m going to get.  Like so many of us out there we’ve been forced into the BIG BLOCK O’ SANNAGE whereby we are allotted massive chunks of RAID 5 from our even-more-massiver RAID 5 SAN.  This is not the dedicated, separate LUNs we DBAs long for where we can place logs on RAID 1+0 and data on RAID 5.  Nope it’s all RAID 5 and even though we can have different mapped drives for our SAN allocation for logs and data it all derives from the same chunk. 

The second challenge posed by Chris: explain Database Mirroring in common terms really depends upon the audience.  Therefore I’ve listed a few options below:

Target Audience: Kids

—>  Mirroring is like that Whack-A-Mole game.  Y’know, the one where you beat on the heads of hapless plastic moles with a little pillow on a stick corded to the machine so your Daddy doesn’t take home the stick to use with Mommy when you go to bed so it isn’t stolen?  If done right, Mirroring means that when the Server Gods pound on your SQL Server and it goes down, your other SQL Server that looks just like it pops up somewhere else so your users can pound on it until it goes down and the old one pops back up.  If done wrong though, the databases don’t end up looking the same and your mole over here (look left and down) may look like a ferret while your mole over here (look right and up a bit) may need to be seen by a doctor for possible early signs of melanoma.

Target Audience: Non-Tecnical Wives, Husbands, Boyfriends, or Girlfriends

—-> Blah blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, SQL, blah, blah, blah, blah, blah, blah, blah, blah, Mirroring, blah, blah, blah, blah, blah, blah, blah, blah, blah, blah, so I don’t have to work late fixing the down server, blah, blah, blah, blah, blah, blah.

Target Audience: High School Physics Students

—->  It’s like when that dude Shroeder took a break from playing piano for that Lucy chick and stuck his cat in a box with some drugs.  Everyone knew there was a cat it the box (and that Shoeder was not to be trusted with his dad’s weed stash, but no one knew if the cat was dead or not.  On second thought it’s nothing like that. 

Target Audience: Musicians

—->  Mirroring is like paying your buddy Lou that you’ve know since High School go out on tour with you with the sole purpose of tossing you out a new guitar if you break a string.  The second guitar looks just like the first and if done right, it’s tuned to match the first.  Only a couple seconds of downtime and you’re back to shredding.  Plus it gives Lou some spending cash.  He’s not been the same since that high cat took out his left eye his sophmore year.  Even a one-eyed, tone-deaf burnout with Ailurophobia can mirror a SQL Database.

Target Audience: 1,000,000 Monkeys

—->  You know when you’ve been pounding away on keyboards for an infinite amount of time and the server goes down?  You have to start all over, right?  Well if the system you were using was mirrored you would only be hung up for a couple seconds and then right back to pounding away without any further hassle.  Now get back to work Code Monkey and finish building the software for that Oliva Wilde fembot you’ve been tasked with building.

Target Audience: Dogs

—-> It’s like when we play that ball game together where I fake the ball throw and you go after a phantom ball.  Only you end up finding a ball in the yard where I faked the throw.  Both balls look exactly the same, but you can only play with one at a time.  Speaking of that… here we trudge on Bacon Bits and Bytes territory… (treading lightly)

Target Audience: Porn Stars

—->  It’s like when you answer the door for the pizza guy and he sees you and your girlfriend obviously not able to pay because you have nowhere to store cash in your current state of undress.  Only one of you can repay him at a time for the pizza (in this scene) but if done correctly all users are satisfied with only limited interruption in connectivity, and no one calls it a failover.

I’m not sure who has been tagged on this, but I’ll push this exercise off onto Joe Webb, Grant Fritchey, and Gail Shaw, because I always value what they have to offer!