HELP! Looking for Query to find out CPU & Memory

Question

Post reply

HELP! Looking for Query to find out CPU & Memory

TOUGHSQL2003

SSC Rookie

Points: 39
More actions
October 27, 2003 at 7:21 pm

#176812

This is a first for me, I have been looking everywhere to determine if there is a way within SQL that I can query for CPU (how many processors the server has, etc) & Memory statistics (how much memory the server has vs. how much is currently being used) of a server. I have heard this is possible but I need some help finding out how! I have looked through everything and still no luck. ANy thoughts on this? Thanks!

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

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

growl Hall of Fame Points: 3068 More actions · Answer 1

Try xp_msver to get information about the server. This however will not tell how much ram is currently being used.

mccork SSC-Insane Points: 22288 More actions · Answer 2

This piece of code works for SQL2k under Win2k, XP and Win2003. However, it's slow for XP & WIN2003 because winmsd seems to ignore switches and return everything under those OSes. You might be better off playing with systeminfo rather than winmsd for XP and 2003.

 

SET NOCOUNT ON

IF @@VERSION LIKE '%Windows NT 5.%' -- Win2k / XP / Win2003

    BEGIN

        USE master

        EXEC master..xp_cmdshell 'winmsd /report %computername%.txt /categories +systemsummary', no_output

        WAITFOR DELAY '00:00:05'  -- can't remember why I included this line ??

        CREATE TABLE #Txt (id INT IDENTITY PRIMARY KEY CLUSTERED, Txt VARCHAR(500) NULL)

        INSERT #Txt EXEC master..xp_cmdshell 'type %computername%.txt'

        SELECT  [Attribute] = LEFT(Txt, [LabelLen])

               ,[KB] = CAST(CAST(REPLACE(SUBSTRING(Txt, [NumberStart], [NumberEnd]-[NumberStart]), ',', '') AS FLOAT)  * [Multiplier] AS INT)

            FROM (    

                SELECT  Txt

                       ,[LabelLen] = 5+CHARINDEX('Memory', Txt)

                       ,[NumberStart] = PATINDEX('%[123456789]%', Txt)

                       ,[NumberEnd] = LEN(RTRIM(Txt)) - 2

                       [Multiplier] = CASE RIGHT(RTRIM(REPLACE(Txt,CHAR(9), ' ')),2) WHEN 'GB' THEN 1024*1024 WHEN 'MB' THEN 1024 ELSE 1 END

                    FROM #Txt 

                    WHERE Txt LIKE 'Total Physical Memory%' 

                       OR Txt LIKE 'Available Physical Memory%'

                       OR Txt LIKE 'Total Virtual Memory%'

                       OR Txt LIKE 'Available Virtual Memory%'

                    ) Raw

        DROP TABLE #Txt

    END

Cheers,

- Mark

Edited by - mccork on 10/27/2003 10:50:37 PM

Cheers,
- Mark

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
Try xp_msver to get information about the server. This however will not tell how much ram is currently being used.

Add to above:

SELECT cntr_value 'SQL Server memory used (KB)'

FROM master..sysperfinfo

WHERE counter_name = 'Total Server Memory (KB)'

--Jonathan