wiqbit - Services - Database Support

Database Support

A man standing in front of a computer screen

Keep your databases running at their maximum potential and protect them to ensure your business continuity. We like to use C# to automate the maintenance and monitoring of SQL Server, MySQL, and Oracle databases.

SQL Server

MySQL

Oracle

Backups

Indices

Jobs

Long Queries

Backups

Hardware failures happen; Imagine a faulty disk that leaves the database files corrupted. Human errors happen; Imagine forgetting a WHERE clause and updating or deleting all the records in a table. Using C# helps us automate the process of monitoring backups and identifying disks that are running low on free space to minimize data loss and downtime.

Indices

Using indices incorrectly can be counter-productive and result in degraded performance. Additionally, over time, they may become fragmented and increasingly inefficient. Using C# helps us automate the process of identifying indices that are negatively impacting performance and indices that would benefit from defragmentation.

Jobs

Jobs allow us to automate the execution of repetitive tasks such as checking database integrity, running a query, or shrinking database files, and perform those tasks on a schedule. Using C# helps us automate the process of monitoring jobs and sending notifications when there are issues or sending notifications about their status (running, stopped, etc.).

Long-Running Queries

In our experience, databases support a small, finite number of connections. These connections must be treated as precious resources that are protected from accidental misuse. Using C# helps us automate the process of monitoring for long-running queries that should be optimized to ensure database availability and scalability.

Example of a C# Worker Service

SELECT  DISTINCT
        SCHEMA_NAME(o.schema_id) AS SchemaName
        ,OBJECT_NAME(o.object_id) AS TableName
        ,i.[name] AS IndexName
        ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS IsPartitioned
        ,p.partition_number AS PartitionNumber
        ,dmv.Avg_Fragmentation_In_Percent AS AverageFragmentationInPercent
FROM    sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
        ON i.object_id = p.object_id
        AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
        ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, N'LIMITED') dmv
        ON dmv.object_id = i.object_id
        AND dmv.index_id = i.index_id
        AND dmv.partition_number = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
        ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
        ON ps.data_space_id = ds.data_space_id
WHERE   OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0;

This worker service connects to SQL Server and Azure SQL databases to retrieve information about index fragmentation.

If the fragmentation is >= 30% and the index is partitioned...

ALTER INDEX [{IndexName}] ON [{SchemaName}].[{TableName}]
REBUILD PARTITION = {PartitionNumber};

If the fragmentation is >= 30% and the index is not partitioned...

ALTER INDEX [{IndexName}] ON [{SchemaName}].[{TableName}]
REBUILD;

If the fragmentation is >= 10% and the index is partitioned...

ALTER INDEX [{IndexName}] ON [{SchemaName}].[{TableName}]
REORGANIZE PARTITION = {PartitionNumber};

If the fragmentation is >= 10% and the index is not partitioned...

ALTER INDEX [{IndexName}] ON [{SchemaName}].[{TableName}]
REORGANIZE;

View on GitHub

Let's Talk

Contact