SQL Server Performance

Database Usage

Discussion in 'SQL Server 2008 General DBA Questions' started by gshyam23, Jun 6, 2011.

  1. gshyam23 New Member

    Hello everyone,

    I got a requirement to track all the information like the users connecting, queires executing by them and time, etc. I think we can use profiler for this but I want to track these details for few months, hence profiler will not be a good option I think.
    Also I looked at SQL Server auditing and I felt it will not fit my requirement as I want to capture the details at server level.

    Anyone please suggest me (any script/dmv/etc) which can be used to capture these details.

    Thanks a lot in advance.

    -Shyam
  2. Luis Martin Moderator

    For continues trace I'll use profiler with some filter like "duration", and running in WS storing in file, not table.
  3. gshyam23 New Member

    Any script available to store the information in a user table from sysprocesses view?
  4. Jahanzaib Member

    Code:
    SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
    WHERE STATUS !='SLEEPING'
    ORDER BY CPU DESC
    Create the same table def with Id column Primary key because month of data will be huge and then use

    Code:
    INSERT INTO YOURTABLE
    SELECT ST.TEXT,SP.SPID,WAITTIME,LASTWAITTYPE,CPU,PHYSICAL_IO,STATUS,HOSTNAME,PROGRAM_NAME,CMD,LOGINAME FROM SYS.SYSPROCESSES SP
    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST
    WHERE STATUS !='SLEEPING'
    ORDER BY CPU DESC
    
    you can schedule this query on SQL Server Agent

    you can use another stuff for this purpose is

    1-EventData()

    http://msdn.microsoft.com/en-us/library/ms173781.aspx

    2-Database Audit Specification

    http://technet.microsoft.com/en-us/library/cc280404.aspx
  5. satya Moderator

  6. Luis Martin Moderator

    Using server side, the output don't use columns order. I mean, I export from profile to script with filters (that is in the script) and some particular columns order (that not).
    Anyway is not a big deal, but is a bug?
  7. dineshasanka Moderator

    Service side traces are the best for this.

Share This Page