SqlServer：SqlServer（服务器磁盘监控，创建管理员账号分配权，添加链接服务器，查询CPU，查询内存）

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定时作业，每天自动更新即可

　　如图：

 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