-- 查詢相關的版本資料
SELECT RIGHT(LEFT(@@VERSION,25),4) N'產品版本編號' ,
SERVERPROPERTY('ProductVersion') N'版本編號',
SERVERPROPERTY('ProductLevel') N'版本層級',
SERVERPROPERTY('Edition') N'執行個體產品版本',
DATABASEPROPERTYEX('master','Version') N'資料庫的內部版本號碼'
--
SELECT @@VERSION N'相關的版本編號、處理器架構、建置日期和作業系統'
GO
|
使用Stored Procedure優點
|
l 提升效能與強化安全性
l 減少網路流量
n 不用在網路上傳輸數百行、千行的inline
SQL code
l 提升安全性
n 賦予EXEC權限，無須存取資料表
n 減少SQL injection攻擊
l 效能佳
n 避免當下耗費CPU資源去執行編譯
n 避免CPU重複編譯的資源耗損
n DBA可效能優化每一隻SP
|
|
不使用Stored Procedure缺點
|
l 黑洞：效能、安全
l 浪費網路資源
n 網路上傳輸數百行、千行的inline
SQL code
l 安全漏洞
n 權限漏洞
n SQL injection
l 效能差
n 當下耗費CPU資源執行編譯SQL code
n 浪費CPU資源重複編譯SQL code
n 效能未被優化
|
-- 01_查詢目前資料庫內，每一個資料表的使用空間資訊 USE TSIO GO SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)', a2.create_date N'建立日期', a2.modify_date N'修改日期' FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY a1.data DESC GO -- 02_新增 資料行 rowversion，觀察耗費的時間 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS TIME ON GO ALTER TABLE TS_PK_2GB ADD vercol rowversion; GO -- 03_觀察 資料表 SELECT TOP 100 * FROM TS_PK_2GB GO
-- 01_建立範例資料表：rowversion_Index1 與 rowversion_NoIndex1
USE DBRV01
GO
CREATE TABLE rowversion_Index1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO
CREATE TABLE rowversion_NoIndex1
(sid int IDENTITY PRIMARY KEY, pid varchar(50), rowrev rowversion)
GO
-- 02_在範例資料表：rowversion_Index1 的 rowversion資料行上，建立索引
CREATE NONCLUSTERED INDEX [NCL01_rowversion] ON [dbo].[rowversion_Index1]
(
[rowrev] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
--03 檢視資料表的索引類型
SELECT t.name N'資料表', i.name N'索引名稱',
i.type_desc N'索引類型的描述',
i.is_unique N'索引是否唯一'
FROM sys.tables t INNER JOIN sys.indexes i
ON t.object_id = i.object_id
GO
-- 04_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
inx.name AS N'索引名稱',
index_type_desc N'索引類型',
avg_fragmentation_in_percent '片段(%)',
avg_page_space_used_in_percent N'頁面飽和度(%)',
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP' AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO
-- 05_Insert/Update/Delete 大量資料列，四萬筆
SET NOCOUNT ON
DECLARE @cnt INT =1
WHILE @cnt < 10001
BEGIN
INSERT rowversion_Index1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_Index1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_Index1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_Index1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_NoIndex1(pid) VALUES('A_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_NoIndex1(pid) VALUES('B_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_NoIndex1(pid) VALUES('C_'+CAST(SYSDATETIME() AS varchar(50)))
INSERT rowversion_NoIndex1(pid) VALUES('D_'+CAST(SYSDATETIME() AS varchar(50)))
SET @cnt +=1
END
GO
-- DELETE：1萬筆
DELETE FROM rowversion_Index1
WHERE pid LIKE 'B_%'
GO
DELETE FROM rowversion_NoIndex1
WHERE pid LIKE 'B_%'
GO
-- UPDATE：1萬筆
UPDATE rowversion_Index1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO
UPDATE rowversion_NoIndex1
SET pid = ('E_'+CAST(SYSDATETIME() AS varchar(50)))
WHERE pid LIKE 'C_%'
GO
-- 05_快速_檢視指定資料庫內的每一個索引之片段(fragmentation)資訊，使用 SAMPLED 模式
SELECT sch.name N'結構描述', obj.name N'資料表',
inx.name AS N'索引名稱',
index_type_desc N'索引類型',
avg_fragmentation_in_percent '片段(%)',
avg_page_space_used_in_percent N'頁面飽和度(%)',
fragment_count,
avg_fragment_size_in_pages,
page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'SAMPLED') AS phy
INNER JOIN sys.indexes AS inx ON phy.object_id = inx.object_id AND phy.index_id = inx.index_id
INNER JOIN sys.objects AS obj ON phy.object_id = obj.object_id
INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id
WHERE index_type_desc <> 'HEAP' AND fragment_count IS NOT NULL AND avg_fragment_size_in_pages IS NOT NULL
ORDER BY 2
GO
-- 06_觀察：rowversion資料行，有建立索引的使用特性 -- Index Seek
SELECT * FROM rowversion_Index1
WHERE rowrev = 0x000000000003AD11
GO
-- 07_觀察：rowversion資料行，沒有建立索引的使用特性 - Clustered index Scan
SELECT * FROM rowversion_NoIndex1
WHERE rowrev = 0x0000000000024C20
GO
-- 01_建立新資料庫
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DBRV01')
DROP DATABASE [DBRV01]
GO
CREATE DATABASE DBRV01
GO
-- 02_查詢-目前資料庫的目前 rowversion 資料類型值
USE DBRV01
GO
SELECT @@DBTS N'傳回目前資料庫的目前 rowversion 資料類型值'
GO
-- P01_查詢-目前資料庫的目前 rowversion 資料類型值
-- 03_建立範例資料表，使用資料類型：rowversion
CREATE TABLE rowversion01
(sid int IDENTITY, pid varchar(10), rowrev rowversion)
GO
-- 04_新增資料列，請明確要新增的資料行，
INSERT rowversion01(pid) VALUES('A')
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('C')
INSERT rowversion01(pid) VALUES('D')
GO
-- 05_查詢資料
SELECT * FROM rowversion01
GO
-- P02_資料行rowversion，自動產生的唯一的二進位數字
-- 06_加入對資料類型 rowversion 的排序
SELECT * FROM rowversion01
ORDER BY rowrev DESC -- 可以排序
GO
/*
觀察
資料行 pid 值為 C，，其 rowrev 值是 0x00000000000007D3
*/
-- P03_加入對資料類型 rowversion 的排序
-- 07_修改pid資料行，由 C 改為 S。
UPDATE rowversion01
SET pid = 'S'
WHERE pid = 'C'
GO
-- P04_資料行 pid 值為 C，，其 rowrev 值是 0x00000000000007D3
-- 08_查詢資料表
SELECT * FROM dbo.rowversion01
ORDER BY rowrev DESC
GO
/*
觀察
資料行 pid 值為 C，，資料類型 rowversion 的值，已經自動變更為 0x00000000000007D5
*/
-- P05_rowversion值，自動更新
-- 09_加入篩選條件式：WHERE 查詢方式，使用 大於(>)
SELECT * FROM dbo.rowversion01
WHERE rowrev > 0x00000000000007D2
GO
-- P06_WHERE 查詢方式，使用 大於
-- 10_加入篩選條件式：WHERE 查詢方式，使用 等於(=)
SELECT * FROM dbo.rowversion01
WHERE rowrev = 0x00000000000007D2
GO
-- P07_WHERE 查詢方式，使用 等於
-- 11_新增資料列，pid值有重複，測試若 UPDATE更新多筆資料列時，資料類型 rowversion 值的特性
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('B')
INSERT rowversion01(pid) VALUES('B')
GO
-- 12_查詢資料表
SELECT * FROM dbo.rowversion01
GO
/*
觀察
資料行 pid 值為 C，，資料類型 rowversion 是：
0x00000000000007D2、0x00000000000007D6、0x00000000000007D7 與 0x00000000000007D8
*/
-- P08_pid值有重複，觀察資料類型 rowversion 值的特性
-- 13__修改pid資料行，由 B 改為 T，預估有四筆資料列會受到影響
UPDATE rowversion01
SET pid = 'T'
WHERE pid = 'B'
GO
-- 14_查詢資料表
SELECT * FROM dbo.rowversion01
GO
/*
觀察
資料行 pid 值為 C，，資料類型 rowversion 的值，已經自動變更為：
0x00000000000007D9、0x00000000000007DA、0x00000000000007DB 與 0x00000000000007DC
-- 原本
資料行 pid 值為 C，，資料類型 rowversion 的值：
0x00000000000007D2、0x00000000000007D6、0x00000000000007D7 與 0x00000000000007D8
*/
-- P09_更動多筆資料列，資料類型 rowversion 的值，已經自動變更
-- 15_不得直接對 rowversion 資料行做任何異動，例如：不得 INSERT、UPDATE。
INSERT dbo.rowversion01(pid, rowrev) VALUES('E', 0x00000000000007DE)
GO
/*
錯誤
訊息 273，層級 16，狀態 1，行 118
無法將明確值插入時間戳記資料行。請使用 INSERT 配合資料行清單排除時間戳記資料行，或者將 DEFAULT 插入時間戳記資料行。
*/
-- P10_不得直接對 rowversion 資料行新增資料
-- 16_不得直接對 rowversion 資料行做任何異動，例如：不得 INSERT、UPDATE。
UPDATE rowversion01
SET rowrev = 0x00000000000007E2
WHERE pid = 'D'
GO
/*
錯誤
訊息 272，層級 16，狀態 1，行 129
無法更新時間戳記資料行。
*/
-- P11_不得直接對 rowversion 資料行執行UPDATE
資料表使用空間
|
SQL Server配置的記憶體
|
執行次數
|
耗時(秒)
|
476.64(MB)
|
1024(MB)
|
第1次執行
|
9.155
|
第2次執行
|
0.017
|
||
第3次執行
|
0.019
|
資料表使用空間
|
SQL Server配置的記憶體
|
執行次數
|
耗時(秒)
|
2031.57(MB)
|
1024(MB)
|
第1次執行
|
38.887
|
第2次執行
|
39.123
|
||
第3次執行
|
39.109
|
-- 01_建立資料庫 SET NOCOUNT ON USE master GO CREATE DATABASE [TSIO] ON PRIMARY ( NAME = N'TSIO', FILENAME = N'C:\TSQLDB\TSIO.mdf' , SIZE = 8192MB , FILEGROWTH = 102400KB ) LOG ON ( NAME = N'TSIO_log', FILENAME = N'C:\TSQLDB\TSIO_log.ldf' , SIZE = 102400KB , FILEGROWTH = 102400KB ) GO -- 02_建立兩個範例資料表，一個有索引，一個沒有 USE TSIO GO CREATE TABLE TS_NOPK_2GB (sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000)) GO CREATE TABLE TS_PK_2GB (sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000)) GO CREATE TABLE TS_NOPK_500MB (sid INT IDENTITY, wdatetime datetime2 ,cdata char(8000)) GO CREATE TABLE TS_PK_500MB (sid INT IDENTITY PRIMARY KEY, wdatetime datetime2 ,cdata char(8000)) GO -- 03_新增資料列 DECLARE @cnt INT=1 WHILE @cnt < 61000 BEGIN INSERT TS_PK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME()) INSERT TS_NOPK_500MB VALUES (SYSDATETIME(),CONVERT(varchar(100),GETDATE(), 113)+' '+ APP_NAME()) SET @cnt +=1 END GO -- 04_查詢目前資料庫內，每一個資料表的使用空間資訊 SELECT a3.name AS N'結構描述', a2.name AS N'資料表', a1.rows AS N'資料列筆數', (a1.reserved + ISNULL(a4.reserved,0))* 8.0/1024 AS N'配置的空間總量(MB)', a1.data * 8.0/1024 AS '資料(MB)', (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8.0/1024 AS N'索引(MB)', (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8.0/1024 AS N'尚未使用(MB)', a2.create_date N'建立日期', a2.modify_date N'修改日期' FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY a1.data DESC GO
-- 查詢：伺服器所組態的最大可用記憶體是：1024 MB SELECT cfg.name AS N'組態選項', cfg.minimum AS N'最小值', cfg.maximum AS N'最大值', cfg.value AS N'設定的組態選項值', cfg.value_in_use AS N'執行中的值', cfg.description AS N'描述' FROM sys.configurations AS cfg WHERE name ='max server memory (MB)' -- 原本：2147483647 USE master GO EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'max server memory (MB)', N'1024' GO RECONFIGURE WITH OVERRIDE GO EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE GO /*=========================================================*/ -- 05_查詢_沒有使用到索引，回傳一筆 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS TIME ON GO -- 第 1 次：查詢此資料表的使用空間是：500MB /* 目前組態的最大可用記憶體是：1GB， */ SELECT * FROM TS_NOPK_500MB WHERE sid=1 -- 第 2 次：查詢此資料表的使用空間是：500MB SELECT * FROM TS_NOPK_500MB WHERE sid=1 -- 第 3 次：查詢此資料表的使用空間是：500MB SELECT * FROM TS_NOPK_500MB WHERE sid=17 -- 05_查詢_沒有使用到索引，回傳一筆 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE SET STATISTICS TIME ON GO /*=========================================================*/ -- 第 1 次：查詢此資料表的使用空間是：2GB /* 目前組態的最大可用記憶體是：1GB， */ SELECT * FROM TS_NOPK_2GB WHERE sid=1 -- 第 2 次：查詢此資料表的使用空間是：2GB SELECT * FROM TS_NOPK_2GB WHERE sid=1 -- 第 3 次：查詢此資料表的使用空間是：2GB SELECT * FROM TS_NOPK_2GB WHERE sid=1 /*=========================================================*/ -- 觀察：各個資料庫所使用的緩衝集區 - SQL Server 緩衝集區(buffer pool)中的使用資訊 SELECT db.name, (COUNT(*)*8.0)/1024 AS N'耗用緩衝集區(MB)' FROM sys.dm_os_buffer_descriptors ob INNER JOIN sys.sysdatabases db ON ob.database_id =db.dbid GROUP BY db.name ORDER BY 2 DESC GO