Dave Turpin, SQL Server Professional

I just wrapped up my first ever attendance at a SQL Saturday.  I was not disappointed.  But not only was I an attendee, but I was also a presenter (speaker) too.

The event was held at the Microsoft campus in Charlotte.  The facility is first class and the principal organizers, Peter Shire and the management and staff at SQL Sentry, could not have found a better venue.

There were 54 scheduled sessions presented across 9 tracks that were delivered by 25-30 speakers.  Most of the speakers were SQL Server MVPs that flew in from all over the country.  And then there was me…

When I signed up for the event I suggested 3 “proposed” sessions: Query Plan Analysis, Implement a Sliding Window Partition, and a session on Profiler / Trace.  I figured one might get selected, maybe.  Fortunately for me the query plan session was picked.

Also, at the time of my “proposed” sessions, there was very few speakers registered.  So I assumed that they were going to have a tough time getting people to run the sessions.  Fast forward 2 weeks later and the speaker roster looked like a Who’s Who of SQL Server MVPs, both local and out of state.  Out-of-towners included Andy Kelly, Andy Leonard, Steve Jones, Andy Warren, Kevin Kline, Jessica Moss and more.  Local MVP talent included John Welch, Alejandro Mesa, Rafael Salas and several top SQL talents from Microsoft itself.  Honestly, had I known the speaker list was going to be of that caliber I would not have had the audacity to inject my name on such a list.  But sometimes Providence has a way of getting us do go outside our comfort zone to take us to the next level.

There were several unexpected benefits of being a speaker at SQL Saturday…  First of all you get to wear a really cool button down shirt at the event.  It was navy blue with the SQL Saturday logo and event number embroidered above the pocket.  It will be a nice keepsake of the event. 

The night before the event there was a “speakers dinner”.  Apparently the speakers dinner is standard protocol…  the main sponsor of the events hosts a dinner for all the speakers at a nice restaurant.  In our case we all went to Red Rocks in Birkdale Village.  Anything and everything on the menu or the bar, was fair game.

The second biggest benefit of being a speaker was the opportunity to meet, in a social setting, the many MVPs of the group.  Like they say about the PGA golf pros… Those guys are good.

Finally, at the top of the list of speaking at a SQL Saturday, is the lessons learned by preparing and delivering a technical presentation to a technology savvy audience.  For me there were many…  One of my favorite sayings is “If you want to learn it, teach it”.  The prep work of organizing your thoughts for the presentation, thinking about how to simplify complex concepts, trying to anticipate questions that may be presented, and then figuring out the answers, all contribute.

As expected, the attendees of the experience did ask some insightful questions that challenged my own knowledge of the subject.  Fortunately I had some help from the attendees who helped explain the answer to the question… To them I will repeat myself…  Thank you.

At first I was a little disappointed that my presentation was in the last time slot of the day… 4-5.  I didn’t expect anyone to stick around to see it, after all, it was one of the first decent Saturdays of the year.  Was I ever wrong…  Although my session was in one of the smaller rooms, it was packed.  My guess is there was between 40 and 60 people in it.  All the seats where taken.  People were sitting on a table that flanked the length of one of the walls, the back wall was lined with folks standing, and a few people were straddled on the floor.

Obviously I had a popular topic… because there weren’t coming to see me, a rookie presenter.  After seeing several sessions though out the day that were closely related to my session, in particular the session by Andy Warren on column and index statistics and Kevin Kline’s session on internals (basically how queries are processed) I was concerned that my session would be too much of a repeat of their messages.

Instead, I do believe, partly because one of the attendees told my so after the event, that my hands on demo filled the gap between the theory and concepts presented by others and the real world.  For that comment I am grateful…

I now have more work to do to improve my own knowledge and the overall presentation.  That only confirms the Learn it – Teach it theory.  But overall the experience was a 10!

Since Saturday I have had several LinkedIn invites from attendees, asked to consider presenting at the SQL Server UG in Columbia, SC, asked to consider presenting a webcast for SQLLunch, and had a couple folks indicate that the presentation has inspired them to look deeper at their own code and at the general topic of query plan analysis.  I know I struggle regularly with trying to get my arms around this 800 pound gorilla we call SQL server.  If 1 person in that room was moved to improve I consider it a homerun.  2 or more… well, that’s a grand slam.

I can ask no more from this event.  The rewards I have personally and professionally received from it far exceeded my expectations.  To all of the participants that stayed late, to those that provided constructive and supportive feedback, and to those that didn’t walk out…   I Thank You!

Stay curious; Keep learning…

Dave

A couple of weeks ago a colleague asked me how to determine the recovery model of a database using T-SQL.  In other words, not using SSMS.  For the purpose of this post I’m going to show how to determine the recovery model for a database using SSMS, and then answer the presented question.

The following screen can be viewed in SSMS by RIGHT clicking on the database in Object Explorer ==> Properties ==> Options.

From this we can see that the AdventureWorks database is Simple (the other options are Full and Bulk Logged).

But back to the original question…  How can this be determined using T-SQL?  At the time we were at lunch and I suggested using sp_helpdb.  I wasn’t positive this property was included…  but it turned out to be a good guess:

If you prefer to identify only the recovery model using T-SQL, such as to be able to save the information in a variable of temp table you can use the following approach:

There is an older version of the DATABASEPROPERTYEX called DATABASEPROPERTY.  According to BOL, it is not recommend using DATABASEPROPERTY as it has been scheduled for deprecation.

We can validate that DATABASEPROPERTY is deprecated by tracing deprecation in Profiler:

After running  the same query using DATABASEPROPERTY instead of DATABASEPROPERTYEX we observe the following in Profiler:

This only confirms what BOL was telling us.

OK, last approach (for today, I’m sure there are more methods) to determine the recovery model for a database:

Lessons Learned: As is always the case with SQL Server there are always multiple ways to arrive at the same result.  In this case we learned that we can capture a database’s recovery model by using:

• SSMS
• sp_helpdb
• DATABASEPROPERTYEX
• sys.databases view

We also learned and confirmed that DATABASEPROPERTY (no EX) will not be supported in versions beyond SQL Server 2008. 

Finally, we showed how to verify a deprecated command using SQL Profiler.

Although this post was focused on the recovery model of the database any of these methods will work with any other database properties.  For more information on the entire set of database properties see BOL for DATABASEPROPERTY .

Stay curious; keep learning…

Dave

One way to find out if a transaction is causing a block is with the sp_who2 system stored procedure.  The scope of this procedure is server-wide.  It identifies all connections to the database, the login name, the application that is running, and in the context of locks and blocks, any SPIDs that are causing a block.  The offending SPID will be in the BlkBy column.

I often find myself using transactions when doing DELETE or UPDATE operations to allow myself the option of ROLLING back the transaction in case something goes awry.  As equally often, I forget to COMMIT the transaction and move on the the next order of business, resulting in a lock on the table and essentially blocking everyone out of it’s use (unless they use the with (nolock) hint in their query).

If I ran the following query and forgot to commit the transaction what would happen?

USE AdventureWorks;
GO

BEGIN TRAN
INSERT INTO person.contact (FirstName, LastName, EmailAddress, PasswordHash, PasswordSalt)
VALUES ('Dave', 'Turpin', 'dave.turpin@acm.org', 'goblygook', 'Salt');

The table would lock up until either a commit or rollback happens.  But how would I know if there are any open transactions, either mine or somebody else?  sp_who2 would not indicate any blocks because there aren’t any.  There’s only an open transaction.  But as long as no other processes are tying to use that table there will not be any blocking/locking issues.

DBCC OPENTRAN will list the earliest open transaction.  In our simple example, the DBCC will return:

In this case we can see that SPID 54 had a transaction open since 5:08AM.  Now we can take a look at SPID 54, see who it is, and initiate either a rollback or commit.  Chances are if an uncommitted transaction is hanging out there there’s either a major bug in someone’s code, we have a legitimate locking/blocking problem, or its an ad hoc batch.

Since DBCC OPENTRAN only identifies the earliest open transaction, you may have to resolve someone else’s transaction before actually getting to yours.  Hopefully there will not be any open transactions for very long and the point is moot.  In practice, however, when we need to look for open transactions it’s because something else isn’t working or…  The red phones are lighting up with customer complaints.

Stay curious; keep learning…

Dave

And here’s the results:

At instance 173 we see the call to the sub-job.  Note the run_duration = 0.  If the job was synchronous this step should have taken 60 seconds.  At line 174 we note the Step 2 kicking off.

Finally, on 176 we see the sub-job running and taking 1 minute (100).

Had the sp_start_job run synchronously line 173 would have had a duration of 1 minute instead of 0.

CONCLUSION:  The system stored procedure sp_start_job is asynchronous.  If you are using this procedure to kick off other jobs all subsequent steps in the calling job stream should be independent of the subordinate job.

This “feature” could be used to start up several jobs concurrently from a single scheduled agent job.  In most cases, however, the use of sp_start_job from SQL Agent should be used with extreme care.

Happy SQL-ing!