As is known, indexes play a crucial role in databases, providing quick access to the necessary records. Therefore, it is important to maintain them in a timely manner. There is a considerable amount of material written about analysis and optimization, including on the Internet. For example, there was a recent overview of this topic in .
There are many paid and free solutions for this. For instance, there is a ready-made , based on an adaptive method for index optimization.
Next, let’s look at the free utility , created by .
The main technical difference between SQLIndexManager and several other analogs is highlighted by the author himself. and .
In this article, we will also take a look at the project and the capabilities of this software solution.
This utility is discussed .
Over time, most of the comments and bugs have been fixed.
So, let's now move on to the SQLIndexManager utility itself.
The application is written in C# .NET Framework 4.5 using Visual Studio 2017 and employs DevExpress for the forms:
and looks as follows:
All queries are formed in the following files:
- Index
- Query
- QueryEngine
- ServerInfo
When connecting to the database and sending queries to the DBMS, the application is registered as follows:
ApplicationName=”SQLIndexManager” When the application is launched, a modal window will open for adding a connection:
Currently, loading the complete list of all instances of MS SQL Server available over local networks is not working.
You can also add a connection using the far-left button on the main menu:
Next, the following queries will be executed against the DBMS:
- Obtaining information about the DBMS
SELECT ProductLevel = SERVERPROPERTY('ProductLevel') , Edition = SERVERPROPERTY('Edition') , ServerVersion = SERVERPROPERTY('ProductVersion') , IsSysAdmin = CAST(IS_SRVROLEMEMBER('sysadmin') AS BIT) - Getting a list of available databases with their brief properties
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
After executing the above scripts, a window will appear containing brief information about the databases of the selected MS SQL Server instance:
It is worth noting that additional information is displayed based on permissions. If there are , then you can choose data from the view . If such permissions do not exist, then fewer data is simply returned to avoid slowing down the query.
Here you need to select the databases of interest and click the 'OK' button.
Next, the following script will be executed for each selected database to analyze the state of the indexes:
Index state analysis
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 = @Fragmentation
OR
i.PagesCount > @PreDescribeSize
OR
i.IndexType IN (5, 6)
)
As evidenced by the queries themselves, temporary tables are frequently used. This is done to avoid recompilations, and in the case of a large schema, the plan can be generated in parallel when data is inserted, since inserting with table variables is only possible in a single stream.
After executing the above script, a window will appear with the index table:
Here you can also output other detailed information such as:
- database
- number of sections
- date and time of the last access
- compression
- file group
etc.
The columns themselves can be configured:
In the cells of the Fix column, you can choose what action will be performed during optimization. Additionally, upon completion of the scan, the default action is chosen based on the selected settings:
You need to select the required indexes for processing.
Using the main menu, you can both save the script (this same button starts the optimization process of the indexes):
as well as save the table in various formats (this same button allows you to open detailed settings for analyzing and optimizing indexes):
You can also refresh the information by clicking the third button on the left in the main menu next to the magnifying glass.
The button with the magnifying glass allows you to select the necessary databases for review.
Currently, there is no complete help system. Therefore, clicking the button '?' will simply bring up a modal window containing essential information about the software product:
In addition to everything described above, there is a search bar in the main menu:
When starting the index optimization process:
You can also view the log of executed actions at the bottom of the window:
In the detailed settings window for index analysis and optimization, you can configure more fine-tuned options:
Suggestions for the application:
- to make it possible to selectively update statistics not only for indexes but also in different ways (fully or partially update)
- to make it possible to not only select databases but also different servers (this is very convenient when there are many instances of MS SQL Server)
- for greater flexibility in usage, it is suggested to wrap commands in libraries and output them to PowerShell commands, similar to what is done here:
- Enable saving and changing personal settings both for the entire application and, if necessary, for each instance of MS SQL Server and each database.
- From points 2 and 4, the desire arises to create groups by databases and groups by instances of MS SQL Server for which the settings are the same.
- Implement a duplicate index search (both full and partial, which either slightly differ or differ only in included columns).
- Since SQLIndexManager is used solely for MS SQL Server databases, this should be reflected in its name, for example: SQLIndexManager for MS SQL Server.
- Extract all non-GUI parts of the application into separate modules and rewrite them in .NET Core 2.1.
At the time of writing this article, point 6 from the wishes is actively being developed and is already supported with the ability to search for full and similar duplicates.
file — continuous reading of events from one or more local files;
Source: habr.com
