none
can FORCED parameterization can help on reporting databases, RRS feed

  • Question

  • Hi,

       The situation is, i have 1000 queries  getting executed (sent)  from web application and 400 sp in Database.

    they used to show data from reporting database, it is only used for reporting.

    some of them get big data, some of them get less data.

    i would like to know can FORCED parameterization  help in imporving the performance.

    is there any steps which i should follow before implimenting it, and i noticed i can force parameterization only a set of queries , so please tel me steps to find where and how i can find thoes quries.

    like this one was

    yours sincerely.


    • Edited by rajemessage Friday, November 29, 2013 9:35 AM
    Friday, November 29, 2013 9:00 AM

Answers

  • thank u, is there any way one can find out queries which not prameteried,

    You could run a trace which captures the event SQL:BatchCompleted, but do not include RPC:Completed. This does not mean that everyting to capture is unparameterised - there may not be any parameters at all. There may be constants in the query, but they are really logically constants.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by rajemessage Sunday, December 1, 2013 9:01 AM
    Friday, November 29, 2013 12:49 PM

All replies

  • Forced parameterisation may improve your performance. It may ruin your performance. It may have no effect at all. It all depends on your workload.

    Forced parameterisation is intended as a cover-up for poorly written applications which do not used stored procedures, nor parameterise the commands, but build a single SQL string with all parameter values inlined into the query. That is, if there is a lot of requests to see a certain order, there will be commands sent do SQL Server like this:

      SELECT ... FROM Orders WHERE OrderID = 12334
      SELECT ... FROM Orders WHERE OrderID = 12335
      SELECT ... FROM Orders WHERE OrderID = 12339

    instead of:

      SELECT ... FROM Orders WHERE OrderID = @orderid

    The problem is that all these query strings are saved into the plan cache in SQL Server and takes up a lot of space and reduces the efficiency of the cache. Furhermore, since each request for a new order will not find a hit in the cache, there will be compilation everytime.

    With forced parameterisation, the queries are reinterpreted as parameterised, and a single cache entry is created. The amount of compilation is reduced, and the CPU load on the server decreases.

    But this cuts both ways. Say that there is a query like:

      SELECT * FROM Orders WHERE Status = 'N'

    That is, you want to see new orders. There is a filtered index on Status which is very efficient, since at any time, there are rarely more than 100 new orders (out of a total of many millions). But if this query is parameterised, the index cannot be used, and the query will result in a table scan, with disastrous performance as a consequence.

    If your application uses stored procedure exclusive, the setting will not have any effect at all.

    My recommendation is that you leave this setting off. But if you know that your application uses a lot of queries with inlined parameter values, do this:

    sp_configure 'optimize for ad hoc workloads', 1
    reconfigure

    This will prevent the queries from being cached the first time, and only if they reappear they enter the cache. Many people suggest that this setting should always be on, so if you don't know - turn it on.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, November 29, 2013 9:24 AM
  • When setting parametrization to FORCED, if we stored first query plan that is not optimal, every other request will use this bad plan, and we can get bad performance :)

    Regards Harsh

    Friday, November 29, 2013 9:31 AM
  • thank u, is there any way one can find out queries which not prameteried,

    so that specific query or set of query can be parameteried .

    yours sincerly.

    Friday, November 29, 2013 12:08 PM
  • thank u, is there any way one can find out queries which not prameteried,

    You could run a trace which captures the event SQL:BatchCompleted, but do not include RPC:Completed. This does not mean that everyting to capture is unparameterised - there may not be any parameters at all. There may be constants in the query, but they are really logically constants.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by rajemessage Sunday, December 1, 2013 9:01 AM
    Friday, November 29, 2013 12:49 PM