Voice of the DBA

Author: way0utwest

  • Controlling Alerts

    None of us likes to be on call for our organizations. Late night phone calls, especially for something silly like adding a new user to a system or rebooting a machine, can make for irritable employees in the morning. Too many nights in a row of problem calls, legitimate or not, and I’d argue that an employee’s effectiveness during the day is substantially diminished.

    Since many of us are paid to be creative and improve systems over time, is regular firefighting a good use of our knowledge? This might be even more true if we find ourselves receiving escalation calls on a regular basis and our daily workload doesn’t change.

    I ran across a company that had set up a rule for alerts and pages. If any alert was set up in an automated system, it had to have corresponding documentation, with a resolution, in an operations manual. That’s an interesting idea, and certainly promotes the idea of performing root cause analysis and understanding your systems in advance. However does this really reduce any escalation? 

    Perhaps if there is a team available 24/7 that triages initial alerts this helps, but if there’s a small team of people that must support systems, is it worth the effort to document every issue? I suspect that a better policy is that repeating alerts, those that occur regularly, need to be analyzed and a more permanent solution implemented. I know that’s easy to say and hard to do, but that really is the best policy. At least if you want to ensure your employees are working efficiently over time.

    For those calls that are ad hoc, I recommend a log of actions taken be kept in an easily accessible location, in a text format for searching. That way if a different person receives a similar alert, at least they’ll have some idea of what actions the last person performed.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Refactoring Mistakes Are Why We Write Tests

    I wrote a short piece the other day trying to show how one can use tSQLt to test code. It’s a simple test built against a user defined function. It works well and when the test is run, it passes.

    Here was my code:

    ALTER function [dbo].[calculateEstimateOfReadingTime] ( @value varchar(max) )
returns int
as
    begin
      declare
        @ret as int = 1
      , @i as int = 1;
      while @i <= len(@value)
        begin
          if substring(@value, @i, 1) = ' '
            begin
              set @ret = @ret + 1;
            end
          set @i = @i + 1;
        end
      return @ret / 250;
;    end

    Someone in the comments pointed out that we can more efficiently refactor this code to :

    ALTER FUNCTION [dbo].[calculateEstimateOfReadingTime] 
( 
    @value varchar(max) 
)
RETURNS int
AS
BEGIN
RETURN (
    SELECT LEN(@value) - LEN(REPLACE(RTRIM(@value), ' ', '')) + 1
)
END

    However when I run the test, I get these results:

    functiontesta

    That’s not good, but that’s why we test.

    I could easily see someone refactoring the code, finding a more elegant method of rewriting this code and after running some quick tests, they check this in to source control (hopefully) and maybe deploy it to production. Hopefully QA catches this, but wouldn’t we want to notice this in development?

    The refactored code misses a divide by 250.

    Write tests, use them to catch mistakes. These simple ones slip through at times and are what make deployments really, really stressful.

  • VMs are not VMs

    I was at VMWare recently. One of the main things that all of the SQL Server professionals that were there tried to emphasize is that SQL Server workloads are not like other workloads. The impact on the various host resources, the stress on the storage systems, these are fundamentally different in a database server. The loads tend to be higher, but not always, however the tolerance for delays tends to be lower than for many other types of applications.

    This becomes an issue if you work in an organization that doesn’t understand the challenges of database systems. It’s entirely possible that your virtualization administators, or your storage administrators don’t recognize that the SQL Server might need more resources. Or they don’t believe the impact is greater for the organization. To be fair, that might be true, but someone other than the DBA or system administrator should decide if the database is more important than the file server and should be treated differently from an infrastructure perspective.

    No matter what level of resources your database server need, it’s not going to run like other systems. Typically this means that the density of VMs has to change when a database server is involved. As an example, I know of a system that typically has a 10:1 guest:host ratio for most of their server systems. However for SQL Servers it’s 4:1 or lower. The same is true for storage. Aggregate bandwidth doesn’t always reflect the ability of a storage system to keep up with database requests. It becomes important that both you and your storage administrators learn to speak the same language and understand what requirements exist for SQL Server VMs.

    Virtualization really starts to highlight the advantages of a DevOps environment. DBAs and developers should work closely with the virtualization and storage administrators to learn what each others’ requirements are and how each of us can help the other perform their particular job at a higher level. Infrastructure staff can help prepare standard environments and ensure production looks like staging. Developers and DBAs can help a vSphere admin learn a little PowerCLI and programming. That might get them to be more cognizant of the particular requirements of your SQL Server and be more willing to work with you.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and LibSyn.

     

  • PostgreSQL v SQL Server

    A site that’s a comparison of the platforms.

    http://www.pg-versus-ms.com/

    Certainly some good arguments being made, but (admittedly) biased towards specific workload items that the author uses.

    However, I agree with lots of the items listed. There are quite a few items, such as 1.1 and 1.2. Not having 1.5 is stupid silly. I know I can write a CLR assembly and stick this in here, but why hasn’t it just been included? 1.6 is interesting for sure and as someone that lives in BOL, it is very frustrating.

    Without a doubt there are some cumbersome things in SQL Server that I think could be made much, much easier. However, as pointed out numerous times, MS is concerned about money, and not necessarily making the product better.

    I don’t agree with the Linux v Windows debate. Lots and lots of people run Windows and the latest surveys I’ve seen of servers from automated places show Windows as close in terms of numbers.

    I’m also not sure some of the BI stuff matters a lot in a general comparison, but if that’s what you do, then this is interesting. I’d also point out that since MS is trying to move into these areas, some of these things are being addressed. SQL Server 2016 will support JSON and R.

    Enjoy the read. Don’t get too wrapped up in the details, but certainly if you think some of these items are important, let MS know.