Looking into Forced Parameterization

  • I have been reading up about forced parameterization since it was mentioned in a post about dynamic SQL and I am wondering if it would be suitable for a system that I am working on. This web based system makes use of stored procedures and client side ADO parameterised SQL for the majority of the DB work but there are a few parts of the system that are using SQL strings built up with literal values and the pages where this method is being used is actually on the part of the system that is hit the most often, especially when crawlers come visiting. We have noticed the CPU on the SQL server jumps quite high during certain periods and I am wondering if its the recompiling of these queries that is causing the issue. These pages are "result" pages that handles paging, ordering on any column, filtering by any column etc and so the SQL is built up depending on the criteria on the client. I could spend time re-writing these queries to use parameters but I am curious about "Forced Parameterization"

    I read on this SQL Server Performance article the following

    "By default, only relatively simple queries can be parameterized. If your application uses mostly simple queries, the default simple parameterization of SQL Server 2005 might be more than adequate to meet your performance expectations."

    What I want to know is when does simple become complex enough to not be handled by the default simple parameterization? Are there any examples of this breaking point and is there a way I can easily find out if these "result page" queries are being parameterized, cached and re-used or whether they are not being handled by the simple mode and its the recompilation that is causing the CPU issue.

    I have gone over the MS KB and the system would not be affected adversely in terms of computed columns, indexed views etc.

  • Check out the forced parameterization topic in the Books Online (SQL Server's documentation). It lists all the various times that you can't use forced parameterization such as:

    INSERT ... EXECUTE queries

    Statements inside procedures, triggers, udf's since they have execution plans,

    client side prepared statements,

    queries with RECOMPILE hint,

    pattern and escape clause arguments used in LIKE

    There are others. If a healthy percentage of your queries meet these criteria, then you're unlikely to see a benefit from forced parameterization.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I've read the BOL and the site in question does seem suitable as the main page getting hit is using a static SQL string thats being executed and none of the features BOL warns about apply.

    I looked at trying to parameterize the SELECT with ADO but the WHERE clause is build up in another method, cached and then used by multiple SELECT statements on that page so its not a simple change.

    I turned FORCED on for our DEV box this morning however using the following I get a "Incorrect syntax near PARAMETERIZATION' error. However doing through Management console works fine.

    ALTER DATABASE XXXX PARAMETERIZATION FORCED

    I was hoping to see in the cached objects DMV the SELECT I was trying to parameterize but nothing appeared. I made sure to flush the buffers/cache before switching. I thought maybe if it was compiling and caching the plan that this SQL would show it up

    SELECTTOP 100

    [Plan usage] = cp.usecounts

    ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

    (CASE WHEN qs.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

    ,[Parent Query] = qt.text

    ,DatabaseName = DB_NAME(qt.dbid)

    ,cp.cacheobjtype

    --, *

    FROMsys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

    WHEREcp.plan_handle=qs.plan_handle

    --AND ObjType NOT IN('Proc','View','Trigger') --trying to find the adhoc query from client

    AND qt.dbid IN(XXXX) --my DB ID

    ORDER BY [Plan usage] ASC

    Wouldn't I expect to see the query in question related to a cached plan if the FORCE command had made the SELECT compile and then cached it for re-use?

    Also I am still not sure how complex a query has to be before SIMPLE mode becomes insufficent for the job??? The query in question is a SELECT using ROW_NUMBER(), TOP() and OVER() and has 5 parameters.

    I could not see any mention of it in the DMV before I turned FORCED on.

    Thanks for your help.

  • The correct syntax is:

    ALTER DATABASE xxxx SET PARAMETERIZATION FORCED

    You were just missing the SET statement.

    You should still see an execution plan, regardless of whether or not it's got forced parameterization. SQL Server compiles and stores execution plans for everything except trivial plans, which, from the sound of it, this is not.

    Simple parameterization is extremely limited. Usually we're talking about an ad hoc query against a single table.

    Try a simpler version of your query, just against the sys.dm_exec_cached_plans DMV and sys.dm_exec_sql_text DMF, just to get started. Once you've identified that you're seeing the queries in cache, you can expand on it.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The problem was that the dbid column was returing NULL values so the result I was looking for was not being returned. I modified the query to

    SELECTtext,*

    FROMsys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

    INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle

    WHEREcp.plan_handle=qs.plan_handle

    AND (ObjType = 'Adhoc' OR ObjType='Prepared')

    --AND BucketID = 4646 --once FORCED is enabled to check the execution count

    I notice that when SIMPLE or FORCED mode is on the same query run from the website repeatedly causes the execution count to increment (same bucketID). However if I copy and paste the query into a new Query Analyser and run it this causes a new cached plan record and if I repeatedly call that query it will also increment the counter. This seems to suggest that the plan is being cached but not shared between the website and Query Analyser even though the SQL is exactly the same (same offset values etc).

    It also suggests that SIMPLE mode is sufficent for the query to be cached and re-used.

    Would you make the same conclusion?

    Thanks

  • Well, again, any plan will be cached and could be reused. As to whether or not simple is doing what you want, when you look at the query text in the execution plan, do you see variable substitution for the values you pass in so that you see @1, @2, @3 instead of 'a', 'b', 42? If so, then parameterization is occurring. If not, then the query is just being cached and reused when the same values are passed in.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes you are right it was due to the same values being submitted. I changed the values between calls and it causes new plans. The value of objType = AdHoc and there is no parameterization before hand so its just the same plan being used due to the same values being submitted.

    When I change to FORCED I get objType = Prepared and the beginning on the Text value contains the params e.g

    (@0 int,@1 int,@2 int,@3 int,@4 varchar(8000),@5 int,@6 int)

    However I still get 2 different cached plans when FORCED is on when I run the same query (same OR different param values) from the web and QA. The value of the text column is exactly the same for both rows. So I am not quite sure what is going on there.

  • The different plans from the web is probably, but not definately, because of differences in the type of connection or settings within the connection. If you drill down on all the information available through the plan or through the DMV's that describe the plan you can probably identify the issue. I suspect that two connections from the web will see consistent behavior (or at least they should).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes running the same query from different browser instances (IE and FF3) with different param values and the same plan is shared.

    Looking at the differences between columns in the output of the query I am using I can see no difference apart from the I/O,CPU,time values etc. I will have to drill down into some other DMVs when I get a chance.

    Thanks for your help

  • Excellent.

    So you are using forced parameterization then?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Well I've been testing on our dev box to ensure that its worth doing and that we currently aren't benefiting from cached plan re-use for this particular page/query and it looks like FORCED might be the way to go.

    Before making a change to our live box we would want to run some logging before and after to see if helps the CPU situation.

    Also I read somewhere that its possible to turn FORCED P on for just a query using a hint it might be that for this particular query I could turn it on but leave the DB in simple mode. Is that possible?

  • You can using a hint, but since this is ad hoc (right?) you'll need to use a plan guide.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Actually, though... thinking about it. I'm not sure you could make a plan guide work. Hmmm... that might be tough.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes its an adHoc query on an old site ASP classic! being passed to an SQL 2005 DB.

    The system in general runs very well and handles quarter million hits a day with fast response times.

    However the page in question (results page) sometimes gets over-hit with crawlers and we do experience quite high CPU on the SQL box periodically so I think that anything that can help speed up the main query on this page will be benefitial.

  • Just to let you know that it is possible to set the DB to simple mode and run that particular query as forced using a plan guide.

    I just ran a test by setting the DB back to SIMPLE and then created a PLAN GUIDE with

    EXEC sp_create_plan_guide using the template option and setting the hint N'OPTION(PARAMETERIZATION FORCED)';

    I then ran the same queries from both browsers with different values for the BETWEEN clause and the cached plan was parameterized and used multiple times.

    The only issue I have is that it will take quite a few plan guides to cover all the queries I need as the query contains a TOP clause which I notice always causes different cached plans plus that multiplied by the number of tables I am selecting from would be a lot of plan guides. I use one DB for 200+ sites but segment the main data up by having different tables using the site ID as a suffix. So even though the TOP value and the table name are "parameters" they cannot be parameterized e.g

    SELECT *

    FROM (

    SELECT TOP(40) ROW_NUMBER() OVER(ORDER BY SortKey desc)

    As Row, JobID, JobTitle, Blah, Blah

    FROM SITE_JOBS_INDEX_5000 as idx

    WHERE1=1 AND

    idx.nJobPK IN(

    SELECTDISTINCT(JobFK)

    FROMSITE_JOB_CATEGORY_MATRIX_5000

    WHERE1=1 AND

    IndustrySector = 'aerospace-and-aviation-jobs'

    )

    ) as JOBS

    WHERE Row between 30 AND 40;

    As I have 200+ sites plus most sites have a changable number of jobs it would take some sort of nightly job that would have to drop the existing plans and then loop through each site counting the number of live jobs and then use that to create X number of plans that sufficently covered the paging (TOP Clause).

    However it might just be easier to set FORCED on for the whole DB and see if I have any detrimental affects from it.

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply