Voice of the DBA

Tag: presentations

Common SQL Server Mistakes – Indexing Every Column

If one index helps speed up queries, than more indexes should help more, right? They do, but they also come at a price. Both in performance during data modifications (insert/update/delete), and in terms of space since each index must be stored somewhere.

I have never bothered to index every column of a table. Actually I’m not sure if I’ve indexed every column of any table. Perhaps that’s because I originally came from a dBase/Clipper/Foxpro environment and I had to manage every index manually. Adding too many indexes resulted in a lot of coding.

In SQL Server, each index is useful in two ways. When someone puts a filter in the WHERE clause, like this:

select
  CustomerID, TerritoryID, AccountNumber
from Sales.Customer
where CustomerID = 10

then an index on CustomerID will speed up this query. Instead of having to scan all rows of the table, the index on CustomerID can be searched, the correct row found. If this is a clustered index, then the data can be read. If it’s a non-clustered index, the server can retrieve the rowID and then go get the data from the clustered index without reading all the rows in the table.

In more recent versions of SQL Server, multiple indexes can be used. For example,

select
  CustomerID, TerritoryID, AccountNumber
from Sales.Customer
where CustomerID = 10
  and TerritoryID = 23

In AdventureWorks, there are indexes on both CustomerID and TerritoryID for this table. It is possible (with lots of data), that the optimizer might choose to scan the CustomerID index for all matching rows and then the TerritoryID index for matching rows, join those results together to get a set of rows for the overall query and read the clustered index for those specific rows. However that’s not something you can count on in SQL Server. Typically one index is used in many queries.

So why not index every column?

First, if the columns are large, like varchar(max), text, or varbinary(max) columns, then it doesn’t make sense to build large indexes unless you often query these fields. Even then, a full-text index is likely a better choice.

Second, each time you change data (insert/update/delete), then all indexes must be updated at the same time. This means that your write performance suffers, and that impacts the server read performance as well since resources are being used to perform those updates. The more indexes, the more work that has to be done in support of any DML statement.

Lastly, you typically find that most of the time you query a table based on 3-5 fields, and those are the best candidates for indexes. For transactional tables, this is typically the number of indexes that you want to put on each table. Reporting tables, or OLAP type tables, might have more indexes, but these are tables that typically receive mostly read activity, and rare write activity.

Which columns do you index? Pick those columns that often appear in your queries, and that are fairly selective. You can always query the missing index DMVs for help in choosing which indexes the optimizer things it might use.

September 29, 2010
Common SQL Server Mistakes – Multi Row DML Triggers

How often have you seen someone write a DML trigger like this:

create trigger mytrigger on Mytable for insert as

declare @id int
select @id = id from inserted

update xx set yyy = zz
where id = @id

return

There seems to be this common misconception that a trigger fires for each change in a row (insert/update/delete), and that’s not true. As noted in Books Online, triggers fire once for the insert/update/delete. Typically this is an implicit transaction for the statement. If you have multiple statements inside an explicit transaction, the trigger fires once for each insert/update/delete statement in the transaction.

That means that if I change two rows and I have the trigger above, I won’t get the behavior I expect from the trigger. Let’s say that I want to update my inventory table each time I change an order. Imagine that I have this in my orders table:

and this in my inventory table.

If I now write this trigger:

alter trigger orders_update_inventory on orders
  for update
  as

  declare @qty int
     ,    @product int

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – @qty
   where productid = @product

return

and execute this:

update orders
  set qty = 2
   where orderid = ’59CD85CE-984C-4D33-9E23-5F6159848277′

I will find that my inventory table looks like this:

That appears to work, but what happens if we execute this?

update orders
  set qty = qty+1
  where[CustomerID int] = 2

Then we find that the inventory is

In this case productID 1 has had its inventory reduced by 1, but not product ID 2. Why not?
When the trigger fires, there are actually this data in the tables:

Inserted
OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD…    2010-09-22 3              3           1
01B6…    2010-09-22 2              2           2

and deleted

OrderID OrderDate CustomerID int qty         productid
——– ———- ————– ———– ———–
59CD85… 2010-09-22 2              2           1
01B66E… 2010-09-22 2              1           2

However the trigger, in setting the variables to the result of a query could have picked either of the rows, but only one row. SQL Server doesn’t guarantee order without an ORDER BY, so either product ID could have been chosen. As a result, only one of the products had the inventory updated.

A proper trigger would look like this

alter trigger orders_update_inventory on orders
  for update
  as

  select @qty = a.qty – b.qty
    from inserted a
       inner join deleted b
         on a.orderid = b.orderid
  select @product = productid from inserted

  update inventory
   set onhand = onhand – ( a.qty – b.qty)
   from inserted a
       inner join deleted b
         on a.orderid = b.orderid
   where inventory.productid = i.productid

return

Triggers should always be written to handle multiple rows, using the inserted and deleted tables for joins instead of variables. Even if you always just update single rows, coding this way will prevent issues if there is a multiple row change.

September 23, 2010
Common SQL Server Mistakes – SELECT *
I’ve been trying to work on some new presentations so that I have a variety, including some spares, when I go to events. One of the topics that I think has some value, especially for .NET and sysadmin groups, is a list of common mistakes, how to fix them, and why they’re bad.
I was going to call this Common Developer Mistakes, but I’m not sure that would go over well at Developer events, and I see DBAs making these mistakes along with Windows admins.
I decided to build a series of blog posts as I work through the presentation to document some of the issues, and help me work through speaking points. Please feel free to comment.
SELECT * Is For Short Term Use Only
The first mistake that I often see in application code is that too often people write things like
```
SELECT * 
FROM Sales.Customer
```
.csharpcode, .csharpcode pre{font-size: small;color: black;font-family: consolas, “Courier New”, courier, monospace;background-color: #ffffff;/*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt {background-color: #f4f4f4;width: 100%;margin: 0em;}.csharpcode .lnum { color: #606060; }

What does this do? If I run this in my 2008 AdventureWorks database, I get something like this:

You can see that I end up with multiple columns (CustomerID, TerritoryID, CustomerType, rowguid, ModifiedDate). That’s handy, and cool, and allows me to get all the data in the table.

But do I really need it?

In most applications, my guess is that we don’t. Why do we need TerritoryID? That’s a foreign key to the SalesTerritory table, and typically what I want instead is the SalesTerritory.Name column instead of the ID value.

I could do this:
```
SELECT *
FROM Sales.Customer c
INNER JOIN Sales.SalesTerritory t
ON c.TerritoryID = t.TerritoryID
```
but that’s any better. Now I’ve returned even more columns, 10 more to be exact, including TerritoryID twice, once from each table. In AdventureWorks, this is 19k rows, and at a minimum this query has returned 19k rows x 8 bytes (int data type) too much data. That doesn’t sound like a lot, but what if this runs in your application 500 times a day? That’s a lot of wasted:
- bandwidth
- disk access
- memory from caching
- CPU work on the server AND client
I would also guess that most of the time when you access a customer, you don’t even want all the rows. Likely you want to filter this somehow, and you will with a WHERE clause, but it’s still wasted time and resources.

We know that the database often is a bottleneck. It’s a shared resource, it’s one machine, and it doesn’t scale as easy as multiple clients or web servers, or even developers, so we should avoid wasting resources when we don’t have to.

What Do You Do?

Here’s what I recommend:

You can write this, and it’s what I often do:
```
SELECT TOP 2
*
FROM Sales.Customer
```
And I get a limited result set:

Why is this better? I do this so I can easily see the column names. I can then include those in my SELECT statement, with a quick rewrite.

I could also quickly use the Object Explorer to find the columns like this:

And you can right click, and choose “script” and “as SELECT”

and paste the code into your query window. The results would look something like this:

Alternatively, my employer, Red Gate Software, makes a fantastic product called SQL Prompt that will help you quickly grab columns. For me, I can do an SSF, and get a SELECT * FROM and then choose the table:

Not that I see the columns to the right. I could also just select the table with a tab and then if I remove my askterisk, I get a list of columns I can easily pick:

SQL Prompt makes this easier, but it isn’t that hard to just do this by hand. You could easily grab the columns you need from SSMS and add them to queries.

The database is a limited resource, even if you have a 256 core server with 1024GB of RAM. You still want to query the data you need and only return what’s necessary. A little more effort when building code will pay off later with much better performing applications.

References
A few links from other people that see this as an issue as well.
September 1, 2010
SQL Saturday #28 – Keynote

I was honored to be asked to do the keynote for SQL Saturday #28 in Baton Rouge recently. It’s taken me longer than expected to process this and upload it, but here is my keynote speech from a couple weeks ago:

Part 1: The first 14 minutes or so with some introductory comments.

I shot this with my Flip camera on a stand near the stage since there wasn’t a good place to put it. A large auditorium and I wandered out of sight quite a bit. I realize that I also need a better way to grab audio for various events like this, so I’m looking at a separate audio mic and recorder.

August 26, 2010