SQL Server Task Manager using TSQL

SQL Server Task Manager & System Memory Query

In this post, we’ll break down a SQL query that retrieves the running tasks from the Windows Task Manager and fetches important system memory statistics from SQL Server. This combined approach helps system administrators monitor running tasks and memory usage on the server effectively. Let's dive right in!

1. Retrieving Task Information from the Task Manager

The first part of the query extracts the list of tasks running on your SQL Server. It uses the xp_cmdshell extended stored procedure to execute the Windows tasklist command, which lists the running processes in CSV format. This data is then inserted into a temporary table.

-- Drop the temporary table if it exists
drop table if exists #TM

-- Create the temporary table for storing task data
Create table #TM (id int identity, tsk varchar(1000))

-- Insert tasklist output into the temporary table
insert into #TM (tsk) exec xp_cmdshell 'tasklist /v /FO csv'
    

Here’s a quick explanation of the steps in this code:

• xp_cmdshell runs the tasklist command with the /v option for detailed information and /FO csv for CSV formatting.
• The task list is inserted into the temporary table #TM</>, where each row contains the task details like process name, PID, memory usage, etc.

2. Cleaning and Formatting the Task Data

Once we have the raw task data, we need to clean it up. A Common Table Expression (CTE) is used here to remove unwanted characters like commas and double quotes from the CSV output, making it easier to extract individual task details later.

;with ct as (
    select *, 
           replace(replace(replace(tsk,'","','|'),'"',''),',','') ntsk
    from #TM where id >= 2
)
    

What’s happening here:

• The replace function is applied multiple times to remove unwanted characters like quotes and commas from the task data.
• The cleaned data is stored in a new column ntsk</>, which will be processed further.

3. Parsing the Data and Extracting Specific Information

Next, we use SQL Server’s JSON_VALUE function to parse the cleaned task data and extract specific fields, such as the task name, PID, memory usage, and others. We also use CROSS APPLY to convert the cleaned data into a JSON array format that’s easier to work with.

Select A.id,
       ImageName = nullif(JSON_VALUE(JS,'$[0]'),''),
       PID = nullif(JSON_VALUE(JS,'$[1]'),''),
       SessionName = nullif(JSON_VALUE(JS,'$[2]'),''),
       SessionNo = nullif(JSON_VALUE(JS,'$[3]'),''),
       MemUsage = cast(replace(nullif(JSON_VALUE(JS,'$[4]'),''), ' k','') as int),
       Status = nullif(JSON_VALUE(JS,'$[5]'),''),
       UserName = nullif(JSON_VALUE(JS,'$[6]'),''),
       CPUTime = nullif(JSON_VALUE(JS,'$[7]'),''),
       WindowTitle = nullif(JSON_VALUE(JS,'$[8]'),'')
From ct A
CROSS APPLY (values ('["'+replace(string_escape(ntsk,'json') ,'|','","')+'"]') ) B(JS)
order by MemUsage desc
    

Here’s how the data is processed:

• The string_escape function prepares the data for JSON formatting by escaping special characters, and the replace function replaces pipes with commas to create a valid JSON array.
• JSON_VALUE is then used to extract specific fields from the JSON array. For example, $[0] extracts the image name (task name), $[1] gets the PID, and so on.
• We use nullif to handle empty strings and replace them with NULL.
• The result is ordered by memory usage in descending order to highlight the processes that are consuming the most resources.

4. Retrieving System Memory Information

Finally, we fetch system memory details using the sys.dm_os_sys_memory DMV (Dynamic Management View). This provides crucial information about the physical memory available on the server.

-- Retrieve system memory information
SELECT   @@servername as servername,
         total_physical_memory_kb / 1024 AS total_physical_memory_mb,
         available_physical_memory_kb / 1024 AS available_physical_memory_mb,
         system_memory_state_desc
FROM     sys.dm_os_sys_memory;
    

What this query does:

• total_physical_memory_kb and available_physical_memory_kb give the total and available physical memory in kilobytes. We divide these by 1024 to convert them into megabytes (MB).
• system_memory_state_desc describes the current state of the system’s memory (whether it’s under stress, healthy, etc.).
• @@servername Returns the name of the SQL Server instance where this query is running.

5. Conclusion

This SQL query provides an effective way to monitor running tasks and system memory on your server. It allows you to:

• Retrieve detailed task/process information from the Windows Task Manager.
• Format and clean up raw task data into a readable structure.
• Get important memory statistics to keep your server healthy and optimized.

By using this query, you can quickly identify high-memory processes and track overall system performance in a simple, easy-to-understand format.

Note: The xp_cmdshell extended stored procedure must be enabled for this query to work. Make sure your environment allows the execution of this command.

If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!

How to Remove Duplicate Records in SQL Server

If you're working with databases, one common problem you might face is duplicate data. Duplicates can cause a lot of headaches, especially when it comes to data analysis or reporting. But don’t worry! In this post, we’ll walk through a simple way to remove duplicate records in SQL Server using a stored procedure that you can use in your own projects.

We’ll break down the SQL code step-by-step to make sure you understand how it works. By the end of this tutorial, you’ll be able to use this technique to keep your data clean and organized.

Step 1: What is a Duplicate Record?

A duplicate record is when you have two or more rows in your table with the exact same data in one or more columns. For example, if you have a list of customers and two customers have the same name, email, and phone number, those would be considered duplicates.

Step 2: The Concept of the RemoveDuplicate Procedure

In SQL Server, you can use a stored procedure to automate the process of removing duplicates. A stored procedure is like a saved set of SQL commands that can be executed later. The RemoveDuplicate procedure that we’ll look at helps to:

• Find duplicate records in a table.
• Remove the extra copies, leaving only one unique row.

Step 3: Understanding the Code

Let’s break down the SQL code that removes duplicates. Don’t worry if it seems complex at first. We’ll explain it in a way that’s easy to follow.

Step 3.1: The Procedure and Parameters

The procedure is created with the following parameters:

CREATE PROCEDURE RemoveDuplicate @SCDID sysname, @IsDebug bit = 0

• @SCDID: This is an ID that identifies the specific rule you want to apply for removing duplicates. It helps the procedure know which table to work with.
• @IsDebug: This is a flag that lets you print out the SQL commands for debugging. If you're just starting out, you can use this to see the queries that will be executed.

Step 3.2: Using ROW_NUMBER() to Identify Duplicates

The first part of the code uses a Common Table Expression (CTE) and the ROW_NUMBER() function to label rows:

WITH ct AS (
    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
    FROM SourceDatabase.SourceSchema.SourceObject
)

The ROW_NUMBER() function assigns a unique number to each row, starting from 1. The key part is PARTITION BY, which groups rows based on a specific column. For example, if you're looking for duplicates in the "Email" column, all rows with the same email will be grouped together.

After the row numbers are assigned, we can delete the duplicates.

Step 3.3: Removing Duplicates

Now that the rows are numbered, we can delete the duplicates by selecting rows with rn >= 2 (anything that’s not the first occurrence):

DELETE FROM ct WHERE rn >= 2;

This means that if there are multiple rows with the same data in the ColumnSearch, all except the first one will be deleted.

Step 3.4: Handling Column Exclusions

Sometimes, you don’t want to compare all columns for duplicates. In that case, we use the ColumnExclude parameter to exclude certain columns from the comparison. The code dynamically builds a list of column names, excluding any columns specified in ColumnExclude:

SELECT @rOut = STRING_AGG(c.name, ',')
    FROM SourceDatabase.sys.COLUMNS c
    WHERE c.name NOT IN (@ColumnExclude)

Step 3.5: Final Cleanup Using LAG()

In the final step, we use the LAG() function, which allows us to compare each row with the previous row in a group. If the current row has the same data as the previous one, it’s considered a duplicate and will be removed:

WITH ct AS (
    SELECT *, Ckp = CONCAT(@rval),
           PRv = LAG(CONCAT(@rval)) OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
    FROM SourceDatabase.SourceSchema.SourceObject
)
DELETE FROM ct WHERE ISNULL(ckp, '') = ISNULL(PRv, '');

Step 4: Executing the Procedure

Once you understand how the procedure works, you can execute it to remove duplicates. Simply call the procedure with the SCDID (rule ID) and @IsDebug parameter (set to 1 for debugging):

EXEC RemoveDuplicate @SCDID = 'yourRuleID', @IsDebug = 1;

If @IsDebug is set to 1, the procedure will print out the SQL commands it is going to execute, so you can check them before they run.

Step 5: Original Table Design for SCD2_Rule

Here’s the original design of the SCD2_Rule table used in the stored procedure. This table contains information about your source and target tables and the columns used for comparison:

SCDID	SourceDatabase	SourceSchema	SourceObject	TargetDatabase	TargetSchema	TargetObject	ColumnSearch	ColumnExclude
sysname	sysname	sysname	sysname	sysname	sysname	sysname	nvarchar(-1)	nvarchar(-1)

Step 6: Complete SQL Stored Procedure Code

Here’s the full SQL code for the RemoveDuplicate procedure, including everything we’ve discussed so far:

CREATE PROCEDURE RemoveDuplicate
    @SCDID sysname,
    @IsDebug bit = 0
AS
BEGIN
    DECLARE @Sql AS NVARCHAR(MAX), @rVal varchar(max)

    -- Fetch the SQL query template for the given SCDID
    SELECT @Sql = 'WITH ct AS (
        SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ' + ColumnSearch + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE rn >= 2'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    -- Debugging: Print the dynamic SQL if IsDebug is set to 1
    IF @IsDebug = 1
        PRINT @Sql

    -- Execute the dynamic SQL to remove duplicates
    EXEC SP_EXECUTESQL @Sql

    -- Other duplicate handling: Exclude specified columns
    SELECT @Sql = 'SELECT @rOut = STRING_AGG(c.name, '','')
        FROM ' + SourceDatabase + '.sys.COLUMNS c
        JOIN ' + SourceDatabase + '.sys.tables t ON c.object_id = t.object_id
        WHERE t.name = ''' + SourceObject + ''' 
        AND c.name NOT IN (' + b.a + ') AND c.name <> ''DataValidTo'''
    FROM dbo.SCD2_Rule a
    OUTER APPLY (
        SELECT STRING_AGG(''' + value + ''', ',') a
        FROM STRING_SPLIT(a.ColumnSearch + ',' + a.ColumnExclude, ',') b1
    ) b
    WHERE SCDID = @SCDID

    -- Debugging: Print the dynamic SQL for column exclusions
    IF @IsDebug = 1
        PRINT CONCAT('@Sql : ', @Sql)

    -- Execute the query for excluding columns
    EXEC sp_executesql @Sql, N'@rOut varchar(max) OUTPUT', @rOut=@rVal OUTPUT;

    -- Debugging: Print the result of excluded columns
    IF @IsDebug = 1
        PRINT CONCAT('@rVal : ', @rVal)

    -- Final cleanup: Remove records with identical values in key columns
    SELECT @Sql = 'WITH ct AS (
        SELECT *, Ckp = CONCAT(' + @rVal + '),
            PRv = LAG(CONCAT(' + @rVal + ')) OVER(PARTITION BY ' + REPLACE(ColumnSearch, ',DataSystemDate', '') + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE ISNULL(ckp, '''') = ISNULL(PRv, '''')'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    -- Debugging: Print the final SQL for cleanup
    IF @IsDebug = 1
        PRINT @Sql

    -- Execute the final cleanup query
    EXEC SP_EXECUTESQL @Sql
END
        

Step 7: Conclusion

You’ve just learned how to remove duplicate records from your SQL Server tables using a stored procedure. The RemoveDuplicate procedure is powerful because it allows you to automate the process of cleaning up your data.

Key Takeaways:

• ROW_NUMBER() is used to assign a unique number to each row based on certain columns.
• LAG() compares rows to detect duplicates.
• Dynamic SQL is used to make the procedure flexible for different tables and columns.
• Debugging is made easy with the @IsDebug flag to see the SQL commands before they run.

Now you can keep your SQL Server tables clean by removing duplicate data with ease. If you have any questions or need further clarification, feel free to ask in the comments!

Bonus Tip:

If you're working with large datasets, be sure to test the procedure on a smaller subset of your data before running it on your production database. Always back up your data first!

If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!

How to find unsaved file location for SQL query in management studio

Losing unsaved work can be frustrating, especially in SQL Server Management Studio (SSMS). Here's how to recover your work.

Default Location for Unsaved SQL Files

SSMS may store unsaved queries in temporary locations:

• Windows XP:
  C:\Documents and Settings\<YourUsername>\My Documents\SQL Server Management Studio\Backup Files
• Windows Vista / 7 / 10 / 11:

  %USERPROFILE%\Documents\SQL Server Management Studio\Backup Files
  OR
  %USERPROFILE%\AppData\Local\Temp

Replace <YourUsername> with your actual Windows username.

View Recently Executed SQL Queries

Run this SQL to view recent activity:

USE <Database Name>;
SELECT 
    execquery.last_execution_time AS [Date Time],
    execsql.text AS [Script]
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
ORDER BY execquery.last_execution_time DESC;

⚠️ Replace <Database Name> with the name of your working database.

Enable AutoRecover in SSMS

1. Open SSMS.
2. Go to Tools > Options.
3. Navigate to Environment > AutoRecover.

Adjust settings like time interval and number of backups to keep.

What Happens on Restart?

SSMS prompts to recover unsaved queries after a crash:

Conclusion

Enable AutoRecover and know where unsaved files go to prevent data loss in SSMS.

Read specific errors using temp table

 Read specific errors using a temp table

Ever need to quickly check recent errors in your SQL Server logs? This handy T-SQL snippet allows you to query the SQL Server error log and gather information about various processes and messages. It's a great way to get a snapshot of what's been happening in your SQL Server environment.

Querying SQL Server Error Logs

The following SQL code creates a temporary table and populates it by reading the last seven SQL Server error logs (from 0 to 6). It then selects all entries where the process is not 'logon', helping you filter out routine login messages and focus on actual errors or important events.

create table #t (dt datetime, process varchar(200), msg varchar(1000))
insert into #t exec sp_readerrorlog 0,1,'error'
insert into #t exec sp_readerrorlog 1,1,'error'
insert into #t exec sp_readerrorlog 2,1,'error'
insert into #t exec sp_readerrorlog 3,1,'error'
insert into #t exec sp_readerrorlog 4,1,'error'
insert into #t exec sp_readerrorlog 5,1,'error'
insert into #t exec sp_readerrorlog 6,1,'error'
select * from #t where process <> 'logon'

---

Further Reading and Related Topics

For more insights into SQL Server, error handling, and performance monitoring, check out these excellent resources:

• Manufacturer information inside SQL Server - Learn how to dig into hardware details within your SQL Server instance.
• How to use Extended Events to catch all errors in SQL Server - A deeper dive into using Extended Events for comprehensive error capturing.
• SQL Socket & Core information - Understand how SQL Server utilizes your system's sockets and cores.
• Get IO warning from log - Identify potential I/O bottlenecks or issues from your SQL Server logs.
• Get TempDB files count - A quick tip for checking the number of TempDB files.
• How to find SQL Server Cluster information - Discover details about your SQL Server cluster setup.

Query Plan read using T-SQL

How to Analyze SQL Server Execution Plans from a Saved XML File

If you have an execution plan saved as an XML file, here's how you can read and analyze it in SQL Server.

📄 Step 1: Load the Execution Plan File

Use the OPENROWSET function to load your .xml file into a variable.

DECLARE @plancontents VARCHAR(MAX), @xml XML;
SET @plancontents = (
    SELECT * 
    FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData
);

🔍 Step 2: Retrieve Recent Queries Matching a Specific Pattern

You can extract the top 100 recent SQL queries that match a specific text pattern, along with their execution times and plan handles.

SELECT TOP 100 
    execquery.last_execution_time AS [Date Time],
    execsql.TEXT AS [Script],
    execquery.plan_handle
FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
WHERE execsql.TEXT LIKE '%WITH Temp_Accounts (%'
ORDER BY execquery.last_execution_time DESC;

🧠 Step 3: Analyze Execution Plan Nodes

Once you have the execution plan XML, you can use XQuery to dig into specific attributes of each node, such as cost estimates and operation types.

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    c.query('.') AS ExecPlanNode
FROM @xml.nodes('//child::RelOp') AS T(c)
ORDER BY EstimatedTotalSubtreeCost DESC;

🔗 Useful References

• Escape XML Special Characters in SQL Server
• Working with XML and NULL Columns in SQL Server
• Querying XML Data Types in SQL Server
• Tracking CPU Utilization Trends in SQL Server

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance and execution behavior directly from saved plan files. This method is particularly useful when troubleshooting or optimizing complex queries.

Get a count of SQL connections by IP address

Here's a handy SQL query to get an overview of your active SQL Server connections and sessions. It helps you quickly see who is connected, from where, and how many connections they have open. This can be super useful for monitoring activity or troubleshooting connection issues.

SELECT ec.client_net_address
 ,es.[program_name]
 ,es.[host_name]
 ,es.login_name
 ,COUNT(ec.session_id) AS [connection count]
FROM sys.dm_exec_sessions AS es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections AS ec WITH (NOLOCK) ON es.session_id = ec.session_id
GROUP BY ec.client_net_address
 ,es.[program_name]
 ,es.[host_name]
 ,es.login_name
ORDER BY ec.client_net_address
 ,es.[program_name]
OPTION (RECOMPILE);

This query joins two dynamic management views (DMVs):

• sys.dm_exec_connections: Provides information about the physical connections to the SQL Server.
• sys.dm_exec_sessions: Gives details about each active session on the SQL Server.

By grouping and counting, we can easily see the number of connections per unique combination of client IP address, program name, host name, and login name. The OPTION (RECOMPILE) ensures the query plan is recompiled each time it runs, which can be beneficial for DMVs that frequently change data.

Get tempdb version store space usage by database

Looking to understand how much space the version store is using in your SQL Server tempdb? The sys.dm_tran_version_store_space_usage dynamic management view is your go-to. It provides a table showing the total tempdb space consumed by version store records for each database.

This view is super efficient and inexpensive to run because it doesn't dig into individual version store records. Instead, it gives you an aggregated view of the version store space used in tempdb on a per-database basis. That makes it perfect for quick checks and monitoring!

Retrieve Version Store Space Usage

SELECT DB_NAME(database_id) AS [Database Name],
       reserved_page_count AS [Version Store Reserved Page Count],
       reserved_space_kb / 1024 AS [Version Store Reserved Space (MB)]
FROM sys.dm_tran_version_store_space_usage WITH (NOLOCK)
ORDER BY reserved_space_kb / 1024 DESC
OPTION (RECOMPILE);

This query will give you a clear breakdown of the version store space by database, ordered from largest to smallest.

For more SQL Server insights, check out this article on getting your TempDB files count.

You can also find more detailed documentation on sys.dm_tran_version_store_space_usage on the Microsoft Docs website.

CPU utilization trends

Track SQL Server CPU Usage for the Last 256 Minutes

Ever wonder how your SQL Server has been utilizing its CPU over time? This handy T-SQL script allows you to view the CPU utilization history for the last 256 minutes, broken down into one-minute intervals. It's a fantastic way to quickly pinpoint any performance bottlenecks or trends related to CPU usage.

The SQL Script

DECLARE @ts_now BIGINT = (
  SELECT cpu_ticks / (cpu_ticks / ms_ticks)
  FROM sys.dm_os_sys_info WITH (NOLOCK)
  );

SELECT TOP (256) SQLProcessUtilization AS [SQL Server Process CPU Utilization]
 ,SystemIdle AS [System Idle Process]
 ,100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
 ,DATEADD(ms, - 1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
 SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle]
  ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization]
  ,[timestamp]
 FROM (
  SELECT [timestamp]
    ,CONVERT(XML, record) AS [record]
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
    AND record LIKE N'%<SystemHealth>%'
  ) AS x
 ) AS y
ORDER BY record_id DESC
OPTION (RECOMPILE);

How It Works

This script queries `sys.dm_os_ring_buffers`, specifically looking at the `RING_BUFFER_SCHEDULER_MONITOR` type. This ring buffer captures system health events, including CPU utilization. By parsing the XML output from the `record` column, we can extract details like `SystemIdle` (how much CPU is idle), `SQLProcessUtilization` (how much CPU SQL Server is using), and then calculate the CPU usage by other processes.

For more in-depth information about `sys.dm_os_ring_buffers` and how it's used, check out this great resource: Inside sys.dm_os_ring_buffers.

Give this script a try and let us know in the comments if you found it useful for your SQL Server monitoring!

Hardware information from SQL Server

This query provides valuable basic hardware information about your database server.

It's important to note that a 'HYPERVISOR' value for 'virtual_machine_type_desc' doesn't automatically confirm SQL Server is running inside a virtual machine. This merely indicates that a hypervisor is present on your host machine.

SELECT cpu_count AS [Logical CPU Count], scheduler_count, 
       (socket_count * cores_per_socket) AS [Physical Core Count], 
       socket_count AS [Socket Count], cores_per_socket, numa_node_count,
       physical_memory_kb/1024 AS [Physical Memory (MB)], 
       max_workers_count AS [Max Workers Count], 
       affinity_type_desc AS [Affinity Type], 
       sqlserver_start_time AS [SQL Server Start Time],
       DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [SQL Server Up Time (hrs)],
       virtual_machine_type_desc AS [Virtual Machine Type], 
       softnuma_configuration_desc AS [Soft NUMA Configuration], --SQL Server 2016
       sql_memory_model_desc, --Added in SQL Server 2016 SP1
       container_type_desc -- New in SQL Server 2019
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

For more in-depth information, you can explore the following resources:

• Soft-NUMA (SQL Server)
• sys.dm_os_sys_info

Check all drive space on the server

Ever need to quickly check the available space on your SQL Server drives? Here are a couple of handy SQL queries that can help you out. We'll explore using a dynamic management view and a system stored procedure.

Method 1: Using `sys.dm_os_enumerate_fixed_drives`

This dynamic management view provides detailed information about fixed drives, including their path, type, and free space. It's a robust method for obtaining precise data.

SELECT fixed_drive_path
    ,drive_type_desc
    ,CONVERT(DECIMAL(18, 2), free_space_in_bytes / 1073741824.0) AS [Available Space (GB)]
FROM sys.dm_os_enumerate_fixed_drives WITH (NOLOCK)
OPTION (RECOMPILE);

Method 2: Using `xp_fixeddrives`

For a simpler and quicker overview, the `xp_fixeddrives` extended stored procedure is a great option. It returns a summary of free space on fixed drives.

xp_fixeddrives

You can find more in-depth information about sys.dm_os_enumerate_fixed_drives on the Microsoft documentation.

---

Further Reading on SQL Server Space Management

If you're interested in learning more about managing space in SQL Server, check out these related posts:

• Get TempDB Version Store Space Usage by Database
• Log File Used Space

Resource Governor Resource Pool information

Ever wondered about the current status of your SQL Server resource pools? This quick query provides valuable insights into their performance and memory utilization.

SELECT
    pool_id,
    [Name],
    statistics_start_time,
    min_memory_percent,
    max_memory_percent,
    max_memory_kb / 1024 AS [max_memory_mb],
    used_memory_kb / 1024 AS [used_memory_mb],
    target_memory_kb / 1024 AS [target_memory_mb],
    min_iops_per_volume,
    max_iops_per_volume
FROM
    sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);

This query leverages the sys.dm_resource_governor_resource_pools dynamic management view to give you a clear picture of how your resource governor is managing your SQL Server resources.

Get any memory dumps from SQL Server

Understanding the status of memory dumps in your SQL Server instance is crucial for troubleshooting and performance analysis. This post provides a simple method to quickly get details about any memory dumps, including their location, when they occurred, and their size.

Retrieve SQL Server Memory Dump Information

You can use the following SQL query to access data from the sys.dm_server_memory_dumps dynamic management view. This view provides valuable insights into recent memory dump events.

SELECT
    [filename],
    creation_time,
    size_in_bytes / 1048576.0 AS [Size (MB)]
FROM
    sys.dm_server_memory_dumps WITH (NOLOCK)
ORDER BY
    creation_time DESC
OPTION (RECOMPILE);

This query will return the **filename** of the dump, its **creation time**, and its **size in megabytes**. The results are ordered by creation time, showing the most recent dumps first.

For more in-depth information about the sys.dm_server_memory_dumps view, you can refer to the official Microsoft documentation: sys.dm_server_memory_dumps on Microsoft Docs

Related SQL Server Memory Management Resources

• Get Memory Clerk Usage
• Get In-Memory OLTP Index Usage
• Check Memory Pressure on the Server
• Memory Grants Pending Value
• Resource Governor Resource Pool Information

Manufacturer information inside SQL server

How to Identify Your System's Manufacturer and Model from the SQL Server Error Log

Did you know you can quickly pinpoint your SQL Server's system manufacturer and model number directly from its error log? It's a handy trick, especially when you need to gather system details without digging deep into system properties.

Using a Simple SQL Query

Here's the straightforward SQL query you can use:

EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer';

This command searches the current SQL Server error log for entries containing the word "Manufacturer." The output will typically include a line detailing your system's make and model.

Important Considerations

Keep in mind that this query will only yield results if the error log hasn't been recycled since your SQL Server instance last started. If the log has cycled, the relevant information might no longer be present in the current log.

Verifying Virtual Machine Environments

Beyond just identifying the manufacturer, this method can also be used to confirm if your SQL Server is running within a virtual machine (VM). Many virtualized environments will clearly state "Virtual Machine" or similar in their manufacturer details.

Further Reading and Resources

For more advanced techniques on working with SQL Server error logs and system information, check out these related posts:

• Read specific errors using temp table - Learn how to extract specific error messages efficiently.
• SQL Socket & Core information - Discover how to get details about your SQL Server's CPU sockets and cores.

Determine which scalar UDFs are in-lineable

When you're working with SQL Server, understanding the properties of your functions can be really helpful for performance optimization. The following SQL query helps you identify your scalar user-defined functions (UDFs) and check if they are "inlineable."

Check if Your SQL Scalar Functions are Inlineable

This query provides insight into whether a scalar UDF can be inlined, which can significantly improve query performance by essentially expanding the function's logic directly into the calling query, avoiding the overhead of a function call.

SELECT OBJECT_NAME(m.object_id) AS [Function Name]
 ,is_inlineable
 ,inline_type
FROM sys.sql_modules AS m WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK) ON m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
OPTION (RECOMPILE);

For more detailed information on scalar UDF inlining and related system views, you can refer to the official Microsoft documentation:

• Scalar UDF Inlining
• sys.sql_modules

How to check what databases are accessible?

Ever wonder which databases you can actually get into? There's a simple SQL query that'll show you exactly which ones are accessible on your system. It's a quick way to check your database permissions at a glance.

SELECT name, HAS_DBACCESS(name) FROM sys.databases;

This statement lists each database by name and tells you whether you have access to it. Pretty handy for a quick security check!

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.

SQL Server Services information

Ever wondered about the nitty-gritty details of your SQL Server services? This handy query gives you a quick overview of essential information for each service running on your server.

SELECT servicename
 ,process_id
 ,startup_type_desc
 ,status_desc
 ,last_startup_time
 ,service_account
 ,is_clustered
 ,cluster_nodename
 ,[filename]
 ,instant_file_initialization_enabled
FROM sys.dm_server_services WITH (NOLOCK)
OPTION (RECOMPILE);

This query pulls details like the **service name**, its **process ID**, how it's set to **start up** (manual, automatic, etc.), its **current status**, when it last **started**, the **account** it's running under, and whether it's part of a **cluster**. It even tells you if **Instant File Initialization** is enabled!

For more in-depth information about `sys.dm_server_services`, you can always refer to the official Microsoft documentation.

Get Memory Clerk Usage

Here's a useful SQL query to help you understand how memory is being utilized by different components within your SQL Server instance. This query provides a clear breakdown of memory usage by various memory clerk types, ordered by the highest consumption.

SELECT TOP (10) mc.[type] AS [Memory Clerk Type]
 ,CAST((SUM(mc.pages_kb) / 1024.0) AS DECIMAL(15, 2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC
OPTION (RECOMPILE);

This query leverages the sys.dm_os_memory_clerks Dynamic Management View (DMV), which is an excellent resource for monitoring SQL Server memory. For more in-depth exploration of SQL Server memory and performance, check out these related articles:

• Understanding Memory Grants Pending in SQL Server
• Retrieving Hardware Information from SQL Server
• Another Look at Getting Memory Clerk Usage
• How to Get Memory Dumps from SQL Server

Feel free to leave a comment if you have any questions or further insights on SQL Server memory management!

SQL Server NUMA Node information

This query provides valuable insights into your NUMA (Non-Uniform Memory Access) nodes, showing their composition and how much load they're handling. It helps you understand the distribution of resources and activity across your system's memory architecture.

SELECT node_id
 ,node_state_desc
 ,memory_node_id
 ,processor_group
 ,cpu_count
 ,online_scheduler_count
 ,idle_scheduler_count
 ,active_worker_count
 ,avg_load_balance
 ,resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
OPTION (RECOMPILE);

After running this, you'll want to verify that an equal number of schedulers are assigned to each NUMA node. This is especially important for physical or virtual machines with more than four sockets or over 24 physical cores, as balanced scheduler distribution can significantly impact performance.

For more detailed information, check out these resources:

• Microsoft documentation on sys.dm_os_nodes
• Balancing SQL Server Core Licenses Across NUMA Nodes by SQLskills