Well, I am looking to put together a SQL Server benchmarking tool using the AdventureWorksDW2008R2 database as a starting point.  I am running into trouble finding a SQL Script that will really stress a CPU inside the SQL Server Process so I went back to an old stand by, finding primes.  I’ve messed with prime finders in different languages over the years and I have even posted a few articles about them.  (Article 1, Article 2).  I developed two sql scripts, the first is your more standard procedural programing style you would see in C or similar languages just using T-SQL.  The second script takes advantage of the SQL Server engine and attacks the problem with a set based approach.

Script 1

SET NOCOUNT ON;
print sysdatetime();
declare @counter int;
declare @value int;
declare @prime bit;
declare @prime_table table
(prime int);
set @value=3;
while @value<100000
BEGIN
	set @prime=0;
	set @counter=3;
	while @counter<(@value/3)+1 /*Equal to 3 then 3 would have % of 0*/
	BEGIN
		if @value % @counter=0
		BEGIN
			set @prime=1;
			break;
		END
		set @counter+=2;
	END
	if @prime=0
print @value;
	set @value+=2;
END
print sysdatetime();

Script 2

SET NOCOUNT ON;
print sysdatetime();
declare @value int;
declare @5_flag tinyInt;
declare @prime_table table
(prime int);
set @value=3;
insert into @prime_table values(@value);
set @value+=2;
set @5_flag=0;
while @value<100000
BEGIN
	if not exists (select 1 from @prime_table where @value%prime=0 and prime<(@value/3)+1)
		insert into @prime_table values(@value);	
	set @value+=2;
END
select
*
from
@prime_table 
print sysdatetime();

Inside the SQL engine the set based approach is 10 times as fast as the non set based approach. (~4 seconds compared to 50 on one machine) Both of these scripts stress only one CPU core.  I’m working on a way to have it take advantage of multiple CPU cores, when I do I’m sure I’ll post it and look for a new benchmark section in the SQLScripts project coming soon!

As always any thoughts or comments are welcome, if you have a more efficient formula I would love to see it.

If you run the script post your run time as well as your CPU model I would like to see what kinds of times other SQL Servers put up.