\u4f7f\u7528SQL Server\u7684\u5167\u5efa\u89d2\u8272\u548c\u81ea\u5b9a\u7fa9\u6b0a\u9650\u5be6\u65bd\u6700\u5c0f\u6b0a\u9650\u6a21\u578b\u3002\u4ee5\u4e0b\u662f\u5275\u5efa\u5177\u6709\u7279\u5b9a\u6b0a\u9650\u7684\u81ea\u5b9a\u7fa9\u89d2\u8272\u7684\u793a\u4f8b:<\/p>\n

CREATE ROLE DataAnalyst\r\nGRANT SELECT ON Schema::dbo TO DataAnalyst\r\nGRANT EXECUTE ON Schema::dbo TO DataAnalyst\r\nDENY DELETE ON Schema::dbo TO DataAnalyst\r\nGO<\/code><\/pre>\n
\u76e1\u53ef\u80fd\u4f7f\u7528Windows\u8eab\u4efd\u9a57\u8b49,\u56e0\u70ba\u5b83\u5229\u7528\u4e86Windows\u4f5c\u696d\u7cfb\u7d71\u7684\u5b89\u5168\u529f\u80fd\u3002<\/p>\n
2.2 \u8cc7\u6599\u52a0\u5bc6<\/h3>\n
\u4f7f\u7528\u900f\u660e\u8cc7\u6599\u52a0\u5bc6(TDE)\u4fdd\u8b77\u975c\u614b\u8cc7\u6599\u3002\u4f7f\u7528\u4ee5\u4e0bT-SQL\u547d\u4ee4\u555f\u7528TDE:<\/p>\n
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';\r\nCREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';\r\nCREATE DATABASE ENCRYPTION KEY\r\nWITH ALGORITHM = AES_256\r\nENCRYPTION BY SERVER CERTIFICATE TDECert;\r\nALTER DATABASE YourDatabase\r\nSET ENCRYPTION ON;<\/code><\/pre>\n
\u5c0d\u65bc\u654f\u611f\u6b04\u4f4d,\u8003\u616e\u4f7f\u7528Always Encrypted,\u5b83\u78ba\u4fdd\u8cc7\u6599\u5728\u8a18\u61b6\u9ad4\u4e2d\u4e5f\u4fdd\u6301\u52a0\u5bc6\u72c0\u614b\u3002<\/p>\n
2.3 \u7a3d\u6838\u548c\u5408\u898f<\/h3>\n
\u8a2d\u7f6eSQL Server Audit\u4ee5\u8ffd\u8e64\u8cc7\u6599\u5eab\u6d3b\u52d5\u4e26\u78ba\u4fdd\u7b26\u5408\u76e3\u7ba1\u8981\u6c42\u3002\u4ee5\u4e0b\u662f\u5275\u5efa\u4f3a\u670d\u5668\u7a3d\u6838\u898f\u7bc4\u7684\u65b9\u6cd5:<\/p>\n
CREATE SERVER AUDIT MyServerAudit\r\nTO FILE (FILEPATH = 'C:\\SQLAudit\\')\r\n\r\nCREATE SERVER AUDIT SPECIFICATION MyServerAuditSpecification\r\nFOR SERVER AUDIT MyServerAudit\r\nADD (FAILED_LOGIN_GROUP),\r\nADD (SUCCESSFUL_LOGIN_GROUP)\r\nWITH (STATE = ON)<\/code><\/pre>\n
\u5b9a\u671f\u5be9\u67e5\u7a3d\u6838\u65e5\u8a8c\u4e26\u8a2d\u7f6e\u53ef\u7591\u6d3b\u52d5\u7684\u8b66\u5831\u3002<\/p>\n
[\/vc_column_text][\/vc_column][\/vc_row][vc_row el_class=”blog-detail-section”][vc_column][vc_column_text]<\/p>\n
3. SQL Server \u6548\u80fd\u512a\u5316<\/strong><\/h2>\n
\u512a\u5316SQL Server\u6548\u80fd\u5c0d\u65bc\u7dad\u8b77\u9ad8\u6548\u7684\u8cc7\u6599\u5eab\u64cd\u4f5c\u81f3\u95dc\u91cd\u8981\u3002\u8b93\u6211\u5011\u63a2\u8a0e\u4e00\u4e9b\u63d0\u5347SQL Server\u6548\u80fd\u7684\u9ad8\u7d1a\u6280\u8853\u3002<\/p>\n
3.1 \u67e5\u8a62\u512a\u5316\u6280\u8853<\/h3>\n
\u5229\u7528\u67e5\u8a62\u57f7\u884c\u8a08\u5283\u8b58\u5225\u74f6\u9838\u3002SQL Server 2016\u53ca\u66f4\u9ad8\u7248\u672c\u4e2d\u7684Query Store\u529f\u80fd\u63d0\u4f9b\u4e86\u6709\u95dc\u67e5\u8a62\u6548\u80fd\u96a8\u6642\u9593\u8b8a\u5316\u7684\u5bf6\u8cb4\u898b\u89e3\u3002\u4f7f\u7528\u4ee5\u4e0b\u547d\u4ee4\u555f\u7528Query Store:<\/p>\n
ALTER DATABASE YourDatabase\r\nSET QUERY_STORE = ON\r\n(\r\n    OPERATION_MODE = READ_WRITE,\r\n    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),\r\n    DATA_FLUSH_INTERVAL_SECONDS = 3000,\r\n    MAX_STORAGE_SIZE_MB = 1000,\r\n    INTERVAL_LENGTH_MINUTES = 60\r\n)<\/code><\/pre>\n
\u8b39\u614e\u4f7f\u7528\u67e5\u8a62\u63d0\u793a\u4f86\u6307\u5c0e\u67e5\u8a62\u6700\u4f73\u5316\u5668\u3002\u4ee5\u4e0b\u662f\u4f7f\u7528FORCE ORDER\u63d0\u793a\u7684\u793a\u4f8b:<\/p>\n
SELECT c.CustomerName, o.OrderDate\r\nFROM Customers c\r\nINNER JOIN Orders o ON c.CustomerID = o.CustomerID\r\nOPTION (FORCE ORDER)<\/code><\/pre>\n
3.2 \u7d22\u5f15\u512a\u5316<\/h3>\n
\u5be6\u65bd\u53e2\u96c6\u548c\u975e\u53e2\u96c6\u7d22\u5f15\u7684\u7d44\u5408\u3002\u4f7f\u7528\u8cc7\u6599\u5eab\u5f15\u64ce\u8abf\u512a\u9867\u554f\u63a8\u85a6\u6700\u4f73\u7d22\u5f15\u7b56\u7565\u3002\u8003\u616e\u70ba\u8cc7\u6599\u5009\u5132\u67e5\u8a62\u4f7f\u7528\u6b04\u4f4d\u5b58\u5132\u7d22\u5f15:<\/p>\n
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_FactSales_ColumnStore\r\nON FactSales\r\n(DateKey, ProductKey, CustomerKey, SalesAmount)<\/code><\/pre>\n
3.3 \u8cc7\u6e90\u8abf\u63a7\u5668\u4f7f\u7528<\/h3>\n
\u5229\u7528\u8cc7\u6e90\u8abf\u63a7\u5668\u7ba1\u7406\u5de5\u4f5c\u8ca0\u8f09\u7d44,\u9632\u6b62\u8cc7\u6e90\u5bc6\u96c6\u578b\u67e5\u8a62\u5f71\u97ff\u6574\u9ad4\u6548\u80fd\u3002\u4ee5\u4e0b\u662f\u8a2d\u7f6e\u57fa\u672c\u8cc7\u6e90\u8abf\u63a7\u5668\u914d\u7f6e\u7684\u65b9\u6cd5:<\/p>\n
-- \u5275\u5efa\u5de5\u4f5c\u8ca0\u8f09\u7d44\r\nCREATE WORKLOAD GROUP ReportingGroup\r\nWITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,\r\n      REQUEST_MAX_CPU_TIME_SEC = 60);\r\n\r\n-- \u5275\u5efa\u5206\u985e\u5668\u51fd\u6578\r\nCREATE FUNCTION dbo.ClassifierFunction() \r\nRETURNS SYSNAME \r\nWITH SCHEMABINDING\r\nAS\r\nBEGIN\r\n    RETURN \r\n    CASE \r\n        WHEN IS_MEMBER('ReportingUsers') = 1 \r\n        THEN 'ReportingGroup'\r\n        ELSE 'default'\r\n    END\r\nEND;\r\n\r\n-- \u61c9\u7528\u5206\u985e\u5668\u51fd\u6578\r\nALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifierFunction);\r\nALTER RESOURCE GOVERNOR RECONFIGURE;<\/code><\/pre>\n
[\/vc_column_text][\/vc_column][\/vc_row][vc_row el_class=”blog-detail-section”][vc_column][vc_column_text]<\/p>\n
4. \u9ad8\u7d1a T-SQL \u7a0b\u5f0f\u8a2d\u8a08<\/strong><\/h2>\n
T-SQL, SQL Server\u7684SQL\u8b8a\u9ad4,\u70ba\u8907\u96dc\u8cc7\u6599\u64cd\u4f5c\u548c\u7ba1\u7406\u63d0\u4f9b\u4e86\u5f37\u5927\u7684\u529f\u80fd\u3002<\/p>\n
4.1 \u901a\u7528\u8cc7\u6599\u8868\u904b\u7b97\u5f0f (CTEs)<\/h3>\n
CTEs\u63d0\u4f9b\u4e86\u4e00\u7a2e\u7de8\u5beb\u66f4\u6613\u8b80\u548c\u53ef\u7dad\u8b77\u4ee3\u78bc\u7684\u65b9\u6cd5\u3002\u4ee5\u4e0b\u662f\u4f7f\u7528\u905e\u8ff4CTE\u751f\u6210\u65e5\u671f\u7bc4\u570d\u7684\u793a\u4f8b:<\/p>\n
WITH DateCTE AS (\r\n    SELECT CAST('2024-01-01' AS DATE) AS Date\r\n    UNION ALL\r\n    SELECT DATEADD(day, 1, Date)\r\n    FROM DateCTE\r\n    WHERE Date < '2024-12-31'\r\n)\r\nSELECT Date FROM DateCTE\r\nOPTION (MAXRECURSION 366)<\/code><\/pre>\n
4.2 \u8996\u7a97\u51fd\u6578<\/h3>\n
\u8996\u7a97\u51fd\u6578\u5728\u8207\u7576\u524d\u884c\u76f8\u95dc\u7684\u4e00\u7d44\u884c\u4e0a\u57f7\u884c\u8a08\u7b97\u3002\u4ee5\u4e0b\u662f\u8a08\u7b97\u904b\u884c\u7e3d\u8a08\u7684\u793a\u4f8b:<\/p>\n
SELECT \r\n    OrderDate,\r\n    OrderTotal,\r\n    SUM(OrderTotal) OVER (ORDER BY OrderDate) AS RunningTotal\r\nFROM Orders<\/code><\/pre>\n
4.3 \u5b58\u5132\u7a0b\u5e8f\u4e2d\u7684\u932f\u8aa4\u8655\u7406<\/h3>\n
\u4f7f\u7528TRY…CATCH\u584a\u5728\u5b58\u5132\u7a0b\u5e8f\u4e2d\u5be6\u73fe\u7a69\u5065\u7684\u932f\u8aa4\u8655\u7406:<\/p>\n
CREATE PROCEDURE usp_InsertOrder\r\n    @CustomerID INT,\r\n    @OrderDate DATE,\r\n    @TotalAmount DECIMAL(18,2)\r\nAS\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n    BEGIN TRY\r\n        BEGIN TRANSACTION\r\n            INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)\r\n            VALUES (@CustomerID, @OrderDate, @TotalAmount)\r\n        COMMIT TRANSACTION\r\n    END TRY\r\n    BEGIN CATCH\r\n        IF @@TRANCOUNT > 0\r\n            ROLLBACK TRANSACTION\r\n        \r\n        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()\r\n        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()\r\n        DECLARE @ErrorState INT = ERROR_STATE()\r\n        \r\n        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)\r\n    END CATCH\r\nEND<\/code><\/pre>\n
[\/vc_column_text][\/vc_column][\/vc_row][vc_row el_class=”blog-detail-section”][vc_column][vc_column_text]<\/p>\n
5. \u4e26\u767c\u63a7\u5236\u548c\u6b7b\u9396\u8655\u7406<\/strong><\/h2>\n
\u7ba1\u7406\u4e26\u767c\u4ea4\u6613\u548c\u9632\u6b62\u6b7b\u9396\u5c0d\u65bc\u7dad\u8b77\u8cc7\u6599\u5eab\u97ff\u61c9\u6027\u548c\u5b8c\u6574\u6027\u81f3\u95dc\u91cd\u8981\u3002<\/p>\n
5.1 \u4ea4\u6613\u9694\u96e2\u5c64\u7d1a<\/h3>\n
\u6839\u64da\u61c9\u7528\u7a0b\u5f0f\u7684\u8981\u6c42\u9078\u64c7\u9069\u7576\u7684\u9694\u96e2\u5c64\u7d1a\u3002\u4ee5\u4e0b\u662f\u8a2d\u7f6e\u9694\u96e2\u5c64\u7d1a\u7684\u65b9\u6cd5:<\/p>\n
SET TRANSACTION ISOLATION LEVEL READ COMMITTED<\/code><\/pre>\n
\u8003\u616e\u5c0d\u8b80\u53d6\u5bc6\u96c6\u578b\u5de5\u4f5c\u8ca0\u8f09\u4f7f\u7528\u5feb\u7167\u9694\u96e2\u4ee5\u6e1b\u5c11\u963b\u585e:<\/p>\n
ALTER DATABASE YourDatabase\r\nSET ALLOW_SNAPSHOT_ISOLATION ON\r\n\r\n-- \u5728\u60a8\u7684\u61c9\u7528\u7a0b\u5f0f\u4e2d:\r\nSET TRANSACTION ISOLATION LEVEL SNAPSHOT<\/code><\/pre>\n
5.2 \u6b7b\u9396\u6aa2\u6e2c\u548c\u9810\u9632<\/h3>\n
\u4f7f\u7528SQL Server Profiler\u4e2d\u7684\u6b7b\u9396\u5716\u4f86\u5206\u6790\u548c\u9632\u6b62\u6b7b\u9396\u3002\u5be6\u65bd\u9069\u7576\u7684\u7d22\u5f15,\u4e26\u8003\u616e\u5728\u9069\u7576\u7684\u60c5\u6cc1\u4e0b\u4f7f\u7528NOLOCK\u63d0\u793a\u3002\u4ee5\u4e0b\u662f\u8a2d\u7f6e\u6b7b\u9396\u8ffd\u8e64\u7684\u793a\u4f8b:<\/p>\n
-- \u5275\u5efa\u8ffd\u8e64\u6587\u4ef6\r\nDECLARE @TraceID INT\r\nEXEC sp_trace_create @TraceID OUTPUT, 2, N'C:\\Traces\\DeadlockTrace'\r\n\r\n-- \u8a2d\u7f6e\u6b7b\u9396\u8ffd\u8e64\r\nEXEC sp_trace_setevent @TraceID, 148, 1, 1  -- TextData\r\nEXEC sp_trace_setevent @TraceID, 148, 12, 1 -- SPID\r\nEXEC sp_trace_setevent @TraceID, 148, 14, 1 -- StartTime\r\n\r\n-- \u555f\u52d5\u8ffd\u8e64\r\nEXEC sp_trace_setstatus @TraceID, 1\r\n\r\n-- \u8a18\u5f97\u5728\u5b8c\u6210\u5f8c\u505c\u6b62\u4e26\u522a\u9664\u8ffd\u8e64\r\n-- EXEC sp_trace_setstatus @TraceID, 0\r\n-- EXEC sp_trace_setstatus @TraceID, 2<\/code><\/pre>\n
5.3 \u9396\u5b9a\u5347\u7d1a\u548c\u9396\u5b9a\u63d0\u793a<\/h3>\n
\u77ad\u89e3\u9396\u5b9a\u5347\u7d1a\u6a5f\u5236,\u4e26\u8b39\u614e\u4f7f\u7528\u9396\u5b9a\u63d0\u793a\u4f86\u63a7\u5236\u4e26\u767c\u884c\u70ba\u3002\u4ee5\u4e0b\u662f\u4f7f\u7528\u9396\u5b9a\u63d0\u793a\u7684\u793a\u4f8b:<\/p>\n
SELECT * FROM Orders WITH (ROWLOCK, UPDLOCK)\r\nWHERE OrderID = 12345<\/code><\/pre>\n
\u8b39\u614e\u4f7f\u7528\u9396\u5b9a\u63d0\u793a,\u56e0\u70ba\u5982\u679c\u4f7f\u7528\u4e0d\u7576,\u5b83\u5011\u53ef\u80fd\u6703\u5f71\u97ff\u4e26\u767c\u6027\u548c\u6548\u80fd\u3002<\/p>\n
[\/vc_column_text][\/vc_column][\/vc_row][vc_row el_class=”blog-detail-section”][vc_column][vc_column_text]<\/p>\n
\u7d50\u8ad6<\/strong><\/h2>\n
\u638c\u63e1\u9019\u4e9b\u9ad8\u7d1aSQL Server\u7ba1\u7406\u6280\u8853\u5c0d\u65bc\u65e8\u5728\u512a\u5316\u7cfb\u7d71\u7684\u8cc7\u6599\u5eab\u5c08\u696d\u4eba\u58eb\u81f3\u95dc\u91cd\u8981\u3002\u5f9e\u7a69\u5065\u7684\u5099\u4efd\u7b56\u7565\u548c\u5805\u56fa\u7684\u5b89\u5168\u63aa\u65bd\u5230\u6548\u80fd\u8abf\u512a\u548c\u8907\u96dc\u7684T-SQL\u7a0b\u5f0f\u8a2d\u8a08,\u9019\u4e9b\u6280\u80fd\u69cb\u6210\u4e86\u6709\u6548\u8cc7\u6599\u5eab\u7ba1\u7406\u7684\u9aa8\u5e79\u3002\u5728\u5be6\u65bd\u9019\u4e9b\u6280\u8853\u6642,\u8acb\u8a18\u4f4f,\u6210\u529f\u7684\u95dc\u9375\u5728\u65bc\u6301\u7e8c\u5b78\u7fd2\u4e26\u9069\u61c9\u4e0d\u65b7\u767c\u5c55\u7684\u8cc7\u6599\u5eab\u6280\u8853\u9818\u57df\u3002<\/p>\n
[\/vc_column_text][\/vc_column][\/vc_row]<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"
[vc_row el_class=”blog-detail-section”][vc_ […]<\/p>\n
Read More…<\/a><\/p>\n","protected":false},"author":2,"featured_media":14784,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[61],"tags":[3089,2525,2526,1667,852],"class_list":["post-14814","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-hong-kong-dedicated-server-tc","tag-t-sql-tc","tag-sql-server-tc","tag-database-management-tc","tag-performance-tuning-tc","tag-server-security-tc"],"acf":[],"yoast_head":"\n