How to find... ? - Need sql scripts

• Srinivasan-199185

  Mr or Mrs. 500

  Points: 504

  More actions

  January 5, 2005 at 5:44 am

  #163411

  hi all,

  Pl. help me in finding the following using SELECT statement or any alternate method,

  1. how many users are currently connected to a database?
  2. how many sql commands are currently being executed by database?
  3. how many sql commands are hanging due to lack of resource?
  4. how many objects are locked?
  5. how to find long running queries?
  6. is there a tool to findout & optimize SQL Queries?

  Regards,

  Srinivasan

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 5, 2005 at 5:56 am

  #535956

  sp_who, sp_who2 and sp_lock might be a start. Profiler, which ships with SQL Server is a good tool, but Optimization is up to yourself. To a certain degree is the Index Tuning wizard, which also ships with SQL Server useful.

  HTH

   

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  January 6, 2005 at 8:11 am

  #536199

  Users in a database is pretty simple...

   

   

  Select p.SPID, P.STATUS, P.BLOCKED, P.CMD, P.CPU, P.PHYSICAL_IO, P.LAST_BATCH, P.PROGRAM_NAME, P.OPEN_TRAN, P.HOSTNAME, P.LOGINAME

  from master.dbo.sysprocesses p (nolock)

  join master..sysdatabases d (nolock) on p.dbid = d.dbid

  Where d.[name] = 'MASTER' --> database name here

  ---

  
  Shamless self promotion - read my blog http://sirsql.net