Geekly articles weekly

Ikhtisar alat SQLIndexManager gratis

Seperti yang Anda ketahui, indeks memainkan peran penting dalam DBMS, menyediakan pencarian cepat dari catatan yang diperlukan. Karena itu, sangat penting untuk melayani mereka tepat waktu. Cukup banyak materi yang telah ditulis tentang analisis dan optimisasi, termasuk di Internet. Misalnya, ulasan terbaru tentang topik ini dibuat dalam publikasi ini .

Ada banyak solusi berbayar dan gratis untuk ini. Misalnya, ada solusi turnkey berdasarkan metode optimasi indeks adaptif.

Selanjutnya, pertimbangkan utilitas SQLIndexManager gratis, yang ditulis oleh AlanDenton .

Perbedaan teknis utama antara SQLIndexManager dan sejumlah analog lainnya dibuat oleh penulis di sini dan di sini .

Dalam artikel yang sama, kami melihat proyek dan kemungkinan menggunakan solusi perangkat lunak ini.

Diskusikan utilitas ini di sini .
Seiring waktu, sebagian besar komentar dan bug telah diperbaiki.

Jadi, sekarang mari kita beralih ke utilitas SQLIndexManager itu sendiri.

Aplikasi ini ditulis dalam C # .NET Framework 4.5 di Visual Studio 2017 dan menggunakan DevExpress untuk formulir:



dan terlihat seperti ini:



Semua permintaan dihasilkan dalam file berikut:

  1. Indeks
  2. Pertanyaan
  3. Queryengine
  4. ServerInfo



Saat menghubungkan ke database dan mengirim permintaan ke DBMS, aplikasi ditandatangani sebagai berikut:

ApplicationName=”SQLIndexManager”

Saat aplikasi dimulai, jendela modal terbuka untuk menambahkan koneksi:


Di sini memuat daftar lengkap semua contoh MS SQL Server yang tersedia melalui jaringan lokal belum berfungsi.

Anda juga dapat menambahkan koneksi menggunakan tombol paling kiri pada menu utama:



Selanjutnya, permintaan DBMS berikut akan diluncurkan:

  1. Memperoleh Informasi DBMS
     SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)

  2. Mendapatkan daftar database yang tersedia dengan properti singkatnya
     SELECT DatabaseName = t.[name] , d.DataSize , DataUsedSize = CAST(NULL AS BIGINT) , d.LogSize , LogUsedSize = CAST(NULL AS BIGINT) , RecoveryModel = t.recovery_model_desc , LogReuseWait = t.log_reuse_wait_desc FROM sys.databases t WITH(NOLOCK) LEFT JOIN ( SELECT [database_id] , DataSize = SUM(CASE WHEN [type] = 0 THEN CAST(size AS BIGINT) END) , LogSize = SUM(CASE WHEN [type] = 1 THEN CAST(size AS BIGINT) END) FROM sys.master_files WITH(NOLOCK) GROUP BY [database_id] ) d ON d.[database_id] = t.[database_id] WHERE t.[state] = 0 AND t.[database_id] != 2 AND ISNULL(HAS_DBACCESS(t.[name]), 1) = 1


Setelah mengeksekusi skrip di atas, sebuah jendela muncul yang berisi informasi singkat tentang database dari instance MS SQL Server yang dipilih:



Perlu dicatat bahwa informasi tambahan ditampilkan berdasarkan hak. Jika ada sysadmin , maka Anda dapat memilih data dari tampilan sys.master_files . Jika tidak ada hak seperti itu, maka lebih sedikit data yang dikembalikan agar tidak memperlambat permintaan.

Di sini Anda perlu memilih basis data yang menarik dan klik tombol "OK".

Selanjutnya, skrip berikut akan dieksekusi untuk setiap database yang dipilih untuk menganalisis status indeks:

Analisis Status Indeks
 declare @Fragmentation float=15; declare @MinIndexSize bigint=768; declare @MaxIndexSize bigint=1048576; declare @PreDescribeSize bigint=32768; SET NOCOUNT ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF IF OBJECT_ID('tempdb.dbo.#AllocationUnits') IS NOT NULL DROP TABLE #AllocationUnits CREATE TABLE #AllocationUnits ( ContainerID BIGINT PRIMARY KEY , ReservedPages BIGINT NOT NULL , UsedPages BIGINT NOT NULL ) INSERT INTO #AllocationUnits (ContainerID, ReservedPages, UsedPages) SELECT [container_id] , SUM([total_pages]) , SUM([used_pages]) FROM sys.allocation_units WITH(NOLOCK) GROUP BY [container_id] HAVING SUM([total_pages]) BETWEEN @MinIndexSize AND @MaxIndexSize IF OBJECT_ID('tempdb.dbo.#ExcludeList') IS NOT NULL DROP TABLE #ExcludeList CREATE TABLE #ExcludeList (ID INT PRIMARY KEY) INSERT INTO #ExcludeList SELECT [object_id] FROM sys.objects WITH(NOLOCK) WHERE [type] IN ('V', 'U') AND ( [is_ms_shipped] = 1 ) IF OBJECT_ID('tempdb.dbo.#Partitions') IS NOT NULL DROP TABLE #Partitions SELECT [object_id] , [index_id] , [partition_id] , [partition_number] , [rows] , [data_compression] INTO #Partitions FROM sys.partitions WITH(NOLOCK) WHERE [object_id] > 255 AND [rows] > 0 AND [object_id] NOT IN (SELECT * FROM #ExcludeList) IF OBJECT_ID('tempdb.dbo.#Indexes') IS NOT NULL DROP TABLE #Indexes CREATE TABLE #Indexes ( ObjectID INT NOT NULL , IndexID INT NOT NULL , IndexName SYSNAME NULL , PagesCount BIGINT NOT NULL , UnusedPagesCount BIGINT NOT NULL , PartitionNumber INT NOT NULL , RowsCount BIGINT NOT NULL , IndexType TINYINT NOT NULL , IsAllowPageLocks BIT NOT NULL , DataSpaceID INT NOT NULL , DataCompression TINYINT NOT NULL , IsUnique BIT NOT NULL , IsPK BIT NOT NULL , FillFactorValue INT NOT NULL , IsFiltered BIT NOT NULL , PRIMARY KEY (ObjectID, IndexID, PartitionNumber) ) INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = CASE WHEN ABS(a.ReservedPages - a.UsedPages) > 32 THEN a.ReservedPages - a.UsedPages ELSE 0 END , PartitionNumber = p.[partition_number] , RowsCount = ISNULL(p.[rows], 0) , IndexType = i.[type] , IsAllowPageLocks = i.[allow_page_locks] , DataSpaceID = i.[data_space_id] , DataCompression = p.[data_compression] , IsUnique = i.[is_unique] , IsPK = i.[is_primary_key] , FillFactorValue = i.[fill_factor] , IsFiltered = i.[has_filter] FROM #AllocationUnits a JOIN #Partitions p ON a.ContainerID = p.[partition_id] JOIN sys.indexes i WITH(NOLOCK) ON i.[object_id] = p.[object_id] AND p.[index_id] = i.[index_id] WHERE i.[type] IN (0, 1, 2, 5, 6) AND i.[object_id] > 255 DECLARE @files TABLE (ID INT PRIMARY KEY) INSERT INTO @files SELECT DISTINCT [data_space_id] FROM sys.database_files WITH(NOLOCK) WHERE [state] != 0 AND [type] = 0 IF @@ROWCOUNT > 0 BEGIN DELETE FROM i FROM #Indexes i LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id] WHERE ISNULL(dds.[data_space_id], i.DataSpaceID) IN (SELECT * FROM @files) END DECLARE @DBID INT , @DBNAME SYSNAME SET @DBNAME = DB_NAME() SELECT @DBID = [database_id] FROM sys.databases WITH(NOLOCK) WHERE [name] = @DBNAME IF OBJECT_ID('tempdb.dbo.#Fragmentation') IS NOT NULL DROP TABLE #Fragmentation CREATE TABLE #Fragmentation ( ObjectID INT NOT NULL , IndexID INT NOT NULL , PartitionNumber INT NOT NULL , Fragmentation FLOAT NOT NULL , PRIMARY KEY (ObjectID, IndexID, PartitionNumber) ) INSERT INTO #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation) SELECT i.ObjectID , i.IndexID , i.PartitionNumber , r.[avg_fragmentation_in_percent] FROM #Indexes i CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r WHERE i.PagesCount <= @PreDescribeSize AND r.[index_level] = 0 AND r.[alloc_unit_type_desc] = 'IN_ROW_DATA' AND i.IndexType IN (0, 1, 2) IF OBJECT_ID('tempdb.dbo.#Columns') IS NOT NULL DROP TABLE #Columns CREATE TABLE #Columns ( ObjectID INT NOT NULL , ColumnID INT NOT NULL , ColumnName SYSNAME NULL , SystemTypeID TINYINT NULL , IsSparse BIT , IsColumnSet BIT , MaxLen INT , PRIMARY KEY (ObjectID, ColumnID) ) INSERT INTO #Columns SELECT ObjectID = [object_id] , ColumnID = [column_id] , ColumnName = [name] , SystemTypeID = [system_type_id] , IsSparse = [is_sparse] , IsColumnSet = [is_column_set] , MaxLen = [max_length] FROM sys.columns WITH(NOLOCK) WHERE [object_id] IN (SELECT DISTINCT i.ObjectID FROM #Indexes i) IF OBJECT_ID('tempdb.dbo.#IndexColumns') IS NOT NULL DROP TABLE #IndexColumns CREATE TABLE #IndexColumns ( ObjectID INT NOT NULL , IndexID INT NOT NULL , OrderID INT NOT NULL , ColumnID INT NOT NULL , IsIncluded BIT NOT NULL , PRIMARY KEY (ObjectID, IndexID, ColumnID) ) INSERT INTO #IndexColumns SELECT ObjectID = [object_id] , IndexID = [index_id] , OrderID = CASE WHEN [is_included_column] = 0 THEN [key_ordinal] ELSE [index_column_id] END , ColumnID = [column_id] , IsIncluded = ISNULL([is_included_column], 0) FROM sys.index_columns ic WITH(NOLOCK) WHERE EXISTS( SELECT * FROM #Indexes i WHERE i.ObjectID = ic.[object_id] AND i.IndexID = ic.[index_id] AND i.IndexType IN (1, 2) ) IF OBJECT_ID('tempdb.dbo.#Lob') IS NOT NULL DROP TABLE #Lob CREATE TABLE #Lob ( ObjectID INT NOT NULL , IndexID INT NOT NULL , IsLobLegacy BIT , IsLob BIT , PRIMARY KEY (ObjectID, IndexID) ) INSERT INTO #Lob (ObjectID, IndexID, IsLobLegacy, IsLob) SELECT c.ObjectID , IndexID = ISNULL(i.IndexID, 1) , IsLobLegacy = MAX(CASE WHEN c.SystemTypeID IN (34, 35, 99) THEN 1 END) , IsLob = 0 FROM #Columns c LEFT JOIN #IndexColumns i ON c.ObjectID = i.ObjectID AND c.ColumnID = i.ColumnID WHERE c.SystemTypeID IN (34, 35, 99) GROUP BY c.ObjectID , i.IndexID IF OBJECT_ID('tempdb.dbo.#Sparse') IS NOT NULL DROP TABLE #Sparse CREATE TABLE #Sparse (ObjectID INT PRIMARY KEY) INSERT INTO #Sparse SELECT DISTINCT ObjectID FROM #Columns WHERE IsSparse = 1 OR IsColumnSet = 1 IF OBJECT_ID('tempdb.dbo.#AggColumns') IS NOT NULL DROP TABLE #AggColumns CREATE TABLE #AggColumns ( ObjectID INT NOT NULL , IndexID INT NOT NULL , IndexColumns NVARCHAR(MAX) , IncludedColumns NVARCHAR(MAX) , PRIMARY KEY (ObjectID, IndexID) ) INSERT INTO #AggColumns SELECT t.ObjectID , t.IndexID , IndexColumns = STUFF(( SELECT ', [' + c.ColumnName + ']' FROM #IndexColumns i JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID WHERE i.ObjectID = t.ObjectID AND i.IndexID = t.IndexID AND i.IsIncluded = 0 ORDER BY i.OrderID FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '') , IncludedColumns = STUFF(( SELECT ', [' + c.ColumnName + ']' FROM #IndexColumns i JOIN #Columns c ON i.ObjectID = c.ObjectID AND i.ColumnID = c.ColumnID WHERE i.ObjectID = t.ObjectID AND i.IndexID = t.IndexID AND i.IsIncluded = 1 ORDER BY i.OrderID FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'), 1, 2, '') FROM ( SELECT DISTINCT ObjectID, IndexID FROM #Indexes WHERE IndexType IN (1, 2) ) t SELECT i.ObjectID , i.IndexID , i.IndexName , ObjectName = o.[name] , SchemaName = s.[name] , i.PagesCount , i.UnusedPagesCount , i.PartitionNumber , i.RowsCount , i.IndexType , i.IsAllowPageLocks , u.TotalWrites , u.TotalReads , u.TotalSeeks , u.TotalScans , u.TotalLookups , u.LastUsage , i.DataCompression , f.Fragmentation , IndexStats = STATS_DATE(i.ObjectID, i.IndexID) , IsLobLegacy = ISNULL(lob.IsLobLegacy, 0) , IsLob = ISNULL(lob.IsLob, 0) , IsSparse = CAST(CASE WHEN p.ObjectID IS NULL THEN 0 ELSE 1 END AS BIT) , IsPartitioned = CAST(CASE WHEN dds.[data_space_id] IS NOT NULL THEN 1 ELSE 0 END AS BIT) , FileGroupName = fg.[name] , i.IsUnique , i.IsPK , i.FillFactorValue , i.IsFiltered , a.IndexColumns , a.IncludedColumns FROM #Indexes i JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = i.ObjectID JOIN sys.schemas s WITH(NOLOCK) ON s.[schema_id] = o.[schema_id] LEFT JOIN #AggColumns a ON a.ObjectID = i.ObjectID AND a.IndexID = i.IndexID LEFT JOIN #Sparse p ON p.ObjectID = i.ObjectID LEFT JOIN #Fragmentation f ON f.ObjectID = i.ObjectID AND f.IndexID = i.IndexID AND f.PartitionNumber = i.PartitionNumber LEFT JOIN ( SELECT ObjectID = [object_id] , IndexID = [index_id] , TotalWrites = NULLIF([user_updates], 0) , TotalReads = NULLIF([user_seeks] + [user_scans] + [user_lookups], 0) , TotalSeeks = NULLIF([user_seeks], 0) , TotalScans = NULLIF([user_scans], 0) , TotalLookups = NULLIF([user_lookups], 0) , LastUsage = ( SELECT MAX(dt) FROM ( VALUES ([last_user_seek]) , ([last_user_scan]) , ([last_user_lookup]) , ([last_user_update]) ) t(dt) ) FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE [database_id] = @DBID ) u ON i.ObjectID = u.ObjectID AND i.IndexID = u.IndexID LEFT JOIN #Lob lob ON lob.ObjectID = i.ObjectID AND lob.IndexID = i.IndexID LEFT JOIN sys.destination_data_spaces dds WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] AND i.PartitionNumber = dds.[destination_id] JOIN sys.filegroups fg WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] WHERE o.[type] IN ('V', 'U') AND ( f.Fragmentation >= @Fragmentation OR i.PagesCount > @PreDescribeSize OR i.IndexType IN (5, 6) )


Seperti yang Anda lihat dari kueri sendiri, tabel sementara sering digunakan. Hal ini dilakukan agar tidak ada kompilasi ulang, dan dalam kasus skema besar, rencana dapat dihasilkan secara paralel saat memasukkan data, karena memasukkan dengan variabel tabel dimungkinkan hanya dalam satu aliran.

Setelah menjalankan skrip di atas, sebuah jendela dengan tabel indeks akan muncul:



Di sini Anda juga dapat menampilkan informasi terperinci lainnya, seperti:

  1. sebuah database
  2. jumlah bagian
  3. tanggal dan waktu panggilan terakhir
  4. kompresi
  5. filegroup

dll.
Kolom itu sendiri dapat disesuaikan:



Di sel-sel kolom Fix, Anda dapat memilih tindakan apa yang akan dilakukan selama optimasi. Juga, ketika pemindaian selesai, tindakan default dipilih berdasarkan pengaturan yang dipilih:



Anda harus memilih indeks yang diinginkan untuk diproses.

Dengan menggunakan menu utama, Anda dapat menyimpan skrip (tombol yang sama memulai proses pengoptimalan indeks itu sendiri):



simpan tabel dalam berbagai format (tombol yang sama memungkinkan Anda membuka pengaturan terperinci untuk analisis dan optimalisasi indeks):



Juga, informasi dapat diperbarui dengan mengklik tombol ketiga di sebelah kiri di menu utama di sebelah kaca pembesar.

Sebuah tombol dengan kaca pembesar memungkinkan Anda memilih basis data yang diinginkan untuk dipertimbangkan.

Saat ini tidak ada sistem bantuan lengkap. Karena itu, menekan tombol “?” itu hanya akan menyebabkan munculnya jendela modal yang berisi informasi dasar tentang produk perangkat lunak:



Selain semua hal di atas, menu utama memiliki bilah pencarian:



Saat memulai proses pengoptimalan indeks:



Juga di bagian bawah jendela Anda dapat melihat log tindakan yang dilakukan:



Di jendela untuk analisis terperinci dan optimalisasi indeks, Anda dapat mengonfigurasi opsi yang lebih halus:



Saran untuk aplikasi:

  1. memungkinkan pembaruan statistik selektif tidak hanya untuk indeks tetapi juga dengan cara yang berbeda (sepenuhnya diperbarui atau sebagian)
  2. memungkinkan tidak hanya untuk memilih database, tetapi juga server yang berbeda (ini sangat nyaman ketika ada banyak contoh MS SQL Server)
  3. untuk fleksibilitas yang lebih besar dalam penggunaan, diusulkan untuk membungkus perintah di perpustakaan, dan mengeluarkannya ke perintah PowerShell, seperti yang dilakukan, misalnya, di sini: dbatools.io/commands
  4. memungkinkan untuk menyimpan dan mengubah pengaturan pribadi baik untuk seluruh aplikasi dan, jika perlu, untuk setiap instance dari MS SQL Server dan setiap database
  5. dari klausa 2 dan 4 mengikuti keinginan untuk membuat grup pada database dan grup pada contoh MS SQL Server, yang pengaturannya sama
  6. mencari indeks duplikat (penuh dan tidak lengkap, yang sedikit berbeda atau hanya berbeda dalam kolom yang disertakan)
  7. Karena SQLIndexManager hanya digunakan untuk DBMS MS SQL Server, Anda perlu mencerminkan ini dalam nama, misalnya, sebagai berikut: SQLIndexManager untuk MS SQL Server
  8. Hapus semua bagian aplikasi dari GUI ke dalam modul terpisah dan tulis ulang menjadi .NET Core 2.1

Pada saat penulisan, artikel 6 dari keinginan sedang dikembangkan secara aktif dan sudah ada dukungan dalam bentuk pencarian untuk duplikat lengkap dan serupa:



Sumber


Source: https://habr.com/ru/post/id461277/

More articles:


All Articles