主頁 > 知識庫 > SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)

SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)

熱門標(biāo)簽:我要地圖標(biāo)注數(shù)量有限制嗎 電銷需要外呼系統(tǒng)嗎 互聯(lián)網(wǎng)電話外呼系統(tǒng) 零成本地圖標(biāo)注賺錢 安卡拉地圖標(biāo)注app 家庭農(nóng)場地圖標(biāo)注名稱怎樣起名 電話機器人怎么代理商 400電話辦理泰安 千呼電話機器人可以試用嗎

前言

關(guān)于SQL Server基礎(chǔ)系列尚未結(jié)束,還剩下最后一點內(nèi)容未寫,后面會繼續(xù)。有園友詢問我什么時候開始寫SQL Server性能系列,估計還得等一段時間,最近工作也比較忙,但是會陸陸續(xù)續(xù)的更新SQL Server性能系列,本篇作為性能系列的基本引導(dǎo),讓大家嘗嘗鮮。在涉及到SQL Server性能優(yōu)化時,我看到的有些文章就是一上來列出SQL Server的性能優(yōu)化條例,根本沒有弄清楚為什么這么做,當(dāng)然也有可能是自己弄懂了,只是作為備忘錄,但是到了我這里,我會遵循不僅僅是備忘錄,還要讓各位園友都能易于理解,不至于面試時只知道其果,不知其因。

存儲過程性能優(yōu)化

禁用受影響函數(shù)通過設(shè)置SET NOCOUNT ON

如上當(dāng)我們進(jìn)行查詢時總是會返回受影響的行數(shù),這種消息只是對于我們調(diào)試SQL時有幫助,其他再無其他幫助,我們可以通過設(shè)置 SET NOCOUNT ON 來禁用這個特性,這將有顯著的性能提升,有利于減少網(wǎng)絡(luò)流量的傳輸。在存儲過程中我們像如下設(shè)置。

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

使用架構(gòu)名稱+對象名稱

這個建議在開篇我們就已經(jīng)明確講過,通過設(shè)置架構(gòu)名稱的對象名稱是最合格的,此時將直接執(zhí)行編譯計劃而不是在使用緩存計劃時還要去其他可能的架構(gòu)中去查找對象。所以我們建議總是像如下使用。

SELECT * FROM Sales.Customers -- 推薦
-- 而不是
SELECT * FROM Customers -- 避免
--調(diào)用存儲過程如下
EXEC dbo.MyProc -- 推薦
--而不是
EXEC MyProc -- 避免

存儲過程名稱禁止以sp開頭

如果一個存儲過程名稱以sp開頭,此時數(shù)據(jù)庫查詢引擎首先將在master數(shù)據(jù)庫中去查找存儲過程然后再是在當(dāng)前會話的數(shù)據(jù)庫中去查找存儲過程。

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

網(wǎng)上隨便一搜索就看到如下查詢一行是否存在的SQL語句。

declare @message varchar(200),
  @name varchar(200)
if exists(select * from students where 學(xué)號='1005')
begin
set @message='下列人員符合條件:'
print @message
set @name=(select 姓名 from students where 學(xué)號='1005')
print @name
end
else
  begin
    set @message='沒有人符合條件'
    print @message
  end
go

當(dāng)判斷一條記錄是否在表中存在時我們使用IF EXISIS,如果在IF EXISTS中內(nèi)部語句中有任何值返回則返回TRUE。如上述

if exists(select * from students where 學(xué)號='1005')

此時將返回學(xué)號 = '1005'的這一行,而如果用1代替則不用返回滿足條件的這一行記錄,在查詢時為了網(wǎng)絡(luò)傳輸我們應(yīng)該最小化處理數(shù)據(jù),所以我們應(yīng)該像如下做返回單值1.

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

使用sp_executesql而不是使用EXECUTE

sp_executesql支持使用參數(shù)而不是使用EXECUTE來提高代碼重用,動態(tài)語句的查詢執(zhí)行計劃只有對每個字符包括大小寫、空格、參數(shù)、注釋相同的語句才重用。如果利用EXECUTE執(zhí)行如下動態(tài)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)

執(zhí)行查詢計劃如下,如果再一次使用不同的@contactname值,此時查詢執(zhí)行計劃將再次創(chuàng)建@contactname不會達(dá)到重用的目的

如果我們使用利用sp_executesql像如下查詢,如果對于不同的@contactname值,此時查詢執(zhí)行計劃將被會重用,將會達(dá)到提高性能的目的。

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之后開始支持異常處理,如果我們進(jìn)行異常語句檢查處理,如果出現(xiàn)異常將不會導(dǎo)致利用更多的代碼來消耗更多的資源和時間。

盡可能使事務(wù)簡短

事務(wù)的長度會影響阻塞和死鎖。直到事務(wù)結(jié)束排他鎖不會釋放,在高隔離級別中共享鎖的生命周期更長, 因此,冗長的事務(wù)意味著鎖定的時間更長,鎖定的時間越長最終導(dǎo)致阻塞,在有些情況下,阻塞會轉(zhuǎn)變成死鎖,所以為了更快的執(zhí)行、更少的阻塞,我們應(yīng)該使事務(wù)的長度盡量簡短。

數(shù)據(jù)壓縮和頁壓縮提高IO

SQL Server主要的性能取決于磁盤IO效率,改善IO意味著提高性能,在SQL Server 2008中提供了數(shù)據(jù)和備份壓縮功能。下面我們一起來看看。

數(shù)據(jù)壓縮

數(shù)據(jù)壓縮意味著磁盤保留的空間減少,數(shù)據(jù)壓縮可以配置在表上的聚集索引、非聚集索引、索引視圖或者分區(qū)表或者分區(qū)索引。數(shù)據(jù)壓縮可以在兩個級別中實現(xiàn):一個是行壓縮,另外一個是頁壓縮,甚至頁壓縮會自動實現(xiàn)行壓縮,當(dāng)通過CREATE TABLE、CREATE INDEX語句時會壓縮表和索引,為了改變一個表、索引和分區(qū)的壓縮狀態(tài)通過 ALTER TABLE.. REBUILD WITH or ALTER INDEX.. REBUILD WITH語句實現(xiàn)。當(dāng)一個堆棧的壓縮狀態(tài)改變后,此時非聚集索引將重建,在行壓縮中,使用以下四種方法來消除未使用的空間。

1.減少記錄中的元數(shù)據(jù)開銷。

2.所有數(shù)字類型(INT、NUMERIC等)和基于數(shù)字類型(如DATETIME、MONEY)將會轉(zhuǎn)換成可變長度值,例如INT類型在壓縮后所有未被消耗的空間將會被回收。比如我們知道0-255可以存儲一個字節(jié)中,若我們的值是100,在磁盤中INT是4個字節(jié),但是在壓縮之后其余3個字節(jié)將會被回收。

3.CHAR和NCHAR會轉(zhuǎn)換成可變長度存儲,在壓縮之后對于實際存儲的數(shù)據(jù)將不會再有空格,比如我們定義CHAR(10),此時我們存儲的數(shù)據(jù)為Jeffcky,默認(rèn)情況下將會預(yù)留10個字節(jié),此時將會有3個字節(jié)為空格補充,但是在壓縮之后這3個字節(jié)將會被回收,僅僅只預(yù)留7個字節(jié)。

4.所有NULL和0都已經(jīng)過優(yōu)化不需要字節(jié)。

頁壓縮

頁壓縮將會通過以下三種方法實現(xiàn)。

1.上述已經(jīng)提到的所有。

2.前綴壓縮:在每頁上的每一列,被標(biāo)識的所有行的公共值以及存儲在標(biāo)題下的每一行,在壓縮之后公共值將替換為標(biāo)題行的引用。

3.字典壓縮:在字典壓縮中,每一頁中的每一列標(biāo)識公共值,是將存儲在標(biāo)題行的第二行中,然后這些公共值將替換為新行中的值的引用。

說了這么多,具體到底是怎樣使用的呢?請繼續(xù)往下看,我們通過使用臨時數(shù)據(jù)庫插入748條數(shù)據(jù),如下:

USE tempdb
GO
CREATE TABLE TestCompression (col1 INT, col2 CHAR(50))
GO
INSERT INTO TestCompression VALUES (10, '壓縮測試')
GO 748

接下來進(jìn)行行壓縮和頁壓縮來和原始未壓縮進(jìn)行比較看看。

-- 原始值
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = 設(shè)置行壓縮
ALTER TABLE TestCompression
REBUILD WITH (DATA_COMPRESSION = ROW);
GO
EXEC sp_spaceused TestCompression
GO
-- DATA_COMPRESSION = 設(shè)置頁壓縮
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

結(jié)果如下:

壓縮后數(shù)據(jù)顯然變少了,如果數(shù)據(jù)量足夠大頁壓縮比行壓縮的數(shù)據(jù)會更少,從而減少IO提高性能,不知道看到本文的你是否在生產(chǎn)服務(wù)上是否已經(jīng)應(yīng)用過呢,下次可以試試。

以上所述是小編給大家介紹的SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一),希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!

您可能感興趣的文章:
  • SQL Server中的Forwarded Record計數(shù)器影響IO性能的解決方法
  • SQLServer存儲過程中事務(wù)的使用方法
  • SQL SERVER調(diào)用存儲過程小結(jié)
  • SQL Server存儲過程中使用表值作為輸入?yún)?shù)示例

標(biāo)簽:新鄉(xiāng) 黃山 濱州 文山 來賓 池州 大同 東營

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)》,本文關(guān)鍵詞  SQL,Server,聚焦,存儲,過程,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)》相關(guān)的同類信息!
  • 本頁收集關(guān)于SQL Server 聚焦存儲過程性能優(yōu)化、數(shù)據(jù)壓縮和頁壓縮提高IO性能方法(一)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章