Voice of the DBA

Tag: sql server

  • 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

  • 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

  • Temporary Indexes

    Temporary placeholders can be valuable

    Recently I was studying for the MCM exam and one of the things that came up in my research was the idea of temporary indexes. I had never used them before and was intrigued by the idea. I have dropped indexes before a large load and then recreated them, but these are indexes I planned to keep on the table indefinitely.

    There are always processes that you run periodically on some schedule. These might be end of month, or end of year processes where you run a series of reports or queries that aren’t necessarily run at other times in your business cycle.

    Since this was a new concept for me, I wanted to ask and see how many others might actually be using this. Maybe you’ll get an idea for how to speed up some of the periodic, but very important, processes you run. For this Friday:

    Do you use temporary indexes?

    By temporary, I mean indexes that you create for a process and then discard at the end of a process. These could be indexes that you use to supplement some type of data load, perhaps indexes used in staging tables. Or they could be indexes that are added to some OLTP tables for the processing of certain reports in a short period of time.

    However you might implement them, let us know how you use them, and how they have improved performance for you.

    Steve Jones

    The Voice of the DBA Podcasts