How to create a query which kill CPU?

Question

question

Bartoloz asked Mar 12, '13

How to create a query which kill CPU?

Hi, Maybe here I will find an answer for my question. 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? Thanks in advance!

t-sql performance cpu testing

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-03-13T12:31:54Z

Bartoloz answered Mar 13, '13

Thanks for ideas! I was able to successfully overload my machine up to 100% CPU by using combination of your advice (used A.Mechanic tool) and query which I got from Sergiy on SSC forum: 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 The query was added to the procedure which I ran by SQLQueryStress tool in 40 threads.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder commented · Mar 14, 2013 at 01:03 AM

Using the format SELECT * INTO #columns instead of creating the table first then using INSERT #columns SELECT ... locks the sysobjects table for the duration of the query. The prior format should never be used in production.

2 ·

Answer 2 · 2013-03-12T16:01:35Z

Kev Riley answered Mar 12, '13

You might want to look at Adam Machanic's SQLQueryStress tool at http://www.datamanipulation.net/SQLQueryStress

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2013-03-12T16:03:43Z

ThomasRushton answered Mar 12, '13

A Google search for [SQL Stress][1] brings up some useful utilities, such as [MS's own OStress][2] (part of the Replay Markup Language (RML) utilities pack). Combine that with a query such as the [one mentioned here][3], (found by [searching Google for SQL Stress CPU][4]) and you might be onto a goer. (That query doesn't impact the IO subsystem, but gets SQL Server to hammer itself with mathematical calculations.) [1]: https://www.google.co.uk/search?q=sql+stress [2]: http://support.microsoft.com/kb/944837 [3]: http://www.stevefibich.net/wordpress/2012/07/19/sql-stress-code-cpu-stressing/ [4]: https://www.google.co.uk/search?q=sql+stress+cpu

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2013-03-13T12:51:47Z

Tim answered Mar 13, '13

Get any of my developers to write some production code for you. :)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bartoloz commented · Mar 13, 2013 at 01:14 PM

With all due respect to developers, I thought the same but the machine seems to be a winner this time ;-)

1 ·

question