An unfixed issue will always haunt you on the day of go-live. Today was one of that day when customer started complaining about serious performance issues post go-live. Eventually, a small configuration change saved the day and in the end customer was happy!!!!
Here is what customer complained:
- Application is very slow and there is lot of clocking.
- Application user has to wait for as long as a minute after pressing a button to fetch data.
- CPU utilization was constantly high, never came down below 90% post deployment
Our observations:
- Found more than 1000 queries taking consuming high CPU time (average worker time)
- There were more than 500 queries with average execution time of more than 10 seconds
- Maximum Degree of parallelism was “0”
- Cost Threshold of Parallelism was “5”
- SQL Server Box has single NUMA node with 8 logical processors
Analysis and Correction Steps
- Max Degree of Parallelism: As per guidelines for max degree of parallelism, SQL Server with single NUMA node with 8 logical processors should have MAX DOP setting of 8 or less. Default setting of zero should always be changed based on the CPU cores available to the SQL Server.
- Cost Threshold of Parallelism: This setting was also set to default, as per recommendations by experts this setting should be set between 25-50. But one has to always test and find the a number that is not too high or not too low. A low value say 5 (default), means lot of queries whose cost is greater than 5 will be chosen to execute in parallel. There are queries that does not require parallel execution, but they are forced to go parallel and hence execution time shoots up. The opposite is true when the value is high. A candidate query that should execute in parallel will execute as a single thread with high execution time. We recommended the pre-tested value of 30 measured for an equivalent workload.
- High CPU: CPU utilization was high as almost all the queries went for an implicit conversion from NVARCHAR to VARCHAR. Then happens when a parameterized query declares default NVARCHAR(4000) for a string parameter from application. The underlying column in the database is of type VARCHAR, hence query goes thru implicit conversion. This behaviour is called parameter sniffing, where SQL Server complies the execution plan sniffing the parameters from the input query and use that plan whenever the query is executed. Apparently, implicit conversion makes the existing indexes unusable and query goes for a Clustered Index Scan or a full table scan (for Heap). This also shoots up the execution time of the query. This fix to this problem is to let SQL Server know the datatype of parameters. In our case, we know that there is no NVARCHAR column in the database so a small change in jdbc url solved the problem (sendStringParametersAsUnicode=false).In the end, it was a day accomplished (with happy customer) and go-live saved 🙂
SQLpathy 