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

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

前言

关于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
目录
相关文章
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第18天】SQL Server 存储过程具有提高性能、增强安全性、代码复用和易于维护等优点。它可以减少编译时间和网络传输开销,通过权限控制和参数验证提升安全性,支持代码共享和复用,并且便于维护和版本管理。然而,存储过程也存在可移植性差、开发和调试复杂、版本管理问题、性能调优困难和依赖数据库服务器等缺点。使用时需根据具体需求权衡利弊。
|
1月前
|
SQL 缓存 监控
SQL性能提升指南:五大优化策略与十个实战案例
在数据库性能优化的世界里,SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载,提高应用响应速度,甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤,并结合十个实战案例,为你提供一份详尽的性能提升指南。
54 0
|
2月前
|
存储 SQL 缓存
SQL Server存储过程的优缺点
【10月更文挑战第22天】存储过程具有代码复用性高、性能优化、增强数据安全性、提高可维护性和减少网络流量等优点,但也存在调试困难、移植性差、增加数据库服务器负载和版本控制复杂等缺点。
132 1
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
668 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
存储 SQL 数据库
Sql Server 存储过程怎么找 存储过程内容
Sql Server 存储过程怎么找 存储过程内容
143 1
|
2月前
|
存储 SQL 数据库
SQL Server存储过程的优缺点
【10月更文挑战第17天】SQL Server 存储过程是预编译的 SQL 语句集,存于数据库中,可重复调用。它能提高性能、增强安全性和可维护性,但也有可移植性差、开发调试复杂及可能影响数据库性能等缺点。使用时需权衡利弊。
|
2月前
|
存储 缓存 算法
如何优化阻塞IO的性能?
【10月更文挑战第6天】如何优化阻塞IO的性能?
58 5
|
2月前
|
SQL 监控 Oracle
Oracle SQL性能优化全面指南
在数据库管理领域,Oracle SQL性能优化是确保数据库高效运行和数据查询速度的关键
|
2月前
|
SQL 数据挖掘 数据库
SQL查询每秒的数据:技巧、方法与性能优化
id="">SQL查询功能详解 SQL(Structured Query Language,结构化查询语言)是一种专门用于与数据库进行沟通和操作的语言
|
2月前
|
存储 SQL 数据库
SQL Server 临时存储过程及示例
SQL Server 临时存储过程及示例
63 3