SQLServer 企业版无法使用所有 CPU 内核

问题现象：

SqlServer不能使用操作系统CPU最大内核数，无法发挥服务器最佳性能，计算资源闲置浪费。

问题原因：

因从SqlServer 2012之后的版本，企业版本分为 Entetprise CAL Edition 和 Entetprise Core Edition，其中Entetprise CAL Edition 许可限制SqlServer只能使用物理CPU 20核数，逻辑处理器最大为40，Entetprise Core Edition版本则是无限制操作系统最大的内核数。

按 SQL Server 版本划分的计算能力限制 - SQL Server | Microsoft Docs

具体说明，可参考上述微软官方KB

适用范围：

美宜佳所有相关SqlServer 2012以上版本的数据库。

适用版本：

SqlServer 2012 以上的Entetprise Edition， 注意：SqlServer 2012以下版本不受影响。

如何查询CPU最大核数和SQLServer使用最大内核数？

查看正在使用的CPU逻辑处理器

use master
select * from sys.dm_os_schedulers where is_online=1 and status='VISIBLE ONLINE'

查看无法使用的CPU逻辑处理器

use master
select * from sys.dm_os_schedulers where is_online=0

或是通过Windows任务管理器--性能--进行查看当前CPU内核数。

问题解决方法

进行SqlServer序列号更换，升级为SqlServer Entetprise Core Edition

重要注意事项：升级时SQLSERVE服务要处于运行状态。

升级前需要关闭数据库相应的活动会话，包含前端程序，数据库作业，确保没有活动事务和活动会话正在进行，因为升级过程会重启SQLSERVER数次，以防止数据库出现实例恢复，增加风险，整机升级过程在10分钟以内。

具体升级步骤：

  1. 加载SqlServer安装镜像包，要注意对应相应的版本
  2. 打开CMD，cd 目录，切换到虚拟光驱盘，运行以下命令进行版本升级。

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID="TBR8B-BXC4Y-298NV-PYTBY-G3BCP" /IACCEPTSQLSERVERLICENSETERMS

其中 /INSTANCENAME 为SQLSERVER实例名，一般默认为MSSQLSERVER

PID则为需要升级的序列号，进行相应的替换即可。

      3.升级完成后，可再次通过上述SQL查询可用的处理器，正常情况下可以看到所有的逻辑处理器都为'VISIBLE ONLINE' 状态，也可以通过select @@version进行查看当前数据库版本

     4. 检查数据库运行状态和前端程序是否正常运行。

清理无法删除的作业

USE [msdb]
DECLARE @job_name VARCHAR(100)
SET @job_name = N'backup_diff.Subplan_1'    -- SELECT * FROM sysjobs_view; 可以通过这句查询作业名称，直接拷过来，避免手动输错
 
--注：jobName为维护计划对应的jobName
--删除在计划里面的日志
DELETE
    sysmaintplan_log
FROM
    sysmaintplan_subplans AS subplans
    INNER JOIN sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
    INNER JOIN sysmaintplan_log ON subplans.subplan_id =sysmaintplan_log.subplan_id
WHERE
    (syjobs.name = @job_name)
 
--删除代理的作业
DELETE
    sysjobschedules FROM sysjobs_view v
    INNER JOIN sysjobschedules o ON v.job_id=o.job_id
WHERE
    v.name=@job_name
 
--删除子计划
DELETE
    sysmaintplan_subplans
FROM
    sysmaintplan_subplans AS subplans    
    INNER JOIN sysjobs_view AS syjobs ON subplans.job_id = syjobs.job_id
WHERE
    (syjobs.name = @job_name)
 
--删除作业
DELETE FROM msdb.dbo.sysjobs_view WHERE name = @job_name

SELECT * FROM sysjobs_view

用户导出与导入

DBA 经常会遇到的一个场景就是数据库的迁移，把数据库从A服务器迁移到B服务器。在数据库完成迁移后，我们还需要把相关的其他一些服务器级别的对象迁移过去，比如作业，登录账户，链接服务器等。今天主要分享导出登录账户的脚本

/*
--SQL 数据库导出迁移用户账户及权限
--EG. [sp_help_revlogin]
*/

--sp_hexadecimal
USE master;
GO
IF OBJECT_ID('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal;
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue VARBINARY(256),
@hexvalue VARCHAR(256) OUTPUT
AS
DECLARE @charvalue VARCHAR(256);
DECLARE @i INT;
DECLARE @length INT;
DECLARE @hexstring CHAR(16);
SELECT @charvalue = '0x';
SELECT @i = 1;
SELECT @length = DATALENGTH(@binvalue);
SELECT @hexstring = '0123456789ABCDEF';
WHILE (@i <= @length)
BEGIN
DECLARE @tempint INT;
DECLARE @firstint INT;
DECLARE @secondint INT;
SELECT @tempint = CONVERT(INT, SUBSTRING(@binvalue, @i, 1));
SELECT @firstint = FLOOR(@tempint / 16);
SELECT @secondint = @tempint - (@firstint * 16);
SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint + 1, 1) + SUBSTRING(@hexstring, @secondint + 1, 1);
SELECT @i = @i + 1;
END;
SELECT @hexvalue = @charvalue;
GO

----- sp_help_revlogin

IF OBJECT_ID('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin;
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin] @login_name sysname = NULL
AS
DECLARE @name sysname;
DECLARE @type VARCHAR(1);
DECLARE @hasaccess INT;
DECLARE @denylogin INT;
DECLARE @is_disabled INT;
DECLARE @PWD_varbinary VARBINARY(256);
DECLARE @PWD_string VARCHAR(514);
DECLARE @SID_varbinary VARBINARY(85);
DECLARE @SID_string VARCHAR(514);
DECLARE @tmpstr VARCHAR(1024);
DECLARE @is_policy_checked VARCHAR(3);
DECLARE @is_expiration_checked VARCHAR(3);
DECLARE @defaultdb sysname;
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid,
p.name,
p.type,
p.is_disabled,
p.default_database_name,
l.hasaccess,
l.denylogin
FROM sys.server_principals p
LEFT JOIN sys.syslogins l
ON (l.name = p.name)
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name <> 'sa';
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid,
p.name,
p.type,
p.is_disabled,
p.default_database_name,
l.hasaccess,
l.denylogin
FROM sys.server_principals p
LEFT JOIN sys.syslogins l
ON (l.name = p.name)
WHERE p.type IN ( 'S', 'G', 'U' )
AND p.name = @login_name;
OPEN login_curs;
FETCH NEXT FROM login_curs
INTO @SID_varbinary,
@name,
@type,
@is_disabled,
@defaultdb,
@hasaccess,
@denylogin;
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.';
CLOSE login_curs;
DEALLOCATE login_curs;
RETURN -1;
END;
SET @tmpstr = '/* sp_help_revlogin script ';
PRINT @tmpstr;
SET @tmpstr = '** Generated ' + CONVERT(VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */';
PRINT @tmpstr;
PRINT '';
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT '';
SET @tmpstr = '-- Login: ' + @name;
PRINT @tmpstr;
IF (@type IN ( 'G', 'U' ))
BEGIN -- NT authenticated account/group 
SET @tmpstr
= 'CREATE LOGIN ' + QUOTENAME(@name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']';
END;
ELSE
BEGIN -- SQL Server authentication
SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS VARBINARY(256));
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT;
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT;
SELECT @is_policy_checked = CASE is_policy_checked
WHEN 1 THEN
'ON'
WHEN 0 THEN
'OFF'
ELSE
NULL
END
FROM sys.sql_logins
WHERE name = @name;

SELECT @is_expiration_checked = CASE is_expiration_checked
WHEN 1 THEN
'ON'
WHEN 0 THEN
'OFF'
ELSE
NULL
END
FROM sys.sql_logins
WHERE name = @name;

SET @tmpstr
= 'CREATE LOGIN ' + QUOTENAME(@name) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = '+ @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']';

IF (@is_policy_checked IS NOT NULL)
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked;
END;
IF (@is_expiration_checked IS NOT NULL)
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked;
END;
END;
IF (@denylogin = 1)
BEGIN -- login is denied access 
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME(@name);
END;
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access 
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME(@name);
END;
IF (@is_disabled = 1)
BEGIN -- login is disabled 
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME(@name) + ' DISABLE';
END;

select @tmpstr=@tmpstr+ N';EXEC sp_addsrvrolemember N''' +sp.name+ ''' ,N''' + rsp.name+'''; '
FROM sys.server_principals sp 
LEFT JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id 
LEFT JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id 
where rsp.name is not NULL
AND sp.name=@name

PRINT @tmpstr;
END;
FETCH NEXT FROM login_curs
INTO @SID_varbinary,
@name,
@type,
@is_disabled,
@defaultdb,
@hasaccess,
@denylogin;
END;
CLOSE login_curs;
DEALLOCATE login_curs;
RETURN 0;

--生成创建登录账号脚本
exec master..sp_help_revlogin

把脚本COPY到新的服务器上执行

总结

使用这个办法有2个好处。

1.可以批量同步所有需要的登录账号 

2.由于产生的SID是相同的，不会有孤立账号的问题。在ALWAY ON环境下，我们就需要采用这种方式。不然没有ALWAYS ON 切换后都需要手动处理 孤立账号的问题

作业导出与导入

在SSMS 中，选中如下  作业的文件夹

然后按F7 ，就会出现下面的界面，然后按CTRL 选中需要导出的作业

右键 编写作业为--》 CREATE 到

就能生成所有需要的作业的脚本，然后在迁移后的服务器上面执行。

注意事项
如果作业的所有者是windows 账号，那么迁移后由于服务器名称不一样，可能不存在相同的登录账号。

在执行脚本时会提示如下错误：

把@owner_login_name 修改为异地节点存在的账号，问题就解决。

DBLink导出与导入

在SSMS 中，选中如下  链接服务器 的文件夹

然后按F7 ，就会出现下面的界面，然后按CTRL 选中需要导出的链接服务器

然后把生成的脚本，COPY到新的服务器上面，然后将远程登录密码改为实际密码（脚本中已替换为########，不可直接执行），执行就可以了。在建立完成后一定要测试链接服务器是不是可用。

临时完全备份

USE master;
GO
DECLARE @FileName NVARCHAR(256);
SET @FileName = N'E:\backup\备份数据库名_FullBackup_20200707.bak';
BACKUP DATABASE [备份数据库名]
TO DISK = @FileName
WITH COMPRESSION,
INIT,
SKIP,
FORMAT;
GO

DECLARE @backupSetId AS INT;
SELECT @backupSetId = position
FROM msdb..backupset
WHERE database_name = N'备份数据库名'
AND backup_set_id =
(
SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = N'备份数据库名'
);
IF @backupSetId IS NULL
BEGIN
RAISERROR(N'验证失败。找不到数据库“备份数据库名”的备份信息。', 16, 1);
END;
RESTORE VERIFYONLY
FROM DISK = N'E:\backup\备份数据库名_FullBackup_20200707.bak'
WITH FILE = @backupSetId,
NOUNLOAD,
NOREWIND;
GO

删除数据模板

删除表数据需要再三确认，可以删除后先进行备份，在进行删除

select * into table_name_bak  FROM  table_name  with (nolock);  --删除数据之前备份

当删除数据行数超过2万行时，为避免锁表阻塞，请使用循环删除的方法

脚本范例如下：

DECLARE @coun INT;
DECLARE @i INT;
SET @i=0 ;
SELECT @coun=COUNT(*)/5000 FROM table_name with (nolock) ; --汇总需要删除的数据行数，从而得到需要循环次数
WHILE @i<@coun+1                        
begin
      DELETE TOP (5000) FROM  table_name ;  --每次删除5000行

  SET @i=@i+1 ;

  WAITFOR DELAY '00:00:01';  --每次循环延迟1S后继续循环

END

表关联删除，删除时必须是where 条件，不能使用 ON

DECLARE @coun INT;
DECLARE @i INT;
SET @i=0 ;
SELECT @coun=COUNT(*)/5000 FROM aa_test a , ab_test b WHERE a.id=b.id ; --汇总需要删除的数据行数，从而得到需要循环次数 
WHILE @i<@coun+1                        
begin
      DELETE TOP (5000) a FROM aa_test a , ab_test b WHERE a.id=b.id  ;  --每次删除5000行 ，删除时必须是where 条件，不能使用 ON

  SET @i=@i+1 ;

  WAITFOR DELAY '00:00:01';  --每次循环延迟1S后继续循环

END

建议删除较大数据接入相关负责人提醒（短信或邮件）

SQLServer 内存分析

SELECT type,--Clerk的类型 
sum(virtual_memory_reserved_kb)as vm_Reserved_kb,--保留的内存 
sum(virtual_memory_committed_kb)as vm_Committed_kb,--提交的内存 
sum(awe_allocated_kb)as awe_Allocated_kb,--开启AWE后使用的内存 
sum(shared_memory_reserved_kb)as sm_Reserved_kb,--共享的保留内存 
sum(shared_memory_committed_kb)as sm_Committed_kb,--共享的提交内存 
sum(single_pages_kb)as SinlgePage_kb,-- Buffer Pool中的Stolen的内存 
sum(multi_pages_kb)as MultiPage_kb-- MemToLeave的内存
FROM sys.dm_os_memory_clerks
GROUP BY type ORDER BY type

--查询Buffer Pool/Memory To Leave
SELECT
sum(virtual_memory_reserved_kb)AS [Reserved],
sum(virtual_memory_committed_kb)AS [Commit],
sum(virtual_memory_reserved_kb)/sum(virtual_memory_committed_kb) AS[Reserved/Commit],
sum(single_pages_kb)+sum(multi_pages_kb) AS [Stolen],
(sum(virtual_memory_committed_kb)+sum(single_pages_kb))/1024/1024 AS [Buffer Pool],
sum(multi_pages_kb) AS[MemToLeave],
SUM(awe_allocated_kb)AS [AWEAllocated]
FROM sys.dm_os_memory_clerks

--查询SQLSERVER内存使用

select * from sys.dm_os_process_memory

创建拥有所有数据库权限的账户(不含系统数据库)

SET NOCOUNT ON;

DECLARE @database VARCHAR(200) = '';
DECLARE @sql_usedatabase VARCHAR(200) = '';
DECLARE @sql VARCHAR(MAX) = '';
DECLARE @databaseUsername VARCHAR(200) = '数据库用户名'; --修改为实际数据用户名
DECLARE @databaseUserPassword VARCHAR(200) = '123'; --用户密码

SET @sql
    = 'USE [master]'
	+CHAR(10)+ 'GO' 
	+CHAR(10)
	+'CREATE LOGIN [' + @databaseUsername + '] WITH PASSWORD=N''' + @databaseUserPassword + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'
	+CHAR(10)+ 'GO' 
	+CHAR(10);

PRINT @sql;

IF OBJECT_ID('tempdb.dbo.#DD') IS NOT NULL
    DROP TABLE #DD;

SELECT ROW_NUMBER() OVER (ORDER BY dbid) id,
       *
INTO #DD
FROM master..sysdatabases
WHERE sid != 0x01;

DECLARE @i INT = 1;
DECLARE @num INT =
        (
            SELECT COUNT(1) FROM master..sysdatabases WHERE sid != 0x01
        );
WHILE @i <= @num
BEGIN
    SET @database =
    (
        SELECT name FROM #DD WHERE id = @i
    );
    SET @sql_usedatabase = 'USE ' + @database + ' ' 
	+CHAR(10)+ 'GO' 
	+CHAR(10);

    SET @sql
        = @sql_usedatabase + 'CREATE USER [' + @databaseUsername + '] FOR LOGIN [' + @databaseUsername+ '] WITH DEFAULT_SCHEMA=[dbo]'
		  +CHAR(10)+ 'GO' 
		  +CHAR(10)
          + @sql_usedatabase + 'EXEC sp_addrolemember ''db_owner'', ' + @databaseUsername 
		  +CHAR(10)+ 'GO'
		  +CHAR(10);

    PRINT @sql;
    SET @i = @i + 1;
END;

数据库作业所有者账号替换

查询作业信息

SELECT [job].job_id,[job].[name] AS '作业名称'
,[dp].[name] AS '作业创建者'
, CASE [job].[enabled]
WHEN 1 THEN '是'
WHEN 0 THEN '否'
END AS '是否启用',
(case when subsystem='SSIS' then isnull(pr.name,'SQLSERVER 代理服务账户') else '无' end) '代理账户'
FROM [msdb].[dbo].[sysjobs] AS [job]
LEFT JOIN [msdb].[sys].[servers] AS [sv]
ON [job].[originating_server_id] = [sv].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [cat]
ON [job].[category_id] = [cat].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [step]
ON [job].[job_id] = [step].[job_id]
AND [job].[start_step_id] = [step].[step_id]
LEFT JOIN [msdb].[sys].[server_principals] AS [dp]
ON [job].[owner_sid] = [dp].[sid]
left join msdb.dbo.sysproxies pr on pr.proxy_id=[step].proxy_id
ORDER BY [dp].[name];

修改作业运行账号

EXEC msdb.dbo.sp_update_job @job_id=N'9BEAAA7C-6842-4C71-94D7-0F6D1997855A' --这个作业ID是根据第一步中查询的作业信息获取得到
, @owner_login_name=N'sa' --确保用户是存在且有效的
GO

修改作业运行账号(循环执行所有作业)

use msdb
go

declare @id varchar(50)
declare cur cursor read_only for
		select job_id from  msdb.dbo.sysjobs   WHERE SUSER_SNAME(owner_sid)='sa'
open cur
FETCH NEXT FROM cur  into @id
WHILE @@FETCH_STATUS = 0 
begin 
   EXEC msdb.dbo.sp_update_job @job_id=@id, @owner_login_name=N'sa'
   FETCH NEXT FROM cur  into @id
end
close cur
deallocate cur

获取占用 CPU 资源排行

-- 获取消耗时间较长的SQL
  SELECT TOP 50    

  total_worker_time/1000 AS [总消耗CPU 时间(ms)],

  execution_count [运行次数],

  qs.total_worker_time/qs.execution_count/1000. as [平均消耗CPU 时间(ms)],   

 SUBSTRING(qt.text,qs.statement_start_offset/2+1,         

(case when qs.statement_end_offset = -1 then DATALENGTH(qt.text)        

 else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1)as [使用CPU的语法],

 qt.text [完整语法],   

 qt.dbid, dbname=db_name(qt.dbid),

 qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName,

local_net_address '服务器的IP'

 FROM sys.dm_exec_query_stats qs

 CROSS apply sys.dm_exec_sql_text(qs.sql_handle) as qt

 CROSS APPLY (SELECT distinct local_net_address

              FROM sys.dm_exec_connections

              WHERE local_net_address IS NOT NULL) qp

ORDER BY         total_worker_time DESC

链接服务器账号替换

查看连接服务器使用的账号情况

查看账号
SELECT distinct SRV_NAME = srv.name,
SRV_PROVIDERNAME = srv.provider,
SRV_PRODUCT = srv.product,
SRV_DATASOURCE = srv.data_source,
g.remote_name
FROM sys.servers srv
LEFT JOIN sys.linked_logins g
ON g.server_id = srv.server_id
ORDER BY g.remote_name;

设置为“使用此安全上下文建立连接”（不推荐）

因为使用安全上下文，只要有登录数据库的账号，就可以使用此链接服务器，安全性不高。

EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = N'192.168.112.5',
@locallogin = NULL,
@useself = N'False',
@rmtuser = N'用户名',
@rmtpassword = N'密码';

执行后效果等同于在此界面如此操作

设置为“不使用安全上下文建立连接”（推荐）

--设置为“不使用安全上下文建立连接”
EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = N'192.168.112.5', --根据实际服务器地址修改
                              @locallogin = NULL,
                              @useself = N'False',
                              @rmtuser = N'';
GO

--设置“本地服务器登录到远程服务器的映射”
EXECUTE sys.sp_addlinkedsrvlogin @rmtsrvname = '192.168.112.5', --被访问的服务器别名
								 @locallogin = N't',           --本地登录
                                 @useself = 'false',            --是否通过模拟本地登录名或显式提交登录名和密码来连接到远程服务器
                                 @rmtuser = 'mpos',             --用户名
                                 @rmtpassword = '######';   --密码
GO

执行后效果等同于在此界面如此操作

删除“本地服务器登录到远程服务器的映射”

EXEC master.dbo.sp_droplinkedsrvlogin @rmtsrvname = N'192.168.112.5', @locallogin = N'本地服务器登录名'


  • 无标签
写评论...