We have a newly build server where one of the queries is taking 110ms and while in rest of the servers it is taking 6 ms. The query plan and the dataset are identical. The hardware configuration is also identical.

If we turn this query into a stored procedure then the performance is 6ms. If we pad this query with comments and bulk up the query then the elapsed time balloons to 200ms or more. With a padded comment the performance is still good in the rest of the good servers.

There is a minor difference in the version of SQL Server. On the good instances it is CU7 and where as on the not so good instance it is CU9.

We looked into different DMVs to look for any anomalies and couldn’t find one. 

Wondering if there is something that we can do to investigate where the bottleneck is. Needless to say we are fairly new to SQL Server.

​We have a newly build server where one of the queries is taking 110ms and while in rest of the servers it is taking 6 ms. The query plan and the dataset are identical. The hardware configuration is also identical. If we turn this query into a stored procedure then the performance is 6ms. If we pad this query with comments and bulk up the query then the elapsed time balloons to 200ms or more. With a padded comment the performance is still good in the rest of the good servers. There is a minor difference in the version of SQL Server. On the good instances it is CU7 and where as on the not so good instance it is CU9. We looked into different DMVs to look for any anomalies and couldn’t find one.  Wondering if there is something that we can do to investigate where the bottleneck is. Needless to say we are fairly new to SQL Server.    Read More

​