Post reply

How to find... ? - Need sql scripts

  • 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

  • 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]

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply