Contents
Why is Management Studio Query slow in SQL Server?
Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. Another interesting topic to understand is Parameter Sniffing as outlined in Slow in the Application, Fast in SSMS?
Why does SSMS run faster than SQL Server?
By default the queries executed in SSMS had ARITHABORT ON and the queries that come from application have ARITHABORT OFF. This is what caused the difference in execution time. You can check the SSMS SET options by going to Tools > Options > Query Execution > SQL Server > Advanced.
How long does it take to execute a SSMS query?
The one with ARITHABORT ON executes in 1 second and the one with ARITHABORT OFF executes in 10 seconds. By default the queries executed in SSMS had ARITHABORT ON and the queries that come from application have ARITHABORT OFF.
How long does it take to run a SQL Server stored procedure?
Our users complained about slowness in a few areas of our application. We ran Profiler and found that a few SQL Server stored procedures were taking more than 6-10 seconds. I manually ran those same stored procedures in SQL Server Management Studio (SSMS) and they executed in less than second.
How to improve query speed in SQL Server?
Instead I converted the view into a User-Table-Function where the parameter was the value needed on the WHERE clause. In fact this WHERE clause was nested 3 queries deep and it still worked and it was back to the < 10ms speed. Eventually I changed the parameter to be a TYPE that is a table of UtilAcctIds (int).
Why is Sow performance slow in SQL Server?
In my case in DB table column type was defined as VarChar and in parameterized query parameter type was defined as NVarChar, this introduced CONVERT_IMPLICIT in the actual execution plan to match data type before comparing and that was culprit for sow performance, 2 sec vs 11 sec.
How to run a fast SQL Server query?
WHERE C.CreateDtTm >= (SELECT MAX (StartTime) FROM #Times) AND C.CreateDtTm < (SELECT MAX (EndTime) FROM #Times) This performed instantly as opposed to several minutes for the original code (obviously your results may vary) .