使用 SQL Server 時需要經常用到的幾個設定選項

SqlServer 查詢sql執行時間，

 

在查詢分析器中輸入以下內容：

set statistics profile on
set statistics io on
set statistics time on
go
　　<這裡寫上你的語句...>
go
set statistics profile off
set statistics io off
set statistics time off

 

用這個檢查一下項目中的SQL語句，發現一件和想象不一樣的事情，SQL語句：

select min(ID) from

(select top 25 ID from tblInfo where charCityFrom='石家莊'  order by id desc) as temp

 

掃描計數 1，邏輯讀取 9 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
CPU 時間 = 0 毫秒，佔用時間 = 2 毫秒。

 

 

 

1. set deadlock_priority

說明：控制在發生死結情況時會話的反應方式。如果兩個進程都鎖定資料，並且直到其他進程釋放自己的鎖時，每個進程才能釋放自己的鎖，即發生死結情況。

文法：set deadlock_priority { low | normal | @deadlock_var }

參數：low  指定當前會話為最佳選擇死結犧牲品。microsoft sql server 自動復原死結犧牲品的事務，並給用戶端應用程式返回 1205 號死結錯誤資訊。
　　　normal  指定會話返回到預設的死結處理方法。
　　　@deadlock_var 是指定死結處理方法的字元變數。如果指定 low，則 @deadlock_var 為 3；如果指定 normal，則 @deadlock_var 為 6。

注釋：set deadlock_priority 的設定是在執行或運行時設定，而不是在分析時設定。

許可權：set deadlock_priority 許可權預設授予所有使用者。

 

2. set lock_timeout

說明：指定語句等待鎖釋放的毫秒數。

文法：set lock_timeout timeout_period

參數：timeout_period 是在 microsoft sql server 返回鎖定錯誤前經過的毫秒數。值為 -1（預設值）時表示沒有逾時期限（即無限期等待）。
　　　   當鎖等待超過逾時值時，將返回錯誤。值為 0 時表示根本不等待，並且一遇見鎖就返回資訊。

注釋：在串連開始時，該設定的值為 -1。設定更改後，新設定在其餘的連線時間裡一直有效。
　　　set lock_timeout 的設定是在執行或運行時設定，而不是在分析時設定。
　　　readpast 鎖定提示為該 set 選項提供了另一種方式。

許可權：set lock_timeout 許可權預設授予所有使用者。

樣本：下例將鎖逾時期限設定為 1,800 毫秒。

　　　set lock_timeout 1800
　　　go

 

3. @@lock_timeout

說明：返回當前會話的當前鎖逾時設定，單位為毫秒。

文法：@@lock_timeout

傳回型別：integer

注釋：set lock_timeout 允許應用程式設定語句等待阻塞資源的最長時間。當一條語句已等待超過 lock_timeout 所設定的時間，則被鎖住的語句將自動取消，並給應用程式返回一條錯誤資訊。
　　　在一個串連的開始，@@lock_timeout 返回一個 –1值。

樣本：下面的樣本顯示當一個 lock_timeout 值未被設定時的結果集。

　　　select @@lock_timeout
　　　下面是結果集：
　　　----------------
　　　-1

　　　下面的樣本設定 lock_timeout 為 1800 毫秒，然後調用 @@lock_timeout。

　　　set lock_timeout 1800
　　　select @@lock_timeout

　　　下面是結果集：
　　　------------------------------
　　　1800

 

4. set identity_insert

說明：允許將顯式值插入表的識別欄位中。

文法：set identity_insert [ database.[ owner.] ] { table } { on | off }

參數：database 是指定的表所駐留的資料庫名稱。
　　　owner 是表所有者的名稱。
　　　table 是含有識別欄位的表名。

注釋：所有時候，會話中只有一個表的 identity_insert 屬效能設定為 on。如果某個表已將此屬性設定為 on，並且為另一個表發出了 set identity_insert on 語句，則 microsoft sql server 返回一個錯誤資訊，指出 set identity_insert 已設定為 on 並報告此屬性已設定為 on 的表。
　　　如果插入值大於表的當前標識值，則 sql server 自動將新插入值作為當前標識值使用。
　　　set identity_insert 的設定是在執行或運行時設定，而不是在分析時設定。

許可權：執行許可權預設授予 sysadmin 固定伺服器角色和 db_owner 及 db_ddladmin 固定資料庫角色及對象所有者。

樣本：下例建立一個含有識別欄位的表，並顯示怎麼使用 set identity_insert 設定填充由 delete 語句導致的標識值中的空隙。

　　　-- create products table.
　　　create table products (id int identity primary key, product varchar(40))
　　　go
　　　-- inserting values into products table.
　　　insert into products (product) values (screwdriver)
　　　insert into products (product) values (hammer)
　　　insert into products (product) values (saw)
　　　insert into products (product) values (shovel)
　　　go

　　　-- create a gap in the identity values.
　　　delete products
　　　where product = saw
　　　go

　　　select *
　　　from products
　　　go

　　　-- attempt to insert an explicit id value of 3;
　　　-- should return a warning.
　　　insert into products (id, product) values(3, garden shovel)
　　　go

　　　-- set identity_insert to on.
　　　set identity_insert products on
　　　go

　　　-- attempt to insert an explicit id value of 3
　　　insert into products (id, product) values(3, garden shovel)
　　　go

　　　select *
　　　from products
　　　go

　　　-- drop products table.
　　　drop table products
　　　go

 

5. set implicit_transactions

說明：為串連設定隱性事務模式。

文法：set implicit_transactions { on | off }

注釋：當設定為 on 時，set implicit_transactions 將串連設定為隱性事務模式。當設定為 off 時，則使串連返回到自動認可事務模式。
　　　當串連是隱性事務模式且當前不在事務中時，執行下列語句將啟動事務：

　　　alter table fetch revoke
　　　create  grant select
　　　delete insert truncate table
　　　drop open update

　　　如果串連已在開啟的事務中，則上述語句不啟動新事務。
　　　對於因為該設定為 on 而自動開啟的事務，使用者必須在該事務結束時將其顯式提交或復原。否則當使用者中斷連線時，事務及其所包含的所有資料更改將復原。在事務提交後，執行上述任一語句即可啟動新事務。
　　　隱性事務模式將保持有效，直到串連執行 set implicit_transactions off 語句使串連返回到自動認可模式。在自動認可模式下，如果各個語句成功完成則提交。
　　　在進行串連時，sql server odbc 驅動程式和用於 sql server 的 microsoft ole db 提供程式自動將 implicit_transactions 設定為 off。對來自 db-library 應用程式的串連，set implicit_transactions 預設為 off。
　　　當 set ansi_defaults 為 on 時，將啟用 set implicit_transactions。
　　　set implicit_transactions 的設定是在執行或運行時設定，而不是在分析時設定。

樣本：下例示範在將 implicit_transactions 設定為 on 時顯式或隱式啟動事務。他使用 @@trancount 函數示範開啟的事務和關閉的事務。

　　　use pubs
　　　go

　　　create table t1 (a int)
　　　go
　　　insert into t1 values (1)
　　　go

　　　print use explicit transaction
　　　begin tran
　　　insert into t1 values (2)
　　　select tran count in transaction= @@trancount
　　　commit tran
　　　select tran count outside transaction= @@trancount
　　　go

　　　print setting implicit_transactions on
　　　go
　　　set implicit_transactions on
　　　go

　　　print use implicit transactions
　　　go
　　　-- no begin tran needed here.
　　　insert into t1 values (4)
　　　select tran count in transaction= @@trancount
　　　commit tran
　　　select tran count outside transaction= @@trancount
　　　go

　　　print use explicit transactions with implicit_transactions on
　　　go
　　　begin tran
　　　insert into t1 values (5)
　　　select tran count in transaction= @@trancount
　　　commit tran
　　　select tran count outside transaction= @@trancount
　　　go

　　　select * from t1
　　　go

　　　-- need to commit this tran too!
　　　drop table t1
　　　commit tran
　　　go

 

6. set nocount
說明：使返回的結果中不包含有關受 transact-sql 語句影響的行數的資訊。

文法：set nocount { on | off }

注釋：當 set nocount 為 on 時，不返回計數（表示受 transact-sql 語句影響的行數）。當 set nocount 為 off 時，返回計數。
　　　即使當 set nocount 為 on 時，也更新 @@rowcount 函數。
　　　當 set nocount 為 on 時，將不給用戶端發送預存程序中的每個語句的 done_in_proc 資訊。當使用 microsoft sql server 提供的工具 + 生產力執行查詢時，在 transact-sql 語句（如 select、insert、update 和 delete）結束時將不會在查詢結果中顯示"nn rows affected"。
　　　如果預存程序中包含的一些語句並不返回許多實際的資料，則該設定由於大量減少了網路流量，因此可顯著提高效能。
　　　set nocount 設定是在執行或運行時設定，而不是在分析時設定。

許可權：set nocount 許可權預設授予所有使用者。

樣本：下例在 osql 工具 + 生產力或 sql server 查詢分析器中執行時，可防止顯示有關受影響的行數的資訊。

　　　use pubs
　　　go
　　　-- display the count message.
　　　select au_lname
　　　from authors
　　　go
　　　use pubs
　　　go
　　　-- set nocount to on and no longer display the count message.
　　　set nocount on
　　　go
　　　select au_lname
　　　from authors
　　　go
　　　-- reset set nocount to off.
　　　set nocount off
　　　go

 

7. @@rowcount
說明：返回受上一語句影響的行數。

文法：@@rowcount

傳回型別：integer

注釋：所有不返回行的語句將這一變數設定為 0 ，如 if 語句。

樣本：下面的樣本執行 update 語句並用 @@rowcount 來檢測是否有發生更改的行。

　　　update authors set au_lname = jones
　　　where au_id = 999-888-7777
　　　if @@rowcount = 0
　　　   print warning: no rows were updated

 

8. set rowcount

說明：使 microsoft sql server 在返回指定的行數之後停止處理查詢。

文法：set rowcount { number | @number_var }

參數：number | @number_var 是在停止給定查詢之前要處理的行數（整數）。

注釋：建議將當前使用 set rowcount 的 delete、insert 和 update 語句重新編寫為使用 top 文法。有關更多資訊，請參見 delete、insert 或 update。
　　　對於在遠端資料表和本地及遠端資料分割視圖上執行的 insert、update 和 delete 語句，忽略 set rowcount 選項設定。
　　　若要關閉該選項（以便返回所有的行），請將 set rowcount 指定為 0。
　　　說明  設定 set rowcount 選項將使大多數 transact-sql 語句在已受指定數目的行影響後停止處理。這包括觸發器和 insert、update 及 delete 等資料修改語句。rowcount 選項對動態資料指標無效，但限制鍵集的行集和非感應式資料指標。使用該選項時應謹慎，他主要和 select 語句一起使用。
　　　如果行數的值較小，則 set rowcount 替代 select 語句 top 關鍵字。
　　　set rowcount 的設定是在執行或運行時設定，而不是在分析時設定。

許可權：set rowcount 許可權預設授予所有使用者。

樣本：set rowcount 在指定的行數後停止處理。在下例中，注意有 x 行滿足預付款少於或等於 $5,000 的條件；不過，從更新所返回的行數中能看出並非所有的行都得到處理。rowcount 影響所有的 transact-sql 語句。

　　　use pubs
　　　go
　　　select count(*) as cnt
　　　from titles
　　　where advance >= 5000
　　　go

　　　下面是結果集：

　　　cnt      
　　　-----------
　　　11         

　　　(1 row(s) affected)

　　　目前，將 rowcount 設定為 4，並更新預付款等於或大於 $5,000 的所有行。

　　　-- set rowcount to 4.
　　　set rowcount 4
　　　go
　　　update titles
　　　set advance = 5000
　　　where advance >= 5000
　　　go

 

9. set transaction isolation level
說明：控制由串連發出的所有 microsoft sql server select 語句的預設事務鎖定行為。

文法：set transaction isolation level
　　　{ read committed
　　　    | read uncommitted
　　　    | repeatable read
　　　    | serializable
　　　}

參數：read committed 指定在讀取資料時控制共用鎖定以避免髒讀，但資料可在事務結束前更改，從而產生不可重複讀取或幻像資料。該選項是 sql server 的預設值。
　　　read uncommitted 執行髒讀或 0 級隔離鎖定，這表示不發出共用鎖定，也不接受獨佔鎖定。當設定該選項時，能對資料執行未提交讀或髒讀；在事務結束前能更改資料內的數值，行也能出目前資料集中或從資料集消失。該選項的作用和在事務內所有語句中的所有表上設定 nolock 相同。這是四個隔離等級中限制最小的層級。
　　　repeatable read 鎖定查詢中使用的所有資料以防止其他使用者更新資料，不過其他使用者能將新的幻像行插入資料集，且幻像行包括在當前事務的後續讀取中。因為並發低於預設隔離等級，所以應只在必要時才使用該選項。
　　　serializable 在資料集上放置一個範圍鎖，以防止其他使用者在事務完成之前更新資料集或將行插入資料集內。這是四個隔離等級中限制最大的層級。因為並發層級較低，所以應只在必要時才使用該選項。該選項的作用和在事務內所有 select 語句中的所有表上設定 holdlock 相同。

注釋：一次只能設定這些選項中的一個，而且設定的選項將一直對那個串連保持有效，直到顯式更改該選項為止。這是預設行為，除非在語句的 from 子句中在表級上指定最佳化選項。
　　　set transaction isolation level 的設定是在執行或運行時設定，而不是在分析時設定。

樣本：下例為會話設定 transaction isolation level。對於每個後續 transact-sql 語句，sql server 將所有共用鎖定一直控制到事務結束為止。

　　　set transaction isolation level repeatable read
　　　go
　　　begin transaction
　　　select * from publishers
　　　select * from authors
　　　...
　　　commit transaction

 

10. set xact_abort

說明：指定當 transact-sql 語句產生執行階段錯誤時，microsoft sql server 是否自動復原當前事務。

文法：set xact_abort { on | off }

注釋：當 set xact_abort 為 on 時，如果 transact-sql 語句產生執行階段錯誤，整個事務將終止並復原。為 off 時，只復原產生錯誤的 transact-sql 語句，而事務將繼續進行處理。編譯錯誤（如語法錯誤）不受 set xact_abort 的影響。
　　　對於大多數 ole db 提供程式（包括 sql server），隱性或明確交易中的資料修改語句必須將 xact_abort 設定為 on。唯一不必該選項的情況是提供程式支援嵌套事務時。有關更多資訊，請參見分散式查詢和分散式交易。
　　　set xact_abort 的設定是在執行或運行時設定，而不是在分析時設定。

樣本：下例導致在含有其他 transact-sql 語句的事務中發生違反外鍵錯誤。在第一個語句集中產生錯誤，但其他語句均成功執行且事務成功提交。在第二個語句集中，set xact_abort 設定為 on。這導致語句錯誤使批處理終止，並使交易回復。

　　　create table t1 (a int primary key)
　　　create table t2 (a int references t1(a))
　　　go
　　　insert into t1 values (1)
　　　insert into t1 values (3)
　　　insert into t1 values (4)
　　　insert into t1 values (6)
　　　go
　　　set xact_abort off
　　　go
　　　begin tran
　　　insert into t2 values (1)
　　　insert into t2 values (2) /* foreign key error */
　　　insert into t2 values (3)
　　　commit tran
　　　go

　　　set xact_abort on
　　　go

　　　begin tran
　　　insert into t2 values (4)
　　　insert into t2 values (5) /* foreign key error */
　　　insert into t2 values (6)
　　　commit tran
　　　go

　　　/* select shows only keys 1 and 3 added.
　　　   key 2 insert failed and was rolled back, but
　　　   xact_abort was off and rest of transaction
　　　   succeeded.
　　　   key 5 insert error with xact_abort on caused
　　　   all of the second transaction to roll back. */

　　　select *
　　　from t2
　　　go

　　　drop table t2
　　　drop table t1
　　　go