SQL Server is a powerful database management system that can guide us in optimizing performance. In this blog post, we will explore the concept of Showplan Warnings and how they can help us diagnose and improve query performance.

Previous Showplan Warnings

In versions prior to SQL Server 2012, there were already a few warnings in the execution plan that could assist us in identifying slow query issues. These warnings were categorized into query level warnings and operator level warnings.

Query level warnings included common issues like missing indexes, which were displayed in Management Studio after executing a query. Operator level warnings were raised at the operator level and were indicated by a yellow exclamation mark on the operator icon.

Examples of operator level warnings in previous versions of SQL Server were “No Join Predicates” and “Missing Column Statistics”. These warnings provided valuable insights into potential performance bottlenecks.

New SQL Server 2012 Showplan Warnings

In SQL Server 2012, new Showplan Warnings were introduced to further enhance query troubleshooting and tuning. These warnings specifically addressed hash join and sort spill to tempdb issues caused by low memory conditions.

With the new Showplan Warnings, there was no longer a need to capture trace events to identify sort or hash warnings. The execution plan itself would now display these warnings, making it easier to identify and address performance problems.

Example: Sort Spill Warning

Let’s take a look at an example to understand how the new Showplan Warnings work. Consider the following script:

		USE tempdb
		GO
		-- Preparing data
		CREATE TABLE Plan_Warning
		(id INT PRIMARY KEY CLUSTERED,
		name VARCHAR(25), website CHAR(50))
		GO
		SET NOCOUNT ON
		GO
		-- Pumping in dummy Data
		BEGIN TRAN
		DECLARE @counter INT
		SET @counter = 1
		WHILE @counter <= 100000
		BEGIN
		INSERT INTO Plan_Warning(id, name, website)
		VALUES (@counter, 'Vinod Kumar' + CONVERT(VARCHAR(10), @counter),
		'blogs.ExtremeExperts.com')
		SET @counter = @counter + 1
		END
		COMMIT TRAN
		PRINT 'Load completed ...'
		GO
	

Now, let’s execute the following query:

		SET STATISTICS XML ON
		GO
		SELECT * FROM Plan_Warning
		WHERE id >= 7000
		ORDER BY name DESC OPTION (MAXDOP 1)
		GO
		SET STATISTICS XML OFF
		GO
	

The execution plan for this query will display a warning symbol with the Sort Operator. In the warning section, it will indicate that a spill occurred to tempdb. This happens when the query retrieves more rows than can fit in the available memory.

Similar warnings can also occur for Hash Joins, where the warning will be displayed on the Hash Join node in the execution plan.

Conclusion

Showplan Warnings in SQL Server provide valuable insights into potential performance issues. When encountering these warnings, it is important to analyze the query and consider optimizing it by adding additional WHERE clauses or monitoring TempDB growth and contentions.

By continuously exploring and learning about these fine prints in every release of SQL Server, we can improve the performance of our database queries and ensure efficient data retrieval.