Voice of the DBA

Author: way0utwest

  • The Poor Soul

    Poor Souls

    I recently had someone post this after I made a comment about someone taking responsibility for their database server.

    “So Steve, what would you recommend then for those poor souls?  As being one of them it drives me nuts to constantly be told I can’t get more training as it’s not my true job position yet I’m responsible for making the databases work. “

    I’ve been in this spot a few times, in and out of IT. I’ve been tossed into cooking or bartending jobs without training and had to learn quickly how to do the job, and I’ve had the same thing happen in IT. In a few cases the companies knew it was a bad situation and they eventually got me training, in others they didn’t. Here’s the advice I have for you.

    Ultimately you are responsible for the job. Tough love, but you’re being paid for that job, so you are responsible.  That means you have to learn how the technology in your environment works and how to solve the problems you have.

    First, get your resume up to date. Make sure it is ready for submission, and you are prepared to get fired every week.  Keep an eye on the job market and save some extra money, because to me, the financial security for my family comes first.

    Second, learn to restore data and then make sure you have backups in place. This is secondary because if you have a failure quick, you want to be ready to get a new job. But ultimately no matter what breaks or doesn’t work, getting data back first is crucial.

    As you go through all of this, you might be fighting fires. So while you practice restores or document the environment, you might be trying to fix things and asking questions of others, but invest the time to get yourself into a solid position.

    I’d also talk to my boss regularly. Every time I found a place I didn’t know something, I’d make a note and let my boss know this is a hole. Maybe I can learn it, maybe I can’t, but I could use help. That might be the best ROI for conferences. Go, make friends, get contacts that can fill your knowledge holes. Or find consultants you can call.

    Steve Jones

    The Voice of the DBA Podcasts

  • Dropping a Row

    Could you drop a row in your system?

    In a scalability discussion, I saw this comment: Companies like Google or FaceBook manage a lot of data, but it’s not held the same degree of scrutiny. For example, if FaceBook dropped 1 out of 1,000 random guestbook posts, would anyone notice? At the end of the day would they even care enough for it to make national headline news?

    How many companies would accept a random 1 out of 1,000 dropped data entry row? Or an update that didn’t take? Most management in companies I’ve worked for wouldn’t even want to think about accepting that level data loss.

    Ultimately I think this points out the difference between some of the non-RDBMS platforms  that can accept some data loss. Even Google, as amazing as their results are and with lots of redundancy, aren’t held to some large standard of data integrity. If two of us search for the same term at the same time and get different results, is that an issue? Or to put it another way, if the CFO and CEO both run reports at the same time, can they differ in their results?

    For most of us, the answer is that the results cannot differ. While I think most of the NoSQL and other non-RDBMS architectures have a lot of effort put into ensuring that data gets hardened on a node when it is updated, there can be a lack of consistency between nodes. A node could lag behind others or even fail before synchronization with other nodes. That is a concern in any system that looks to scale out to a large number of servers, and an even larger concern for data whose integrity is critical.

    An amazing level of thought has gone into SQL Server to provide extremely high levels of data integrity. Every time I think I’ve found a problem or hole in the product, it seems someone at SQLskills explains the reason behind the architecture. The answer usually makes perfect sense to me and has me wondering what else I will learn in one of their Immersion training weeks. Hopefully I’ll get to one soon.

    There are definitely places where you might accept dropped rows. Information published on intranets, an application recording vacation requests, and any other number of small non-critical systems. SQL Server is not a good fit for all database applications, but for those that use it, you can be sure that none of your rows will be dropped.

    Steve Jones

    The Voice of the DBA Podcasts

  • T-SQL Tuesday #18 – My CTE

    It’s time for T-SQL Tuesday again, and it’s number 18. Hard to believe it’s been a year and a half since Adam Machanic (blog | @AdamMachanic) thought of the idea. I’ve participated in most and it’s something I look forward to each month. This month Bob Pusateri hosts the party with the theme of CTEs. No, it’s not thermal unit of expansion, and I hated chemistry.

    My CTE

    When CTEs were introduced, I thought they were a great idea. They made it much easier to write complicated queries that might need derived tables. In the past, writing something like this was hard to read.

    SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
                 ) b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    A CTE can make this much easier to keep track of, especially in places where you don’t want to create a view instead.

    WITH ProductCTE
    AS ( SELECT ph.ProductID, ph.StandardCost, pri.Quantity
                     FROM Production.ProductCostHistory ph
                       INNER JOIN Production.ProductInventory pri
                         ON ph.ProductID = pri.ProductID
                    WHERE StandardCost > 10
    ) SELECT p.Class, p.Color, p.DaysToManufacture, p.ListPrice
     FROM Production.Product p
      INNER JOIN ProductCTE b
        ON p.ProductID = b.ProductID
      INNER JOIN Production.ProductInventory pi ON p.ProductID = pi.ProductID
      WHERE p.Color IS NULL AND p.DiscontinuedDate IS NULL

    I know this isn’t a great example, but by moving subqueries to a CTE structure, the end query is easier to debug and read.

    Top X of a Group

    Suppose you have a small result set of something like this.

    CREATE TABLE Books
    ( BookID INT IDENTITY(1,1) , BookName VARCHAR(200) , Genre VARCHAR(50) , reads INT ) go INSERT Books SELECT 'Old Man''s War', 'Sci-Fi', 200
    INSERT Books SELECT 'Ender''s Game', 'Sci-Fi', 345
    INSERT Books SELECT 'Red Thunder', 'Sci-Fi', 143
    INSERT Books SELECT 'Quarter Share', 'Sci-Fi', 25
    INSERT books SELECT 'The Enemy', 'Thriller', 67
    INSERT books SELECT 'The Hunt for Red October', 'Thriller', 678
    INSERT books SELECT 'Bad Luck and Trouble', 'Thriller', 545
    INSERT books SELECT 'Game of Lions', 'History', 644
    INSERT books SELECT 'The Rise of Theodore Roosevelt ', 'History', 67
    INSERT books SELECT 'An American Life: The Autobiography', 'History', 267

    Suppose I wanted to top two books from each genre, ranked by reads. A TOP 2 won’t work because that doesn’t allow you to specify groups. However using ROW_NUMBER and an OVER clause in a CTE, this becomes an easy query.

    WITH BookRanks AS ( SELECT b.BookID
          , b.BookName
          , b.Genre
          , b.reads
          , ROW_NUMBER() OVER (PARTITION BY b.genre ORDER BY reads DESC) AS Counter FROM Books b
     ) SELECT bookID
          , Genre
          , reads
          , bookname
      from BookRanks
       WHERE counter <= 2

    That gives me an easy to read result set:

    bookID  Genre    reads bookname

    ——- ——– —– ———————————————————-

    8       History  644   Game of Lions

    10      History  267   An American Life: The Autobiography

    2       Sci-Fi   345   Ender’s Game

    1       Sci-Fi   200   Old Man’s War

    6       Thriller 678   The Hunt for Red October

    7       Thriller 545   Bad Luck and Trouble

    Older T-SQL Tuesday Topics

    Just a quick list of the past topics and the roundups.

  • Tiered Storage

    Storage, storage, storage

    One of the things that I saw demonstrated at my very first PDC conference in 1998 was the addition of Hierarchical Storage Management to Windows servers. This was in the Windows NT 4 era, and I was investigating a variety of ways that we could potentially handle a large number of fax and scanned images for my company. We eventually implemented a RW optical jukebox to help us manage our large collection of images and it worked very well in helping us manage costs.

    I saw this great article recently on tiered storage that uses a database system as an example of how you can potentially improve the performance of your system and manage costs by using different types of storage. It’s worth the read, and talks about a fictional example of how we might use SSDs for frequently accessed data and SATA HDDs for cold, less accessed data.

    As we collect and store more and more data, I think that we will find that we are storing lots of data that we potentially access very infrequently.  If that is the case, then we ought to be considering different types of storage that can handle the needs of that particular set of data while also managing costs. I have always struggled with my budgets for database servers, trying to manage CPU, RAM, and disk costs and find a balance among them. If I can potentially use different costs of disk storage to gain more RAM or CPU power, it’s a trade-off that I would have often made.

    The trick with tiered storage is knowing your data and the access patterns. That means better understanding your queries and access patterns, and that requires better knowledge of how SQL Server works. You should learn to query DMVs and read performance metrics and apply that knowledge to your own systems. Those skills might just help you improve performance in a very cost effective way, an accomplishment that is worth bringing up in your annual review.

    Steve Jones

    The Voice of the DBA Podcasts