Voice of the DBA

Tag: Performance

  • Instrumentation

    In a discussion recently someone mentioned that they built in timing mechanisms into their application so they were aware of how long certain modules or functions were executing. Using this data, they could easily determine if the system was performing poorly by comparing the average of timings to the current performance.

    I thought that was a great idea, and it shows some proactive, forward thinking on the part of the developer. I haven’t often seen this in SQL Server, though it does seem that many good DBAs maintain some type of overall baseline for their database servers in order to help them response to performance issues.

    For a Friday poll, I was curious how many of you actually think about monitoring your systems. I decided to ask this in a general way to get an idea of what others might be doing inside SQL Server as well as in the applications that connect to SQL Server.

    Do you build instrumentation into your applications and code to measure the performance?

    I’m wondering if you actually collect, log, and even analyze data on how long processes take, or how much data you work with. Do you capture row counts from data loads or processing? Do you maintain an average time for code to execute?

    I haven’t done a lot of this in code. There was one application which watched for files and loaded them every day. We built code into SQL Server that tracked how long the load took, and how many rows were processed, mostly as a way to prepare for future hardware upgrades. As a production DBA, I have typically kept an average on size and time of backups for the same reason. It helps me capacity plan, and alerts me to growing loads for the server.

    If you do anything differently, or if you’ve found this to be valuable, or even a waste of time, let us know this Friday.

    Steve Jones

  • Appliances – Just Add Data

    The first release of a large data appliance from Microsoft is the SQL Server 2008 R2 Parallel Data Warehouse Edition in which you buy a rack of pre-configured servers that you access with tools and perform limited management on. I was reminded of this as I read about Teradata’s new product strategy, it seemed that they have integrated an appliance in there as well.

    Appliances bring some value to customers by supposedly reducing some of the tuning, configuration, setup, etc. costs in setting up a system. Hopefully the also reduce some ongoing costs that might come from mis-configuration, things running slowly, etc. How many times has an inexperienced person setup a server in a way that causes issues for an application?

    However appliances tend to be expensive. They have to be if there is any level of support from the vendors in custom tuning. That limits how many places will purchase them, and there is also some level of distrust and fear from IT workers that worry about being displaced by a machine. Almost funny when you think about many of our systems displacing other jobs.

    Appliances haven’t had great success in the past, outside of network devices, but I wonder if we might be better off examining them more in the future. Rather than trying to have general database instances put together by IT groups, should we have small, modular appliances that handle various database services and then just write the code and supply the data. For this Friday, let me know what you think:

    Would you like to see small, modular data appliances that you connect to storage and then just add data?

    Steve Jones

  • The DBA Financial Analyst

    The DBA job is somewhat nebulous, with a variety of skills being needed, and the mix of those skills varying from company to company. As companies look to save money, and look to perhaps go with cloud computing or other types of hosted environments, are DBAs going to need to also perform financial calculations?

    I was reading a post on SQL Azure recently that talked about the cost of indexes. Not performance, or space, but actual real dollars and cents cost since indexes take up space and you are charged by the space you use. Adding extra indexes, or adding columns to indexes, or adding covering columns, can cost you real money if you have any large scale of resources.

    So does that mean that in addition to trying to determine what changes you can make to code to improve performance, or what new features might make sense to your applications, you need to also run some financial ROI calculation on the choices? Maybe PowerPivot was released just in time for all those virtual systems that might charge by the byte or cycle.

    As a DBA, I’ve had to budget, and try to determine  how to spend funds for hardware, balancing out choices for RAM v disk v CPU, and even comparing secondary servers for read-only scale out against a larger single server. However I typically don’t have to compare the cost of features in my analysis.

    I’ve sometimes wished I could pay for just one feature, and it’s starting to look like that might be something that could happen in the cloud. While the possibility if mix and match features intrigues me, the extra work to add cost to my calculations along with performance makes me think the cloud might be more trouble than it’s worth.

    Steve Jones

  • SubQuery Performance

    Why would you do this?

    select distinct(hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
         and hostname=t.hostname
     group by hostname),
    (    select count(*) as criticalCnt 
     from @temp where severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         and hostname=t.hostname
     group by hostname)
         from @temp t

    I ran into this on a post where someone had asked about how to basically call a CASE statement. The posted didn’t know how and someone posted this as a way to tally the various counts of alerts.

    I had glossed over it when I saw it, but when someone else replied with this statement, saying performance was better, I decided to look at see how much better.

    select hostname,
    sum(case when severity_guid='0168A833-1732-411E-8205-C2F6CD91737D' 
         then 1 else 0 end) as [Count_Of_0168A833-1732-411E-8205-C2F6CD91737D]
    ,sum(case when severity_guid='CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' 
    then 1 else 0 end) as [Count_Of_CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5]
    ,sum(case when severity_guid='C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' 
         then 1 else 0 end) as [Count_Of_C4CF8A23-A106-4617-BAB0-94DA3CA74EF1]
     from @temp 
    group by hostname

    I set the statistics on for these queries and found these results.

    Query 1:

    Table ‘#45544755’. Scan count 52, logical reads 52, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Query 2:

    Table ‘#473C8FC7’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    That’s a huge difference. If this were to be run with any significant frequency on a system, you’d be using (4 x the number of rows) as many scans of the data than you needed to. Even though they are logical scans, it’s still CPU and memory movement you are requiring, and if this were a significant amount of data.

    You can also see a drastic difference in the execution plans:

    ScanPlan1

    ScanPlan2

    I’ll let you figure out which plan goes to which query.

    This is a simple example, but it shows where someone really can get poor performance over time with badly written SQL. It’s nice to have various ways to solve problems, but you also want to choose the appropriate tool. Subqueries make sense at times, but this isn’t one of them.