There is not unusual situation that query or stored procedure run very slow from the application, but run fast from the SSMS (SQL Server Management Studio).
There are some optional reasons for this situation, I'll describe in this post.
Parameter Sniffing
Different Plans for Different Settings
Different Settings between SSMS and the application create different plans.
Different Default Setting between SSMS and the application is for ARITHABORT – in Applications using ADO .Net, ODBC or OLE DB is OFF, and in SSMS is ON. This can cause different plans. In many the ARITHABORT settings is the reason for performance problems in the application.
Check settings of sessions using:
SELECT * FROM sys.dm_exec_sessions
WHERE session_id in (68,61) --SSMS session VS APP session.
http://copypastenet.blogspot.com/2011/10/get-set-options-for-current-session.html
Blocking
Blocking between statements and/or transactions from/in the applications.
Permissions on the remote database
Permissions on the remote database can be different than the permissions of the user in SSMS, and this can cause to different statistics, query plans, etc.
For simulate the application execution, try to execute in SSMS with:
EXECUTE AS USER = 'AppUser'
Bad SQL Code
Bad SQL Code is always bad.....
Plans for Dynamic SQL
Unparameterised SQL is bad for the performance:
Not good:
SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value)
EXEC(@sql)
Or in C#:
cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();
Good:
EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value',
N'@value int', @value = @value
Or in C#:
cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value";
cmd.Parameters.Add("@value", SqlDbType.Int);
cmd.Parameters["@value"].Value = value;
Application cache
Similar to Parameter Sniffing.
Note: Beside of my experience, in this post I also based on one of the best articles that I ever read:
No comments:
Post a Comment