SQL query timeout from application but works fast from SSMS

Recently faced very strange problem. Application which was working great during last two years just suddenly had timeout on one particular page. The page was always very fast and was showing just last 12 records from the table.

So I did what always was doing to check slow performing query, started SQL Profiler to trace the query. Query by itself is pretty complex and had a lot of parameters, Profiler is the best tool I think in that cases.

After I easily found long running query in Profiler I copy pasted it into SSMS and what a surprize query just run in 300ms. Meanwhile the query from UI (asp.net) was running almost 2 minutes.

To get session Id from from simple SQL query just run SELECT @@SPID, so this had to be your probably fast executing query. Session id for slow executing Application you can find in profiler, it is a column in UI.

As a result I got these two rows, 1st row is slow session, 2nd row is second session

As you can see the only difference is arithabort setting.
More information about it can be found here
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql

Strange thing I had no arithmetic expressions in my query which could lead to the problem.
So for an experiment I added this query to SSMS which was executing fast to see if it will slow down

It had expected effect, query just slowed to 2 mins from 200ms!
After playing with query a little bit I found that issue was in comparing date part of the query
Fast:
AND (@FromDate IS NULL OR o.Created >= @FromDate)
AND (@ToDate IS NULL OR o.Created < @ToDate)
Slow:
AND (@FromDate IS NULL OR o.Created >= @FromDate)
AND (@ToDate IS NULL OR o.Created <= @ToDate)
Not sure what has to do <= Datetime compare with arithmetic overflow but at least it helped.

As a solution I could
1. change <= to <
2. Add SET ARITHABORT OFF to the query
3. change setting for the table

I have chosen 3rd option, which should prevent from similar problems later.
Switching in UI the parameter to True fixed the slow running query immidietly.

IntroductionWhile planning and developing web sites we always should keep in mind that someone always will be paranoid with it. So being paranoid with security will keep us away from security issues we can meet after production. As more popular will be our web site, then more paranoid should be security. With internet technologies explosion and content enhancing, migrating to dynamic pages, payment systems integration web sites became more vulnerable [1]. Who is who?A threat is a potential problem and bad thing which can occur with your system and data.
Vulnerability is a weak area where threat can occur. It can be after bad design, weak data encryption or sensitive information storing on client side without encryption [4]. Vulnerable areasSo where we can potentially have a breach? Well-designed application will be built on layers like database, business, communication, user interface. So each layer as communication between layers is a potential security problem. Authentication impleme…