Writing Better Queries Presentation Materials

I delivered a webcast for the PASS DBA Virtual Chapter yesterday. It was called Tips & Tricks for Writing Better Queries. I really appreciated everyone who made the time to attend. I hope you found it to be time well spent.

As promised, here is a copy of the slide deck and demonstration code I used during the session. Please let me know if you have any questions.

The presentation was recorded and should be available on the PASS web site within a few days.

Related Posts

How To Write Better Queries WebCast

One character. That’s all it takes; just one character in a Transact-SQL query can make the difference between an index seek and an index scan.

Transact-SQL is easy to learn. But to truly get the best performance from your SQL Server you must have a good understanding beyond the basics of syntax. To become a Transact-SQL Superhero, you must be aware of some of the nuances of the language and how little things can affect your queries.

PASS DBA Virtual Chapter LiveMeeting Event

On Wednesday, 23 February, 2011, at noon Eastern Standard Time, I’ll share some of the lessons I’ve learned during my years of working with SQL Server. We’ll discuss indexing, stored procedures, triggers, and even user-defined functions.

I hope you’ll join me for this free webcast.

Related Posts

A NULL Is Not Unknown

Like it or not, NULLs happen. I don’t think that’s I’ve ever seen a production database that didn’t have NULLs.

Waiter, there's a NULL in my soup.

I’m not arguing whether or not NULLs in a database are good or bad. That discussion ranks up there with religion, politics, and naming conventions. My point is that NULLs exist. They are a fact of life and we must understand them conceptually before we can handle them programmatically.

So, What Is A NULL Anyway?

Let’s start with a definition. NULL is a convenient way to represent the absence of a value. It is not zero in the case of numeric columns. It is not a zero-length string in character columns. It is most assuredly not 1900-01-01 in a date column.

A NULL means that no value has been supplied. There is no value. There is no string; there is no number; there is no date. There is only the absence of a value.

But Doesn’t NULL Mean An Unknown Value?

It’s true that we don’t have an actual value for NULL. But that doesn’t necessarily mean the value is “unknown”. Let me illustrate with an example.

Consider a web site where pet owners make appointments with a Pet Cosmetologist. (I know; just go with it.) The web site asks the pet’s name, species, etc. One of the questions is the pet’s gender. For some animals, this is a very straightforward question. For other animals, answering that question takes uncommon skill.

The web developers recognized that some pet owners may not know Puddle’s gender so they provided three options in the drop down list: Male, Female, and Unknown. That should handle most every case. Dog owners can enter Male or Female as appropriate. Owners of kittens can enter Unknown.

I'm just not myself without a mani-pedi.

Gender is not a required field on the web site. The owners of the Pet Boutique don’t want to turn away an appointment request just because the pet’s owner doesn’t answer a question. Thus, owners of kittens who are embarrassed by their sexing ineptitude can leave that answer blank.

See the difference? When the pet owner enters Male or Female, we know the gender of the animal. If they enter Unknown, we have a value provided by the owner. We don’t know whether to expect a boy or girl, but we have been given an answer to the question.

If the non-required question is left blank, we may end up with a NULL in the database. So NULL is the absence of a value altogether. It’s a question unanswered. Of course, the web developers may default to “Unknown” but that, in itself, may be misleading since it was not provided by the user.

Next Up: Nullology 101

If NULLs permeate our databases, it’s critical that we understand their affect. For example:

  • What happens when we sum a column that contains NULLs?
  • What happens if we concatenate strings that contain NULLs?
  • Can a NULL equal another NULL?

I’ll tackle these questions and more in the next post.

Related Posts

Using PowerShell To Search The Event Log

The “30-minute DBA” was operating in a highly effective mode. He’d automated most everything that could be automated. The first half hour of each day was spent reviewing the automated reports that landed in his inbox over night, taking actions as necessary. That’s why he’s paid the big bucks and gets to spend the balance of his time playing and learning.

“I’m convinced that he actually works 30 minutes a day and the rest of his time is spent playing around with cool SQL Server things in his test lab.” That’s what another DBA told me about the 30-Minute DBA.

Searching The Event Logs

There are a lot of options for automation. PowerShell has become one of my favorites. For example, PowerShell can be used to peek into the Windows Event Log, searching for anything of interest to you. I’ve already written about one way to sift through the events; now I’ll share a few more options.

To examine the Application Event Log, invoke the following cmdlet in PowerShell

Get-EventLog Application;

You can narrow the results to only those entries that are of type Error using the EntryType parameter.

Get-EventLog Application -EntryType Error;

You can further limit the results by looking at only the past 24 hours.

Get-EventLog Application -EntryType Error -After (Get-Date).AddDays(-1);

Using the Message parameter, you can search for specific words or phrases in the error message.

Get-EventLog Application -Message "*failed*";

If a specific event becomes a sporadic problem, you can search for the event using the Where functionality.

Get-EventLog Application | where {$_.EventId -eq 1309} | Format-Table -autosize;

Using these techniques, you can easily set up your own automated Event Log review process and insert the results into an email, a spreadsheet , or a database table for review.

Question:

  • What techniques are you using to automate your daily tasks?
  • What do you wish you could automate?

What SQL Server Service Pack Do I Have Installed?

If you’ve used SQL Server for a while, you’re probably familiar with the @@version function. @@ variables are system variables that are automatically populated by SQL Server. Selecting the @@version function in a query window produces the following results on my system.

SELECT @@VERSION;
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Developer Edition on Windows NT 6.1 <X86> (Build 7600: ) (Hypervisor)

In the results, you can easily see that I’m running SQL Server 2008 R2  Developer Edition on an Intel X86 processor. Notice that the results also contain operating system information as well. In this case, it shows that SQL Server is installed on a Windows NT 6.1 machine.

This can cause confusion for many who are unfamiliar with the @@version variable. Frequently, the service pack of the operating system is placed toward the end of the text. It’s easy to assume that this is the service pack level for the SQL Server, after all we are asking SQL Server for the information. That’s not the case. It’s the operating system system pack level.

The ServerProperty Function

To determine SQL Server’s service pack level, use the built-in SERVERPROPERTY function with appropriate parameters – EDITION, PRODUCTLEVEL, and PRODUCTVERSION.

SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion;

In another post, I’ve shown how the SERVERPROPERTY function can be used to find the location of the SQL Server ErrorLog file.

Not familiar with terms like RTM, Service Pack, GDR, and CU? I’ve written about the terms in another post.

Enjoy.

When Were The Statistics In SQL Server Last Updated?

When SQL Server receives a new query, the Query Optimizer undertakes the task of identifying a good plan to resolve the query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested. Dr. DeWitt did an amazing job of explaining this process in understandable terms at the 2010 PASS Community Summit.

Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.

If the statistics are outdated and do not accurately represent the distribution of values in the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in the optimizer not using an index when it should, or using an index when it would be more efficient to scan the table. Statistics also influence the logical join order and physical types selected.

That’s why it is crucial that the statistics be updated regularly. How often? Well, that, of course, is going to depend on the data being stored, the frequency of updates, inserts, and deletes to the table, etc. It could be nightly; it could monthly. It just depends.

So, how can you tell when the statistics where last updated for an index?

The following query demonstrates this in SQL Server 2005/2008. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.

SELECT
   t.name AS Table_Name
   ,i.name AS Index_Name
   ,i.type_desc AS Index_Type
   ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM
   sys.indexes i JOIN
   sys.tables t ON t.object_id = i.object_id
WHERE
   i.type > 0
ORDER BY
   t.name ASC
   ,i.type_desc ASC
   ,i.name ASC

In SQL Server 2000, a similar query can be run.

SELECT
   o.name AS Table_Name
   ,i.name AS Index_Name
   ,STATS_DATE(o.id,i.indid) AS Date_Updated
FROM
   sysobjects o JOIN
   sysindexes i ON i.id = o.id
WHERE
   xtype = ‘U’ AND
   i.name IS NOT NULL
ORDER BY
   o.name ASC
   ,i.name ASC

These queries can be useful while troubleshooting and diagnosing performance-related issues. Sometimes, it’s as simple as outdated statistics.

The PowerShell CookBook

“A script club.” That’s what a friend of mine said when he was telling me how he’d learned the basics of PowerShell. I did a double take. “You had a user group meeting at a strip club?!?!” He laughed and clarified, this time enunciating more clearly.

cutting board image

Last week at the PASS Community Summit in Seattle, I delivered a session called The PowerShell CookBook. In it, I demonstrated some scripts that I use when auditing SQL Server instances. I showed how to programmatically collect a list of SQL Server instances and then iterate through each instance to gather information about its configuration parameters, databases, and jobs.

As promised in the session, I’ve made the scripts available for you to use and tweak to suit your own needs.

If you were in the session, thanks for coming! I hope you found it worthwhile! After the session, someone asked if I’d be willing to deliver the session remotely to their user group. The answer is yes; just contact me using twitter, email, or a comment to this blog and we’ll work out a time.

See you next time!

Follow

Get every new post delivered to your Inbox.

Join 36 other followers