ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

What happens if an application sends Adhoc queries to SQL server continuously, without defining parameters data type and it's size?

 Is there any settings to improve adhoc query performance in SQL server?  Will it leads to SQL server memory leakage?

user-image
تم إضافة السؤال من قبل مستخدم محذوف‎
تاريخ النشر: 2016/04/04
Belal AL-Hifawi
من قبل Belal AL-Hifawi , Technical Team leader , PIONEERS TECH SOFT

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.

Rizwan Afgan
من قبل Rizwan Afgan , SQL Server Administrator / Developer , Oracas (Pvt) Ltd

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. 

 

المزيد من الأسئلة المماثلة