Thursday, January 28, 2010

What the Exec?

I’ve been working with SQL Server for 10 years and have worked in production with versions 6.5 – 2005 and used 2008 personally.  I know I haven’t seen everything, but a couple of weeks ago a friend of mine asked me to look at some stored procedures he was working on and I saw something I’d never seen before.  The code was something like this (I’m using the AdventureWorks database, 2005 version):
CREATE PROCEDURE dbo.exec_example
(
@parameter VARCHAR(10) = 'employee',
@ID INT = 10 
)
AS

SET NOCOUNT ON;

DECLARE @sp_name NVARCHAR(100);

IF @parameter = 'employee'
BEGIN
SET @sp_name = 'dbo.uspGetEmployeeManagers'
END
ELSE
BEGIN
SET @sp_name = 'dbo.uspGetManagerEmployees'
END;

EXEC @sp_name @id;

RETURN;
The interesting part is where the stored procedure name is put in a variable and then you just use Exec @sp_name with the parameters listed after.  I’d never seen this done before, and my first comment on the code was, “I don’t think that should work”, but it does.  I also looked it up in BOL and here’s a couple of snippets from the EXECUTE (Transact-SQL) entry:

From the Arguments section:



@ module_name_var








Is the name of a locally defined variable that represents a module name.

From the Examples section:
E. Using EXECUTE with a stored procedure variable
The following example creates a variable that represents a stored procedure name.

DECLARE @proc_name varchar(30);
SET @proc_name = 'sys.sp_who';
EXEC @proc_name;
I had been contacted to try to help clean up and improve the code my friend was writing and this was a time where I learned something.  This is one of the things I love about SQL Server, and technology in general, you can learn from anyone, even when you’ve been called in as the expert.

Sunday, January 24, 2010

SQLSaturday 32 – Tampa Recap

I had the privilege to attend and speak at SQLSaturday 32 – Tampa this past weekend.  As I said in my event announcement, SQLSaturday is always a good time, and this one was no different.  My wife (yes, I brought my wife and she enjoyed herself, too) and I left Sanford about 1:30 Friday afternoon so we could beat the traffic and arrived in Tampa a little before 4:00 pm.  Our biggest problem on the way was that there was construction near the hotel so we had to drive around a little to find the hotel parking garage.

Friday

Once we got checked in we enjoyed a nice stroll around the historic Ybor City area where we ran into Aaron Nelson (@SQLVariant) and his dad, and the Pragmatic Works crew, Adam Jorgensen (@adam_jorgensen), Brian Knight (@brianknight), Patrick Leblanc (@patrickdba), and Jeremy from Boston (can’t remember his last name).  Then we made our way to The Spaghetti Warehouse for dinner and the speaker/volunteer get together.  This was our second mistake as all the other SQLSaturday’s I’ve spoken at have provided only appetizers so that’s what I thought would be provided so we ate dinner BEFORE the party and then found out that dinner WAS provided.  We still had a good time as I got to know Kevin Boles better and then met Troy Gallant (@GratefulDBA) and David Taylor (@dyfhid) for the first time.  The speaker/volunteer party is ALWAYS a good time as you meet new people and get back together with all the regular gang.

Saturday

My wife and I arrived at the event location on Saturday morning about 8:15, assuming that would give us time to check-in and then I could find my room and get set-up for my 9:00 session.  Well, it didn’t quite work out that way.  There was a glitch that slowed check-in so I skipped check-in and went right to my room to get set-up.  My biggest fear when speaking is that something technical is going to go wrong like not getting the projector to work, so I always get into the room as soon as I can.  I was glad that I did as I did have trouble getting the projector working, but I finally found a “cheat sheet” in the podium drawer and go everything working.

The check-in problems delayed the start of the first session, so I didn’t get started until between 9:10 and 9:15.  That little delay gave me some time to socialize with the early arrivers and find out that most were at their first SQLSaturday and did not attend a user group.  So I was able to encourage them to make an effort to attend their local user group and extol the virtues of user groups.  During the presentation I was also able to promote PASS and the PASS Summit.

The Presentation

My presentation was titles, “Why I Use Stored Procedures”, and I billed it a an open discussion, but, unfortunately, I still ended up doing most of the talking as I didn’t have anyone in the room who strongly held the opposite opinion.  My main points were that stored procedures offer benefits in the areas of:

  • Security
  • Maintenance
  • Performance

I ended the session by demoing some of the different ways to access a database using .NET.  I had a simple windows form application with a textbox and 5 buttons, each of which used a different method to to query the database.  I used:

  • Linq to SQL
  • Entity Framework
  • ADO.NET with Ad Hoc SQL
  • ADO.NET with Parameterized SQL
  • ADO.NET stored procedure command

During the demo I had Profiler running to show the SQL that was submitted using each method.  I traced the following events:

  • RPC:Completed
  • SP:CacheMiss
  • SP:CacheHit
  • SP:CachInsert

I wanted the attendees to see the following things:

  1. That Linq to SQL and Entity Framework work similarly and that while they use parameterized SQL, the parameters (at least character parameters) are set to the length of the string.  This means that a new plan is built whenever you have a different length string passed.  One attendee mentioned that this is fixed in .NET 4.0.
  2. That Ad hoc SQL has a new plan created if there are any changes to the SQL, including spaces and case.
  3. That ADO.NET parameterized SQL and calling a stored procedure (using the stored procedure command type) build one query plan and re-use it.

Turnout for the session was good as the room was basically full which was nice considering the other sessions going on and the delay at the check-in table. 

Overall the session went well and the attendees seemed to have enjoyed the session and learned something.  The biggest mistake I made was that I forgot to put my contact information in my slide deck.  The positive side of that is that several people came to get my business cards and I think that was partly due to my forgetting to put the contact information in the deck.

All materials are available on the SQLSaturday site here.

Day Summary

I then attended a three sessions, Dmitri Korotkevitch’s “Getting familiar with SQL Server Storage Engine”, Geoff Hiten's “Clustering for Mere Mortals” (a great session since I don't have clustering experience), and Jonathan Kehayias’ “Real Time Problem Identification with Event Notifications” (another great session).  Then it was time to head out as we needed to get home to Sanford to pick up our kids.

As usual Pam Shaw (@pamshaw), Jorge Segarra (@sqlchicken), and volunteers deserve kudos for putting on a great event.  There are always a few glitches like at check-in, but those really don’t put a damper on the event at all.

Thursday, January 21, 2010

SQLSaturday #32 – Tampa

Just a reminder that SQLSaturday #32 – Tampa is taking place this weekend (January 23rd).  This will be my 5th SQLSaturday and 4th as a speaker and every one has been a great event.  As a matter of fact, I enjoy SQLSaturday’s so much that I’m bringing my non-technical wife to this one with me.  You can still register here and see the schedule here.

I’ll be speaking at my 3rd SQLSaturday and my session is:

Why Should I Use Stored Procedures?

With the proliferation of ORM Tools like Linq To SQL, NHibernate, there has been a move to no longer use stored procedures for Data Access, I personally disagree. This will be a professional and polite open discussion starting with why I believe stored procedures are the best way to access data in SQL Server. Alternate view points welcome.

I’ll be sharing my views on why I believe stored procedures are better for security, performance, and maintenance.  I really do hope that there will be attendees who will share their views and experiences in a professional manner.  I have to agree with Paul Randal(@PaulRandal) who said on twitter the other day:

Credibility = being happy to admit it when you're wrong or don't know

Which reminds me of this verse:

A good reputation and respect are worth much more than silver and gold.  (Proverbs 22:1 CEV)

So far I’ve done okay in maintaining my credibility and, I think, respect and I plan on continuing that at this event as well.

Tuesday, January 19, 2010

Three Events that Brought Me Here

Paul Randal started the latest community chain blog, What three events brought you here?, and I was tagged by Steve Jones (@way0utwest), his post, and Brian Kelley (@kbriankelley), his post

I could go a couple of different directions with this:

  1. How I ended up a DBA/Developer
  2. How I ended up serving as a missionary with New Tribes Mission doing DBA and development work.  Did you even know you could do that?

I think for this post I’ll go with #2 because I sort of covered #1 in this post which I wrote just before Paul started the chain.

Friends in Low High Places

I got started in tech because my best friend (Greg Corrigan) from high school and through college was (still is) as programmer and was also the software development manager at the local pulp & paper company.  He did some consulting work on the side that I helped out with as mainly a hardware grunt, but I also did some network setup and a bit of Microsoft Access programming with him.  During this time he had 2 Software Developer positions open and, for about 6 months, he kept asking me to apply, but I kept refusing because I didn’t feel qualified and I didn’t want to take advantage of our friendship.  This was in 1999 before the dot com bubble burst so to get experienced developers or even computer science graduates to move to northern NH (here’s where it is) was pretty much impossible.  My friend believed in me and I finally had (another) blow up with my boss and decided I’d apply.  I interviewed with Greg and the other developer on staff (Ernie Miner) and then with the Greg’s boss, the Controller.  I was hired, basically as an apprentice and I started out writing reports using Access as a front end and rather quickly moved to SQL Server development and I found I had an aptitude for working with data and that I could “think in sets” so I stayed with SQL Server, while still doing some VB and then VB.NET development.  My friend, Greg, was and still is a great teacher and he really provided a good foundation.  I still wish I had a better grasp of theory, but I’ve been getting the job done for 10 years.

A Failing Industry

The pulp and paper industry in the United States is, sadly, a dying industry.  While I was in the industry it seemed another mill was closing every month, and it finally happened to us.  About 20 months, late summer 2001, after I was hired the facility I worked at closed it’s doors as the parent company filed for bankruptcy.  It turns out I worked for crooks, I call that company a mini-Enron.  They were borrowing against inventory that, in some cases, we didn’t even have.  The owner of that company is now in prison.  Anyway, this was AFTER the bubble burst so I spent 9 months looking in vain for work as a developer (didn’t know I was or wanted to be a DBA), when our facility was purchased out of bankruptcy and I was one of the lucky few to be brought back, the only problem was that in that company’s opinion, “I.T. is a necessary evil that brings no value to the company.” so we were a bit hamstrung in what we could do, but I was happy to have a job.  About a year later I arrived at work one Monday to find out that there was a round of unexpected layoffs (the expected ones had happened a few months earlier).  At that point every salaried employee decided it was time to look for another job, including me.

A Fit including Faith

My wife and I had discussed becoming missionaries with New Tribes right after we had married, but decided to wait a year or two which turned into ten.  Based on that early discussion I decided to check out the New Tribe web site as part of my “job” search.  I clicked on “Go” and “Careers” and saw a posting for 2 Database developers in Sanford. FL.  I applied on-line and then called my wife to tell her what I had done.  This was in February 2004 and we were “hired” in May that year.  After about 2 1/2 years of travelling around New England visiting churches doing what New Tribes calls Partnership Development (we had to go out and fund raise our salary.  People of like faith pledge to donate money to the NTM for us so we can help spread the Gospel of Christ), other missions call it deputation.  We finally made the move in June 2007.  I started out with NTM learning the existing system in Visual FoxPro and doing some work with that, then I began working with one of the other developers on re-writing our personnel system using SQL Server and ASP.NET (C#).  I also eventually inherited a financial system that was running on a SQL Server in the Finance office on a PC AFTER it had crashed and needed to be recovered.  That recovery has been by biggest success to date with NTM.

The move to Florida also got me involved in the SQL Server community.  Prior to the move the most involved I had been was lurking and occasionally posting to the SQLServerCentral forums.  Now I have written articles, done videos, blogged, presented at user groups and SQLSaturday’s, and, recently, become a leader in my local user group (OPASS).

So that’s how I got where I am in 3 parts, even though there are a lot of other things that happened along the way.

I’m going to tag:

Monday, January 18, 2010

Accidental DBA?

It seems to me that over the last year or so that there has been an explosion in the use of the term “Accidental DBA” in the SQL Server community.  A google Bing search for “Accidental DBA” returns over 7 million results including blog posts, articles, editorials, and video training.  Since I’ve only been a SQL Server DBA and involved in the SQL Server community I don’t know if there is a similar term with other RDBMS products like Oracle or DB2, but I can say that I “accidentally” became a SQL Server DBA, it wasn’t the career I planned on in college, nor once I entered the Information Technology field.  The interesting thing is that many of the people I respect in the SQL Server community did not originally become SQL Server DBA’s by choice.  Andy Leonard (@AndyLeonard) says he became a SQL Server DBA because “I was the closest one to the SQL Server when the DBA left”.
So how did I end up a SQL Server DBA?  Well, it’s a long story that I’ll try to make short.  I started out by getting a Bachelor’s degree in Physical Education and then I learned that working with children was probably not a strength.  I struggled for awhile and thought I’d like to get into IT, and I thought I’d enjoy network and system administration, but, through a good friend I got a job as a software developer working with Access, Visual Basic, SQL Server, Sybase, and DB2 400.  As I worked with each of the technologies I found that I had an aptitude for SQL Server and here I am 10 years later, looking for ways to share the things I’ve learned and finding that the more I know, the more I need to learn.
So how did you become involved with SQL Server?

Thursday, January 14, 2010

Broadcasting OPASS over Live Meeting

Well, on January 12th OPASS moved into the present by broadcasting our meeting over the internet using Live Meeting.  Our main speaker, the incomparable Andy Leonard (@AndyLeonard), was presenting to us from somewhere outside Richmond, Virginia and we had attendees from the Europe to California.

Why did we do Live Meeting?

Since OPASS was started by Andy Warren (@sqlandy) we have been meeting six times a year, basically every other month, which makes finding speakers easier and means we don’t need to raise as much money, but, it also means that we have had trouble becoming a “habit” for attendees.  We did a survey and the majority said that they would like to meet monthly and that it would make them more likely to attend regularly, so we decided we’d move to monthly meetings for 2010.  Another part of the survey asked about bringing in speakers via Live Meeting so we could expand the reach of our speakers, to which we had a very positive response.  Live Meeting was the technology chosen because the chapter can get a free account through PASS.  As I type this I’m also watching/listening to Oregon SQL Developers Group over UStream, so there are other methods as well.

What were our concerns?

Our, really Andy’s, main concern was that having a remote presenter would make it feel like a webinar instead of a social user group meeting.  Our other concern was that people would join on-line instead of coming to the meeting in-person.

How did we do it?

OPASS is fortunate that we meet at the SQLShare offices so we have good internet access and plenty of PC’s to work with.  Our setup took three PC’s, although I think we can get it down to two.

  1. One PC connected to the projector so the people in the room could see the slides and demos.
  2. One PC connected to speakers, camera to broadcast the room to the speaker, and microphone.  When we tested we used a bluetooth headset for the mic which was great because it could be moved around the room, but then we couldn’t get that to work at the actual meeting so we used the mic built-in to the webcam.  We will be working on that again.  The mic is important because it can make the meeting more interactive.
  3. One PC connected to be the moderator for questions from remote users and, if the mic doesn’t work, local users.

We wanted to combine PC’s 1 and 2, but we had trouble getting the mic to work on PC 1 and also the webcam would not share over Live Meeting from PC 1.   We wanted to broadcast video of the group to the speaker so that the speaker could get visual feedback while presenting.  The lack of live feedback was one of the most difficult things for me when I did a SQLLunch, so we hope that this will help the speaker.

What did we miss?

Well, I mentioned out technical problems with the local mic and the webcam.  I forgot to click record as I was involved in trying to solve the minor video and audio issues right up until we started, along with trying to greet people.  We forgot to turn off the screen saver on the PC connected to the projector so we lost the presentation a couple of times.

What can be done to make it work better/easier?

  1. Put together a checklist so we don’t forget any steps, like recording.
  2. Have a dedicated PC for display, audio (in and out), and video streaming out.  Then we can configure once and just plug it in for each meeting.
  3. Set at time to get on with the presenter so you can test audio and video.  Andy Leonard was great as he connected about an hour before he was due to start, without being asked, so we could make sure everything worked, but this wasn’t planned.
  4. Have someone who controls the display PC and acts as a “director” to switch between the presenter’s slides/demo and the video of the presenter (if they are sharing video).  Basically, when answering questions, show the presenter and when doing demos/presenting show the slides/demo.  This is Andy Warren’s idea and is a good one.  The only thing that might be better is to have a second projector so you could show the slides on one and the presenter on another.
  5. Make sure we are totally setup before the meeting so that more time can be spent networking and encouraging networking.

As with anything else, the more we do it the better we will get.

How’d it go over with the attendees?

All the local attendees thought it went well and that it worked great.  Our concern about reduced physical attendance was unfounded as we had our usual attendance of about 20 plus about 10 on-line attendees.  The physical attendees all said that being there in person was a much better experience than attending on-line so that was encouraging.  I felt like having 2-way audio really made the meeting better as the only Live Meeting’s I have attended have only had 1-way audio and that isn’t ideal.    The 2-way audio was another of Andy’s ideas.  We haven’t gotten any negative feedback from our on-line attendees so that seems to have gone well also.

Will we do it again?

Definitely!  We are planning on broadcasting all our meetings using Live Meeting and we are looking for our a remote speaker for our April 13th meeting.  So if you are a interested and available let me know and we’ll see if we can work something out.

So, all you folks who have been doing Live Meeting, how have you been doing it?  Have you done 2-way audio and video and how has it worked?  Any comments or suggestions?  We are always looking for ways to do things better. 

Tuesday, January 12, 2010

Would Third-party Product Driven/Vendor Sessions Benefit User Groups and Community Events?

In the the couple of years I have been involved in the SQL Server community I have noticed that there is an issue with third-party product driven sessions or sponsor sessions at events.  The argument I usually hear against these sessions is that organizers are concerned about the event becoming viewed as a marketing event.  I’ve even had a short email discussion with Grant Fritchey (@GFritchey) about how it would be nice if he did a session on database source and version control based on a post he made in this SQLServerCentral thread.  His response to my suggestion was:

…but I need to do it without using any particular vendor’s products… not easy…

and

…lots of organizations and people won’t accept presentations when they include third party products. It’s too much like a sales pitch…

Steve Jones (@way0utwest), also, had an editorial poll last Friday (1/8/2010), Balancing the Message, that dovetails nicely with this post, where he asks:

Think about the value that you've gotten from third party products. Think about the problems you've solved, or the help you've gotten from a backup tool, an editor, or a performance product. How would you like to learn about enhancements. What's a high impact, low interruption way of communicating with you?

I’d like to present 2 reasons why I think third-party product driven and/or vendor sessions should be acceptable at events:

  1. Most of us use third-party products, and we could use the help in learning how to use them better.  Isn’t that the point of user groups and SQLSatuday’s?  If I’m using a third-party product to monitor my SQL Server all the great sessions on DMV’s, Wait Stats, and Extended Events are great, but I also need to be taught how to best leverage the capabilities of the product as well.  The best part is that this session doesn’t even have to come from the vendor, it can be from any SQL Server professional who uses and is passionate about the product.
  2. Vendors pay for these events and they need to get value from the event as well.  Sure, they get a table and the opportunity to do demos for folks who stop by, but I think they would get a better response with a full-session and proper presentation facilities.  I did have a Twitter/email conversation about this with Brent Ozar (@BrentO), who works for Quest.  He thinks that Quest, and likely other vendors, would be more willing to sponsor events if they were guaranteed a time-slot for a product demo.  I don’t have an issue with this as long as the session is clearly marked as a product demonstration.  This demo could be the thing that gets them the sale or sales that make the event worth sponsoring.  You wouldn’t give every sponsor a session, but your top-level sponsors might get one and you could limit the number of top level slots.

I have to admit that reason 1 carries more weight with me as an attendee/organizer than reason 2.  I’d especially be interested in sessions submitted and presented by users of a third-party product than by the vendor as it would be less likely to be a sales session.  I have to admit, that, as a member of OPASS leadership, I’d be unlikely to have a vendor session at a user group meeting, but at a SQLSaturday, I’d happily have a 2 or 3 vendor sessions, as I believe any marketing backlash would easily be offset by the 20 or more non-vendor session.

So what do you think?  Would you be turned off to SQLSaturday or other community event if there were vendor sessions or sessions based on third-party products?

Video Featured on SQLShare

I didn’t have a chance to blog this yesterday (January 11, 2010), but my most recent video, Dynamic SQL Using sp_executesql, was featured on SQLShare yesterday.

It was great as it generated several comments and I was able to take the time to answer them this morning.  I love it when there is feedback on something I’ve done.

January OPASS Meeting Tonight

Tonight is the January meeting for OPASS and we have a great schedule for the evening.

Robert Hurwitz a developer for the Florida K-12 online public schools will be doing a mini-presentation on Automating Index Maintenance from 6:15 to 6:30 and out main presentation will be by Andy Leonard (@AndyLeonard) via Live Meeting on Incremental Loads in SSIS from 6:45-8:00. 

Both presentations will be part of the Live Meeting broadcast, but we hope that you local people will come in person to support Robert and to take advantage of the networking opportunities.  You can attend the Live Meeting using this link the event id and password are included in the link so you should have no problems getting in.

Pleas visit the OPASS web site for directions and more details.

See you tonight!

Thursday, January 7, 2010

Book Review: SQL Server 2008 Query Performance Tuning Distilled

I recently finished reading the Apress book SQL Server 2008 Performance Tuning Distilled by Grant Fritchey and Sajal Dam.  I consider Grant to be a personal friend in addition to his being one of the top SQL Server experts.  I have not had the opportunity to meet Grant’s co-author, Sajal Dam, although the fact that he worked with Grant on this book makes me place him in high regard.  Unlike the other 2 book reviews (Murach’s JavaScript and DOM Scripting and Apress Pro SQL Server 2008 Administration I have done, this is a book I purchased. 

Overall Impression

This was a fantastic book.  I agree with what Brent Ozar (@BrentO) said in his review:
My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book.
Several times in the last two weeks, I reached for this book first.
I have also reached for this book several times since I purchased it, when I was baselining servers or looking for ways to improve performance.

Chapter Details

Chapter 1 – SQL Query Performance Tuning

A great introduction to the process of performance tuning and common performance killers.  I especially like the way the authors present the concept of “Good Enough” Tuning, where the performance may not have reached the theoretical optimum, but is “good enough” for your purposes.   It sets up the rest of the book very well.

Chapter 2 – System Performance Analysis

The authors present some of the tools you can use to measure the performance of your system including Performance Monitor and Dynamic Management Views.  They present a good list of performance monitor counters to watch and the general thresholds that indicate a potential bottleneck.  Not only are the thresholds presented, but potential resolutions as well.  I refer back to this chapter often.  They also present the best outline for creating a baseline that I have read. 

Chapter 3 – SQL Query Performance Analysis

From system analysis now to query analysis.  This chapter covers Profiler, or as Grant prefers to call it, SQL Trace, analyzing execution plans, and query cost (client statistics, execution time, statistics IO).  The authors present steps to use each of these tools to improve individual query performance.

Chapter 4 – Index Analysis

The authors do a great job of starting with an explanation of indexes to advanced indexing techniques including filtered indexes and special indexes on full-text, spatial and xml data types.  Experienced DBA’s will recognize the techniques described for designing and choosing indexes and for beginners it is like having an expert along side them.

Chapter 5 – Database Engine Tuning Advisor

A short look at the tool provided by Microsoft to analyze a workload and present index recommendations.  A good tool, but one to be used carefully.

Chapter 6 – Bookmark Lookup Analysis

A look at, what is in my opinion, a very common cause of poor performance, bookmark (key/RID) lookups.  The authors define and then show how to troubleshoot and resolve bookmark lookups.  A must read chapter.

Chapter 7 – Statistics Analysis

One of the longer chapters in the book and rightly so as Statistics are a poorly understood subject and, in some cases, hard to understand and analyze.  This had been one of my weaker areas in SQL Server and this chapter certainly helped me become more comfortable with it.  I know that there is at least one query I have used an index hint on that, were I to go back, I would probably remove because updating statistics would cause it to use that index.

Chapter 8 – Fragmentation Analysis

Great coverage of what  causes fragmentation and how to remove it.  One of the best explanations of Fill Factor and it’s significance

Chapter 9 – Execution Plan Cache Analysis

Another of the longer chapters as this is an important skill/art to understand.  The authors explain how all the steps involved in creation and re-use from generation to aging out.  Then they explain how to optimize queries for caching.  Again this is a learned skill, almost more of an art.

Chapter 10 – Stored Procedure Recompilation

A good companion to chapter 9 as execution plan caching and stored procedure recompilation are related.  The authors do a good job showing how to find what causes recompilation and then how to avoid it.

Chapter 11 – Query Design Analysis

The authors go into the art of designing queries optimally.  One of the key section talks about how to use indexes effectively.   Another section also discusses things that are part of good database design (Domain and Referential Integrity) help with query performance.  These are often neglected because they are “enforced” by the application, so they need to be emphasized.

Chapter 12 – Blocking Analysis

Blocking is, in my opinion, one of the most misunderstood aspects of the relational database as it is a core component to guarantee the ACID properties, yet it is often confused with Deadlocking (covered in chapter 13).  The authors do an excellent job covering all areas of locking, ways to capture blocking data, and ways to reduce it through indexes, isolation levels, and partitioning.

Chapter 13 -  Deadlock Analysis

Another example of good organization as it follows the Blocking Analysis chapter.  The authors do a good job providing an explanation and example of deadlocking.  I especially liked the section on Using Error Handling to Catch a Deadlock, so you can handle the deadlock in your T-SQL.  The authors also give the best ways to avoid deadlocks and the caveats with each.

Chapter 14 – Cursor Cost Analysis

A great chapter on the cost and benefits of using cursors.  Since SQL Server is designed to work with sets, using cursors is not usually the best solution, but the authors do a good job explaining how cursors work and how to write the best performing cursors in the cases you may need to use them.

Chapter 15 – Database Workload Optimization

The authors take you through the process of applying the information presented throughout the book.

Chapter 16 – SQL Server Optimization Checklist

The authors present a short summary of each of the optimization techniques presented in the book.  Once you’ve read the book you can just refer back to the final chapter to get guidance and, if needed, go deeper into the area you are using by referring back to an earlier chapter.

Conclusion

A really great book to learn how to make your SQL Server perform better.  It has information for every level of SQL Server professional from beginner to expert.  I particularly liked the summary chapters (15, 16) at the end of the book.  This is a book I think every SQL Server professional should have on their bookshelf.

Monday, January 4, 2010

2010 Goals

It’s time to make a list of things to shoot for in the year ahead.  I did okay on my 2009 goals, but I hope to do better this year.  There is a new blog chain started by Tom LaRock (@SQLRockstar) for goals and a theme word for the year.  I have not yet been “tagged”, but since I’m posting my goals anyway, I’ll steal the “theme” as well. 
My theme word for the year is going to be “Finish”. 
Finish Line - Air Farce One
I’ve always been great at starting, but get easily frustrated and leave things unfinished, thus most of the goals for this year will be ones unfinished from last year.

Community

  1. Write 1 article per quarter for SQLServerCentral.  I didn’t finish it in 2009 so I’ll try again this year.
  2. Submit 1 video per quarter to SQLShare.  Again, an unfinished goal from last year that I’ll try again this year.
  3. Grow OPASS from 15-20 attendees to 25+ per meeting.
  4. Speak at 4 events this year (user groups, SQLSaturday’s, PASS Summit?)
  5. Present for one PASS Virtual Chapter

Professional Development

  1. Two blog posts a week with at least 3 scheduled.  I managed to average 2 posts a week in 2009, but rarely had anything scheduled.
  2. Read 2 technical and 2 professional development (productivity, networking) books.  I always have trouble finishing books I’m supposed to learn from and even more trouble applying what I’ve read.
  3. Get MCITP: Database Administrator 2008 – I’ve never considered certification that important, but as I move forward in my career I think this will help.

Technical

  1. Develop an application (C# or PowerShell) to automate testing of backups.  Copy and restore on test server.  I’d like it to be able to automatically restore Full/Diff/Transaction Log backups, even to a point in time restore. 
  2. Learn how to and do a page restore.  I’ve a read about them, but never tried to do one.
  3. Develop an application (C#) that uses SQL Compact Edition for offline access and uses SQL Azure for storage.

Personal

  1. Lose 25 lbs.  Do this through regular exercise and eating better.  Did well in Active August, but hurt my ankle in September and didn’t finish.
  2. Play my bass guitar 10 minutes a day to finish the book my wife got for me.
  3. Study my Bible every day.  I read now, but I want to really study to really finish this goal.
I’ve doubled the number of goals I had last year, but, as I mentioned, almost all of these are things left unfinished from 2009.  I think the hardest ones will be writing for SSC, recording for SQLShare, getting certified, the 2 that involve application development (probably the lowest on my list) and losing weight.  My plan is to post quarterly progress reports. 
I don’t know, am I being too ambitious?