5

I have a simple SQL query that when run from C# takes over 30 seconds then times-out every time, whereas when run on SQL Server Management Studio successfully completes instantly. In the latter case, a query execution plan reveals nothing troubling, and the execution time is spread nicely through a few simple operations.

I've run 'EXEC sp_who2' while the query is running from C#, and it is listed as taking 29,000 milliseconds of CPU time, and is not blocked by anything.

I have no idea how to begin solving this. Does anyone have some insight?

The query is:

SELECT
    c.lngId,
    ...
FROM tblCase c
    INNER JOIN tblCaseStatus s ON s.lngId = c.lngId
    INNER JOIN tblCaseStatusType t ON t.lngId = s.lngId
    INNER JOIN [Another Database]..tblCompany cm ON cm.lngId = cs.lngCompanyId
WHERE t.lngId = 25
    AND c.IsDeleted = 0
    AND s.lngStatus = 1
shA.t
  • 16,580
  • 5
  • 54
  • 111
Paul
  • 16,285
  • 13
  • 41
  • 52
  • 1
    Likely to be either parameter sniffing or a SET option that's different. Is it a stored procedure? To trouble shoot use SQL Profiler or `sys.dm_exec_query_plan` to get the execution plan of the C# one and compare with the Management Studio one. – Martin Smith Jun 18 '10 at 14:55
  • 1
    Is this a single query or a stored procedure? Use the SQL Profiler tool to run a detailed trace while the query is being executed from the application to ensure the same database commands are being executed and to see exactly how long to query takes when executed in this manner. – DCNYAM Jun 18 '10 at 14:57
  • It isn't a stored procedure at the moment, so there should be no compilation problems. Do you know of any likely SET options that might be different, that could cause this? I think the options are defaults on both sides. – Paul Jun 18 '10 at 14:58
  • 1
    @Paul - Do you use indexed views or persisted computed columns? If so `SET ARITHABORT` can make a difference. You're best off capturing the execution plan though. – Martin Smith Jun 18 '10 at 15:00
  • Do you select a lot of data that has to be transfered to the C# program? Maybe lobs or something? Is the C# code running on the server or on some other computer (over a network)? – Janick Bernet Jun 18 '10 at 15:01
  • @Martin Smith -- No, neither. – Paul Jun 18 '10 at 15:01
  • @inflagranti -- No, just 11 rows with 14 columns. All varchars, ints or dates. – Paul Jun 18 '10 at 15:03
  • 1
    possible duplicate of [SQL Server ARITHABORT](http://stackoverflow.com/questions/1416242/sql-server-arithabort) – NotMe Jun 18 '10 at 15:08
  • @Chris It is instant in SQL Server Management Studio when ARITHABORT is ON and OFF – Paul Jun 18 '10 at 15:16
  • @Paul - I just noticed what you said about CPU time. Can you post the query? – Martin Smith Jun 18 '10 at 15:31
  • @Martin -- done. The fields are all just table columns, except one simple scalar-valued function. – Paul Jun 18 '10 at 15:42
  • 1
    @Paul - scalar-valued functions can definitely cause performance problems. I'm not sure how to go about identifying it as the definite culprit though. Did you manage to get the execution plan for the C# one? This might be either of interest or completely irrelevant! http://blogs.msdn.com/b/mikecha/archive/2009/05/19/sql-high-cpu-and-tempdb-growth-by-scalar-udf.aspx – Martin Smith Jun 18 '10 at 15:50
  • What happens when you take out the join to the other database? I wonder if there is a security component here. – NotMe Jun 18 '10 at 16:49
  • Sorry that I haven't answered for a while. The C# version has become instant now the same as the SQL, with no changes being made to the query or the connection. It's frustrating that it stops me investigating what causesed it. If it happens again, I'll try the ideas given here. We have lots of cross-database queries, and the table sizes are fairly small (3,000 rows), so I wouldn't expect the scalar-valued function to cause it to slow down from instant to over 30 seconds. – Paul Jun 21 '10 at 07:35

1 Answers1

5

To start with, extract the query plan of the query when is run from C#:

select p.query_plan, *
from sys.dm_exec_requests r
cross apply sys.dm_exec_query_plan(r.plan_handle) p
where r.session_id = <spid of C# connection>

Then compare it with the plan executed under the SSMS session (simply click the Show actual plan in toolbar).

And, as a general rule, always try to apply a methodical approach rather than guess. Wait and Queues is a very good, proven, performance troubleshooting methodology for SQL Server.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569