Voice of the DBA

Tag: T-SQL

  • 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.

  • April Blogger Challenge 4–Filtered Index Limitations

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

    Filtered Index Limits

    I ran across a limitation recently with filtered indexes and was surprised. Apparently, you can’t make complex filters in your definitions.

    In the last post, I wrote about creating a filtered index on gender, looking for non-null columns in a table. That code looked like this:

    CREATE INDEX Users_Gender_Filtered
  ON Users (gender)
  WHERE gender IS NOT NULL;

    However, what if I find that I have lots of NULL values, but also lots of spaces, ‘ ‘, in my table because this is an optional field, and the data entry code changed at some point. Neither of these rows actually helps me in finding the rows with an ‘M’ or a ‘F’.

    I tried creating an index like this:

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

    That didn’t work, so I tried parenthesis.

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

    That didn’t work either. No matter how I moved parens around, they didn’t allow a complex (if two criteria are “complex”) filtered index.

    This is a limitation of a filtered index right now. I’m not sure if it will be changed, though I would like it to be. There are other limitations, so read the docs carefully, and think about what might work well in your environment.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

    Look for the other posts in the April challenge.

  • Altering a column to add NOT NULL

    I had to do this recently and needed to look up the syntax as I couldn’t remember it. I don’t do this often, but I was working on a particular item and wanted to test this.

    I had a table, something like this:

    CREATE TABLE SimpleTable
( MyID INT NOT NULL PRIMARY KEY
, MyChar VARCHAR(200)
, [Status] TINYINT
);

    I decide that the [Status] column shouldn’t be NULL and decide to ensure it’s always got a value. I don’t want to rebuild the table, I want to ALTER it. I use this syntax:

    ALTER TABLE dbo.SimpleTable
 ALTER COLUMN Status TINYINT NOT NULL;

    That’s it. I do this and the column now no longer accepts NULL values.

    Of course, if there’s data in there, I need to set those values to something that isn’t NULL before I can do this, but that’s a separate topic.

    By the way, I remembered most of this syntax. What I forgot is that I need to include the datatype in there to make this change. There are a number of restrictions with this command, so be sure that you read carefully before you try to make changes. If you do this in SSMS, you might end up with a script for a new table, which may not be what you want to occur in a production environment.

    Reference

    ALTER TABLE – https://msdn.microsoft.com/en-us/library/ms190273.aspx

  • April Blogger Challenge 3 – Filtered Indexes

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

    Why Filtered Indexes

    I haven’t used filtered indexes much in my career as they didn’t exist while I was a full time SQL developer and DBA. They appeared in SQL Server 2008, and are a very useful feature in certain circumstances.

    Imagine I have a simple table:

    CREATE TABLE Users ( MyID int IDENTITY(1, 1) , firstname varchar(250) , lastname varchar(250) CONSTRAINT pkUsers PRIMARY KEY CLUSTERED (firstname, lastname) , gender char(1) , postalcode varchar(12) , contactphone varchar(12) ); GO

    I want to add some indexes that look for specific fields since those are times I am limiting searching in the table. For example, suppose that gender is an optional entry by users. I find that I often have NULL in this column, so I create a filtered index.

    I’ll do that as follows:

    CREATE INDEX Users_Gender_Filtered 
   ON Users (gender) 
   WHERE gender IS NOT NULL;

    This is the same type of index statement we normally use, but we’ve added a WHERE clause to the statement. This means that only those rows with a non-NULL value in the gender column will be included in this index.

    Why Use Filtered Indexes?

    There are some reasons given in the BOL article below, but the main reason to do this is performance. I could easily add an index using gender that isn’t filtered, but if I had 1.5mm rows in my table and 1.4mm of them have NULL here, then do I want to scan those 1.4mm rows if a query wants a specific gender? I don’t.

    Indexes take space, which means more space on disk, more backup space, and more time for backups. However they also impact performance when we read the index, or we update a column used in an index. Each of those operations requires either a read or write of the index. If I can reduce the reads and writes to indexes, then I can improve performance dramatically.

    I would urge you to look at filtered indexes whenever you have data where a large number of rows are not going to match a query. In these cases, you can use a filtered index that is highly selective.

    References

    A few places I used to research this post.

    Quick and Easy Blogging

    This post occurred to me while I was writing some code and was the first post I thought of for the challenge. However I realized I could start with the other posts, and so I wrote those posts first. This one took about 10 minutes to read through limits on filtered indexes, try 4-5 experiments to get the code to work, and write the post.

    Look for the other posts in the April challenge.