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 (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Demo Script – Keeping CPU Busy

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

  2. 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.

  3. Pingback: SQL SERVER – T-SQL Script to Keep CPU Busy | SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s