SQL Queries and CPU usage

Details: Category: SQL; Published: Thursday, 28 April 2016 22:35; Written by Super User; Hits: 1201

Many companies today that have invested in expensive hardware, and are running their applications and databases on it, are experiencing CPU constraints, either overall or at certain peak times in their application or database hosts or even both. In regarding of the database host then 70 % to 80 % of the cases it is due to poorly written SQL queries that are executed against the database either just once or multiple times. Many of these companies invest in bigger and faster hardware to solve the problem. However these investments can in most cases be skipped and the problem solved in a matter of minutes and/or much cheaper way using built in tools in the database or additional GUI tools that are either bought for a small license fee or even free of charge.
Solving CPU intensive SQL queries in cheaper way.

When building sql queries it becomes very crucial to build it in such way that the queries are as efficient as possible. Why ? It all comes down to performance. i.e the application performance. When an application runs a query against a database, either residing on the same host as the application or a different deticated database host, it consumes certain amount of CPU usage. This CPU usage can range from something under 1 % up to almost 100% of the CPU usage depending on data, table layout, database config but most of all how the query is written. A very badly written SQL query can put down a busy host in a matter of minutes or seconds in certain scenarios.

Every database uses a certain accesspath to retrieve date that a SQL query is asking for. A good accesspath is the key to a fast retrieval of data. These accesspaths can be displayed in either text format or graphical format in tools/utilities giving the developer or DBA better understanding how the query is accessing the data. These method/utility is called EXPLAIN. Graphical tools call the database built in EXPLAIN for a certain query and displays the accesspath in a graphical structure format. Explaining a query usually takes not longer than several seconds or up to a minute and gives you a clear understanding on how the data is accessed. It also calculates in certain database systems (like DB2 and Oracle) the weighted cpu cost and displays it in timerons¹, and best weighted timerons that a query can get is 1. Note that I would consider anything under 100 adequate in rare cases although I prefer values under 20. ANYTHING above 1000 timerons should be considered VERY BAD query. The lower these CPU (timerons) values are for a query the more likely it is that the query is not CPU intensive and hence lower the risk of CPU constraints. Explain also indicates if indexes are missing on tables for that particular query. You will see missing indexes where there are indications of TABLESCAN on a table. Adding an index that matches the where clause columns of the query for that particular table eliminates TABLESCANS hence lowering the CPU cost and faster retrieval of data. If that does not help rewriting the query becomes necessary.
Very simple Workioad Scenarios

Let's look at several very simple imaginery workloads scenarios with only 5 queries that consume the whole CPU of a database machine. Given these 4 workload scenarios below we see that in workload(1) there is a query (3) which has 40 % of the CPU and in the next workload(2) that query has risen to 60 % of the CPU. This query would be our first candidate to tune. In the workload(1) we see that we have 10 % free CPU time. The workload(2) is a scenario where the query(3) is causing the host to enter a massive swapping or CPU constraints which, if not reacted in a timely manner, can bring the host down.

Query CPU table

After tuning the query(3) we see that the query CPU cost decreases in workload(3) around 83% and with some additional fine tuning, the CPU decreases around 90% from the workload(2) scenario.2 Tuning the other queries as well leaves us with 70 % to 80 % free CPU time for additional query workload which can be added to the database host without any additional hardware cost.

Looking at these CPU consumption in a stacked column chart like this (below) gives you a clear view of the benefits that these CPU savings gives your application and database environment.

Query CPU chart

Even though these workloads scenarios are imaginery you can get a picture on what the benefit it is to use good and cheap/free EXPLAIN tools to solve your CPU intensive query workload and use your expensive hardware in the most optimal way as possible leaving room for more workload to be added and best of all skipping the hardware upgrade until later.

^{1) IBM Defenition of a CPU cost A timeron is a unit of measurement used to give a rough relative estimate of the resources, or cost, required by the database server to execute two plans for the same query. The resources calculated in the estimate include weighted CPU and I/O costs.}

^{2) Note that In a real workload scenarios I have helped rewriting some very bad SQL queries with a performance benefits of 99,9995 %.}