Yes, as Tibor set, you can gain speed by saying
ALTER DATABASE db SET PARAMETERIZATION FORCED
this setting causes SQL Server to parameterize every query sent to it.
That is, a query like:
SELECT * FROM Orders WHERE OrderStatus = 'Bad' AND OrderDate < '20070101'
will be put into the cache as:
SELECT * FROM Orders WHERE OrderStatus = @1 AND OrderDate < @2
This measure can be a big speed winner with an application that generates
SQL with inlined parameter values, despite this being bad practice. And Mmgt
Studio is such an application. It's certainly embarrassing that Microsoft
themselves cannot adhere to good practice.
The reason this is a winner is that when the same query is repeated all
over again, just with different parameter values, SQL Server does not have
to spend time on compiling the queries.
But you should not have forced parameterization with a well-written
application that uses parameterised queries or stored procedures, since
SQL my parameterise where it shouldn't.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
