Skip navigation

Every DBA has at some point asked: “What the heck happened to my database?” In this blog, we’ll review some of the more common scenarios where some little detail slips through the proverbial crack and that often results in some kind of database anomaly which momentarily raises tensions and blood pressures.

Supposedly nothing is new or has changed, yet without any obvious reason, database performance has suddenly noticeably slowed. Some possible causes and effects to look for:

  1. Database size has grown and crossed some threshold at which performance characteristics have changed either due to physical design limitation, query optimizer weakness, database statistics inadequacy, hardware resource bottlenecks, and numerous other potential issues caused simply by an increase in size reaching some inflection point. While this may appeal as the candidate of choice, it’s simply not often the sole authentic reason – but it can happen.
  2. Database object and/or metadata health have been become compromised by some unplanned and infrequently occurring boundary condition resulting in an unbalanced state. For example, an index may become unbalanced, statistics may become stale, a partition expansion might be problematic, and many other conceivably looming glitches exist to make life interesting. For most seasoned DBAs these types of issues are not that hard to spot and fairly easy to correct.
  3. Unenvisioned skews or imbalances in database workloads might occur whereby normal tasks and operations are radically impacted in an unforeseen manner. For example, at quarter end business users may submit too many reports all at once, or more likely some scheduled processes or jobs may overlap their execution in a manner that overly stresses the hardware and software resources. I can state from experience that this last issue was the most common issue in the production databases that I managed. As a DBA I did more job schedule balancing than I could have ever imagined.

This last issue has one more wrinkle to be aware of, virtualization. Now the DBA might not be able to as easily identify resource issues due to the shared nature of the resources adding an additional layer of complexity. Moreover, the virtualization infrastructure might further complicate proper forensic analysis. Assume that your database server virtual machine (VM) can be dynamically relocated to overcome some resource imbalances. Now imagine that a performance problem occurs this month, but that the dynamic VM relocation masks the impact of the issue. Thus, when the problem becomes perceptible the first question might well be when did it really first occur? It may have been a problem for weeks or months.

Now let’s move that same database server to the cloud.  Not only do you have the same virtualization related concerns about problems potentially being masked, but now there could be questions about whether we have purchased the correctly sized cloud components from the ever-growing catalog of deployment options available. Moreover, are we on shared, elastic disk storage when we should have maybe more properly chosen dedicated SSD or NVMe storage? What about network latency, are we split across regions or availability zones for an application whose nature requires something different? One could argue that one of the most useful artificial intelligence or machine learning applications the market needs is a cloud monitor, governor and advice tool. There simply aren’t enough knowledgeable experts on proper cloud provisioning. Most people are essentially stumbling their way through given that it’s so easy to scale up when wrong – it just costs more money.

Don’t get me wrong, I love the cloud. I am just smart enough to know that I don’t know all the answers and there are far fewer people who do at the current time than we really need. Not even the big cloud providers can hire enough people fast enough and train them to fill this gap. Therefore, for the next few years we are probably in for some interesting times. I think Robert Kennedy’s quote most apropos:

Like it or not, we live in interesting times. They are times of danger and uncertainty, but they are also the most creative of any time in the history of mankind. And everyone here will ultimately be judged - will ultimately judge himself - on the effort he has contributed to building a new world society and the extent to which his ideals and goals have shaped that effort.

It’s not uncommon to observe rampant over reliance of database application code on TEMP tables, most often as a SQL language programming crutch. I have a theory as to why. Programmers are chiefly taught record oriented or “record at a time” processing logic as for accessing data. Some lucky students might experience some exposure to a set oriented language such as LIS, but usually insufficient to adequately prepare them for SQL’s set based foundation. As such they often muddle along as best they can – which most often means liberally using TEMP tables. Now while there are legitimate cases where a TEMP table makes sense and is required, it should be the exception rather than the rule. If nothing else, it should at least be the choice of last resort after attempting to accomplish the task within a Single SQL statement.

 

I also believe that the way programmers think is more about “how” vs. “what”. You’ve most likely heard the theory about “right-brained” vs “left-brained. I content that “how” is more analytical and methodical thinking and thus left-brained. Therefore “what” is more creative or artistic, and therefore right-brained. While this observation is more opinion than fact, evidence seems to fit such a conclusion. For now let’s accept that premise and see how it might compound this problem of over reliance on TEMP tables.

 

The business person might state a problem like this, what percentage of our employees are making 20% more than their department average in our largest 10% of departments? But the programmer tends to dissect that into several smaller problems:

  • What are the largest 10% of departments by employee head count
  • How many employees per department for those large departments
  • What is average salary per department for those large departments
  • How many employees in a large department are paid 20% > average
  • How many employees are there in total
  • What is the percentage of those making more than average overall

 

There’s nothing wrong with dissecting a problem per se. Remember we all learned about the advantages of the “divide and conquer” approach to problem solving and coding. The main problem is that a “record at a time” attitude will quite often result in multiple SQL statements with intermediate results stored in TEMP tables to be passed on to the following SQL statement. You might argue “So what as the correct result is returned and that’s what matters?” However by subdividing the original problem into multiple smaller ones and thus multiple SQL statements, the database SQL query optimizer can only formulate execution plans for those smaller SQL statements rather than potentially finding a superior approach when the whole problem is presented as a single SQL statement. Moreover creating TEMP tables will consume disk space which when later dropped could end up fragmenting your storage space. Plus you may need to create indexes on your TEMP tables to duplicate what may already exist on the table, so once again it could exasperate your storage issues.

I used a simple data model with just two tables as shown here.

 

xxx0.png

 

The single SQL statement query to obtain the answer that I came up with for SQL Server was as follows (there could of course be other answers which might be superior – so please comment your ideas):

 

with num_emps as (select count(eno) as xcnt from emp),

     avg_dsal as (select dno, count(eno) as xcnt, avg(salary) as xavg

                     from emp group by dno),

        dep_data as (select count(*) as xcnt

                     from emp join avg_dsal on emp.dno=avg_dsal.dno

                  where emp.salary>avg_dsal.xavg*1.2

                  and avg_dsal.xcnt in (select top 10 percent

                                                   count(*) as xcnt from dept))

select dep_data.xcnt, num_emps.xcnt, CONVERT(VARCHAR(50),

          cast(dep_data.xcnt as decimal)/

          cast(num_emps.xcnt as decimal)*100)+' %' AS [%]

from dep_data, num_emps

 

The result for my sample data which I calculated by hand to make sure was 100% correct was:

 

xxx1.png

 

While writing a query such as this might take more time, I suggest that it’s well worth the time for all the reasons already stated. I also think from a maintainability and readability standpoint it’s also superior. I often have to ask people who create TEMP tables lots of questions when I have to work on their code.