Voice of the DBA

Tag: Performance

  • Database Queries and Scalability

    I ran across this post with a very provocative title:  (thanks Brent Ozar Unlimited). It’s an interesting read from a developer, talking about how so many website designs aren’t built for scalable because they rely too heavily on a set of database queries place on every page. If you expect a database query to retrieve or store a user id, or other data on every page, then you are limited to the scalability of your database back end. There’s no doubt there, but the author has ideas that can help you overcome these issues.

    One of the problems is that systems read and write account information (or registrations) to the database directly. That’s definitely a scale issue, and the author suggests generating the client information on the web server and storing it in a more robust and scalable backend. That makes sense, but it’s with the caveat that ” As long as you eventually write the data to the db later on, you’re good to go.” Good point, but now you need a replication process of some sort (or messaging) that gets the data to your database. This ignores some of the synchronization issues across multiple servers as well, but it’s not a bad idea.

    The idea of avoiding joins, by using a cached set of data to iterate through and pull data from other tables may work in some cases, but I’m not sure this is the most efficient way of grabbing data from a database, ensuring it’s the latest data, and not impacting concurrency. This technique seems to invalidate the power of an RDBMS by not using joins to effectively, and efficiently, retrieve data. I suspect replicated copies of slowly changing data is a better way of scaling your system than avoiding joins.

    Overall I don’t love the article, and depending on which platforms and technologies you’re using, it might be better, or worse, advice. I would say that building better systems involves considering alternative techniques, allowing for failures, and using new techniques like messaging, caching, asynchronous processes and more. As we try to improve the quality of our software, we should be talking about, writing about, and trying out new techniques, learning what can work better in our environments.

    Steve Jones

    Video and Audio versions

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    Follow Steve Jones on Twitter to find links and database related items and announcements.
    Steve Jones Windows Media Video ( 22.3MB) feed

    MP4 iPod Video ( 25.7MB) feed

    MP3 Audio ( 5.2MB) feed

    Feeds are available at iTunes and Mevio

    To submit an article, rant or editorial,
    log in to the Contribution Center

  • Achieve Better Performance

    One of the hottest topics at most every SQL Server event I attend is performance. The sessions that talk about improving performance through various means are well attended, and that tells me a few things. One is this is an important topic, and two, people need to learn more about it.

    This past week I noticed a lot of performance related topics appearing in blogs and headlines. I’m not sure why there were so many this week, but I think it does deserve pointing out that this is a good week to beef up your skills in this area. I saw a little bit of something for everyone, no matter what your job.

    Database administrators, who often deal with the end product from developers and can make limited changes, should make sure they understand statistics, which can clue them into problems in a number of situations. You ought to be able to troubleshoot various issues, like blocking or tempdb issues. For those dealing with merge replication issues, you ought to ensure you understand how to deal with conflicts and resolve them. No matter who you are, you need to understand the impact of IO on your server, and that means ensuring you can measure the load. Measuring CPU can be tricky, but there’s an interesting idea from the SQL Server team this week.

    The developers, who can make changes early in the process of software development, really should learn to write better SQL. That means studying the various ways people solve problems and practicing using some of them. Do you know the issues with Scalar UDFs? Do you understand temporary objects? Can you PIVOT data? There are so many writings on T-SQL that you can’t keep up, but you should be getting better over time if you write T-SQL code in your job.Developers also should understand the basics of indexing, and deliver a few basic indexes along with their software. That’s the least you can do.

    This week we started to see various blogs on SQL Server 2014, especially the Hekaton feature. While I’m sure the next version of SQL Server will perform better and speed up some of your workloads, it can’t cover from fundamental mistakes, at least not completely. Do your career, and your current and future employers a favor and work on your performance skills this week.

  • What Do You Want to Know About I/O?

    Hope to see you at the conference.
    Hope to see you at the conference.

    There’s lots I want to know. What affect does an SSD have if I use it for a log file? One of my filegroups with indexes? What about tempdb? How do you dig in and prove latency to the storage groups from within SQL Server? Will the query optimizer take advantage of information about drive performance? There are lots of questions I have, and I’m hoping to get answers. I’m hoping to attend the “Ask Anything I/O” panel at SQL Intersection in a few weeks. I’m leaving the event Wed night, so unless there’s someone I’m trying to catch in a last minute meeting, I’ll be sitting in the audience.

    I am guessing that Brent Ozar, Kevin Farlee, and Mat Young will have lots of answers for me and others. I’m also guessing most of the questions will be met with references that are already out there, and I hope that the presenters will publish a list of questions and answers for attendees. That’s fine, as there are lots of times when I’m looking for information that has been published, but I can’t find in the ocean of Google results. Attending a session given by experts can be a way to shortcut some research and searching to find where the answers are located.

    That’s one of the reasons to attend a conference. Getting quick answers from experts, or learning how to better target your research. All the speakers at SQL Intersection are well known, longtime, expert SQL Server users who are friendly. They’re more than willing to answer questions, or help guide you to solve a problem or learn something new.

    The other great thing about conferences? You get to hear some great questions from other attendees. We all attack problems differently, and work in very diverse environments. There are many times I’ve heard how another person has implemented SQL Server and it’s intrigued, or even inspired, me to try something similar with my instances.

    If you can find the funding, SQL Intersection is a great event to attend, with an impressive list of speakers, each hand picked by Kimberly Tripp and Paul Randal of SQLskills. They provide amazing training in their Immersion events, and are trying to bring even more options to you with SQL Intersection. You can even walk away with something tangible in addition to all the knowledge you’ll gain: a Surface Tablet.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Default Fillfactor for an Index

    I ran down the rabbit hole on transaction logs recently. I started with Paul Randal’s post over at the SQL Sentry blog on trimming the transaction log, then went to his video on log analysis. I also glanced at the posts on index cleaning and what index stats don’t tell you. What started out as a quick “what can I do to help transaction logs perform better” became a few hours of reading, executing code and thinking.

    However the post that caught my eye was Paul’s post on choosing an index fill factor. I’ve seen various notes on the fact that fill factor can matter for performance and maintenance, but I haven’t often seen someone give some good concrete rules on what you should choose. In a nutshell, here’s Paul’s advice:

    1. Don’t set a system wide fill factor with sp_configure
    2. Start with 70 for specific indexes that seem to experience lots of fragmentation

    I like this advice. It’s simple, and easy to start using, although the caveat to #2 is that you need to monitor and perhaps adjust the fill factor (up or down from 70) and possibly change your maintenance schedule. I might lean towards leaving my maintenance alone, especially with a script like the SQL Fool Index Defrag Script running and playing with fill factor to ensure I minimized page splits.

    There’s also the trade-off of requiring more space for your index (and maintenance) if you move to 70 from 100.

    I do think that changing the system wide level is a bad idea. If you aren’t sure what your system wide fill factor is, here’s a post on checking it.