SQL SERVER – Demo Script – Keeping CPU Busy

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)

Previous Post
SQLAuthority News – Uncut and Unedited Video Interview of Pinal Dave
Next Post
SQL SERVER – Concurrency Problems and their Relationship with Isolation Level

Related Posts

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.

    Reply
  • aasim abdullah
    March 3, 2011 3:21 pm

    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 = ‘#’

    Reply
  • aasim abdullah
    March 3, 2011 3:23 pm

    Yeah why not

    ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
    ON [Sales].[SalesOrderDetail] REBUILD
    GO 1000

    Reply
  • 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.

    Reply
  • Interesting scripts, I guess Looping operations in T-SQL would increase CPU Activity.

    Reply
  • Interesting scripts. But I want to know is there any script to know the CPU is idle since 10 minutes.Anyone please.

    Reply
  • 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

    Reply

Leave a Reply

Menu