SqlServer:SqlServer(服务器磁盘监控,创建管理员账号分配权,添加链接服务器,查询CPU,查询内存)

Easter79
• 阅读 742

1.服务器磁盘监控

(1)总链接服务上开启所有链接服务器的RPC:

----------------------总链接服务器上面,开启每个服务器的RPC
--exec sp_serveroption @server='S32' , @optname= 'rpc', @optvalue ='TRUE'

--exec sp_serveroption @server='S32' , @optname= 'rpc out', @optvalue='TRUE'

SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc'',@optvalue =''TRUE'' ' FROM sys.servers
ORDER BY name
SELECT 'exec sp_serveroption @server= '''+ name+''' , @optname= ''rpc out'',@optvalue =''TRUE'' ' FROM sys.servers
ORDER BY name

(2)各个服务上创建监测(根据本地情况,需要改总链接服务器IP和本机IP):

------添加链接服务器
IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name like '%100.80.10.30%')
BEGIN
    EXEC master.dbo.sp_addlinkedserver
        --服务器名称
        @server = '100.80.10.30',  --36-48,12-15,
        @srvproduct = N'', @provider = 'SQLOLEDB',
        --IP
        @datasrc = '100.80.10.30';
    EXEC master.dbo.sp_addlinkedsrvlogin --IP
        '100.80.10.30', false, NULL, --账号
        'sa', --密码
        'hnsjt_lwsj@2018'; 
END;
GO    
    

CREATE DATABASE [monitor];
GO
USE [monitor];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:        <Author,,Name> k
-- Create date: <Create Date,,>20190218
-- Description:    <Description,,>监控磁盘使用情况
-- =============================================

CREATE PROCEDURE disk_monitor
AS
    BEGIN
            

        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
        
        --创建相关表
        IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL
            BEGIN
                CREATE TABLE #server_disk_usage
                    (
                      [ip] [NVARCHAR](400) ,
                      [disk_num] [NVARCHAR](10) NOT NULL ,
                      [total_size_mb] [BIGINT]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_total_size_mb]
                        DEFAULT ( (0) ) ,
                      [free_siez_mb] [BIGINT]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_free_siez_mb]
                        DEFAULT ( (0) ) ,
                      [disk_info] [NVARCHAR](400)
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_disk_info]
                        DEFAULT ( '' ) ,
                      [iswarning] [NVARCHAR](400) ,
                      [check_time] [DATETIME]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_check_time]
                        DEFAULT ( GETDATE() ) ,
                      CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED
                        ( [disk_num] ASC )
                    )
                ON  [PRIMARY];
            END;


        --==========================================
        --查看所有数据库使用到的磁盘剩余空间
        DECLARE @disk TABLE
            (
              [disk_num] VARCHAR(50) ,
              [free_siez_mb] INT
            );
        INSERT  INTO @disk
                EXEC xp_fixeddrives;
        
        --更新当前磁盘的剩余空间信息
        UPDATE  M
        SET     M.[free_siez_mb] = D.[free_siez_mb]
        FROM    #server_disk_usage AS M
                INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num];

        --插入新增磁盘的剩余空间信息
        INSERT  INTO #server_disk_usage
                ( [disk_num] ,
                  [free_siez_mb]
                )
                SELECT  [disk_num] ,
                        [free_siez_mb]
                FROM    @disk AS D
                WHERE   NOT EXISTS ( SELECT 1
                                     FROM   #server_disk_usage AS M
                                     WHERE  M.[disk_num] = D.[disk_num] );
            
    
    
    -------------------------------------------------------2
                
                    
        

        
            --开启CMDShell
        EXEC sp_configure 'show advanced options', 1;

        RECONFIGURE WITH OVERRIDE;

        EXEC sp_configure 'xp_cmdshell', 1;

        RECONFIGURE WITH OVERRIDE;

            --========================================
            --创建临时表用来存放每个盘符的数据
        CREATE TABLE #tempDisks
            (
              ID INT IDENTITY(1, 1) ,
              DiskSpace NVARCHAR(200)
            );
            --============================================
            --将需要检查的磁盘放入临时表#checkDisks
        SELECT  ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID ,
                [disk_num]
        INTO    #checkDisks
        FROM    #server_disk_usage
        WHERE   [total_size_mb] = 0;

            --============================================
            --循环临时表#checkDisks检查每个磁盘的总量

        DECLARE @disk_num NVARCHAR(20);
        DECLARE @total_size_mb INT;
        DECLARE @sql NVARCHAR(200);
        DECLARE @max INT;
        DECLARE @min INT;
        SELECT  @max = MAX(RID) ,
                @min = MIN(RID)
        FROM    #checkDisks;

        WHILE ( @min <= @max )
            BEGIN
                SELECT  @disk_num = [disk_num]
                FROM    #checkDisks
                WHERE   RID = @min;

                SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '
                    + @disk_num + ':' + '''';
                PRINT @sql;

                INSERT  INTO #tempDisks
                        EXEC ( @sql
                            );

                SELECT  @total_size_mb = CAST(( RIGHT(DiskSpace,
                                                      LEN(DiskSpace)
                                                      - CHARINDEX(': ',
                                                              DiskSpace) - 1) ) AS BIGINT)
                        / 1024 / 1024
                FROM    #tempDisks
                WHERE   ID = 2;

            --SELECT @total_size_mb,@disk_num

                UPDATE  #server_disk_usage
                SET     [total_size_mb] = @total_size_mb
                WHERE   [disk_num] = @disk_num;

            --SELECT * FROM  #tempDisks

                TRUNCATE TABLE #tempDisks;

                SET @min = @min + 1;

            END;


            --==========================================
            --CMDShell

        EXEC sp_configure 'xp_cmdshell', 0;

        EXEC sp_configure 'show advanced options', 1;

        RECONFIGURE WITH OVERRIDE;
    
               

        DELETE  FROM [100.80.10.30].[monitor].[dbo].[server_disk_usage]
        WHERE   CONVERT(VARCHAR, check_time, 23) = CONVERT(VARCHAR, GETDATE(), 23)
                AND ip = '100.80.10.48';
        INSERT  INTO [100.80.10.30].[monitor].[dbo].[server_disk_usage]
                SELECT  '100.80.10.48' AS IP ,
                        [disk_num] AS Drive_Name ,
                        CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB ,
                        CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18,
                                                              2)) AS Used_Space_GB ,
                        CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB ,
                        CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18,
                                                              2)) AS Free_Space_Percent ,
                        [disk_info] ,
                        [check_time]
                FROM    #server_disk_usage;
        
            
        DROP TABLE #server_disk_usage;
            
            
    END;
GO

(3)总链接服务器创建监测:

CREATE DATABASE [monitor];
GO

USE [monitor];
GO
/****** Object:  StoredProcedure [dbo].[get_disk_use_info]    Script Date: 02/19/2019 09:46:23 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:        <Author,,Name> k
-- Create date: <Create Date,,>20190218
-- Description:    <Description,,>监控磁盘使用情况
-- =============================================
CREATE PROCEDURE [dbo].[get_disk_use_info]
AS
    BEGIN
            
            
            
            
            
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            --******************************************************
            --******************************************************
        IF OBJECT_ID('server_disk_usage') IS NOT NULL
            BEGIN
                DROP TABLE server_disk_usage;
            END;
            

        --==========================================
        --创建相关表
        IF OBJECT_ID('tempdb..#server_disk_usage') IS NULL
            BEGIN
                CREATE TABLE #server_disk_usage
                    (
                      [disk_num] [NVARCHAR](10) NOT NULL ,
                      [total_size_mb] [BIGINT]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_total_size_mb]
                        DEFAULT ( (0) ) ,
                      [free_siez_mb] [BIGINT]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_free_siez_mb]
                        DEFAULT ( (0) ) ,
                      [disk_info] [NVARCHAR](400)
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_disk_info]
                        DEFAULT ( '' ) ,
                      [check_time] [DATETIME]
                        NOT NULL
                        CONSTRAINT [DF_#server_disk_usage_check_time]
                        DEFAULT ( GETDATE() ) ,
                      CONSTRAINT [PK_#server_disk_usage] PRIMARY KEY CLUSTERED
                        ( [disk_num] ASC )
                    )
                ON  [PRIMARY];
            END;


        --==========================================
        --查看所有数据库使用到的磁盘剩余空间
        DECLARE @disk TABLE
            (
              [disk_num] VARCHAR(50) ,
              [free_siez_mb] INT
            );
        INSERT  INTO @disk
                EXEC xp_fixeddrives;
        
        --更新当前磁盘的剩余空间信息
        UPDATE  M
        SET     M.[free_siez_mb] = D.[free_siez_mb]
        FROM    #server_disk_usage AS M
                INNER JOIN @disk AS D ON M.[disk_num] = D.[disk_num];

        --插入新增磁盘的剩余空间信息
        INSERT  INTO #server_disk_usage
                ( [disk_num] ,
                  [free_siez_mb]
                )
                SELECT  [disk_num] ,
                        [free_siez_mb]
                FROM    @disk AS D
                WHERE   NOT EXISTS ( SELECT 1
                                     FROM   #server_disk_usage AS M
                                     WHERE  M.[disk_num] = D.[disk_num] );
            
    
    
    -------------------------------------------------------2
                
                    
        

        
            --开启CMDShell
        EXEC sp_configure 'show advanced options', 1;

        RECONFIGURE WITH OVERRIDE;

        EXEC sp_configure 'xp_cmdshell', 1;

        RECONFIGURE WITH OVERRIDE;

            --========================================
            --创建临时表用来存放每个盘符的数据
        CREATE TABLE #tempDisks
            (
              ID INT IDENTITY(1, 1) ,
              DiskSpace NVARCHAR(200)
            );
            --============================================
            --将需要检查的磁盘放入临时表#checkDisks
        SELECT  ROW_NUMBER() OVER ( ORDER BY [disk_num] ) AS RID ,
                [disk_num]
        INTO    #checkDisks
        FROM    #server_disk_usage
        WHERE   [total_size_mb] = 0;

            --============================================
            --循环临时表#checkDisks检查每个磁盘的总量

        DECLARE @disk_num NVARCHAR(20);
        DECLARE @total_size_mb INT;
        DECLARE @sql NVARCHAR(200);
        DECLARE @max INT;
        DECLARE @min INT;
        SELECT  @max = MAX(RID) ,
                @min = MIN(RID)
        FROM    #checkDisks;

        WHILE ( @min <= @max )
            BEGIN
                SELECT  @disk_num = [disk_num]
                FROM    #checkDisks
                WHERE   RID = @min;

                SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '
                    + @disk_num + ':' + '''';
                PRINT @sql;

                INSERT  INTO #tempDisks
                        EXEC ( @sql
                            );

                SELECT  @total_size_mb = CAST(( RIGHT(DiskSpace,
                                                      LEN(DiskSpace)
                                                      - CHARINDEX(': ',
                                                              DiskSpace) - 1) ) AS BIGINT)
                        / 1024 / 1024
                FROM    #tempDisks
                WHERE   ID = 2;

            --SELECT @total_size_mb,@disk_num

                UPDATE  #server_disk_usage
                SET     [total_size_mb] = @total_size_mb
                WHERE   [disk_num] = @disk_num;

            --SELECT * FROM  #tempDisks

                TRUNCATE TABLE #tempDisks;

                SET @min = @min + 1;

            END;


            --==========================================
            --CMDShell

        EXEC sp_configure 'xp_cmdshell', 0;

        EXEC sp_configure 'show advanced options', 1;

        RECONFIGURE WITH OVERRIDE;
    
            

        IF OBJECT_ID('server_disk_usage') IS NULL
            BEGIN
                CREATE TABLE server_disk_usage
                    (
                      ip VARCHAR(200) ,
                      Drive_Name VARCHAR(200) ,
                      Total_Space_GB VARCHAR(200) ,
                      Used_Space_GB VARCHAR(200) ,
                      Free_Space_GB VARCHAR(200) ,
                      Free_Space_Percent VARCHAR(200) ,
                      [disk_info] VARCHAR(200) ,
                      [check_time] DATETIME
                    );
            END;
            
        
        INSERT  INTO [server_disk_usage]
                SELECT  '100.80.10.30' AS IP ,
                        [disk_num] AS Drive_Name ,
                        CAST([total_size_mb] / 1024.0 AS NUMERIC(18, 2)) AS Total_Space_GB ,
                        CAST(( [total_size_mb] - [free_siez_mb] ) / 1024.0 AS NUMERIC(18,
                                                              2)) AS Used_Space_GB ,
                        CAST([free_siez_mb] / 1024.0 AS NUMERIC(18, 2)) AS Free_Space_GB ,
                        CAST([free_siez_mb] * 100 / [total_size_mb] AS NUMERIC(18,
                                                              2)) AS Free_Space_Percent ,
                        [disk_info] ,
                        [check_time]
                FROM    #server_disk_usage;
        
            
        DROP TABLE #server_disk_usage;
            
      
    END;

(4)在总链接服务器上创建调用的存储过程,调用各个服务器上的监控,更新各个服务器磁盘使用情况,存放至 [NETWORKING_AUDIT] 库的T_DISK_MONITOR表

-- =============================================
-- Author:        k
-- Create date: 20190219
-- Description:    更新各个服务器磁盘使用情况,存放至T_DISK_MONITOR表
-- =============================================
ALTER PROCEDURE [dbo].[updateDiskMonitorInfo]
    
AS
BEGIN
        -------------调用各个服务器上的监控
        EXEC [monitor].[dbo].[get_disk_use_info]
        DECLARE @ServerName VARCHAR(255);
        DECLARE @sql VARCHAR(8000);
        DECLARE cur CURSOR
        FOR
          SELECT name FROM  sys.servers 
          WHERE name !='WIN-4AAGSGNR81A'
        OPEN cur;
        FETCH NEXT FROM cur INTO @ServerName;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @sql = 'EXEC ['+@ServerName+'].[monitor]..[disk_monitor];'
                EXEC (@sql);        
                FETCH NEXT FROM cur INTO @ServerName;
        
            END;
        CLOSE cur;
        DEALLOCATE cur;
        
       
       -------------将磁盘使用情况存放至系统库[NETWORKING_AUDIT]
       IF  EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T_DISK_MONITOR')
       BEGIN
          DROP TABLE [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR];
       END
       SELECT * INTO [NETWORKING_AUDIT].[dbo].[T_DISK_MONITOR]
       from [monitor].[dbo].[server_disk_usage]
       ORDER BY ip
        
      
END

(5)创建SQLSERVER定时作业,每天自动更新即可

如图:

SqlServer:SqlServer(服务器磁盘监控,创建管理员账号分配权,添加链接服务器,查询CPU,查询内存)

2.创建管理员账号分配权限

(1)基本语法:

--查看数据库schema, user 的存储过程
select * from sys.database_principals
select * from sys.schemas 
select * from sys.server_principals
select * from sysusers
select * from sys.syslogins

--创建登陆帐户(create login)
create login dba with password='abcd1234@', default_database=mydb

--为登陆账户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba
create user dba for login dba with default_schema=dbo

--通过加入数据库角色,赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'dba'

--让 SQL Server 登陆帐户“dba”访问多个数据库
use mydb2
go create user dba for login dba with default_schema=dbo
go exec sp_addrolemember 'db_owner', 'dba'go

--禁用登陆帐户
alter login dba disable
--启用登陆帐户
alter login dba enable
--登陆帐户改名
alter login dba with name=dba_tom
--登陆帐户改密码: 
alter login dba with password='aabb@ccdd'
--数据库用户改名: 
alter user dba with name=dba_tom
--更改数据库用户 defult_schema: 
alter user dba with default_schema=sales
--删除数据库用户: 
drop user dba
--删除 SQL Server登陆帐户: 
drop login dba



--创建角色 r_test  
EXEC sp_addrole 'r_test'  
  
--添加登录 l_test,设置密码为pwd,默认数据库为pubs  
EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'  
  
--为登录 l_test 在数据库 pubs 中添加安全账户 u_test  
EXEC sp_grantdbaccess 'l_test','u_test'  
  
--添加 u_test 为角色 r_test 的成员  
EXEC sp_addrolemember 'r_test','u_test'  
  
  
--用l_test登陆,发现在SSMS中找不到仍和表,因此执行下述两条语句出错。  
select * from Sales.Orders  
select * from HR.Employees  
  
--授予角色 r_test 对 HR.Employees 表的所有权限  
GRANT ALL ON HR.Employees TO r_test  
--The ALL permission is deprecated and maintained only for compatibility.   
--It DOES NOT imply ALL permissions defined on the entity.  
--ALL 权限已不再推荐使用,并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。  
  
--测试可以查询表HR.Employees,但是Sales.Orders无法查询  
select * from HR.Employees  
  
  
--如果要收回权限,可以使用如下语句。(可选择执行)  
revoke all on HR.Employees from r_test  
--ALL 权限已不再推荐使用,并且只保留用于兼容性目的。它并不表示对实体定义了 ALL 权限。  
  
  
--授予角色 r_test 对 Sales.Orders 表的 SELECT 权限  
GRANT SELECT ON Sales.Orders TO r_test  
  
--用l_test登陆,发现可以查询Sales.Orders和HR.Employees两张表  
select * from Sales.Orders  
select * from HR.Employees  
  
--拒绝安全账户 u_test 对 HR.Employees 表的 SELECT 权限  
DENY SELECT ON HR.Employees TO u_test  
  
--再次执行查询HR.Employees表的语句,提示:拒绝了对对象 'Employees' (数据库 'InsideTSQL2008',架构 'HR')的 SELECT 权限。  
select * from HR.Employees  
  
--重新授权  
GRANT SELECT ON HR.Employees TO u_test  
  
--再次查询,可以查询出结果。  
select * from HR.Employees  
  
  
USE InsideTSQL2008  
--从数据库中删除安全账户,failed  
EXEC sp_revokedbaccess 'u_test'  
--删除角色 r_test,failed  
EXEC sp_droprole 'r_test'  
--删除登录 l_test,success  
EXEC sp_droplogin 'l_test'

(2)创建管理员用户和创建只读用户

--******************创建管理员账号******************--
--**************************************************--
USE master
Go
--创建登陆帐户(create login)
create login lwsjbackgr with password='hnsjt_lwsj@2019', default_database=master

--为登陆账户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba
create user lwsjbackgr for login lwsjbackgr with default_schema=dbo

--添加服务器角色
EXEC master.dbo.sp_addsrvrolemember @loginame = N'lwsjbackgr',   @rolename = N'sysadmin'

--添加数据库角色
--use [国土资源厅]
--create user sread for login sread with default_schema=dbo
    --此处分配只读
--exec sp_addrolemember 'db_datareader', 'sread' 

--禁用sa账号
alter login sa DISABLE



--******************创建只读权限账号****************--
--**************************************************--

--------------------------强制中断连接中的数据库
CREATE TABLE #temt(spid NVARCHAR(max),
ecid NVARCHAR(max),
status NVARCHAR(max),
logname NVARCHAR(max),
hostname NVARCHAR(max),
blk NVARCHAR(max),
dbname NVARCHAR(max),
cmd NVARCHAR(max),
request_id NVARCHAR(max)
)
INSERT INTO #temt
exec sp_who 

IF EXISTS(SELECT 1 FROM  #temt WHERE logname = 'sread')
BEGIN
        DECLARE @spid NVARCHAR(200)
        DECLARE @ksql NVARCHAR(200)
        DECLARE cur CURSOR
        FOR
         SELECT spid FROM  #temt WHERE logname = 'sread'        
        OPEN cur;
        FETCH NEXT FROM cur INTO @spid;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @ksql = 'KILL '+@spid
                EXEC (@ksql)
                FETCH NEXT FROM cur INTO @spid;        
            END;
        CLOSE cur;
        DEALLOCATE cur;
END
DROP TABLE #temt

--------------------------删除只读用户
IF EXISTS(Select 1 FROM sys.sysusers WHERE name ='sread')
BEGIN
    DROP USER sread
END
IF EXISTS(Select 1 FROM sys.syslogins WHERE name ='sread')
BEGIN
    DROP LOGIN sread
END

   
   


--创建只读用户(create login)
create login sread with password='hnsjt_123456', default_database=master

--为只读用户创建数据库用户(create user),在mydb数据库中的security中的user下可以找到新创建的dba
create user sread for login sread with default_schema=dbo


--------------------------每个数据库添加只读权限角色
        
        DECLARE @DatabaseName VARCHAR(255);
        DECLARE @user VARCHAR(255);
        SET @user = 'sread'
        DECLARE @sql VARCHAR(8000);
        DECLARE cur CURSOR
        FOR
           SELECT name FROM sys.databases
           
        OPEN cur;
        FETCH NEXT FROM cur INTO @DatabaseName;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @sql = 'USE ['+@DatabaseName+'] 
                    IF EXISTS(Select 1 FROM sys.sysusers WHERE name = '''+@user+''')
                    BEGIN
                        EXEC sp_revokedbaccess '''+@user+'''
                    END
                    create user '+@user+' for login '+@user+' with default_schema=dbo
                    EXEC sp_addrolemember ''db_datareader'','''+@user+'''
                '
                EXEC (@sql);        
                FETCH NEXT FROM cur INTO @DatabaseName;
        
            END;
        CLOSE cur;
        DEALLOCATE cur;
        
        

3.添加链接服务器

------查询现有链接服务器状态
    SELECT * FROM SYS.servers
    
--创删除远程链接服务器

--删除运行本地与远程之间的用户映射
execute sys.sp_droplinkedsrvlogin @rmtsrvname='100.80.10.30',@locallogin=null
--删除链接服务器
execute sys.sp_dropserver @server='100.80.10.30',@droplogins='droplogins'


--添加远程链接服务器

--创建远程链接服务器
execute sys.sp_addlinkedserver @server='100.80.10.30',    --被访问的服务器别名
        @srvproduct='',
        @provider='SQLOLEDB',
        @datasrc='100.80.10.30'    --被访问的服务器地址(IP地址,端口号\服务器名称)
--创建本地用户与远程服务器中用户之间的映射
execute sys.sp_addlinkedsrvlogin @rmtsrvname='100.80.10.30',    --被访问的服务器别名
        @useself='false',    --是否通过模拟本地登录名或显式提交登录名和密码来连接到远程服务器
        @locallogin=null,    --本地登录
        @rmtuser='lwsjbackgr',    --用户名
        @rmtpassword='hnsjt_lwsjdb@2019'    --密码
go

4.查询CPU

DECLARE @ts_now bigint
set @ts_now = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
FROM master.sys.dm_os_sys_info WITH (NOLOCK)); 
SELECT
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]  ,
SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
(100 - SystemIdle - SQLProcessUtilization) AS [Other Process CPU Utilization]
FROM (
      SELECT
   record.value('(./Record/@id)[1]', 'int') AS record_id,
      record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
      record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS [SQLProcessUtilization],
   [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM master.sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE N'%%') AS x
      ) AS y
ORDER BY record_id DESC;

5.查询内存

SELECT  total_physical_memory_kb / 1024 AS [物理内存(MB)] ,  
        available_physical_memory_kb / 1024 AS [可用物理内存(MB)] ,  
        system_cache_kb / 1024 AS [系统缓存内存总量(MB)] ,  
        ( kernel_paged_pool_kb + kernel_nonpaged_pool_kb ) / 1024 AS [内核池内存总量(MB)] ,  
        total_page_file_kb / 1024 AS [操作系统报告的提交限制的大小(MB)] ,  
        available_page_file_kb / 1024 AS [未使用的页文件的总量(MB)] ,  
        system_memory_state_desc AS [内存状态说明]  
FROM    sys.dm_os_sys_memory
点赞
收藏
评论区
推荐文章
芝士年糕 芝士年糕
2年前
Linux操作系统性能指标监控与通知
最近整了一台服务器,搭建了web网站,整了一下监控系统,这样也方便,我用的是3A的服务器,服务挺不错的系统的性能指标监控是比较常见的针对系统的管理场景,比如系统有挖矿程序,或者系统本身存在高CPU进程(正常应用),除了CPU之外,也可以监控内存,硬盘
Easter79 Easter79
3年前
SQL Server配置Oracle链接服务器
1、在SQLServer服务器上安装Oracle客户端。安装时选择定制,勾选如下选项:!(https://static.oschina.net/uploads/space/2016/1213/105827_6YGf_921036.png)2、导入注册表项目,在服务器上新建文本保存以下内容后修改后缀为reg,然后运行。WindowsRe
Wesley13 Wesley13
3年前
RPC编程
1简介任何RPC客户机服务器程序的重要实体都包括IDL文件(接口定义文件)、客户机stub、服务器stub以及由客户机和服务器程序共用的头文件。客户机和服务器stub使用RPC运行时库通信。RPC运行时库提供一套标准的运行时例程来支持RPC应用程序。了解运行时例程的内部情况有助于进一步了解RPC编程。在一般
Stella981 Stella981
3年前
Eg挨蒙—Zabbix监控进程占cpu和内存大小及批量监控端口
监控简介:通过shell脚本的方式,实现对进程占cpu百分比和内存大小的监控,通过python脚本方式,实现批量监控服务器端口。一、监控进程占cpu的百分比和内存的大小1、在agent端编写脚本\root@monitorsbin\$cat/usr/local/zabbix/scripts/processtatus.sh!/
Stella981 Stella981
3年前
SQL Server配置Oracle链接服务器
1、在SQLServer服务器上安装Oracle客户端。安装时选择定制,勾选如下选项:!(https://static.oschina.net/uploads/space/2016/1213/105827_6YGf_921036.png)2、导入注册表项目,在服务器上新建文本保存以下内容后修改后缀为reg,然后运行。WindowsRe
Easter79 Easter79
3年前
Tensorflow的gRPC编程(一)
首先了解什么叫RPC,为什么要RPC,RPC是指远程过程调用,也就是说两台服务器A,B,一个应用部署在A服务器上,想要调用B服务器上应用提供的函数/方法,由于不在一个内存空间,不能直接调用,需要通过网络来表达调用的语义和传达调用的数据。比如说,一个方法可能是这样定义的:EmployeegetEmployeeByName(StringfullN
Stella981 Stella981
3年前
Mac 终端链接远程服务器
直接在终端输入命令: ssh p 服务器端口号 用户名@服务器IP ssh i 秘钥文件 p 服务器端口号 用户名@服务器IP例如: ssh i pubkey_to_linux p 65500 root@192.168.1.18     //带秘钥文件
Stella981 Stella981
3年前
LoadRunner监控window系统各项指标详解
一.监控系统时,需要监控的项System系统Processor处理器Memory 内存PhysicalDisk磁盘Server服务器!(https://oscimg.oschina.net/oscnet/a3ab8a6dcfacffe98f6085a9f338de408d6.png
Wesley13 Wesley13
3年前
mysql远程备份
相关链接:https://blog.csdn.net/LiuHuan\_study/article/details/81512831https://www.cnblogs.com/ryanzheng/p/8444128.html1.跨服务器备份原理:服务器A192.168.100.200mysqlroot123456
云平台监控指标的设定
本文分享自天翼云开发者社区@《》,作者:AE86上山了为了确保存储和服务器能应付不断增长的业务需求,对磁盘资源、内存和CPU资源、宿主操作系统进行监控是必要的。1.磁盘资源服务器硬盘是磁盘资源中最慢的组件,在企业数据中心,仔细设计存储子系统,不要让它成为主
Easter79
Easter79
Lv1
今生可爱与温柔,每一样都不能少。
文章
2.8k
粉丝
5
获赞
1.2k