Read Windows Registry from SQL server

Ever wonder about the nitty-gritty details of your SQL Server's underlying hardware? Sometimes, peeking into the Windows Registry can give you some valuable insights. Here are a few handy SQL commands to pull essential hardware information, along with some important considerations for optimizing performance.

Unveiling Storage Driver Parameters

For those running SQL Server on VMware, understanding your storage driver settings is crucial for optimal I/O. You can query the registry to see the current driver parameters for your PVSCSI adapter:

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\services\pvscsi\Parameters\Device', N'DriverParameter';

VMware suggests specific values for intensive I/O workloads: RequestRingPages=32 and MaxQueueDepth=254. These settings can significantly impact your disk performance.

For more in-depth information, refer to the official VMware knowledge base article: https://kb.vmware.com/s/article/2053145

---

Discovering Your BIOS Release Date

Knowing your system's BIOS release date can be useful for troubleshooting or determining if you're running on an up-to-date firmware version. Here's how to retrieve it directly from the Windows Registry:

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\BIOS', N'BiosReleaseDate';

---

Identifying Your Processor Details

Understanding your server's processor is fundamental to SQL Server performance tuning. You can grab the full processor description from the registry with this command:

EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

For insights into processor selection for SQL Server and how it impacts performance, check out this valuable resource:

https://www.sqlskills.com/blogs/glenn/processor-selection-for-sql-server/

And if you're looking for a comprehensive utility to analyze your CPU and other system components, CPU-Z is a fantastic tool:

https://www.cpuid.com/softwares/cpu-z.html

How to Identify Tables Without a Primary Key in SQL Server

In SQL Server, primary keys play a crucial role in maintaining data integrity and optimizing query performance. However, during development or when working with legacy databases, you may come across tables that lack a primary key—either by design or oversight.

Identifying these tables is an essential step in ensuring your database is well-structured and reliable.

📌 SQL Query to Find Tables Without a Primary Key

The query below retrieves a list of all tables in your database that do not have a primary key defined:

SELECT s.[name] + N'.' + t.[name] AS [Table]
FROM sys.tables t
INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.key_constraints kc
    WHERE kc.[type] = N'PK'
      AND kc.parent_object_id = t.[object_id]
);

🧩 Why This Matters

Tables without primary keys can lead to:

• Duplicate or inconsistent data
• Poor query performance
• Issues with joins and indexing strategies
• Complications in replication, ETL, and other operations

Make it a habit to review your schema regularly and ensure every table has a well-defined primary key—unless there's a valid reason not to.