Voice of the DBA

Tag: T-SQL

  • Reframing to Overcome Filtered Index Limitations

    I’m continuing on with the Blogger’s challenge in this post.

    Turning the Problem Around

    In the last post, I wrote about limitations in filtered indexes. I proposed that my table had lots of data with NULL or blank spaces in the gender column, and I wanted to avoid indexing those rows. I tried this code

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');

    However, that failed. I can’t use two sets of criteria in the filtered index. But I can use one, so I need to re-frame the problem.

    If I look at the data, I have four choices: ‘M’, ‘F’, NULL, ‘ ‘. I can group two of those choices together, looking for a positive (matching) set of data rather than a negative (non-matching set. The easy way to do that is with an IN clause.

    CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IN ('M', 'F');

    This works, and I’ve got a filtered index. In the real world, I’d actually drop the first index (gender is not null), and only go with this one.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me as I was writing the other post. I almost added a note on turning the index around, but realized this is a separate topic, and it makes a nice, second post. This post required < 10 minutes.

    This post continues the April Blogger Challenge is from Ed Leighton-Dick, aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

  • Idempotent

    I ran into the word idempotent in the Stairway to Integration Services. I had heard the word, but I hadn’t really considered how important it can be for a DBA or developer until that time. It’s a term used in computer science, as well as other sciences, but I think it’s one that many of us don’t consider when we’re writing code, especially code used to deploy software to other systems.

    Most of us have written scripts like this:

    if exists (select object_id from sys.objects where name = 'uspGetSales')
  drop procedure uspGetSales;
create procedure uspGetSales
as
...

    At the end of running this code, we have the system in a state. If we run this over and over, we’ll regularly return to the same state, which is often exactly what we want to occur. That works great when deploying code, but what about this:

    insert into states select 'CO', 'Colorado';

    If I run that multiple times, what will happen? Either I’ll get errors if one of these fields is a PK, or I’ll get multiple inserts. If I’m running this as part of a software deployment, do I want either of those conditions? Do I want my end user to experience either one?

    No, I don’t. Certainly if I’m manually making changes to systems I can probably avoid issues, but that’s not what I want to do. Maybe you do, but I don’t. I’d like to be able to restart my deployment if something fails, without causing other issues. I’d like idempotent code, like this:

    if not exists (select abbrev from states where abbrev = 'CO')
  insert into states select 'CO', 'Colorado';

    That way if I happen to run this code twice, or ten times, I arrive in the same place.

    I realize that building scripts and deployment processes that are idempotent is a pain. It’s work, but it’s also scriptable and repeatable work that is easy to automate over time with a few patterns. I also realize that a little extra work to prevent issues is often an investment that’s worth making for both my customers, and my reputation.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Complete Definitions

    I saw a script from a user recently that looked like this:

        Create db x on file =
    go
    alter db set compatbility = 120
    go
    alter db set containment = none
    go
    ...
    alter db set service_broker off
    go

    That’s interesting because most of the code I see looks more like this

        create database x;
    go

    That’s it. At first I thought this was overkill, but then I wondered. If we are actually creating a new database for an application, shouldn’t we set the options the way that our application needs them rather than rely on defaults? I know that often many of us work with defaults on our instances and databases, but should we expect all configuration options to just be at their defaults? I know I certainly have been burned in the past with default changes.

    I’ve seen similar scripts for code, with SET ANSI_NULLS and other settings in the script. While I sometimes think that code gets in the way, I know that the script contains the settings that work for the code I’ve written and tested. If I remove those settings, it’s entirely possible that my code might not work. Do you know all the ways in which the various SET parameters for code affect your code? I’m sure that many of us don’t think about these changes when we’re writing code, assuming that the dev, test, and production servers will be the same as our database. In most cases they are, but not always.

    There’s a trend towards explicitly declaring the settings for an environment and then letting the system ensure that it maintains those settings when it’s built, or perhaps every so often. If that’s the case, and someone makes a mistake, say on ANSI_PADDING, do we really want to assume none of our code was compiled with those settings? I’m not sure I do, and I suspect that we should be explicitly putting all our settings at the top of scripts to ensure code behaves as we expect.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Finding Problem Code

    Performance of a SQL Server seems to be one of the main concerns for most developers and DBAs. We are under pressure to constantly squeeze more performance out of our applications and reduce the time it takes for users to interact with systems. Reports can be especially problematic as users don’t want to wait for results, but certainly data entry tasks can’t be impacted either.

    Whether or not we can actually make changes to the system, or even have time to bother might be irrelevant. I’m sure there are plenty of databases that some of us don’t even bother to try to tune because we’re busy elsewhere. Likely writing more code for additional features or reports that have been requested. As a result, we may pile up lots of code that isn’t necessarily run often. However when that code is executed, we’ll receive no shortage of criticism if the code doesn’t perform well.

    This week I wanted to know how you might go about finding the code that is problematic if there isn’t a complaint. If you do have time to pro-actively tune your system, what are the techniques you use to examine a system. I suspect the more advanced people will have answers, and I hope they share them as there’s no shortage of readers that may struggle to improve the impressions of their database server.

    What’s the best way to find the SQL statements you need to tune?

    It’s a simple question, but imagine that you know there are complaints, but don’t know what is the best way to focus your time. You, as a new or accidental DBA, want to improve the system, but where do you start? What code is the problem? Is the report that your boss complains about that’s the issue or are there other queries that slow down the report execution?

    Share the way you’d tackle a system when you don’t have a specific query in mind to tune.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.2MB) podcast or subscribe to the feed at iTunes and LibSyn.