Some cache corrupt on your SQL server, Run 2 commands to clean:
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS removes any non dirty (iow, clean) pages:
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.
DBCC FREEPROCCACHE does is empty the procedure cache.
Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. Use DBCC FREEPROCCACHE to clear the plan cache carefully. Freeing the plan cache causes, for example, a stored procedure to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance
Extra Note:
The ARITHABORT setting is one of the things the optimizer looks at when it is determining how to execute your query (more specifically, for plan matching). It is possible that the plan in cache has the same setting as SSMS, so it uses the cached plan, but with the opposite setting your C# code is forcing a recompile (or perhaps you are hitting a really BAD plan in the cache), which can certainly hurt performance in a lot of cases.
SET ARITHABORT ON; SELECT ...
Reference: http://www.sommarskog.se/query-plan-mysteries.html
Clearing the cache
DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE did the trick! The execution plan was somehow corrupt or not updated.Reference: http://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio
