SQL Server-聚焦存储过程性能优化、数据压缩和页压缩提高IO性能(一)

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

前言

关于SQL Server基础系列尚未结束,还剩下最后一点内容未写,后面会继续。有园友询问我什么时候开始写SQL Server性能系列,估计还得等一段时间,最近工作也比较忙,但是会陆陆续续的更新SQL Server性能系列,本篇作为性能系列的基本引导,让大家尝尝鲜。在涉及到SQL Server性能优化时,我看到的有些文章就是一上来列出SQL Server的性能优化条例,根本没有弄清楚为什么这么做,当然也有可能是自己弄懂了,只是作为备忘录,但是到了我这里,我会遵循不仅仅是备忘录,还要让各位园友都能易于理解,不至于面试时只知道其果,不知其因。

存储过程性能优化

禁用受影响函数通过设置SET NOCOUNT ON

如上当我们进行查询时总是会返回受影响的行数,这种消息只是对于我们调试SQL时有帮助,其他再无其他帮助,我们可以通过设置 SET NOCOUNT ON 来禁用这个特性,这将有显著的性能提升,有利于减少网络流量的传输。在存储过程中我们像如下设置。

复制代码
CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Your Procedure code
SELECT [address], city, companyname FROM Sales.Customers
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
复制代码

使用架构名称+对象名称

这个建议在开篇我们就已经明确讲过,通过设置架构名称的对象名称是最合格的,此时将直接执行编译计划而不是在使用缓存计划时还要去其他可能的架构中去查找对象。所以我们建议总是像如下使用。

复制代码
SELECT * FROM Sales.Customers -- 推荐
-- 而不是
SELECT * FROM Customers -- 避免

--调用存储过程如下
EXEC dbo.MyProc -- 推荐
--而不是
EXEC MyProc -- 避免
复制代码

存储过程名称禁止以sp开头 

如果一个存储过程名称以sp开头,此时数据库查询引擎首先将在master数据库中去查找存储过程然后再是在当前会话的数据库中去查找存储过程。

使用IF EXISTS (SELECT 1) 而不是 (SELECT *)

网上随便一搜索就看到如下查询一行是否存在的SQL语句。

复制代码
declare @message varchar(200),
    @name varchar(200)
if exists(select * from students where 学号='1005')
begin
set @message='下列人员符合条件:'
print @message
set @name=(select 姓名 from students where 学号='1005')
print @name
end
else
    begin
        set @message='没有人符合条件'
        print @message
    end
go
复制代码

当判断一条记录是否在表中存在时我们使用IF EXISIS,如果在IF EXISTS中内部语句中有任何值返回则返回TRUE。如上述

if exists(select * from students where 学号='1005')

此时将返回学号 = '1005'的这一行,而如果用1代替则不用返回满足条件的这一行记录,在查询时为了网络传输我们应该最小化处理数据,所以我们应该像如下做返回单值1.

IF EXISTS (SELECT 1  FROM Sales.Customers
WHERE [address] = 'Obere Str. 0123')

使用sp_executesql而不是使用EXECUTE

sp_executesql支持使用参数而不是使用EXECUTE来提高代码重用,动态语句的查询执行计划只有对每个字符包括大小写、空格、参数、注释相同的语句才重用。如果利用EXECUTE执行如下动态SQL语句。

DECLARE @Query VARCHAR(100)
DECLARE @contactname VARCHAR(50)
SET @contactname = 'Allen, Michael'
SET @Query = 'SELECT * FROM Sales.Customers WHERE contactname = ' + CONVERT(VARCHAR(3),@contactname)
EXEC (@Query)

执行查询计划如下,如果再一次使用不同的@contactname值,此时查询执行计划将再次创建@contactname不会达到重用的目的

如果我们使用利用sp_executesql像如下查询,如果对于不同的@contactname值,此时查询执行计划将被会重用,将会达到提高性能的目的。

DECLARE @Query VARCHAR(100)
SET @Query = 'SELECT * FROM Sales.Customers WHERE contactname = @contactname'
EXECUTE sp_executesql @Query,N'@contactname VARCHAR(50)',@contactname = 'Allen, Michael'

对于异常处理利用TRY-CATCH处理

在SQL Server 2005之后开始支持异常处理,如果我们进行异常语句检查处理,如果出现异常将不会导致利用更多的代码来消耗更多的资源和时间。

尽可能使事务简短

事务的长度会影响阻塞和死锁。直到事务结束排他锁不会释放,在高隔离级别中共享锁的生命周期更长, 因此,冗长的事务意味着锁定的时间更长,锁定的时间越长最终导致阻塞,在有些情况下,阻塞会转变成死锁,所以为了更快的执行、更少的阻塞,我们应该使事务的长度尽量简短。

数据压缩和页压缩提高IO

SQL Server主要的性能取决于磁盘IO效率,改善IO意味着提高性能,在SQL Server 2008中提供了数据和备份压缩功能。下面我们一起来看看。

数据压缩

数据压缩意味着磁盘保留的空间减少,数据压缩可以配置在表上的聚集索引、非聚集索引、索引视图或者分区表或者分区索引。数据压缩可以在两个级别中实现:一个是行压缩,另外一个是页压缩,甚至页压缩会自动实现行压缩,当通过CREATE TABLE、CREATE INDEX语句时会压缩表和索引,为了改变一个表、索引和分区的压缩状态通过 ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH语句实现。当一个堆栈的压缩状态改变后,此时非聚集索引将重建,在行压缩中,使用以下四种方法来消除未使用的空间。

1.减少记录中的元数据开销。

2.所有数字类型(INT、NUMERIC等)和基于数字类型(如DATETIME、MONEY)将会转换成可变长度值,例如INT类型在压缩后所有未被消耗的空间将会被回收。比如我们知道0-255可以存储一个字节中,若我们的值是100,在磁盘中INT是4个字节,但是在压缩之后其余3个字节将会被回收。

3.CHAR和NCHAR会转换成可变长度存储,在压缩之后对于实际存储的数据将不会再有空格,比如我们定义CHAR(10),此时我们存储的数据为Jeffcky,默认情况下将会预留10个字节,此时将会有3个字节为空格补充,但是在压缩之后这3个字节将会被回收,仅仅只预留7个字节。

4.所有NULL和0都已经过优化不需要字节。

页压缩

页压缩将会通过以下三种方法实现。

1.上述已经提到的所有。

2.前缀压缩:在每页上的每一列,被标识的所有行的公共值以及存储在标题下的每一行,在压缩之后公共值将替换为标题行的引用。

3.字典压缩:在字典压缩中,每一页中的每一列标识公共值,是将存储在标题行的第二行中,然后这些公共值将替换为新行中的值的引用。

说了这么多,具体到底是怎样使用的呢?请继续往下看,我们通过使用临时数据库插入748条数据,如下:

复制代码
USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, '压缩测试')
GO 748
复制代码

接下来进行行压缩和页压缩来和原始未压缩进行比较看看。

复制代码
-- 原始值
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = 设置行压缩
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = 设置页压缩
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = 没有压缩
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = NONE);
GO
EXEC sp_spaceused TestCompression
GO
复制代码

结果如下:

压缩后数据显然变少了,如果数据量足够大页压缩比行压缩的数据会更少,从而减少IO提高性能,不知道看到本文的你是否在生产服务上是否已经应用过呢,下次可以试试。

总结

本节我们稍微讲解了下存储过程的性能优化和数据压缩提高IO,当然还有其他优化,这里只是作为后续开篇。





本文转自Jeffcky博客园博客,原文链接:http://www.cnblogs.com/CreateMyself/p/6391759.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
12天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 数据处理 数据库
专坑同事的SQL写法:性能杀手揭秘
【8月更文挑战第29天】在日常的数据库开发与维护工作中,编写高效、清晰的SQL语句是每位数据工程师的必修课。然而,不当的SQL编写习惯不仅能降低查询效率,还可能给同事的工作带来不必要的困扰。今天,我们就来揭秘八种常见的“专坑同事”SQL写法,助你避免成为那个无意间拖慢整个团队步伐的人。
31 1
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL 慢查询秘籍】慢SQL无处遁形!实战指南:一步步教你揪出数据库性能杀手!
【8月更文挑战第24天】本文以教程形式深入探讨了MySQL慢SQL查询的分析与优化方法。首先介绍了如何配置MySQL以记录执行时间过长的SQL语句。接着,利用内置工具`mysqlslowlog`及第三方工具`pt-query-digest`对慢查询日志进行了详细分析。通过一个具体示例展示了可能导致性能瓶颈的查询,并提出了相应的优化策略,包括添加索引、缩小查询范围、使用`EXPLAIN`分析执行计划等。掌握这些技巧对于提升MySQL数据库性能具有重要意义。
71 1
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
60 0
|
2月前
|
JSON 数据格式 Java
化繁为简的魔法:Struts 2 与 JSON 联手打造超流畅数据交换体验,让应用飞起来!
【8月更文挑战第31天】在现代 Web 开发中,JSON 成为数据交换的主流格式,以其轻量、易读和易解析的特点受到青睐。Struts 2 内置对 JSON 的支持,结合 Jackson 库可便捷实现数据传输。本文通过具体示例展示了如何在 Struts 2 中进行 JSON 数据的序列化与反序列化,并结合 AJAX 技术提升 Web 应用的响应速度和用户体验。
81 0
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
99 0
|
2月前
|
存储 SQL 数据库
如何使用 SQL Server 创建存储过程?
【8月更文挑战第31天】
72 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL性能调优的神奇之处:如何用优化技巧让你的数据库查询飞起来,实现秒级响应?
【8月更文挑战第31天】在现代软件开发中,数据库性能至关重要。本文通过一个实战案例,展示了从慢查询到秒级响应的全过程。通过对查询的详细分析与优化,包括创建索引、改进查询语句及数据类型选择等措施,最终显著提升了性能。文章还提供了示例代码及最佳实践建议,帮助读者掌握SQL性能调优的核心技巧。
62 0
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
50 6
|
2月前
|
SQL NoSQL 关系型数据库
性能与扩展性的考量:SQL vs NoSQL
【8月更文第24天】在选择数据库系统时,开发者和架构师面临着一个关键决策:是选择传统的SQL(结构化查询语言)数据库还是现代的NoSQL(非关系型)数据库。这两种类型各有优劣,尤其是在性能和扩展性方面。本文将深入探讨SQL和NoSQL数据库在这两个方面的差异,并通过具体的代码示例来展示它们各自的优势。
53 0