SQL Server預存程序及進階應用程式

 

今天主要探討下SQL Server預存程序的應用及最佳化方案。

     預存程序：由於本人之前的一篇文章對預存程序簡單的做了概述，http://www.cnblogs.com/weilengdeyu/archive/2012/12/26/2834625.html這裡就不再贅述了。今天來看下預存程序的進階應用程式。

首先來看下預存程序能夠包含哪些內容：

     可以看出，預存程序不僅包含單個select語句，還可以包含select語句塊，例如Case When，同樣還可以包含邏輯控制語句，如if—else等。

注意：預存程序中也可以包含insert，update和delete語句。

然後我們來看下擴充預存程序xp_cmdshell的用法

案例：啟用xp_cmdshell

    SQL  Server2000中啟用擴充預存程序：

　　Use master

　　Exec sp_addextendedproc xp_cmdshell,'xplog70.dll'

　　Go

   SQL  Server 2005或SQL Server 2008啟用xp_cmdshell

　　sp_configure 'xp_cmdshell',1

　　go

　　reconfigure --讓sp_configure立即生效

　　go

　　--開啟進階選項

　　sp_configure 'show advanced options',1

　　go

　　reconfigure

　　go

     說明：伺服器選項總共有36個，預設情況下，sp_configure預存程序只顯示其中的10個，顯示結果中不包含進階選項，而且所有新的SQL Server配置選項都不會出現在這個精簡的清單中。然而，我們可以使用show advanced options命令參數讓SQL Server顯示出所有選項。

　　案例：禁用/刪除xp_cmdshell

　　　　Sql server 2000刪除擴充預存程序

　　　　--刪除擴充預存程序

　　　　use master

　　　　exec sp_dropextendedproc 'xp_cmdshell'

　　　　go

　　提高預存程序效能的方法

　　　　01,使用 set nocount on

　　　　文法 
　　　　SET NOCOUNT { ON | OFF } 

　　　　注釋 
　　　　當 SET NOCOUNT 為 ON 時，不返回計數（表示受 Transact-SQL 語句影響的行數）。當 SET NOCOUNT 為 OFF 時，返回計數。 

　　　　當 SET NOCOUNT 為 ON 時，將不給用戶端發送預存程序中的每個語句的 DONE_IN_PROC 資訊。當使用 Microsoft SQL Server 提供的工具 + 生產力執行查詢時，在        Transact-SQL 語句（如 SELECT、INSERT、 UPDATE 和 DELETE）結束時將不會在查詢結果中顯示"nn rows affected"。 

　　02,減少選擇性參數

　　03,最佳化SQL語句

　　　　001,避免頻繁訪問同一張或多張表

　　　　002,盡量避免大量事務操作

　　　　003,盡量避免使用遊標

　　　　004,注意where語句寫法

  　　　　 必須考慮語句順序，應該根據索引順序、範圍大小來確定條件子句的前後順序，儘可能的讓欄位順序與索引順序相一致，範圍從大到小。

　　　　005,盡量使用exists代替select count()判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。

　　　　006,注意表之間串連的資料類型

　　　　007,先寫DDL,再寫DML

　　　　008,合理使用索引

　　　　　　要注意索引的維護，周期性重建索引，重新編譯預存程序。

　　　　009,合理使用tempdb系統資料表

　　　　　　I,盡量避免使用distinct、order by、group
by、having、join語句

　　　　　　II,避免頻繁建立和刪除暫存資料表

　　　　　　III,暫存資料表中插入資料過大，可使用select into代替create table

　　　　　　IV,使用了暫存資料表，要在預存程序最後顯式刪除

　　　　　　V,避免使用大暫存資料表與其他大資料量表的連結查詢和修改

　　Sqlprofiler的使用

　　　　通過Sqlprofiler可以檢測到任何SQL  server語句的執行,協助我們分析SQL語句的最終執行過程。

　　　　使用方式：開啟工具→SQL Server Profiler如

         

點擊串連，出現介面

    

點擊運行

 

該介面可以查看SQL Server的跟蹤資訊。

 

今天暫時探討到這裡，希望能給大家帶來協助！