question

Bartoloz avatar image
Bartoloz asked

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-sqlperformancecputesting
10 |1200 characters needed characters left characters exceeded

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

Bartoloz avatar image
Bartoloz answered
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 comment
10 |1200 characters needed characters left characters exceeded

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

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 Likes 2 ·
Kev Riley avatar image
Kev Riley answered
You might want to look at Adam Machanic's SQLQueryStress tool at http://www.datamanipulation.net/SQLQueryStress
10 |1200 characters needed characters left characters exceeded

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

ThomasRushton avatar image
ThomasRushton answered
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 characters needed characters left characters exceeded

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

Tim avatar image
Tim answered
Get any of my developers to write some production code for you. :)
1 comment
10 |1200 characters needed characters left characters exceeded

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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