萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 統計所有使用者資料表筆數:游標法 vs 系統檢視法效能大比拚

專案需求:監控資料庫中所有使用者資料表的列數。作者提供兩種實作並實測效能,結果驚人。

方法一:游標動態組合 SQL(傳統寫法)

DECLARE @NAME VARCHAR(50), @SQL VARCHAR(MAX);

SET @SQL = '
DECLARE @RESULT_TABLE TABLE ([TableName] VARCHAR(32), [RowCount] INT);
DECLARE @TEMP_COUNT INT;';

DECLARE TB_CURSOR CURSOR FOR
SELECT TABLE_NAME = CONVERT(SYSNAME, O.NAME)
FROM SYS.ALL_OBJECTS O
WHERE O.TYPE = 'U' 
  AND HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(O.SCHEMA_ID)) + '.' + QUOTENAME(O.NAME), 'OBJECT', 'SELECT') = 1;

OPEN TB_CURSOR;
FETCH NEXT FROM TB_CURSOR INTO @NAME;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = @SQL + CHAR(10) + 'INSERT INTO @RESULT_TABLE SELECT ''' + @NAME + ''', COUNT(1) FROM ' + @NAME + ';';
    FETCH NEXT FROM TB_CURSOR INTO @NAME;
END

CLOSE TB_CURSOR; DEALLOCATE TB_CURSOR;

SET @SQL = @SQL + CHAR(10) + 'SELECT * FROM @RESULT_TABLE';
EXEC (@SQL);

特點:使用表變數存放中繼結果,避免 tempdb 開銷;但游標逐列處理、動態 SQL 組合、大量 COUNT(*) 掃描,效能較差。

方法二:系統檢視直接查詢(集合導向)

DECLARE @RESULT_TABLE TABLE ([TableName] VARCHAR(32), [RowCount] INT);

INSERT INTO @RESULT_TABLE
SELECT O.NAME, I.ROWCNT
FROM SYS.SYSOBJECTS O, SYSINDEXES I
WHERE O.ID = I.ID 
  AND O.XTYPE = 'U' 
  AND I.INDID < 2;  -- 0=Heap, 1=Clustered Index

SELECT * FROM @RESULT_TABLE;

原理:SYSINDEXES.ROWCNT 儲存資料列估計值(統計資料更新後較準確),INDID < 2 只取堆積或叢集索引,避免重複計算非叢集索引。

效能實測對比

指標方法一(游標)方法二(系統檢視)
CPU 時間片622
邏輯讀取極高(每表掃描)極低(僅讀系統表)
執行時間秒級毫秒級

結論與建議

  • 方法二完勝:效能差距 30 倍以上,且程式碼簡潔
  • ROWCNT 為統計資料估計值,極高精度需求可搭配 DBCC UPDATEUSAGE 或 sp_spaceused
  • SQL Server 2005+ 建議改用 sys.dm_db_partition_stats 或 sys.tables 搭配 SUM(p.rows) 獲得更準確結果
  • 避免在生產環境高峰期對大量大表執行 COUNT(*)

善用系統目錄檢視,拒絕游標暴力枚舉,是 DBA 與開發者的基本功。

copyright © 萬盛學電腦網 all rights reserved