quote:

---

This is more of an abstract answer than I am sure you would like, but....when you begin the task of performance tuning, no matter how basic, it is vital that you have a goal in mind.

---

quote:

---

Returns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent doing work since Microsoft® SQL Server™ was last started

---

declare @Seconds int

set nocount on

If @Seconds IS NULL
	Set @Seconds = 10 


CREATE TABLE [#sysprocess] (
	[spid] [smallint] NOT NULL ,
	[blocked] [smallint] NOT NULL ,
	[cpu] [int] NOT NULL ,
	[physical_io] [bigint] NOT NULL ,
	[memusage] [int] NOT NULL ,
	[open_tran] [smallint] NOT NULL ,
	[status] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[cmd] [nchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[loginame] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[tStamp] [datetime] NULL 
          CONSTRAINT [DF__hold_sysp__tStam__5AAF56EE] DEFAULT (getdate())
) 

Declare @i int

set @i = 0


While @i <= @Seconds
begin

	insert	#sysprocess (spid, blocked, cpu, 
          physical_io, memusage, open_tran, status, cmd, loginame )
	select spid, blocked, cpu, physical_io, 
          memusage, open_tran, status, cmd, loginame 
	from master..sysprocesses
	
	set @i = @i + 1

	waitfor delay '00:00:01'
end


declare @total_ms numeric(8,2)
declare @StartTime datetime, @StopTime datetime
declare @msg varchar(1000)
-- by loginame
Select 	@StartTime = min(tstamp) , 
	@StopTime = max(tstamp) , 
	@total_ms = datediff(ms, min(tstamp), max(tstamp))
from dbo.hold_sysprocess

Print 'Started at: ' + convert(varchar, @StartTime)  
Print ' '
Print 'Duration (ms): ' + convert(varchar, @total_ms)
Print ' '
--Print 'Results by loginame:'
Print '---------- CPU Users -----------'
Print ' '
select top 10 left(loginame, 20) as loginame, 
	sum(CPU) as [CPU (ms)],
	convert(numeric(5,0), convert( numeric(8,2), sum(CPU)) / @total_ms * 100  )  
           as CPU_Pct,
 sum(IO) as IO,
 Sum(Mem) as Mem_pages,
 Sum(Mem) * 8 as Mem_KB

From (select spid, loginame, CPU = max_cpu - min_cpu,
 IO = max_io - min_io,
 Mem = max_memusage
from (select spid, loginame, min(cpu) as min_cpu, max(cpu) as max_cpu, 
 min(physical_io) as min_io, Max(physical_io) as max_io, 
 max(memusage) as max_memusage
from #sysprocess
group by spid, loginame) as a ) as b
group by loginame
Order by 2 desc, 5 desc

Print ' '
Print '---------- Memory Users -----------'
Print ' '
select top 10 left(loginame, 20) as loginame, sum(CPU) as [CPU (ms)],
 convert(numeric(5,0), convert( numeric(8,2), sum(CPU)) / @total_ms * 100  )  
          as CPU_Pct,
 sum(IO) as IO,
 Sum(Mem) as Mem_pages,
 Sum(Mem) * 8 as Mem_KB

From (select spid, loginame, CPU = max_cpu - min_cpu,
 IO = max_io - min_io,
 Mem = max_memusage
from (select spid, loginame, min(cpu) as min_cpu, max(cpu) as max_cpu, 
 min(physical_io) as min_io, Max(physical_io) as max_io, 
 max(memusage) as max_memusage
from #sysprocess
group by spid, loginame) as a ) as b
group by loginame
Order by 5 desc, 2 desc

Print ' '
Print '---------- IO Users -----------'
Print ' '
select top 10 left(loginame, 20) as loginame, sum(CPU) as [CPU (ms)],
  convert(numeric(5,0), convert( numeric(8,2), sum(CPU)) / @total_ms * 100  )  
           as CPU_Pct,
 sum(IO) as IO,
 Sum(Mem) as Mem_pages,
 Sum(Mem) * 8 as Mem_KB

From (select spid, loginame, CPU = max_cpu - min_cpu,
 IO = max_io - min_io,
 Mem = max_memusage
from (select spid, loginame, min(cpu) as min_cpu, max(cpu) as max_cpu, 
 min(physical_io) as min_io, Max(physical_io) as max_io, 
 max(memusage) as max_memusage
from #sysprocess
group by spid, loginame) as a ) as b
group by loginame
having Sum(IO) <> 0
Order by 4 desc, 2 desc

drop table #sysprocess
set nocount off

quote:

---

Page47, as soon as you have 3 CPU's running in a box, let me know.

I'm pretty sure you can only have them in multiples of 2
2
4
6
8

---

quote:

---

Here's a little script I wrote to show an ISP who'se using most of their resources over a given range of time:

---

quote:

---

DECLARE @multiplier TINYINT
--Remove comment for the multiplier that suits your # of CPUs
--SET @multiplier = 32 -- This is for a 1-way machine
--SET @multiplier = 16 -- This is for a 2-way machine
--SET @multiplier = 8 -- This is for a 4-way machine
--SET @multiplier = 4 -- This is for a 8-way machine

---

DECLARE @uptime_seconds1 INT
DECLARE @uptime_seconds2 INT

SELECT 
  @uptime_seconds1 = DATEDIFF(s, crdate, GETDATE())
FROM master..sysdatabases WHERE NAME = 'tempdb'

SET
  @uptime_seconds2 = (@@CPU_BUSY + @@IO_BUSY + @@IDLE) / 1000

SELECT
  (@uptime_seconds1 * 1.0) / @uptime_seconds2 AS 'Multiplier'
 

quote:

---

DECLARE @multiplier TINYINT
--Remove comment for the multiplier that suits your # of CPUs
--SET @multiplier = 32 -- This is for a 1-way machine
--SET @multiplier = 16 -- This is for a 2-way machine
--SET @multiplier = 8 -- This is for a 4-way machine
--SET @multiplier = 4 -- This is for a 8-way machine

---

EXEC master.dbo.xp_msver 'ProcessorCount'

quote:

---

EXEC master.dbo.xp_msver 'ProcessorCount'
to find out the number of Processors.

---

ValterBorges, thanks for the tip on xp_msver, now my script is generic.

quote:

---

Just out of curiosity, have you tried using sp_monitor?

---

Rob, I just tried using sp_monitor. I get an error:

Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94
Arithmetic overflow error converting expression to data type int.

Unfortunately, I don't have the time to debug sp_monitor right now and see what is overflowing.

However, my actual question is that I have one machine that does not seem to adhere to the (32 / #processors) formula for the correct multiplier. The script I posted a couple posts back in this thread uses reverse calculations to show the true multiplier on the machine where it is run.

When I run this on my 1-processor machine, it returns 32. Check. When I run it on my 2-processor machine, it returns 16. Check. However, when I run it on my 4-processor machine (which is our production server), I get 17.2. Huh? A few days ago the same script was returning 18.8. I'm just trying to see if anyone knows why this machine doesn't follow the formula. Obviously, I am missing something (actually, many things, but we won't go into that here ).

Thanks.


==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton