Tag Archives: #DatabaseAdministration

Standard

Posted by

Posted on

May 21, 2026

Posted under

Setup

Comments

Leave a comment

SQL Server Cleanup Using UninstallString When Programs and Features Entry is Missing

Issue

In some environments, SQL Server services may still exist on the server, but the related SQL Server entries are missing from Programs and Features. This can happen due to missing or corrupted registry entries, incomplete uninstallations, or failed patching activities.

To identify the uninstall information from the registry, the following PowerShell script can be used:

Get-ItemProperty HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\*, `
HKLM:\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* |
Where-Object { $_.DisplayName -like “*SQL Server 2016*” } |
Select-Object DisplayName, UninstallString

Solution

The above script helps retrieve the UninstallString for SQL Server components directly from the registry.

In many cases, the uninstall command may contain:

/I

For uninstallation, this should be replaced with:

/X

Example:

MsiExec.exe /x

This forces the uninstall operation instead of launching the installer in maintenance mode.

Important Remarks

  • This approach should mainly be considered for remote support cases or emergency cleanup situations.
  • There is always a possibility of future issues when manually cleaning up SQL Server components using registry-based uninstall methods.
  • The recommended and safest approach is always to rebuild the server if SQL Server installation metadata or registry entries are heavily corrupted.
  • Use this method only as a last resort and proceed at your own risk.

Summary

When SQL Server entries are missing from Programs and Features, the uninstall details can still be retrieved from the Windows registry using PowerShell. Replacing /I with /X in the uninstall command can help remove orphaned SQL Server components. However, since this method relies on registry-based cleanup, it should only be used cautiously in exceptional scenarios, with server rebuild remaining the preferred long-term solution.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

March 28, 2024

Posted under

Azure Managed Instance, Azure SQL Database, Performance

Comments

Leave a comment

Elevating SQL Server Performance with In-Memory OLTP Table Variables

In the realm of database management, optimizing performance while minimizing resource contention is a perennial challenge. One of the critical areas where contention can be notably reduced is in the utilization of temporary storage mechanisms like tempdb. SQL Server’s In-Memory OLTP feature offers a compelling solution to this challenge, particularly through the use of in-memory optimized table variables. This post explores how leveraging In-Memory OLTP table variables can significantly improve performance by alleviating tempdb allocation contention, especially when compared to traditional table variables, temporary tables, or common table expressions (CTEs).

Understanding tempdb Contention

Before diving into the solution, it’s crucial to understand the problem. The tempdb system database in SQL Server is a shared resource used for various temporary storage activities, including user-created temporary tables, temporary stored procedures, table variables, and CTEs. However, heavy tempdb usage, particularly in high-concurrency environments, can lead to contention issues, impacting overall database performance.

Enter In-Memory OLTP Table Variables

In-Memory OLTP, introduced in SQL Server 2014, revolutionized how data is stored and accessed by storing designated tables and table variables in memory rather than on disk. This feature not only speeds up data access but also significantly reduces contention by bypassing traditional disk-based storage mechanisms.

Defining an In-Memory OLTP Table Variable

Unlike regular table variables, an in-memory OLTP table variable is defined as a user-defined table type with the MEMORY_OPTIMIZED = ON option. Here’s how to create one:

CREATE TYPE JB_InMem_TableType AS TABLE (
    col1 INT,
    col2 DATETIME,
    col3 VARCHAR(255),
    INDEX IX_JB_InMem_TableType_Col1 NONCLUSTERED (Col1)
) WITH (MEMORY_OPTIMIZED = ON);
GO

Utilizing In-Memory OLTP Table Variables in Stored Procedures

In-memory OLTP table variables can be seamlessly integrated into stored procedures. Here’s a simple example:

CREATE PROCEDURE sp_proc1
    @Col1 INT
AS
BEGIN 
    DECLARE @jb_table1 JB_InMem_TableType;
    INSERT INTO @jb_table1 
    SELECT col1, col2, col3 FROM Table1
    SELECT col1, col2, col3 FROM @jb_table1 WHERE col1 = @Col1;
END
GO

In this example, @jb_table1 is populated from Table1 and then queried, all while residing entirely in memory.

Benefits Over Traditional Methods

The use of in-memory OLTP table variables offers several advantages over traditional table variables, temporary tables, or CTEs:

  • Reduced tempdb Contention: Since in-memory objects do not rely on tempdb, they avoid contributing to tempdb contention, a common bottleneck in high-concurrency scenarios.
  • Performance Improvement: Accessing data in memory is significantly faster than disk-based access, leading to improved query performance.
  • Transactional Consistency: In-memory OLTP supports fully ACID-compliant transactions, ensuring data integrity without compromising on performance.

Conclusion

In-memory OLTP table variables represent a powerful tool in the SQL Server performance tuning arsenal. By leveraging in-memory storage, developers and DBAs can achieve significant performance improvements and reduce contention issues associated with tempdb. Whether you’re building high-performance applications or optimizing existing database workloads, the use of in-memory OLTP table variables is certainly worth considering.

For more tutorials and tips on SQL Server, including performance tuning and database management, be sure to check out our JBSWiki YouTube channel.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Standard

Posted by

Posted on

January 23, 2024

Posted under

Query Store

Comments

Leave a comment

Unveiling Query Performance Insights: Leveraging T-SQL with SQL Server Query Store

Introduction

In the dynamic realm of database management, monitoring and optimizing query performance is paramount. SQL Server offers the Query Store feature, a powerful tool that captures and stores detailed information about query execution plans and runtime statistics. In this blog post, we’ll delve into leveraging T-SQL to extract valuable insights from the Query Store, focusing on queries executed within the last 7 days.

Query Store Essentials

Before we dive into T-SQL scripts, let’s briefly revisit the Query Store. It acts as a repository for query-related data, allowing database administrators to analyze historical performance metrics and troubleshoot issues efficiently.

Comprehensive Query Analysis in the Last 7 Days

To get a comprehensive view of queries executed in the last 7 days, we can use the following T-SQL script:

-- T-SQL script to analyze queries executed in the last 7 days

SELECT 
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
	ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time BETWEEN (getdate()-7) AND (getdate())
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY count_executions DESC

This script provides a detailed breakdown of query performance, including execution counts, total duration, CPU time, and more, for queries executed in the last 7 days.

Specific Query Search Example

Suppose you want to focus on queries containing a specific keyword within the last 7 days. The following T-SQL script demonstrates how to achieve this:

-- T-SQL script to search for a specific query in the last 7 days

SELECT 
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
	ROUND(CONVERT(float, SUM(rs.avg_cpu_time*rs.count_executions))*0.001,2) total_cpu_time,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_reads*rs.count_executions))*8,2) total_logical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_physical_io_reads*rs.count_executions))*8,2) total_physical_io_reads,
	ROUND(CONVERT(float, SUM(rs.avg_logical_io_writes*rs.count_executions))*8,2) total_logical_io_writes,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.first_execution_time BETWEEN (getdate()-7) AND (getdate())
and qt.query_sql_text LIKE 'DELETE FROM%'
GROUP BY p.query_id, qt.query_sql_text, q.object_id
HAVING COUNT(distinct p.plan_id) >= 1
ORDER BY count_executions DESC

This example narrows down the search to queries with the keyword “DELETE FROM” executed in the last 7 days.

Conclusion

In conclusion, harnessing the power of T-SQL with the SQL Server Query Store provides a robust way to analyze and optimize query performance. The provided scripts offer a starting point for gaining valuable insights into the queries executed in the last 7 days, enabling proactive database management and performance tuning.

Stay tuned for more in-depth explorations of SQL Server features and best practices!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.