Voice of the DBA

Author: way0utwest

  • 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

  • An Impressive SQL Server

    What would impress you?

    Recently I heard about one SQL Server with 1TB of RAM and another one that was handling over 10,000 transactions a second. I read a note about a 1.1PB database, built on SQL Server to hold data from telescopes. That particular data warehouse is producing 1.4TB a day. I know it’s mostly binary data, but that’s a lot of 1s and 0s being stored in SQL Server.

    Over the years I’ve heard regular complaints and concerns from executives about SQL Server scalability. Those complaints have dwindled, but I think there are people in the relational database world that still question SQL Server’s ability to handle high volumes or data sets.

    This Friday, I wanted to see what might change their mind, or maybe what might make you view SQL Server differently. While I suspect most of you are SQL Server fans, that doesn’t mean that you don’t have concerns at times about how far you can push SQL Server. The question this week is:

    What company would impress you if SQL Server backed their main systems?

    This isn’t scientific, and I’m not defining which systems impress you. It could be their web site, their supply chain management, their largest data warehouse, or anything else. What company talking about using SQL Server in one of their systems would impress you? And which system would you like to see backed by SQL Server?

    When I think about a lot of data, high volumes and rates, I think of a few things. The stock market, sports, and Amazon. If Amazon switched their website and ordering systems to SQL Server, I’d be impressed. If Major League Baseball, or the National Football League were to run all their statistical systems on SQL Server, I’d be impressed. With all the fantasy sports fans out there, the gathering and processing live data for real time calculations of performance, would be impressive.

    I’ve left out other companies. Wal-Mart using SQL Server for supply chain management would be impressive. UPS managing packages on SQL Server would be something. I’m sure have your own ideas, and let us know this week.

    Steve Jones

    The Voice of the DBA Podcasts

  • My 2011 PASS Summit Submissions

    I wasn’t sure that I would submit anything, but in the end I committed to a few events in Seattle and decided that this was something I should do. If I get selected it will save my boss some $$ and it’s part of what I should be doing for my job.

    I’ve done 3 different presentations in the last year for various events and decided on submitting only one of them to the Summit. The other is a variation on one I’m building for SQL in the City. I built that one to show off some Red Gate tools, so the places where I show demos of tool stuff I’ll rip those out and go with generic solutions instead.

    The last one actually came to me one night when I couldn’t sleep. I had been thinking about work and had an upset stomach one night. I was up a few times and while sitting on the edge of the tub, trying to decide if I should go back to bed or just give up, this presentation came to me. I had the iPad with me and spent about 45 minutes or so in Evernote, sketching the outline to the presentation in 10 parts, and then fleshing out 4 or 5 of them into a few paragraphs apiece.

    Here are my three, and no idea how they’ll do against the competition, or even if the committee will consider them, but we’ll see.

    Preparation for Disaster

    Sooner or later some sort of disaster occur on your SQL Server instance. It might be the destruction of a server, the corruption of a page inside the database, or just the unexpected deletion of some data. When disaster does strike, will you be prepared? Steve Jones will cover a number of common disaster occurrences and the ways in which you can be prepared to handle the situations.

    The Top Ten SQL Server Skills You Need

    Come learn the ten most important skills that every accidental DBA, developer, or even manager should be able to do in SQL server. If you want to know where to start working with SQL server or which 10 things will get you through most of your days, some see Steve Jones show you how to accomplish these ten important tasks and explain why they are important. These ten skills represent the 80/20 rule in action; if you can do these ten things, you can successfully get through most of your days at work.

    Branding Yourself for a Dream Job -The Modern Resume

    Everyone wants a dream job that they enjoy going to each week. However finding that job, and getting yourself hired can be hard for most people. Steve Jones will give you practical tips and suggestions in this session that show you how to better market yourself, how to get the attention of employers, and help improve the chances that the job you want will get offered to you. Learn about networking, blogging, writing, speaking , and more from Steve Jones, who has successfully used these techniques to find his dream job.

  • A row has no row number

    It seems that every month I have someone asking the question about ordering or row numbers for a query. Let’s get one thing clear from the start: there are no "row numbers" in a table.

    You can assume that the first row you inserted is row number one, but it’s not. In fact, depending on the indexing or lack of indexing, you may or may not get that row returned first by a query. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.

    As an example. If I have this People table:

    ID Name
    -- -------
    1  Steve
    2  Gail

    and I query: 

    select ID, name from people order by name

    I get 

    ID Name
    -- -------
    2  Gail
    1  Steve

    I could add a row number 

     
    SELECT row_number() OVER (ORDER BY [name])
           , [Name]
       FROM dbo.People

    and get this: 

       Name
    -- -------
    1  Gail
    2  Steve

    But "Gail" isn’t linked to "1" as a row number. If I do this: 

     
    INSERT people SELECT 3, 'Bob'

    SELECT row_number() OVER (ORDER BY [name])
           , [Name]
       FROM dbo.People

    I now get this: 

       Name
    -- -------
    1  Bob
    2  Gail
    2  Steve


    Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move.

    While it might appear in some queries that you are getting consistent ordering of results, don’t confuse coincidence with causality. You might live on those assumptions for years, building code on them, and then make a few changes and lots of things break.

    If you need ordering, use ORDER BY.