نحوه بررسی Performance و کارایی Query ها و دیتابیس

Printable View

چهارشنبه 07 تیر 1391, 14:24 عصر
حمیدرضاصادقیان

نحوه بررسی Performance و کارایی Query ها و دیتابیس

سلام دوستان.
به زودی سلسله مقالاتی در مورد اینکه به چه شکل و از کجا باید بررسی دیتابیس و Performance یک دیتابیس رو تحلیل کرد و ایراداتشو پیدا کرد و رفع نمود ، ارائه خواهم کرد.
به زودی فصل اول اون رو قرار خواهم داد.

با تشکر.
چهارشنبه 07 تیر 1391, 14:37 عصر
حمیدرضاصادقیان

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

در بررسی Performance ما باید چند مورد رو مورد بررسی قرار بدیم:
1- سخت افزار Server
2- سخت افزار Client
3- تنظیمات اصلی خود SQL Server
4- پهنای باند شبکه
5- ساختار اصلی خود Database
6- بررسی Tempdb Database
7- بررسی Query ها، Stored Procedure ها ، View ها ،Function ها و Trigger ها.
8- بررسی نرم افزار و قسمت های مشکل دار برنامه که دچار کندی می شوند.
9- استفاده از Profiler و Activity Monitor.
10- بررسی ساختار جداول و Index ها.

که در پستهای آینده تک تک موارد رو با جزئیات کامل بررسی خواهم کرد.
چهارشنبه 14 تیر 1391, 15:15 عصر
حمیدرضاصادقیان

2 ضمیمه

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

مقدمه :
امروزه با افزایش نرم افزارهای تحت بانک های اطلاعاتی و پا به سن گذاشتن آنها ، یکی از مسائل مهم و دغدغه های شرکتها و سازمانهای بزرگ و کوچک ، نگهداری و مراقبت از بانک های اطلاعاتی هست.
در این زمینه یکی از اساسی ترین مباحث پیچیده و گسترده ، Performance Tuning یا بهبود و میزان سازی کارایی بانک های اطلاعاتی هست.
در این زمینه راهکارها و روشهای گوناگونی برای بررسی مشکل سرعت ، ترافیک شبکه ، مشکلات همزمانی و غیره تجزیه و تحلیل خواهند شد تا با ابزارهای گوناگون و تکنیکهای مختلف ، سبب کاهش مشکلات سرعت و بازدهی بانک های اطلاعاتی باشیم.
در سلسله مباحثی که مطرح خواهد شد ، ابزارهای گوناگون بررسی شده و روشهای گوناگون برای بررسی مشکلات کارایی سیستم مطرح و به تفصیل توضیح داده خواهد شد. امید است در پایان این سلسله مطالب آموزشی شاهد افزایش کارایی بانک های اطلاعاتی و افزایش عملکرد بهتر برنامه نویسان بانک های اطلاعاتی باشیم.

درس اول :
آشنایی با ساختار فایلهای Data.
هر دیتابیس حداقل شامل 2 فایل و در بعضی موارد فایلهای بیشتر می باشد.
به طور معمول هر دیتابیس شامل یک MDF (فایل اصلی اطلاعات) و یک LDF (فایل LOG) هست که بعضی مواقع میتوان فایلهای Data بیشتری با پسوند NDF به دیتابیس اضافه کرد.همچنین میتوان فایلهای Log بیشتری نیز اضافه کرد که معمولا کاربردی ندارند.
هر فایل MDF از یک سری PageDataو Index Page تشکیل شده است.
SQL Server 13 نوع از disk pages ها رو به کار میبرد.
Data pages، دو نوع از LOB pages ، row-overflow pages ، index pages ، Page Free Space(PFS) ، Global Allocation Map و Shared Global Allocation Map (GAM) و (SGAM)، Index Allocation Map(IAM)، Bulk Changed Map(BCM) و Differential Changed Map(DCM) ، File Header Page ، Boot Page .
تمام اطلاعات کاربر روی data یا LOB Page ها ذخیره می شوند و تمام ردیفهای ایندکسها بر روی Index Page ها ذخیره می شود. PFS Page ها اطلاعات page های قابل دسترس در دیتابیس را برای داده های جدید نگهداری میکنند. Allocation Page ها (GAM,SGAM,IAM) اطلاعات مابقی Page ها رانگهداری می کنند.اینها شامل هیچ ردیفی از دیتابیس نیستند و فقط برای کارهای داخلی SQL Server به کار می روند.
BCM , DCM برای کارآمد عمل کردن Backup,Restore به کار می روند.
حال به بررسی Data Page ها می پردازیم

Data Pages :
ساختاری هست که شامل اطلاعاتی می باشد که کاربر وارد جداول دیتابیس می کند.در SQL Server سه نوع گوناگون از data page ها داریم که هرکدام از آنها داده ها را به شیوه های گوناگون ذخیره میکنند که شامل in-row data , overflow-data , LOB ها هستند.
Data Page ها یک حجم ثابت 8KB یا 8192 bytes دارند که شامل 3 قسمت اصلی
Page Header , Data Row , Row Offset Array هست.

ضمیمه 89212

Page Header :
همانطور که در تصویر بالا ملاحظه میکنید ، 96 بایت اول هر Data Page شامل Page Header هست و مابقی به Data row, row offset array اختصاص می یابد.
در جدول زیر اطلاعات موجود در Page Headerرا به ما نمایش میدهد.

ضمیمه 89213

ادامه دارد...

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

ادامه درس اول

ردیفهای اطلاعات در In-Row Data:

در ادامه Page Header ، ناحیه ای هست که داده ها درون آن ذخیره می شوند. بیشترین مقدار یک ردیف واحد اطلاعاتی برای ذخیره سازی در In-Row برابر 8060 byte می باشد.تعداد ردیفهایی که میتوانند بر روی یک Page ذخیره شوند وابستگی زیادی به ساختار جداول و نوع داده هایی که میخواهند ذخیره شوند دارند.به عنوان مثال ، یک جدول با فیلدهایی با طول ثابت(غیر متغیرهایی که با var شروع شده اند) بر روی هر Page تعداد ردیفهای ثابتی را ذخیره می کنند، در صورتی که داده های با طول متغیر ، بسته به میزان داده های وارد شده می توانند درون یک Page ذخیره شوند.نگهداری داده های با طول کمتر، اجازه ذخیره سازی ردیفهای بیشتری بر روی یک Page را می دهد که باعث کاهش I/O شده و منجربه قرار گرفتن تعداد Page های بیشتری در Buffer می شود که در نهایت افزایش سرعت را به همراه دارد.

Row Offset Array :

شامل یک Block متشکل از واحدهای 2 byte می باشد.هر کدام از قسمتها شامل Offset شروع ردیف مورد نظر در Page می باشد.هر ردیف یک فضای 2 بایتی در این قسمت دارد .این آدرس درون هر ردیف ذخیره نمی شود چون فضای ذخیره سازی هر ردیف در Page کاهش پیدا می کند.
این Block شامل یک مرتب سازی منطقی نیز می باشد.به عنوان مثال ، اگر یک جدول شامل Clustered Index باشد ، SQL Server به ترتیب Clustered Index Key ،مقادیر رو در این جدول ذخیره خواهد کرد.این به این معنی نیست که ردیفهایی که درون Page ذخیره می شوند ،به صورت فیزیکی براساس Clustered Index Keyذخیره خواهند شد ، بلکه در slot 0 آدرس ردیف اول Clustered index key ذخیره می شود و در slot 1 آدرس ردیف بعدی و الی آخر. همانطور که در ادامه خواهیم دید ،هرردیف میتواند در هر جایی از یک Page به صورت فیزیکی ذخیره شود.

بررسی Data Page :

توسط دستور DBCC Page میتوان محتویات یک Page از قبیل Page Header ، Data rows و row offset array رو برای یک Page در دیتابیس ملاحظه کرد.فقط یک کاربر با دسترسی Administrator یا همان System Administrator میتواند این دستور را اجرا کند.
این دستور Undocumented هست و توضیحات آن در Books Online نمی باشد .در ادامه این دستور کامل توضیح داده خواهد شد.
  

DBCC PAGE ({dbid | dbname}, filenum, pagenum[, printopt])
  
نتیجه این دستور به کاربر ارسال خواهد شد.

نکته مهم : توسط دستور
 DBCC TraceOn(3604)
برای SQL Server مشخص میکنیم که نتیجه را به کاربر ارسال کند.بدون تنظیم این Flag هیچ نتیجه ای به کاربر ارسال نخواهد شد.

توضیحات دستور DBCC page :

Dbid: شامل Id دیتابیسی که Page درون آن قرار دارد.
Dbname : نام دیتابیس مورد نظر که Page درآن قرار دارد.
Filenum : شماره فایل مورد نظر که Pageدرآن قرار دارد.
Pagenum : شماره Page مورد نظر درون فایل.
Printopt :

0 (به صورت پیش فرض) : چاپ buffer header و page header 1: چاپ buffer header ، page header ، و هر ردیف به صورت جداگانه و row offset table .
2: چاپ buffer , page header ، کل Page و row offset table .
3: چاپ buffer , page header ، هر ردیف به صورت جداگانه و row offset table و هر ردیف با اطلاعات موجود درآن به صورت جداگانه مشخص می شود.
مثال :

با اجرای این دستور ، نتیجه زیر را به شما نمایش میدهد.
  

Dbcc traceon(3604)
  Go
  Dbcc page(pubs,1,40,1)
  Go
  
نتیجه :
PAGE: (1:40)





BUFFER:





BUF @0x045E4AC0



bpage = 0x14AB8000                   bhash = 0x00000000                   bpageno = (1:40)

bdbid = 48                           breferences = 0                      bcputicks = 0

bsampleCount = 0                     bUse1 = 11711                        bstat = 0xc00109

blog = 0x59bb7979                    bnext = 0x00000000                   



PAGE HEADER:





Page @0x14AB8000



m_pageId = (1:40)                    m_headerVersion = 1                  m_type = 1

m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x0

m_objId (AllocUnitId.idObj) = 93     m_indexId (AllocUnitId.idInd) = 1    Metadata: AllocUnitId = 281474982805504

Metadata: PartitionId = 281474982805504                                   Metadata: IndexId = 1

Metadata: ObjectId = 93              m_prevPage = (0:0)                   m_nextPage = (0:0)

pminlen = 28                         m_slotCnt = 18                       m_freeCnt = 7502

m_freeData = 654                     m_reservedCnt = 0                    m_lsn = (14:111:154)

m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0

m_tornBits = 0                       



Allocation Status



GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED            



DATA:





Slot 0, Offset 0x231, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C231



00000000:   10001c00 04010000 01000000 66000000 †............f...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 1, Offset 0x250, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C250



00000000:   10001c00 05010000 01000000 1f000000 †................         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 2, Offset 0x212, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C212



00000000:   10001c00 06010000 01000000 07010000 †................         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 3, Offset 0x60, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C060



00000000:   10001c00 2e010000 01000000 2d010000 †............-...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 4, Offset 0x1d4, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C1D4



00000000:   10001c00 49010000 01000000 7c010000 †....I.......|...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 5, Offset 0x7f, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C07F



00000000:   10001c00 69010000 01000000 2c010000 †....i.......,...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 6, Offset 0x9e, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C09E



00000000:   10001c00 6a010000 01000000 0f000000 †....j...........         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 7, Offset 0xbd, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C0BD



00000000:   10001c00 6b010000 01000000 70000000 †....k.......p...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 8, Offset 0xdc, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C0DC



00000000:   10001c00 6c010000 01000000 71000000 †....l.......q...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 9, Offset 0xfb, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C0FB



00000000:   10001c00 6d010000 01000000 2e010000 †....m...........         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 10, Offset 0x11a, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C11A



00000000:   10001c00 6e010000 01000000 71000000 †....n.......q...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 11, Offset 0x139, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C139



00000000:   10001c00 6f010000 01000000 71000000 †....o.......q...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 12, Offset 0x158, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C158



00000000:   10001c00 70010000 01000000 78000000 †....p.......x...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 13, Offset 0x177, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C177



00000000:   10001c00 71010000 01000000 78000000 †....q.......x...         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 14, Offset 0x196, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C196



00000000:   10001c00 72010000 01000000 0f000000 †....r...........         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 15, Offset 0x1b5, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C1B5



00000000:   10001c00 73010000 01000000 0f000000 †....s...........         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 16, Offset 0x26f, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C26F



00000000:   10001c00 74010000 01000000 10000000 †....t...........         

00000010:   00000000 01000000 01000000 070040††††..............@          



Slot 17, Offset 0x1f3, Length 31, DumpStyle BYTE



Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31



Memory Dump @0x5E94C1F3



00000000:   10001c00 7c010000 01000000 7d010000 †....|.......}...         

00000010:   00000000 00000000 ffffff7f 070040††††........ÿÿÿ...@          



OFFSET TABLE:



Row - Offset                         

17 (0x11) - 499 (0x1f3)              

16 (0x10) - 623 (0x26f)              

15 (0xf) - 437 (0x1b5)               

14 (0xe) - 406 (0x196)               

13 (0xd) - 375 (0x177)               

12 (0xc) - 344 (0x158)               

11 (0xb) - 313 (0x139)               

10 (0xa) - 282 (0x11a)               

9 (0x9) - 251 (0xfb)                 

8 (0x8) - 220 (0xdc)                 

7 (0x7) - 189 (0xbd)                 

6 (0x6) - 158 (0x9e)                 

5 (0x5) - 127 (0x7f)                 

4 (0x4) - 468 (0x1d4)                

3 (0x3) - 96 (0x60)                  

2 (0x2) - 530 (0x212)                

1 (0x1) - 592 (0x250)                

0 (0x0) - 561 (0x231)                
همانطور که در نتیجه دستور ملاحظه می کنید ، شامل 4 قسمت می باشد Buffer Header ، Page Header ، Data و Row Offset Table .
بخش Buffer شامل اطلاعاتی در مورد Buffer بدست آمده از Page است. منظور از Buffer در اینجا ، ساختار داخلی حافظه که یک Page رو مدیریت میکند می باشد و اطلاعات این بخش شامل زمانی هست که Page درون حافظه یا RAM فراخوانی می شود.
Page Header شامل اطلاعات کامل درون Header بدست آمده از Page می باشد.
بخش Data شامل اطلاعات مربوط به هر ردیف می باشد.
زمانی که DBCC Page فیلد Printopt با مقدار 1 یا 3 اجرا می شود ، موقعیت slot هر ردیف ، Offset ردیف مورد نظر بر روی Page و طول ردیف را مشخص میکند.
ردیف اطلاعات به 3 بخش تقسیم می شود .
ستون سمت چپ موقعیت بایتی هر ردیف اطلاعات را نمایش میدهد.
ستون وسط داده های حقیقی ذخیره شده بر روی page است که شامل 4 ستون 8 رقمی هگزادسیمال می باشد.ستون سمت راست شامل کارکترهای ASCII وابسته به ستون وسط یا اطلاعات اصلی می باشد.فقط کارکترهای اطلاعات در این ستون قابل خواندن میباشد ، اگرچه ممکن است بعضی از کارکترها نیز نشان داده شود.
بخش OFFSET Table اطلاعات مربوط به row offset array در انتهای page را به ما نمایش میدهد. در نتیجه فوق ملاحظه میکنید که این Page دارای 18 ردیف بوده که اولین ردیف آن (slot 0) از Offset 561 (0x231) شروع شده است. در صورتی که اولین ردیف به صورت فیزیکی مربوط به slot 3 می باشد که با Offset 96 (0x60) ذخیره شده است.

ادامه دارد....

دوشنبه 06 شهریور 1391, 08:11 صبح
حمیدرضاصادقیان

1 ضمیمه

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس
خوب در جلسات قبل با ساختار داخلی فایل MDF آشنا شدیم و متوجه شدیم به چه طریق میتوان داده های آنرا مشاهده کرد.

حال در این جلسه میخواهیم بررسی کنیم ببینیم در یک SQL Server که Database های مختلفی وجود دارد ، عمده مشکل مربوط به کدام Database می باشد و به صورت سلسله مراتبی تا کشف Query های مشکل دار Database مورد نظر رو بررسی کنیم.

با اجرای Script ضمیمه شده، دیتابیس Performance به همراه یک سری از داده های تستی ساخته میشود.

در این دیتابیس ، جدول اصلی Orders می باشد که شامل 1000000 رکورد می باشد که دارای 4 سال اطلاعات از سال 2005 می باشد.

جدول Customer شامل 20000 رکورد می باشد و جدول Employee شامل 500 رکورد و جدول Shippers نیز شامل 5 رکورد می باشد.

توجه کنید که فیلدهای Order Date ، Customers ID ، Employee Id و Shipper ID در جدول Orders توسط توابع Random ایجاد شده که ممکن است در سیستم شما دارای مقادیر متفاوت باشد.

جدول Nums یک جدول کمکی عددی هست که شامل یک فیلد N با نوع Integer از 1 تا 1000000 می باشد.

متدولوژی Tuning

هنگامی که سیستم شما دچار مشکل می شود، از کجا باید برای پیدا کردن ایراد و رفع آن شروع کرد؟

کاربران مشکل بازدهی رو براساس میزان انتظار متوجه میشوند. یک درخواست ارسال میکنند و تا زمانی که جواب را دریافت کنند منتظر می مانند. اگر بیشتر از 3 ثانیه طول بکشد بیان میکنند که سیستم مشکل دارد.

کاربران در مورد تعداد دستورات منتظر در صف ، یا اینکه Cache hit ratio چیست اطلاعی ندارند همچنین در مورد Blocking اطلاعی ندارند و تنها چیزی که میدانند میزان انتظار هست که باید بحث بررسی بازدهی از همین نقطه شروع شود.

مراحلی که ما برای بازدهی یک سیستم بررسی خواهیم کرد به صورت بالا-پایین می باشد یعنی ابتدا در سطح Instance مقدار wait بررسی خواهد شد و با یک سری مراحل نزولی به بررسی Process ها و Component ها که باعث ایجاد وقفه های بزرگ در سیستم می شوند خواهیم پرداخت.

مراحل زیر به ترتیب بررسی خواهند شد:
1. تحلیل مدت زمان انتظار در سطح Instance
2. ارتباط انتظارها با صف ها
3. مشخص کردن یک مجموعه از عملیات
4. رفتن به سطح Database/File
5. رفتن به سطح Process
6. بهینه کردن Query /Index
در نسخه 2008 ابزاری به نام Data Collector هست که مجموعه ای از داده ها از قبیل Performance و مابقی را از منابع مختلف جمع آوری کرده و در یک مجموعه به نام Management Data Warehouse ذخیره میکند.

Data Collector سه ابزار برای جمع آوری داده های سیستمی را نصب میکند که اطلاعات Disk Usage ، Server Activityو Query Statistics information را جمع آوری و ذخیره میکند.

پلت فرم جدید Data Collection به شما این امکان را میدهد که به صورت خودکار مقادیر Performance و اطلاعات دیگر را جمع آوری کرده و با استفاده از گزارشات از قبل ایجاد شده ، نمودارهای گرافیکی ترسیم کنید.

برای اطلاعات بیشتر در مورد این ابزار میتوانید به Books Online قسمت System Data Collection Sets مراجعه بفرمائید.

ادامه دارد...
دوشنبه 06 شهریور 1391, 14:14 عصر
حمیدرضاصادقیان

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس
تجزیه و تحلیل زمان انتظار در سطح Instance

اولین مرحله از متدولوژی میزان سازی یا Tuning ، مشخص کردن این که کدام نوع از انتظار ، بیشترین مقدار را در سطح Instance به خود اختصاص داده است، می باشد.
توسط یک گزارش از(DMV(Dynamic Management View به نام sys.dm_os_wait_stats میتوان به نتیجه دلخواه رسید.
این DMV شامل بیش از 400 نوع انتظار مختلف می باشد.
با استفاده از Query زیر Wait های موجود در سیستم شما براساس نوع آن به شما نمایش داده خواهد شد.
```
   

 SELECT
         wait_type,
         Waiting_tasks_count,
         wait_time_ms,
         max_wait_time_ms,
         signal_wait_time_ms 
   FROM sys.dm_os_wait_stats dows
   ORDER BY wait_type;

   
```
مقادیر نمایش داده شده در این DMV از آخرین باری که SQL Server را Reset کرده ایم محاسبه میگردد.
اگر میخواهید مقادیر آنرا به صورت دستی صفر کنید میتوانید از دستور زیر استفاده کنید( البته الان این دستور را اجرا نکنید):
```
   

 DBCC SQLPERF('sys.Dm_os_wait_stats',CLEAR);

   
```
این DMV شامل فیلدهای زیر می باشد:
1. Wait_type
2. Waiting_tasks_count تعداد انتظارها بر روی این نوع انتظار(wait)
3. Wait_time_ms مجموع زمان انتظار برای این نوع انتظار ( شامل Signal_wait_time_ms)
4. Max_wait_time_ms
5. Signal_wait_time_ms تفاوت بین زمانی که یک waiting thread مشخص شده و هنگامی که شروع به کار کرده است.
توضیحات همه فیلدها مشخص و ساده می باشند ،فقط توضیح مختصری در مورد فیلد آخر ارائه خواهم کرد.
یک Thread یک وضعیت انتظار را زمانی ثبت می کند که منابع مورد نیاز آن در دسترس نباشد.
هنگامی که منابع آزاد شدند ، Waiting Thread علامت گذاری می گردد . هرچند ممکن است در آن لحظه CPU به دلیل پاسخ به Thread های دیگر مشغول باشد. فیلد signal_wait_time_ms مدت زمان بین لحظه شروع علامت گذاری Thread تا زمانی که بتواند به منابع CPU دست پیدا کرده و از آن استفاده کند را مشخص میکند. اگر این مقدار زیاد باشد میتوان حدس زد که ممکن است CPU دچار اشکال شده باشد.

ادامه دارد...
دوشنبه 13 شهریور 1391, 14:59 عصر
حمیدرضاصادقیان

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

در میان انواع گونان انتظارها، شما انتظارهای مختلف مربوط به
Locks,Latches,I/O(I/O Latchs),parallelism,transaction log,memory,compilations,OLEDB(Linked server and etc)

رو مشاهده خواهید کرد.
ممکن است بخواهید از بعضی از نوع ها چشم پوشی کنید مانند sleep waits که زمانی رخ میدهد یک thread به تعویق افتاده وکاری نمیکند ،یا queue wait که یک worker آزاد بوده ومنتظر هست یک عملیات به آن اختصاص داده شود، و انواع دیگر که در BOL نیز توضیح داده شده و بیانگر ایرادی نیست.

مانند CLR_AUTO_EVENT ، REQUEST_FOR_DEADLOCK_SEARCH و ... .

I/O به طور نمونه پرهزینه ترین منابع درگیر با تغییرات داده ها هستند.

همچنین اگر query ها یا Index ها درست طراحی نشده و Tune نشده باشند ، نتیجه نیز I/O زیادی در برخواهد داشت.
همچنین وقتی کاربر در مورد قدرت فکر میکند تنها به CPU و Ram سیستم توجه میکند و معمولا توجه مناسبی به زیر سیستمهای I/O ندارند . سیستمهای Database به زیرسیستمهای I/O قدرتمندی نیاز دارد.
مقادیر زیاد در wait های وابسته به Network مانند ASYNC_NETWORK_IO ممکن است بیانگر مشکل شبکه باشد ، اگر چه ممکن هست نشان دهد که Client ، داده های ارسال شده توسط SQL Server را با سرعت کافی استفاده نمیکند و سرعت پایینی دارد.

استفاده زیاد از Query های تک کاره به جای استفاده از Stored Procedure ها یا دستورات از پیش آماده شده ، ممکن است سبب افزایش بی رویه حافظه در Plan های Query های تک بعدی شود درنتیجه باعث افزایش مقدار در نوع CMEMTHREAD می شود و هنگامی رخ میدهد که یک عملیات در قسمت thread-safe memory object به انتظار می باشد.
همچنین ممکن است شما با Parallel Query plan هایی برخورد کنید که از Thread های زیادی استفاده میکنند.در نتیجه ممکن است مقدار زیادی در CXPACKET باشد که سبب می شود Thread های زیادی منتظر باشند تا مابقی Thread ها به کار خود خاتمه دهند.البته توجه کنید که ممکن است مقدار داخل CXPACKET فقط یک نشانه از رخدادهای دیگر مانند I/O های زیاد به دلیل کمبود استفاده از Index های مهم باشد که باعث افزایش مقدار انتظارهای مربوط به I/O می شود.
سیستمهای OLTP دارای مقادیر زیادی تغییرات بر روی داده ها در بخش های کوچک هستند، و Transaction Log ها بعضی مواقع بر روی سیستمها یک تنگنا ایجادمی کند. زمانی که SQL Server نتواند به سرعت بر روی فایل LOG بنویسد مقدار WRITELOG افزایش پیدا میکند.

دیتابیس Tempdb نیز میتواند یک تنگنای جدی باشد به خاطر اینکه تمام جداول موقت ، خواه جداول مفهومی ایجاد شده توسط Execution Plan یا صریحا باشد، در داخل دیتابیس Tempdb ایجاد می گردد.مشکلات بازدهی بر روی Tempdb باعث افزایش I/O بر روی سیستم خواهد شد.مقادیر زیاد در latch wait ها مانند PAGE_LATCH_UP ممکن است باعث تداخل در قسمتهای داخلی GAM,SGAM,IAM و PFSPage ها باشد. ممکن است به دلیل تخصیص pageاضافی برای جداول Temp باشد ، یا درج رکوردهای زیاد در Heap ، و دلایل دیگر باشد.

ادامه دارد....
شنبه 08 مهر 1391, 21:22 عصر
حمیدرضاصادقیان

1 ضمیمه

Activity Monitor

در این بخش در مورد Activity Monitor توضیحاتی نوشتم که به روند پیدا کردن مشکلات در کندی SQL Server کمک شایانی خواهد کرد.
پنج شنبه 14 فروردین 1393, 20:05 عصر
حمیدرضاصادقیان

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

سلام.
یکی از ابزارهای کاربردی برای Monitor کردن SQL Server استفاده از Management Data Warehouse یا MDW هست که به نام Data Collector مطرح هست.
در لینک زیر مقاله ای جهت راه اندازی آن آماده کردم.
در مباحث بعدی نیز نحوه گزارش گیری آن را ارائه خواهم کزد.

نحوه راه اندازی Data Collector
یک شنبه 24 فروردین 1393, 17:47 عصر
حمیدرضاصادقیان

نقل قول: نحوه بررسی Performance و کارایی Query ها و دیتابیس

سلام.
در اینجا نحوه گزارش گیری از Data Collector رو توضیح دادم.

موفق باشید