Bragging on My Daughter Again

As I have mentioned before, my ten year old daughter Danielle is in the Young Marines.  One of the steps in the Young Marine program is Junior Leadership School, two weekends of drill and classroom training on leadership.  Danielle was nominated by her unit’s leadership to go to the training and did very well.  As part of the training she was required to do an oral report on one of the fourteen characteristics of leadership and she chose to do endurance.  Her instructors were so impressed that they asked her to do her speech at the Memorial Day ceremony at The National Vietnam War Museum in Orlando, The Bunker, on Sunday, May 24th.  Danielle accepted, but as the day approached she got nervous and wanted to back out.  Her mother and I did not let her back out because we believe that once you make a commitment you honor that commitment.  It was also important to us that she understand what an honor it was for her to be invited to speak at this event that honors those that serve or have served our great country in the military.

The ceremony included presentation of the colors, invocation, Danielle’s speech, Young Marine Recruit graduation, placing of the wreath, The Last Patrol, and Convocation.  There were about 100 people present and, I have to say, you would never have known that Danielle was nervous when she did her speech.  She spoke slowly and clearly and acted like she spoke in front of groups all the time.  I had her practice with me twice during the week and she did much better on Sunday than in practice.  Alice, her mother, and I couldn’t be more proud of her.  Not just because she did well, but because she honored the commitment she had made, even though she did not want to do it.

Alice and I are thankful for the children that God has blessed us with and are excited to see Danielle excel.

Tampa SSUG Recap

I had the opportunity and privilege to present Introduction to SQL Server Profiler to the Tampa SSUG last night.  I was impressed by the turnout as it was a full house with about 40 in attendance.  I arrived early, and had a chance to meet a few people, and attempt to work on my networking skills.  I learned I need more practice as I’m still having trouble remembering names of people that I met for the first time.  Some of the people I met were Jonathan Kehayias, @jmkehayias on twitter; Carlos; Andres (I hope I spelled it right); @SQLChicken, Jorge Segarra; Ron Dameron and Nathan (Fraggle on SSC), who I had met at SQLSaturday 8 – Orlando last fall; and, of course, Pam Shaw (@pamshaw) who does a great job leading the group.

After Pam went through the announcements Jorge led a discussion on the pros and cons of social networking (Twitter, LinkedIn, Facebook, etc…).  Some good stories were told about how problems had been solved through the active SQL Server community on Twitter, and a couple of attendees shared how that they had used LinkedIn to either find a job or a candidate for a job.  I think some attendees may have been convinced that there are business/professional reasons to use social networking tools.

After that discussion I was up with my presentation on Profiler.  This was the first time I’ve done the presentation on my new laptop with SQL Server 2008 installed so I had a couple of minor technical glitches like not having reporting services running as I expected and adjusting to how Vista handles presentation mode.  I still need some work on that.  I always start by asking who has used or uses Profiler and I am always surprised that every hand doesn’t go up.  The other question I ask is how many people know that SQL Server 2005/2008 has a server-side trace running (the Default Trace) on install.  Again, the majority of attendees do not know this.  Overall I covered everything I planned in time and had some good questions around the difference between SP:Completed/RPC:Completed and SP:StmtCompleted/RPC:StmtCompleted and good discussion about why you even have the option of having Profiler write to a file or table while running the GUI, since it is recommended against and you can save to a file or table when the trace is done.  Jonathan Kehayias gave me a couple of tips on things I missed or should have said, like when tracing lock events DON’T trace Lock:Acquired as that will fill up a trace fast.  Remember locks are good, deadlocks are bad.  It felt like everyone was pretty happy with the information presented and several people let me know they enjoyed the session, so I guess it well. 

Even though it went well, I know I can do better, but the more I present the better I’ll get.  I’ve got SQLSaturday 14 – Pensacola coming up on June 6th where I’ll be doing my Profiler presentation and Space Coast User Group in September where I’ll be doing a presentation on mining the Default Trace.  I hope to see you at one of the upcoming events!

What are EventSubClass and ObjectType in my Trace File?

Have you ever wondered what that 1 in EventSubClass or ObjectType 8277 mean in that trace file you are querying using fn_trace_gettable?  If I open the file in Profiler I see EventSubClass 0-Begin and for ObjectType I see 8277-U, but when I use fn_trace_gettable I only see 0 and 8277.  Well, today I found out how to find out what those nice integers mean.  Both translations can be found in the sys.trace_subclass_values system view.  This view consists of the trace_event_id (eventclass in the trace file), trace_column_id, subclass_name, and subclass_value.  So the query:

SELECT
TE.NAME AS event_name,
 TSV.subclass_name,
TSV.subclass_value 
FROM
sys.trace_events AS TE JOIN
sys.trace_subclass_values AS TSV ON
TE.trace_event_id = TSV.trace_event_id JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id 
WHERE
TC.[name] = 'EventSubClass'
ORDER BY
 event_name

Will return all the EventSubClass names for each event and:

SELECT
TE.NAME AS event_name,
TSV.subclass_name,
TSV.subclass_value 
FROM
sys.trace_events AS TE JOIN
sys.trace_subclass_values AS TSV ON
TE.trace_event_id = TSV.trace_event_id JOIN
sys.trace_columns AS TC ON
TSV.trace_column_id = TC.trace_column_id 
WHERE
TC.[name] = 'ObjectType'
ORDER BY
event_name

returns all the ObjectType names, well actually abbreviations.  If you really want to know the names of the ObjectTypes you need to look here.  Or as Brad McGahee recommends in his book Mastering SQL Server Profiler you can search for “ObjectType Trace Event Column” in BOL.

An Explanation of Why to Use Stored Procedures

I wish I was a smart as the guys I interact with on sites like SQLServerCentral and Twitter.  Paul Nielsen, @PaulNielsen on Twitter, has an excellent blog post today on Why Use Stored Procedures that I think is a must read for everyone involved with SQL Server.  I am a huge advocate of stored procedures and Paul does much better job explaining the why’s than I ever could. 

So thanks Paul, now I have someplace to point the nay-sayers.

JumpstartTV Featured Content

My video, Creating a Performance Counter Log, is the featured video on JumpstartTV today.  This is a short one covering how to create a counter log using Perfmon.  This is a pre-requisite for another video I have done, Integrating Performance Monitors with SQL Server Profiler. 

A side note, I really enjoyed making the videos and I think the short (less then 5 minute) how-to videos are a great way to get some practical knowledge.

Software Developers, Remember that People Need to Use Your Software

As a hybrid DBA\Developer I had the message of the title reinforced through a negative experience with a web site this past week.  Please bear with me as I tell a fairly long story/rant to illustrate my point.

The web site I had an issue with is www.sunpass.com.  SunPass is Florida’s prepaid toll system and they provide a web site to manage your account.  There are 2 devices you can purchase, a portable transponder and a SunPass Mini Sticker transponder which applies to your windshield and is only good for 1 vehicle.  My wife and I purchased a SunPass Mini and I went to the web site and activated it and I set up my account so that it would auto-replenish using one of my credit cards when the balance dipped below a certain threshold. 

Well, last week we traded in the vehicle that had the Mini in it, so we bought a new Mini for our new vehicle.  This is when the trouble started.  I went to the SunPass web site to activate the new Mini.  I logged into my account and nowhere on my account home page was there a link to activate a new transponder or remove the old transponder.  I thought, “Well, the package says I can activate it online and I activated the first one online so I know it has to be here somewhere.”  So I double–checked and nope, not there.  I went back to the SunPass home page and, lo and behold, the “Existing Customers activate a New Transponder” link was on that page instead of my account home page.  Alright that’s not the most intuitive interface, but I only spent about 10 minutes trying to find it, but at least I found it.  So I clicked on the link and here is what happened:

Wait a minute, I don’t have a commercial account, what’s going on?  So I lookup the customer agreement and here is the definition of a commercial account:

5.2 Commercial Accounts: Primarily for companies or businesses with corporate owned, leased or rented vehicles and/or trailers. A current Federal Employer Identification Number (FEIN) must be provided to open an account of this type. FEIN information must be kept current and may be requested by a SunPass® representative at any time. The minimum opening balance for a Commercial Account is dependent on the User’s estimated monthly usage, but must be a minimum of $50.00. User shall be notified of any changes to the required replenishment amount on their monthly Account Summary Statement. For Commercial accounts to qualify for tax exempt status, User must provide a valid tax exemption certificate at the time of account activation.

Nope, I didn’t provide any of this information when I setup my account so, why am I getting this message?  I found the contact us link and sent them a message asking why I was getting the message.  As expected I got a non-response response:

Please call customer service

The next day I got a letter from SunPass telling me that I had a negative balance and I needed to call them to pay up.  Now I’m not only confused, but I’m a getting a bit ticked off, I mean, I did setup my account to auto-replenish, why isn’t it happening?  Back to the web site to make sure auto-replenish is setup, yup, it is.  So I call customer service to deal with the issues.   Here are the answers I got:

1. Can’t add transponder because I have a commercial account.
   The reality is that when they added the new Mini’s they could not treat them like regular transponders, why I don’t know, so if you open your account with a Mini your account is treated like a commercial account.  So you have to call to activate any new Mini’s on your account.  I asked “How was I supposed to know that?” and the answer is you have to call.  So I suggested that they fix the message to tell me the REAL reason the web site doesn’t do what asked.
2. Can’t delete the old transponder.
   Again, because it is a Mini it is treated differently and they assume you will disable it by taking it off the windshield so you don’t need to ever disable the transponder.  I asked, “How was I supposed to know that?” and the answer is you have to call.
3. Auto-replenish isn’t working.
   Apparently the system had a “glitch”, so while the web site is showing that auto-replenish is setup it really never was.  I asked, “How was I supposed to know that?” and the answer is you have to call.

So now to the point of the post. What could the developers/designers done differently to provide a better user experience.

1. Put account related activities on the account home page.  So an add transponder link on my account home page in addition to the main home page would have made life much simpler and intuitive.
2. Provide a message that conveys the real reason functionality is not available.  Tell me that you cannot add a Mini to an existing account, not that I have a Commercial account.  In my opinion, the real answer is to not provide the functionality at all.  If account type = Commercial do not show the Add transponder link.
3. Catch errors and tell the user that their efforts were unsuccessful.  If something goes wrong in setting up auto-replenishment tell me!  If it happens in a batch at the end of the day, send me an email telling me it failed.  Don’t wait to send me a letter when I’m already in the hole.

Please keep the end user in mind when designing and developing your applications.  Don’t frustrate your customers, whether internal or external, with poor design.

SSIS Trace File Source Adapter

I just found out about the Trace File Source Adapter available from SQLIS.com.  This tool will allow you to read a SQL 2005/2008 trace file within an SSIS package.  I learned about it from this entry on Chris Webb’s blog.

I have not used or even downloaded it yet, but I will as I am interested in all things Trace/Profiler related.  It looks interesting although I don’t see a compelling use for it at this point.  I am firm believer in being aware of the tools available and this is another one to add to the toolbox.