شمارش ردیف و فضای مصرفی جداول SQL Server با sp_rows

شمارش ردیف و بررسی فضای مصرفی جداول SQL Server: راهنمای جامع با sp_rows

در مدیریت پایگاه‌های داده SQL Server، یکی از نیازهای رایج، دستیابی به اطلاعات دقیق درباره تعداد ردیف‌ها و فضای دیسک مصرفی توسط جداول است. این اطلاعات برای تحلیل عملکرد، برنامه‌ریزی ظرفیت و بهینه‌سازی پایگاه داده اهمیت فراوانی دارد. معمولاً این کار می‌تواند کمی پیچیده باشد، اما راهکارهای مختلفی برای آن وجود دارد که در این مقاله به بررسی آن‌ها می‌پردازیم.

استفاده از sp_spaceused برای بررسی فضای تک جدول

یکی از ساده‌ترین روش‌ها برای به دست آوردن اطلاعات فضای مصرفی و تعداد ردیف یک جدول خاص، استفاده از Stored Procedure سیستمی sp_spaceused است. این پروسیجر اطلاعات کاملی درباره فضای مصرفی، تعداد ردیف‌ها، فضای رزرو شده و فضای استفاده شده توسط داده‌ها و ایندکس‌ها را برای یک جدول مشخص ارائه می‌دهد.

EXEC sp_spaceused 'tbl_Sample'

اگرچه این روش برای یک جدول واحد بسیار کاربردی است، اما برای بررسی چندین جدول یا کل پایگاه داده، نیاز به رویکرد دیگری داریم.

بررسی فضای مصرفی و شمارش ردیف برای تمام جداول با sp_MSforeachtable

برای بررسی تمام جداول یک پایگاه داده، می‌توان از Stored Procedure سیستمی sp_MSforeachtable به همراه sp_spaceused استفاده کرد. این روش به شما امکان می‌دهد تا یک دستور را برای هر جدول کاربر در پایگاه داده فعلی اجرا کنید و نتایج را جمع‌آوری نمایید. ابتدا یک جدول موقت ایجاد می‌کنیم تا نتایج را در آن ذخیره کنیم و سپس با اجرای sp_MSforeachtable برای هر جدول، خروجی sp_spaceused را در آن درج می‌کنیم.

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
 DROP TABLE #SpaceUsed;
CREATE TABLE #SpaceUsed
 (
  name          NVARCHAR(128)
 ,rows          CHAR(11)
 ,reserved      VARCHAR(18)
 ,data          VARCHAR(18)
 ,index_size    VARCHAR(18)
 ,unused        VARCHAR(18)
 );

INSERT INTO #SpaceUsed
 EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';

SELECT
 name
 ,rows
 ,CAST(REPLACE(reserved, ' KB', '') AS INT) / 1024. AS reserved_mb
 ,CAST(REPLACE(data, ' KB', '') AS INT) / 1024. AS data_mb
 ,CAST(REPLACE(index_size, ' KB', '') AS INT) / 1024. AS index_mb
 ,CAST(REPLACE(unused, ' KB', '') AS INT) / 1024. AS unused_mb
FROM #SpaceUsed
ORDER BY name;

این رویکرد اطلاعات ارزشمندی را ارائه می‌دهد، اما نیازمند ایجاد یک جدول موقت و تبدیل رشته‌ها به اعداد برای محاسبات است. همچنین، فیلتر کردن بر اساس Schema یا نام جدول در آن به سادگی قابل پیاده‌سازی نیست.

معرفی Stored Procedure سفارشی sp_rows برای بررسی جامع

با توجه به چالش‌های موجود در روش‌های فوق، می‌توان یک Stored Procedure سفارشی به نام sp_rows ایجاد کرد که انعطاف‌پذیری بیشتری را برای دریافت شمارش ردیف و فضای مصرفی جداول فراهم می‌کند. این پروسیجر به شما امکان می‌دهد تا نتایج را فیلتر کرده، بر اساس پارامترهای مختلف مرتب‌سازی نمایید و اطلاعات را به صورت دقیق‌تر و کاربردی‌تری مشاهده کنید.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_rows]
 @TableName NVARCHAR(2000) = NULL
,@schemaName NVARCHAR(2000) = NULL
,@RowCountOnly BIT = 0
,@IncludeSystemObjects BIT = 0
,@DatabaseName NVARCHAR(128) = NULL
,@OrderBy VARCHAR(20) = 'TableName'
,@IsDescending BIT = 0
AS
BEGIN
 SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX);
 DECLARE @DB SYSNAME;
 SET @DB = ISNULL(@DatabaseName, DB_NAME());

 SET @sql = '
IF OBJECT_ID(''tempdb..#SpaceUsed'') IS NOT NULL
 DROP TABLE #SpaceUsed;
CREATE TABLE #SpaceUsed
 (
  name          NVARCHAR(128)
 ,rows          CHAR(11)
 ,reserved      VARCHAR(18)
 ,data          VARCHAR(18)
 ,index_size    VARCHAR(18)
 ,unused        VARCHAR(18)
 );

INSERT INTO #SpaceUsed
 EXEC ' + QUOTENAME(@DB) + '.sys.sp_MSforeachtable ''EXEC ' + QUOTENAME(@DB) + '.sys.sp_spaceused ''''?''''''';

 SELECT
  CAST(CASE WHEN PARSENAME(name, 2) IS NULL THEN PARSENAME(name, 1) ELSE PARSENAME(name, 2) END AS NVARCHAR(128)) AS SchemaName
 ,CAST(CASE WHEN PARSENAME(name, 2) IS NULL THEN '''' ELSE PARSENAME(name, 1) END AS NVARCHAR(128)) AS TableName
 ,CAST(rows AS INT) AS RowCount
 ,CAST(REPLACE(reserved, '' KB'', '''') AS INT) / 1024. AS Reserved_MB
 ,CAST(REPLACE(data, '' KB'', '''') AS INT) / 1024. AS Data_MB
 ,CAST(REPLACE(index_size, '' KB'', '''') AS INT) / 1024. AS Index_MB
 ,CAST(REPLACE(unused, '' KB'', '''') AS INT) / 1024. AS Unused_MB
 INTO #FinalSpaceUsed
 FROM #SpaceUsed
 WHERE 1 = 1';

 IF @IncludeSystemObjects = 0
 BEGIN
  SET @sql = @sql + ' AND name NOT LIKE ''sys.%'' AND name NOT LIKE ''MSreplication_objects'' AND name NOT LIKE ''MSpeer_lsns'' AND name NOT LIKE ''MStracer_tokens''';
 END;

 IF @TableName IS NOT NULL
 BEGIN
  SET @TableName = REPLACE(@TableName, '''', '''''');
  SET @sql = @sql + ' AND CASE WHEN PARSENAME(name, 2) IS NULL THEN PARSENAME(name, 1) ELSE PARSENAME(name, 1) END LIKE ''' + @TableName + '''';
 END;

 IF @schemaName IS NOT NULL
 BEGIN
  SET @schemaName = REPLACE(@schemaName, '''', '''''');
  SET @sql = @sql + ' AND CASE WHEN PARSENAME(name, 2) IS NULL THEN PARSENAME(name, 1) ELSE PARSENAME(name, 2) END LIKE ''' + @schemaName + '''';
 END;

 IF @RowCountOnly = 1
 BEGIN
  SET @sql = @sql + ' SELECT SchemaName, TableName, RowCount FROM #FinalSpaceUsed';
 END
 ELSE
 BEGIN
  SET @sql = @sql + ' SELECT SchemaName, TableName, RowCount, Reserved_MB, Data_MB, Index_MB, Unused_MB FROM #FinalSpaceUsed';
 END;

 SET @sql = @sql + ' ORDER BY ' + QUOTENAME(@OrderBy);

 IF @IsDescending = 1
 BEGIN
  SET @sql = @sql + ' DESC';
 END
 ELSE
 BEGIN
  SET @sql = @sql + ' ASC';
 END;

 --PRINT @sql;
 EXEC sp_executesql @sql;

END;

این پروسیجر در پایگاه داده master ایجاد می‌شود تا از هر پایگاه داده‌ای قابل دسترسی باشد. پارامترهای آن شامل @TableName، @schemaName برای فیلتر کردن جداول، @RowCountOnly برای نمایش فقط تعداد ردیف‌ها، @IncludeSystemObjects برای گنجاندن اشیاء سیستمی، @DatabaseName برای مشخص کردن پایگاه داده مورد نظر و @OrderBy و @IsDescending برای مرتب‌سازی نتایج هستند.

نحوه استفاده از sp_rows

پس از ایجاد Stored Procedure sp_rows، می‌توانید به سادگی از آن برای دریافت اطلاعات مورد نیاز خود استفاده کنید:

-- نمایش تمامی جداول در پایگاه داده فعلی
EXEC sp_rows;

-- نمایش جداول با فیلتر نام (مانند '%User%')
EXEC sp_rows @TableName = '%User%';

-- نمایش جداول با فیلتر Schema (مانند 'dbo')
EXEC sp_rows @schemaName = 'dbo';

-- نمایش فقط تعداد ردیف‌ها
EXEC sp_rows @RowCountOnly = 1;

-- مرتب‌سازی بر اساس تعداد ردیف‌ها به صورت نزولی
EXEC sp_rows @OrderBy = 'RowCount', @IsDescending = 1;

-- بررسی یک پایگاه داده دیگر (مثلاً 'AdventureWorks2019')
EXEC sp_rows @DatabaseName = 'AdventureWorks2019', @OrderBy = 'Reserved_MB', @IsDescending = 1;

جزئیات پیاده‌سازی sp_rows

پروسیجر sp_rows به طور داخلی از sp_MSforeachtable برای جمع‌آوری اطلاعات از تمام جداول استفاده می‌کند، دقیقاً مانند روش دستی که پیش‌تر توضیح داده شد. سپس، این اطلاعات را در یک جدول موقت #SpaceUsed ذخیره می‌کند. پس از آن، یک دستور SQL پویا (Dynamic SQL) ساخته می‌شود که بر اساس پارامترهای ورودی، فیلترها و مرتب‌سازی‌های لازم را اعمال می‌کند.

تابع PARSENAME برای استخراج نام Schema و نام جدول از فیلد name استفاده می‌شود. این تابع نام شیء را به چهار بخش (Server.Database.Schema.Object) تقسیم می‌کند. از آنجایی که sp_spaceused فقط نام جدول و Schema را برمی‌گرداند، PARSENAME(name, 2) برای Schema و PARSENAME(name, 1) برای نام جدول به کار می‌رود. در مواردی که Schema مشخص نشده باشد (مانند جداول سیستمی)، PARSENAME(name, 2) مقدار NULL برمی‌گرداند و نام جدول از PARSENAME(name, 1) به دست می‌آید.

دستور REPLACE(reserved, ' KB', '') برای حذف عبارت ” KB” از رشته‌های فضای مصرفی و تبدیل آن‌ها به مقادیر عددی (INT) به کار می‌رود. سپس این مقادیر بر 1024 تقسیم می‌شوند تا به واحد مگابایت (MB) تبدیل گردند که خوانایی آن‌ها را به شدت بهبود می‌بخشد.

بخش WHERE 1 = 1 در دستور SQL پویا به عنوان یک پایه برای اضافه کردن شرط‌های فیلتر پویا استفاده می‌شود. این یک الگوی رایج برای جلوگیری از مشکلات سینتکسی در زمان اضافه کردن شرط‌ها با AND است. شرط‌های name NOT LIKE 'sys.%' و سایر موارد مشابه، جداول سیستمی را فیلتر می‌کنند، مگر اینکه پارامتر @IncludeSystemObjects روی 1 تنظیم شده باشد.

مرتب‌سازی نتایج با استفاده از پارامتر @OrderBy و @IsDescending به صورت پویا ساخته می‌شود. این امکان را فراهم می‌کند که کاربران نتایج را بر اساس ستون‌های مختلف مانند TableName، RowCount، Reserved_MB و غیره، به صورت صعودی یا نزولی مرتب کنند.

در نهایت، دستور SQL ساخته شده با sp_executesql اجرا می‌شود. این یک روش ایمن و کارآمد برای اجرای SQL پویا است که از حملات SQL Injection نیز جلوگیری می‌کند و به پارامترسازی کوئری‌ها کمک می‌کند.