December 2010 S3OLV Meeting

Categories: News, Professional, SSC
Comments: 1 Comment
Published on: November 30, 2010

It is amazing how the months just seem to fly by these days.  I probably say that every month.  It seems appropriate though.

This month we will be broadcasting the meeting once again.  This month the technology bug will not bite us like last month.  I can feel it – we are bound for a problem free evening ;).

For any that are interested in attending this presentation virtually, you are welcome to attend.  Here is the information that you will need in order to attend the meeting.

Attendee URL:  https://www.livemeeting.com/cc/UserGroups/join?id=8Z4B6M&role=attend

Meeting ID:  8Z4B6M

Wayne Sheffield is going to be presenting to us this month.  His presentation will be on the subject of Table Variables and some myths concerning those.  It is certain to be well worth your time.

Drumroll Please…

Comments: No Comments
Published on: November 29, 2010

Defensive Db Programming Chapter 10

After dragging this review on for months now, it is finally time to bring this bad boy home and wrap it up.  I have been working through a chapter by chapter review of the book by Alex Kuznetsov (Blog | Twitter).  Alex has been taking us through the process and various techniques to better program defensively.  This is the last installment in that series (yes shed a little tear).  This chapter is devoted to teaching us how to better deal with Modifications that Survive Concurrency.  Some of the problems associated with this are:

  • lost modifications, a.k.a. lost updates – such problems occur when modifications performed by one connection are overwritten by another; these typically occur silently; no errors are raised
  • resource contention errors – such as deadlocks and lock timeouts
  • primary key and unique constraint violations – such problems occur when different modifications attempt to insert one and the same row. (p. 337)

Another way of stating these types of problems is that there is a “Race Condition.”  You may have heard this term thrown around by the developers from time to time.  It isn’t made up and is a very real potential problem that must be dealt with accordingly.

I can’t give this chapter enough props nor enough due through a short blog post on it.  It is a lengthy chapter and covers a very important topic (my opinion).  Alex covers each of the race conditions and continues to show how to resolve that race condition.  He uses real world scenarios as you should have grown to see up to this point.  I will point out that I had not considered using RowVerssion to help resolve some race conditions.  That is at least one thing I have learned from reading this chapter.

Another thing that struck me and made me think is related to the TSQL patterns that Fail High concurrency.  Here two methods are scrutinized and a third method is proposed.  The third method is MERGE.  That is a subject about which I could learn a lot.

For the final time (since this series has come to a close), go get the book and read it.  You can find the rest of the articles by linking back through this link.

Giving Thanks

Categories: News, SSC
Comments: No Comments
Published on: November 22, 2010

I came across a blog post today from Jason Strate (Blog | Twitter) about giving thanks.  This topic has come about due to the holiday in the United States that happens to be Thanksgiving and is this Thursday (11/25).  In that post, Jason asks that we talk about things for which we are thankful or even just talk about Thanksgiving from the meal or family perspective.  In other words – a somewhat open ended topic.

Thus, without further adieu, here is a list of things for which I am thankful – in top 10 fashion.

10.  Microsoft CRM:  Without CRM, I would not be able to learn daily about bad design and worst practices.  I have seen evidence that this is not the case with just CRM and that it is also true of Sharepoint.  Someday I will finish that blog post I have been meaning to do.  That post will shed some light on the lessons CRM has taught me.

9.  SQLServerCentral:  I think this one goes without saying.  SSC is a great hangout spot for SQL Server geeks of all ability levels.  I have learned a lot from regularly visiting SSC.  SSC has also provided me with other invaluable opportunities (such as friendships).  Thanks goes out to SSC, the threadizens, and all participants there.

8.  SQL Community:  This might be able to be broken up into multiple items such as the Local Groups, SQL Saturday Groups, PASS, twitter, online help in various places, etc,etc,etc.  There are a lot of pretty cool people out there helping others.  It is worthwhile to get involved with the community.

7.  SQL Server:  It should be painfully obvious that a SQL Professional would be thankful for the platform in which s/he works.  Without SQL Server, I guess I might be a NoSQL admin or maybe a MySQL admin or maybe even an Oracle admin.  But the fact remains that I work with SQL Server.  It gives me learning opportunities and I enjoy it.

6.  The ability to Learn:  This goes hand in hand with item 7 in the list.  Learning helps keep one sharp.  Learning can be fun and exciting as well.  With technology we are afforded the opportunity to learn something new on a very frequent basis.

5.  Employment:  No matter the grumblings one may be able to toss around about a job, it is good to have one.  It is good to be employed no matter the state of the economy.

4.  Good Friends:  Sure there can be bad friends out there.  It is nice to be able to shoot the breeze about unimportant topics with other people.  Friends can be a nice support system.

3.  Family:  A step above friends should be family.  For me, this is not immediate family, but extended family.  The people you should be able to rely on in really tough times with more personal problems than you may want to discuss with a friend.  These are the people that you sometimes long to see, can visit and then return to your own abode.

2.  My children:  My children bring me great joy every day.  I am thankful for the opportunity to have five children in my home and to provide for them.  I am thankful for the opportunity I have been given to try and teach them while watching the grow.

1.  My wife:  She is the person that helps me keep going when times are tough.  She has more faith and confidence in me than I do.  She is the glue for our family.  I have been married to her for 15 years and it just gets better with age.

Sure, number 10 may have been a bit sarcastic, but the remaining nine are serious.  We all have things that make us thankful or at least make us think about being thankful.  What do you have that you would like to share?  Sometimes it is just the opportunity to sit and play a game on the Wii with the children, sometimes it might be eating a drumstick while watching some pigskin.  Join the party and let us know what you think.

Defensive Db Programming Chapter 09

Comments: No Comments
Published on: November 22, 2010

It is down to the final two chapters of the book by Alex Kuznetsova.  Check out the previous chapter here.  The review of this book is certainly taking longer to produce than I had planned.  However, I think you find that the book is still worth it and that this little series is also worth it.  I hope that the spread of this series is at least getting to a few more people here and there and that more people may get a copy of “Defensive Database Programming”.

This chapter is about concurrent queries and Isolation Levels.  I think these seem to be a hot button of late – at least I have been hearing more and more talk about them than in the near past.  Alex starts the chapter with a stark realization.  He states:

Even the simplest SELECT, against one table, may retrieve incorrect results when the base table is being modified at the same time. (p. 297)

Think about that statement for a bit.  I imagine it won’t take you long to realize that it is true.  I am certain that you have come up with several examples that underscore that statement.

Alex then progresses through Isolation levels giving a demonstration of what will happen under the described circumstances.  This chapter is not intended to explain the differences between the Isolation levels, but merely to demonstrate what could happen – as has been the theme throughout the book to this juncture.  These examples that Alex uses first describe a theoretical problem and then he reproduces the theoretical problem with real world scenarios.

Alex also outlines in this chapter some considerations for choosing the correct Isolation level for your business needs.  Pros and Cons are weighed and usefulness of the Isolation level is discussed.  Alex also offers up the notion of using a query hint to issue a table lock when querying the database.  Due to the reciprocal effects Alex also states that it is almost never really an option.

Alex goes the extra mile in this chapter by discussing how we can minimize deadlocks.

In the end Alex makes a recommendation as to which Isolation level he would use.  Check it out and see for yourself.

Defensive Db Programming Chapter 08

Comments: 1 Comment
Published on: November 11, 2010

We are slowly getting to the end of this book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.  We are now discussing chapter 8 and it is a good chapter.  This chapter delves into Error Handling.

While the use of TRY…CATCH certainly is the best way to handle errors in T-SQL, it is not without difficulties. (p. 259)

In this chapter, Alex discusses some of the problems associated with error handling and demonstrates how to resolve some of those problems.  We will also see how to use XACT_ABORT.

The first key to error handling is to prepare for unanticipated failure.  That is the crux of error handling.  We anticipate certain behaviors and code for that, anything else needs to be handled through error handling so we can see what has happened.  Code sometimes fails, and queries against the data can also fail on occasion – we need to plan for those failures.

When dealing with data modifications there are occasions when using explicit transactions is necessary due to the nature of the data modification – we would want to ensure that any and all changes to the data were rolled back.  There are other times when we would want to ensure that processing halts immediately when an error is encountered.  In such a scenario, XACT_ABORT comes in useful.  Note, however, that this setting is probably better set explicitly ON or OFF depending on the needs.

A recommendation in this chapter is to use client-side error handling for all but the simplest of error handling.  Error-handling is far more robust in other languages than it is in TSQL.  This is a recommendation that I support.  Error handling in TSQL has become better over time (i.e. with the addition of the TRY…CATCH) and is quite useful for some degrees of error handling.  One particular realm where error handling in TSQL is lacking is in the area of Code-Reuse.  The TRY…CATCH must be rewritten for each stored procedure for which you wish to enable error handling.

As always, this chapter covers some of the gotchas of error handling in TSQL and gives examples on how to implement as well as circumvent some of the problems.  Check it out!!

Blogger Awards

A few days ago I wrote about an election that was taking place (ok so it was a week ago at this point).  That election was the SQLServerpedia Awards.  My first post on that election can be found here.  I had wanted to write another post about the election and make a whole bunch of campaign promises – to help make it feel political.  One big difference is that I was going to run a positive campaign and just talk about what I would do if I were to win.

Well, I won in one of the categories and I never wrote that campaign promises post.  So, I will just tell you what I would have promised and then maybe do some of those things.

  1. I will display the trophy in my cubicle at work
  2. I will support my fellow bloggers as much as I can just as I have done in the past.  There are some exceptional bloggers out there.
  3. I will continue to work with SQL Server
  4. I will continue to blog!!!!
  5. I will also finish my series on the book review for which I won.  Then I will progress to the next book or two and write reviews for them as well.
  6. I will continue to provide posts to SQLServerPedia

Thanks to all who voted for me.  I appreciate the support.  I recommend checking out the other books in the Book Review category.  I also recommend that you read the Blogs of the other people that were nominated for the SQL Choice Awards/ SSP Awards.

In short, this award is great and I really appreciate it.  In the grand scheme of things I am happy to have received it and rest assured that it doesn’t change my blog or me – except that I will post a badge on my blog.  We also had many excellent DB professionals and bloggers who won in various other categories.

Thanks SQLServerpedia for the awards and putting on these awards for the community.

November S3OLV Recap

Categories: News, Professional, SSC, SSSOLV
Comments: 2 Comments
Published on: November 9, 2010

Last Thursday we had the monthly meeting for our local PASS chapter.  I would normally try to get the recap out a bit sooner.  This month, I intentionally delayed the recap.  We had a plethora of things going wrong at this meeting that it left the leadership a bit befuddled.

We did get to participate in a great presentation – let’s take nothing away from Steve Jones from SSC.  He presented to us on the Modern Resume and he was a trooper about it.  For me that is a hallmark of somebody that is a seasoned speaker, is humble, and has leadership skills.  I think these skills tie together rather nicely and they manifest themselves pretty well when things don’t go as planned.

I appreciate all of those who attended virtually and in person.  I am thankful to Steve for taking time and altering his travel plans to adapt for us so we could hear him speak.

As a result of this meeting – look for changes to be coming from the S3OLV User Group.  Charley and I discussed this on Friday and decided to do a few things differently.  We have to discuss further how to go about some of these changes.  As a preview, look forward to the following:

  1. Site Change – this will help us better advertise and stay on top of the updates (something I have been doing very poorly at over the past couple of months).  With the change, we will be exploring the use of a Blog to manage the site along with allowing members to post articles to the Blog.
  2. Newsletter – we want to get more info out there.  One possible item to be included in the newsletter would be a Member Spotlight.  We are working on other ideas to go with the Newsletter.
  3. We will also be changing the invite and reminder process.

We really feel that these changes will make a difference for our group.  We also realize that these changes will take more time and more planning – but they are for the good of the group.

Let us know what you think!!

S3OLV Nov Reminder

Categories: News, Professional, SSC, SSSOLV
Tags: ,
Comments: No Comments
Published on: November 3, 2010

This is a last minute reminder about the monthly S3OLV User Group meeting.  It is being held on Nov. 4th, the first Thursday of the month rather than the second Thursday of the month.

You can find more information about it in my first announcement for the meeting.

I hope to see as many of you there (online or in person) as possible.

Defensive Db Programming Chapter 07

We are here again after another …um long lapse in time … and ready for another episode in this series.  Today we get to talk about chapter 7 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Advanced Use of Constraints

This chapter ties in well with chapters 4 and 6.  You may want to review those chapters as you read this chapter.  There were problems raised in those prior chapters and now they are being discussed at length in this chapter.  First order of business is to revisit the ticket tracking system that was discussed in chapter 4.

Ticket Tracking

The system was left with business rules implemented via triggers in chapter 4.  We are now going to explore using constraints in lieu of the triggers to enforce the business rules.  For sake of testing, the environment should be reset and a new environment built.  Alex provides all of the scripts to do so.  As Alex progresses through this example he lays out the assumptions and some of the problems with those assumptions.  Through a combination of Indexes and constraints.  Another item that was implemented was the use of ON UPDATE CASCADE to help ensure the business requirements could be met.  Alex talks about the implications on performance and the reasons for implementing the cascade update.  Make sure you read this chapter to find out more.

Inventory System

There is a substantial amount of discussion about a new example in this chapter.  That discussion revolves around the Inventory System and several use cases.  I recommend reading the chapter.  I am not going to discuss it beyond that here in this post.  There are a great collection of scripts to run through the example.

Conclusion

In this chapter Alex shows us how a combination of constraints can enforce complex business rules.  The use of constraints should be included as a viable option when trying to enforce business rules.

Once again, go get this book and read it.  It is well worth the read.

DB Maintenance

Comments: No Comments
Published on: November 2, 2010

About a month ago I read a post by Brad McGehee (Blog | Twitter) concerning a checklist for SQL Server.  It seems to be a rather comprehensive list.  If you don’t have some sort of maintenance checklist yet, I would recommend that you shoot on over and check out his list.  In addition to checking it out, I recommend that you make suggestions of other items to check when doing maintenance.  In addition to getting a maintenance routine setup, I would also suggest that a way to automate as much as possible be explored.

You can find the list here.

«page 1 of 2








Calendar
November 2010
M T W T F S S
« Oct   Dec »
1234567
891011121314
15161718192021
22232425262728
2930  
Content
SQLHelp

SQLHelp


Welcome , today is Friday, April 28, 2017