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分钟以内。

具体升级步骤：

加载SqlServer安装镜像包，要注意对应相应的版本
打开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 企业版无法使用所有 CPU 内核

清理无法删除的作业

用户导出与导入

总结

作业导出与导入

DBLink导出与导入

临时完全备份

删除数据模板

空间快捷链接

页面树结构

5014-SQLServer 日常维护操作指引

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

清理无法删除的作业

用户导出与导入

总结

作业导出与导入

DBLink导出与导入

临时完全备份

删除数据模板