November 14, 2008 at 11:38 am
when i exe sp_who2 mentioned below are my assumptions and questions..please correct and suggest me...
i)as far as i know i can see the connections to a particular db using sp_who2...then in the result in the "dbname" column why does it have different dbs...is it bcoz it is somewhere related to that db?
ii)on a particular spid if see high value in "CPUTime" column and high value in "DISKI/O" column compared to other values..does it means that the query at that particular spid should be tuned?..or should i look for some other columns before cmng to conclusion that particular query is taking more time.
Thanks
November 14, 2008 at 12:08 pm
i got the answer..i didnt realize it before...this is for people who dont know..i guess most of the people here would know it..where we run sp_who2 it will give the result irrespective of the database...
i)inorder to find the number of users connections connected to a db can use this command:
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame order by DatabaseName
ii)inorder to find no of connections to a server can use this:
select count(*)
from sys.dm_exec_connections
im still waiting for the reply for the questions regarding the query from my previous post..thx
November 14, 2008 at 12:52 pm
High CPUTime and DiskIO might just mean that the connection has been open for a very long time and a lot of work has been done. The connection could be open for 24 hours with 200 queries run during that time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply