标签归档:sql优化

mssql sqlserver 加快数据库回滚的速度–ACCELERATED_DATABASE_RECOVERY用法说明


摘要:
下文通过举例的方式,讲述sqlserver中加速数据库回滚时间的方法,如下所示:
实验环境:sql server 2019


当一个长事务被意外终止或停止时,回滚此事务需要大量的时间,下文讲述sqlserver2019中通过开启

ACCELERATED_DATABASE_RECOVERY
属性,来提升sqlserver事务回滚时间的方法

例: 测试方案一

---1.创建数据库 

CREATE DATABASE [maomao];
GO
USE [maomao]
GO
SET STATISTICS TIME ON
GO

---2.开启事务,并运行以下脚本
BEGIN TRANSACTION
 

DROP TABLE IF EXISTS testTable;
 
---生成新数据表,并插入数据
SELECT TOP 10000000 a1.* 
INTO testTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

 
/*
SQL Server Execution Times:
CPU time = 1632000 ms, elapsed time = 26108 ms.
*/

---3回滚事务
ROLLBACK
GO 
/*
SQL Server Execution Times:
CPU time = 386000 ms, elapsed time = 12603 ms.
*/ 

例:
测试方案2:
设置 ACCELERATED_DATABASE_RECOVERY属性为ON

 ALTER DATABASE [maomao] 
  SET ACCELERATED_DATABASE_RECOVERY = ON;
  GO 


---2.开启事务,并运行以下脚本
BEGIN TRANSACTION
 

DROP TABLE IF EXISTS testTable;
 
---生成新数据表,并插入数据
SELECT TOP 10000000 a1.* 
INTO testTable 
FROM sys.all_objects AS a1
CROSS JOIN sys.all_objects AS a2
CROSS JOIN sys.all_objects AS a3
GO

 
/*
SQL Server Execution Times:
CPU time = 1632000 ms, elapsed time = 26108 ms.
*/

---3回滚事务
ROLLBACK
GO 
/*
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
*/ 


从以上的两个测试用例上,我们可以看出设置ACCELERATED_DATABASE_RECOVERY属性后,rollback性能得到了质的飞跃。

mssql sqlserver 中亿万条数据表如何做到秒查


摘要:
下文讲述优化公司的报表,使用报表从1个小时的运行效率提升到半秒不到就显示出数据的方法分享(非powerBI),如下所示:
实验环境:sql server 2008 R2


该系统是我集团公司的BOSS基础业务管理系统,已运行15年之久,期间经过多次的版本迭代,系统累计了大量的数据。系统数据库中,主营业务记录表的数据都达到了上亿条,里面缓存表都达到了百万级,数据报表的压力可想而知。。。
由于长期无人监管,大家都抱着和稀泥的方式去做事,所以即使无法查询出数据,也无人反馈,可以说公司的IT已经不做事了(因为大家都好多年没涨薪水了。。。)公司的各项信息系统都处于无人监管的状态。最近几年市场竞争激烈,老板才想起来,要搞一下管理,所以才想到了IT,才想到了管理,当然也想到了我们的系统该修一修了。
下文将讲述,我们第一步对数据报表的修复

实践思路:

1.把索引都不补上
索引都补上之后,发现情况还是没有好转,数据的检索速度没有任何改善,
2.对表分区
当我对表 按照日期进行分区后,所有的检索都没有任何改善
3.对sql语句进行分析:
我们发现where条件中采用很多如下的写法:

where 条件中有 like ‘%’+变量+’%’
where 条件中有 case end函数
表连接中存在多次连接同一表的操作
sql返回的数据中存在*

发现了sql脚本中的问题时,此时我们需要做的工作是对sql脚本进行改写:

1.剔除 where 条件中case end函数
对查询条件所对应的表建立临时表,然后将临时表的数据进行汇总
使用临时表可以减少表扫描
2.将in修改为exists ,not in变更为 not exists关键字
3.同使用部门确认,将 where 条件中有 like ‘%’+变量+’%’ 变更为 where 条件中有 like ‘%’+变量+”的可行性
4.在对表的读取时,加入 with(nolock)

通过以上的改写后,sql脚本的运行时间从1小时变更为半秒中。

通过反复的测试和总结,这次效率提升点在于使用了临时表,减少物理表的扫描次数,提升了系统性能

mssql sqlserver 2008 sql查询脚本导致CPU升至100%的解决方法分享


摘要:
下文讲述一次sql脚本导致CPU飘高的处理方法,如下所示:
实验环境:sql server 2008 R2


今天有同事对四张千万级大表进行join查询,导致CPU升至100%,影响系统的正常使用,
下文讲述通过将sql查询设置为并行操作后,CPU可正常工作,设置方法如下所示:

   sp_configure 'show advanced options',1 reconfigure with override

   sp_configure 'max degree of parallelism',4 reconfigure with override