how to get information?

Question

Post reply

how to get information?

raj-247939

SSCertifiable

Points: 5999
More actions
September 13, 2005 at 4:23 am

#111654

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

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

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

philcart SSC-Forever Points: 47794 More actions · Answer 1

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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 2

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

philcart SSC-Forever Points: 47794 More actions · Answer 3

"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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 4

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

philcart SSC-Forever Points: 47794 More actions · Answer 5

"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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 6

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

philcart SSC-Forever Points: 47794 More actions · Answer 7

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

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

Great information Phil !!!

(and very professional handling of this 'individual')

Cheers

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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 9

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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 10

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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 11

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

philcart SSC-Forever Points: 47794 More actions · Answer 12

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

raj-247939 SSCertifiable Points: 5999 More actions · Answer 13

raj-247939

SSCertifiable

Points: 5999

September 15, 2005 at 3:11 am

#590044

:w00t

homebrew01 SSC Guru Points: 55548 More actions · Answer 14

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.