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

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.

Removing Time From A Datetime Value

How much time would a DateTime have if a DateTime had no time?

Ok, forgive the bad pun. But the point remains is a valid one. How can we efficiently strip off the time portion of a DateTime value in SQL Server?

I’ve seen a several methods proffered on the Internet. Some are quite clever; others are not so clever. The latter includes converting the DateTime to a VarChar and using string manipulation techniques to loop off the time. That’s not going to perform well.

So what is the best way? Let’s find out.

Three Methods

I’m going to narrow down the field to only three methods for manageability. There may be other methods out there. If you know of one that’s not covered here and you think may be better, please let me know.

To test each case, I’ll employ the conversion 10,000,000 times to see which finishes the quickest. I’ll do this with the following code.

SET NOCOUNT ON
DECLARE @cnt INT = 1;
DECLARE @start DATETIME = GETDATE();
DECLARE @var DATETIME;

WHILE @cnt < 10000000
BEGIN
SET @var = [time removal option]
SET @cnt += 1;
END

SELECT @var;
PRINT CAST(DATEDIFF(millisecond, @start, GETDATE()) AS INT);

Cast To A Float And Back

First let’s look at a technique that casts the DateTime to a float, gets the floor of the resulting value, and then casts it back as a DateTime.

CAST(FLOOR(CAST(getdate() AS FLOAT)) AS DATETIME);

This option finished in a 16,256 milliseconds.

Adding A Date

The next option uses the DATEDIFF function to calculate the number of days since the beginning of time and then uses the DATEADD function to convert it back to a DateTime value.

DATEADD(dd,0, DATEDIFF(dd,0, GETDATE()));

This completed in 15,593 milliseconds, slightly better then the first option.

Converting To A Char And Back

Finally, let’s consider a technique that converts the DateTime to a Char of a specified format and then casts it back to a DateTime.

CAST(CONVERT(CHAR(11), GETDATE(), 113) AS DATETIME

As you may have expected, the conversion to a Char significantly slowed the process down. In fact coming it at 32,698 milliseconds, it took over twice as long as the second method.

Say, Do You Have The Time?

From the three methods I’ve considered, the second method which included using a DATEDIFF to count the number of days since the beginning of time slightly edged out the first method. Both significantly beat converting the DateTime to a Char string.

So, do you have a favorite method? If so, use the test script I’ve posted about and see how it performs. Let me know how it does.

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.

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.

SQL Server Locking & Blocking At DevLink 2010

Wow! Thanks to everyone who came to my SQL Server Locking & Blocking session at devLINK this morning! I’m completely humbled by the turnout!

Thanks for hanging around until the end, too! At the beginning of the session, I said we’d have 45 minutes to cover 90 minutes worth of material. Even so, I had in my mind that the session would last until noon. So when I finished at 11:55pm, I thought I had finished 5 minutes early. I didn’t realize until later that I was actually 10 minutes late! I hope it was worth your while.

As promised, here is a link to the presentation slide deck and the demo scripts (Query 1, Query 2). Feel free to ask any follow up questions in the comments area below. And I’d appreciate your feedback on the session in SpeakerRate.

Lots More SQL To Come

If you’re interested in more topics and sessions on SQL Server, the Nashville SQL Server User Group is hosting a SQLSaturday #51 in just a couple of weeks (August 21, 2010). There are over 30 great sessions presented by SQL Server MVPs, authors, and industry experts from around the country. And it’s completely free. Space is limited so register soon.

Thanks again and I hope to see you at SQLSaturday #51!

Writing Better Queries in Steel City

T-SQL is a very forgiving language. As long as you have the basic syntax of the statement correct, SQL Server assumes you know what you’re doing and it will gladly do your bidding for you.

But anyone who has written queries for very long will tell you that not all queries are created equally. Some return with results in under a second; we like those almost as much as we like bacon. Almost. Some take a few minutes to mull over the data and get back to you; that’s like fake bacon. And alas, some queries will run for eternity minus one.

So what’s the difference? And how can we go for the bacon and avoid the fake bacon and eternity minus one queries?

That’s the topic of a presentation I’m delivering at the Steel City SQL Server User Group meeting next week. If you’re in the Central Alabama area, I hope you’ll come out and join me.

Update:
The meeting starts at 600pm on Tuesday, July 20th, 2010 at
601 Beacon Pkwy, West Suite 106
Birmingham AL 35209.

When you enter the office park, there’s an office building immediately on the right, follow the short curve past it to the next building, it is the New Horizons one.

How to Tell When SQL Server Started?

finger_trap_2010_04_28.jpg

As a consultant, I’m regularly asked to diagnose and solve problems when I have incomplete or conflicting information given to me by the client. They don’t mean any harm, but different people in the organization have different perspectives and different memories. Sometimes it is difficult to know what is really happening.

When I’m working with systems like SQL Server, it is always good to gather as much information from the system itself as I can and not rely too heavily on someone else’s memory. Trust but verify as President Ronald Reagan once said.

When was the server restarted?

SQL Server maintains a lot of information about how it’s doing and when certain events ocurred. Much of this information resides in the Dynamic Management Views (DMVs) of SQL Server 2005 and 2008.

For example, let’s say we want to know when the SQL Server instance was last restarted. When can easily collect this information using a quick query. There are several other methods as well but this one is my preferred way.

SELECT
Login_Time
FROM
sys.dm_exec_sessions
WHERE
Session_Id = 1;

server_restart_dmv.jpg

Additional Resources

SQL Server Books Online has more information about this and other DMVs. Additionally there are some other great resources on the web.

And there are many, many more. Just use your favorite search engine and begin using the DMVs to make your job a little easier.

Question:

  • What’s your favorite DMV?
Follow

Get every new post delivered to your Inbox.

Join 32 other followers