If you are having application performance issues and how cab we figure out it is related to disk / SAN issues? If write cache is enabled on operating system / disk for DAS or on SAN, writes from a database should be very fast and with a consistent response.
The best metric in perf mon is to measure the avg disk sec / write i.e the amount of time it took for a write to be completed by the disk or SAN. Generally a write should be with in 1ms - 5ms [microsoft recommendation] especially for transaction log.
If the write response is jumping all over the place and is not consistent over time, its time to look into SAN or disk performance.
Sunday, September 11, 2011
Sunday, August 28, 2011
SQL Server - Forced paramaterization
Sql server has a database option called parameterization. If the option is set to forced, sql server automatically parametrizes the queries and saves the query plan, so that it can be re-used for similar kind of queries. This is very helpful especially for ad-hoc kind of work load.
At my work, we have this option on as our work load is primarily ad hoc and this has helped in keeping down our cpu utilization. Recently our database cpu usage started hitting 70-80%. During that time, the queries with IN clause seem to be taking the longest time to execute.The query plan looked fine. On further analysis setting the time statistics on, it was the compilation that was taking nearly 3-4 seconds.
When the same query was executed against database with simple parameterization, the compile time was between 100ms to 200ms.
The query had more then 1500+ elements in the IN clause. The process of parameterization these 1000+ elements was taking nearly adding 3-4 seconds to the compile time.
There are couple of options to tackle this.
1. create a plan guide for this specific kind of query and override the forced parameterization to simple.
2. Rewrite the query so as to not to use the IN clause. Move the IN clause elements into a table and convert the IN clause to a join.
First option was not feasible as its the elements in the IN clause was completely user controlled and the plan guide had to match the query exactly. So if the elements in the IN clause change, the plan guide was not useful.
After rewriting the query to a join and deploying the fix, brought down the cpu usage to 30-40%.
So if your cpu usage is high on the database server with forced parameterization option on, look for queries with IN clause.
At my work, we have this option on as our work load is primarily ad hoc and this has helped in keeping down our cpu utilization. Recently our database cpu usage started hitting 70-80%. During that time, the queries with IN clause seem to be taking the longest time to execute.The query plan looked fine. On further analysis setting the time statistics on, it was the compilation that was taking nearly 3-4 seconds.
When the same query was executed against database with simple parameterization, the compile time was between 100ms to 200ms.
The query had more then 1500+ elements in the IN clause. The process of parameterization these 1000+ elements was taking nearly adding 3-4 seconds to the compile time.
There are couple of options to tackle this.
1. create a plan guide for this specific kind of query and override the forced parameterization to simple.
2. Rewrite the query so as to not to use the IN clause. Move the IN clause elements into a table and convert the IN clause to a join.
First option was not feasible as its the elements in the IN clause was completely user controlled and the plan guide had to match the query exactly. So if the elements in the IN clause change, the plan guide was not useful.
After rewriting the query to a join and deploying the fix, brought down the cpu usage to 30-40%.
So if your cpu usage is high on the database server with forced parameterization option on, look for queries with IN clause.
Subscribe to:
Posts (Atom)