Register now or log in to join your professional community.
Is there any settings to improve adhoc query performance in SQL server? Will it leads to SQL server memory leakage?
If you specify correctly (type, size, precision and scale) of your parameters, the SQL Optimizer could reuse your query without rebuilding query plan.
If you don't specify, SQL will create many different queries in the plan cache as you have different string lengths and will avoid the optimization.
Parameterized query will definitely improve query processing performance. However, query without parameters will take more time and in case of many queries, bottlenecks may be created.
You can improve the performance of SQL Server by carefully designing your query with performance factor in mind. Implement indexes, test your queries for performance and in NO case query more data then needed.
