CPU%, Disk% and Memory utilization from TSQL
-
Does anyone know how I can get current CPU%, Disk% and MEMORY utilization using TSQL?
THANKS!
Francis
-----------------
SQLRanger.com -
Hi fapel,
I guess you don't search for SQL internal function like @@CPU_BUSY.
If so, look at BOL for 'system statistical functions'.
If not this might be an alternative for you. Take a look at http://www.sysinternals.com/ntw2k/freeware/pstools.shtml. It's freeware and extremely useful, although I'm not sure how to query result via xp_cmdshell.
Hello Brian Kelley?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for the reply Frank.
After a closer look at sp_monitor, I think this will give me what I need.
Cheers,
Francis
Francis
-----------------
SQLRanger.com -
No matter Francis!
quote:
After a closer look at sp_monitor, I think this will give me what I need.if only all problems could be so easily solved
Anyway, the link is worth visiting!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Francis,
you were looking for the SQL Server internal stuff?
How about this?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pactldb_MonitorServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pactldb_MonitorServer]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[svrmonitor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[svrmonitor]
GO
CREATE TABLE [dbo].[svrmonitor] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sampletime] [datetime] NOT NULL ,
[lastrun] [datetime] NOT NULL ,
[cpu_busy] [int] NOT NULL ,
[io_busy] [int] NOT NULL ,
[idle] [int] NOT NULL ,
[pack_received] [int] NOT NULL ,
[pack_sent] [int] NOT NULL ,
[connections] [int] NOT NULL ,
[pack_errors] [int] NOT NULL ,
[total_read] [int] NOT NULL ,
[total_write] [int] NOT NULL ,
[total_errors] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[svrmonitor] WITH NOCHECK ADD
CONSTRAINT [PK_svrmonitor] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE procedure dbo.pactldbmonitor
as
declare @last_rundatetime
declare @now datetime
declare @cpu_busy int
declare @io_busyint
declare @idleint
declare @pack_receivedint
declare @pack_sentint
declare @pack_errorsint
declare @connectionsint
declare @total_readint
declare @total_writeint
declare @total_errorsint
declare @oldcpu_busy int
declare @intervalint
declare @mspertickint
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'pactldbmonitor')
return (1)
end
select @mspertick = convert(int, @@timeticks / 1000.0)
select
@now = getdate(),
@cpu_busy = @@cpu_busy,
@io_busy = @@io_busy,
@idle = @@idle,
@pack_received = @@pack_received,
@pack_sent = @@pack_sent,
@connections = @@connections,
@pack_errors = @@packet_errors,
@total_read = @@total_read,
@total_write = @@total_write,
@total_errors = @@total_errors
select
@oldcpu_busy = cpu_busy, @last_run = sampletime
FROM
svrmonitor
WHERE
id=(SELECT MAX(id) FROM svrmonitor)
begin
insert into svrmonitor
(sampletime,
lastrun,
cpu_busy,
io_busy,
idle,
pack_received,
pack_sent,
connections,
pack_errors,
total_read,
total_write,
total_errors
)
values
(
@now,
@last_run,
@cpu_busy,
@io_busy,
@idle,
@pack_received,
@pack_sent,
@connections,
@pack_errors,
@total_read,
@total_write,
@total_errors
)
end
return (0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I've put this in a job which runs once a day. Hope this helps!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply