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
Reference: Pinal Dave (https://blog.sqlauthority.com)
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