SQL Server 最佳記憶體設定

1、鎖定記憶體分頁 (Lock Pages in Memory)
A.執行 gpedit.msc
[本機電腦原則]/[電腦設定]/[Windows 設定]/[安全性設定]/[本機原則]/[使用者權限指派]
[鎖定記憶體中的分頁] 新增 [服務]中 SQLServer的啟動帳號
B.執行 gpupdate /force 即時更新原則設定

2、SQL Server可用記憶體限制設定
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3200'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

實體記憶體	Max Server Memory
2GB	1500
4GB	3200
6GB	4800
8GB	6400
12GB	10000
16GB	13500
24GB	21500
32GB	29000
48GB	44000
64GB	60000
72GB	68000
96GB	92000
128GB	124000

SQL TempTable差異及使用場合

#TempTable
在需要新增多筆暫存資料的場合，盡量使用 temp table。

DECLARE @Table Table(ID INT);
只有單筆記錄的場合才使用 table variable

SQL將所有資料表放入MemoryCache

DECLARE @TableName NVarChar(max)
DECLARE @TSQL NVarChar(max)

DECLARE curSelect CURSOR FAST_FORWARD FOR
SELECT name FROM sys.tables WHERE type='U' ORDER BY name;

OPEN curSelect

FETCH NEXT FROM curSelect
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL=N'
SELECT * FROM '+@TableName
EXEC(@TSQL);

WAITFOR DELAY '00:00:01'

    FETCH NEXT FROM curSelect
    INTO @TableName
END
CLOSE curSelect;
DEALLOCATE curSelect;

移除SQL執行計畫快取

由於執行計畫可能被快取，所以有時候後來執行的TSQL會使用到前面的執行計畫，若要確保使用新的執行計畫，可以透過以下語法，從計畫快取移除所有元素

DBCC FREEPROCCACHE;

SQL使用CURSOR語法

DECLARE vendor_cursor CURSOR FAST_FORWARD FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
--WHILE do something
WAITFOR DELAY '00:00:01'

    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

SQL執行動態TSQL回傳值

declare @rsql Varchar(250)

declare @csql Varchar(300)

declare @rc Nvarchar(500)

declare @cstucount int

declare @ccount int

set @rsql='(select Classroom_id from EA_RoomTime where zc='+@zc+' and xq='+@xq+' and T'+@time+'=''否'') and ClassroomType=''1'''

--exec(@rsql)

set @csql='select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '

set @rc=@csql+@rsql

exec sp_executesql @rc,N'@a int output,@b int output',@cstucount output,@ccount output--將exec的結果放入變數中的做法

SQL動態CREATE VIEW指定DB

DECLARE @sql nvarchar(4000);
DECLARE @sql_package nvarchar(4000);
DECLARE @dbName varchar(100);

set @dbName = 'Northwind';
set @sql = 'create view dbo.test_view '
set @sql = @sql + ' as'
set @sql = @sql + ' select * from customers;'

SET @sql_package = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@sql_package)

SQL更改ServerName

--更名後 ServerName
SELECT CONVERT(varchar(255), SERVERPROPERTY('servername'));
GO
--更名前 ServerName
select @@servername;
GO

--Drop錯的 ServerName
sp_dropserver 'old_name';
GO
--新增正確 ServerName
sp_addserver 'new_name', local;
GO

SQL更改資料庫名稱

USE master;
GO

-- 切換至單人模式
ALTER DATABASE DB1
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO

-- 更名資料庫
EXEC sp_renamedb '<欲更名的資料庫名稱>', '<新的資料庫名稱>';
GO

-- 回復多人模式
ALTER DATABASE DB1
SET MULTI_USER
GO

--更改群組檔案、資料庫檔案、資料庫日誌檔的名稱
ALTER DATABASE <舊資料庫名稱>
Modify NAME = <新資料庫名稱>;

SQL恢復[正在還原]資料庫

RESTORE DATABASE DB1
WITH RECOVERY

SQL搬移Table到新filegroup語法

--適用 PK Index 同時存在 PK Constraint
1.搬移 Data & 刪除 PK Index:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 WITH (ONLINE=ON, MOVE TO [FG_NEW]);

2.新增 PK Index:
ALTER TABLE Table1  ADD CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED ([KeyCol]) ON [FG_NEW];

3.搬移 NonClustered index:
CREATE NONCLUSTERED INDEX IX_Table1
ON Table (FLD1)
WITH (DROP_EXISTING = ON, ONLINE = ON, FILLFACTOR =90)
 ON [FG_NEW];

SQL 錯誤訊息抓取

DECLARE @ERR_NO INT
DECLARE @ERR_MSG NVarChar(max)

SELECT 1/0;
SET @ERR_NO=@@ERROR

SELECT @ERR_MSG='ERR('+CAST(@ERR_NO AS NVarChar(max))+'): '+text
FROM sys.messages
WHERE language_id=1028
AND message_id = @ERR_NO;

PRINT @ERR_MSG;

--===============================

BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
PRINT 'ERR('+CAST(ERROR_NUMBER() AS NVarChar(max))+'): '+ERROR_MESSAGE()
END CATCH
PRINT '仍繼續往下'

T-SQL 執行計畫中排序為最高成本的解決方法

SELECT FLD1, FLD2
FROM TABLE1
ORDER BY FLD3, FLD4

1.建立 Index
CREATE NONCLUSTERED INDEX NCI_TABLE1
ON TABLE1(FLD3, FLD4)
INCLUDE (FLD1, FLD2)

2.更新統計資訊
UPDATE STATISTICS TABLE1;

SQL 並用 PARTITION 與 ROW_NUMBER()範例

--總筆數TotalCounts, 群組筆數GroupCounts, 排序每筆RowID
SELECT TotalCounts=COUNT(*) OVER(),
               GroupCounts=COUNT(*) OVER(PARTITION BY TerritoryName),
               ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowID
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;

SQL StoreProcedure 參數範例

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName nvarchar(50),
    @FirstName nvarchar(50)
AS
 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

SQL統計資訊方針

1.取消自動更新統計資訊設定

2.新增維護計劃固定更新統計資訊

3.新增維護計劃刪除SQL自動更新統計資訊(_WA_SYS)

Store Procedure 執行計劃最佳化

--SQL Server 2008 以上版本在Store procedure 中, 加上 OPTIMIZE FOR
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION (OPTIMIZE FOR (@city_name UNKNOWN));

更新SQL資料庫或資料表狀態

1.更新DB:
EXEC sp_updatestats;
GO
DBCC UPDATEUSAGE (DB1);
GO

2.更新Table
UPDATE STATISTICS Table1;
GO
DBCC UPDATEUSAGE (DB1,'Table1');
GO

3.快速更新Table(剔除Index)
UPDATE STATISTICS Table1 WITH FULLSCAN, COLUMNS;
GO

將 SQL 單一大 DB.mdf 資料平均分配到多個 DB.ndf

1.新增 DB.ndf:
ALTER DATABASE DB1
ADD FILE
(
NAME = DF1,
FILENAME = 'D:\SQL_DATA\df1.ndf',
SIZE = 10240MB,
FILEGROWTH = 100MB
)

2.將資料從大DB.mdf 搬至其他 DB.ndf:
DBCC SHRINKFILE(DB, EMPTYFILE)

3.觀察資料搬移情形(UsedExtents), 並隨時中止搬移:
DBCC SHOWFILESTATS