how to get information?

  • Hi Every one,

     

    From few i am working that how can i get information about the system I/O summary,Memory usage,process,all locks,blocking locks,database I/O Summary,user I/O Summary,waits,top sql cache, and last backups.

    in one QA window with one sql query

    pls help

    from

    Killer

  • Are you really serious that you want all this "in one QA window with one sql query" ??

    Closest you're going to get if this is a definate requirement is DBCC PERFMON.

     

    --------------------
    Colt 45 - the original point and click interface

  • Hi Phill,

     

    What u think i am playing with sql server or i am joking.

    DBCC PERFOM did not give all the information.

    from

    Killer

     

  • "What u think i am playing with sql server or i am joking."

    I don't know what to think, that's why I asked the question "Are you really serious that you want all this 'in one QA window with one sql query' ??"

    "DBCC PERFOM did not give all the information."

    Yes I know DBCC PERFMON won't give you all the information as I said, this is the "Closest you're going to get if this is a definate requirement"

     

    --------------------
    Colt 45 - the original point and click interface

  • Hi,

    what do u mean about seriousness.

    Do this is the way u ask a person who need help?

    Ok see this i tried but i am not able to get few things

    SELECT spid 'PID',

     rtrim(status) 'Status', SUSER_SNAME(sid) 'User', rtrim(hostname) 'Host'

      , rtrim(program_name) 'Program', memusage 'Mem Usage', cpu 'CPU Time',

                   physical_io 'I/O', blocked 'Blocked', 'Database'= CASE

                       WHEN dbid=0 THEN '[NULL]' ELSE DB_NAME(dbid) END,

                   cmd 'Command', last_batch 'Last Batch', login_time 'Login Time',

             rtrim(nt_domain) 'NT Domain', rtrim(nt_username) 'NT User',

        net_address 'Net Address', net_library 'Net Library' FROM master.dbo.sysprocesses

    (nolock) ORDER BY 2

    hope u find some thing diffrent from others

    from

    Killer

     

  • "what do u mean about seriousness.

    Do this is the way u ask a person who need help?"

    Given your history, under this and other usernames on this forum, yes this is how I ask you a question.

    Now lets look at what you want "in one QA window with one sql query"

    system I/O summary --> DBCC SQLPERF(IOSTATS)

    Memory usage --> DBCC MEMUSAGE or DBCC MEMORYSTATUS

    process --> presuming you mean executing command here, from sp_who2

    all locks --> sp_lock

    blocking locks --> list of exclusive locks from sp_lock

    database I/O Summary --> Loop through "SELECT * FROM ::fn_virtualfilestats ( <dbid>, <fileid> ) " for each database and file

    user I/O Summary --> sp_who2

    waits --> DBCC SQLPERF(WAITSTATS)

    top sql cache --> DBCC CACHESTATS (I do recall a command that listed cache objects, but I can't recall what it is now)

    last backups --> query sysbackuphistory

    There are quite a few variations of DBCC SQLPERF that you might be able to utilise,

    DBCC SQLPERF (LOGSPACE)

    DBCC SQLPERF (UMSSTATS)

    DBCC SQLPERF (WAITSTATS)

    DBCC SQLPERF (IOSTATS)

    DBCC SQLPERF (RASTATS)

    DBCC SQLPERF (THREADS)

    DBCC SQLPERF (SPINLOCKSTATS)

    DBCC SQLPERF (UMSSPINSTATS)

    DBCC SQLPERF (NETSTATS)

    DBCC SQLPERF (LRUSTATS)

    Check these pages for some useful info,

    http://www.sql-server-performance.com/dbcc_commands.asp

    http://www.sqldev.net/misc/sp_waitstats.htm

    http://www.sqldev.net/misc/fn_filestats.htm

    http://www.sqldev.net/articles/dbcc_sqlperf.htm

    http://www.databasejournal.com/features/mssql/article.php/10894_3414111_2

    Now do you see why I asked if you were serious ??

     

    --------------------
    Colt 45 - the original point and click interface

  • Hi Phill,

    I think u did not understand what i asked for.

    I know the about given information i can have all i asked for  but u see what i asked for .one query means one query.

    have executed my query i need help on that. Guide me to get what i want.

    Thanx for ur reply.

     

    from

    Killer

     

  • Sorry, I help people reach their goals, I don't do their work for them.

    If you can't take the information I've provided and re-build your query, I'm not going to do it for you.

     

    --------------------
    Colt 45 - the original point and click interface

  • Great information Phil !!!

    (and very professional handling of this 'individual')

    Cheers

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Thanx Phill,

    I did not asked to work on that i just asked u how can i reach to my goal and where i am wrong.

    from

    Killer

     

  • Thanx for ur precious help?

    I know what i need and  this is what i needed.

    I dont want  u to work for me?

    -------------------------

    top sql cache --> DBCC CACHESTATS (I do recall a command that listed cache objects, but I can't recall what it is now)

    Now lets look at what u cannot recall

    --**************TOP SQL Cache***********************

    select USER_NAME() select usertype,type,name from systypes where usertype>=257

    DECLARE @db sysname CREATE TABLE ##syscacheobjects

    (   objtype nvarchar(17) NOT NULL,cachetype nvarchar(34) NOT NULL,objid int NOT NULL,dbid smallint NOT NULL,

    userid smallint NOT NULL,usecounts int NOT NULL,sqlbytes int NOT NULL,pagesused int NULL,

    sql nvarchar(3568) NULL,    DBName sysname NULL,ObjectName sysname NULL, UserName sysname NULL

    )BEGIN INSERT ##syscacheobjects (objtype, cachetype, objid, dbid, userid, usecounts, sqlbytes, pagesused,

    sql, DBName ) SELECT TOP 100 objtype, cacheobjtype, objid, dbid, uid, usecounts, sqlbytes,

    pagesused, sql, db_name(dbid) FROM master..syscacheobjects

    ORDER BY 6 DESC END DECLARE DBCursor CURSOR FOR SELECT distinct DBName FROM ##syscacheobjects OPEN DBCursor

    DECLARE @quotedDB nvarchar(130) FETCH NEXT FROM DBCursor INTO @db WHILE(@@fetch_status <> -1)

    BEGIN SET @quotedDB = QUOTENAME(@db, '''')

    EXEC('UPDATE ##syscacheobjects SET  

    ObjectName = name FROM [' + @db + ']..sysobjects where id = objid and DBName =  ' + @quotedDB)

    EXEC('UPDATE ##syscacheobjects SET UserName = name FROM [' + @db + ']..sysusers where userid = uid 

    and DBName =  ' + @quotedDB) FETCH NEXT FROM DBCursor INTO @db END DEALLOCATE DBCursor

    SELECT objtype, cachetype, objid, ObjectName, dbid, DBName, userid, UserName, usecounts,

     sqlbytes, kb_used = pagesused * 8, sql FROM ##syscacheobjects ORDER BY 9 DESC

    DROP TABLE ##syscacheobjects

     

    from

    Killer

  • Thanx for ur precious help?

    I know what i need and  this is what i needed.

    I dont want  u to work for me?

    -------------------------

    top sql cache --> DBCC CACHESTATS (I do recall a command that listed cache objects, but I can't recall what it is now)

    Now lets look at what u cannot recall

    --**************TOP SQL Cache***********************

    select USER_NAME() select usertype,type,name from systypes where usertype>=257

    DECLARE @db sysname CREATE TABLE ##syscacheobjects

    (   objtype nvarchar(17) NOT NULL,cachetype nvarchar(34) NOT NULL,objid int NOT NULL,dbid smallint NOT NULL,

    userid smallint NOT NULL,usecounts int NOT NULL,sqlbytes int NOT NULL,pagesused int NULL,

    sql nvarchar(3568) NULL,    DBName sysname NULL,ObjectName sysname NULL, UserName sysname NULL

    )BEGIN INSERT ##syscacheobjects (objtype, cachetype, objid, dbid, userid, usecounts, sqlbytes, pagesused,

    sql, DBName ) SELECT TOP 100 objtype, cacheobjtype, objid, dbid, uid, usecounts, sqlbytes,

    pagesused, sql, db_name(dbid) FROM master..syscacheobjects

    ORDER BY 6 DESC END DECLARE DBCursor CURSOR FOR SELECT distinct DBName FROM ##syscacheobjects OPEN DBCursor

    DECLARE @quotedDB nvarchar(130) FETCH NEXT FROM DBCursor INTO @db WHILE(@@fetch_status <> -1)

    BEGIN SET @quotedDB = QUOTENAME(@db, '''')

    EXEC('UPDATE ##syscacheobjects SET  

    ObjectName = name FROM [' + @db + ']..sysobjects where id = objid and DBName =  ' + @quotedDB)

    EXEC('UPDATE ##syscacheobjects SET UserName = name FROM [' + @db + ']..sysusers where userid = uid 

    and DBName =  ' + @quotedDB) FETCH NEXT FROM DBCursor INTO @db END DEALLOCATE DBCursor

    SELECT objtype, cachetype, objid, ObjectName, dbid, DBName, userid, UserName, usecounts,

     sqlbytes, kb_used = pagesused * 8, sql FROM ##syscacheobjects ORDER BY 9 DESC

    DROP TABLE ##syscacheobjects

     

     

  • Yes you can query the system tables, but I was talking about a single command.

    It might have been in an earlier version of SQL Server. It showed the basic cache statistics and I think it was the top 20 objects in the cache.

     

    --------------------
    Colt 45 - the original point and click interface

  • :w00t

  • Just wondering .........

    I ran the "TOP SQL CACHE" query listed above, and got results, but when & why would you run that ?  I'm not familiar enough with that data to understand how it would be useful.

Viewing 15 posts - 1 through 15 (of 21 total)

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