Voice of the DBA

Tag: sql server

  • Enabling Compression? Update your baseline

    I wrote recently about capacity planning, with an item near the end about disk usage. Someone pointed out to me that compression can dramatically affect your baseline, and as you implement it, or de-implement it, you need to update your baseline.

    That’s an important point, and if you make changes, you want to do one of two things.

    • Recalculate the old numbers to be in line with the new ones
    • Make a note in your documentation somewhere

    Whether you restate the history is up to you, but I wouldn’t. I hate changing the old values, but I can see someone not being aware of changes and perhaps getting confused. If that is a problem, and you are sure you’ll keep compression around, then you can run a quick test of compressed and uncompressed backups, get a rough idea of the ratio, and then alter your old numbers.

    For me, I prefer to make a documentation note, and update everyone to be aware of the change, perhaps even adding filters to my extrapolation routines to not use the older data.

  • Insert multiple rows from one INSERT statement

    One of the things that has been annoying for a long time in SQL Server is adding lots of data for testing. Personally I use Data Generator, and I recommend that, but for a quick few rows of data, do you want to do this:

    CREATE TABLE SalesOrders
( OrderID INT IDENTITY(1,1)
, OrderDate DATETIME
, CustomerID INT
, OrderAmount NUMERIC(10, 4)
)
GO
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    There’s a lot of INSERT typing, even with copy/paste functionality and editing the various rows gets to be cumbersome of you actually get some sample data like this that you want to convert (say from someone’s blog post of results:

    1, 12/1/2011, 3, 123

    2, 12/2/2011, 4, 2

    3, 12/1/2011, 3, 123

    You could do the UNION thing, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
  SELECT '1982-05-19 06:31:48.950', 1,           579040.5070
UNION ALL
  SELECT '1994-11-27 17:14:41.790', 2,           348808.5860
UNION ALL
  SELECT '1972-11-08 17:40:01.170', 3,           758992.3650
UNION ALL
  SELECT '1972-05-31 01:19:05.530', 4,           779853.1990
UNION ALL
  SELECT '1994-12-22 10:40:57.410', 5,           666173.8040

    That works, but it’s still a little cumbersome.

    In SQL Server 2008, there’s a better way. You can now include multiple sets of data in your insert, like this:

    INSERT SalesOrders (OrderDate, CustomerID, OrderAmount)
VALUES 
(           '1982-05-19 06:31:48.950', 1,           579040.5070),
(           '1994-11-27 17:14:41.790', 2,           348808.5860),
(           '1972-11-08 17:40:01.170', 3,           758992.3650),
(           '1972-05-31 01:19:05.530', 4,           779853.1990),
(           '1994-12-22 10:40:57.410', 5,           666173.8040)

    Just put brackets around each set of data, and you can easily insert multiple rows.

  • RECONFIGURE can flush the procedure cache

    I ran across this KB article the other day, which lists a few ways in which performance is affected by various maintenance or administrative type operations.

    In KB article 917818, it notes that some operations cause a performance issue. Some of the operations make sense (offline/online, restores, etc), but there were a few that surprised me. For example, did you know that Autoclose flushes the cache? Might not be a big deal, but it also might mean that your apps based on Express might end up running slowly each time the user accesses the databases.

    There are also a number of items which are implemented by a RECONFIGURE that will flush the cache. These are listed in the KB and are:

    • cross db ownership chaining
    • index create memory (KB)
    • remote query timeout (s)
    • user options
    • max text repl size (B)
    • cost threshold for parallelism
    • max degree of parallelism
    • min memory per query (KB)
    • query wait (s)
    • min server memory (MB)
    • max server memory (MB)
    • query governor cost limit

    Also, changing a filegroup to read-only will flush the cache.

    This is by design, and I wouldn’t expect it to change anytime soon. Since these options can affect query plans, it might make sense to flush the cache, but if you don’t agree, file a CONNECT item and stump for votes.

  • Capacity Planning – Monitor and Extrapolate

    I wrote a post recently on capacity planning, and then thought of one more thing. In addition to planning for the future for new systems, you also need to regularly monitor your existing ones.

    Planning for the future, IMHO, isn’t something you want to do when you have a crisis, like running out of disk space. A good DBA is proactive, and looking to monitor resource usage and extrapolate out regularly to get an idea of

    1. when you’re overwhelm your current hardware
    2. how big to go on the next upgrade.

    Nothing worse than your boss complaining about performance, you recommending an upgrade and then having no idea what to ask for. You also don’t want to get caught in emergency situations when nothing has changes in your workload, other than you weren’t paying attention.

    I used to do this in a few ways, and there are numerous ones today. You can use a product like SQL Monitor, you can use the MDW from Microsoft if you have EE, you can use other products or roll your own. I have done all of these, except MDW, and they have all worked well.

    In terms of memory, I haven’t done this in some time, so I’ll let you get details from another source on how to monitor the usage over time on the modern versions. Here’s a good thread to get you started.

    In terms of CPU, I tend to monitor the raw CPU percentage over time, using a broad average across a whole day, watching the peaks. I would trend this out for 3-4 months, and see if it is increasing. Since CPUs can be hard to upgrade, you want to know this well in advance.

    Disk Space

    This is the big one, and quite embarrassing when you can’t capacity plan here. There are numerous ways to do this, but here’s what I’ve done.

    I monitor backup size every day and store it in a small administrative database. If I move the database, I move the backup information to the new instance as well. You can query the msdb tables for the sizes.

    SELECT DATEPART( yyyy, backup_finish_date) 'backup year' , DATEPART( mm, backup_finish_date) 'backup month' , DATEPART( dd, backup_finish_date) 'backup day' , database_name
, SUM(backup_size) 'backup_size' , type FROM msdb.dbo.backupset
 GROUP BY database_name
 , type , DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) ORDER BY DATEPART( yyyy, backup_finish_date) , DATEPART( mm, backup_finish_date) , DATEPART( dd, backup_finish_date) , database_name

    There’s a quick query, and you can perform some summaries of sizes by types of backup, convert to GB, etc. I used to run a similar query every day and do three things:

    • store the results
    • compare the last backup to the one before it.
    • extrapolate out the growth of backups to reach the disk size

    Obviously I wanted to track the results, and I would monthly create an old average of the sizes from last year so I wasn’t storing crazy amount of data.

    I compared the backup each day with the previous one to catch weird things. If the backups differed in size by more than 20%, I raised a flag. This was to catch changes in process, or potential errors from data loads or imports. Anything that changes 20% a day can be a problem, so this allowed me to find problems quickly.

    Lastly, I aggregated all backups on the instance, extrapolating out the growth across weeks, and estimating when I’d fill the disk. When this was inside of 2 months, I knew I better start asking for more space.