Re: Extreme performance issues (SQL Server 2000/ADO.NET/C#)

From: Frans Bouma [C# MVP] (perseus.usenetNOSPAM_at_xs4all.nl)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 04:09:26 -0700

Jon Rista wrote:
> I'm using ADO.NET in a windows service application to perform a process on
> SQL Server 2000. This process runs very quickly if run through Query
> Analyser or Enterprise Manager, but takes an excessively long time when run
> through my application. To be more precise, executing stored procedures and
> views through Query Analyser take between 10 and 20 seconds to complete. The
> same exact stored procedures and views, run in the same exact order, through
> my program, take anywhere from 30 minutes to 2 hours to complete, and the
> system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical
> ram) is pegged at 25% cpu usage (the query uses 100% of a single cpu's worth
> of processing power). I am at a complete loss as to why such a vast
> difference in execution time would occurr, but here are some details.
>
> The windows service executes on a workstation.
> SQL Server 2000 executes on a server different from the workstation through
> a 100mbps ethernet network.
> Query Analyser/Enterprise Manager run on the same workstation as the windows
> service.
(snip)

        In theory, a proc executed from ADO.NET should perform the same as when it
is called from QA. The problem is: ADO.NET calls a proc differently (uses
exec sp_executesql. Check with SqlServer profiler). This might be a
difference.

        Also, QA keeps the connection open. While this might not be a difference per
se, but it can be that your application opens/closes a connection each time.

        Also check if you can put extra options in the connection string when you
look at the QA connection options.

        QA uses OleDb, however what you're doing is not provider bound, as all
actions are executed on the server...

        Have you performed any profiling on teh server? (use the performance
counters added by sqlserver for detailed analysis)

                Frans. 

-- 
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP

Relevant Pages

  • Re: Windows Services GUI
    ... Open a form from a Windows Service ... SQL Server has a number of Windows Services that run, ... have multiple custom commands defined. ... Remoting Server, in which case you call a method to have it perform some ...
    (microsoft.public.dotnet.languages.vb)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Tracing user & permissions (triggers and stored p)
    ... staging tables + sql agent job ... >> Please specify the version of SQL Server you are using. ... >> xp_cmdshell is run (proc vs. trigger executes proc). ... >> from xp_cmdshell executes under the security context of the SQL Server ...
    (microsoft.public.sqlserver.security)
  • DTS Package throws error on Client Machines only
    ... I have a package that executes fine from the server where SQL Server in installed. ... Dim strPkgName As String ... at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) ...
    (microsoft.public.sqlserver.dts)
  • COM object with CLSID {. . . } is either not valid or not register
    ... I have a package that executes fine from the server where SQL Server in installed. ... Dim strPkgName As String ... at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) ...
    (microsoft.public.sqlserver.dts)