Michael J. Swart

RSS Feed
RSS Feed
Email Me
Email Me
About Me
About Me

March 18, 2014

A Primer on Locks, Blocks and Deadlocks

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 8:50 am

Lock, block and deadlock are three related terms in SQL Server. They have a specific meaning and are often confused with each other. I hope to clear them up here.

(I’m using a new visual format. This means you RSS readers will have to come by and see it in person.)

But There’s One More Thing…

As long as I’m experimenting with visual formats, check out the same content in another medium.
The Locking Primer Presentation

More On This

This was just a primer. The best resource page remains Kendra Little’s Locking and Blocking in SQL Server.

March 16, 2014

T-SQL Tuesday #52 Round-Up: Argue Against a Popular Opinion

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 1:52 pm

T-SQL Tuesday LogoThere were thirteen excellent posts this month. (See the invite post here). I loved reading every one of them. It gave a really good perspective on topics that are typically seen as cut and dry. Thank you to each one of the bloggers for putting in the time to participate this month. Here are the posts, each with a mini-review.

Michael J Swart
(@MJSwart)		 I Don’t Mind SELECT * Sometimes

The opinion:
The popular opinion is that “SELECT *” should always be avoided. I argue that there are some cases where there are no downsides.
Did I buy it?
I may be a little biased, but I totally bought it. Truer words were never spoken.
Boris Hristov
(@BorisHristov)		 Only My Technical Skills Matter

The opinion:
The popular opinion is that only technical skills matter. Boris argues that the most effective professionals develop other skills (like soft skills).
Did I buy it?
For sure. I would also add to his post. It’s easy to agree that soft skills matter. But I’ve recently learned that (holy crap) do they ever matter!
Tech skills are important.
Communication skills can come with enough practice
But tech skills + communication skills + wisdom are a lethal combination.
Russ Thomas
(@SQLJudo)		 Eye Heart You Dee Effs

The opinion:
There are a lot of UDF-haters out there in SQL land. Not Russ, he loves them.
Did I buy it?
What say I? I say aye! Well done Russ. Totally got into the spirit of this month’s topic. BTW, he gets the Michael J Swart “Best Article Of The Month” award.
Bob Pusateri
(@SQLBob)		 Why I Hate Row Compression

The opinion:
Row compression has been an extremely popular feature since its introduction. But Bob warns us of the risk of using it as spackle for sloppy data modeling.
Did I buy it?
Sloppy design causes future problems. I buy that. Row compression can enable sloppy design. I buy that. The Spock in me says row compression can cause future problems. But the Kirk in me has trouble accepting it.
Thanks for writing the most though-provoking article.
Ken Fisher
(@sqlstudent144)		 Keep your head down!

The opinion:
Ken takes a look at people known for their collaborative character and he argues against that behavior with the more isolationist advice: “Keep your head down!”. The sarcasm is called out explicitly and so Ken is actually promoting more collaborative behaviors.
Did I buy it?
His post’s thesis argues with the popular trend of more collaboration. As a blogger myself, thumbs up! It’s was a really well written article, the light-hearted style makes for a good read.
Rob Farley
(@rob_farley)		 Scans are better than Seeks. Really.

The opinion:
Rob and I had a great discussion in the week leading up to #tsql2sday. He wants us to believe (with no irony) that scans are better than seeks.
Did I buy it?
After reading the article, yes, I buy it. It turns out there’s a lot of subtlety when talking about seeks and scans. Especially when seeks perform RangeScans or when scans operate on filtered indexes.
Brent Ozar
(@BrentO)		 Stop Tuning with Wait Stats Percentages

The opinion:
It’s all in the title. Wait statistics are a popular method of tuning SQL Server. Brent argues that you shouldn’t pay much attention to the reported percentages. Or at least not without some careful context.
Did I buy it?
Sure did. My own take is that wait stats are useful when measured against representative load, when there’s something to tune, and when you look wait rankings over percentages.
Jeremiah Peschka
(@peschkaj)		 What Use is an Average?

The opinion:
I invited Jeremiah to contribute this month. Jeremiah has a writing style that doesn’t always show in his more technical blog posts. I was not disappointed. The popular opinion he seems to be arguing against is that the “average” statistic is a worthwhile measure (when applied to query durations for example). He wants us to believe it’s not always enough.
Did I buy it?
Yep, it’s not always enough. And he gets creative with his citations. It was crazy enough Jeremiah… it was crazy enough.
Steve Hood
(@SimpleSQLServer)		 Why worry about CXPACKET

The opinion:
The opinion Steve Hood tackles is that the wait type “CXPACKET” is a problem and should cause worry. He argues that it just indicates parallelism which is not necessarily a problem.
Did I buy it?
Yes, In the particular case of CXPACKET, it seems that enough people are tempted to treat the symptom rather than the disease. Steve’s article addresses that.
Steve Jones
(@way0utwest)		 Arguments

The opinion:
Steve Jones argues against “Never Turn on Auto Shrink”.
Really?
Really Really?
Did I buy it?
I’m a believer. He gave not one but two scenarios where I would use autoshrink as well.
Mike Fal
(@mike_fal)		 Stop depending on “it depends”

The opinion:
So this is interesting. Mike Fal is asking us to get off the fence. It’s popular for DBAs to fall back on “it depends” when cornered for an answer. Mike wants us to “stop it”.
Did I buy it?
Yes, you bet. One of the reasons I picked this topic (“argue against a popular opinion”) is to explore exactly what things depend on. One of the best written articles in this list.
Jes Schultz Borland
(@grrl_geek)		 Why Back Up System Databases?

The opinion:
Arguing against public opinion. Jes tells us that that could be the title of her autobiography. The opinion she argues against is that it’s super important to back up your system databases. She thinks it’s just not as important as others want us to believe.
Did I buy it?
Yes, Jes makes a good point. She raises something important. Whether you back up your system databases or script out something to help rebuild a server from scratch. It should be practiced at least once.
Tamera Clark
(@tameraclark)		 Don’t Be That Guy

The opinion:
Tamera takes a different tack. Rather than an opinion, she argues against Mr. Popular asking us to not be that guy.
Did I buy it?
She gives 10 pieces of advice to a particular target audience. It’s great advice and it’s a good piece of writing. My only worry is that not enough of her target audience will recognize themselves as the target audience.

March 10, 2014

I Don’t Mind SELECT * Sometimes

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 11:50 pm

T-SQL Tuesday LogoI’m participating in this month’s T-SQL Tuesday. The host – handsome genius – has asked us to argue against a popular opinion. So the opinion I’m tackling today is that SELECT * should always be avoided.

I’ve heard that a lot. For example, it seems to be a popular question on Stack Overflow. The consensus is that SELECT * is discouraged in favor of SELECT col1, col2, col3 where you select the columns you need and no others.

I’m going to look at some of the reasons that are given to support this advice. I’m also going to qualify when it might not matter so much.

No not always, only a Sith deals in absolutes.

Unused columns cause excessive load on the database.
Touché. This is true, SELECT * often causes SQL Server to use the clustered index because it’s usually the only covering one. This can waste an opportunity to use a more efficient index.

However, even when us developers follow the “Query only what you need” advice, our SELECT queries often do in fact need every single column. In this case, SELECT * is fine.

Columns are returned in an unknown order
That’s true. The fear is that an application will contain a bug when it depends on a column order that is incorrect. Well simply don’t do that. Most applications are able to retrieve row values in the record set by column name rather than column position. Identify columns by name. Then the column order doesn’t matter.

You can’t search code for the use of a column
That’s an interesting one. Say I’ve got a USERS table with a Title column. This column is getting dusty and so we plan to deprecate it and ultimately remove it.

It would be great to search our code base and find where this column gets used. Presumably the code that contains SELECT * would be missed in the search but any code that contains SELECT ... Title would be found.

But how big a problem is this? The only trouble is when an application uses SELECT * and then after it gets the results, it takes that data set and looks for the column Title by name. If that’s the case, then you will probably find that instance of the Title string in your code search.

The development effort is not really impacted (because SELECT * doesn’t need to change). The testing effort is not impacted terribly either. I can’t think of a scenario where Ctrl+F column name is the only thing standing in the way of a bug getting into production. If you can think of one, then it’s probably not the only quality issue that needs attention.

TL;DR

I don’t mind SELECT * when I’m using every column and when I don’t depend on the order of the columns.

March 3, 2014

T-SQL Tuesday #052: Argue Against A Popular Opinion

Filed under: SQLServerPedia Syndication — Tags: — Michael J. Swart @ 10:07 pm

T-SQL Tuesday Logo

Update See the round up post here.
I was asked to host T-SQL Tuesday this month, T-SQL Tuesday #52. This marks my second time hosting and I’m happy to do it again.

Your writing assignment for March 2014 is to

pick a popular opinion and argue against it.

… or at least qualify it. Given any issue, people drift to two kinds of crowds. There’s the “it depends” crowd and there’s the “never ever” crowd. We tend to fall in with one crowd or the other.  This month, I want you to find an “never ever” issue and argue for it (or conversely, find a “always” issue and argue against it).

I wonder how this month will go. It takes guts to go against common wisdom.

You don’t necessarily have to argue against a universal opinion, but it should at least be popular. I think that your choice of opinions is practically limitless:

  • Bob Duffy had a list of 10 interview questions that annoy SQL professionals. With some great topics there including GUIDs, Cursors, and heaps.
  • Google results for “SQL.Server should.never”
  • Fair’s fair. Here are the results for “SQL.Server should.always”
  • Does anyone want to have a shot at redeeming Microsoft Access?
  • Foreign Keys, SchmoreignKeys.
  • Check it out. SQL Server supports varbinary(max)! Ideal for json documents and xml documents (or both!)
  • Shrinking databases and/or log files (because of the fragmentation! God save us all from fragmentation!)

Here’s a little secret. This month’s topic is not for you. It’s for the readers. It’s a chance for you to give them a more nuanced understanding of a topic that they may not have given a lot of thought up until now. I’m a little curious myself.

The rules are the same as always:

Follow These Rules

  1. The post must go live on your blog between 00:00 GMT Tuesday, March 11, 2014 and 00:00 GMT Wednesday, March 12, 2014.
    In other words, set your sql server date, time and timezone properly and run this script:

    IF GETUTCDATE() BETWEEN '20140311' AND '20140312'
	SELECT 'You Can Post'
ELSE
	SELECT 'Not Time To Post'
  2. Your post has to link back to this post, and the link must be anchored from the logo (found above) which must also appear at the top of the post
  3. Leave a comment here (below) or I won’t be able to find your post.

That’s it! Good luck! Can’t wait to see what you have in store.

Your humble host,
Michael J. Swart

Powered by WordPress