Tuesday, November 10, 2009

ad hoc queries and sql server and mysql

when ever a query is fired to a database, it is parsed and various plans are generated. The best or good enough plan is chosen by the engine and is used to execute the query.

Once a plan is generated, it is maintained in the plan cache by the sql server for future re use in case same query is executed where as mysql generates the plan every time the query is executed. The caveat in the sql server for ad hoc queries, is that the queries must match text to text, case sensitive for the query plan to be reused.

For eg:
query 1:
    SELECT col1 FROM table1 WHERE col2 = 100;
query 2:
    SELECT col1 FROM table1 WHERE col2 = 200;

The above 2 queries will generate 2 query plans and will be cached in the plan cache as 2 different plans even though the only difference is in the predicate value.

This leads to couple of interesting issue.

1. SQL server 2005 introduced TokenAndPermUserStore cache which affects the performance for ad hoc queries.

 http://support.microsoft.com/default.aspx/kb/927396

This issue seems to have been fixed in sql server 2005 sp3 and sql server 2008.

2. The size of plan cache grows streadily as sql server caches all the plans until it hits the memory pressure.

The sql server can be forced to use the same plan for similar type of queries by
1. By changing database setting from simple parameterization to forced parameterization. This will replace the literal value in the queries to parameter. So when a new query with different predicate is fired it will reuse the parameterized query plan. The plan cache will have the parametrized query plan and  the shell query plans for similar query holding a pointer to the parameterized query plan. I will elobrate on this in one of the follow up blog.
 additional inforamtion on forced parameterization.

   http://technet.microsoft.com/en-us/library/ms175037.aspx 

2. Using prepared statements in java.  This will help in creating plans for parameterized queries and will be reused by the sql server. The only drawback of using prepared statement is that it adds an additional network round trip but doesn't increase the plan cache for similar queries.

We have implemented the forced parameterization and it boosted the app performance by 30 % and reduced the TokenAndPermUserStore cache growth from rate of 2 to 2.5 MB per 10 minutes to 600 kb per 10 minutes.

No comments:

Post a Comment