Get top average elapsed time queries

Ever wondered which queries are hogging resources in your SQL Server database? This handy SQL script can help you identify those troublesome queries by showing you the top 50 based on average elapsed time. It's a fantastic way to quickly pinpoint performance bottlenecks!

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
 ,REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10), ''), CHAR(13), '') AS [Short Query Text]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,qs.min_elapsed_time
 ,qs.max_elapsed_time
 ,qs.last_elapsed_time
 ,qs.execution_count AS [Execution Count]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,qs.total_physical_reads / qs.execution_count AS [Avg Physical Reads]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_elapsed_time / qs.execution_count DESC
OPTION (RECOMPILE);

This script leverages two powerful SQL Server Dynamic Management Views (DMVs):

• sys.dm_exec_query_plan: This DMV provides the execution plan for a query, which is crucial for understanding how SQL Server intends to run your queries and identifying potential issues.
• sys.dm_exec_query_stats: This DMV returns aggregated performance statistics for cached query plans. It's your go-to for seeing how queries have performed over time.

By combining data from these two DMVs, the script gives you a comprehensive overview of your top-performing (or rather, top-resource-consuming) queries, including metrics like average elapsed time, logical and physical reads, and even whether a missing index is suggested in the query plan. This can be an invaluable tool for any database administrator or developer looking to optimize SQL Server performance!

How to find SQL Server Cluster information

 How to find SQL Server Cluster information

In this post, I'll walk you through how to retrieve information about a SQL Server cluster.

SELECT SERVERPROPERTY('IsClustered'), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
       SERVERPROPERTY('MachineName'), SERVERPROPERTY('InstanceName'), SERVERPROPERTY('ServerName')
;

WITH ClusterActiveNode AS (
    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName, CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS (
    SELECT NodeName FROM sys.dm_os_cluster_nodes
)
SELECT b.nodename, ISNULL(active, 'Passive') AS NodeStatus
FROM ClusterNodes AS b
LEFT JOIN ClusterActiveNode AS a ON a.NodeName = b.nodename;

Machine Name: This is the name of the Windows computer where the server instance is running. If the instance is clustered, it returns the name of the virtual server for a SQL Server instance running on a Microsoft Cluster Service.
Instance Name: The name of the specific SQL Server instance the user is connected to.
Server Name: This provides both the Windows server and instance information related to a specified SQL Server instance.

The following query displays the location of the SQL Server failover cluster diagnostic log (SQL Server Error Log). This information is valuable for troubleshooting and also reveals the locations of other error and diagnostic logs.

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

To find information about your storage, you can use the query below:

/* Using In-Built Function to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM fn_virtualservernodes();
GO
EXEC XP_FIXEDDRIVES;

/* Using DMV to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM sys.dm_os_cluster_nodes;
GO
SELECT * FROM fn_servershareddrives();

/* Using DMV Function to Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM sys.dm_io_cluster_shared_drives;

EXEC master.dbo.xp_readerrorlog 1, 1, N'NETBIOS', NULL, NULL, NULL, N'desc';

Sql server Database list with Size

 

How to List Databases with Their Sizes in SQL Server

If you're managing a SQL Server instance and want to check the size of each database—including both data and log files—you can use the following query. This script retrieves all user databases, calculates the size of their data and log files in megabytes, and displays the results in descending order by data file size.

SQL Query

SELECT
    DB.name,
    SUM(CASE WHEN [type] = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN [type] = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM    sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id IS NULL -- Exclude database snapshots
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC;

Explanation

• sys.master_files contains information about all database files, including data and log files.

• The type column distinguishes file types: 0 for data files and 1 for log files.

• The size is reported in 8KB pages, so multiplying by 8 and dividing by 1024 converts it to megabytes (MB).

• Snapshots are excluded using the source_database_id IS NULL condition.

This query is useful for DBAs who want to monitor and manage database storage more effectively.