Voice of the DBA

Tag: Database Weekly

  • The Flash Database

    I caught this piece on the impact of flash storage on database engine design this week. It’s interesting as there’s been debate for years as to whether SQL Server should alter its behavior if it detects SSDs being used for storage instead of physical disks. It doesn’t, and perhaps that’s fine, though the article makes me think there are performance gains to be had if behavior changes.

    The article really looks at a few of the NoSQL products, though the design changes aren’t necessarily specifically limited to those products. I particularly thought that two ideas in the piece were interesting: indexes in memory with data on disk and the realization that threading can be he bottleneck with SSDs. I’m not sure if Windows and/or SQL Server could use these ideas, but they are interesting.

    I do wonder sometimes if a little more control of indexes would be helpful in SQL Server. Imagine if I could limit a large slice of memory to strictly non-clustered indexes and then have other data on SSDs. Would there be a way to tune SQL Server to run better for some workloads? Perhaps the algorithsm that choose query plans would change if they knew a scan of an NCI could be completed in a fraction of the time that a seek took place on a CI? Maybe we’d be willing to perform more seeks on in-memory indexes before performing lookups on disk.

    The idea of more concurrent operations, requiring more threads, also seems to be an area where I could suspect that both Windows Server and SQL Server could benefit from SSDs. If the systems changed their read and write algorithms and used many more threads with SSDs, could we get more throughput? Should our systems be more aware of how many controllers and paths might be on a system? I wonder, especially as some of this hardware becomes cheaper and cheaper. I could certainly see more organizations looking at using lots of smaller SSDs for a few servers that require high performance than a SAN.

    However it’s not as though SQL Server isn’t trying to take advantage of technology changes. The In-Memory OLTP system  and Buffer Pool Extensions in SQL Server 2014 are designed to take advantage of more memory and SSDs to dramatically improve performance. I don’t know what else might be coming in the next version of SQL Server, but I do hope that as new ideas emerge, SQL Server considers taking advantage of them.

  • Data is Not Always Enough

    As data professionals, our jobs deal with ensuring that there is data available, accurate, and relevant to the organizations in which we work. However we often go beyond the simple maintenance, gathering, and manipulation of data to help our clients and customers understand the information contained in our systems. In many of my positions, whether as developer or DBA, I’ve been tasked with working closely with business people to make decisions based on data.

    I’ve always been of the opinion that more data is better, though I certainly understand the problems of Big Data and the potential to reveal false patterns or overwhelm systems with conflicting information. The better I understand the domain, and the better educated the business analysts are, the better we can work to extract information from databases.

    However that’s not always enough to help an organization act. I read an interesting piece this week on data in sports, which talks about one of the NHL team’s managers using data to make decisions on how to restructure his team. It’s interesting to see that additional data gives the general manager more insight, and leads him towards a decision, but the rest of the organization can’t follow through. In this case it’s a matter of money, but resources constrain our efforts in many decisions. I think this is a place where more flexible, and perhaps more in-depth, BI-type analysis of what-if scenarios can be more helpful.

    The other piece that caught my eye had to do with real estate, and housing prices, with the author bemoaning the lack of data in determining the value of a house. It’s interesting, but to me, it’s flawed. More data won’t help because houses aren’t like many other commodities. One house is not fungible with another one, and the market is both fast moving, and inherently full of friction. We rarely buy a house without visiting it, a task that consumes time, and slows the movement of information through the system. I’m as frustrated as the author, but I don’t know that more data would help in this case.

    Data is important, and it should be a part of our decisions, but we should remember that data isn’t necessarily going to make the decision for us. We need to be ready to incorporate our own knowledge and judgment into a data set to help us decide on a course of action.

    Steve Jones

     

  • Patching Problems

    I applied the Windows 8.1 update a few weeks ago and had some issues with my machine. Windows was fine, but I lost my SQL Server service. A few others, including some of the SQLServerCentral community also had issues, but their fixes didn’t work for me. It was OK, because the problems gave me a chance to use PoSh to solve a real problem. I’ll be blogging about that in the next week.

    However the 8.1 update has caused lots of issues, and Microsoft is acknowledging these problems. That’s good, but the process gives me pause, and to a large extent, I think this makes more and more people suspect about all of Microsoft’s patching processes. I bet there are companies that feel justified in waiting for SP1 for SQL Server 2014 before upgrading, even though there is a chance that the patch will cause problems itself.
    This is one reason I’ve been hesitant to remain current with Cumulative Updates (CUs). Microsoft doesn’t stand behind them, with the text on each CU page that users should only apply the patch if they are experiencing specific problems. Otherwise users are told to wait for the next Service Pack, which seem to be coming less and less.
    Any patch can cause issues, and I certainly don’t like the idea of automatic updates always being applied because if there are issues, they can become much more widespread than controlled updates. There is also the issue of vendor responsiveness. Microsoft has pushed out patches that caused issues, and while they’ve been responsive, I don’t want to have all of my desktops, or all of my SQL Servers, down because of a bad patch.
    I don’t know how we patch in a more effective manner, but I do know that I want to have some control over updates as an end user, and I also want ways to remove patches. Moving to the app model of always applying patches over patches, and never rolling back seems to be a step in the wrong direction.
    Steve Jones

  • Performance Tuning Through Panic

    We’ve all been there at one time or another: panicked by a performance issue. I once upgraded a FoxPro for DOS application to a VFP front end with a SQL Server back end. We had a much more powerful server, generations ahead of the previous file server share. When the first users logged in and searches ran slower, I was in a panic. My team worried about the size of our inventory data in memory and quickly upgraded our 4GB, 1996-era server to 8GB of RAM, hoping for improvement. We also hurriedly split out queries and separated some data in an effort at quick tuning. It wasn’t much above a knee-jerk reaction, but we had users, and management, yelling at us on an hourly basis.

    I’m sure many of you have had other performance problems that resulted in some panic during your career. I’ve had more since then, and will probably have more in the future. Many of us do, and many of us perform knee-jerk performance tuning. However you can, and should, avoid tuning in a panic, as pointed out by Paul Randal. Doing so usually doesn’t fix the problem, and when it reoccurs, it’s more embarrassing with people less likely to listen to your next great idea.

    Tuning a system is a science, but it’s a complex science. Without experience, without having knowledge about all the complex interactions, and a deep understanding of how all the parts fit together, it often looks like a hopeless task. Who can keep all that information in their head and relate all the various components of the application? The network, the OS, the SAN, clients, all can cause problems that appear to be in the database.  As Paul points out, failing to understand the actual root cause can lead you to make changes that will have no effect on the actual underlying issue. You’ll often realize this when the problem re-occurs in a short time.

    I think one of the best reason to hire consultants is that they see so many problems that they can recognize patterns in performance that many of us don’t see. What they do appears to be an art because they can seemingly understand what information is relevant, and what isn’t. They walk down a list of potential issues, gathering evidence in a logical way, but one that can appear random to someone with much less knowledge and experience.

    All of us can learn more, we can read about problems others have had and learn how they solved the issue. However we often face the same reaction that many consultants do: no one wants to implement the advice. Until clients and vendor are willing to tune their code, many problems will only be masked by hardware.

    For a time. Then they’ll reappear.

    Steve Jones