Pindah rata rata tsql

Kinerja SQL Server IO Semuanya Perlu Anda Pertimbangkan Kinerja SQL Server IO sangat penting untuk kinerja keseluruhan. Akses ke data pada disk jauh lebih lambat daripada di memori, sehingga mendapatkan sebagian besar disk lokal dan SAN sangat penting. Ada banyak saran di web dan di buku tentang kinerja SQL Server IO, namun saya menemukan satu sumber yang mencantumkan semua yang perlu dipertimbangkan. Inilah usaha saya untuk membawa semua informasi bersama di satu tempat. Jadi, inilah daftar segala hal yang dapat saya pikirkan yang dapat mempengaruhi kinerja IO. Saya telah memesannya mulai dari disket fisik dan memindahkan kawat ke server dan akhirnya skema kode dan database. Gagal Disk Ketika drive gagal dalam array disk maka perlu diganti. Dampak terhadap kinerja sebelum penggantian tergantung pada array penyimpanan dan konfigurasi RAID yang digunakan. RAID 5 dan RAID 6 menggunakan paritas terdistribusi, dan paritas ini digunakan untuk menghitung pembacaan ketika sebuah disk gagal. Performa baca kehilangan keuntungan membaca dari beberapa disk. Ini juga benar, meskipun pada tingkat yang lebih rendah, pada RAID 1 (cermin) array. Membaca kehilangan keuntungan membaca dari banyak garis untuk data pada disk yang gagal, dan menulis mungkin sedikit lebih lambat karena peningkatan rata-rata waktu pencarian. Write Cache Bila transaksi dilakukan, maka surat ke log transaksi harus diselesaikan sebelum transaksi ditandai sebagai commit. Hal ini penting untuk memastikan integritas transaksional. Dulu cache tulis itu tidak disarankan, namun banyak array penyimpanan terbaru memiliki cache yang didukung baterai yang sepenuhnya disertifikasi untuk digunakan dengan SQL Server. Jika Anda memiliki pilihan untuk memvariasikan distribusi memori antara membaca dan menulis cache, cobalah untuk mengalokasikan sebanyak mungkin ke cache tulis. Hal ini karena SQL Server melakukan caching baca sendiri melalui kolam penyangga, jadi setiap tembolok baca tambahan pada disk controller tidak bermanfaat. Thin Provisioning Thin provisioning adalah teknologi yang disediakan oleh beberapa SAN dimana penyimpanan disk sebenarnya digunakan cukup untuk data, sementara server tampil berukuran penuh, dengan banyak ruang kosong. Dimana total disk yang dialokasikan ke semua server melebihi jumlah penyimpanan fisik, ini diketahui sebagai over-provisioning. Beberapa vendor SAN mencoba mengklaim bahwa kinerja tidak terpengaruh, namun tidak selalu benar. Saya melihat masalah ini baru-baru ini pada array 3PAR. Bunyi berurutan secara signifikan lebih lambat pada LUN yang ditetapkan tipis. Beralih ke LUN yang ditentukan tebal lebih dari dua kali lipat throughput baca sekuensial. Dimana Apakah The Disk Apakah mereka di mana Anda pikir mereka itu Sangat mungkin untuk dihubungkan ke array penyimpanan, tapi untuk IO permintaan untuk melewati array yang lain. Hal ini terkadang dilakukan sebagai cara yang murah untuk meningkatkan ruang disk - menggunakan perangkat keras yang ada yang kurang dimanfaatkan lebih murah daripada membeli lebih banyak disk. Masalahnya adalah bahwa ini memperkenalkan komponen lain ke dalam jalur dan merugikan kinerja - dan DBA bahkan mungkin tidak menyadarinya. Pastikan Anda tahu bagaimana SAN dikonfigurasi. Smart Tiering Hal ini disebut hal yang berbeda oleh vendor yang berbeda. Array penyimpanan akan terdiri dari dua atau lebih jenis disk, dengan berbagai kinerja dan biaya. Ada disk 10K yang lebih lambat - ini adalah yang termurah. Kemudian Anda memiliki disk 15K. Ini lebih cepat tapi lebih mahal. Dan mungkin ada beberapa SSD super cepat. Ini bahkan lebih mahal, meski harganya turun. Smart tiering memigrasikan data antar tingkatan sehingga data yang lebih banyak diakses diakses pada penyimpanan yang lebih cepat sementara data yang kurang umum turun ke penyimpanan yang lebih lambat. Ini baik-baik saja pada prinsipnya, tapi Anda adalah DBA. Anda harus sudah tahu data mana yang perlu diakses dengan cepat dan mana yang bisa lebih lambat. Apakah Anda benar-benar menginginkan sebuah algoritma membuat keputusan ini untuk Anda Dan tugas perawatan rutin dapat membingungkan keseluruhannya. Pertimbangkan sebuah beban indeks yang akan dibangun kembali semalam. Misalkan database terakhir yang akan diolah adalah database arsip - apakah Anda ingin ini adalah memonopoli SSD saat pengguna melakukan login terlebih dahulu di pagi hari, sementara database penting misi merana turun di tingkat bawah Ini adalah penyederhanaan yang berlebihan, tentu saja. Algoritma tiering lebih canggih dari itu, tapi poin saya berdiri. Anda harus menentukan prioritas untuk data SQL Server Anda. Jangan biarkan vendor SAN (atau admin penyimpanan) membujuk Anda jika tidak. Replikasi Tingkat Penyimpanan Replikasi tingkat penyimpanan adalah fitur pemulihan bencana yang menyalin data tingkat blok dari SAN utama ke yang lain - sering kali berada di pusat data terpisah. Vendor SAN tidak mengklaim dampak pada kinerja, dan ini benar jika dikonfigurasi dengan benar. Tapi saya telah melihat replikasi yang tidak dikonfigurasi dengan baik memiliki dampak serius pada kinerja. Seorang klien mengalami beberapa tahun kinerja IO yang buruk. Ketika saya bergabung dengan mereka, saya mempertanyakan apakah replikasi penyimpanan itu bertanggung jawab. Saya diberitahu untuk tidak begitu bodoh - vendor telah memeriksa dan itu bukan masalah - itu pasti SQL Server itu sendiri Beberapa bulan kemudian saya dihubungi lagi - mereka telah mematikan replikasi saat dalam proses pindah ke yang baru. Pusat data dan tebak apa yang Write latency ditingkatkan dengan urutan besarnya. Izinkan saya mengulangi bahwa ini disebabkan oleh konfigurasi yang buruk dan kebanyakan replikasi penyimpanan tidak mempengaruhi kinerja secara nyata. Tapi hal lain yang perlu dipertimbangkan jika youre berjuang dengan kinerja SQL Server IO. Adaptor Bus Host Pastikan firmware SAN dan HBA kompatibel. Terkadang saat SAN ditingkatkan, HBA di server diabaikan. Hal ini dapat mengakibatkan kesalahan yang tidak teratur, atau bahkan membuat penyimpanan tidak dapat diakses. Silahkan melihat kedalaman antrian HBA. Default yang umum adalah 32, yang mungkin tidak optimal. Beberapa penelitian telah menunjukkan bahwa peningkatan ini menjadi 64 atau lebih tinggi dapat meningkatkan kinerja. Hal itu juga bisa memperburuk keadaan, tergantung pada beban kerja, pembuatan dan model SAN, tata letak disk, dll. Jadi uji tuntas jika Anda bisa. Beberapa admin penyimpanan menolak memodifikasi kedalaman antrian HBA karena mereka mengira semua orang menginginkan hal yang sama di server mereka dan array penyimpanan akan dibanjiri. Dan mereka benar, juga membujuk mereka bahwa itu hanya untuk Anda. Berjanjilah untuk tidak memberi tahu orang lain. Terserah. Dapatkan kedalaman antrian ekstra jika Anda pikir ini akan menguntungkan kinerja. Terlalu Banyak Server Ketika sebuah perusahaan mengeluarkan uang kecil di jaringan area penyimpanan, mereka ingin mendapatkan nilai uang. Jadi tentu saja, setiap server baru yang disertakan akan terhubung sehingga bisa memanfaatkan semua ruang disk yang indah itu. Ini baik-baik saja sampai beberapa server mulai mengeluarkan banyak permintaan IO dan pengguna lainnya mengeluhkan perlambatan kinerja. Ini adalah sesuatu yang saya lihat berulang kali pada begitu banyak klien, dan tidak ada solusi mudah. Perusahaan tidak mau atau tidak mampu membeli SAN lain. Jika Anda pikir ini adalah masalah bagi Anda, letakkan jadwal bersama semua pekerjaan - di semua server - dan cobalah menjadwal ulang beberapa agar beban kerja didistribusikan lebih merata. Partisi Alignment dan Formatting Saya akan secara singkat menyebutkan keselarasan partisi, walaupun Windows 2008 menggunakan offset default 1MB jadi ini lebih sedikit masalah daripada dulu. Saya juga tidak yakin bahwa banyak SAN modern banyak mendapatkan keuntungan dari latihan ini. Saya melakukan tes pada EVA beberapa tahun yang lalu dan hanya menemukan 2 perbaikan. Meski begitu, beberapa persen masih patut diupayakan. Sayangnya Anda harus merobohkan volume Anda dan menciptakan kembali partisi Anda jika ini harus diperbaiki pada sistem yang ada. Ini mungkin tidak sebanding dengan kerumitan kecuali Anda berusaha untuk setiap inci terakhir dari kinerja. Memformat adalah sesuatu yang lain yang harus dilakukan dengan benar. SQL Server menyimpan data di halaman 8KB, namun ini diambil di blok 8, disebut luasan. Jika disk diformat dengan unit alokasi 64KB, ini bisa memiliki keuntungan kinerja yang signifikan. Multipathing Jika Anda tidak menggunakan disk lokal maka Anda harus memiliki beberapa redundansi yang terpasang pada subsistem penyimpanan Anda. Jika Anda memiliki SAN, Anda memiliki jaringan HBA, fabric, switch, dan pengendali yang rumit antara SQL Server dan disk. Harus ada setidaknya dua HBA, switch, dll dan ini semua harus dihubungkan bersama sedemikian rupa sehingga ada banyak jalur ke disk. Redundansi ini terutama untuk ketersediaan tinggi, namun jika multipathing telah dikonfigurasikan sebagai active aktif, Anda mungkin akan melihat manfaat kinerja juga. Network Attached Storage Karena SQL Server 2008 R2 telah memungkinkan untuk membuat, mengembalikan atau melampirkan database pada file share. Ini memiliki sejumlah kemungkinan penggunaan, dan terutama untuk lingkungan yang paling buruk, hal ini dapat membuat manajemen kapasitas menjadi lebih mudah, dan membuat basis data bergerak antar server lebih cepat. Pertanyaan yang harus ditanyakan, bagaimanapun, adalah quotDo Anda benar-benar menginginkan ini dalam productionquot Performance tidak akan sebagus drive lokal atau SAN. Ada komponen tambahan dalam rantai, jadi keandalan mungkin tidak sebaik. Dan dengan menggunakan jaringan, data Anda menggunakan infrastruktur yang sama seperti lalu lintas TCPIP lainnya, yang sekali lagi dapat mempengaruhi kinerja. Tapi ada kabar baik Sementara ketersediaan masih menjadi kekhawatiran, perbaikan di SMB pada Windows Server 2012 (dan melalui update ke WIndows Server 2008 R2) telah membuatnya semakin cepat. Saya melihat sebuah kutipan dari karyawan Microsoft di suatu tempat yang mengklaim kinerja penyimpanan lokal. Saya tidak dapat menemukan kutipannya sekarang, dan saya tidak ingat apakah dia mengukur latency atau throughput. Fragmentasi Disk Seberapa sering Anda menggunakan alat Disk Defragmenter pada PC Anda untuk menganalisis dan defragmentasi drive C Anda? Seberapa sering Anda memeriksa fragmentasi pada disk pada Server SQL Anda Bagi kebanyakan orang yang tidak pernah berada di tempat yang dekat, taruhan yang buruk. Namun fragmentasi volume sama merugikan kinerja SQL Server seperti pada PC Anda. Anda dapat mengurangi kemungkinan fragmentasi disk dengan beberapa cara: Data dan file log pra-ukuran, daripada mengandalkan pertumbuhan otomatis Tetapkan peningkatan pertumbuhan otomatis ke nilai yang masuk akal daripada default 10 Hindari penyusutan data dan file log Jangan pernah, Pernah menggunakan opsi database autoshrink Pastikan disk didedikasikan untuk SQL Server dan tidak dibagi dengan aplikasi lain Anda dapat memeriksa fragmentasi menggunakan alat yang sama seperti pada PC Anda. Disk Defragmenter tersedia di semua versi server Windows. Cara lain untuk mengecek adalah melalui kelas Win32Volume di WMI. Bit PowerShell ini melaporkan fragmentasi persen file untuk semua volume pada server tertentu. Jika Anda memiliki fragmentasi yang signifikan ada beberapa cara untuk memperbaikinya. Pilihan pilihan saya adalah sebagai berikut, namun membutuhkan beberapa downtime. Menghentikan layanan SQL Backup file pada disk (terutama file MDF, ndf dan ldf - lebih aman daripada menyesal) Jalankan alat Disk Defragmenter Windows Jalankan layanan SQL Periksa log kesalahan untuk memastikan tidak ada kesalahan saat startup Jalankan CHECKDB terhadap semua database Kecuali tempdb). Ive pernah melihat alat defrag menyebabkan korupsi, tapi Anda tidak bisa terlalu berhati-hati Pilihan lain yang tidak memerlukan downtime adalah dengan menggunakan alat pihak ketiga seperti Diskeeper. Ini bisa sangat efektif untuk memperbaiki dan mencegah fragmentasi disk, namun memerlukan biaya dan menggunakan driver filter - lihat komentar saya di bawah ini. Driver Filter Driver filter adalah perangkat lunak yang berada di antara permintaan IO dan write to disk. Hal ini memungkinkan penulisan untuk diperiksa dan ditolak, dimodifikasi atau diaudit. Jenis driver filter yang paling umum dipasang oleh perangkat lunak anti-virus. Anda tidak ingin perangkat lunak anti-virus memeriksa setiap menulis ke file database Anda. Anda juga tidak ingin memeriksa backup Anda, atau menulis ke log kesalahan, atau jejak default. Jika Anda menginstal perangkat lunak AV, Anda dapat menentukan pengecualian. Kecualikan semua folder yang digunakan oleh SQL Server, ditambah drive yang digunakan oleh data dan file log, ditambah folder yang digunakan untuk backup. Yang lebih baik lagi adalah mematikan pemeriksaan AV online, dan jadwalkan pemindaian pada waktu yang sepi. OLTP dan BI di Server Sama Sangat jarang menemukan sistem yang murni OLTP. Sebagian besar akan memiliki semacam elemen pelaporan juga. Sayangnya, dua jenis beban kerja tidak selalu hidup berdampingan dengan bahagia. Saya telah membaca banyak artikel oleh Joe Chang, dan dalam satu artikel dia menjelaskan mengapa hal ini terjadi. Pada dasarnya, rencana permintaan OLTP mengambil baris dalam batch kecil (kurang dari ambang 25 baris) dan permintaan IO ini ditangani secara sinkron oleh mesin database, yang berarti mereka menunggu data diambil sebelum melanjutkan. Beban kerja dan kueri MT yang besar, seringkali dengan rencana paralel, mengeluarkan permintaan IO asinkron dan memanfaatkan sepenuhnya kemampuan HBA untuk mengantri permintaan. Akibatnya, permintaan OLTP harus mengantri di belakang permintaan BI, menyebabkan kinerja OLTP menurun secara signifikan. Auto-grow dan Inisialisasi File Instan Sangat baik untuk mengaktifkan auto-tumbuh, sama seperti tindakan pencegahan, meskipun Anda juga harus mencari data dan file log lama sehingga jarang dibutuhkan. Namun, apa yang terjadi jika file data tumbuh dan Anda tidak memiliki inisialisasi file instan yang diaktifkan Terutama jika auto-grow disetel terlalu besar. Semua IO terhadap file tersebut harus menunggu agar file tersebut berkembang, dan ini mungkin dilaporkan dalam kuota terkenal yang diambil lebih dari 15 detik untuk menyelesaikan pesan yang ada di log kesalahan. Inisialisasi Instan tidak akan membantu pertumbuhan log, jadi pastikan log auto-growth increment tidak terlalu tinggi. Untuk informasi lebih lanjut tentang inisialisasi file instan dan bagaimana cara mengaktifkannya, lihat link ini Database File Initialization. Dan sementara pada subjek auto-tumbuh, lihat bagian pengisian proporsional, di bawah ini. Kinerja Log Transaksi Berapa lama log transaksi Anda menulis kurang dari 1ms Lebih dari 5ms Lihatlah statistik file virtual, penghitung kinerja, atau waktu tunggu WRITELOG untuk melihat apakah log tulis latency menjadi masalah bagi Anda. Menulis ke log transaksi berurutan, sehingga kepala tulis pada disk idealnya berada di tempat asal log terakhir. Ini berarti tidak ada waktu mencari, dan cepat cepat menulis kali. Dan karena transaksi tidak dapat dilakukan sampai log mengeras ke disk, Anda bergantung pada penulisan cepat ini untuk sistem performant. Saran selama bertahun-tahun adalah untuk log transaksi untuk setiap database berada di disknya sendiri. Dan saran ini masih bagus untuk disk lokal, dan untuk beberapa array penyimpanan. Tapi sekarang banyak SAN memiliki cache tulis yang didukung baterai mereka sendiri, saran ini tidak sepenting dulu. Asalkan cache cukup besar untuk mengatasi ledakan aktivitas menulis (dan melihat komentar saya sebelumnya tentang mengalokasikan lebih banyak cache untuk ditulis daripada dibaca), Anda akan mendapatkan latency yang sangat rendah. Jadi bagaimana jika Anda tidak memiliki kemewahan dari dolar mega-dolar dan banyak cache tulis Kemudian saran yang ada sejak tahun 1990an masih berlaku: Satu file log transaksi per database pada drive sendiri RAID 1, RAID 10 atau RAID 01 Jadi dengan asumsi Anda puas dengan tata letak file log Anda, apa lagi yang bisa memperlambat log Anda menulis File Log Virtual Meskipun log transaksi ditulis secara berurutan, file itu sendiri dapat terfragmentasi secara internal. Saat pertama kali dibuat itu terdiri dari beberapa potongan yang disebut virtual log files. Setiap kali tumbuh, baik secara manual maupun otomatis, beberapa file log virtual ditambahkan. Log transaksi yang tumbuh beberapa kali bisa berakhir dengan ribuan file log virtual. Memiliki terlalu banyak VLF dapat memperlambat penebangan dan mungkin juga memperlambat backup log. Anda juga perlu berhati-hati untuk menghindari VLF yang terlalu besar. Berkas log virtual tidak aktif tidak dihapus sampai akhir tercapai dan yang berikutnya mulai digunakan. Untuk model recovery penuh, hal ini tidak terjadi sampai backup log berikutnya. Jadi cadangan log akan tiba-tiba memiliki lebih banyak pekerjaan, dan dapat menyebabkan masalah kinerja saat terjadi. Jawaban untuk log transaksi besar adalah dengan menetapkan ukuran awal maksimal 8000MB, dan kemudian secara manual tumbuh dalam potongan 8000MB sampai ukuran target. Hal ini menghasilkan ukuran VLF maksimum 512MB, tanpa membuat jumlah VLF yang terlalu banyak. Catatan: saran ini hanya untuk pertumbuhan manual. Jangan otomatis tumbuh dengan 8000MB Semua transaksi di database akan berhenti sementara ruang ekstra diinisialisasi. Autogrow harus jauh lebih kecil - namun cobalah untuk ukuran file secara manual agar auto grow tidak mungkin dibutuhkan. Batas Manajer Log Mesin database menetapkan batasan jumlah log yang dapat di flight pada satu waktu. Ini adalah batas per-database, dan bergantung pada versi SQL Server yang digunakan. SQL Server membatasi jumlah IO dan MB yang beredar per detik. Batasnya berbeda dengan versi dan apakah 32 bit atau 64 bit. Lihat Mendiagnosis Masalah Kinerja dan Batas Log Log dari Manajer Log untuk lebih jelasnya. Inilah sebabnya mengapa latensi tulis harus serendah mungkin. Jika dibutuhkan 20ms untuk menulis ke log transaksi, dan Anda dibatasi untuk 32 IO dalam penerbangan pada satu waktu, itu berarti maksimal 1600 transaksi per detik, jauh di bawah banyak database dengan volume tinggi yang dibutuhkan OLTP. Hal ini juga menekankan pentingnya menjaga ukuran transaksi tetap kecil, karena satu transaksi yang sangat besar dapat menahan transaksi lainnya saat melakukan transaksi. Jika menurut Anda batasan ini mempengaruhi kinerja menulis log di database Anda, ada beberapa cara untuk mengatasi masalah ini: Bekerja pada peningkatan kinerja menulis log Jika Anda memiliki operasi dengan minimal login, Anda dapat mengalihkan database untuk menggunakan model pemulihan BULK LOGGED. Hati-hati meskipun - cadangan log yang berisi operasi minimal login harus dipulihkan secara penuh. Point in restore tidak mungkin dilakukan. Split database volume tinggi menjadi 2 atau lebih database, karena batas log berlaku per database Aktivitas Log Non-Sequential Ada tindakan yang dilakukan oleh mesin database yang memindahkan kepala tulis dari akhir file log. Jika transaksi masih dilakukan saat ini terjadi, Anda memiliki kinerja overhead dan log yang memburuk. Operasi yang dibaca dari file log mencakup rollback transaksi besar, backup log dan replikasi (agen pembaca log). Ada sedikit yang dapat Anda lakukan tentang sebagian besar, tapi menghindari rollbacks besar adalah sesuatu yang harus ditangani pada tahap desain dan pengembangan aplikasi. Isi Proporsional Tabel sangat aktif dapat ditempatkan pada kelompok file yang memiliki beberapa file data. Hal ini dapat meningkatkan kinerja baca jika disk fisik berbeda, dan dapat meningkatkan kinerja penulisan dengan membatasi pertengkaran di halaman alokasi (terutama berlaku untuk tempdb). Anda kehilangan beberapa keuntungan, meskipun, jika Anda tidak memanfaatkan algoritma pengisian proporsional. Isi proporsional adalah proses dimana database mencoba mengalokasikan halaman baru sebanding dengan jumlah ruang kosong pada setiap file data dalam kelompok file. Untuk mendapatkan manfaat maksimal pastikan setiap file berukuran sama, dan selalu ditumbuhkan dengan increment yang sama. Ini untuk pertumbuhan manual dan auto. Satu hal yang harus diperhatikan adalah bagaimana pertumbuhan otomatis bekerja. SQL Server melakukan yang terbaik untuk mengisi file dengan kecepatan yang sama, tapi satu akan selalu terisi sesaat sebelum yang lain, dan file ini kemudian akan otomatis tumbuh dengan sendirinya. Ini kemudian mendapatkan alokasi halaman yang lebih baru daripada yang lain dan menjadi hotspot sementara sampai yang lain juga otomatis tumbuh dan mengejar ketinggalan. Hal ini tidak mungkin menyebabkan masalah bagi sebagian besar database, walaupun untuk tempdb mungkin lebih terlihat. Trace flag 1117 menyebabkan semua file data dalam kelompok file tumbuh bersama, jadi perlu dipertimbangkan apakah ini adalah masalah bagi Anda. Secara pribadi saya lebih suka mengukur ukuran file secara manual sehingga tidak diperlukan pertumbuhan otomatis. Konfigurasi tempdb Mari kita mulai dengan beberapa hal yang semua orang setuju: file tempdb harus ditempatkan pada penyimpanan tercepat yang tersedia. Lokal SSD sangat ideal, dan dari SQL Server 2012 ini bahkan dimungkinkan di cluster Pre-size data dan file log, karena pertumbuhan otomatis dapat menyebabkan masalah kinerja saat terjadi. Benda-benda sementara baru diciptakan setiap saat, jadi pertengkaran di GAM , Halaman SGAM dan PFS mungkin menjadi masalah di beberapa lingkungan dan sekarang ada beberapa perbedaan pendapat: Ada banyak saran di seluruh web untuk membuat satu file data tempdb per inti untuk mengurangi pertaruhan alokasi. Paul Randall tidak setuju (Mitos SQL Server DBA sehari: (1230) tempdb harus selalu memiliki satu file data per inti prosesor). Dia mengatakan bahwa terlalu banyak file sebenarnya bisa memperburuk keadaan. Solusinya adalah membuat lebih sedikit file dan untuk meningkatkan hanya jika diperlukan Ada lebih banyak saran, sering diulang, untuk memisahkan file tempdb dari database lain dan meletakkannya di spindle fisik mereka sendiri. Joe Chang tidak setuju dan memiliki argumen yang sangat bagus untuk menggunakan kumpulan disk biasa. (Penempatan data, log dan temp file). Saya akan memutuskan untuk memutuskan apa yang harus dilakukan AutoShrink Opsi basis data AutoShrink telah ada sejak saya mulai menggunakan SQL Server, menyebabkan banyak masalah kinerja bagi orang-orang yang telah mengaktifkannya tanpa menyadari sepenuhnya apa yang dilakukannya. Seringkali aplikasi pihak ketiga akan menginstal database dengan opsi ini diaktifkan, dan DBA mungkin tidak menyadarinya sampai nanti. Jadi mengapa itu buruk Dua alasan: Selalu digunakan bersamaan dengan pertumbuhan otomatis, dan siklus terus tumbuh-menyusut-tumbuh menyebabkan sejumlah besar fragmentasi disk fisik. Sudah saya bahas topik tadi di artikel ini. Sementara melakukan shrink ada banyak tambahan IO, yang memperlambat sistem untuk segala hal lainnya Nonaktifkan itu. Alokasikan cukup ruang untuk data dan file log, dan ukuran mereka sesuai. Dan jangan lupa untuk memperbaiki semua fragmentasi itu saat Anda melakukannya. Memori Tidak Cukup Ini adalah artikel tentang kinerja SQL Server IO, bukan memori. Jadi saya tidak ingin membahasnya secara rinci di sini - itu adalah subjek untuk artikel yang berbeda. Saya hanya ingin mengingatkan Anda bahwa SQL Server menyukai memori - semakin baik. Jika seluruh database Anda sesuai dengan ingatan, Anda akan memiliki sistem yang jauh lebih cepat, melewati semua IO yang lambat itu. Kekurangan memori dapat menyebabkan halaman kotor disiram ke disk lebih sering untuk memberi ruang bagi lebih banyak halaman yang dibaca. Kekurangan memori juga dapat menyebabkan peningkatan tempdb IO, karena lebih banyak worktables untuk operasi sort dan hash harus dilipat ke disk. Bagaimanapun, intinya bagian ini benar-benar membuat satu pernyataan: Isi server Anda dengan memori sebanyak yang Anda mampu, dan sebanyak mungkin edisi SQL Server dan Windows bisa diatasi. SQL Server 2014 memiliki fitur baru yang memungkinkan beberapa tabel dipertahankan dalam memori, dan diakses melalui prosedur tersimpan yang tersimpan secara native. Beberapa perancangan ulang beberapa kode yang ada mungkin diperlukan untuk memanfaatkan ini, namun sepertinya ada peningkatan kinerja yang hebat untuk sistem OLTP yang mulai menggunakannya. Penggunaan tempdb tempdb yang tinggi dapat menjadi konsumen utama IO dan dapat mempengaruhi kinerja keseluruhan jika digunakan secara berlebihan. Perlu melihat berbagai alasan penggunaannya, dan memeriksa sistem Anda untuk memastikan Anda meminimalkan hal ini sejauh mungkin. Objek sementara yang dibuat pengguna Yang paling umum adalah tabel sementara, variabel tabel dan kursor. Jika ada tingkat penciptaan yang tinggi, hal ini dapat menyebabkan pertengkaran halaman alokasi, walaupun meningkatkan jumlah file data tempdb sebagian dapat mengurangi hal ini. Proses pembuatan tabel sementara yang sangat besar atau variabel tabel adalah tidak besar, karena ini bisa menyebabkan banyak IO. Objek Internal Mesin database membuat tabel kerja di tempdb untuk penanganan hash bergabung, menyortir dan spooling dari set hasil menengah. Ketika operasi semacam atau hash bergabung membutuhkan lebih banyak memori daripada yang telah diberikan, mereka menumpahkan ke disk (menggunakan tempdb) dan Anda akan melihat peringatan Hash dan Mengurutkan peringatan dalam jejak default. Awalnya saya menulis beberapa paragraf tentang bagaimana dan mengapa hal ini terjadi dan apa yang dapat Anda lakukan untuk mencegahnya, tapi kemudian saya menemukan posting ini yang menjelaskannya dengan lebih baik - Memahami Hash, Sort and Exchange Spill Events. Versi Store Penggunaan ketiga tempdb adalah untuk toko versi. Ini digunakan untuk versi baris. Versi baris dibuat saat isolasi snapshot atau opsi snapshot yang dilakukan sebelumnya digunakan. Mereka juga dibuat saat indeks online dibangun kembali untuk pembaruan dan penghapusan yang dilakukan selama pembuatan ulang dan untuk penanganan modifikasi data ke beberapa kumpulan hasil aktif (MARS). Aplikasi yang ditulis dengan buruk (atau pengguna nakal) melakukan pembaruan besar yang mempengaruhi ribuan baris ketika tingkat isolasi berdasarkan versi baris yang digunakan dapat menyebabkan pertumbuhan yang cepat dalam tempdb dan berdampak buruk pada kinerja pengguna lain. Tabel dan Pemindaian Indeks Pemindaian tabel adalah pemindaian tumpukan. Scan indeks adalah pemindaian indeks berkerumun atau non-cluster. Keduanya mungkin pilihan terbaik jika indeks penutup tidak ada dan banyak baris kemungkinan akan diambil. Pemindaian indeks berkerumun melakukan lebih baik daripada pemindaian meja - namun ada alasan lain untuk menghindari tumpukan Tetapi apa yang menyebabkan pemindaian digunakan di tempat pertama, dan bagaimana Anda bisa mencari lebih banyak statistik di luar tanggal Sebelum memeriksa indeks dan kode, buatlah Yakin bahwa statistik up to date Aktifkan quotauto create statisticsquot. Jika kuotauto update statisticsquot tidak diaktifkan pastikan Anda menjalankan update statistik manual secara teratur. Ini adalah ide bagus bahkan jika statistik kuotaotomatis diperbarui, karena ambang batas sekitar 20 baris yang berubah sebelum pembaruan otomatis dimulai sering tidak cukup, terutama bila baris baru ditambahkan dengan tombol naik. Pilihan Indeks Terkadang indeks yang ada tidak digunakan. Lihat peningkatan selektivitasnya, mungkin dengan menambahkan kolom tambahan, atau memodifikasi urutan kolom. Pertimbangkan apakah indeks penutup bisa dibuat. Pencarian lebih mungkin dilakukan jika pencarian bookmark tidak diperlukan. Lihat posting ini di pointquot quottipping oleh Kimberly Tripp. Titik Tipping. TSQL yang tidak efisien Cara query ditulis juga bisa menghasilkan pemindaian, walaupun ada indeks yang berguna. Beberapa alasan untuk hal ini adalah: Ungkapan yang tidak dapat ditawar dalam klausa WHERE. Quotsargquot berarti Simple ARGument. Jadi pindahkan perhitungan dari kolom dan ke konstanta sebagai gantinya. Jadi misalnya, ini tidak akan menggunakan indeks pada OrderDate: WHERE DATEADD (HARI 1. OrderDate) gt GETDATE () Sedangkan ini akan menggunakan indeks jika ada (dan cukup selektif): WHERE OrderDate gt DATEADD (DAY. - 1. GETDATE ()) Konversi secara implisit dalam kueri juga dapat mengakibatkan pemindaian. Lihat posting ini oleh Jonathan Kehayias Implisit Konversi yang menyebabkan Indeks Scans. Parameter Sniffing Parameter Sniffing mengendus adalah hal yang baik. Hal ini memungkinkan rencana kembali digunakan dan meningkatkan kinerja. Tapi terkadang hal itu menghasilkan rencana eksekusi yang kurang efisien untuk beberapa parameter. Pemeliharaan Indeks Setiap indeks harus dipertahankan. Saya tidak membicarakan rencana perawatan, tapi tentang fakta bahwa ketika baris dimasukkan, dihapus dan diperbarui, indeks non-clustered juga harus diubah. Ini berarti IO tambahan untuk setiap indeks di atas meja. Jadi, ada kesalahan untuk memiliki lebih banyak indeks daripada yang Anda butuhkan. Periksa apakah semua indeks sedang digunakan. Periksa duplikat dan indeks berlebihan (di mana kolom di kolom adalah subkumpulan kolom di kolom lain). Periksa indeks dimana kolom pertama identik tapi sisanya tidak - terkadang ini bisa digabungkan. Dan tentu saja, tes, tes, tes. Index Fragmentasi Index fragmentasi mempengaruhi kinerja IO dengan beberapa cara. Pemindaian jarak jauh kurang efisien, dan kurang mampu memanfaatkan baca baca. Ruang kosong yang dibuat di halaman mengurangi kepadatan data, yang berarti lebih banyak membaca IO diperlukan Fragmentasi itu sendiri disebabkan oleh pemisahan halaman, yang berarti lebih banyak menulis IO. Ada beberapa hal yang bisa dilakukan untuk mengurangi dampak fragmentasi, atau untuk mengurangi jumlah fragmentasi. Membangun kembali atau mengatur ulang indeks secara teratur Tentukan faktor pengisian yang lebih rendah sehingga perpecahan halaman terjadi lebih jarang (meski tidak terlalu rendah, lihat di bawah) Mengubah indeks berkerumun untuk menggunakan tombol naik sehingga baris baru ditambahkan sampai akhir, bukan disisipkan ke dalam Tempat acak di tengah Catatan Teruskan Ketika sebuah baris di tumpukan diperbarui dan membutuhkan lebih banyak tempat, maka akan disalin ke halaman baru. Tapi indeks non-clustered tidak diperbarui untuk mengarah ke halaman baru. Sebagai gantinya, pointer ditambahkan ke halaman awal untuk menunjukkan lokasi yang telah dipindahkan oleh baris. Ini disebut forwarding pointer, dan mungkin ada rantai panjang dari pointer ini untuk dilalui untuk menemukan data akhirnya. Tentu, ini berarti lebih banyak IO. Tumpukan tidak bisa di defragmentasi dengan membangun kembali indeks (tidak ada). Satu-satunya cara untuk melakukannya adalah dengan membuat indeks berkerumun di tumpukan, dan kemudian menjatuhkannya setelah itu. Sadarilah bahwa ini akan menyebabkan semua indeks non-clustered dibangun kembali dua kali - sekali untuk indeks berkerumun baru, dan sekali lagi ketika dijatuhkan. Jika ada banyak, ini adalah ide bagus untuk menurunkan indeks non-clustered terlebih dahulu, dan menciptakannya kembali setelah itu. Sebaiknya hindari tumpukan bila memungkinkan. Saya menerima mungkin ada kasus di mana mereka adalah pilihan yang lebih efisien (memasukkan ke dalam tabel arsip, misalnya), tapi selalu pertimbangkan apakah indeks berkerumun akan menjadi pilihan yang lebih baik - biasanya begitu. Ruang Terbuang Di dunia ideal setiap halaman data pada disk (dan di memori) akan 100 penuh. Ini berarti minimum IO diperlukan untuk membaca dan menulis data. Dalam prakteknya, ada ruang terbuang di hampir semua halaman - terkadang sangat tinggi - dan ada banyak alasan mengapa hal ini terjadi. Faktor isi rendah sudah saya sebutkan faktor pengisian sudah. Jika terlalu tinggi, dan perpecahan halaman terjadi saat baris dimasukkan atau diperbarui, masuk akal untuk membangun kembali indeks dengan faktor isi lebih rendah. Namun, jika faktor isi terlalu rendah, Anda mungkin memiliki banyak ruang terbuang di halaman database, sehingga menghasilkan lebih banyak IO dan memori. Inilah salah satu contohnya dan skenario lihat. Terkadang kompromi dibutuhkan. Perpecahan halaman Hal ini juga dibahas di atas. Tapi begitu juga fragmentasi, perpecahan halaman juga bisa mengakibatkan terbuangnya ruang jika ruang kosong tidak digunakan kembali. Solusinya adalah dengan melakukan defragment dengan membangun kembali atau mereorganisasi indeks secara teratur. Pilihan Limbah Tipe Data Gunakan tipe data terkecil yang Anda bisa. Dan coba hindari tipe data panjang tetap, seperti CHAR (255), kecuali jika Anda secara teratur memperbarui ke panjang terpanjang dan ingin menghindari pemisahan halaman. Alasannya sederhana saja. Jika Anda hanya menggunakan 20 karakter dari 200, itu adalah 90 ruang terbuang, dan lebih banyak IO sebagai hasilnya. Semakin tinggi kerapatan data per halaman semakin baik. Pemikiran malas membuat pengembang membuat AddressLine1, AddressLine2, dan sebagainya sebagai CHAR (255), karena mereka sebenarnya tidak tahu apa yang paling lama seharusnya. Dalam hal ini, lakukan beberapa penelitian, cari tahu yang terpanjang adalah 50 karakter (misalnya) dan kurangi CHAR (50), atau gunakan tipe data panjang variabel. Desain Skema Ive sudah menyebutkan pilihan tipe data di atas, namun ada beberapa skema pilihan desain lainnya yang dapat mempengaruhi jumlah IO yang dihasilkan oleh database aplikasi. Yang paling umum adalah merancang tabel yang terlalu lebar. Kadang saya melihat tabel dengan 20, 30, 50, bahkan 100 kolom. 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. Window Functions (OVER Clause)Help Make a Difference If I had to name one concept in standard SQL that I thought was the most important one, and that is worth Microsoftrsquos investment for future versions of SQL Server, Irsquod say window functions, hands down, without a doubt. Window functions are a subset of what the standard calls set functions, meaning, functions that are applied to a set of rows. The term window is used to describe the set of rows that the function operates on, and the language provides a clause called OVER where you provide the window specification. So whatrsquos the big deal, and what makes window functions more important than other features that are missing in SQL Server There are so many reasonshellip But first Irsquoll give a bit more background about window functions, and then Irsquoll get to the reasons and demonstrate use caseshellip First, to clarify, SQL Server 2005 already introduced some support for window functionsmdashthe ranking calculations: ROWNUMBER, RANK, DENSERANK and NTILE, and partial support for window aggregate functions with only the partitioning part implemented. SQL Server 2005 was a great release for developers with so many cool and practical T-SQL features. The number of solutions that I simplified and optimized just with the ROWNUMBER function and CTEs is amazing. Still, there are many standard features related to window functions that SQL Server didnrsquot yet implement (as of SQL Server 2008 R2) and that can help address quite a wide variety of business problems with simpler and more efficient solutions. These days the next major release of Microsoft SQL Servermdashversion 11mdashis being developed. These are pivotal days for candidate features where decisions are made whether they will or will not make it to the final release. And even though I think that more complete support for window functions is so important to developers and to the success of SQL Server, Irsquom not sure at all that we will see those in the product. This is time for us as part of the SQL Server community to express our strong opinion. Hopefully Microsoft will realize how important it is for us to have those features in the product, as well as to show that the SQL Server communityrsquos opinion matters. In this article I will explain some of the key features that are missing in SQL Server and why itrsquos important to add support for such features. If you share my opinion, and havenrsquot done so already, you can cast your vote in the following feature request items: Like with any thing in life that yoursquore not aware of, you donrsquot know how it can help you if you donrsquot know that it exists. My feeling is that many developers are not really aware of the capabilities of the standard window functions and therefore Microsoft doesnrsquot see a lot of demand for it. Education and raising the topic to peoplersquos awareness is therefore key to the realization of the benefits, and as a consequence, encourage people to ask Microsoft for more support. The unfortunate part is that all of SQL Serverrsquos leading competitors including Oracle, DB2 and Teradata for some time now already have a far more complete support for window functions. So even though my focus and passion is for SQL Server, I sometimes find myself in the awkward situation of demoing standard SQL window functions on Oracle when teaching or presenting. So whatrsquos missinghellip The most important missing features are probably ordering and framing options for window aggregate functions. Other key features that are still missing are distribution and offset functions, and reusability of window definitions. More details shortly. Why are window functions so powerful SQL is often referred to as a set-based language. The reason is that the language is based on the relational model, which in turn is based, in part, on mathematical set theory. When writing SQL queries yoursquore supposed to deal with a table (or relation, which is a set) as a whole, as opposed to the tablersquos individual rows. Also, since sets have no order, yoursquore not supposed to make any assumptions in regards to the physical ordering of the data. The reality is that for many developers set-based thinking is far from being intuitive, and it can take a few good years to truly think in SQL terms. This is why often developers tend to use cursorsmdashbecause using those feel like an extension to what they already know. Cursors allow you to deal with one row at a time, and also rely on specified order of the data. Window functions have an ingenious design. They do operate on sets, or windows, while allowing you to indicate ordering as part of the calculation where relevant. Not to confuse with cursors, window functions allow defining ordering for the calculation without making any expectations in regards to ordering of the input data given to the query or the output coming out of the query. In other words, no relational concepts are violated. Ordering is only part of the specification of the calculation. Similarly, other common elements in querying problems, like partitioning, framing of applicable rows, are all intuitive parts of the window specification. So in a sense, I see window functions as bridging the big gap that exists between cursoriterative and set-based thinking. Now, thatrsquos a lot of words before showing even one example. So letrsquos look at a few more concrete examples of some of the missing featureshellip Most of the examples Irsquoll show are against a database called InsideTSQL2008. You can find the script creating it here: InsideTSQLbookssourcecodeInsideTSQL2008.zip. In addition, the following view will be used in some of the examples: SET NOCOUNT ON USE InsideTSQL2008 GO IF OBJECTID ( 39Sales. EmpOrders39. 39V39 ) IS NOT NULL DROP VIEW Sales. EmpOrders GO CREATE VIEW Sales. EmpOrders WITH SCHEMABINDING AS SELECT O. empid , DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) AS ordermonth. SUM (OD. qty ) AS qty , CAST ( SUM (OD. qty OD. unitprice (1 - discount )) AS NUMERIC (12. 2 )) AS val , COUNT () AS numorders FROM Sales. Orders AS O JOIN Sales. OrderDetails AS OD ON OD. orderid O. orderid GROUP BY empid. DATEADD ( month. DATEDIFF ( month. 0. O. orderdate ), 0 ) GO Ordering and Framing for Window Aggregate Functions As mentioned, currently window aggregate functions support only a partitioning element. Whatrsquos missing are ordering and framing options. The standard supports an ORDER BY clause to define ordering in the window and ROWS and RANGE clauses that frame the window based on the defined ordering. A classic example that would benefit from ordering and framing is running totals. Consider the following Accounts table definition: CREATE TABLE dbo. Accounts ( actid INT NOT NULL, -- partitioning column tranid INT NOT NULL, -- ordering column val MONEY NOT NULL -- measure CONSTRAINT PKAccounts PRIMARY KEY (actid. tranid ) ) The table represents deposit (positive value) and withdrawal (negative value) transactions in bank accounts. You need to calculate at each point what the account balance was. Like with many querying problems therersquos a partitioning element (actid), ordering element (tranid), and a measure that the calculation applies to (val). Window aggregate functions in standard SQL support all three elements. Herersquos how you would express the query calculating the balance at each point for each account: SELECT actid. tranid. val , SUM (val ) OVER ( PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS balance FROM dbo. Accounts You can achieve such calculations today in SQL Server using a subquery or a join: -- Set-Based Solution Using Subqueries SELECT actid. tranid. val , ( SELECT SUM (S2.val ) FROM dbo. Accounts AS S2 WHERE S2.actid S1.actid AND S2.tranid lt S1.tranid ) AS balance FROM dbo. Accounts AS S1 -- Set-Based Solution Using Joins SELECT S1.actid. S1.tranid. S1.val , SUM (S2.val ) AS balance FROM dbo. Accounts AS S1 JOIN dbo. Accounts AS S2 ON S2.actid S1.actid AND S2.tranid lt S1.tranid GROUP BY S1.actid. S1.tranid. S1.val But besides the fact that these solutions are not as straightforward and intuitive as the one using a window function, therersquos a big problem with the way SQL Server currently optimizes the subquery and join solutions. Assuming you defined a covering index on the partitioning column, followed by the ordering column, and including the aggregated measure, for each row SQL Server will scan all rows with the same partitioning value and an ordering value that is less than or equal to the current. Given p partitions with r rows in average, and fairly even distribution of rows in partitions, the total number of rows processed in such a plan is pr p(r r2)2. This means that in respect to the partition size, the algorithmic complexity, or scaling, of the solution s quadratic (N2). Thatrsquos bad. The window function form lends itself to good optimization, especially with the fast track case like the above (rows between unbounded preceding and current row). It should be straightforward to the optimizer to optimize this query with one ordered scan of the index, translating to simply pr rows being scanned. Another example for running totals is querying a table called EmpOrders with a row for each employee and month, and calculating the cumulative performance for each employee and month in other words, the total value for the employee from the beginning of hisher activity until the current month. Herersquos how you would express it with a window aggregate: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runqty FROM Sales. EmpOrders empid ordermonth qty runqty ----------- ----------------------- ----------- ----------- 1 2006-07-01 00:00:00.000 121 121 1 2006-08-01 00:00:00.000 247 368 1 2006-09-01 00:00:00.000 255 623 1 2006-10-01 00:00:00.000 143 766 1 2006-11-01 00:00:00.000 318 1084 . 2 2006-07-01 00:00:00.000 50 50 2 2006-08-01 00:00:00.000 94 144 2 2006-09-01 00:00:00.000 137 281 2 2006-10-01 00:00:00.000 248 529 2 2006-11-01 00:00:00.000 237 766 . There are many business examples where ordering and framing options can be useful besides calculating account balances. Those include inventory, running totals for reporting, moving averages, and so on. Herersquos an example for a query calculating the average of the last three recorded periods: SELECT empid. ordermonth , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS avglastthree FROM Sales. EmpOrders There are also various temporal querying problems where running totals serve part of the solution. For simplicity I showed examples where framing is based on the ROWS clause where you indicate an offset in terms of number of rows. The standard also supports a RANGE clause that allows indicating an offset in terms of values, such as time intervals, as in the following example returning the average of the last three months: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth RANGE INTERVAL 39239 MONTH PRECEDING ) AS sum3mqty FROM Sales. EmpOrders ORDER BY empid. ordermonth The SQL standard defines several offset functions that would make developersrsquo life so much easier compared to the tools available today for similar needs. Among the missing offset functions are LAG and LEAD, returning a value from a row in a given offset from the current row based on specified ordering. For example, the following query will return, for each current order, also the order date of the previous and next orders: SELECT custid. orderdate. orderid , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prvod , LEAD (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS nxtod FROM Sales. Orders custid orderdate orderid prvod nxtod ------- ----------- -------- ----------- ----------- 1 2007-08-25 10643 NULL 2007-10-03 1 2007-10-03 10692 2007-08-25 2007-10-13 1 2007-10-13 10702 2007-10-03 2008-01-15 1 2008-01-15 10835 2007-10-13 2008-03-16 1 2008-03-16 10952 2008-01-15 2008-04-09 1 2008-04-09 11011 2008-03-16 NULL 2 2006-09-18 10308 NULL 2007-08-08 2 2007-08-08 10625 2006-09-18 2007-11-28 2 2007-11-28 10759 2007-08-08 2008-03-04 2 2008-03-04 10926 2007-11-28 NULL . Notice how elegant and intuitive this form is. The default offset is one row, but you can also be explicit if you need an offset that is other than one row, e. g. three rows: SELECT custid. orderdate. orderid , LAG (orderdate. 3 ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS prv3od FROM Sales. Orders There are lots of business examples for the usefulness of these functions, like recency calculations, trend analysis, and others. Herersquos an example for a query addressing recency calculations, returning the difference in terms of days between the current and previous orders: SELECT custid. orderdate. orderid , DATEDIFF ( day , LAG (orderdate ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ), orderdate ) AS diff FROM Sales. Orders Other missing offset functions are FIRSTVALUE, LASTVALUE, returning the value from the first or last rows in the partition based on specified ordering. Herersquos an example returning the value of the first and last orders per customer with each order: -- FIRSTVALUE, LASTVALUE SELECT custid. orderdate. orderid. val , FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS valfirstorder , LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS vallastorder FROM Sales. OrderValues custid orderdate orderid val valfirstorder vallastorder ------- ----------- -------- ------- --------------- -------------- 1 2007-08-25 10643 814.50 814.50 933.50 1 2007-10-03 10692 878.00 814.50 933.50 1 2007-10-13 10702 330.00 814.50 933.50 1 2008-01-15 10835 845.80 814.50 933.50 1 2008-03-16 10952 471.20 814.50 933.50 1 2008-04-09 11011 933.50 814.50 933.50 2 2006-09-18 10308 88.80 88.80 514.40 . And herersquos an example calculating the difference between the current order value and the first and last: SELECT custid. orderdate. orderid. val , val - FIRSTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. orderid ) AS difffirst , val - LASTVALUE (val ) OVER ( PARTITION BY custid ORDER BY orderdate. ordered ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS difflast FROM Sales. OrderValues Standard SQL supports window distribution functions that performing statistical calculations. Specifically it supports the PERCENTRANK and CUMDIST functions, calculating a percentile rank and cumulative distribution. These functions give you a relative rank of a row in respect to other rows in the window partition, expressed as ratiopercent. The specific formulas used by the two variants are: PERCENTRANK: (RK-1)(NR-1), where RK rank, NR number of rows in partition CUMEDIST: NPNR, where NP number of rows preceding or peer with current row (same as next rank - 1) Herersquos an example using these functions: SELECT custid. COUNT () AS numorders , PERCENTRANK () OVER ( ORDER BY COUNT ()) AS percentrank , CUMEDIST () OVER ( ORDER BY COUNT ()) AS cumedist FROM Sales. Orders GROUP BY custid custid numorders percentrank cumedist ------- ---------- ------------ --------- 13 1 0.0000 0.0112 33 2 0.0114 0.0337 43 2 0.0114 0.0337 42 3 0.0341 0.1124 53 3 0.0341 0.1124 . 37 19 0.9545 0.9663 24 19 0.9545 0.9663 63 28 0.9773 0.9775 20 30 0.9886 0.9888 71 31 1.0000 1.0000 Reuse of Window Definition using WINDOW Clause Suppose you need to write several window functions that rely on the same window definition (or part of it). You will end up with a lot of repetition of code. Standard SQL has a clause called WINDOW that allows naming a window definition or part of it, making it reusable. For example, instead of: SELECT empid. ordermonth. qty , SUM (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runsumqty , AVG (qty ) OVER ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS runavgqty , FROM Sales. EmpOrders SELECT empid. ordermonth. qty , SUM (qty ) OVER W1 AS runsumqty , AVG (qty ) OVER W1 AS runavgqty , FROM Sales. EmpOrders WINDOW W1 AS ( PARTITION BY empid ORDER BY ordermonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) As you can see, with the WINDOW clause the code is shorter, more readable, and easier to maintain. I showed just part of the standard support for window functions that SQL Server is still missing. Therersquos more, like window frame exclusion. There are also other set functions not implemented, like ordered set functions, and so on. But here I wanted to make a point in hope that Microsoft will realize how important it is to add such support in SQL Server 11. If you feel so as well, help make a difference by voting for the items, write about the topic, talk about it, increasing peoplersquos awareness. Hopefully this request will find open ears. As a reminder, here are the open items for some of the requests for enhancements: Thank you for expressing so well what I would haveshould have written a long time ago. Personally a SQLSErver fan, I have had to become expert at Oracle to support our platform for the many customers who still think Oracle is better. While my gripes with Oracle would take many pages to begin to express, I DO like its full support of windows functions, especially LEAD and LAG. No reason to add to what has been already so eloquently said in this post. If there is a polling place anywhere to encourage Microsoft to fully support windows function in a future release, please direct me to it Is it too late for 2011 Looking at the OVER clause in BOL at the moment the enhancements seem entirely under whelming msdn. microsoften-uslibraryms18946128SQL.11029.aspx Please Log In or Register to post comments. Importing SQL Server Data Using SSIS - Which Option is Fastest By: Daniel Calbimonte Read Comments (27) Related Tips: More Integration Services Development This article is useful for SSIS developers who do not know which tasks are best to use in an SSIS projects. The main problem is that at the end of development if performance is slow then you will need to rebuild the project and change components. This article shows various ways of importing data and shows which types of components perform best within SSIS. The contest will be between the following components: ODBC Tasks ADO NET Tasks OLEDB Task SQL Server Destination T-SQL Tasks I created different SSIS packages in order to test performance. In this demo I used SSIS 2012 and the database Adventureworks 2012 . In this demo I am going to import the table AdventureWorks2012.Sales. SalesOrderDetail to the test2 database which is on the same instance of SQL Server. SalesOrderDetails is the table with more rows in AdventureWorks2012. In order to create the database test2 and the destination table dbo. OrderDetails, use this T-SQL code: Test 1 - ODBC Tasks The first example will use ODBC Source and ODBC Destination as shown below: When we run the package we notice the average time is 5 minutes 57 seconds to import the rows: Test 2 - ADO NET Tasks As noticed, ODBC is pretty slow. Lets try another approach. We are going to truncate the destination table first: Lets try ADO tasks to import the same data and verify if these components are faster: The average elapsed time in my testing was 11 seconds. This is much better. Test 3 - OLEDB Tasks This time we are going to import the same data using the OLEDB Tasks. Again we will truncate the table in the test2 database first. The average elapsed time is 5 seconds. Note that I am using the fast load option with the Table Lock option in the OLE DB Destination Task: If we do not use the fast load option, the average elapsed time was 2 minutes and 21 seconds: OK. The fast load option really improves performance. I will return to that configuration. What about the OLE DB Source. By default I am using the option Table or view in the OLE DB Source as shown below: Lets use a SQL Command instead as shown below. The average elapsed time is 2.85 seconds . Test 4 - SQL Server Destination Now, lets try to use the SQL Destination as the destination instead of OLE DB Destination: The average elapsed time is 2.5 seconds. At this point it is the best option. Test 5 - Execute T-SQL Task Finally, some people think that the best option is to use the Execute T-SQL Task: I am using a simple insert statement to import data from one source to another: The average elapsed time is 1.8 seconds Finally Ive been told that if the query runs inside a stored procedure it is even faster: Lets create a stored procedure: After creating the stored procedure we are going to call it in the Execute T-SQL Task: The average elapsed time is 2.12 seconds . The stored procedures does not improve performance. Lets review the table with the results: You may think the morale of the story is to use the Execute T-SQL Task instead of other SSIS tasks. In this example we were importing data on the same instance, but this will not always be the case. So the morale of the story is that there are many alternatives when creating a SSIS project and we have to carefully study the alternatives in different scenarios. There are great SSIS tools and we do not always use the best options. With each new version of SSIS new tasks are added and performance may be improved with existing tasks. The main changes in SSIS for SQL 2008 and 2012 are related to performance improvements. Next Steps If you are working in a SSIS project make sure you are using the best tasks and also verify if there are other SSIS tasks that can be used in your project. Also make sure you are following the best practices recommended by the experts: Last Update: 7132012 Great read and analysis, but I have one caveat to add. If you need to move a large amount of data, you need to take care of the transaction log growth. This is not a much of a concern using SSIS. For instance, I needed to move 1.3 billion rows (15 columns) and began using TSQL which quickly filled my logs. However, using OLE DB Source and Destination (Bulk Inserts) with fast load, there was little impact to the log file. Thursday, September 20, 2012 - 9:19:12 AM - vinodhkumar Its very useful. great job. Thanks Monday, August 27, 2012 - 10:54:42 AM - Orlando Colamatteo I agree with some others that the testbed is a bit contrived. If youre looking to move data from one table to another on the same instance then SSIS will rarely be a viable option. Some form of T-SQL will almost certainly outperform an SSIS operation. A more realistic scenario is moving data between two disparate data sources. It is surpising how poorly the ODBC Destination performs, especially in light of what Microsoft has publicly said in that they will be moving away from OLE DB interfaces and standardizing on ODBC in future products: In the ODBC Destination I expected Microsoft to implement the loading of data via the bulk load API as they did with the FastLoad option of the OLE DB Destination. On a separate note regarding loading data into MySQL with SSIS: In the past I did some performance tests with the Cherry City OLE DB driver for MySQL and it is horribly slow as it only inserts one row at a time. This is not to mention the fact that it crashed BIDS regularly when developing with it. Given the lack of a benefit I would stick with the tools built into SSIS and avoid the hassle of installing and configuring a third party driver. If youre using SSIS 2005 I would recommend using a Script Component as a Destination and issuing batch-inserts against a connection made using the MySQL ODBC Driver: msdn. microsoften-uslibraryms135939.aspx If youre using SSIS 2008 I would recommend using an ADO NET Destination with the MySQL ODBC Driver. In my tests it was only able to achieve about 240 rowsminute throughput to MySQL which is quite disappointing: msdn. microsoften-uslibrarybb895291(vsql.105).aspx If youre using SSIS 2012 I would recommend using an ODBC Destination with the MySQL ODBC Driver. In my tests it outperformed the ADO NET Destination over 3 to 1 but still only achieved about 800 rowsminute throughput, which was still quite disappointing: msdn. microsoften-uslibraryhh758691(vsql.110).aspx