If you want to improve performance of reports or End of Day by 20-40% without changing query or introducing any new index or anything at all, let’s figure out the parameter which will help us.
The parameter is “enable literal autoparam“. Whether this parameter is enabled in our environment or not can be checked by executing below command:
sp_configure “enable literal autoparam”
go
Default value of this parameter is set as 0 meaning disabled, you may enable the same by running below command:
sp_configure “enable literal autoparam”, 1
go
This is a dynamic parameter and will be effective immediately, can be changed by someone who has System Administrative(Sa_role), mostly DBA.
This can also be set at session level(enable/disable) by executing below command:
set literal_autoparam [off | on]
go
When we enable this parameter at server level, it changes values to variables(where clause) for the queries. Let’s understand that with below example:
select emp_name from employee where id = 10012
go
select emp_name from employee where id = 30011
go
When we run above queries, optimizer will understand this as two different queries, compile them separately and create as well as store their plan differently whereas both the queries are almost identical with only where clause different. When we enable literal autoparam, the query that gets stored in plan cache will be as below:
select emp_name from employee where id = @@@V0_INT
go
Here @@@V0_INT is an internally generated name(depends on datatype) by the system for the parameter that represents the literal values 10012 and 30011.
All instances of literal values(where clause) in the query text are replaced by internally generated parameters and they are stored in the procedure cache, query plans and hence will be used when identical queries are executed, it could be reports/EOD.
From Sybase ASE documentation, below are attribute of this parameter:
- Reduces compilation time on the second—and subsequent—executions of the query, regardless of the literal values in the query.
- Reduces the amount of SQL text storage space, including memory usage in the statement cache and the number of rows in sysqueryplans for abstract plans and query metrics.
- Reduces the amount of procedure cache used to store query plans.
- Occurs automatically within Adaptive Server, when enabled: you need not change the applications that submit the queries to Adaptive Server.
This is effective in most of the cases however it has some bottlenecks when it comes to function-based index and when queries are used with same argument however different data-types.
Please leave your comment if you liked this post or have any feedback.