Find single-use, ad-hoc, and prepared queries

Ever wondered what's lurking in your SQL Server's plan cache? This handy query helps you find single-use ad-hoc and prepared statements that might be cluttering up your memory. Identifying these can be a great first step in optimizing your database performance!

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
 ,t.[text] AS [Query Text]
 ,cp.objtype AS [Object Type]
 ,cp.cacheobjtype AS [Cache Object Type]
 ,cp.size_in_bytes / 1024 AS [Plan Size in KB]
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
WHERE cp.cacheobjtype = N'Compiled Plan'
 AND cp.objtype IN (N'Adhoc', N'Prepared')
 AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
 ,DB_NAME(t.[dbid])
OPTION (RECOMPILE);

This query leverages two powerful Dynamic Management Views (DMVs) in SQL Server to gather its insights:

• sys.dm_exec_cached_plans: This DMV provides information about query plans in the cache.
• sys.dm_exec_sql_text: Use this to retrieve the actual text of a SQL batch or individual statement.

Get database file info

Ever wondered where your SQL Server database files are actually stored? This handy query helps you quickly find the **file names and physical paths for all user and system databases** on your instance. It's a great way to get an overview of your database file locations and properties.

SELECT DB_NAME([database_id]) AS [Database Name]
 ,[file_id]
 ,[name]
 ,physical_name
 ,[type_desc]
 ,state_desc
 ,is_percent_growth
 ,growth
 ,CONVERT(BIGINT, growth / 128.0) AS [Growth in MB]
 ,CONVERT(BIGINT, size / 128.0) AS [Total Size in MB]
 ,max_size
FROM sys.master_files WITH (NOLOCK)
ORDER BY DB_NAME([database_id])
 ,[file_id]
OPTION (RECOMPILE);

This query pulls information from the sys.master_files catalog view, which provides details about the files in your SQL Server instance.

How to Get TempDB Files Count in SQL Server

Ever wonder how many data files your tempdb database is currently using? You can quickly find this information by querying the SQL Server error log. This can be helpful for monitoring your SQL Server instance's configuration and performance.

-- Get number of data files in tempdb database (Query 24) (TempDB Data Files)
EXEC sys.xp_readerrorlog 0, 1, N'The tempdb database has';
  

The above SQL snippet uses the xp_readerrorlog extended stored procedure to filter the error log for messages related to the number of tempdb data files. This provides a direct way to see the current count without needing to delve into system views.

---

More SQL Server Insights

Here are some other valuable resources and tips for managing your SQL Server environment:

Reading Specific Errors with Temporary Tables

For more advanced error log analysis, check out this post on how to read specific errors using temporary tables. This technique offers greater flexibility in parsing and analyzing log data.

Retrieving I/O Warnings from the Log

Input/Output (I/O) warnings can be crucial indicators of performance bottlenecks. Learn how to retrieve I/O warnings directly from your SQL Server log to proactively address potential issues.

Understanding SQL Socket Core Information

For a deeper dive into your SQL Server's configuration, exploring SQL socket core information can provide valuable insights into how your server is utilizing its CPU resources.

Suspect Pages table in SQL server

This table tracks pages that encountered a minor 823 error or an 824 error, listing one row per problematic page. While these pages are flagged as potentially corrupt, they might still be intact. The **event_type** column is updated once a suspect page is repaired, reflecting its new status.

SELECT 
    DB_NAME(database_id) AS [Database Name],
    [file_id],
    page_id,
    event_type,
    error_count,
    last_update_date
FROM 
    msdb.dbo.suspect_pages WITH (NOLOCK)
ORDER BY 
    database_id
OPTION (RECOMPILE);

For more details, refer to the official Microsoft documentation on suspect_pages.