As a SQL Server developer or administrator, it is crucial to understand the impact of running ad-hoc queries on a production server. In a recent performance tuning project, I encountered a situation where developers were running large dataset queries randomly on the production server. This raised concerns about the server’s performance and the potential risks associated with such practices.

Running ad-hoc queries on a production server can lead to several issues. Firstly, it can pollute the server’s cache with unnecessary cache plans. These cache plans take up valuable memory and can impact the overall performance of the server. Additionally, ad-hoc queries are not optimized for execution and can result in inefficient query plans, leading to slower query performance.

Instead of running ad-hoc queries on the production server, it is recommended to create alternate scenarios where developers can synchronize the database and query on a separate server. This approach ensures that the production server is dedicated to supporting the production application and is not burdened with development work.

One way to identify ad-hoc queries and their associated cache plans is by using the following query:

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

This query retrieves the text of the ad-hoc queries, their cache plan sizes, and the plan handles. By analyzing the results, you can determine the memory consumption caused by these ad-hoc queries.

If you identify any large plan cache that you deem unnecessary, you can remove it using the following command:

DBCC FREEPROCCACHE(plan_handle)

Make sure to obtain the plan_handle from the previous query, where the third column indicates the plan handle. However, it is important to note that modifying cache plans should be done with caution and only on non-production environments unless you fully understand the implications.

Experience in SQL Server administration or development is valuable, but it should not overshadow the importance of expertise and best practices. Running ad-hoc queries on a production server can have detrimental effects on performance and should be avoided. By following proper methods such as using queries, stored procedures, and optimizing query plans, you can ensure the smooth operation of your production application.

Remember, optimizing SQL Server performance is a continuous process that requires a deep understanding of the underlying concepts and best practices. By avoiding ad-hoc queries on the production server, you can maintain a stable and efficient environment for your applications.