SQL Optimizer Parameterization


Add a note herePlan Recompilation

Add a note hereOnce the Query Optimizer has come up with a plan, which may have taken a considerable amount of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does this by caching the plan it just created, and taking steps to ensure that the plan is reused as widely as possible. It does this by using parameterization options.

Add a note here Parameterization

Parameterization is a process whereby SQL Server will take the T-SQL you entered and look for ways to replace values that may be variables with a token, so that if a similar query is processed, SQL Server can identify it as being the same underlying query, apart from some string, or integer values, and make use of the already cached plan. For example, the following is a basic T-SQL query to return data from the Adventure Works 2008 database:
Add a note here
   select *
   from person.person
   where lastname = 'fluffy'
Add a note hereThe parameterization of this query would result in the string ‘fluffy’ being replaced with a parameter such that if another user executes the following query, the same plan would be used, saving on compilation time:
Add a note here
   select *
   from person.person
   where lastname = 'jonathan'
Add a note hereNote that this is just an example, and this particular query gets a trivial plan, so it isn't a candidate for parameterization.
Add a note hereThe SQL Server Books Online topic on "Forced Parameterization" contains a lot of very specific details about what can and cannot be converted to a parameter.
Add a note hereTo determine whether a query has been parameterized, you can search for it in the DMV sys.syscacheobjects (after first executing the query to ensure it is cached). If the SQL column of this DMV shows that the query has been parameterized, you will see that any literals from the query have been replaced by variables, and those variables are declared at the beginning of the batch.

Add a note hereParameterization is controlled by the SQL Server configuration options — simple or forced:

§  Add a note hereSimple Parameterization: The default operation of SQL Server is to use simple parameterization on all queries that are suitable candidates. Books Online provides numerous details about which queries are selected and how SQL Server performs parameterization. Using simple parameterization, SQL Server is able to parameterize only a relatively small set of the queries it receives.
§  Add a note hereForced Parameterization: For more control over database performance, you can specify that SQL Server use forced parameterization. The Forced parameterization option forces SQL Server to parameterize all literal values in any select, insert, update, or delete statement queries. There are some exceptions to this, and these are well documented in SQL Server Books Online. Forced parameterization is not appropriate in all environments and scenarios. It is recommended that you use it only for a very high volume of concurrent queries, and when you are seeing high CPU from a lot of compilation/recompilation. If you are not experiencing a lot of compilation/recompilation, then forced parameterization is probably not appropriate. If you use forced in the absence of these symptoms, you may end up with a degradation in performance and/or throughput, as SQL Server takes more time to parameterize a lot of queries that are not then reused.
Add a note hereForced parameterization can also be more finely controlled through the use of plan guides. You will learn about plan guides in more detail later in this chapter.

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the following
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a
Read more

Cross Domain Webix Post

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.  Error: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:7100' is therefore not allowed access. We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy. Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser i)                     We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*) ii)                    Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this. Thereafter using the following code the JSON data wa
Read more