Tạo khóa chính cho các bảng chưa có ràng buộc Primary Key
Đoạn script dưới đây dùng để quét toàn bộ các bảng người dùng (User Table) và tự động thêm khóa chính vào cột id đối với những bảng chưa thiết lập.
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @SQLScript NVARCHAR(MAX);
DECLARE TableList CURSOR FOR
SELECT s.name, t.name
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE NOT EXISTS (
SELECT 1
FROM sys.key_constraints k
WHERE k.parent_object_id = t.object_id
AND k.type = 'PK'
);
OPEN TableList;
FETCH NEXT FROM TableList INTO @SchemaName, @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'Đang xử lý bảng: ' + @TableName;
SET @SQLScript = 'ALTER TABLE [' + @SchemaName + '].[' + @TableName + '] ADD CONSTRAINT [PK_' + @TableName + '] PRIMARY KEY CLUSTERED (id)';
EXEC sp_executesql @SQLScript;
FETCH NEXT FROM TableList INTO @SchemaName, @TableName;
END;
CLOSE TableList;
DEALLOCATE TableList;
Phân tích các truy vấn tiêu tốn nhiều tài nguyên nhất
Script giúp giám sát hiệu năng bằng cách liệt kê các câu lệnh SQL có thời gian thực thi tổng cộng lớn nhất, bao gồm thông tin về I/O và CPU.
SELECT TOP 20
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 QueryText,
qs.execution_count AS ExecutionCount,
qs.total_logical_reads AS TotalLogicalReads,
qs.total_logical_writes AS TotalLogicalWrites,
qs.total_elapsed_time / 1000 AS TotalElapsedTimeMs,
qs.total_worker_time / 1000 AS TotalCPUTimeMs,
DB_NAME(qs.database_id) AS DatabaseName
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_elapsed_time DESC;
Tạo Index dựa trên thống thiếu sót của hệ thống (Missing Indexes)
Hệ thống SQL Server lưu trữ thông tin về các Index có thể giúp cải thiện hiệu suất. Script này sẽ tính toán mức độ ảnh hưởng và tự động sinh ra câu lệnh tạo Index phù hợp.
SELECT
OBJECT_NAME(d.object_id) AS TableName,
'CREATE NONCLUSTERED INDEX [IX_' + REPLACE(REPLACE(REPLACE(ISNULL(equality_columns, ''), ',', '_'), ' ', ''), '[', '') + '_' + CAST(NEWID() AS VARCHAR(8)) + '] ON ' + SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(d.object_id) +
' (' + ISNULL(equality_columns, '') +
CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ', ' ELSE '' END +
ISNULL(inequality_columns, '') + ')' +
ISNULL(' INCLUDE (' + included_columns + ')', '') AS CreateIndexScript,
gs.avg_user_impact AS ImpactPercent,
gs.avg_total_user_cost * avg_user_impact * (gs.user_seeks + gs.user_scans) AS ImprovementScore
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats gs ON g.index_group_handle = gs.group_handle
INNER JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
ORDER BY ImprovementScore DESC;
Xem chi tiết cấu trúc Index của một bảng cụ thể
Để kiểm tra các cột đang tham gia vào Index của bảng YourTableName, bạn có thể sử dụng truy vấn sau.
DECLARE @TargetTable NVARCHAR(256) = 'YourTableName';
SELECT
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
c.name AS ColumnName,
ic.key_ordinal AS ColumnOrder,
ic.is_included_column AS IsIncluded
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE t.name = @TargetTable
ORDER BY i.name, ic.key_ordinal;
Kiểm tra mức độ phân mảnh (Fragmentation) của Index
Phân mảnh cao làm giảm hiệu suất đọc dữ liệu. Script dưới đây giúp xác định chỉ số phân mảnh và đề xuất hành động Rebuild hay Reorganize.
SELECT
t.name AS TableName,
i.name AS IndexName,
s.avg_fragmentation_in_percent AS FragmentationPercent,
CASE
WHEN s.avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX [' + i.name + '] ON [' + t.name + '] REBUILD'
WHEN s.avg_fragmentation_in_percent > 10 THEN 'ALTER INDEX [' + i.name + '] ON [' + t.name + '] REORGANIZE'
ELSE 'No Action Needed'
END AS RecommendedAction
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, i.index_id, NULL, 'LIMITED') s
WHERE s.avg_fragmentation_in_percent > 5
AND i.name IS NOT NULL
ORDER BY s.avg_fragmentation_in_percent DESC;
Script tự động bảo trì và tối ưu hóa Index toàn cơ sở dữ liệu
Sử dụng con trỏ (Cursor) để duyệt qua các Index có phân mảnh và thực thi lệnh Rebuild hoặc Reorganize tương ứng.
SET NOCOUNT ON;
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @Fragmentation FLOAT;
DECLARE @MaintenanceSQL NVARCHAR(MAX);
DECLARE IndexCursor CURSOR FOR
SELECT
SCHEMA_NAME(t.schema_id),
t.name,
i.name,
s.avg_fragmentation_in_percent
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), t.object_id, i.index_id, NULL, 'LIMITED') s
WHERE i.name IS NOT NULL
AND s.avg_fragmentation_in_percent > 5.0
AND s.page_count > 1000; -- Chỉ xử lý các index có dung lượng đáng kể
OPEN IndexCursor;
FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @Fragmentation;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Fragmentation > 30.0
BEGIN
SET @MaintenanceSQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REBUILD WITH (ONLINE = OFF);';
PRINT N'Rebuilding Index: ' + @IndexName + N' trên bảng ' + @TableName;
END
ELSE
BEGIN
SET @MaintenanceSQL = 'ALTER INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + '] REORGANIZE;';
PRINT N'Reorganizing Index: ' + @IndexName + N' trên bảng ' + @TableName;
END
BEGIN TRY
EXEC sp_executesql @MaintenanceSQL;
END TRY
BEGIN CATCH
PRINT N'Lỗi khi xử lý Index ' + @IndexName + N': ' + ERROR_MESSAGE();
END CATCH
FETCH NEXT FROM IndexCursor INTO @SchemaName, @TableName, @IndexName, @Fragmentation;
END;
CLOSE IndexCursor;
DEALLOCATE IndexCursor;
SET NOCOUNT OFF;