Unanswered: SQL Queries/Stored Procedure for Spiking CPU Utilization
I am a Performance Engineer and as part of a project assignment my manager requirement is simulate DB CPU Processor Utilization in QA Environment matching with Production Environment.
For Example if Production CPU Usage is 15% and QA is 5 %, rest 10% CPU spiking should be achieved by writing a stored procedure or SQL query which jacks up CPU Utilization from 5 to 15%.
I am not an expert in this area and need help in achieving this, though i am writing some sort of SQL logic or a stored procedure which inserts data into tables for excessively i am not sure to what extent this might spike the CPU Utilization in DB, i believe there is some sophisticated way to do this.
Need pointers on how we can write queries or SQL Logic which will spike up CPU Utilization.
I am using IBM Data Studio and Db2 database.
I am not sure to provide more details on DB2 Version etc....though i tried to execute the commands provided to get version and license etc...
To make sure you are only burning CPU and nothing else, you need to do CPU intensive activities. The easiest to to tablescan a table that is totally in a bufferpool. Create a table and fill it with lots of data. Make sure the bufferpool for this table is large enough to hold the data for the entire table. Then issues queries against the table. After a bit the bufferpool should have all of the data loaded and you will see the CPU utilization increase.