www.dntips.ir
مطالب ۳۶۹۴
نویسندگان ۲۷۶
گروه‌های مطالب ۱۰۲۴
نقشه‌های راه ۱۱۹
دوره‌ها ۱۴
اشتراک‌ها ۱۷۹۱۴
عنوان:

‫بررسی دو نکته (ترفند) کاربردی در SQL Server


نویسنده: محمد رجبی
تاریخ: ۱۳۹۳/۰۲/۰۳ ۱۶:۲۵
آدرس: www.dntips.ir

1- اندازه گیری تعداد Transaction‌ها در واحد زمان روی یک Database خاص در SQL Server 

جهت بدست آوردن تعداد Transaction‌ها در واحد زمان( Transactions Per Second ) روی یک Database خاص در یک سیستم عملیاتی، جهت ارتقاء سخت افزاری ، تست فشار و ... می‌توانید از یک DMV با نام sys.dm_os_performance_counters به طریق زیر استفاده نمائید: 
declare @cntr_value bigint

Select @cntr_value=cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

/* ایجاد یک تاخیر مثلاً یک ثانیه */
waitfor delay '00:00:01'

Select cntr_value -@cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'
View معرفی شده تمامی شمارنده‌های عملکردی را برای یک Instance خاص شامل می‌شود، ستون instance_name  برابر نام بانک اطلاعاتی مورد نظر می‌باشد.

2- sys.sp_MSforeachtable 

از رویه‌های ذخیره شده UnDocumented در SQL Server می‌باشد و این قابلیت را دارا است که برای هر یک از جداول موجود در  یک بانک اطلاعاتی، یک رویه‌ای را اجرا کند. برای مثال با استفاده از دستور زیر، می‌توانید تعداد سطرها، اندازه‌ی داده‌ها و ایندکس‌های یک جدول را بدست آورید

EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';
به عنوان یک مثال کاربردی، با اجرای دستور زیر می‌توان جداول بانک اطلاعاتی مورد نظرتان را از لحاظ معیارهایی که پیشتر ذکر آن رفت، مورد بررسی قرار دهید.
 USE [AdventureWorksDW2008R2]
GO

CREATE TABLE #TableSpaceUsed(
[name] [nvarchar](120) NULL,
[rows] [nvarchar](120) NULL,
[reserved] [nvarchar](120) NULL,
[data] [nvarchar](120) NULL,
[index_size] [nvarchar](120) NULL,
[unused] [nvarchar](120) NULL
) ON [PRIMARY]

Insert Into #TableSpaceUsed
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

Select * from #TableSpaceUsed
Order by CAST([rows] as int) desc

Drop table #TableSpaceUsed
خروجی مثال فوق به شکل زیر است.


مطالب مشابه

نظرات

  • محمد رجبی در ۱۳۹۳/۰۶/۱۸ ۱۲:۵۴
    یک مورد استفاده کاربردی، از رویه sp_MSforeachtable به هنگام تجمیع بانک‌های اطلاعاتی است (برای مثال تجمیع دو DB کوچک در یک DB بزرگتر). برای این منظور می‌توان در ابتدا تمامی Constraint‌های جداول را موقتاً غیر فعال کرد، عملیات Load داده در جداول را انجام داد و مجدداً آنها را فعال نمود.
    چنانچه از SSIS Package برای اینکار استفاده شود، با فرض اینکه در مرحله قبل Schema تمامی اشیاء بانک منتقل شده است، Control Flow این Package شامل Step‌های زیر میباشد:
    گام اول - غیر فعال کردن تمامی Constraint‌های جداول
    برای این منظور از Object موسوم به Execute SQL Task به این صورت استفاده میشود که در بخش SQL Statement دستور زیر نوشته شود:
     exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    گام دوم - انتقال داده‌ها به شکل Data Only
    برای این منظور از Object موسوم به Transfer SQL Server Objects Task به صورت زیر استفاده میشود :
    در بخش Object تغییرات زیر اعمال شود:
    - در قسمت Destination ، پروپرتی CopyData با مقدار True و ExistingData با مقدار Append تنظیم شود.
    - در قسمت Destination Copy & Option ، پروپرتی CopyAllTables در بخش ObjectsToCopy با مقدارTrue تنظیم شود.
     
    گام سوم - فعال کردن مجدد Constraint‌های جداول 
    برای این منظور از Object موسوم به Execute SQL Task به صورت زیر استفاده میشود :
    در بخش SQL Statement دستور زیر نوشته شود:
     exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    نکته: در صورت وجود Trigger برای جداول بانک اطلاعاتی نیز می‌توان به همین شکل عمل نمود. (ابتدا آنها را غیر فعال و مجدا فعال کرد)

    برای مثال شکل SSIS Package فوق به صورت تصویر فوق است.