Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday Topics

    I was asked to host a T-SQL Tuesday, and I accepted.

    Once again, my fingers move a bit faster than my brain. “Sure, be happy to host one. Send me a date” flew out of my fingers before I thought through the implications. I’m happy to help the community, and I think Adam Machanic (Blog|Twitter) had a fantastic idea with the T-SQL Tuesday blog party, and I look forward to them every month.

    But I’m busy, and this is a very busy time of year for the next month, with the hope that most of December will be quiet for me. It’s been a crazy year for me and I’m looking to try and better manage life next year.
    Adam immediately responded to my email with a date, and I have December. I was thinking he’s a busy guy, and my mail might get lost in the shuffle. However it didn’t and I ended up with not only an acceptance, but a date that’s not too far in the future.

    So I need a topic.

    There are any number of topics out there, and after all, T-SQL Tuesday has only been around for a year. However I was somewhat stumped as to what a “good topic” will be. After all, I want this to be a good one.
    The ones I’ve seen so far are:

    I’ve got a few ideas, and some work ahead of me to develop something in the next month around SQL Connections, SQL Saturday #59 and a week of family vacation. If you have any ideas of your own, don’t post them. Save them for your own T-SQL Tuesday and contact Adam to host one.

    I’m not sure what will happen in November, and I’ll be jammed up with a post since I’ll be at Connections next week, so whether it’s the first Tuesday or second, I’ll be hustling a bit to participate.

  • T-SQL Tuesday #12 – Misconceptions

    TSQL2sDay150x150 It’s time for T-SQL Tuesday again, and I’m happy to participate again. This is a monthly blog party started by Adam Machanic (Blog|Twitter )that asks people to write on a particular topic all on the same day.

    This month’s theme is misconceptions in SQL Server, hosted by Sankar Reddy. You can read the rules for the party and get information from his blog.

    The Most Common Misconception

    I read a lot of forum posts, literally hundreds a week on all topics in SQL Server. One thing that I consistently see asked is the dreaded:

    “My transaction log has grown so large it filled the disk”

    That’s a common occurrence, too common in my opinion, and while there are people that forget to setup any backups, I also find in the majority of cases people just don’t understand one thing:

    A full backup does not clear the transaction log.

    Too many people assume that the log will get managed by a full backup. It doesn’t. While some log record get included in a full backup, they do not get marked as “backed up” and the space re-used.

    If there is one thing that I wish everyone managing a SQL Server knew, it would be that they need to make full and log backups to properly manage the disk space usage by their logs.

  • 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 Tuesday – Beach Time

    TSQL2sDay150x150[1] I almost forgot about T-SQL Tuesday this time, so I’m scrambling to get an entry done today.

    This is a monthly blog party, on a central theme. This month the theme is Beach Time, from Jason Brimhall, and it’s a good one. This is the 9th T-SQL Tuesday, started by Adam Machanic on his blog earlier this year.

    Packing Heavy

    I’ll admit, when I travel I typically over-pack a bit. I think it’s because I’m a little anal-retentive, a little detail oriented, and I hate missing things when I’m traveling. I know I can usually buy my way out of a jam, but sometimes it’s a pain, and I don’t like it.

    What do I need to do before I leave? There’s one big thing here, and that’s communicate. If I have processes that will notify me of problems, or things to watch, I have to be sure that I have someone watching for those notifications, and I have trained them on what to do. I need to let my boss know who will be covering for me (if they haven’t designated someone) and I need to let any customer service people, business analysts, etc. that I typically work with, that I’ll be gone.

    All of this leads to my second point: expectations. I haven’t always done a good job of setting expectations in the past, and have been interrupted while on vacation. Part of that is OK if you’re well-paid, you’re the go-to-guy, but you don’t want that to be the default when you’re on vacation. Let your boss know that emergencies mean down systems, not a sales guy trying to close a deal for his month. It means significant loss of revenue, not a report that the boss wants today instead of next week.

    This is hard, but it pays to set expectations, and it pays to respond to inappropriate calls with “I’m on vacation, we can talk when I get back.” This is much better than “call me if you have a problem,” which invites people to interrupt you, disturbs your family, and doesn’t teach others to think for themselves. Let them flounder a bit; I can almost guarantee they’ll be fine.

    Emergencies

    There are true emergencies, and it’s possible that whoever you leave in charge can’t judge them well. They might call you and ask if this is a problem, and you might need to take that call. Ever since cell phones have been common, since around 1996 or so, I’ve had one. It’s typically been used for work, with me billing back some of the charges. Before that, I had a pager.

    I’ve had numerous interruptions over the years, but two emergencies that stood out. One was when I worked for a small company and took vacation because I had family in town. I was around the local area, and took a call from my boss near the end of my vacation that a server was down. A RAID controller had failed, and while the sysadmin had replaced it, they couldn’t restore the SQL Server. They kept running this:

    RESTORE DATABASE XXX FROM DISK = ‘YYYY.bak’

    RESTORE LOG XXX FROM DISK = ‘ZZZZZ_1.trn’

    RESTORE LOG XXX FROM DISK = ‘ZZZZZ_2.trn’

    Anyone see an issue? Yep, they weren’t using NORECOVERY. They actually were using the GUI, but I recognized the issue. I was driving nearby, and started walking them through the issue, but realized it would be quicker if I just helped. I stopped by, explained what was happening while starting the restore, and then built a quick script to grab all the log files (about a dozen) and restore them all for them.

    My second emergency was for the SQL Slammer worm. It actually hit on a Sunday night as I was driving back from a ski vacation. Work couldn’t reach me since there was no cell service, but had convened an all-hands meeting since the network at a Fortune 1000 company was actually shut down. I got a message when I got home and went to work that night. Vacation ended early, but I made up some time later in the week.

    Vacations are Hard

    It’s hard to prep for being away. It’s hard to make sure work is done, and that things function without you. I know many people worry that if things go too smooth when they’re gone, the company won’t see them as valuable.

    I think that’s wrong. You can bring value to the company, and still be away. The key is to pace yourself, and don’t get too overworked. If you have deadlines, make sure they account for vacation, and perhaps push things back. You don’t want to be unreasonable, but don’t be all-accommodating either. It will upset your balance, and could ruin your vacation.