ProHoster > Blogs > Administrācija > Bezmaksīgu rīku SQLIndexManager apskats

Bezmaksīgu rīku SQLIndexManager apskats

Kā zināms, indeksiem ir svarīga loma datubāzēs, nodrošinot ātru meklēšanu nepieciešamajās ierakstos. Tāpēc ir tik svarīgi tos savlaicīgi apkalpot. Par analīzi un optimizāciju ir rakstīts daudz materiālu, tostarp internetā. Piemēram, nesen tika veikts pārskats par šo tēmu šajā publikācijā.

Pastāv daudz maksas un bezmaksas risinājumu šim nolūkam. Piemēram, ir gatavs risinājumu, kas balstās uz adaptīvo indeksu optimizācijas metodi.

Tālāk aplūkosim bezmaksas utilītu SQLIndexManager, kuras autors ir AlanDenton.

Galvenā tehniskā atšķirība starp SQLIndexManager un vairākām citām līdzīgām programmām ir autora paša sniegtais paskaidrojums. šeit un šeit.

Šajā rakstā tāpat aplūkosim projektu un programmatūras risinājuma izmantošanas iespējas.

Par šo utilītu diskutē šeit.
Laika gaitā lielākā daļa piezīmju un kļūdu tika labotas.

Tāpēc tagad pievērsīsimies pašai utilītei SQLIndexManager.

Programma ir uzrakstīta C# .NET Framework 4.5 valodā, izmantojot Visual Studio 2017, un izmanto DevExpress veidlapām:

Bezmaksīgu rīku SQLIndexManager apskats

un izskatās šādi:

Bezmaksīgu rīku SQLIndexManager apskats

Visi vaicājumi tiek veidoti sekojošajos failos:

  1. Index
  2. Query
  3. QueryEngine
  4. ServerInfo

Bezmaksīgu rīku SQLIndexManager apskats

Pieslēdzoties datubāzei un nosūtot vaicājumus datubāzes pārvaldniekam, programma piesakās šādi:

ApplicationName=”SQLIndexManager”

Palaidot programmu, parādīsies modālais logs savienojuma pievienošanai:
Bezmaksīgu rīku SQLIndexManager apskats

Šobrīd vēl nedarbojas pilna MS SQL Server instanču saraksta ielāde, kas pieejams lokālajos tīklos.

Savienojumu var pievienot arī, izmantojot ekrāna kreisajā malā esošo pogu galvenajā izvēlnē:

Bezmaksīgu rīku SQLIndexManager apskats

Tālāk tiks izpildīti sekojošie vaicājumi datubāzes pārvaldniekam:

  1. Informācijas saņemšana par datubāzes pārvaldnieku
    SELECT ProductLevel  = SERVERPROPERTY('ProductLevel')
     , Edition       = SERVERPROPERTY('Edition')
     , ServerVersion = SERVERPROPERTY('ProductVersion')
     , IsSysAdmin    = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT)

  2. Pieejamo datubāzu saraksta iegūšana ar to īsiem īpašību aprakstiem
    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

Pēc iepriekš minēto skriptu izpildes parādīsies logs, kurā būs īsa informācija par izvēlētā MS SQL Server instanču datubāzēm:

Bezmaksīgu rīku SQLIndexManager apskats

Jāatzīmē, ka paplašinātā informācija tiek parādīta atkarībā no tiesībām. Ja ir sysadmin, tad var izvēlēties datus no skatījuma sys.master_files. Ja tādu tiesību nav, tad tiek atgriezti mazāk dati, lai nepalēninātu pieprasījumu.

Šeit nepieciešams izvēlēties interesējošās datu bāzes un nospiest pogu “Labi”.

Tālāk tiks izpildīts šis skripts katrai izvēlētajai datu bāzei indeksa stāvokļa analīzei:

Indeksa stāvokļa analīze

deklarēt @Fragmentation float=15;
deklarēt @MinIndexSize bigint=768;
deklarēt @MaxIndexSize bigint=1048576;
deklarēt @PreDescribeSize bigint=32768;

ISET NOCOUNT ON
ISET ARITHABORT ON
ISET NUMERIC_ROUNDABORT OFF

JA OBJECT_ID('tempdb.dbo.#AllocationUnits') NAV NULL
	DROPS TABLE #AllocationUnits

IZVEIDOT TABLE #AllocationUnits (
	ContainerID   BIGINT PRIMARY KEY
	, ReservedPages BIGINT NOT NULL
	, UsedPages     BIGINT NOT NULL
)

Ievietot #AllocationUnits (ContainerID, ReservedPages, UsedPages)
IZVĒLĒTIES [container_id]
		, SUM([total_pages])
		, SUM([used_pages])
NO sys.allocation_units AR WITH(NOLOCK)
GRUPA PA [container_id]
HAVING SUM([total_pages]) STARPU @MinIndexSize UN @MaxIndexSize

JA OBJECT_ID('tempdb.dbo.#ExcludeList') NAV NULL
	DROPS TABLE #ExcludeList

IZVEIDOT TABLE #ExcludeList (ID INT PRIMARY KEY)

Ievietot #ExcludeList
IZVĒLĒTIES [object_id]
NO sys.objects AR WITH(NOLOCK)
KUR [type] I  ('V', 'U')
		UN ( [is_ms_shipped] = 1 )

JA OBJECT_ID('tempdb.dbo.#Partitions') NAV NULL
	DROPS TABLE #Partitions

IZVĒLĒTIES [object_id]
		, [index_id]
		, [partition_id]
		, [partition_number]
		, [rows]
		, [data_compression]
IEVADĪT #Partitions
NO sys.partitions AR WITH(NOLOCK)
KUR [object_id] > 255
		UN [rows] > 0
		UN [object_id] NAV (IZVĒLĒTIES * NO #ExcludeList)

JA OBJECT_ID('tempdb.dbo.#Indexes') NAV NULL
	DROPS TABLE #Indexes

IZVEIDOT 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)
)

Ievietot #Indexes
IZVĒLĒTIES 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]
NO #AllocationUnits a
PIEVIENOT #Partitions p ON a.ContainerID = p.[partition_id]
PIEVIENOT sys.indexes i AR WITH(NOLOCK) ON i.[object_id] = p.[object_id] UN p.[index_id] = i.[index_id] 
KUR i.[type] I (0, 1, 2, 5, 6)
		UN i.[object_id] > 255

DEKLARĒT @files TABLE (ID INT PRIMARY KEY)
Ievietot @files
IZVĒLĒTIES DISTINCT [data_space_id]
NO sys.database_files AR WITH(NOLOCK)
KUR [state] != 0
		UN [type] = 0

JA @@ROWCOUNT > 0 SĀKT

	DZĒST NO i
	NO #Indexes i
	PA kreisi PAGALMOT sys.destination_data_spaces dds AR WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] UN i.PartitionNumber = dds.[destination_id]
	KUR ISNULL(dds.[data_space_id], i.DataSpaceID) I (IZVĒLĒTIES * NO @files)

BEIGĀ


DEKLARĒT @DBID   INT
		 , @DBNAME SYSNAME

ISET @DBNAME = DB_NAME()
IZVĒLĒTIES @DBID = [database_id]
NO sys.databases AR WITH(NOLOCK)
KUR [name] = @DBNAME

JA OBJECT_ID('tempdb.dbo.#Fragmentation') NAV NULL
	DROPS TABLE #Fragmentation

IZVEIDOT TABLE #Fragmentation (
	ObjectID         INT NOT NULL
	, IndexID          INT NOT NULL
	, PartitionNumber  INT NOT NULL
	, Fragmentation    FLOAT NOT NULL
	, PRIMARY KEY (ObjectID, IndexID, PartitionNumber)
)

Ievietot #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
IZVĒLĒTIES i.ObjectID
		, i.IndexID
		, i.PartitionNumber
		, r.[avg_fragmentation_in_percent]
NO #Indexes i
CROSS APPLY sys.dm_db_index_physical_stats(@DBID, i.ObjectID, i.IndexID, i.PartitionNumber, 'LIMITED') r
KUR i.PagesCount <= @PreDescribeSize
		UN r.[index_level] = 0
		UN r.[alloc_unit_type_desc] = 'IN_ROW_DATA'
		UN i.IndexType I (0, 1, 2)

JA OBJECT_ID('tempdb.dbo.#Columns') NAV NULL
	DROPS TABLE #Columns

IZVEIDOT 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)
)

Ievietot #Columns
IZVĒLĒTIES ObjectID     = [object_id]
		, ColumnID     = [column_id]
		, ColumnName   = [name]
		, SystemTypeID = [system_type_id]
		, IsSparse     = [is_sparse]
		, IsColumnSet  = [is_column_set]
		, MaxLen       = [max_length]
NO sys.columns AR WITH(NOLOCK)
KUR [object_id] I (IZVĒLĒTIES DISTINCT i.ObjectID NO #Indexes i)

JA OBJECT_ID('tempdb.dbo.#IndexColumns') NAV NULL
	DROPS TABLE #IndexColumns

IZVEIDOT 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)
)

Ievietot #IndexColumns
IZVĒLĒTIES 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)
NO sys.index_columns ic AR WITH(NOLOCK)
KUR EXISITS(
		IZVĒLĒTIES *
		NO #Indexes i
		KUR i.ObjectID = ic.[object_id]
			UN i.IndexID = ic.[index_id]
			UN i.IndexType I (1, 2)
	)

JA OBJECT_ID('tempdb.dbo.#Lob') NAV NULL
	DROPS TABLE #Lob

IZVEIDOT TABLE #Lob (
	ObjectID    INT NOT NULL
	, IndexID     INT NOT NULL
	, IsLobLegacy BIT
	, IsLob       BIT
	, PRIMARY KEY (ObjectID, IndexID)
)

Ievietot #Lob (ObjectID, IndexID, IsLobLegacy, IsLob)
IZVĒLĒTIES c.ObjectID
		, IndexID     = ISNULL(i.IndexID, 1)
		, IsLobLegacy = MAX(CASE WHEN c.SystemTypeID I (34, 35, 99) THEN 1 END)
		, IsLob       = 0
NO #Columns c
PA kreisi PIEVIENOT #IndexColumns i ON c.ObjectID = i.ObjectID UN c.ColumnID = i.ColumnID
KUR c.SystemTypeID I (34, 35, 99)
GRUPA PA c.ObjectID
			, i.IndexID

JA OBJECT_ID('tempdb.dbo.#Sparse') NAV NULL
	DROPS TABLE #Sparse

IZVEIDOT TABLE #Sparse (ObjectID INT PRIMARY KEY)
Ievietot #Sparse
IZVĒLĒTIES DISTINCT ObjectID
NO #Columns
KUR IsSparse = 1
		UN IsColumnSet = 1

JA OBJECT_ID('tempdb.dbo.#AggColumns') NAV NULL
	DROPS TABLE #AggColumns

IZVEIDOT TABLE #AggColumns (
	ObjectID        INT NOT NULL
	, IndexID         INT NOT NULL
	, IndexColumns    NVARCHAR(MAX)
	, IncludedColumns NVARCHAR(MAX)
	, PRIMARY KEY (ObjectID, IndexID)
)

Ievietot #AggColumns
IZVĒLĒTIES t.ObjectID
		, t.IndexID
		, IndexColumns = STUFF((
			IZVĒLĒTIES ', [' + c.ColumnName + ']'
			NO #IndexColumns i
			PA kreisi PIEVIENOT #Columns c ON i.ObjectID = c.ObjectID UN i.ColumnID = c.ColumnID
			KUR i.ObjectID = t.ObjectID
				UN i.IndexID = t.IndexID
				UN i.IsIncluded = 0
			GRUPA PA i.OrderID
		)
	FOR XML PATH(''), TYPE).value('(.\/text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
		, IncludedColumns = STUFF((
			IZVĒLĒTIES ', [' + c.ColumnName + ']'
			NO #IndexColumns i
			PA kreisi PIEVIENOT #Columns c ON i.ObjectID = c.ObjectID UN i.ColumnID = c.ColumnID
			KUR i.ObjectID = t.ObjectID
				UN i.IndexID = t.IndexID
				UN i.IsIncluded = 1
			GRUPA PA i.OrderID
		)
	FOR XML PATH(''), TYPE).value('(.\/text())[1]', 'NVARCHAR(MAX)'), 1, 2, '')
FROM (
	IZVĒLĒTIES DISTINCT ObjectID, IndexID
	NO #Indexes
	KUR IndexType I (1, 2)
) t

IZVĒLĒTIES 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
NO #Indexes i
PIEVIENOT sys.objects o AR WITH(NOLOCK) ON o.[object_id] = i.ObjectID
PIEVIENOT sys.schemas s AR WITH(NOLOCK) ON s.[schema_id] = o.[schema_id]
PA kreisi PIEVIENOT #AggColumns a ON a.ObjectID = i.ObjectID UN a.IndexID = i.IndexID
PA kreisi PIEVIENOT #Sparse p ON p.ObjectID = i.ObjectID
PA kreisi PIEVIENOT #Fragmentation f ON f.ObjectID = i.ObjectID UN f.IndexID = i.IndexID UN f.PartitionNumber = i.PartitionNumber
PA kreisi PIEVIENOT (
	IZVĒLĒTIES 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     = (
					IZVĒLĒTIES MAX(dt)
					NO (
						VALUES ([last_user_seek])
						 , ([last_user_scan])
						 , ([last_user_lookup])
						 , ([last_user_update])
					)
			)
NO sys.dm_db_index_usage_stats AR WITH(NOLOCK)
NO [database_id] = @DBID
) u ON i.ObjectID = u.ObjectID UN i.IndexID = u.IndexID
PA kreisi PIEVIENOT #Lob lob ON lob.ObjectID = i.ObjectID UN lob.IndexID = i.IndexID
PA kreisi PIEVIENOT sys.destination_data_spaces dds AR WITH(NOLOCK) ON i.DataSpaceID = dds.[partition_scheme_id] UN i.PartitionNumber = dds.[destination_id]
PIEVIENOT sys.filegroups fg AR WITH(NOLOCK) ON ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id] 
KUR o.[type] I ('V', 'U')
		UN (
			f.Fragmentation >= @Fragmentation
		UN
			i.PagesCount > @PreDescribeSize
		UN
			i.IndexType I (5, 6)
		)

Kā redzams no pašiem pieprasījumiem, diezgan bieži tiek izmantoti pagaidu tabulas. Tas tiek darīts, lai novērstu atkārtotas kompilācijas, un gadījumā, ja ir liela shēma, plāns var tikt ģenerēts paralēli, pievienojot datus, jo pievienošana ar tabulu mainīgajiem ir iespējama tikai vienā plūsmā.

Pēc iepriekš minētā skripta izpildes parādīsies logs ar indeksu tabulu:

Bezmaksīgu rīku SQLIndexManager apskats

Šeit var izvadīt arī citu detalizētu informāciju, piemēram:

  1. datubāze
  2. sekciju skaits
  3. pēdējā piekļuves datums un laiks
  4. saspiešana
  5. failu grupa

utt.
Pašas kolonnas var pielāgot:

Bezmaksīgu rīku SQLIndexManager apskats

Fix kolonnas šūnās var izvēlēties, kāda darbība tiks veikta optimizācijas laikā. Tāpat pēc skenēšanas pabeigšanas noklusējuma darbība tiek izvēlēta pamatojoties uz atlasītām iestatījumiem:

Bezmaksīgu rīku SQLIndexManager apskats

Nepieciešams izvēlēties nepieciešamos indeksus apstrādei.

Ar galveno izvēlni var gan saglabāt skriptu (šī pati poga uzsāk indeksu optimizācijas procesu):

Bezmaksīgu rīku SQLIndexManager apskats

tāpat var saglabāt tabulu dažādos formātos (šī pati poga ļauj atvērt detalizētus iestatījumus indeksu analīzei un optimizācijai):

Bezmaksīgu rīku SQLIndexManager apskats

Informāciju var arī atjaunināt, nospiežot trešo pogu no kreisās puses galvenajā izvēlnē blakus lupai.

Poga ar lupu ļauj izvēlēties nepieciešamās datubāzes apskatei.

Pilnīgas palīdzības sistēmas šobrīd nav. Tāpēc nospiežot pogu “?”, parādīsies tikai modālais logs, kas satur galveno informāciju par programmatūras produktu:

Bezmaksīgu rīku SQLIndexManager apskats

Papildus visam iepriekšminētajam galvenajā izvēlnē ir meklēšanas josla:

Bezmaksīgu rīku SQLIndexManager apskats

Sākot indeksu optimizācijas procesu:

Bezmaksīgu rīku SQLIndexManager apskats

Arī loga apakšdaļā var apskatīt izpildāmo darbību žurnālu:

Bezmaksīgu rīku SQLIndexManager apskats

Detalizēto iestatījumu logā par indeksu analīzi un optimizāciju var iestatīt smalkākas opcijas:

Bezmaksīgu rīku SQLIndexManager apskats

Ieteikumi lietojumprogrammai:

  1. padarīt iespējamu izvēlēties statistikas atjaunināšanu ne tikai indeksiem, bet arī dažādiem veidiem (pilnīgi atjaunināt vai daļēji)
  2. padarīt iespējamu izvēlēties ne tikai DB, bet arī dažādus serverus (tas ir ļoti ērti, kad ir daudz MS SQL Server instanču)
  3. lielākas elastības dēļ izmantošanā piedāvā iepakot komandas bibliotēkās un izvadīt PowerShell komandās, kā tas ir darīts, piemēram, šeit:
  4. dbatools.io/commands
  5. ļaut saglabāt un mainīt personīgās iestatījums gan visai lietojumprogrammai, gan nepieciešamības gadījumā katram MS SQL Server instanca un katrai datubāzei
  6. no 2. un 4. punkta izriet vēlēšanās izveidot grupas pēc datubāzēm un grupas pēc MS SQL Server instancēm, kuru iestatījumi ir vienādi
  7. veikt meklēšanu dublikātu indeksos (pilnajos un nepilnajos, kas vai nu maz atšķiras, vai arī atšķiras tikai pēc iekļautajām kolonnām)
  8. tā kā SQLIndexManager tiek izmantots tikai MS SQL Server DB, tas ir jāatspoguļo nosaukumā, piemēram, šādā veidā: SQLIndexManager for MS SQL Server
  9. visas ne-GUI lietojumprogrammas daļas pārvietot uz atsevišķiem moduļiem un pārrakstīt .NET Core 2.1

Raksta rakstīšanas laikā 6. punkts no vēlēšanām tiek aktīvi attīstīts un jau ir izstrādāta atbalsts pilnu un līdzīgu dublikātu meklēšanai:

Bezmaksīgu rīku SQLIndexManager apskats

Avoti

Avots: habr.com

Iegādājieties uzticamu tīmekļa hostingu ar DDoS aizsardzību, VPS VDS serveriem 🔥 Iegādājieties uzticamu tīmekļa hostingu ar DDoS aizsardzību, VPS VDS serveriem | ProHoster