How to Search SQL Server Error Logs Using T-SQL and Temp Tables

Summary: Learn how to efficiently query the SQL Server Error Log using sp_readerrorlog and temporary tables to filter out noise and identify critical system errors.

Read Specific SQL Server Errors Using a Temp Table

Ever need to quickly check recent errors in your SQL Server logs without scrolling through thousands of lines in the Log File Viewer? This handy T-SQL snippet allows you to query the 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 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 system events.

-- Create a temp table to hold log data
CREATE TABLE #t (dt DATETIME, process VARCHAR(200), msg VARCHAR(1000));

-- Populate the table by reading current and archived logs
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';

-- Filter results to ignore routine logon messages
SELECT * 
FROM #t 
WHERE process <> 'logon'
ORDER BY dt DESC;

-- Cleanup
DROP TABLE #t;
        

---

Found this T-SQL tip helpful? Share it with your fellow DBAs or subscribe for more SQL Server troubleshooting guides!

How to Analyze SQL Server Execution Plans from Saved XML Files via T-SQL

Summary: Learn how to load saved SQL execution plan (.xml) files into SQL Server, query historical plan handles, and use XQuery to extract performance metrics like CPU and IO costs.

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

If you have an execution plan saved as an XML file (often with a .sqlplan extension), you aren't limited to just viewing it graphically. You can load it into SQL Server and query the XML directly to find specific performance bottlenecks. Here is how to read and analyze it using T-SQL.

1. Load the Execution Plan File

The OPENROWSET function is the most efficient way to pull an external .xml file into a SQL variable for processing.

DECLARE @xml XML;

-- Load file content into XML variable
SELECT @xml = CAST(FileData.BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\TEMP\MyPlan.XML', SINGLE_CLOB) AS FileData;

-- Verify the content
SELECT @xml AS LoadedPlan;
        

2. Retrieve Historical Queries via DMVs

If you don't have the file but need to find a plan for a recently executed query, use the Dynamic Management Views (DMVs). This snippet searches for a specific text pattern in your plan cache.

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;
        

3. Deep-Dive Analysis using XQuery

Once your plan is in an XML variable, use XQuery and namespaces to extract specific attributes like PhysicalOp, EstimateCPU, and EstimatedTotalSubtreeCost for every operator in the plan.

-- Define the namespace for SQL Server Showplans
;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]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'float') AS SubtreeCost,
    c.value('.[1]/@EstimateRows', 'float') AS EstimatedRows,
    c.value('.[1]/@EstimateCPU', 'float') AS EstimateCPU,
    c.query('.') AS NodeXml
FROM @xml.nodes('//RelOp') AS T(c)
ORDER BY SubtreeCost DESC;
        

💡 Final Thoughts

By leveraging SQL Server's XQuery capabilities and DMVs, you can gain deep insights into query performance behavior directly from saved files. This method is essential for automated performance auditing or comparing plans across different server environments.

Found this optimization guide helpful? Share it with your SQL community or subscribe for more advanced performance tuning tips!