Keeping CPU busy on purpose is handy when you need to demo a high CPU problem. Recently face very interesting situation, during presentations at event, I was asked very famous questions:
“My CPU is very high all the time, how can I reduce it?”
This is very interesting question and there are many answers and a single blog post is not good enough to justify this subject. I presented few situation to the person who asked the question. The member of the audience who asked question came to me afterwords and asked me few detailed questions. To answer him, I quickly wrote query which simulate high CPU. Here is the script which I wrote which increased CPU from 10% to 80%. I was wondering if there is any similar script which can simulate high CPU usage. If you have share with me and I will publish with due credit.
Here is my script for the same:
USE AdventureWorks GO DECLARE @Flag INT SET @Flag = 1 WHILE(@Flag < 1000) BEGIN ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] REBUILD SET @Flag = @Flag + 1 END GO
Safe Ways to Practice Keeping CPU Busy
Please run this script only on a test or demo server. Rebuilding the same index again and again does not just burn CPU. It also reads and writes a lot of data, and in the full recovery model every rebuild is fully logged, so the transaction log can grow fast. Stop it by cancelling the query in SSMS when your demo is done.
While the loop runs, you can practice the real skill, which is finding what uses the CPU. Try these checks:
- Open Performance Monitor or Task Manager and confirm that sqlservr.exe is the process using the processor.
- Query
sys.dm_exec_requestsand sort bycpu_timeto see which running request is the busy one. - Query
sys.dm_exec_query_statsand sort bytotal_worker_timeto find the cached queries that used the most CPU over time.
On a real server, the cause is rarely a loop like this one. High CPU often comes from a few heavy queries, such as large scans, implicit conversions or a plan that was built for very different parameter values. Find those first, tune them, and measure again. Keeping CPU busy on purpose is a good way to rehearse that routine before you need it for real.
Want to try this yourself? Here is how to install AdventureWorks.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
That script you have above will generate CPU and IO activity.
If you only want to max out your CPU for an extended period of time, run the T-SQL mandlebrot script ( ) with a GO 100 at the end.
EXEC sp_msForEachDb
@command1=’IF ”#” NOT IN (”master”, ”model”, ”msdb”, ”pubs”, ”tempdb”) BEGIN PRINT ”#”;
EXEC #.dbo.sp_msForEachTable ”UPDATE STATISTICS ? WITH FULLSCAN”, @command2=”PRINT CONVERT(VARCHAR, GETDATE(), 9) + ”” – ? Stats Updated””” END’,
@replaceChar = ‘#’
Yeah why not
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
ON [Sales].[SalesOrderDetail] REBUILD
GO 1000
My contribution:
Declare @T DateTime,
@F Bigint;
Set @T=GetDate();
While DATEADD(Second,180,@T)>GETDATE()
Set @F=POWER(2,30);
I reached 50% in SQL Server Activity Monitor
And 100% in Windows Task Manager.
Interesting scripts, I guess Looping operations in T-SQL would increase CPU Activity.
Interesting scripts. But I want to know is there any script to know the CPU is idle since 10 minutes.Anyone please.
USE master
SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id
JOIN sys.objects o3 ON o1.object_id < o3.object_id
SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM #temp o1
JOIN #temp o2 ON o1.MyInt < o2.MyInt