Share via

Menyetel indeks non-kluster dengan saran indeks yang hilang

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Fitur indeks yang hilang adalah alat ringan untuk menemukan indeks yang hilang yang mungkin secara signifikan meningkatkan performa kueri. Artikel ini menjelaskan cara menggunakan saran indeks yang hilang untuk menyetel indeks secara efektif dan meningkatkan performa kueri.

Batasan fitur indeks yang hilang

Saat pengoptimal kueri menghasilkan rencana kueri, pengoptimal kueri menganalisis indeks terbaik untuk kondisi filter tertentu. Jika indeks terbaik tidak ada, pengoptimal kueri masih menghasilkan rencana kueri menggunakan metode akses paling murah yang tersedia, tetapi juga menyimpan informasi tentang indeks ini. Fitur indeks yang hilang memungkinkan Anda mengakses informasi tersebut tentang indeks terbaik sehingga Anda dapat memutuskan apakah indeks tersebut harus diimplementasikan.

Pengoptimalan kueri adalah proses sensitif waktu, sehingga ada batasan pada fitur indeks yang hilang. Batasan meliputi:

  • Saran indeks yang hilang didasarkan pada perkiraan yang dibuat selama pengoptimalan satu kueri, sebelum eksekusi kueri. Saran indeks yang hilang tidak diuji atau diperbarui setelah eksekusi kueri.
  • Fitur indeks yang hilang hanya menyarankan indeks rowstore berbasis disk nonclustered. Indeks unik dan terfilter tidak disarankan.
  • Kolom kunci disarankan , tetapi saran tidak menentukan urutan untuk kolom tersebut. Untuk informasi tentang mengurutkan kolom, lihat bagian Terapkan saran indeks yang hilang di artikel ini.
  • Kolom yang disertakan disarankan , tetapi SQL Server tidak melakukan analisis manfaat biaya mengenai ukuran indeks yang dihasilkan ketika sejumlah besar kolom yang disertakan disarankan.
  • Permintaan indeks yang hilang mungkin menawarkan variasi indeks yang sama pada tabel dan kolom yang sama di seluruh kueri. Penting untuk meninjau saran indeks dan menggabungkan jika memungkinkan.
  • Saran tidak dibuat untuk rencana kueri sepele.
  • Informasi biaya kurang akurat untuk kueri yang hanya melibatkan predikat ketidaksamaan.
  • Saran dikumpulkan untuk maksimal 600 grup indeks yang hilang. Setelah ambang batas ini tercapai, tidak ada lagi data grup indeks yang hilang yang dikumpulkan.

Karena keterbatasan ini, saran indeks yang hilang paling baik diperlakukan sebagai salah satu dari beberapa sumber informasi saat melakukan analisis indeks, desain, penyetelan, dan pengujian. Saran indeks yang hilang bukan resep untuk membuat indeks persis seperti yang disarankan.

Catatan

Azure SQL Database menawarkan penyetelan indeks otomatis. Penyetelan indeks otomatis menggunakan pembelajaran mesin untuk belajar secara horizontal dari semua database di Azure SQL Database melalui AI dan secara dinamis meningkatkan tindakan penyetelannya. Penyetelan indeks otomatis mencakup proses verifikasi untuk memastikan ada peningkatan positif pada performa beban kerja dari indeks yang dibuat.

Menampilkan rekomendasi indeks yang hilang

Fitur indeks yang hilang terdiri dari dua komponen:

  • Elemen MissingIndexes dalam XML rencana eksekusi. Ini memungkinkan Anda menghubungkan indeks yang dianggap hilang oleh pengoptimal kueri dengan kueri yang hilang.
  • Sekumpulan tampilan manajemen dinamis (DMV) yang dapat dikueri untuk mengembalikan informasi tentang indeks yang hilang. Ini memungkinkan Anda melihat semua rekomendasi indeks yang hilang untuk database.

Menampilkan saran indeks yang tidak ada dalam rencana eksekusi

Rencana eksekusi kueri dapat dihasilkan atau diperoleh dengan berbagai cara:

Misalnya, Anda dapat menggunakan kueri berikut untuk menghasilkan permintaan indeks yang hilang terhadap database sampel AdventureWorks.

SELECT City, StateProvinceID, PostalCode  
FROM Person.Address as a
JOIN Person.BusinessEntityAddress as ba on
    a.AddressID = ba.AddressID
JOIN Person.Person as  p on
    ba.BusinessEntityID = p.BusinessEntityID
WHERE p.FirstName like 'K%' and
    StateProvinceID = 9;  
GO

Untuk menghasilkan dan melihat permintaan indeks yang hilang:

  1. Buka SQL Server Management Studio dan sambungkan sesi ke salinan database sampel AdventureWorks Anda.

  2. Tempelkan kueri ke dalam sesi dan hasilkan perkiraan rencana eksekusi di SSMS untuk kueri dengan memilih tombol toolbar Tampilkan Perkiraan Rencana Eksekusi. Rencana eksekusi akan ditampilkan di panel dalam sesi saat ini. Pernyataan Indeks Hilang berwarna hijau akan muncul di dekat bagian atas paket grafik.

    A graphic execution plan in SQL Server Management Studio. A missing index request appears at the top of the missing index request in green font, directly below the Transact-SQL statement.

    Satu rencana eksekusi mungkin berisi beberapa permintaan indeks yang hilang, tetapi hanya satu permintaan indeks yang hilang yang dapat ditampilkan dalam rencana eksekusi grafis. Salah satu opsi untuk melihat daftar lengkap indeks yang hilang untuk rencana eksekusi adalah dengan melihat XML rencana eksekusi.

  3. Klik kanan pada rencana eksekusi dan pilih Tampilkan XML Rencana Eksekusi... dari menu.

    Screenshot showing the menu that appears after right-clicking on an execution plan.

    XML rencana eksekusi akan terbuka sebagai tab baru di dalam SSMS.

    Catatan

    Hanya satu saran indeks yang hilang yang akan ditampilkan dalam opsi menu Detail Indeks Hilang... , bahkan jika beberapa saran ada dalam XML rencana eksekusi. Saran indeks yang hilang ditampilkan mungkin bukan yang memiliki perkiraan peningkatan tertinggi untuk kueri.

  4. Tampilkan dialog Temukan dengan menggunakan pintasan CTRL+f.

  5. Cari MissingIndex.

    Screenshot of the XML for an execution plan. The Find dialog has been opened, and the term MissingIndex has been searched for in the document.

    Dalam contoh ini, ada dua MissingIndex elemen.

    • Indeks pertama yang hilang menunjukkan kueri mungkin menggunakan indeks pada Person.Address tabel yang mendukung pencarian kesetaraan StateProvinceID pada kolom, yang mencakup dua kolom lagi, City dan PostalCode'. Pada saat pengoptimalan, pengoptimal kueri percaya bahwa indeks ini dapat mengurangi perkiraan biaya kueri sebesar 34,2737%.
    • Indeks kedua yang hilang menunjukkan kueri mungkin menggunakan indeks pada Person.Person tabel yang mendukung pencarian ketidaksamaan pada kolom FirstName. Pada saat pengoptimalan, pengoptimal kueri percaya bahwa indeks ini dapat mengurangi perkiraan biaya kueri sebesar 18,1102%.

Setiap indeks nonkluster berbasis disk dalam database Anda membutuhkan ruang, menambahkan overhead untuk penyisipan, pembaruan, dan penghapusan, dan mungkin memerlukan pemeliharaan. Untuk alasan ini, ini adalah praktik terbaik untuk meninjau semua permintaan indeks yang hilang untuk tabel dan indeks yang ada pada tabel sebelum menambahkan indeks berdasarkan rencana eksekusi kueri.

Lihat saran indeks yang hilang di DMV

Anda dapat mengambil informasi tentang indeks yang hilang dengan mengkueri objek manajemen dinamis yang tercantum dalam tabel berikut.

Tampilan manajemen dinamis Informasi yang dikembalikan
sys.dm_db_missing_index_group_stats (T-SQL) Mengembalikan informasi ringkasan tentang grup indeks yang hilang, misalnya, peningkatan performa yang dapat diperoleh dengan menerapkan grup indeks tertentu yang hilang.
sys.dm_db_missing_index_groups (T-SQL) Mengembalikan informasi tentang sekelompok indeks tertentu yang hilang, seperti pengidentifikasi grup dan pengidentifikasi semua indeks yang hilang yang terkandung dalam grup tersebut.
sys.dm_db_missing_index_details (T-SQL) Mengembalikan informasi terperinci tentang indeks yang hilang; misalnya, ini mengembalikan nama dan pengidentifikasi tabel tempat indeks hilang, dan jenis kolom dan kolom yang harus membentuk indeks yang hilang.
sys.dm_db_missing_index_columns (T-SQL) Mengembalikan informasi tentang kolom tabel database yang tidak memiliki indeks.

Kueri berikut menggunakan DMV indeks yang hilang untuk menghasilkan pernyataan CREATE INDEX. Pernyataan pembuatan indeks di sini dimaksudkan untuk membantu Anda dalam membuat DDL Anda sendiri setelah memeriksa semua permintaan untuk tabel bersama dengan indeks yang ada pada tabel.

SELECT TOP 20
    CONVERT (varchar(30), getdate(), 126) AS runtime,
    CONVERT (decimal (28, 1), 
        migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) 
        ) AS estimated_improvement,
    'CREATE INDEX missing_index_' + 
        CONVERT (varchar, mig.index_group_handle) + '_' + 
        CONVERT (varchar, mid.index_handle) + ' ON ' + 
        mid.statement + ' (' + ISNULL (mid.equality_columns, '') + 
        CASE
            WHEN mid.equality_columns IS NOT NULL
            AND mid.inequality_columns IS NOT NULL THEN ','
            ELSE ''
        END + ISNULL (mid.inequality_columns, '') + ')' + 
        ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON 
    migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON 
    mig.index_handle = mid.index_handle
ORDER BY estimated_improvement DESC;
GO

Kueri ini mengurutkan saran menurut kolom bernama estimated_improvement. Perkiraan peningkatan didasarkan pada kombinasi:

  • Perkiraan biaya kueri kueri yang terkait dengan permintaan indeks yang hilang.
  • Estimasi dampak penambahan indeks. Ini adalah perkiraan berapa banyak indeks nonclustered akan mengurangi biaya kueri.
  • Jumlah eksekusi operator kueri (pencarian dan pemindaian) yang telah dijalankan untuk kueri yang terkait dengan permintaan indeks yang hilang. Saat kita membahas indeks yang hilang dengan Penyimpanan Kueri, informasi ini secara berkala dihapus.

Catatan

Skrip Pembuatan Indeks di Tiger Toolbox Microsoft memeriksa DMV indeks yang hilang dan secara otomatis menghapus indeks yang disarankan yang berlebihan, mengurai indeks berdampak rendah, dan menghasilkan skrip pembuatan indeks untuk tinjauan Anda. Seperti pada kueri di atas, tidak menjalankan perintah pembuatan indeks. Skrip Pembuatan Indeks cocok untuk SQL Server dan Azure SQL Managed Instance. Untuk Azure SQL Database, pertimbangkan untuk menerapkan penyetelan indeks otomatis.

Tinjau Batasan fitur indeks yang hilang dan cara menerapkan saran indeks yang hilang sebelum membuat indeks, dan mengubah nama indeks agar sesuai dengan konvensi penamaan untuk database Anda.

Mempertahankan indeks yang hilang dengan Penyimpanan Kueri

Saran indeks yang hilang dalam DMV dibersihkan oleh peristiwa seperti mulai ulang instans, failover, dan pengaturan database offline. Selain itu, ketika metadata untuk tabel berubah, semua informasi indeks yang hilang tentang tabel tersebut dihapus dari objek manajemen dinamis ini. Perubahan metadata tabel dapat terjadi saat kolom ditambahkan atau dihilangkan dari tabel, misalnya, atau saat indeks dibuat pada kolom tabel. Melakukan operasi ALTER INDEX REBUILD pada indeks pada tabel juga menghapus permintaan indeks yang hilang untuk tabel tersebut.

Demikian pula, rencana eksekusi yang disimpan dalam cache paket dibersihkan oleh peristiwa seperti mulai ulang instans, failover, dan pengaturan database offline. Rencana eksekusi mungkin dihapus dari cache karena tekanan memori dan kompilasi ulang.

Saran indeks yang hilang dalam rencana eksekusi dapat dipertahankan di seluruh peristiwa ini dengan mengaktifkan Penyimpanan Kueri.

Kueri berikut mengambil 20 paket kueri teratas yang berisi permintaan indeks yang hilang dari penyimpanan kueri berdasarkan perkiraan kasar dari total pembacaan logis untuk kueri. Data terbatas pada eksekusi kueri dalam 48 jam terakhir.

SELECT TOP 20
    qsq.query_id,
    SUM(qrs.count_executions) * AVG(qrs.avg_logical_io_reads) as est_logical_reads,
    SUM(qrs.count_executions) AS sum_executions,
    AVG(qrs.avg_logical_io_reads) AS avg_avg_logical_io_reads,
    SUM(qsq.count_compiles) AS sum_compiles,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq
JOIN sys.query_store_plan qsp on qsq.query_id=qsp.query_id
CROSS APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on 
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi on 
    qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE    
    qsp.query_plan like N'%<MissingIndexes>%'
    and qsrsi.start_time >= DATEADD(HH, -48, SYSDATETIME())
GROUP BY qsq.query_id, qsq.query_hash
ORDER BY est_logical_reads DESC;
GO

Menerapkan saran indeks yang hilang

Untuk menggunakan saran indeks yang hilang secara efektif, ikuti panduan desain indeks non-klusster. Saat menyetel indeks non-kluster dengan saran indeks yang hilang, tinjau struktur tabel dasar, gabungkan indeks dengan hati-hati, pertimbangkan urutan kolom kunci, dan tinjau saran kolom yang disertakan.

Meninjau struktur tabel dasar

Sebelum membuat indeks non-kluster pada tabel berdasarkan saran indeks yang hilang, tinjau indeks berkluster tabel.

Salah satu cara untuk memeriksa indeks berkluster adalah dengan menggunakan prosedur tersimpan sistem sp_helpindex. Misalnya, kita dapat melihat ringkasan indeks pada tabel Person.Address dengan menjalankan pernyataan berikut:

exec sp_helpindex 'Person.Address';
GO

index_description Tinjau kolom. Tabel hanya dapat memiliki satu indeks berkluster. Jika indeks berkluster telah diimplementasikan untuk tabel, index_description akan berisi kata 'terkluster'.

Screenshot of the sp_helpindex being run against the `Person.Address` table in the AdventureWorks database. The table returns four indexes. The fourth index has an index_description that shows that it's a clustered, unique primary key.

Jika tidak ada indeks berkluster, tabel adalah timbunan. Dalam kasus ini, tinjau apakah tabel sengaja dibuat sebagai timbunan untuk memecahkan masalah performa tertentu. Sebagian besar tabel mendapat manfaat dari indeks berkluster: seringkali, tabel diimplementasikan sebagai tumpukan secara tidak sengaja. Pertimbangkan untuk menerapkan indeks berkluster berdasarkan panduan desain indeks berkluster.

Tinjau indeks yang hilang dan indeks yang ada untuk tumpang tindih

Indeks yang hilang mungkin menawarkan variasi indeks nonclustered yang serupa pada tabel dan kolom yang sama di seluruh kueri. Indeks yang hilang mungkin juga mirip dengan indeks yang ada pada tabel. Untuk performa optimal, yang terbaik adalah memeriksa indeks yang hilang dan indeks yang ada untuk tumpang tindih dan menghindari pembuatan indeks duplikat.

Membuat skrip indeks yang ada pada tabel

Salah satu cara untuk memeriksa definisi indeks yang ada pada tabel adalah dengan membuat skrip indeks dengan Detail Object Explorer:

  1. Koneksi Object Explorer ke instans atau database Anda.
  2. Perluas simpul untuk database yang dimaksud di Object Explorer.
  3. Luaskan folder Tabel.
  4. Perluas tabel yang ingin Anda skrip keluarkan indeksnya.
  5. Pilih folder Indeks.
  6. Jika panel Detail Object Explorer belum terbuka, pada menu Tampilan , pilih Detail Object Explorer atau tekan F7.
  7. Pilih semua indeks yang tercantum di panel Detail Object Explorer dengan pintasan CTRL+a.
  8. Klik kanan di mana saja di wilayah yang dipilih dan pilih opsi menu indeks Skrip sebagai, lalu BUAT Ke dan Jendela Editor Kueri Baru.

Screenshot of scripting out all indexes on a table using the Object Explorer Details pane in SSMS.

Tinjau indeks dan gabungkan jika memungkinkan

Tinjau rekomendasi indeks yang hilang untuk tabel sebagai grup, bersama dengan definisi indeks yang ada pada tabel. Ingatlah bahwa ketika menentukan indeks, umumnya kolom kesetaraan harus dimasukkan sebelum kolom ketidaksetaraan, dan bersama-sama mereka harus membentuk kunci indeks. Untuk menentukan urutan efektif untuk kolom kesetaraan, urutkan berdasarkan selektivitasnya: cantumkan kolom yang paling selektif terlebih dahulu (paling kiri dalam daftar kolom). Kolom unik paling selektif, sementara kolom dengan banyak nilai berulang kurang selektif.

Kolom yang disertakan harus ditambahkan ke pernyataan CREATE INDEX menggunakan klausa INCLUDE. Urutan kolom yang disertakan tidak memengaruhi performa kueri. Oleh karena itu, saat menggabungkan indeks, kolom yang disertakan mungkin digabungkan tanpa khawatir tentang pesanan. Pelajari selengkapnya dalam panduan kolom yang disertakan.

Misalnya, Anda mungkin memiliki tabel, Person.Address, dengan indeks yang sudah ada pada kolom StateProvinceIDkunci . Anda mungkin melihat rekomendasi indeks yang Person.Address hilang untuk tabel untuk kolom berikut:

  • Filter EQUALITY untuk StateProvinceID dan City
  • Filter EQUALITY untuk StateProvinceID dan City, INCLUDE PostalCode

Memodifikasi indeks yang ada agar sesuai dengan rekomendasi kedua, indeks dengan kunci pada StateProvinceID dan City termasuk PostalCode, kemungkinan akan memenuhi kueri yang menghasilkan kedua saran indeks.

Tradeoff umum dalam penyetelan indeks. Kemungkinan untuk banyak himpunan data, City kolom lebih selektif daripada StateProvinceID kolom. Namun, jika indeks StateProvinceID kita yang ada banyak digunakan, dan permintaan lain sebagian besar mencari pada dan StateProvinceIDCity, itu adalah overhead yang lebih rendah bagi database secara umum untuk memiliki indeks tunggal dengan kedua kolom di kunci, mengarah ke StateProvinceID, meskipun itu bukan kolom yang paling selektif.

Indeks mungkin dimodifikasi dengan beberapa cara:

Urutan kunci indeks penting saat menggabungkan saran indeks: City karena kolom di depan berbeda dari StateProvinceID sebagai kolom utama. Pelajari lebih lanjut dalam pedoman desain indeks non-klusster.

Saat membuat indeks, pertimbangkan untuk menggunakan operasi indeks online saat tersedia.

Sementara indeks dapat secara dramatis meningkatkan performa kueri dalam beberapa kasus, indeks juga memiliki biaya operasional dan manajemen. Tinjau panduan desain indeks umum untuk membantu menilai manfaat indeks sebelum membuatnya.

Verifikasi apakah perubahan indeks Anda berhasil

Penting untuk mengonfirmasi apakah perubahan indeks Anda telah berhasil: apakah pengoptimal kueri menggunakan indeks Anda?

Salah satu cara untuk memvalidasi perubahan indeks Anda adalah dengan menggunakan Penyimpanan Kueri untuk mengidentifikasi kueri dengan permintaan indeks yang hilang. Perhatikan query_id untuk kueri. Gunakan tampilan Kueri Terlacak di Penyimpanan Kueri untuk memeriksa apakah rencana eksekusi telah berubah untuk kueri dan apakah pengoptimal menggunakan indeks baru atau yang dimodifikasi. Pelajari selengkapnya tentang Kueri Terlacak di awal dengan pemecahan masalah performa kueri.

Konten terkait

Pelajari selengkapnya tentang penyetelan indeks dan performa di artikel berikut: