Performance difference between query executed through ASP.NET and SSMS

I have also posted this in microsoft.public.sqlserver.programming.

I have a query which, depending on where I run it from, will either take 10 milliseconds or 10 seconds.

The query works perfectly when run in SQL Server Management Studio... in my database of around 70,000 items it returns the results in around 10ms. It uses all my indexes and indexed views correctly.

However when I run the identical query from my ASP.NET application, it takes around 10 seconds... 1000 times longer.

Looking at it in Sql Server Profiler I can't see any difference in the query, except from ASP.NET it needs 62531 reads and from SSMS it needs only 318 reads. If I copy the slow running ASP.NET query from the profiler into SSMS, then it runs quick again. The results returned are the same.

I have provided more details of the query below, but I guess my real question is: What is the best way to debug this? I'm not an expert with SQL Server, so any pointers on where I should start looking to find the difference in how the query is being executed would be a great help.

The query is of the form:

WITH RowPost AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY DateCreated DESC) AS Row,
ItemId,
Title,
....
FROM
Items_View WITH(NOEXPAND)
WHERE ItemX >= @.minX AND ItemX <= @.maxX AND ItemY >= @.minY AND ItemY <= @.maxY
)
SELECT
*,
(SELECT Count(*) FROM RowPost) AS [Count]
FROM RowPost
WHERE Row >= @.minRow AND Row < @.maxRow

Where Items_View is an indexed view, and WITH(NOEXPAND) is being used to force it to use the indexed view (this is optimal). The line beginning "SELECT Count(*)" is to get the total number of results (without having to run the inner query a second time).

This is running against SQL Server Developer Edition.

My guess is its using a different query plan. Query plan reuse is influenced by your connection settings eg SET ANSI NULLS, QUOTED IDENTIFIER etc... and these are most likely different between your aspnet conn and ssms.

Check the setopts values in sys.syscacheobjects and for your queries and see if they differ. You can translate the values by using the following code:

Code Snippet

DECLARE @.sets int
SELECT @.sets = 4347

IF @.sets & 1 = 1 PRINT 'ANSI_PADDING'
IF @.sets & 4 = 4 PRINT 'FORCEPLAN'
IF @.sets & 8 = 8 PRINT 'CONCAT_NULL_YIELDS_NULL'
IF @.sets & 16 = 16 PRINT 'ANSI_WARNINGS'
IF @.sets & 32 = 32 PRINT 'ANSI_NULLS'
IF @.sets & 64 = 64 PRINT 'QUOTED_IDENTIFIER'
IF @.sets & 128 = 128 PRINT 'ANSI_NULL_DFLT_ON'
IF @.sets & 256 = 256 PRINT 'ANSI_NULL_DFLT_OFF'
IF @.sets & 4096 = 4096 PRINT 'ARITHABORT'

HTH!|||

With the information from everyone (both in the MS newsgroup and in the MSDN forums - thanks!) I started looking into this by checking the 'setops' in the sys.syscacheobjects view for the query when executed by ASP.NET and SSMS.

While doing this, I also ran "DBCC DROPCLEANBUFFERS" and "DBCC FREEPROCCACHE".

The first thing I noticed was, as Erland predicted (although I hadn't seen his post at this point), the main difference was ARITHABORT was on for SSMS and off for ASP.NET.

This worried me - if the query was only fast because it was aborting early then I'd have a big problem.

I started to play with this setting to see what the difference was... but to my surprise not only did it not make a difference but my ASP.NET queries were suddenly fast again.

I now can't reproduce the ASP.NET queries being slow. Perhaps clearing the data cache and execution plans forced SQL Server to build a new execution plan and this time it chose a fast one?

Does this sound plausable? Is it a problem people have experienced before, and is there a way to ensure fast execution plans are always used?

Cheers,
James.

|||

For anyone interested, this problem re-occurred after a server restart, but this time (thanks to all the replies here and on the newsgroup) I had the knowlege to debug it.

After turning on Showplan XML in SQL Server Profiler I saw that the fast version of the query was using an index on the indexed view that I had created specifically for the query. The slower query had decided to use a different index that I had created for a slightly different query and wasn't sorted as effectively, which meant it was having to do a manual sort afterwards.

I added a hint to use the correct index and now it is running full speed again.

I believe this occured because sometimes the first query after the server restart or cache clear would be one where the sorting was trivial due to the small number of results returned. It would therefore create a sub-optimal execution plan for the majority of queries where there are many more results returned.

Thanks,
James.

|||Excellent stuff, James. Glad you got it sorted.