Tag: T-SQL

  • T-SQL Tuesday #10 – Indexes

    TSQL2sDay150x150[1] It’s time for T-SQL Tuesday again, the brainchild of Adam Machanic (Blog|Twitter), and this month’s topic is Indexes. A thanks to Michael Swart for hosting this month’s event, and sending me a personal invite through email. That was pretty cool, especially since I don’t necessarily remember when the event is kicking off.

    The invitation is here, and time is running out to participate, but if you want to blog, you can do it today before this script (from Michael) tells you that you can’t

    IF GETUTCDATE() BETWEEN '20100914' AND '20100915'
    SELECT 'You Can Post'
    ELSE
    SELECT
    'Not Time To Post'

    Remembering to Index

    Indexing seems to be one of those things that so many people forget to do. As a result, adding a few indexes is often the easiest win, the lowest hanging fruit, the most effective way of improving performance in many situations.

    I know in the past that I almost always create a single index on a new table, the PK, when I build it. If there are FKs, those are setup as well, and this usually helps, but when building a database, I sometimes forget to add other indexes since I don’t know what other queries I might be running when I start. And as I start to build those queries (to support new functions for the user), it’s easy to forget to add an index.

    sqldatagenerator[1] Since it’s hard to guess what you will query often, and the think/build/test/refactor model leads you down dead ends at times, I don’t typically build an index for a new query. I don’t notice performance issues since I’m usually working with test data that has dozens of rows, not hundreds. In the past, I’ve wished for something like Data Generator. Now that I have it, I typically don’t need it anymore.

    I try to go back and add in indexes as we get close to deployment, but I’ll admit that I typically have found in the production system that I’m missing indexes. This usually occurs over time, as data grows and performance decreases. I’ve wished that I had a better method for handling indexes, and apparently Microsoft was listening to wishes since we now have sys.dm_db_missing_index_details and other missing index DMVs.

    However I found someone that did have a solution. The company I worked for bought a third party piece of software to handle some specialized function for a department. I wasn’t involved, but one day they came to me with a performance issue. As I dug into the application, which had been working for months, I noticed something interesting.

    Every column was indexed.

    Not only was every column indexed, they had added other indexes that reversed columns in the index, so if I had a table like this one:

    CREATE TABLE Customers
    (
    CustomerID INT
    , FirstName VARCHAR(50)
    ,
    LastName VARCHAR(50)
    ,
    ADDRESS1 VARCHAR(50)
    ,
    City VARCHAR(50)
    ,
    StateID INT
    , Country VARCHAR(50)
    , Notes varchar(max)
    )

    I might have these indexes:

    • CI : CustomerID
    • NCI: FirstName
    • NCI: LastName
    • NCI: FirstName, Lastname
    • NCI: LastName, Firstname
    • NCI: Address1
    • NCI: City
    • NCI: StateID
    • NCI: Country
    • NCI: Notes
    • NCI: Address1, city, stateid, country

    It seemed like a little overkill, and after discussing this with the support engineer for awhile, he admitted that a number of these indexes weren’t needed, but the developer didn’t know what to index, so they indexed everything.

    We decided to run a trace on the application for a week, sort through queries, and come up with some more rational choices for indexes. We also removed a few of the duplicate indexes, and we improved performance for a number of functions.

    Indexing is important, but you can overdo it. Make sure that you index, but also think about what you index, and don’t index everything.

  • T-SQL – Is Saving Space Worth It?

    I ran across this blog post on Joe Celko’s Restaurant Seat Assignment Problem that found a way to implement the smallest amount of storage for a classic problem. It’s more of a challenge than a real solution.
    Or at least I hope so.
    The solution actually uses some bitmasking to save space and cram more information into a single field. It’s creative, and interesting to look through, but is it the type of solution that you might want to use in your actual code?
    Personally I like seeing some elegant solutions in code, but not so esoteric or obfuscated that the average developer can’t understand it. Once something gets too complex, it can require more time to support and understand than it might be worth.
    Not that we shouldn’t be educating or improving the skills of other developers in your company, but if there is something that is an order of magnitude more compelx than most other code, I think that a meeting, a brown bag, or even a blog post to explain it is warranted.
    Back to the title of the post. Is saving space worth it? At times it is. I don’t advocate using INT for all fields when a tinyint or smallint will do, but I’m not sure that I find bitmasking to be worth it. In general, I think that creates more confusion than it solves problems or increases performance.

  • Does Join Order Matter?

    According to Conor Cunningham, it doesn’t.

    I have heard that there are issues in the optimizer, and to be sure, there are cases where the order can matter, but for most normal queries, most inner joins, order doesn’t matter. As Conor says, it might seem that a change produces a new plan or changes the performance, but don’t assume that this correlation implies causality.

  • Sharing a Temporary Table

    One of the things that I’ve often seen people do is build temp tables to hold data for each connection in a unique way. Back in older versions of SQL Server this was bad since large temp tables could lock sysobjects in tempdb and cause contention. That’s been fixed, but I still tend to avoid temp tables unless it’s truly something that is session dependent.

    I’ve seen some loading routines that needed temporary or staging data. We used to have some of this in a financial services firm where we had dedicated machines that would pick up files and then load them. However since we weren’t sure which machine would load what data, we wanted a shared data source. The developers didn’t know what to do, or how to share a staging table, so they used temporary tables. This gave us an all-or-nothing approach since the temporary tables disappeared if the connection was lost.

    We also needed a similar shared temporary storage at SQLServerCentral for mail processing. We wanted to pull out rows from a table for a particular sending process, and mark them as used by that process only. Since we had multiple machines that would act as senders, each of them generic, again, we wanted to share some type of “workspace” on SQL Server, but have it unique to each connection.

    The way that I’ve handled both of these in the past is to create a real table containing the structure I need and then add a column to it that identifies the session. If there’s an issue, you can see what rows were in process by a session and either assign them elsewhere, or return them to the pool.

    As an example, let’s say you need to send lots of emails, but you want to store some of this data in SQL Server as it’s processed. I could create a table like this:

    CREATE TABLE EmailSends
    ( EmailAddress varchar(20)
    ,
    SUBJECT varchar(200)
    ,
    Msg varchar(2000)
    ,
    SPID int
    )

    I have the basic data here to send an email. I can load this from other tables, and then add in the SPID. This column allows me to uniquely identify a session, and the process working with this table can determine which rows it should process. I could load this table like this:

    INSERT EmailSends
    SELECT
    EmailAddress, '', '', @@SPID
    FROM EmailList
    This inserts all the emails, and marks these rows as belonging to my SPID. If another connection ran this same query, they’d have rows marked with a different SPID. I could now do some processing on “my” rows like this:
    UPDATE dbo.EmailSends
    SET Subject = a.SUBJECT
    FROM
    EmailSubjects a
    WHERE a.EmailID = 1
    AND EmailSends.SPID = @@SPID

    What happens if there’s data in this table with my SPID that isn’t mine? That can’t happen if you do this both at the beginning and end of your connection:
    DELETE dbo.EmailSends
    WHERE SPID = @@SPID

    It’s a crude technique, and doesn’t necessarily fit a lot of situations, but if you want to use this to manage workloads, maybe redistribute things to different processes, this can help you.