September 28, 2003 at 2:35 am
Hi,
Is there any option or command to let a certain user a'super user'
The reason for this is ,sometimes when you have a high cpu usage you can't access the Current Activity in the Enterprise Maneger
to see all the processes .
we used Ingres Database before and it has power user .So,I think SQL SERVER should have somthing like this.
thanks
September 28, 2003 at 6:20 am
All users are equal as far as processor time. You can make a user a member of the sysadmin role to give them "super user" permissions, but it doesn't change how much processor time they get.
Instead of using EM, try running 'select * from master..sysprocesses', that will let you see everything running at that moment. You could also try using Profiler to catch events with a very high CPU usage.
Andy
September 29, 2003 at 2:09 am
Here's a series of scripts I use to check who's eating up the resources. Shows IO or CPU by username, db or email domain (my usernames are email addresses)
-- Takes snapshot of cpu usage. waits 2 seconds and compares
drop table cpu_usage
go
select cpu, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select difference = p.cpu - u.cpu,
p.cpu, p.loginame, p.spid, p.hostname, p.last_batch
from sysprocesses p join cpu_usage u on p.spid = u.spid
order by 1 desc
-- Takes snapshot of io usage. waits 5 seconds and compares
drop table cpu_usage
go
select physical_io, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select difference = p.physical_io - u.physical_io,
p.physical_io, p.loginame, p.spid, p.hostname, p.last_batch
from sysprocesses p join cpu_usage u on p.spid = u.spid
order by 1 desc
-- summary cpu by email domain --
drop table cpu_usage
go
select cpu, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select
right(rtrim(p.loginame),len(rtrim(p.loginame))-charindex('@',p.loginame)),
sum(p.cpu - u.cpu) as cpu,
count(1) as connections
-- p.loginame, p.cpu, p.spid, p.hostname, p.last_batch
from sysprocesses p join cpu_usage u on p.spid = u.spid
group by right(rtrim(p.loginame),len(rtrim(p.loginame))-charindex('@',p.loginame))
order by 2 desc
-- summary io by email domain --
drop table cpu_usage
go
select physical_io, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select
right(rtrim(p.loginame),len(rtrim(p.loginame))-charindex('@',p.loginame)),
sum(p.physical_io - u.physical_io) as physical_io,
count(1) as connections
from sysprocesses p join cpu_usage u on p.spid = u.spid
group by right(rtrim(p.loginame),len(rtrim(p.loginame))-charindex('@',p.loginame))
order by 2 desc
-- cpu by db --
drop table cpu_usage
go
select cpu, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select
p.dbid,
sum(p.cpu - u.cpu) as cpu,
max(sysdatabases.name) as name,
count(1) as connections
from sysprocesses p join cpu_usage u on p.spid = u.spid
inner join sysdatabases on sysdatabases.dbid = p.dbid
group by p.dbid
order by 2 desc
-- io by db --
drop table cpu_usage
go
select physical_io, spid into cpu_usage from sysprocesses
waitfor delay '000:00:2'
select
p.dbid,
sum(p.physical_io - u.physical_io) as physical_io,
max(sysdatabases.name) as name,
count(1) as connections
from sysprocesses p join cpu_usage u on p.spid = u.spid
inner join sysdatabases on sysdatabases.dbid = p.dbid
group by p.dbid
order by 2 desc
September 29, 2003 at 2:56 am
quote:
Here's a series of scripts I use to check who's eating up the resources....
This is a useful script, but when I tried it, I got multiple lines in the cpu usage script for the same process, all with the same usage count - which didn't change on repeating. A single process (that is an spid) can spawn multiple processes on the cpu and you end up with a cross join with lines for spurious cpu usage.
The script can be improved by also returning the kpid into the temporary table and then adding the clause 'and p.kpid=u.kpid' to the query that follows the delay.
Tony Bater
Tony
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply