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!

Where Is The SQL Server ErrorLog File?

I seldom lose things; I just cannot find them as quickly as I’d like. This is true for keys, tools, and yes even ErrorLog files on SQL Server.

On the servers that I configure, I have a standard way of doing things. I set them up using some industry best practices and some standards that I’ve developed over the years. On those servers, I can find the ErrorLog file quickly since it is in a predictable place for me.

Using T-SQL To Find The ErrorLog File

In my consulting practice, I regularly work with SQL Servers that I did not configure. For those servers, I must discover where things are. One technique that I use is to ask SQL Server itself where things are. For example, the following T-SQL query will return the location of the ErrorLog file.

SELECT SERVERPROPERTY(‘ErrorLogFileName’);

It will return something like the following:

E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

This technique is particularly useful when I’m examining the ErrorLog in a PowerShell script. I blogged about that The Power Of RegEx in PowerShell.

Checking The Event Log With PowerShell

Good administrators work really hard so that they don’t have to work really hard. Well, that’s an oxymoronic statement if I’ve ever written one. So what do I mean?

Smart administrators are willing to spend time automating as much of their routine work as possible. Why go through the hassle of manually checking a long list of servers on a periodic basis when you can configure a scheduled task to do it for you and report only the exceptions. Smart administrators know that the effort to set up these processes on the front-end will pay dividends on the back-end, freeing them up to handle the exceptions or to play with the latest and greatest technology.

Checking Event Logs With PowerShell

Let’s take a simple example using PowerShell.

It’s a good practice to regularly review the Windows Event Log on the your servers to make sure things are going smoothly. You could make a note to remind you to manually connect to each server and sift through its event log. But that would be cumbersome and time intensive.

That’s where PowerShell can help. The following one-line PowerShell script will check the Event Log on a server, looking for only the Errors that have occurred within the past week. It doesn’t report the informational and warning entries, only the errors.

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

You can schedule this to run daily, weekly, or some other interval by changing the -7 parameter on the AddDays method.

Emailing The Results

“That’s great, but it’d be even more convenient to have the result automatically delivered to my inbox.” You can do that.

$smtp_server = “mysmtpserver”;
$to = “joew@myemail.com”;
$from = “administrator@mycompany.com”;
$subject = “Event log from my server”;
$body = Get-EventLog Application -EntryType Error  `
-After (Get-Date).AddDays(-7);

send-mailmessage -to $to -from $from -subject `
$subject -body $body -smtpserver $smtp_server;

And there you have it: the beginning of a PowerShell script to automate a portion of your routine system checks.

Lightening Talk On Covering Indexes Ka-chow! Ka-chow!

“I’m a precision instrument of speed and aerodynamics.” That’s what Lightning McQueen tells Mater when they first meet in Cars. The ever astute Mater replies “You hurt your what?” I love that movie!

I often get a similiar response when I ask people about covering indexes. It’s not that aren’t bright, knowledge professionals. It just seems as though this topic isn’t very well known. And that’s a shame because queries that can be resolved using only a nonclustered index are some of the fastest queries you can run in SQL Server. I explain and demonstrate why in a Simple-Talk article.

I’ll also take up this topic in a Lightening Talk session at the 2010 PASS Summit. We’ll go from zero to covering indexes in 5 minutes.

If you’re attending the Summit, join me for this Lightening Talk. I’ll be the one who’s downing seven expressos right before the session so I can be sure to “cover” all the material in 5 minutes.

Follow

Get every new post delivered to your Inbox.

Join 32 other followers