How to create a query which kill CPU?

Question

Post reply

How to create a query which kill CPU?

b.ozga

SSCommitted

Points: 1891
More actions
March 12, 2013 at 7:28 am

#269773

Hi
For some tests I need to create a query or set of queries which overloads my test machine which is quite powerful, two 6 cores CPUs and 50GB RAM. I was trying to create multiple joins, aggregates, UDFs but nothing is able to make the CPUs suffering. Also linked servers usage which influenced CPU greater degree than previous combinations haven’t even loaded CPU till 50%.
Do you think it’s it possible to overload CPUs on such powerful machine?
Do you know some tips to create really heavy query which can kill machine 😀 ?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Set up a million-row (or more) test table (see Jeff Moden's articles) and do a triangular join running totals on it.

Gather index frag stats. This one is from Glen Berry's blog[/url]:

-- Get fragmentation info for all indexes above a certain size in the current database

-- Note: This could take some time on a very large database

SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],

i.name AS [Index Name], ps.index_id, index_type_desc,

avg_fragmentation_in_percent, fragment_count, page_count

FROM sys.dm_db_index_physical_stats( NULL,NULL, NULL, NULL ,'LIMITED') AS ps

INNER JOIN sys.indexes AS i

ON ps.[object_id] = i.[object_id]

AND ps.index_id = i.index_id

WHERE database_id = DB_ID()

--AND page_count > 500

ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

b.ozga SSCommitted Points: 1891 More actions · Answer 2

Thanks Chris,

As I wrote I was using multiple joins with aggregations on my test tables which have many millions of rows.

This index query is not making any difference for a CPU load for my big test db with lot of indexes.

Any other ideas how to produce such query or reconfigure SQL Server?

Thanks in advance!

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Create an endless loop.

Something like this:

SELECT *

INTO #columns

FROM sys.columns AS C

DECLARE @RCNT int

SET @RCNT = 0

SET NOCOUNT ON;

WHILE @RCNT = 0

BEGIN

UPDATE #columns

SET column_id = 0

WHERE ISNUMERIC(CONVERT(float, CONVERT(nvarchar(20), CONVERT(varchar(20), column_id)))) = 0

SELECT @RCNT = @@ROWCOUNT

END

Pretty successfully brings my CPU to 65%.

Run it in 2 or more SSMS windows if you need more.

_____________
Code for TallyGenerator

b.ozga SSCommitted Points: 1891 More actions · Answer 4

Thanks Sergiy for the query!

I was able to overload my CPU with it and with the A.Mechanic tool http://www.datamanipulation.net/SQLQueryStress

The query was run in multiple threads.