الانتقال من المتوسط - sql 2008

إنترودكتيون مع إصدار سكل سيرفر 2016 المزود بحزمة الخدمة سيرفيس باك 1 التكنولوجيا في الذاكرة كولومنستور متاحة الآن أيضا في إصدارات القياسية والويب وحتى إكسبريس و لوكالدب. بالإضافة إلى الاستفادة من 1 فقط كودباس للحفاظ على، وهذا التغيير في السياسة تصبح أيضا واضحة مساحة التخزين مساحة القرص بسبب ارتفاع البيانات الازدواجية ونسب الضغط، وأخيرا وليس آخرا، لها أيضا أداء الاستعلام مخصصة خطيرة معززة الفرق الرئيسي بين النكهات سكل هو مقدار وحدة المعالجة المركزية السلطة والذاكرة يتم تخصيص لمهام مثل (إعادة) بناء مبنى كولومنستور الفهرس. على سبيل المثال: مع الإصدار القياسي يتم استخدام جوهر واحد (بحد أقصى 100 وقت المعالج لعملية سكلسرفر) والاستعلام عن تسي يحدث بحد أقصى 2 وحدات المعالجة المركزية (MAXDOP2)، مقابل الاستفادة من كافة وحدات المعالجة المركزية المتوفرة في إنتيربريس إديتيون. بناء مؤشر متفاوت المسافات العمود (تسي) مع سكل سيرفر 2016 الإصدار القياسي: بناء تسي مع كل 4 النوى المتاحة مع سكل سيرفر 2016 إنتيربريس إديتيون: التوقيت الأساسي لتحميل 7.2 غيغابايت 60 مليون الصفوف من ملفات خط تبش واحد لا تظهر الكثير من الفرق بين النكهات عند إدخال السائبة البيانات مباشرة إلى جدول كومة أو جدول مع تسي يظهر الفرق عندما نقارن الوقت اللازم لبناء تسي على جدول كومة الذاكرة المؤقتة أو إعادة بناء تسي: لتلخيص، المطلق أسرع طريقة للحصول على البيانات المتوفرة في جدول مع "مؤشر العمود" متفاوت المسافات هو: تحميل في كومة بناء تسي بعد ذلك مع سكل 2016 إنت. إد. التحميل المباشر إلى تسي للجداول التي تحتوي على فهرس كولومنستور متفاوت المسافات تم إنشاؤه بالفعل تأكد من أن يتم البث مباشرة إلى مجموعات الصف المضغوط لزيادة الإنتاجية. من أجل القيام بذلك، يجب أن يكون حجم دفعة إدراج مساوية أو أكبر من 100K الصفوف (102400 أن تكون دقيقة). سيتم كتابة دفعات أصغر إلى جداول مخزن دلتا مضغوط أولا قبل أن يتم نقلها إلى توبل في شرائح مضغوط صف المجموعة النهائية، مما يعني سكل سيرفر لديه للمس البيانات مرتين: هناك خيارات مختلفة لتحميل البيانات وسنذهب أكثر من الأكثر استخداما منها، مثل الأمر إدراج السائبة، بكب و سسيس. دعونا نرى ما هو مطلوب للحصول على أفضل أداء وكيفية مراقبة 1) T-سكل بولك إنزيرت دعونا نبدأ مع الأمر إنزيرت بولك: التحقق من التقدم تحميل البيانات للتحقق من عدد الصفوف التي حصلت بالفعل تحميلها في تسي، حتى عندما يتم استخدام الخيار تابل لوك، الاستعلام عن دمف جديد يسمى sys. dmdbcolumnstorerowgroupphysicalstats: هذا دمف سوف تكشف أيضا حالات مجموعة الموارد المحتملة بمزيد من التفصيل أثناء التحميل. هناك أربع حالات مجموعة صف محتملة أثناء تحميل البيانات. عندما ترى الدولة إنفيسبيل كما هو موضح في الصورة أدناه يعني أن يتم ضغط البيانات إلى روجروب. 0: غير مرغوب فيه (روجروب هو قيد الإنشاء من البيانات في مخزن دلتا) 1: OPEN160160160160160160160 (روجروب يقبل سجلات جديدة) 2: CLOSED160160160 (روجروب شغل ولكن لم تضغط بعد من قبل عملية المحرك توبل) 3: COMPRESSED160 ( روجروب شغل وضغط). 4 TOMBSTONE160 (روجروب جاهز لتكون القمامة التي تم جمعها وإزالتها) عن طريق تحديد حجم دفعة مع قيمة 102400 أو أعلى سوف يحقق أقصى قدر من الأداء والبيانات سوف تحصل على تدفق ومباشرة مضغوط في رغ النهائي هذا السلوك سوف تظهر كما مضغوط. يمكنك أيضا التحقق من دمف التي حصلت على عرض مع SQL2014 للتحقق من الدولة روغروب، وهو sys. columnstorerowgroups دمف: نتيجة الاختبار إدراج البيانات المجمعة في جدول مع تسي عن طريق الأمر إدراج السائبة يمكن تحسين قليلا عن طريق إضافة Batchsize102400 و خيارات تابلوك. هذا يجلب 8 تحسن في الإنتاجية. 2) BCP. exe لا يزال يتم استخدام الأداة المساعدة بكب بشكل كبير جدا في العديد من بيئات الإنتاج لذلك يستحق التحقق من ذلك بسرعة: افتراضيا، الصفوف بكب 1000 الصفوف في الوقت إلى سكل سيرفر. الوقت المستغرق لتحميل 7.2 غيغابايت من البيانات عبر بكب: 530 ثانية. أو 160 113K روزيك تعرض حالة روغروب نفسيبل مما يعني أنه مع الإعدادات الافتراضية يتم استخدام مخزن دلتا. للتأكد من أن الأمر بكب يبث البيانات مباشرة في رغس مضغوط لديك لإضافة الخيار باتشسيز b مع قيمة لا تقل عن 102400. ركضت اختبارات مختلفة مع أحجام دفعة أكبر: حتى 1048576، ولكن أعطى 102400 أفضل لي نتيجة. بكب DB. dbo. LINEITEMCCI في F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h تابلوك تظهر حالة مجموعة الصف الآن كومبريسد مما يعني أننا تجاوز دلتا مخزن وتدفق البيانات في رغس مضغوط: النتيجة: بكب الانتهاء في 457 ثانية أو 133K الصفوف في الثانية أو أثناء الاختبار لاحظت أن إعدادات سسيس 2016 الافتراضية استخدام أحجام ذاكرة التخزين المؤقت التي يمكن أيضا أن تحد من حجم دفعة لتصبح أقل من 100K الصفوف. في المثال أدناه ترى أن البيانات هبطت في مخازن دلتا: يتم إغلاق الدول رغ والحقول دلتاستوريهوبتيد يتم تعبئتها، مما يعني أن مخازن دلتا هي الاستدانة. كانت هذه هي اللحظة التي تمكنك من التواصل مع زملائي الذين لاحظوا ذلك لحسن الحظ، وكان هناك حل بالفعل هناك (انظر: قدرة تدفق البيانات على تخزين البيانات في المخزن المؤقت تفيد بتحميل البيانات إلى تسي). للاستفادة الكاملة من قدرات تدفق تسي لديك لزيادة الذاكرة الافتراضية بوفرزيزي أمبير ماكسروز الإعدادات: تغيير هذه إلى 10x أكبر القيم: 8211 ديفولتماكسوفيروس من 10000 إلى 1024000 وأهمها: 8211 ديفولتبوفرزيزي من 10485760 إلى 104857600. ملاحظة: يجب تعيين الإعداد أوتوادجوستبوفرزيزي جديد إلى ترو عند تحميل صفوف واسعة جدا من البيانات. تغيير أيضا قيم محول الوجهة: 8211 صفوف لكل دفعة: 160 من لا شيء إلى 102400 8211 الحد الأقصى إدراج حجم الالتزام: من 2147483647 إلى 102400 تعادل ميزة قدم مع SP1 2016 سكل سيرفر يفتح مجموعة جديدة كاملة من الاحتمالات للاستفادة من نأمل فإن عمليات التجول أعلاه تساعدك على الحد الأقصى من إدراج السائبة و بكب وأداء سسيس عند تحميل البيانات إلى عمود متفاوت المسافات فهرس ماذا ستكون الطريقة الأسرع المطلقة لتحميل البيانات من ملف مسطح إلى جدول ضمن سكل سيرفر 2016 لقد تغير الكثير منذ أولي آخر على هذا الموضوع منذ سنوات عديدة، إيك إدخال في الذاكرة الأمثل الجداول ومفهومي الجداول الجدول كولومنستور. أيضا قائمة المركبات نقل البيانات للاختيار من بينها ينمو: إلى جانب بكب، و T - سكل إدراج السائبة الأمر، سسيس كأداة إيتل وبويرشيل هناك بعض جديدة تضاف، مثل بوليباس، R الخارجي سكريبت أو أدف. في هذا المنصب سوف تبدأ مع التحقق من مدى أسرع من أمبير دائم دائم غير دائم في الذاكرة الجداول هي إعداد الأساس لهذه الاختبارات إم باستخدام أزور DS4V2 القياسية فم مع 8 النوى 28 غيغابايت من ذاكرة الوصول العشوائي و 2 وحدات التخزين هد مع التخزين المؤقت المضيف تم تمكين رو. (كلا لونس توفر 275 مبسيك رو الإنتاجية على الرغم من أن واجهة المستخدم الرسومية تنص على حد من 60MBsec). أنا ولدت واحد 60 مليون صف 7.2 جيغابيت تبش سانيتيم الملف المسطح كما البيانات لتحميل. كقاعدة أساسية لاستخدامها للمقارنة سنستخدم الوقت المستغرق لتحميل الملف إلى جدول كومة الذاكرة المؤقتة: يكتمل هذا الأمر "إدراج مجمع" العادية خلال 7 دقائق بمتوسط ​​143 كيلو رسيك. تمكين قاعدة بيانات الاختبار للجداول المحسنة للذاكرة تم تصميم (في إصدار مطور برامج أمب SQL20142016 إنتيربريس) الذي تم إدخاله في الذاكرة ل أولتب سريع جدا مع العديد من المعاملات الصغيرة والتزامن العالي، وهو نوع مختلف تماما من عبء العمل مثل إدخال السائبة ولكن فقط من كوريوسيتيليتس محاولة إعطائها هناك 2 أنواع من الجداول في الذاكرة: الجداول دائمة وغير دائمة. وسوف تستمر تلك دائمة على البيانات على القرص، غير متينة منها متعود. لتمكين هذا الخيار علينا القيام ببعض التدبير المنزلي وتخصيص حجم القرص السريع لاستضافة هذه الملفات. أولا، قم بتغيير قاعدة البيانات لتمكين الخيار يحتوي على ميموريوبتيميزداتا متبوعا بإضافة موقع ملف وفيلغروب التي تحتوي على جداول الذاكرة الأمثل: الشيء الثالث الذي يجب القيام به هو إضافة تجمع ذاكرة منفصل إلى مثيل سكل سيرفر بحيث يمكن أن تبقي جميع البيانات التي سوف نقوم بتحميلها في جداول الذاكرة منفصلة عن تجمع الذاكرة الافتراضي: ربط قاعدة بيانات إلى تجمع ذاكرة يتم سرد الخطوات لتعريف تجمع ذاكرة منفصل وربط قاعدة بيانات إليه أدناه: يتم إدارة حمامات الذاكرة إضافية عبر سكل حاكم الموارد. الخطوة الرابعة والأخيرة هي ربط قاعدة بيانات الاختبار إلى بركة الذاكرة الجديدة مع الأمر sys. spxtpbinddbresourcepool.160 لكي يصبح الربط فعالا علينا أن نأخذ قاعدة البيانات دون اتصال ونعيدها عبر الإنترنت. مرة واحدة ملزمة يمكننا تغيير حيوي كمية الذاكرة المخصصة إلى تجمعها عبر الأمر ألتر بولس بولك مع (ماكسمموريبرسنت 80) الأمر. إدراج السائبة في جدول دائم في الذاكرة الآن يتم تعيين كل شيء مع تمكين الخيار في الذاكرة، يمكننا إنشاء جدول في الذاكرة. يجب أن يحتوي كل جدول محسن للذاكرة على فهرس واحد على الأقل (إما فهرس نطاق أو تجزئة) تكون بالكامل (re-) مكونة في الذاكرة ولا يتم تخزينها على القرص مطلقا. يجب أن يحتوي الجدول الدائم على مفتاح أساسي معلن، والذي يمكن بعد ذلك أن يدعمه الفهرس المطلوب. لدعم مفتاح أساسي أضفت عمود رونومبر ROWID1 إضافية إلى الجدول: تحديد حجم دفعة من 1 (ما يصل إلى 5) مليون صف إلى الأمر إدراج بالجملة يساعد على استمرار البيانات إلى القرص في حين أن إدراج بالجملة مستمر (بدلا من حفظ كل ذلك في النهاية) القيام بذلك يقلل من ضغط الذاكرة على بركة الذاكرة بوخك أنشأنا. تحميل البيانات في جدول دائم في الذاكرة يكمل في 5 دقائق 28 ثانية، أو 183K روسيك. هذا الوقت طيب ولكن ليس ذلك أسرع بكثير من خط الأساس لدينا. عند النظر إلى sys. dmoswaitstats يظهر أن ويتستات no.1 هو إمبروفيوايت الذي يحدث عندما ينتظر سكل سيرفر إو تحميل الجزء الأكبر لإنهاء. النظر في عداد الأداء الجزء الأكبر من نسخ رويسك و ديسك يكتب بيتسيك يظهر بيغ إلى القرص المسامير من 275 مبسيك مرة واحدة دفعة حصلت في (المسامير الخضراء). هذا هو الحد الأقصى لما يمكن للقرص تسليم ولكن لا يفسر كل شيء. وبالنظر إلى مكاسب بسيطة، ونحن سوف بارك هذا واحد للتحقيق في المستقبل. مراقبة بركة الذاكرة عن طريق sys. dmresourcegovernorresourcepools دمف يمكن أن نتحقق إذا كان لدينا جدول في الذاكرة يستفيد من بولهك الذاكرة التي تم إنشاؤها حديثا بركة: يظهر الإخراج هذا هو الحال في 7.2GB (بعض اضافية ل رويد) حصلت غير مضغوط تحميلها في الذاكرة تجمع بركة سباحة: إذا حاولت تحميل المزيد من البيانات من لديك ذاكرة المتاحة إلى تجمع سوف تحصل على رسالة مناسبة مثل هذا واحد: تم إنهاء البيان. مسغ 701، مستوى 17، حالة 103، سطر 5 هناك ذاكرة نظام غير كافية في تجمع الموارد 8216PokHK لتشغيل هذا الاستعلام. للبحث عن مستوى أعمق في تخصيص مساحة الذاكرة على أساس جدول في الذاكرة، يمكنك تشغيل الاستعلام التالي (مأخوذة من سكل سيرفر في الذاكرة أولتب إنتيرنالس لمستند سكل سيرفر 2016): يتم تخزين البيانات التي قمنا بتحميلها فقط ك فارهياب مع مؤشر التجزئة: حتى الآن جيد جدا الآن يتيح التحرك والتحقق من كيفية التدريج في الجدول غير دائم يؤدي إدراجها في جدول غير دائم في الذاكرة ل إمد الجداول نحن لسنا بحاجة إلى مفتاح أساسي لذلك نحن فقط إضافة ومؤشر هاش غير متفاوت المسافات وتعيين دورابيليتي سشيمونلي. تكتمل بيانات إدراج السائبة في جدول غير دائم في غضون 3 دقائق مع إنتاجية من 335K روزيك (مقابل 7 دقائق) هذا هو 2.3x أسرع ثم إدراجها في جدول كومة الذاكرة المؤقتة. لتدريج البيانات هذا بالتأكيد فوز سريع سسيس واحدة السائبة إدراج في جدول غير دائم تقليديا سسيس هو أسرع وسيلة لتحميل ملف بسرعة إلى سكل سيرفر لأن سسيس التعامل مع كافة البيانات المعالجة المسبقة بحيث يمكن لمحرك سكل سيرفر قضاء علامات وحدة المعالجة المركزية على استمرار البيانات إلى القرص. سيظل هذا هو الحال عند إدراج البيانات في جدول غير دائم أدناه ملخص للاختبارات ركضت مع سسيس لهذه المشاركة: خيار سسيس فاستبارس و 160 ديفولتبوفيرماكسروز وإعدادات ديفولتبوفيرزيز هي التعزيز الأداء الرئيسي. أيضا موفر أولي دب (SQLOLEDB.1) الأصلي أداء أفضل قليلا من عميل سكل الأصلي (SQLNCLI11.1). عند تشغيل سسيس و سكل سيرفر جنبا إلى جنب، زيادة حجم حزمة الشبكة غير مطلوبة. 160160 النتيجة الصافية: حزمة سسيس الأساسية التي تقرأ مصدر ملف مسطح وكتابة البيانات مباشرة إلى الجدول غير دائم عبر وجهة أولي دب ينفذ مماثل الأمر السائبة إدراج في جدول إمد: يتم تحميل 60 مليون الصفوف في 2minutes 59seconds أو 335K روزيك، مطابقة لأمر إدراج السائبة. سسيس مع الموزع البيانات المتوازن ولكن wait8230160 تم تصميم الجداول في الذاكرة للعمل قفل أمبير مزلاج مجانا لذلك هذا يعني أننا يمكن تحميل البيانات أيضا عبر تيارات متعددة وهذا أمر سهل لتحقيق مع سسيس الموزع البيانات المتوازنة سيجلب فقط أن (بد في القسم المشترك من سسيس تولبوكس) إضافة عنصر بد وإدراج البيانات في نفس الجدول غير دائم مع 3 تيارات يوفر أفضل الإنتاجية: نحن الآن ما يصل الى 526000 روزيك النظر في هذا الخط المسطح جدا، مع فقط 160 من وقت وحدة المعالجة المركزية المستخدمة من قبل سكلسرفر، يبدو أننا ضرب بعض عنق الزجاجة: حاولت بسرعة أن تكون خلاقة من خلال الاستفادة من وظيفة مودولو وأضاف 2 المزيد من تدفق البيانات داخل الحزمة (كل معالجة 13 من البيانات) 160 ولكن هذا لا يتحسن الكثير (1 min52sec) حتى موضوعا كبيرا للتحقيق عن وظيفة في المستقبل 160160 الخيار في الذاكرة غير دائم الجدول يجلب بعض تحسين الأداء الجاد لتدريج البيانات تحميل البيانات 1.5x أسرع مع منتظم السائبة الباحث ر وتصل إلى 3.6 مرات أسرع مع سسيس. هذا الخيار، في المقام الأول تهدف إلى تسريع أولتب، ويمكن أيضا أن تحدث فرقا كبيرا لتقليص نافذة دفعة الخاص بك بسرعة (أن يستمر) معظم الناس على دراية بهذه العبارة، كوتيسيس سوف تقتل طائرين مع ستونكوت واحد. إذا لم تكن كذلك، فإن المرحلة تشير إلى نهج يعالج هدفين في إجراء واحد. (للأسف، فإن التعبير نفسه غير سارة إلى حد ما، كما أن معظمنا don39t تريد رمي الحجارة على الحيوانات البريئة) اليوم I39m الذهاب لتغطية بعض الأساسيات على اثنين من الميزات الرائعة في سكل سيرفر: مؤشر كولومنستور (متوفر فقط في سكل سيرفر المؤسسة) و مخزن الاستعلام سكل. نفذت ميكروسوفت فعلا مؤشر كولومنستور في سكل 2012 المؤسسة، على الرغم من أنها 39ve تعزيزه في آخر إصدارين من سكل سيرفر. عرضت ميكروسوفت مخزن الاستعلام في سكل سيرفر 2016. لذلك، ما هي هذه الميزات والسبب في أنها مهمة حسنا، لدي تجريبي من شأنها أن أعرض كل من الميزات وتبين كيف يمكن أن تساعدنا. قبل أن أذهب أبعد من ذلك، وأنا أيضا تغطية هذا (وغيرها من سكل 2016 الميزات) في بلدي مجلة مجلة كود على الميزات الجديدة سكل 2016. كمقدمة أساسية، يمكن أن يساعد مؤشر كولومنستور تسريع الاستعلامات التي سكاناجريغات على كميات كبيرة من البيانات، و يخزن "مخزن طلبات البحث" عمليات تنفيذ الاستعلام وخطط التنفيذ وإحصاءات وقت التشغيل التي تحتاج عادة إلى جمعها يدويا. ثق بي عندما أقول، هذه هي الميزات الرائعة. لهذا العرض، I39ll باستخدام قاعدة بيانات تجريبي مستودع بيانات ميكروسوفت كونتوسو التجزئة. يتحدث فضفاضة، كونتوسو دو مثل حصة كبيرة حقا أدفنتورورسكوت، مع الجداول التي تحتوي على الملايين من الصفوف. (أكبر جدول أدفنتوروركس يحتوي على ما يقرب من 100،000 الصفوف على الأكثر). يمكنك تنزيل قاعدة بيانات كونتوسو دو هنا: ميكروسوفت-usdownloaddetails. aspxid18279. يعمل كونتوسو دو بشكل جيد جدا عندما تريد اختبار الأداء على الاستعلامات ضد الجداول الكبيرة. كونتوسو دو يحتوي على مستودع البيانات القياسية جدول الحقائق يسمى فاكتونلينزاليس، مع 12.6 مليون الصفوف. that39s بالتأكيد ليست أكبر مستودع مستودع البيانات في العالم، ولكن it39s لا child39s اللعب سواء. لنفترض أنني أريد تلخيص كمية مبيعات المنتجات لعام 2009، وترتيب المنتجات. قد استعلم جدول الحقائق والانضمام إلى جدول "أبعاد المنتج" واستخدام الدالة رانك، مثل ذلك: Here39s مجموعة نتيجة جزئية من أعلى 10 صفوف، من إجمالي المبيعات. على جهاز الكمبيوتر المحمول (i7، 16 غيغابايت من ذاكرة الوصول العشوائي)، الاستعلام يأخذ أي مكان من 3-4 ثوان لتشغيل. قد لا يبدو ذلك وكأنه نهاية العالم، ولكن بعض المستخدمين قد يتوقعون نتائج قريبة من لحظة (الطريقة التي قد ترى نتائج شبه فورية عند استخدام إكسيل مقابل مكعب أولاب). الفهرس الوحيد الموجود حاليا على هذا الجدول هو فهرس متفاوت المسافات لمفتاح المبيعات. إذا نظرنا إلى خطة التنفيذ، سكل سيرفر يجعل اقتراح لإضافة فهرس تغطية إلى الجدول: الآن، فقط لأن سكل سيرفر يقترح فهرس، يعني tn39 يجب عليك إنشاء الفهارس عمياء على كل رسالة اقتباس إندكسكوت. ومع ذلك، في هذه الحالة، يكتشف سكل سيرفر أننا تصفية استنادا إلى السنة، واستخدام مفتاح المنتج ومبلغ المبيعات. لذلك، يقترح سكل سيرفر مؤشر تغطية، مع داتيكي كحقل مفتاح الفهرس. السبب الذي نسميه هذا مؤشر كوتوفرينجكوت لأن سكل سيرفر سوف كوتبرينغ على طول الحقول غير المفتاحكوت استخدمنا في الاستعلام، كوتور ريديكوت. وبهذه الطريقة، سكل سيرفر لا تحتاج إلى استخدام الجدول أو الفهرس متفاوت المسافات في كافة محركات قاعدة البيانات يمكن ببساطة استخدام مؤشر تغطية الاستعلام. تغطية المؤشرات تحظى بشعبية في بعض البيانات تخزين البيانات والإبلاغ سيناريوهات، على الرغم من أنها لا تأتي بتكلفة محرك قاعدة البيانات الحفاظ عليها. ملاحظة: تغطي الفهارس حول لفترة طويلة، لذلك أنا haven39t حتى الآن غطت مؤشر كولومنستور ومخزن الاستعلام. لذلك، سأقوم بإضافة مؤشر التغطية: إذا قمت بإعادة تنفيذ نفس الاستعلام ركضت قبل لحظة (تلك التي جمعت كمية المبيعات لكل منتج)، الاستعلام في بعض الأحيان يبدو لتشغيل حوالي ثانية أسرع، وأحصل على خطة تنفيذ مختلفة، واحدة التي تستخدم مؤشر تسعى بدلا من فهرس المسح الضوئي (باستخدام مفتاح التاريخ على مؤشر تغطية لاسترداد المبيعات لعام 2009). لذلك، قبل مؤشر كولومنستور، يمكن أن يكون هذا طريقة واحدة لتحسين هذا الاستعلام في الإصدارات القديمة من سكل سيرفر. تشغيله أسرع قليلا من أول واحد، وأنا الحصول على خطة التنفيذ مع مؤشر تسعى بدلا من مسح الفهرس. ومع ذلك، هناك بعض القضايا: واثنين من مشغلي التنفيذ كوتيندكس سيكوت و كوتهاش المباراة (التجميع) كوت على حد سواء تعمل أساسا عن طريق روكوت. تخيل ذلك في جدول يحتوي على مئات الملايين من الصفوف. ذات الصلة، والتفكير في محتويات جدول الحقائق: في هذه الحالة، قيمة مفتاح تاريخ واحد أندور قيمة مفتاح منتج واحد قد تتكرر عبر مئات الآلاف من الصفوف (تذكر، يحتوي الجدول حقيقة أيضا مفاتيح للجغرافيا والترويج، بائع ، وما إلى ذلك) لذلك، عندما كوتندكس سيكوت و كوتهاش ماتشكوت صف العمل حسب الصف، وأنها تفعل ذلك على القيم التي قد تتكرر عبر العديد من الصفوف الأخرى. هذا هو عادة حيث I39d سيجو إلى سكل سيرفر كولومنستور الفهرس الذي يقدم سيناريو لتحسين أداء هذا الاستعلام بطرق مذهلة. ولكن قبل أن أفعل ذلك، Let39s العودة في الوقت المناسب. Let39s العودة إلى عام 2010، عندما قدمت مايكروسوفت وظيفة إضافية ل إكسيل المعروفة باسم بويربيفوت. كثير من الناس ربما يتذكرون مشاهدة العروض التجريبية من بويربيفوت ل إكسيل، حيث يمكن للمستخدم قراءة الملايين من الصفوف من مصدر بيانات خارجي إلى إكسيل. سوف بويربيفوت ضغط البيانات، وتوفير محرك لإنشاء الجداول المحورية ومخططات البيفوت التي أجريت بسرعة مذهلة ضد البيانات المضغوطة. يستخدم بويربيفوت تقنية في الذاكرة التي وصفتها ميكروسوفت كوفرتيباكوت. هذه التكنولوجيا في الذاكرة في بويربيفوت من شأنه أن تتخذ أساسا مفاتيح مفتاح كييفورين مفتاح الأعمال وضغط عليها وصولا إلى متجه واحد. كما أن التكنولوجيا الموجودة في الذاكرة سوف تقوم بتقسيم هذه القيم بالتوازي، في كتل عدة مئات في المرة الواحدة. وخلاصة القول هو أن مايكروسوفت خبز كمية كبيرة من تحسينات الأداء في ميزة فيرتيباق في الذاكرة بالنسبة لنا لاستخدام، والحق من مربع المثل. لماذا أنا أخذ هذا نزهة صغيرة أسفل حارة الذاكرة لأنه في سكل سيرفر 2012، نفذت ميكروسوفت واحدة من أهم الميزات في تاريخ محرك قاعدة البيانات الخاصة بهم: مؤشر كولومنستور. الفهرس هو في الحقيقة فهرس بالاسم فقط: هو طريقة لاتخاذ جدول سكل سيرفر وإنشاء مخزن عمود مضغوط في الذاكرة يقوم بضغط قيم المفتاح الخارجي المكرر وصولا إلى قيم متجه واحد. أنشأت مايكروسوفت أيضا تجمع الاحتياطي الجديد لقراءة هذه القيم متجه مضغوط بالتوازي، وخلق إمكانات لتحقيق مكاسب أداء ضخمة. لذلك، I39m الذهاب إلى إنشاء فهرس العمودية على الطاولة، و I39ll نرى كم أفضل (وأكثر كفاءة) تشغيل الاستعلام، مقابل الاستعلام الذي يتم تشغيله ضد مؤشر التغطية. لذلك، I39ll إنشاء نسخة مكررة من فاكتونلينزاليس (I39ll نسميها فاكتونلينزاليسديتايلنكس)، و I39ll إنشاء فهرس العمودية على الجدول مكررة بهذه الطريقة أنا won39t تتداخل مع الجدول الأصلي ومؤشر تغطي بأي شكل من الأشكال. بعد ذلك، I39ll إنشاء فهرس العمودية على الجدول الجديد: لاحظ عدة أشياء: I39ve المحدد عدة أعمدة المفتاح الأجنبي، فضلا عن مبلغ المبيعات. تذكر أن فهرس الأعمدة ليس مثل فهرس مخزن الصف التقليدي. ليس هناك كوتيكوت. نحن ببساطة تشير إلى الأعمدة التي يجب ضغط سكل سيرفر ووضعها في مخزن عمود في الذاكرة. لاستخدام تشبيه بويربيفوت ل إكسيل عندما نقوم بإنشاء فهرس عمود، we39re نقول سكل سيرفر أن تفعل أساسا نفس الشيء الذي فعلت بويربيفوت عندما استوردنا 20 مليون صف في إكسيل باستخدام بويربيفوت لذلك، I39ll إعادة تشغيل الاستعلام، وهذه المرة باستخدام جدول فاكتونلينزاليسديتايلنكس مكرر الذي يحتوي على فهرس مخزن العمود. يتم تشغيل هذا الاستعلام على الفور في أقل من ثانية. ويمكنني أيضا أن أقول أنه حتى لو كان الجدول مئات الملايين من الصفوف، فإنه لا يزال يعمل في كوتابات المثل من إيلاشكوت. يمكننا أن ننظر في خطة التنفيذ (وفي لحظات قليلة، ونحن سوف)، ولكن الآن الوقت 39s لتغطية ميزة مخزن الاستعلام. تخيل للحظة، أننا أجرينا كل من الاستعلامات بين عشية وضحاها: الاستعلام الذي استخدم الجدول فاكتونلينزاليس العادية (مع مؤشر التغطية) ثم الاستعلام الذي استخدم الجدول المكررة مع فهرس العمود. عند تسجيل الدخول في صباح اليوم التالي، نحن نرغب في رؤية خطة التنفيذ لكل من الاستعلامات عند حدوثها، فضلا عن إحصاءات التنفيذ. وبعبارة أخرى، we39d ترغب في رؤية نفس الإحصاءات التي we39d تكون قادرة على معرفة ما إذا كنا ركض كل من الاستفسارات بشكل تفاعلي في ستوديو إدارة سكل، تحولت في تايم وإحصاءات إو، وعرضت خطة التنفيذ مباشرة بعد تنفيذ الاستعلام. حسنا، أن 39s ما مخزن الاستعلام يسمح لنا القيام به يمكننا تشغيل (تمكين) مخزن الاستعلام لقاعدة بيانات، والتي سوف تؤدي سكل سيرفر لتخزين تنفيذ الاستعلام والتخطيط الإحصاءات حتى نتمكن من عرضها في وقت لاحق. لذلك، I39m الذهاب إلى تمكين مخزن الاستعلام على قاعدة بيانات كونتوسو مع الأمر التالي (و I39ll أيضا مسح أي التخزين المؤقت): ثم I39ll تشغيل اثنين من الاستعلامات (و كوتريبندكوت التي ركضتها قبل ساعات): الآن Let39s تدعي أنها ركضت ساعات منذ. وفقا لما قلته، فإن مخزن الاستعلام التقاط إحصاءات التنفيذ. فكيف أرى لهم لحسن الحظ، أن 39s من السهل جدا. إذا قمت بتوسيع قاعدة بيانات كونتوسو دو، I39ll راجع مجلد "استعلام مخزن". مخزن الاستعلام لديه وظائف هائلة و I39ll محاولة لتغطية الكثير من ذلك في المشاركات بلوق لاحقة. ولكن في الوقت الراهن، أريد عرض إحصاءات التنفيذ على الاستعلامات اثنين، وعلى وجه التحديد فحص مشغلي التنفيذ لمؤشر المخازن. لذلك I39ll انقر بزر الماوس الأيمن فوق أعلى استعلامات الموارد المستهلك وتشغيل هذا الخيار. هذا يعطيني الرسم البياني مثل واحد أدناه، حيث أستطيع أن أرى مدة تنفيذ التنفيذ (بالمللي ثانية) لجميع الاستعلامات التي تم تنفيذها. في هذه الحالة، كان الاستعلام 1 الاستعلام ضد الجدول الأصلي مع فهرس التغطية، وكان الاستعلام 2 ضد الجدول مع فهرس الأعمدة. أرقام don39t تكمن مؤشر مخزن تجاوزت أداء مؤشر تابلكوفرينغ الأصلي بعامل ما يقرب من 7 إلى 1. يمكنني تغيير المقياس للنظر في استهلاك الذاكرة بدلا من ذلك. في هذه الحالة، لاحظ أن الاستعلام 2 (استعلام الفهرس عمود مخزن) استخدام ذاكرة أكثر بكثير. هذا يوضح بوضوح لماذا مؤشر العمودية يمثل التكنولوجيا كوتين-ميموريكوت تحميل سكل سيرفر مؤشر مخزن العمود بأكمله في الذاكرة ويستخدم تجمع المخزن المؤقت مختلفة تماما مع مشغلي التنفيذ المحسنة لمعالجة الفهرس. موافق، لذلك لدينا بعض الرسوم البيانية لعرض إحصائيات التنفيذ يمكن أن نرى خطة التنفيذ (ومشغلي التنفيذ) المرتبطة بكل تنفيذ نعم، يمكننا إذا قمت بالنقر فوق شريط العمودي للاستعلام الذي استخدم فهرس العمود، you39ll انظر التنفيذ خطة أدناه. أول شيء نراه هو أن سكل سيرفر إجراء فحص فهرس العمود، والتي تمثل ما يقرب من 100 من تكلفة الاستعلام. قد تكون تقول، كوتايت دقيقة واحدة، الاستعلام الأول يستخدم مؤشر تغطية وأداء فهرس تسعى فكيف يمكن أن يكون مسح مؤشر عمودية يكون أسرع من أن 39s سؤالا مشروعا، ولحسن الحظ هناك 39 إجابة. حتى عندما يقوم الاستعلام الأول بإجراء فهرس، فإنه لا يزال تنفيذ كوترو بواسطة روكوت. إذا وضعت الماوس فوق عامل المسح الضوئي عامل مسح الفهرس، أرى تلميح (مثل واحد أدناه)، مع إعداد واحد مهم: وضع التنفيذ هو باتش (على عكس رو، وهو ما كان لدينا مع الاستعلام الأول باستخدام تغطي المؤشر). هذا الوضع باتش يخبرنا أن سكل سيرفر معالجة ناقلات مضغوط (لأي القيم مفتاح أجنبي التي يتم تكرارها، مثل مفتاح المنتج ومفتاح التاريخ) في دفعات ما يقرب من 1000، بالتوازي. لذلك سكل سيرفر لا يزال قادرا على معالجة مؤشر عمود أكثر من ذلك بكثير بكفاءة. بالإضافة إلى ذلك، إذا وضعت الماوس فوق مهمة هاش ماتش (التجميع)، أرى أيضا أن سكل سيرفر يتم تجميع فهرس المخازن العمودية باستخدام وضع دفعة (على الرغم من أن المشغل نفسه يمثل مثل هذه النسبة الصغيرة من تكلفة الاستعلام) وأخيرا، أنت قد يسأل، كوتوك، لذلك سكل سيرفر ضغط القيم في البيانات، يعامل القيم كمتجهات، وقراءتها في كتل ما يقرب من ألف القيم بالتوازي ولكن الاستعلام الخاص بي يريد فقط البيانات لعام 2009. لذلك هو سكل سيرفر المسح الضوئي فوق مجموعة كاملة من داتاكوت مرة أخرى، سؤال جيد. الجواب هو، كوتنوت ريليكوت. لحسن الحظ بالنسبة لنا، تجمع مخزن المخزن المؤقت مخزن عمود جديد وظيفة أخرى تسمى كوتسيجمنت إكستراكتيونكوت. في الأساس، سيقوم سكل سيرفر بفحص قيم المتجهات للعمود مفتاح التاريخ في فهرس العمود، وإزالة الشرائح التي تقع خارج نطاق عام 2009. I39ll توقف هنا. في المشاركات بلوق لاحق I39ll تغطية كل من مخزن عمود ومخزن الاستعلام بمزيد من التفصيل. أساسا، ما رأينا هنا اليوم هو أن مؤشر كولومنستور يمكن أن تسرع إلى حد كبير الاستعلامات التي سكاناجريغات على كميات كبيرة من البيانات، ومخزن الاستعلام التقاط عمليات تنفيذ الاستعلام والسماح لنا لفحص التنفيذ وإحصاءات الأداء في وقت لاحق. في النهاية، we39d ترغب في إنتاج مجموعة النتائج التي تبين ما يلي. لاحظ ثلاثة أشياء: الأعمدة أساسا محورية كل من أسباب العودة المحتملة، بعد عرض مبلغ المبيعات مجموعة النتائج تحتوي على المجاميع الفرعية بحلول نهاية الأسبوع (الأحد) التاريخ عبر جميع العملاء (حيث العميل هو نول) مجموعة النتائج يحتوي على المجموع الكلي صف (حيث العميل والتاريخ على حد سواء نول) أولا، قبل أن ندخل في نهاية سكل يمكننا استخدام القدرة بيفوتماتريكس ديناميكية في سرس. سنحتاج ببساطة إلى الجمع بين مجموعتي النتائج من عمود واحد ثم يمكننا تغذية النتائج إلى مراقبة مصفوفة سرس، والتي سوف تنتشر أسباب العودة عبر محور الأعمدة من التقرير. ومع ذلك، لا يستخدم الجميع سرس (على الرغم من أن معظم الناس ينبغي). ولكن حتى في هذه الحالة، يحتاج المطورون أحيانا إلى استهلاك مجموعات النتائج في شيء آخر غير أداة الإبلاغ. لذلك لهذا المثال، Let39s نفترض أننا نريد أن ننتج مجموعة النتائج لصفحة شبكة ويب وربما المطور يريد كوتستريب استبعاد الصفوف المجموع الفرعي (حيث لدي قيمة ريسولتسستنوم 2 و 3) ووضعها في شبكة ملخص. حتى الخط السفلي، ونحن بحاجة لتوليد الناتج أعلاه مباشرة من إجراء المخزنة. و كما أضاف تويست الأسبوع المقبل يمكن أن يكون سبب العودة X و Y و Z. لذلك نحن don39t معرفة عدد أسباب العودة يمكن أن يكون هناك. نحن نريد بسيط الاستعلام إلى محورية القيم المحتملة المحتملة لسبب الإرجاع. هنا حيث T - سكل بيفوت لديه قيود نحن بحاجة إلى توفيره القيم الممكنة. منذ أننا فاز 39t نعرف أنه حتى وقت التشغيل، ونحن بحاجة إلى إنشاء سلسلة الاستعلام ديناميكيا باستخدام نمط سكل الحيوي. يتضمن نمط سكل الديناميكي توليد بناء الجملة، قطعة قطعة، تخزينها في سلسلة، ثم تنفيذ السلسلة في النهاية. ديناميك سكل يمكن أن تكون صعبة، كما لدينا لتضمين بناء الجملة داخل سلسلة. ولكن في هذه الحالة، لدينا الخيار الحقيقي الوحيد إذا أردنا التعامل مع عدد متغير من أسباب العودة. I39ve وجدت دائما أن أفضل طريقة لإنشاء حل سكل الديناميكي هو من خلال معرفة ما كوتيدالكوت ولدت الاستعلام سيكون في النهاية (في هذه الحالة، نظرا لأسباب عودة نعرفها عن)، ثم عكس الهندسة ذلك عن طريق قطع معا جزء واحد في وقت واحد. وهكذا، هنا هو سكل نحن بحاجة إذا كنا نعرف أن أسباب العودة (A إلى D) كانت ثابتة ولن تتغير. يقوم الاستعلام بما يلي: يجمع البيانات من ساليسداتا مع البيانات من ريتورداتا حيث نحن كوثارد-ويريكوت كلمة المبيعات كنوع إجراء شكل جدول المبيعات ثم قم باستخدام سبب الإرجاع من بيانات الإرجاع في نفس العمود أكتيونتيب. وهذا سيعطينا العمود أكتيونتيب نظيفة التي محور. نحن الجمع بين عبارات سيليكت اثنين إلى تعبير جدول مشترك (كت)، وهو أساسا الاستعلام الفرعي الجدول المشتقة التي نستخدمها لاحقا في العبارة التالية (إلى بيفوت) عبارة بيفوت ضد كت، التي تجمع الدولارات لنوع الإجراء في أحد قيم نوع الإجراء الممكنة. لاحظ أن هذا isn39t مجموعة النتيجة النهائية. نحن نضع هذا في كت التي تقرأ من كت الأولى. والسبب في ذلك هو أننا نريد أن نفعل مجموعات متعددة في النهاية. عبارة سيليكت النهائية التي تقرأ من بيفوتكت، ويجمعها مع استعلام لاحق ضد بيفوتكت نفسه، ولكن حيث نقوم أيضا بتنفيذ مجموعتين في ميزة مجموعات التجميع في سكل 2008: تجميع حسب تاريخ نهاية الأسبوع (dbo. WeekEndingDate) تجميع لجميع الصفوف () حتى إذا كنا نعرف على وجه اليقين أن we39d لم يكن لديك أكثر رموز العودة السبب، ثم هذا سيكون الحل. ومع ذلك، نحن بحاجة إلى حساب رموز السبب الأخرى. لذلك نحن بحاجة إلى إنشاء هذا الاستعلام بأكمله أعلاه سلسلة واحدة كبيرة حيث أننا بناء أسباب العودة المحتملة كقائمة واحدة مفصولة بفواصل. I39m الذهاب لإظهار كامل رمز سكل T لتوليد (وتنفيذ) الاستعلام المطلوب. ثم I39ll كسر بها إلى أجزاء وشرح كل خطوة. أولا، هنا 39s رمز كامل لتوليد حيوي ما حصلت I39ve أعلاه. وهناك في الأساس خمس خطوات نحتاج إلى تغطيتها. الخطوة 1 . ونحن نعلم أن في مكان ما في هذا المزيج، ونحن بحاجة إلى إنشاء سلسلة لهذا في الاستعلام: ساليسامونت، السبب A، السبب B، السبب C، السبب D0160016001600160 ما يمكننا القيام به هو بنيت التعبير الجدول المشترك المؤقت الذي يجمع بين الثابت كوتساليس السلكية عمود كولومكوت مع قائمة فريدة من رموز السبب المحتملة. مرة واحدة لدينا ذلك في كت، يمكننا استخدام خدعة صغيرة لطيفة ل شمل باث (3939) لانهيار تلك الصفوف في سلسلة واحدة، وضع فاصلة أمام كل صف أن يقرأ الاستعلام، ومن ثم استخدام ستوف لاستبدال المرحلة الأولى من فاصلة مع مساحة فارغة. هذا هو خدعة التي يمكنك أن تجد في مئات من بلوق سكل. So this first part builds a string called ActionString that we can use further down. الخطوة 2 . we also know that we39ll want to SUM the generatedpivoted reason columns, along with the standard sales column. So we39ll need a separate string for that, which I39ll call SUMSTRING. I39ll simply use the original ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets. Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I39ve also created a variable to simply any carriage returnline feed combinations that we want to embed into the generated query (for readability). So we39ll construct the entire query into a variable called SQLPivotQuery. الخطوة 4. We continue constructing the query again, concatenating the syntax we can quothard-wirequot with the ActionSelectString (that we generated dynamically to hold all the possible return reason values) Step 5 . Finally, we39ll generate the final part of the Pivot Query, that reads from the 2 nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2 nd read against PIVOTCTE to implement the grouping sets. Finally, we can quotexecutequot the string using the SQL system stored proc spexecuteSQL So hopefully you can see that the process to following for this type of effort is Determine what the final query would be, based on your current set of data and values (i. e. built a query model) Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you39ll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH(3939) trick So whats on my mind today Well, at least 13 items Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts. Ill (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and Ill add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics. But until today, I never got around to either publishingposting it. Every so often Id think of revising it, and Id even sit down for a few minutes and make some adjustments to it. But then life in general would get in the way and Id never finish it. So what changed A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, Managed Coder , that caught my attention. The title of the article is On Liberal Arts. and I highly recommend that everyone read it. Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to writecommunicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background. He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members). So its a great read, as are Teds other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Bakers Dozen of Reflections: I have a saying: Water freezes at 32 degrees . If youre in a trainingmentoring role, you might think youre doing everything in the world to help someone when in fact, theyre only feeling a temperature of 34 degrees and therefore things arent solidifying for them. Sometimes it takes just a little bit more effort or another ideachemical catalyst or a new perspective which means those with prior education can draw on different sources. Water freezes at 32 degrees . Some people can maintain high levels of concentration even with a room full of noisy people. Im not one of them occasionally I need some privacy to think through a critical issue. Some people describe this as you gotta learn to walk away from it. Stated another way, its a search for the rarefied air. This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isnt to preach how you should go about your business of solving problems but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible. Some phrases are like fingernails on a chalkboard for me. Use it as a teaching moment is one. (Why is it like fingernails on a chalkboard Because if youre in a mentoring role, you should usually be in teaching moment mode anyway, however subtly). Heres another I cant really explain it in words, but I understand it. This might sound a bit cold, but if a person truly cant explain something in words, maybe they dont understand. Sure, a person can have a fuzzy sense of how something works I can bluff my way through describing how a digital camera works but the truth is that I dont really understand it all that well. There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Read. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. لذلك هناك لديك. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from , youre in luck. You can pass an ADO data table (with the same tablename property as the name of the Table Type) to the procedure. For developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment. SQL Server IO Performance Everything You Need To Consider SQL Server IO performance is crucial to overall performance. Access to data on disk is much slower than in memory, so getting the most out of local disk and SAN is essential. There is a lot of advice on the web and in books about SQL Server IO performance, but I havent found a single source listing everything to consider. This is my attempt to bring all the information together in one place. So here is a list of everything I can think of that can impact IO performance. I have ordered it starting at the physical disks and moving up the wire to the server and finally the code and database schema. Failed Disk When a drive fails in a disk array it will need to be replaced. The impact on performance before replacement depends on the storage array and RAID configuration used. RAID 5 and RAID 6 use distributed parity, and this parity is used to calculate the reads when a disk fails. Read performance loses the advantage of reading from multiple disks. This is also true, although to a lesser degree, on RAID 1 (mirrored) arrays. Reads lose the advantage of reading from multiple stripes for data on the failed disk, and writes may be slightly slower due to the increase in average seek time. Write Cache When a transaction is committed, the write to the transaction log has to complete before the transaction is marked as being committed. This is essential to ensure transactional integrity. It used to be that write cache was not recommended, but a lot of the latest storage arrays have battery-backed caches that are fully certified for use with SQL Server. If you have the option to vary the distribution of memory between read and write cache, try to allocate as much as possible to the write cache. This is because SQL Server performs its own read caching via the buffer pool, so any additional read cache on the disk controller has no benefit. Thin Provisioning Thin provisioning is a technology provided by some SANs whereby the actual disk storage used is just enough for the data, while appearing to the server to be full sized, with loads of free space. Where the total disk allocated to all servers exceeds the amount of physical storage, this is known as over-provisioning. Some SAN vendors try to claim that performance is not affected, but thats not always true. I saw this issue recently on a 3PAR array. Sequential reads were significantly slower on thin provisioned LUNs. Switching to thick provisioned LUNs more than doubled the sequential read throughput. Where Are The Disks Are they where you think they are It is perfectly possible to be connected to a storage array, but for the IO requests to pass through that array to another. This is sometimes done as a cheap way to increase disk space - using existing hardware that is being underutilized is less costly than purchasing more disks. The trouble is that this introduces yet another component into the path and is detrimental to performance - and the DBA may not even be aware of it. Make sure you know how the SAN is configured. Smart Tiering This is called different things by different vendors. The storage array will consist of two or more types of disk, of varying performance and cost. There are the slower 10K disks - these are the cheapest. Then you have the 15K disks. These are faster but more expensive. And then there may be some super-fast SSDs. These are even more expensive, although the price is coming down. Smart tiering migrates data between tiers so that more commonly accessed data is on the faster storage while less commonly used data drops down to the slower storage. This is OK in principle, but you are the DBA. You should already know which data needs to be accessed quickly and which can be slower. Do you really want an algorithm making this decision for you And regular maintenance tasks can confuse the whole thing anyway. Consider a load of index rebuilds running overnight. Lets suppose the last database to be processed is an archive database - do you want this is to be hogging the SSD when the users login first thing in the morning, while the mission critical database is languishing down in the bottom tier This is an oversimplification, of course. The tiering algorithms are more sophisticated than that, but my point stands. You should decide the priorities for your SQL Server data. Dont let the SAN vendors (or storage admins) persuade you otherwise. Storage Level Replication Storage level replication is a disaster recovery feature that copies block level data from the primary SAN to another - often located in a separate data center. The SAN vendors claim no impact on performance, and this is true if correctly configured. But I have seen poorly configured replication have a serious impact on performance. One client suffered a couple of years of poor IO performance. When I joined them I questioned whether the storage replication was responsible. I was told not to be so silly - the vendor has checked and it is not the problem - it must be SQL Server itself A few months later I was contacted again - they had turned off the replication while in the process of moving to a new data center and guess what Write latency improved by an order of magnitude. Let me repeat that this was caused by poor configuration and most storage replication does not noticeably affect performance. But its another thing to consider if youre struggling with SQL Server IO performance. Host Bus Adapters Check that the SAN and HBA firmware are compatible. Sometimes when a SAN is upgraded, the HBAs on the servers are overlooked. This can result in irregular errors, or even make the storage inaccessible. Have a look at the HBA queue depth. A common default is 32, which may not be optimal. Some studies have shown that increasing this to 64 or higher can improve performance. It could also make things worse, depending on workload, SAN make and model, disk layout, etc. So test thoroughly if you can. Some storage admins discourage modifying HBA queue depth as they think everyone will want the same on their servers and the storage array will be swamped. And theyre right, too Persuade them that it is just for you. Promise not to tell anyone else. ايا كان. Just get your extra queue depth if you think it will benefit performance. Too Many Servers When a company forks out a small fortune on a storage area network, they want to get value for money. So naturally, every new server that comes along gets hooked up so it can make use of all that lovely disk space. This is fine until a couple of servers start issuing a lot of IO requests and other users complain of a performance slowdown. This is something I see repeatedly at so many clients, and there is no easy solution. The company doesnt want or cant afford to purchase another SAN. If you think this is a problem for you, put a schedule together of all jobs - across all servers - and try to reschedule some so that workload is distributed more evenly. Partition Alignment and Formatting I will briefly mention partition alignment, although Windows 2008 uses a default offset of 1MB so this is less of an issue than it used to be. I am also not convinced that a lot of modern SANs benefit much from the practise. I performed a test on an EVA a few years ago and found just a 2 improvement. Nevertheless, a few percent is still worth striving for. Unfortunately you will have to tear down your volumes and recreate your partitions if this is to be fixed on an existing system. This is probably not worth the hassle unless you are striving for every last inch of performance. Formatting is something else that should be performed correctly. SQL Server stores data in 8KB pages, but these are retrieved in blocks of 8, called extents. If the disks are formatted with 64KB allocation units, this can have a significant performance benefit. Multipathing If you are not using local disk then you should have some redundancy built into your storage subsystem. If you have a SAN you have a complicated network of HBAs, fabric, switches and controllers between SQL Server and the disks. There should be at least two HBAs, switches, etc. and these should all be connected together in such a way that there are multiple paths to the disks. This redundancy is primarily for high availability, but if the multipathing has been configured as activeactive you may see performance benefits as well. Network Attached Storage Since SQL Server 2008 R2 it has been possible to create, restore or attach a database on a file share. This has a number of possible uses, and particularly for devtest environments it can make capacity management easier, and make moving databases between servers much quicker. The question to be asked, though, is quotDo you really want this in productionquot Performance will not be as good as local or SAN drives. There are additional components in the chain, so reliability may not be as good. And by using the network, your data uses the same infrastructure as all the other TCPIP traffic, which again could impact performance. But theres good news While availability is still a worry, improvements in SMB on Windows Server 2012 (and via an update to WIndows Server 2008 R2) have made it significantly faster. I saw a quote from a Microsoft employee somewhere that claimed 97 of the performance of local storage. I cant find the quote now, and I dont remember if he was measuring latency or throughput. Disk Fragmentation How often do you use the Disk Defragmenter tool on your PC to analyze and defragment your C: drive How often do you check fragmentation on the disks on your SQL Servers For most people that is nowhere near as often, Ill bet. Yet volume fragmentation is just as detrimental to SQL Server performance as it is to your PC. You can reduce the likelihood of disk fragmentation in a number of ways: Pre-size data and log files, rather than rely on auto-growth Set auto-growth increments to sensible values instead of the default 10 Avoid shrinking data and log files Never, ever use the autoshrink database option Ensure disks are dedicated to SQL Server and not shared with other applications You can check fragmentation using the same tool as on your PC. Disk Defragmenter is available on all server versions of Windows. Another way to check is via the Win32Volume class in WMI. This bit of PowerShell reports the file percent fragmentation for all volumes on a given server. If you have significant fragmentation there are a couple of ways to fix it. My preferred option is as follows, but requires some downtime. Stop the SQL services Backup the files on the disk (especially mdf, ndf and ldf files - better safe than sorry) Run the Windows Disk Defragmenter tool Start the SQL services Check the error log to ensure no errors during startup Run CHECKDB against all databases (except tempdb). Ive never seen the defrag tool cause corruption, but you cant be too careful Another option that doesnt require downtime is to use a third party tool such as Diskeeper. This can be very effective at fixing and preventing disk fragmentation, but it costs money and uses a filter driver - see my comments below. Filter Drivers A filter driver is a piece of software that sits between an IO request and the write to disk. It allows the write to be examined and rejected, modified or audited. The most common type of filter driver is installed by anti-virus software. You do not want anti-virus software checking every single write to your database files. You also dont want it checking your backups either, or writes to the error log, or default trace. If you have AV software installed, you can specify exclusions. Exclude all folders used by SQL Server, plus the drives used by data and log files, plus the folders used for backups. Even better is to turn off online AV checking, and schedule a scan at a quiet time. OLTP and BI on the Same Server It is rare to find a system that is purely OLTP. Most will have some sort of reporting element as well. Unfortunately, the two types of workload do not always coexist happily. Ive been reading a lot of articles by Joe Chang, and in one article he explains why this is the case. Essentially, OLTP query plans retrieve rows in small batches (less than a threshold of 25 rows) and these IO requests are handled synchronously by the database engine, meaning that they wait for the data to be retrieved before continuing. Large BI workloads and reporting queries, often with parallel plans, issue asynchronous IO requests and take full advantage of the HBA ability to queue requests. As a result, the OLTP requests have to queue up behind the BI requests, causing OLTP performance to degrade significantly. Auto-grow and Instant File Initialization It is good to have auto-grow enabled, just as a precaution, although you should also pre-size data and log files so that it is rarely needed. However, what happens if a data file grows and you dont have instant file initialization enabled Especially if the auto-grow is set too big. All IO against the file has to wait for the file growth to complete, and this may be reported in the infamous quotIOs taken longer than 15 seconds to completequot message in the error log. Instant initialization wont help with log growth, so make sure log auto-growth increments are not too high. For more information about instant file initialization and how to enable it, see this link Database File Initialization . And while on the subject of auto-grow, see the section on proportional fill, below. Transaction Log Performance How long do your transaction log writes take Less than 1ms More than 5ms Look at virtual file stats, performance counters, or the WRITELOG wait time to see if log write latency is an issue for you. Writes to the transaction log are sequential, and so the write head on the disk should ideally be where it was from the last log write. This means no seek time, and blazingly fast write times. And since a transaction cannot commit until the log has hardened to disk, you rely on these fast writes for a performant system. Advice for years has been for the transaction log for each database to be on its own disk. And this advice is still good for local disk, and for some storage arrays. But now that a lot of SANs have their own battery-backed write cache, this advice is not as critical as it used to be. Provided the cache is big enough to cope with peak bursts of write activity (and see my earlier comments about allocating more cache to writes than to reads) you will get very low latency. So what if you dont have the luxury of a mega-bucks SAN and loads of write cache Then the advice thats been around since the 1990s is still valid: One transaction log file per database on its own drive RAID 1, RAID 10 or RAID 01 So assuming you are happy with your log file layout, what else could be slowing down your log writes Virtual Log Files Although a transaction log is written to sequentially, the file itself can become fragmented internally. When it is first created it consists of several chunks called virtual log files. Every time it is grown, whether manually or automatically, several more virtual log files are added. A transaction log that grows multiple times can end up with thousands of virtual log files. Having too many VLFs can slow down logging and may also slow down log backups. You also need to be careful to avoid VLFs that are too big. An inactive virtual log file is not cleared until the end is reached and the next one starts to be used. For full recovery model, this doesnt happen until the next log backup. So a log backup will suddenly have a lot more work to, and may cause performance problems while it takes place. The answer for a big transaction log is to set an initial size of maximum 8000MB, and then manually grow in chunks of 8000MB up to the target size. This results in maximum VLF size of 512MB, without creating an excessively large number of VLFs. Note: this advice is for manual growth only. Do not auto grow by 8000MB All transactions in the database will stop while the extra space is initialised. Autogrow should be much smaller - but try to manually size the file so that auto grow is unlikely to be needed. Log Manager Limits The database engine sets limits on the amount of log that can be in flight at any one time. This is a per-database limit, and depends on the version of SQL Server being used. SQL Server limits the number of outstanding IOs and MB per second. The limits vary with version and whether 32 bit or 64 bit. See Diagnosing Transaction Log Performance Issues and Limits of the Log Manager for more details. This is why the write latency should be as low as possible. If it takes 20ms to write to the transaction log, and you are limited to 32 IOs in flight at a time, that means a maximum of 1600 transactions per second, well below what a lot of high volume OLTP databases require. This also emphasises the importance of keeping transaction sizes small, as one very large transaction could conceivably hold up other transactions while it commits. If you think these limits are affecting log write performance in your databases there are several ways to tackle the problem: Work on increasing log write performance If you have minimally logged operations you can switch the database to use the BULK LOGGED recovery model. Careful though - a log backup containing a minimally logged operation has to be restored in full. Point in time restore is not possible. Split a high volume database into 2 or more databases, as the log limits apply per database Non-Sequential Log Activity There are actions performed by the database engine that move the write head away from the end of the log file. If transactions are still being committed while this happens, you have a seek overhead and log performance gets worse. Operations that read from the log files include rollback of large transactions, log backups and replication (the log reader agent). There is little you can do about most of these, but avoiding large rollbacks is something that should be tackled at the design and development stage of an application. Proportional Fill Very active tables can be placed in a file group that has multiple data files. This can improve read performance if they are on different physical disks, and it can improve write performance by limiting contention in the allocation pages (especially true for tempdb). You lose some of the benefit, though, if you dont take advantage of the proportional fill algorithm. Proportional fill is the process by which the database tries to allocate new pages in proportion to the amount of free space in each data file in the file group. To get the maximum benefit make sure that each file is the same size, and is always grown by the same increment. This is for both manual and auto growth. One thing to be aware of is how the auto growth works. SQL Server does its best to fill the files at the same rate, but one will always fill up just before the others, and this file will then auto grow on its own. This then gets more new page allocations than the others and becomes a temporary hotspot until the others also auto grow and catch up. This is unlikely to cause problems for most databases, although for tempdb it may be more noticeable. Trace flag 1117 causes all data files in a file group to grow together, so is worth considering if this is an issue for you. Personally I would rather manually size the files so that auto growth isnt necessary. tempdb Configuration Lets start with a few things that everybody agrees on: tempdb files should be placed on the fastest storage available. Local SSD is ideal, and from SQL Server 2012 this is even possible on a cluster Pre-size the data and log files, as auto growth may cause performance issues while it occurs New temporary objects are created all the time, so contention in the GAM, SGAM and PFS pages may be an issue in some environments And now some differences of opinion: There is loads of advice all over the web to create one tempdb data file per core to reduce allocation contention. Paul Randall disagrees (A SQL Server DBA myth a day: (1230) tempdb should always have one data file per processor core ). He says that too many files can actually make things worse. His solution is to create fewer files and to increase only if necessary There is more advice, often repeated, to separate tempdb files from other databases and put them on their own physical spindles. Joe Chang disagrees and has a very good argument for using the common pool of disks. (Data, Log and Temp file placement ). Ill leave you to decide what to do AutoShrink The AutoShrink database option has been around ever since I started using SQL Server, causing lots of performance problems for people who have enabled it without fully realising what it does. Often a third party application will install a database with this option enabled, and the DBA may not notice it until later. So why is it bad Two reasons: It is always used in conjunction with auto grow, and the continuous cycle of grow-shrink-grow causes a huge amount of physical disk fragmentation. Ive already covered that topic earlier in this article While it performs the shrink there is a lot of additional IO, which slows down the system for everything else Disable it. Allocate enough space for the data and log files, and size them accordingly. And dont forget to fix all that fragmentation while youre at it. Insufficient Memory This is an article about SQL Server IO performance, not memory. So I dont want to cover it in any detail here - that is a subject for a different article. I just want to remind you that SQL Server loves memory - the more the better. If your entire database(s) fits into memory youll have a much faster system, bypassing all that slow IO. Lack of memory can lead to dirty pages being flushed to disk more often to make space for more pages being read. Lack of memory can also lead to increased tempdb IO, as more worktables for sort and hash operations have to spool to disk. Anyway, the point of this section is really to make one statement: Fill your servers with as much memory as you can afford, and as much as the edition of SQL Server and Windows can address. SQL Server 2014 has a new feature allowing some tables to be retained in memory, and accessed via natively compiled stored procedures. Some redesign of some of your existing code may be needed to take advantage of this, but it looks like a great performance boost for those OLTP systems that start to use it. High Use of tempdb tempdb can be a major consumer of IO and may affect overall performance if used excessively. It is worth looking at the various reasons for its use, and examining your system to ensure you have minimized these as far as possible. User-created temporary objects The most common of these are temporary tables, table variables and cursors. If there is a high rate of creation this can lead to allocation page contention, although increasing the number of tempdb data-files may partially alleviate this. Processes creating very large temporary tables or table variables are a big no-no, as these can cause a lot of IO. Internal Objects The database engine creates work-tables in tempdb for handling hash joins, sorting and spooling of intermediate result sets. When sort operations or hash joins need more memory than has been granted they spill to disk (using tempdb) and you will see Hash warnings and Sort warnings in the default trace. I originally wrote a couple of paragraphs about how and why this happens and what you can do to prevent it, but then I found this post that explains it much better - Understanding Hash, Sort and Exchange Spill Events . Version Store The third use of tempdb is for the version store. This is used for row versioning. Row versions are created when snapshot isolation or read committed snapshot option is used. They are also created during online index rebuilds for updates and deletes made during the rebuild and for handling data modifications to multiple active result sets (MARS). A poorly written application (or rogue user) performing a large update that affects many thousands of rows when a row versioning based isolation level is in use may cause rapid growth in tempdb and adversely impact IO performance for other users. Table and Index Scans A table scan is a scan of a heap. An index scan is a scan of a clustered or non-clustered index. Both may be the best option if a covering index does not exist and a lot of rows are likely to be retrieved. A clustered index scan performs better than a table scan - yet another reason for avoiding heaps But what causes a scan to be used in the first place, and how can you make a seek more likely Out of date statistics Before checking indexes and code, make sure that statistics are up to date. Enable quotauto create statisticsquot. If quotauto update statisticsquot is not enabled make sure you run a manual statistics update regularly. This is a good idea even if quotauto update statisticsquot is enabled, as the threshold of approximately 20 of changed rows before the auto update kicks in is often not enough, especially where new rows are added with an ascending key. Index Choice Sometimes an existing index is not used. Have a look at improving its selectivity, possibly by adding additional columns, or modifying the column order. Consider whether a covering index could be created. A seek is more likely to be performed if no bookmark lookups will be needed. See these posts on the quottipping pointquot by Kimberly Tripp. The Tipping Point . Inefficient TSQL The way a query is written can also result in a scan, even if a useful index exists. Some of the reasons for this are: Non-sargable expressions in the WHERE clause. quotsargquot means Simple ARGument. So move calculations away from the columns and onto the constants instead. So for example, this will not use the index on OrderDate: WHERE DATEADD ( DAY. 1. OrderDate ) gt GETDATE () Whereas this will use an index if it exists (and it is selective enough): WHERE OrderDate gt DATEADD ( DAY. - 1. GETDATE ()) Implicit conversions in a query may also result in a scan. See this post by Jonathan Kehayias Implicit Conversions that cause Index Scans . Bad Parameter Sniffing Parameter sniffing is a good thing. It allows plan re-use and improves performance. But sometimes it results in a less efficient execution plan for some parameters. Index Maintenance Every index has to be maintained. Im not talking about maintenance plans, but about the fact that when rows are inserted, deleted and updated, the non-clustered indexes also have to be changed. This means additional IO for each index on a table. So it is a mistake to have more indexes than you need. Check that all indexes are being used. Check for duplicates and redundant indexes (where the columns in one are a subset of the columns in another). Check for indexes where the first column is identical but the rest are not - sometimes these can be merged. And of course, test, test, test. Index Fragmentation Index fragmentation affects IO performance in several ways. Range scans are less efficient, and less able to make use of read-ahead reads Empty space created in the pages reduces the density of the data, meaning more read IO is necessary The fragmentation itself is caused by page splits, which means more write IO There are a number things that can be done to reduce the impact of fragmentation, or to reduce the amount of fragmentation. Rebuild or reorganize indexes regularly Specify a lower fill factor so that page splits occur less often (though not too low, see below) Change the clustered index to use an ascending key so that new rows are appended to the end, rather than inserted in a random place in the middle Forwarded Records When a row in a heap is updated and requires more space, it is copied to a new page. But non-clustered indexes are not updated to point to the new page. Instead, a pointer is added to the original page to show where the row has moved to. This is called a forwarding pointer, and there could potentially be a long chain of these pointers to traverse to find the eventual data. Naturally, this means more IO. A heap cannot be defragmented by rebuilding the index (there isnt one). The only way to do this is to create a clustered index on the heap, and then drop it afterwards. Be aware that this will cause all non-clustered indexes to be rebuilt twice - once for the new clustered index, and again when it is dropped. If there are a lot of these it is a good idea to drop the non-clustered indexes first, and recreate them afterwards. Better still is to avoid heaps where possible. I accept there may be cases where they are the more efficient choice (inserting into archive tables, for example), but always consider whether a clustered index would be a better option - it usually is. Wasted Space In an ideal world every data page on disk (and in memory) would be 100 full. This would mean the minimum of IO is needed to read and write the data. In practise, there is wasted space in nearly all pages - sometimes a very high percent - and there are a lot of reasons why this occurs. Low fill factor Ive mentioned fill factor already. If it is too high, and page splits are occurring when rows are inserted or updated, it is sensible to rebuild the index with a lower fill factor. However, if the fill factor is too low you may have a lot of wasted space in the database pages, resulting in more IO and memory use. This is one of those quotsuck it and seequot scenarios. Sometimes a compromise is needed. Page splits This is also discussed above. But as well as fragmentation, page splits can also result in wasted space if the empty space is not reused. The solution is to defragment by rebuilding or reorganizing indexes regularly. Wasteful Choice of Data Types Use the smallest data types you can. And try to avoid the fixed length datatypes, like CHAR(255), unless you regularly update to the longest length and want to avoid page splits. The reasoning is simple. If you only use 20 characters out of 200, that is 90 wasted space, and more IO as result. The higher density of data per page the better. Lazy thinking might make developers create AddressLine1, AddressLine2, etc as CHAR(255), because they dont actually know what the longest should be. In this case, either do some research, find out that the longest is 50 characters (for example) and reduce them to CHAR(50), or use a variable length data type. Schema Design Ive already mentioned choice of data types above, but there are other schema design decisions that can affect the amount of IO generated by an application database. The most common one is designing tables that are too wide. I sometimes see a table with 20, 30, 50, even 100 columns. This means fewer rows fit on a page, and for some extreme cases there is room for just one row per page - and often a lot of wasted space as well (if the row is just slightly wider than half a page, thats 50 wasted). If you really do need 50 columns for your Customer table, ask yourself how many of these are regularly accessed. An alternative is to split into 2 tables. Customer, with just a few of the commonly used columns, and CustomerDetail with the rest. Of course, the choice of which columns to move is important. You dont want to start joining the tables for every query as that defeats the object of the exercise. Page or Row Compression Compression is another way of compacting the data onto a page to reduce disk space and IO. Use of row or page compression can dramatically improve IO performance, but CPU usage does increase. As long as you are not already seeing CPU bottlenecks, compression may be an option to consider. Be aware that compression is an Enterprise edition feature only. Backup Compression Since SQL Server 2008 R2, backup compression has been available on Standard edition as well as Enterprise. This is major benefit and I recommend that it be enabled on all instances. As well as creating smaller backups, it is also quicker and means less write IO. The small increase in CPU usage is well worth it. Enable it by default so that if someone sets off an ad hoc backup it will have minimal IO impact. Synchronous MirroringAlwaysOn High safety mode in database mirroring, or synchronous commit mode in AlwaysOn, both emphasise availability over performance. A transaction on the mirroring principal server or primary replica does not commit until it receives a message back from the mirror or secondary replica that the transaction has been hardened to the transaction log. This increases transactional latency, particularly when the servers are in different physical locations. Resource Governor in 2014 Up until and including SQL Server 2012 resource governor has only been able to throttle CPU and memory usage. Finally the ability to include IO in a resource pool has been added to SQL Server 2014. This has obvious use as a way of limiting the impact of reports on the system from a particular user, department or application. Gathering The Evidence There are a lot of ways you can measure SQL Server IO performance and identify which areas need looking at. Most of what follows is available in SQL CoPilot in graphical and tabular form, both as averages since last service start and as snapshots of current activity. Wait Types Use sys. dmoswaitstats to check number of waits and wait times for IOCOMPLETION, LOGBUFFER, WRITELOG and PAGEIOLATCH. Use this script to focus on the IO wait types: SELECT waittype. waitingtaskscount. waittimems - signalwaittimems AS totalwaittimems , 1. ( waittimems - signalwaittimems ) CASE WHEN waitingtaskscount 0 THEN 1 ELSE waitingtaskscount END AS avgwaitms FROM sys. dmoswaitstats WHERE waittype IN ( IOCOMPLETION. LOGBUFFER. WRITELOG. PAGEIOLATCHSH. PAGEIOLATCHUP. PAGEIOLATCHEX. PAGEIOLATCHDT. PAGEIOLATCHKP ) This shows averages since the last service restart, or since the wait stats were last cleared. To clear the wait stats, use DBCC SQLPERF (sys. dmoswaitstats, CLEAR) You can also check sys. dmoswaitingtasks to see what is currently being waited for. Virtual File Stats Query sys. dmiovirtualfilestats to find out which data and log files get the most read and write IO, and the latency for each file calculated using the stall in ms. SELECT d. name AS databasename. mf. name AS logicalfilename. numofbytesread. numofbyteswritten. numofreads. numofwrites. 1. iostallreadms ( numofreads 1 ) avgreadstallms. 1. iostallwritems ( numofwrites 1 ) avgwritestallms FROM sys. dmiovirtualfilestats (NULL, NULL) vfs JOIN sys. masterfiles mf ON vfs. databaseid mf. databaseid AND vfs. FILEID mf. FILEID JOIN sys. databases d ON mf. databaseid d. databaseid Performance Counters There are two ways of looking at performance counters. Select from sys. dmosperformancecounters, which shows all the SQL Server counters, or use Windows Performance Monitor (perfmon) to see the other OS counters as well. Some counters to look at are: SQL Server:Buffer Manager Lazy writessec The number of times per second that dirty pages are flushed to disk by the Lazy Writer process. An indication of low memory, but listed here as it causes more IO. Checkpoint pagessec The number of dirty pages flushed to disk per second by the checkpoint process. Page readssec Number of physical pages read from disk per second Page writessec Number of physical pages written to disk per second Readahead pagessec Pages read from disk in advance of them being needed. Expect to see high values in BI workloads, but not for OLTP SQL Server:Access Methods Forwarded recordssec Should be as low as possible. See above for explanation of forwarded records. Full scanssec The number of unrestricted full scans. Use of UDFs and table variables can contribute to this, but concentrating on seeks will help to keep the value down Page splitssec The number of page splits per second - combining splits due to pages being added to the end of a clustered index as well as quotgenuinequot splits when a row is moved to a new page. Use the technique from the link in the section on index fragmentation, above, to get a more accurate breakdown Skipped ghosted recordssec For information about ghosted records see An In-depth Look at Ghost Records in SQL Server Workfiles createdsec A measure of tempdb activity Worktables createdsec A measure of tempdb activity SQL Server:Databases Log bytes flushedsec The rate at which log records are written to disk Log flush wait time The duration of the last log flush for each database Log flush waitssec The number of commits per second waiting for a log flush Logical Disk Avg Disk secsRead Avg Disk secsWrite Avg Disk Read bytessec Avg Disk Write bytessec Using the sys. dmosperformancecounters DMV, a lot of counters display a raw value, which has to be monitored over time to see values per second. Others have to be divided by a base value to get a percentage. This makes this DMV less useful unless you perform these calculations and either monitor over time or take an average since the last server restart. This script uses the tempdb creation date to get the number of seconds since the service started and calculates the averages for these counters. It also retrieves all other counters and calculates those that are derived from a base value. USE master SET NOCOUNT ON DECLARE upsecs bigint SELECT upsecs DATEDIFF ( second. createdate. GETDATE ()) FROM sys. databases WHERE name tempdb SELECT RTRIM ( objectname ) objectname. RTRIM ( instancename ) instancename. RTRIM ( countername ) countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 65792 UNION ALL SELECT RTRIM ( objectname ), RTRIM ( instancename ), RTRIM ( countername ), 1. CAST ( cntrvalue AS bigint ) upsecs FROM sys. dmosperformancecounters WHERE cntrtype 272696576 UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 100. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 537003264 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND RTRIM ( v. countername ) base RTRIM ( b. countername ) UNION ALL SELECT RTRIM ( v. objectname ), RTRIM ( v. instancename ), RTRIM ( v. countername ), 1. v. cntrvalue CASE WHEN b. cntrvalue 0 THEN 1 ELSE b. cntrvalue END FROM ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073874176 ) v JOIN ( SELECT objectname. instancename. countername. cntrvalue FROM sys. dmosperformancecounters WHERE cntrtype 1073939712 ) b ON v. objectname b. objectname AND v. instancename b. instancename AND REPLACE ( RTRIM ( v. countername ), (ms). ) Base RTRIM ( b. countername ) ORDER BY objectname. instancename. countername Dynamic Management Views and Functions As well as the DMVs in the above scripts, there are a number of others that are useful for diagnosing SQL Server IO performance problems. Here are all the ones I use. Ill add some sample scripts when I get the time: sys. dmoswaitstats sys. dmiovirtualfilestats sys. dmosperformancecounters sys. dmiopendingiorequests sys. dmdbindexoperationalstats sys. dmdbindexusagestats sys. dmdbindexphysicalstats sys. dmosbufferdescriptors It can also be useful to see what activity there is on the instance. Here are your options: The Profiler tool is quick and easy to use - you can start tracing in a matter of seconds. However, there is some overhead and it may impact performance itself - especially when a lot of columns are selected. A server side trace is a better option. A server-side trace has less of an impact than running Profiler. It has to be scripted using system stored procedures, but Profiler has the ability to generate the script for you. Extended Event Sessions Extended events were first introduced in SQL Server 2008, and have been considerably enhanced in SQL 2012. They are very lightweight, and the use of server-side traces and Profiler is now deprecated. Nevertheless, use of extended events may impact performance of high transaction systems if you are not careful. Use an asynchronous target and avoid complicated predicates to limit the overhead. There are a number of tools for gathering performance data from your servers. SQLIO is a simple tool that creates a file on disk and tests latency and throughput for randomsequential IO, at various block sizes and with a variable number of threads. These are all fully configurable. SQLIO is a great way of getting a baseline on a new server or storage, for future comparison. Third party tools are another option for viewing performance metrics. Some show you what is happening on the server right now. Others are built into more complex (and expensive) monitoring solutions. Performance metrics obtained on virtual servers are unreliable. Performance counters and wait stats may give the impression that everything is OK, when it is not. I recommend the use of the performance monitoring tools provided by the VM vendor. In the case of VMWare, this is very easy to use and is built into Virtual Center. This turned into a much bigger article than I expected - SQL Server IO performance is a big subject I started with everything I knew, and double checked my facts by searching the web and checking books. In the process I learnt a whole lot of new stuff and found a lot of useful links. It has been a useful exercise. Hopefully this has been useful for you too.