--若有大批新增或修改資料，建議執行更新統計資料和更新資料列計數,以避免查詢資料會有效能緩慢的問題 --參考資料網站http://msdn.microsoft.com/en-us/library/ms174384.aspx --更新單一桶資料庫統計資料 USE 資料庫名稱; GO EXEC sp_updatestats; GO --參考資料網站http://msdn.microsoft.com/en-us/library/ms187348.aspx --更新單一資料表統計資料 USE 資料庫名稱; GO UPDATE STATISTICS 資料表名稱; GO --參考資料網站http://msdn.microsoft.com/en-us/library/ms188414.aspx --參考資料網站http://msdn.microsoft.com/zh-tw/library/ms188414.aspx --更新單一資料庫中所有物件的資料列計數(Updating page or row counts or both for all objects in the current database) USE 資料庫名稱; GO DBCC UPDATEUSAGE (0); GO --更新某一資料表的資料列計數(Updating page or row count information for a table) USE 資料庫名稱; GO DBCC UPDATEUSAGE ('資料庫名稱','資料表名稱'); GO --更新某一資料表的索引頁面或資料列計數，可透過EXEC sp_help '資料表名稱';取得索引名稱 USE 資料庫名稱; GO DBCC UPDATEUSAGE ('資料庫名稱','資料表名稱','索引名稱'); GO --顯示或變更目前伺服器執行個體的全域組態設定 sp_configure 'show advanced options',1;--設定顯示進階選項，預設為 0 GO RECONFIGURE WITH OVERRIDE;--使用RECONFIGURE使系統使用新的設定值，當使用 RECONFIGURE WITH OVERRIDE 時，請特別小心。 GO --RECONFIGURE 和 RECONFIGURE WITH OVERRIDE 都會使用每個組態選項。 --不過，基本 RECONFIGURE 陳述式會拒絕在合理範圍之外或可能造成選項衝突的任何選項值。 --例如，如果 recovery interval 值超出 60 分鐘，或 affinity mask 值與 affinity I/O mask 值重疊，RECONFIGURE 就會產生錯誤。 --相對地，RECONFIGURE WITH OVERRIDE 會接受任何資料類型正確的選項值，且會強迫利用指定的值來重設組態。 sp_configure 'max degree of parallelism',8;--設定執行單一陳述式所要採用8個處理器。 GO RECONFIGURE WITH OVERRIDE;--使用RECONFIGURE使系統使用新的設定值，當使用 RECONFIGURE WITH OVERRIDE 時，請特別小心。 GO --若要讓伺服器判斷平行處理原則的最大程度，請將此選項設定為 0 (預設值)。 --將平行處理原則的最大程度設定為 0 就會允許 SQL Server 使用所有可用的處理器 (最多 64 個處理器)。 --若要抑制平行計畫的產生，請將 max degree of parallelism 設成 1。 --將這個值設成大於 1 的數字 (最大值 64)，則會限制單一查詢執行所使用的最大處理器個數。 --如果指定的數值大於可用的處理器數目，就會使用可用處理器的實際數目。 --如果電腦只有一個處理器，則會忽略 max degree of parallelism 值。 --您可以在查詢陳述式中指定 MAXDOP 查詢提示，藉以覆寫查詢中的 max degree of parallelism 值。 --檢查資料結構(check the database structure) USE 資料庫名稱; GO DBCC CHECKFILEGROUP; GO DBCC CHECKALLOC; GO DBCC CHECKTABLE ('資料表名稱'); --Checks the integrity of all the pages and structures that make up the table or indexed view GO DBCC CHECKDB('資料庫名稱')--To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB. GO --顯示統計資訊內容 DBCC SHOW_STATISTICS('資料表名稱','統計資訊名稱'); GO -- 或 DBCC SHOW_STATISTICS('資料表名稱',統計資訊名稱); GO --備註:如何取得統計資訊名稱 --統計資訊名稱包含索引鍵和主鍵名稱，還有系統預設資料表統計資訊 --假如一個資料表myTable有索引鍵IX_indexkey001、資料主鍵PK_primary001 --那麼要顯示IX_indexkey001的統計資訊可執行DBCC SHOW_STATISTICS('myTable','IX_indexkey001'); --刪除統計資訊 DROP STATISTICS 資料表名稱.統計資訊名稱; GO --顯示資料庫資訊 EXEC SP_HELPDB; --列出資料庫Instance中所有資料庫的資訊 GO EXEC SP_HELPDB 資料庫名稱; --指令資料庫名稱可以獲得單一資料庫詳細資料 GO EXEC SP_DATABASES; --顯示資料庫Instance中所有資料庫的使用磁碟空間大小 GO --使用SP_SPACEUSED顯示資料列的數目、所保留的磁碟空間和資料表所用的磁碟空間、索引檢視， --或目前資料庫中的 Service Broker 佇列，或顯示整個資料庫所保留和使用的磁碟空間。 --當您卸除或重建大型索引時，或卸除或截斷大型資料表時， --Database Engine 會延遲取消配置實際的頁面及其相關聯鎖定，直到認可交易之後。 --延遲的卸除作業並不會立即釋出已配置的空間。 --因此，在卸除或截斷大型物件之後，sp_spaceused 立即傳回的值不一定能反映實際可用的磁碟空間。 --參考資料網站http://msdn.microsoft.com/zh-tw/library/ms188776.aspx USE 資料庫名稱; GO EXEC SP_SPACEUSED;--顯示目前資料庫所用的磁碟空間 GO EXEC SP_SPACEUSED '資料表';--顯示目前資料表所用的磁碟空間 GO EXEC SP_SPACEUSED @updateusage = N'TRUE';--指出應該執行 DBCC UPDATEUSAGE 來更新空間使用方式資訊 GO --顯示資料庫相關資訊 SELECT * FROM master.dbo.sysdatabases;--列出所有資料庫 GO SELECT * FROM master.dbo.sysxlogins;--列出所有登入帳戶，只適用於SQLServer 2000 GO SELECT * FROM master.dbo.sysprocesses;--列出目前Instance中的連線資訊 GO SELECT * FROM master.dbo.sysservers;--列出目前每個連結或已註冊的遠端伺服器，各包含一個資料列，以及含有一個資料列代表 server_id = 0 的本機伺服器。 GO SELECT * FROM master.dbo.sysconfigures; --列出SQL Server Instance設定，同執行sp_configure相同 GO EXEC sp_configure --同執行 SELECT * FROM sysconfigures GO --查詢使用者自定資料庫 SELECT name AS 'DATABASENAME' FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb','distrbution') ORDER BY 1; GO --顯示單一資料庫的資訊，在每一桶資料庫中皆有下列系統資料表 SELECT * FROM sysfiles;--顯示該桶資料庫資料檔與交易紀錄檔案資訊,詳細資訊可使用EXEC SP_HELPDB 資料庫名稱; GO SELECT * FROM sysfilegroups;--顯示該桶資料庫的檔案群組 GO SELECT * FROM sysobjects;--顯示該桶資料庫裏面所有物件，如資料表、檢視表、預儲程序、使用者自定函式 GO SELECT * FROM syscolumns;--顯示該桶資料庫所有資料表的欄位 GO SELECT * FROM sysindexes;--顯示該桶資料庫所有Index GO SELECT * FROM sysusers;--顯示該桶資料庫的使用者 GO --當下連線資料庫的使用者權限 SELECT * FROM syspermissions;--顯示該桶資料庫的使用者權限 GO --顯示資料庫物件資訊 --參考資料網站http://msdn.microsoft.com/en-us/library/ms187335.aspx EXEC sp_help;--列出所有資料庫物件 GO --列出資料表的資訊 USE 資料庫名稱; GO EXEC sp_help '資料表名稱'; GO ----[以下指令SQL SERVER 2005 以上版本才可以使用]---- SELECT * FROM sys.databases;--列出Instance中所有使用者自定的資料庫metadata,SQLServer 2005版本以上適用 GO SELECT * FROM sys.sysdatabases;--列出Instance中所有系統資料庫metadata,SQLServer 2005版本以上適用 GO --估計執行壓縮後,資料表的大小 EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ;--估計 Production.WorkOrderRouting 資料表的大小 (如果使用 ROW 壓縮來將它壓縮)。 GO EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'PAGE' ;--估計 Production.WorkOrderRouting 資料表的大小 (如果使用 PAGE 壓縮來將它壓縮)。 GO --壓縮資料表 ALTER TABLE Production.WorkOrderRouting REBUILD WITH (DATA_COMPRESSION=ROW);--使用ROW壓縮Production.WorkOrderRouting資料表(Enable row compression on the Production.WorkOrderRouting table) GO ALTER TABLE Production.WorkOrderRouting REBUILD WITH (DATA_COMPRESSION=PAGE);--使用PAGE壓縮Production.WorkOrderRouting資料表(Enable page compression on the Production.WorkOrderRouting table) GO --The following example returns information for all tables and indexes within the instance of SQL Server. Executing this query requires VIEW SERVER STATE permission. SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(N'QuantamCorp'), OBJECT_ID(N'QuantamCorp.Production.WorkOrderRouting'), NULL, NULL);--回傳QuantamCorp.Production.WorkOrderRouting資料表的資料表與索引資訊 GO SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL);--回傳所有資料表與索引資訊(Returning information for all tables and indexes) GO --參考資料網站http://technet.microsoft.com/zh-tw/library/ms176013.aspx --針對 SQL Server 上經過驗證的各個工作階段傳回一個資料列。 --sys.dm_exec_sessionssys.dm_exec_sessions 是伺服器範圍檢視表， --會顯示所有作用中使用者連接和內部工作的相關資訊。 --這個資訊包含用戶版本、用戶程式名稱、用戶登錄時間、登錄使用者、目前工作階段設定、還有更多。 --使用 sys.dm_exec_sessions 來首先檢視目前系統載入及定義感興趣的工作階段， --然後以使用其他動態管理檢視或動態管理函式來學習更多關於工作階段的資訊。 --sys.dm_exec_connections、sys.dm_exec_sessions 和 sys.dm_exec_requests 動態管理檢視對應到 sys.sysprocesses 系統資料表。 SELECT * FROM sys.dm_exec_sessions;--回傳各個工作階段 GO --參考資料網站http://technet.microsoft.com/zh-tw/library/ms181509 --傳回有關與這個 SQL Server 執行個體建立之連接及每一個連接之詳細資料的資訊。 SELECT * FROM sys.dm_exec_connections;--回傳執行個體建立之連接及每一個連接之詳細資料的資訊 GO --參考資料網站http://technet.microsoft.com/zh-tw/library/ms177648 --傳回在 SQL Server 內部執行之每個要求的相關資訊。 SELECT * FROM sys.dm_exec_requests;--回傳內部執行之每個要求的相關資訊 GO --參考資料網站http://technet.microsoft.com/zh-tw/library/ms181929 --傳回 SQL 陳述式的文字以及前五項查詢的平均 CPU 時間。 SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC; GO --傳回以批次方式執行的 SQL 查詢之文字並提供有關這些查詢的統計資訊。 SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count,plan_generation_num,last_execution_time, total_worker_time,last_worker_time,min_worker_time, max_worker_time,total_physical_reads,last_physical_reads, min_physical_reads,max_physical_reads,total_logical_writes, last_logical_writes,min_logical_writes,max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; GO --參考資料網站http://technet.microsoft.com/zh-tw/library/ms189741 --傳回平均 CPU 時間之前五項查詢的相關資訊。 此範例會根據查詢雜湊彙總查詢，讓邏輯上相同的查詢能夠依據其累計資源耗用量進行分組。 USE AdventureWorks2012; GO SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC; GO --傳回查詢的資料列計數彙總資訊 (資料列總數、最小資料列數目、最大資料列數目及上次傳回的資料列數目)。 SELECT qs.execution_count, SUBSTRING(qt.text,qs.statement_start_offset/2 +1, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2 ELSE qs.statement_end_offset end - qs.statement_start_offset )/2 ) AS query_text, qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid, qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.text like '%SELECT%' ORDER BY qs.execution_count DESC; GO --參考資料網站http://technet.microsoft.com/zh-tw/library/cc280701 --傳回平均經過時間所識別之前 10 項預存程序的相關資訊。 SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name', d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], d.last_elapsed_time, d.execution_count FROM sys.dm_exec_procedure_stats AS d ORDER BY [total_worker_time] DESC; GO --設定資料庫自動更新統計資訊、建立統計資訊、更新統計資訊為非同步更新(Enable auto update statistics, auto create statistics, and auto update statistics async) ALTER DATABASE 資料庫名稱 SET AUTO_UPDATE_STATISTICS ON, AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS_ASYNC ON; GO --縮減資料庫檔案大小(shrink a file and a database) --利用將資料庫設為簡易模式來壓縮資料記錄檔(Truncate the log by changing the database recovery model to SIMPLE.) ALTER DATABASE 資料庫名稱 SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (QuantamCorp_Log,1);--指定記錄檔壓縮截斷到1MB的檔案大小(Shrink the truncated log file to 1 MB.) GO ALTER DATABASE QuantamCorp SET RECOVERY FULL;--將資料庫設為完整模式 (Reset the database recovery model.) GO USE QuantamCorp; GO SELECT file_id, name FROM sys.database_files;--取出資料庫檔案代碼與名稱 GO DBCC SHRINKFILE (1,TRUNCATEONLY); GO DBCC SHRINKDATABASE (QuantamCorp,TRUNCATEONLY); GO --列出資料表 USE 資料庫名稱; SELECT * FROM INFORMATION_SCHEMA.TABLES; GO SELECT * FROM sys.syslogins;--列出所有登入帳戶，只適用於SQLServer 2005以上版本 GO