Test CPU usage Percent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Test CPU usage Percent

Author

Topic

Mathias
Posting Yak Master

119 Posts

Posted - 2009-03-20 : 03:30:19

I am looking for an example to test the CPU usage and see if it can reach 100%.

Mathias
Posting Yak Master

119 Posts

Posted - 2009-03-20 : 06:15:35

Example :
declare @i int
set @i=1
while @i<10000000
begin
Set @i=@i+1
end

It peaks at 15% max of the CPU. Why it is not taking more resources?

Mathias
Posting Yak Master

119 Posts

Posted - 2009-03-23 : 02:40:30

How do you benchmark the SQL CPU%, disk access, network IO without using already made tools?

Mathias
Posting Yak Master

119 Posts

Posted - 2009-03-23 : 06:19:00

I have found an example provided by www.sqlworkshops.com

create table tab7 (c1 int primary key clustered, c2 int, c3 char(2000))
go
begin tran
declare @i int
set @i = 1
while @i <= 5000
begin
insert into tab7 values (@i, @i, 'a')
set @i = @i + 1
end
commit tran
go

The query
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 option (maxdop 3)
=>takes 75% on a 4 CPU
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2
=>takes 100%

My question : Why are most of my lenghty store procedures taking up to 25% and never exceeds it since I never use the option (maxdop)?? Were is this setting done?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources