Voice of the DBA

Category: Blog

  • The Avon Walk

    It’s more often than not that I wake up, hoping that no new bad news comes my way.

    The short story is that my wife is going to do a 39mi walk this summer to raise money for cancer. If you are so inclined, you can donate here.

    I’m fine, my family is healthy, and lucky, and we don’t have any real problems. We have inconveniences and annoyances, and I’m thankful that’s all we have to worry about. We are very fortunate that the five of us are all living happy, successful lives.

    However that’s not the case with many friends and acquaintances we have. Lately it seems that 1 out of every 10 people we know has been struck by cancer. Some are survivors in remission, some are struggling and some are just starting their treatments.

    Perhaps it’s a sign of getting older, and our friends getting older. Sickness is more likely as you age, and we are coming to terms with this as the news of friends’ struggles reaches us. It’s sad, and it sucks, and I hope that we can find a cure for the various cancers in my lifetime.

    My wife lost her mother to breast cancer, and she has a few friends that are in the middle of their fights. She writes about it on her donation page, and decided to do something more than donate our own money. She committed to the Avon Walk this summer, a marathon one day, and a half the next. She started her training last weekend, dragging me along for a few walks.

    She is looking to raise awareness of the issue, and also raise money to help fight the disease. If you are inclined to help the research, please feel free to donate. If you’d like to sign up for this challenge, we’d love to have you join in as well.

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

  • Blackout

    It’s anti-SOPA/PIPA day, and a number of sites have shut down for the day. O’Reilly closed their page, and of course, Wikipedia shut down.

    oeillysopa

    Google didn’t do the blackout I was hoping for, but they have hidden their logo:

    blackout

    I like the Wikipedia closure, which appeared seconds after hitting a page. It has a great effect on me.

    wikipedia

    I debated about shutting down this site, but for a WordPress hosted site, I didn’t see an easy way to do it.

    To be clear

    I am not against copyright legislation, nor IP protections. We should ensure that content creators have some recourse and ability to control the way their content is used.

    I think SOPA / PIPA are gross, overreaching ways of doing this that are designed to help a few large companies, and potentially hurt many small ones, and will have no impact on foreign sites. These laws simple cut the US off; they do not affect the operation of the foreign site.

    We can come up with better ways to protect content, while also preserving fair use and personal liberties, and limiting copyright.

    My personal stance is that the original 14 years + 14 year extension for copyright is plenty. If you cannot earn money in those 28 years, let someone else build on your work.