Michael J. Swart

October 28, 2010

3 Problem Solving Resources That Make You Look Like A Genius

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:45 pm

developer morphing into einstein

Lookin' smarter!

Okay, so I consider myself relatively bright. With enough effort I can usually fix nine out of ten database problems that get thrown at me. But if (actually when) I get stuck, there’s a bunch of resources I rely on.

Maybe you’re in the same boat as me. Sometimes a technical problem comes along and there’s no one left around to ask for help. The buck stops with you. Either you crack this problem it or it doesn’t get cracked. Often the people who want an answer or solution don’t care where you get it; They just want to clear away an obstacle that’s stopping them from doing what they want to do.

So here are my three favorite internet resources in no particular order.

Google

Okay, this is kind of an obvious one. But you probably don’t have to think too far back to remember when someone asked you a totally google-able question.

But I’m adding Google in this list because I’d be lost without it (plus it also expands this list to three instead of two). It’s still valid though. Even if all you did is punch in an error code and examined the first solution that Google reports, you still look good.

Twitter + #sqlhelp

Check it out.

I asked a question on Twitter the other day and got great responses almost immediately. I showed a friend at work (Hi Scott) these answers and he said “Huh. Twitter is a great resource if you have enough followers.” *

But that’s just the thing! I explained that I don’t have many followers at all. And you don’t need any followers yourself either. The trick is to add the #sqlhelp tag to your tweet and bingo: there is often a large group of professionals just waiting to help. What’s in it for them? I’m not sure. My guess is that knowing they’ve helped someone out is a great feeling. (Altruism FTW!)

Any way, the success of #sqlhelp is a direct result of the active Sql Server community out there. The hashtag #sqlhelp lives and dies based on participation level. There’s probably some unknown threshold of askers and answers that’s needed for the resource to remain useful. Whatever it is #sqlhelp is way beyond that! I counted about 150 questions in the last 24 hours.

For more info, Brent Ozar covers this method in depth at his site his post. Or just go straight to Twitter’s #sqlhelp page.

Stackoverflow + sql-server tag

Check it out.

So if you’ve got a question that needs some explaining and if your question doesn’t fit into the 140 character limit that Twitter enforces, you’ve got stackoverflow.com.

Newsgroups have been around forever. And technical online forums have been around for a while too. But after discovering stackoverflow, I’ve never needed any other sites (Once you go stack…).

And again, it’s the high number of askers and answerers that make this site so useful. No decent question lasts unasked longer than 20 minutes. And more often than not, your question has already been asked.

Did I Miss One?

So tell me where you look. Is there a favorite internet resource of yours that I missed?

I preemptively stick my tongue out at anyone who mentions MVP newsgroups.

* ~ By the way, that’s Scott in the photo who agreed to sit for the photo (He’s the one on the left) Thanks Scott.

October 11, 2010

Updating Statistics Helps, But Not For The Reasons You Think

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 8:12 pm

T-SQL Tuesday Logo This month it’s Sankar Reddy’s turn to host T-SQL Tuesday. I wish him luck! I know I had a blast last month. This month we’re busting SQL Server misconceptions wherever we find them.

So today I want to explain something about the practice of updating statistics as a technique to improve or maintain performance. It works and it works well, but often not for the reasons you think and I’m going to explain a simpler equivalent technique.

The Story

So you’ve been given the job of finding out why a particular stored procedure is performing poorly. It’s a complicated query plan and one of the first things you notice is that there’s hundreds of thousands of rows coming out of a particular table. After looking closer you figure out that the estimated number of rows is only supposed to be around thirty!

Remember that estimated row counts are based on what the database knows about the data and that info is stored in objects called statistics. As the table grows, these statistics can get out of date. So you try:

UPDATE STATISTICS dbo.MyTable WITH FULLSCAN

which solves the problem. (Good job, take a bow!)

Why It Works

Statistics are used to get an idea of how data is distributed. Based on the data distribution, SQL Server can make better choices on how it chooses query plans. If tables change and stats are out of date, the chosen query plans can be less than optimal. And bad plans have a bad habit of performing orders of magnitude worse than optimal plans.

Why It Really Works

I’ve found that out-of-date statistics can be responsible for bad query plans. But more frequently, I see that the parameter values that are used to compile the plan have just as big an effect.

SQL Server doesn’t just use statistics to estimate the number of rows it’s going to retrieve from a table. It also uses variable values and constants found in the query. And in the case of variables, it always uses the first variable values that it sees when the query gets compiled.

So for example, If I’m looking up a person in the phonebook by last name. The number of people I find is going to depend on whether that last name is common like “Martin” or unique like “Jingleheimer-Schmidt”. Based on the search argument, different plans can get picked which affects database performance.

So even if statistics are already up to date, the act of calling UPDATE STATISTICS  can still have an effect. When you update statistics on a table, SQL Server throws out all the query plans that depend on that table (but see comments). And that query gets recompiled with the next parameter values that come along. And these may be different than the original values.

It’s the equivalent of a giving SQL Server a good thumping, or jolt.

So if you’re getting ahead of me, you can see that updating stats won’t always work, but I’ve seen others use it as part of a trial-and-error approach to query tuning. So that when it works sometimes, it becomes part of your mental toolbelt of tricks to try.

Alternative Technique 1

So you’ve used STATS_DATE and you know that statistics are up to date and you want to give SQL Server the same “thump” to build a new query plan without updating stats. You can use:

sp_recompile @objname='MyTable'

Or even more targeted:

DBCC FREEPROCCACHE (plan_handle)

But I would only recommend this for acute performance problems, it by no means addresses the root cause.

Alternative Technique 2

If you decide that SQL Server needs help in picking its plan consistently you can use something more permanent. Use query hints like

  • OPTIMIZE FOR UNKNOWN,
  • OPTIMIZE FOR @LastName = “Martin”
  • USE PLAN …

But this means you’re telling SQL Server you know better than it does. So make sure you understand every nook and cranny of your tables’ data distribution and query plans.

Conclusion

The misconception is that STATISTICS has a greater effect on query performance than it actually does. The query plans that SQL Server chooses depend on both statistics and compiled parameter values.

Try to get away from a trial-and-error method of query tuning. Learn how to look at the following before making a plan:

  • Find the query plan handle and query plan for a poor performing query.
  • Estimated row counts vs. Actual row counts
  • What parameter values were used to compile a query plan.
  • When statistics were last udpated for a particular index.
  • The data distribution (histogram) of statistics.

October 5, 2010

DBA humour

Filed under: Data Cartoons,SQLServerPedia Syndication,Tongue In Cheek — Michael J. Swart @ 9:44 pm

Data (Bass)

Data (Bass)

Data (Bass)

(Pun so intended)

Powered by WordPress