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.
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();
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.