Selama bertahun-tahun bekerja sebagai DBA SQL Server dan melakukan administrasi server, kemudian optimasi kinerja. Secara umum, saya ingin melakukan sesuatu yang bermanfaat di waktu luang saya untuk Semesta dan kolega kami. Jadi pada akhirnya kami mendapat
alat pemeliharaan indeks
open source kecil untuk SQL Server dan Azure.
Ide
Kadang-kadang, ketika mengerjakan prioritas mereka, orang mungkin menyerupai baterai tipe jari - ada biaya motivasi yang cukup untuk hanya satu flash, dan kemudian itu saja. Dan sampai saat ini, saya tidak terkecuali dengan pengamatan kehidupan ini. Seringkali saya dikunjungi oleh ide-ide untuk membuat sesuatu milik saya sendiri, tetapi prioritas berubah dan tidak ada yang berakhir.
Pengaruh yang cukup kuat pada motivasi dan pengembangan profesional saya disediakan oleh pekerjaan di perusahaan Kharkov Devart, yang terlibat dalam pembuatan perangkat lunak untuk pengembangan dan administrasi database SQL Server, MySQL dan Oracle.
Sebelum datang kepada mereka, saya memiliki sedikit gagasan tentang spesifik membuat produk saya sendiri, tetapi sudah dalam proses saya mendapatkan banyak pengetahuan tentang struktur internal SQL Server. Setelah mengoptimalkan permintaan metadata di lini produk mereka selama lebih dari satu tahun, saya secara bertahap mulai memahami fungsionalitas mana yang lebih diminati di pasar daripada yang lain.
Pada tahap tertentu, muncul ide untuk menciptakan produk niche baru, tetapi karena keadaan ide ini tidak lepas landas. Pada saat itu, untuk proyek baru, tidak ada sumber daya gratis yang cukup klise di dalam perusahaan tanpa mengurangi bisnis inti.
Sudah ketika dia bekerja di tempat baru dan mencoba melakukan proyek sendiri, dia harus terus membuat beberapa kompromi. Gagasan awal untuk membuat produk besar penuh fitur dengan cepat menjadi sia-sia dan secara bertahap berubah menjadi arah yang berbeda - untuk memecah fungsionalitas yang direncanakan menjadi mini-tools terpisah dan mengimplementasikannya secara independen satu sama lain.
Akibatnya,
SQL Index Manager lahir - alat pemeliharaan indeks gratis untuk SQL Server dan Azure. Gagasan utama adalah untuk mengambil sebagai dasar alternatif komersial dari RedGate dan Devart dan mencoba untuk meningkatkan fungsionalitasnya. Untuk menyediakan, baik bagi pengguna pemula dan yang berpengalaman, kemampuan untuk dengan mudah menganalisis dan mempertahankan indeks.
Implementasi
Dengan kata-kata, semuanya selalu terdengar sederhana ... Saya mengambil satu dan melihat beberapa vidosik yang memotivasi, berdiri di rak dan mulai membuat produk yang keren. Tetapi dalam prakteknya, tidak semuanya sangat cerah, karena ada banyak jebakan ketika bekerja dengan fungsi sistem tabel sys.dm_db_index_physical_stats dan, dalam kombinasi, satu-satunya tempat dari mana Anda bisa mendapatkan informasi yang relevan tentang fragmentasi indeks.
Dari hari-hari pertama pengembangan, ada peluang besar untuk membuat jalur suram di antara skema standar dan menyalin logika yang sudah dibajak dari pekerjaan aplikasi yang bersaing, sambil menambahkan sedikit lelucon. Tetapi setelah menganalisis permintaan untuk metadata, saya ingin melakukan sesuatu yang lebih optimal, yang, karena birokrasi perusahaan besar, tidak akan pernah muncul dalam produk mereka.
Saat menganalisis RedGate SQL Index Manager (1.1.9.1378 - $ 155), Anda dapat melihat bahwa aplikasi menggunakan pendekatan yang sangat sederhana: dengan satu kueri, kami mendapatkan daftar tabel dan tampilan pengguna, dan setelah kueri kedua, daftar semua indeks dalam database yang dipilih dikembalikan.
SELECT objects.name AS tableOrViewName , objects.object_id AS tableOrViewId , schemas.name AS schemaName , CAST(ISNULL(lobs.NumLobs, 0) AS BIT) AS ContainsLobs , o.is_memory_optimized FROM sys.objects AS objects JOIN sys.schemas AS schemas ON schemas.schema_id = objects.schema_id LEFT JOIN ( SELECT object_id , COUNT(*) AS NumLobs FROM sys.columns WITH (NOLOCK) WHERE system_type_id IN (34, 35, 99) OR max_length = -1 GROUP BY object_id ) AS lobs ON objects.object_id = lobs.object_id LEFT JOIN sys.tables AS o ON o.object_id = objects.object_id WHERE objects.type = 'U' OR objects.type = 'V' SELECT i.object_id AS tableOrViewId , i.name AS indexName , i.index_id AS indexId , i.allow_page_locks AS allowPageLocks , p.partition_number AS partitionNumber , CAST((c.numPartitions - 1) AS BIT) AS belongsToPartitionedIndex FROM sys.indexes AS i JOIN sys.partitions AS p ON p.index_id = i.index_id AND p.object_id = i.object_id JOIN ( SELECT COUNT(*) AS numPartitions , object_id , index_id FROM sys.partitions GROUP BY object_id , index_id ) AS c ON c.index_id = i.index_id AND c.object_id = i.object_id WHERE i.index_id > 0 -- ignore heaps AND i.is_disabled = 0 AND i.is_hypothetical = 0
Kemudian, dalam satu siklus, untuk setiap bagian indeks, permintaan dikirim untuk menentukan ukuran dan tingkat fragmentasi. Pada akhir pemindaian, indeks yang beratnya kurang dari ambang entri dibuang pada klien.
EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 1, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 2, @partitionNr = 1 EXEC sp_executesql N' SELECT index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)' , N'@databaseId int,@objectId int,@indexId int,@partitionNr int' , @databaseId = 7, @objectId = 2133582639, @indexId = 3, @partitionNr = 1
Saat menganalisis logika aplikasi ini, Anda dapat menemukan banyak kekurangan. Misalnya, jika Anda menemukan kesalahan dengan hal-hal sepele, maka sebelum mengirim permintaan, tidak ada pemeriksaan yang dilakukan tentang apakah bagian saat ini berisi string untuk mengecualikan bagian kosong dari pemindaian.
Tetapi masalahnya paling akut di aspek lain: jumlah permintaan server akan kira-kira sama dengan jumlah total baris dari sys.partitions. Mengingat fakta bahwa database nyata dapat berisi puluhan ribu bagian, nuansa ini dapat menyebabkan sejumlah besar permintaan serupa ke server. Dalam situasi di mana basis data jauh, maka waktu pemindaian akan menjadi lebih lama karena meningkatnya penundaan jaringan untuk setiap permintaan, bahkan yang paling sederhana sekalipun.
Tidak seperti RedGate, produk serupa yang dikembangkan di Devart - dbForge Index Manager untuk SQL Server (1.10.38 - $ 99) menerima informasi dalam satu permintaan besar dan kemudian menampilkan semua yang ada di klien:
SELECT SCHEMA_NAME(o.[schema_id]) AS [schema_name] , o.name AS parent_name , o.[type] AS parent_type , i.name , i.type_desc , s.avg_fragmentation_in_percent , s.page_count , p.partition_number , p.[rows] , ISNULL(lob.is_lob_legacy, 0) AS is_lob_legacy , ISNULL(lob.is_lob, 0) AS is_lob , CASE WHEN ds.[type] = 'PS' THEN 1 ELSE 0 END AS is_partitioned FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s JOIN sys.partitions p ON s.[object_id] = p.[object_id] AND s.index_id = p.index_id AND s.partition_number = p.partition_number JOIN sys.indexes i ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id LEFT JOIN ( SELECT c.[object_id] , index_id = ISNULL(i.index_id, 1) , is_lob_legacy = MAX(CASE WHEN c.system_type_id IN (34, 35, 99) THEN 1 END) , is_lob = MAX(CASE WHEN c.max_length = -1 THEN 1 END) FROM sys.columns c LEFT JOIN sys.index_columns i ON c.[object_id] = i.[object_id] AND c.column_id = i.column_id AND i.index_id > 0 WHERE c.system_type_id IN (34, 35, 99) OR c.max_length = -1 GROUP BY c.[object_id], i.index_id ) lob ON lob.[object_id] = i.[object_id] AND lob.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = i.[object_id] JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id WHERE i.[type] IN (1, 2) AND i.is_disabled = 0 AND i.is_hypothetical = 0 AND s.index_level = 0 AND s.alloc_unit_type_desc = 'IN_ROW_DATA' AND o.[type] IN ('U', 'V')
Kami berhasil menyingkirkan masalah utama dengan tabir dari jenis kueri yang sama dalam produk yang bersaing, tetapi kelemahan dari penerapan ini adalah tidak ada parameter tambahan yang diteruskan ke fungsi sys.dm_db_index_physical_stats yang dapat membatasi pemindaian indeks yang jelas tidak perlu. Bahkan, ini mengarah pada memperoleh informasi tentang semua indeks dalam sistem dan memuat disk tambahan pada tahap pemindaian.
Penting untuk dicatat bahwa data yang diperoleh dari sys.dm_db_index_physical_stats tidak di-cache secara permanen di buffer pool, oleh karena itu, meminimalkan pembacaan fisik ketika mendapatkan informasi tentang fragmentasi indeks adalah salah satu tugas prioritas selama pengembangan.
Setelah beberapa percobaan, ternyata menggabungkan kedua pendekatan, membagi scan menjadi dua bagian. Pertama, satu kueri besar menentukan ukuran bagian, pra-pemfilteran yang tidak berada dalam rentang pemfilteran:
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
Selanjutnya, kami hanya mendapatkan bagian-bagian yang berisi data untuk menghindari operasi baca yang tidak perlu dari indeks kosong.
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)
Bergantung pada pengaturan, hanya jenis indeks yang diperoleh yang ingin dianalisis oleh pengguna (bekerja dengan heaps, indeks cluster / non-cluster, dan indikator kolom didukung).
INSERT INTO #Indexes SELECT ObjectID = i.[object_id] , IndexID = i.index_id , IndexName = i.[name] , PagesCount = a.ReservedPages , UnusedPagesCount = a.ReservedPages - a.UsedPages , 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
Setelah itu, sedikit keajaiban dimulai: untuk semua indeks kecil, kami menentukan tingkat fragmentasi dengan berulang kali memanggil fungsi sys.dm_db_index_physical_stats dengan indikasi lengkap dari semua parameter.
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)
Selanjutnya, kami mengembalikan semua informasi yang mungkin ke klien, memfilter kelebihan data:
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) )
Setelah ini, kueri titik menentukan tingkat fragmentasi untuk indeks besar.
EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1044198770, @IndexId = 1, @PartitionNumber = 1 EXEC sp_executesql N' DECLARE @DBID INT = DB_ID() SELECT [avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'') WHERE [index_level] = 0 AND [alloc_unit_type_desc] = ''IN_ROW_DATA''' , N'@ObjectID int,@IndexID int,@PartitionNumber int' , @ObjectId = 1552724584, @IndexId = 0, @PartitionNumber = 1
Karena pendekatan ini, saat membuat kueri, dimungkinkan untuk menyelesaikan masalah dengan kinerja pemindaian yang ditemukan dalam aplikasi pesaing. Ini bisa diselesaikan, tetapi dalam proses pengembangan, ide-ide baru secara bertahap muncul yang memungkinkan memperluas ruang lingkup aplikasi produk Anda.
Awalnya, dukungan untuk bekerja dengan WAIT_AT_LOW_PRIORITY telah diterapkan, kemudian dimungkinkan untuk menggunakan DATA_COMPRESSION dan FILL_FACTOR untuk membangun kembali indeks.
Aplikasi sedikit ditumbuhi dengan fungsi yang tidak direncanakan sebelumnya, seperti memperbaiki kolom kolom:
SELECT * FROM ( SELECT IndexID = [index_id] , PartitionNumber = [partition_number] , PagesCount = SUM([size_in_bytes]) / 8192 , UnusedPagesCount = ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) / 8192 , Fragmentation = CAST(ISNULL(SUM(CASE WHEN [state] = 1 THEN [size_in_bytes] END), 0) * 100. / SUM([size_in_bytes]) AS FLOAT) FROM sys.fn_column_store_row_groups(@ObjectID) GROUP BY [index_id] , [partition_number] ) t WHERE Fragmentation >= @Fragmentation AND PagesCount BETWEEN @MinIndexSize AND @MaxIndexSize
Atau kemampuan untuk membuat indeks non-cluster berdasarkan informasi dari dm_db_missing_index:
SELECT ObjectID = d.[object_id] , UserImpact = gs.[avg_user_impact] , TotalReads = gs.[user_seeks] + gs.[user_scans] , TotalSeeks = gs.[user_seeks] , TotalScans = gs.[user_scans] , LastUsage = ISNULL(gs.[last_user_scan], gs.[last_user_seek]) , IndexColumns = CASE WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NOT NULL THEN d.[equality_columns] + ', ' + d.[inequality_columns] WHEN d.[equality_columns] IS NOT NULL AND d.[inequality_columns] IS NULL THEN d.[equality_columns] ELSE d.[inequality_columns] END , IncludedColumns = d.[included_columns] FROM sys.dm_db_missing_index_groups g WITH(NOLOCK) JOIN sys.dm_db_missing_index_group_stats gs WITH(NOLOCK) ON gs.[group_handle] = g.[index_group_handle] JOIN sys.dm_db_missing_index_details d WITH(NOLOCK) ON g.[index_handle] = d.[index_handle] WHERE d.[database_id] = DB_ID()
Ringkasan
Setelah enam bulan fase pengembangan aktif, saya senang bahwa rencana tidak berakhir di sana, karena saya ingin mengembangkan lebih lanjut produk ini. Langkah selanjutnya adalah menambahkan fungsionalitas untuk mencari indeks duplikat atau tidak terpakai, serta mengimplementasikan dukungan penuh untuk melayani statistik dalam SQL Server.
Berdasarkan fakta bahwa ada banyak solusi berbayar di pasaran, saya ingin percaya bahwa karena pemosisian bebas, metadata yang lebih dioptimalkan, dan adanya berbagai hal kecil yang bermanfaat bagi seseorang, produk ini pasti akan menjadi berguna dalam tugas sehari-hari.
Versi aplikasi saat ini dapat diunduh di
GitHub . Sumber ada di tempat yang sama.