SQL Server Administration

[Reza_Yarahmadi]

مفاهيم Tuning:
زمانيكه SQL Server را نصب مي نماييد SQL Server با پيش فرضهاي مشخصي نصب مي گردد.
اين تنظيمات براي پايگاه داده هايي كه از اندازه زيادي برخوردار هستند ، مناسب نمي باشد و براي بالا بردن سطح كارايي دیتایبس بايستي تنظيمات لازم صورت بگيرد.
اين تنظيمات براي هر SQL Server يكسان نمي باشد و بايستي هر Server را بنا به نياز ها تنظيم كرد.
در نهايت انجام تنظيمات انتظار می رود كارايي دیتایبس متناسب شود. به عبارت ديگر پس از انجام تنظيمات انتظار می رود

1. SQL Server در مقابل در خواست هاي كاربران ، سريعا پاسخدهي كند.
2. امنيت data ها را تضمين كند.
3. در برابر استفاده از منابع سخت افزاري طوري مصرف كند تا مجموعه Server و Client ها كار خود را به نحو مطلوبي انجام دهند .

معمولا در Tuning يك SQL Server نقاط زير به عنوان گلوگاه هاي سرعت سيستم شناسايي مي شوند

System Resources

1. Disk
2. RAM
3. Processor

Network
Operating system

1. Virtual Memory
2. Processor Scheduling
3. Memory Management
4. AWE

Database Tuning
Client Tuning

براي تنظيم اين 5 نقطه معمولا با استفاده از ابزارهاي Tuning بايد آماري از كاركرد آنها تهيه كرد و بعد تنظيمات لازم را انجام داد. براي بررسي دقيق اين نفاط بايستي در زمان Peak عمليات آنها را مورد ارزيابي قرار داد.


بطور خلاصه مي توان اتفاقاتي كه باعث كندي SQL Server مي شوند را با عناوين زير شمرد :

1. در نقاط كه عمليات I/O زياد روي حجم زيادي از اطلاعات بارها تكرار شود.
2. تعداد كاربران متناسب با فضاهاي كاري در اختيارشان از Server نباشند و كاربران در استفاده از منابع Server با نقصان روبرو شوند.
3. ترافيك اطلاعات در شبكه به حدي زياد باشد تا ارتباط با Server با كندي صورت بگيرد . در اين حالت Virus يكي از عواملي است كه ترافيك شبكه را بالا برده و امكان ارتباط را سخت تر مي كند.
4. تخصيص منابع سخت افزاري در Server متناسب با نياز هاي اجراي برنامه ها تهيه نشده باشد.
5. پياده سازي سيستم ، با كيفيت متناسب با كارايي مناسب توليد نشده باشد كه در اين حالت بايستي سيستم اصلاح شود.

ابزارهاي Tuning
بعد از مشخص شدن مفهوم Tuningبايستي با كمك ابزارهاي Tuning عمل Tuning را انجام دهيم :

ابزارهاي Tuning در سيستم عامل
Windows Monitor

با استفاده از اين ابزار مي توان عملكرد

1. Windows در مورد استفاده از منابع Server مانند Disk ها و Memeory و Processor و Lan و....
2. رفتارهاي SQL Server در مورد استفاده از منابع Server

را مورد بررسي قرار داد.


Performance Log and Alert
براي بررسي عملكرد سرويس Notification Service از اين ابزار استفاده مي شود.



Task Manager
ساده ترين ابراز براي بررسي لحظه به لحظه استفاده از Ram و Processor و ميزان Page File و همچنين رفتار هاي Lan مورد بررسي قرار مي گيرد .

ابزارهاي Tuning در SQL Server
SQL Profiler
ابزاري براي بررسي عملكرد SQL Server و در واقع عملكرد Service در ازاي Request ارسالي از Client ها. اين ابزار را كاملا تشريح خواهيم كرد.


SQL Server Management Studio Activity Monitor
در این قسمت مي توان از كاربران متصل به SQL Server اطلاعاتي شامل SPID ، زمان هاي Login ، آخرين Statement ارسالي به Server و مديريت Lock شدن User ها را مورد بررسي قرار داد.


SQL Server Management Studio Show Plan
براي بررسي منابع مصرف شده در اجراي يك Query مورد استفاده برنامه نويسان و كسانيكه مي خواهند Query هاي برنامه را Tune كنند مورد استفاده قرار مي گيرد.

Stored Procedure
تعدادي Procedure سيتسمی براي بررسي شرايط دیتابیس وجود دارد كه با استفاده از آنها مي توان آماري مناسب تهيه كرد و Tuning را انجام داد.

DBCC
مجموعه ايي از دستورات مي باشد كه با كمك آنها مي توان از دیتابیس گزارشاتي گرفت و دیتابیس را Tune كرد. اين دستورات بعضا قابليت اصلاح اشكالات را با اعمال Switch هايي دارند.

معرفي Profiler
يكي از بهترين ابزارها براي بررسي دستوراتي كه سرويس هاي SQL Server دريافت مي نمايند.
هر نوع دستور SQL كه به تنهايي يا از داخل يك برنامه يا از طرف يك روال ذخيره شده (Stroed Procedure) و يا هر جاي ديگر اجرا شود توسط اين برنامه شناسايي و ثبت مي‌شود. سپس برنامه مذكور عمل تجزيه وتحليل خود را بر روي اين دستور SQL انجام داده و نتايج آن را به مدير سيستم نمايش مي‌دهد

اين ابزار براي بررسي برنامه هايي كه Source آنها بسته شده است و امكان Trace برنامه مثلا در سايت مشتري وجود ندارد ، مورد استفاده قرار مي گيرد.
از كاربرهاي اين برنامه مي توان به موارد زير اشاره كرد :

1. بهترين ابزار براي بررسي كارايي دستوراتي است كه از جانب كاربران به SQL Server ارسال مي شود.
2. با كمك اين ابزار مي توان شناسه SPID كاربر ، ميزان زمان مصرفي براي پاسخدهي به آن درخواست ، ميزان مصرف از منابعي مانند I/O و CPU را بررسي كرد.

نحوه كار برنامه Profiler
برنامه Profiler ليستي از رخدادهايي را كه قادر به تعقيب آن‌ها است در اختيار كاربر قرار مي‌دهد. اين رخدادها پس از انتخاب كاربر در درون يك صف (Queue) قرار گرفته و هرگاه يكي از رخدادها به وقوع بپيوندد، Profiler شرح كاملي از جزييات آن را در يك فايل جهت گزارش به مدير سيستم ثبت مي‌كند. اين عمليات تعقيب كه در Profiler به آن Trace گفته مي‌شود كاملاً توسط كاربر قابل تنظيم است.
رخدادهاي قابل تعقيب توسط پروفايلر به انواع مختلفي تقسيم‌بندي مي‌شوند كه در قسمت Events از منوي WewTrace يعني زماني كه كاربر قصد تعريف يك تعقيب جديد را دارد، مشاهده مي‌شوند.


1 - Cursors
اين مجموعه رخدادهاي مربوط به اتفاقاتي است كه باعث ايجاد شدن، مورد استفاده قرار گرفتن و حذف شدن يك دسته ركوردهاي اطلاعاتي از يك يا چند جدول مي‌شود. همان‌طور كه مي‌دانيد در SQL Server مي‌توان با استفاده از دستور SELECT تعدادي از جداول بانك اطلاعاتي را با هم لينك كرده و مجموعه ركوردهاي اطلاعاتي مربوطه را در يك گروه به نام كرسر قرار داد (همان چيزي كه در زبان‌هاي برنامه‌نويسي مثل ويژوال بيسيك به آن Recordset گفته مي‌شود) هر عملي كه باعث ايجاد شدن يا هر نوع عمليات ديگر بر روي يك كرسر شود مي‌تواند مورد تعقيب پروفايلر قرار گرفته و ثبت شود.

2 - Data Base
اين مجموعه از رخدادها مربوط به فايل‌هاي داده‌اي يك بانك اطلاعاتي است. هر تغييري كه در ساير فايل‌هاي داده‌اي و فايل‌هاي لاگ يك بانك ايجاد شود در اين مجموعه قرار مي‌گيرد.

3 - Errors and Warning
پيام‌هاي خطا و هشدار كه در زمان اجراي دستورات SQL يا در زمان كامپايل و اجراي SPها و يا Triggerها به كاربر داده مي‌شود و همچنين خطاهاي مربوط به OLE DB در اين گروه قرار مي‌گيرد.

4 - Locks
اين گروه از رخدادها، بيشتر زماني مورد استفاده قرار مي‌گيرد كه يك برنامه كاربردي در قفل كردن و آزاد كردن ركوردهاي جداول بانك اطلاعاتي دچار ضعف و اشتباه مي‌شود.
همان‌طور كه مي‌دانيد بسياري از برنامه‌هاي كاربردي در مقاطع زماني خاص اقدام به قفل كردن يك يا چند جدول اطلاعاتي مي‌كنند كه اين كار و همچنين آزاد كردن آن جداول بايد با حساسيت و دقت خاصي انجام شود تا در كار بقيه كاربران اخلال ايجاد نكند اما متأسفانه بسياري از اين نوع برنامه‌ها خصوصاً برنامه‌هايي كه قدمت چنداني ندارند اغلب از اين لحاظ دچار بي‌دقتي و ضعف زيادي هستند.

5 - Scans
هر عملي كه در حافظه اصلي تخصيص داده شده به SQL server قابل دستيابي باشد در اين دسته قرار مي‌گيرد. بخصوص عمليات مربوط به Cache كه در داخل موتور بانك اطلاعاتي انجام مي‌شود جزو اين دسته محسوب مي‌شوند.

6 - Stored procedures
شامل كليه وقايعي كه ممكن است براي يك روال رخ دهد مي‌باشد. كامپايل، فراخواني، شروع اجرا، وضعيت در حال اجرا، پايان اجرا، همگي از جمله رخدادهاي قابل وقوع در اين دسته مي‌باشند.

7 - TSQL
اين نوع رخدادها شامل كليه وقايعي است كه باعث اجراي هر يك از دستورات زبان TSQL به صورت تكي يا دسته‌اي (Batch) مي‌شود. دستورات SELECT ، INSERT ، UPDATE ، DELETE و ... هر كدام آغاز و پاياني مشخص با نتايج معين در يك بانك اطلاعاتي دارند كه مي‌توانند به وسيله اين نوع رخداد مورد بررسي قرار گيرند.

8 - Transaction
در اين دسته، كليه وقايع مربوط به فرآيند(Transaction) از جمله شروع (BEGIN) تأييد (Commit) و بازگشت (Roll Back) قرار مي‌گيرند. هر فرآيند شامل چند دستور SQL مي‌باشد كه يا بايد همگي بدون اشكال اجرا شوند و يا اينكه هيچكدام اجرا نگردند.
اهميت فرآيند و استفاده مناسب از آن‌ها در يك بانك اطلاعاتي و برنامه كاربردي مربوط به آن جاي هيچ‌گونه ترديدي را براي وجود ابزاري جهت ثبت و مانيتورينگ وقايع باقي نمي‌گذارد. لذا اين دسته از رخدادها همانند رخدادهاي SQL يكي از پركاربردترين رخدادها قلمداد مي‌شوند.

9 - Session
اين دسته از وقايع شامل كليه رخدادهاي مربوط به اتصال كاربران به بانك اطلاعاتي (Login) و خروج از آن (Logout) يا قطع اتصال در اثر بروز هر عاملي (Disconnect) مي‌باشد و براي كنترل و رفع ايراد ورود و خروج كاربران به سيستم مورد استفاده قرار مي‌گيرد.


براي اجراي Profile از برنامه Management Studio و از منوی Tools گزینه SQL Server Profiler را انتخاب نمایید.
پس از اجراي اين برنامه بايد مواردي را كه مي خواهيد مورد بررسي قرار دهيد را مشخص نماييد و در مورد اين Trace يك Profile توليد نماييد. قبل از توليد اين Profile بايستي تمامي برنامه ها ابتدا به SQL Server متصل شوند و در واقع Server ايي را كه مي خواهيد مورد بررسي قرار دهيد را مشخص نماييد.

ضمیمه 85643

چنانچه بخواهيد از الگوهاي قديمي استفاده نماييد بصورت زير عمل كنيد

ضمیمه 85644

در Server هاي 2000 و 2005 و 2005Analysis Service مي توان الگوهاي زير را مشاهده كرد :
SP_Count : بررسي اجراي SP هايي كه به كرات اجرا مي شوند.
Standard : يك Trace معمولي و با جزييات استاندارد.
TSQL : بررسي TSQL هايي كه از جانب Client هاي ارسال مي شوند.
TSQL-Duration : بررسي زمان صرف شده براي اجراي يك TSQL
و برخي ديگر. در هر صورت براي اجراي يك رويه صحيح Tuning بايد با استفاده از Event Class هاي موجود يك Trace انجام داد.
در ايجاد يك Trace جديد مي توان نتيجه Trace را در يك File ذخیره کرد. براي ذخيره در فايل حتما حداكثر فضاي فايل را برابر با 640 قرار دهيد .
يا در يك جدول در دیتابیس مجزا ذخيره كرد.

در قسمت Event Class

ضمیمه 85645

براي بررسي يك Connection بايستي Filter اعمال كنيد در غير اين صورت تمامي درخواستهاي ارسالي به Server مورد بررسي قرار مي كيرد.
براي اعمال Filter بايستي :

ضمیمه 85646

پس از اجراي Profiler برنامه زير اجرا مي شود.

ضمیمه 85647

در ستونهاي Profiler موارد مهمي وجود دارد كه كاربر بايد آنها را بشناسد. از اين موارد مي توان به عناوين زير اشاره كرد :

• Application Name : نام Client ايي است كه به SQL Server متصل شده است
• CPU : ميزان زماني برحسب ميلي ثانيه كه اجراي Query بطول انجاميده است
• Read : تعداد دفعاتي كه عمليات خواند اطلاعات از روي ديسكها انجام ميشود را نشان مي دهد .
• Write : تعداد دفعاتي كه عمليات نوشتن اطلاعات از روي ديسكها انجام ميشود را نشان مي دهد .
• Duration : زمان مصرف شده براي پاسخدهي به درخواست ها مي باشد ( برحست ميلي ثانيه )
• SPID : شماره ايي بيشتراز 50 براي كاربران متصل به SQL Server ( يكي از بهترين اطلاعات براي Filter كردن )

در اين ميان تعدادي از Event Class هاي مهم را مورد بررسي قرار مي دهيم :
Database

1. Data File Auto Grow : ميزان رشد اتوماتيك Data File
2. Log File Auto Grow : ميزان رشد اتوماتيك Log File

Lock

1. Deadlock Graph : نمايي از Lock و Deadlock
2. Deadlock : كاربري كه deadlock گرفته است
3. Deadlock Chain : زنجيرهايي از كاربران شركت كننده Deadlock
4. Timeout : ميزان تحمل شيي Lock شده

Stored Procedure

1. Starting : زمان شروع Stored procedure
2. Stmt Starting : زمان شروع دستورات درون Stored procedure
3. Stmt Complete : زمان اجراي دستورات درون Stored procedure
4. Complete : زمان تكميل اجراي Stored Procedure