SQL Server Performance

very slow on large T-SQL batches

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Depa, Feb 14, 2008.

  1. Depa New Member

    Hello everyone - we are having a strange performance issue after upgrading from SQL 2000 32x Std to SQL 2005 64x Std SP2. Our ERP system is not making use of store procedures or any form of T-SQL batches, instead it synchronously sends a bunch of SQL commands. For example, to ship all lines on a sales order it sends a total of ~1000 SQL instructions and waits for each to finish before moving forward with the next. Poor design you would say, huh? Anyway we have to live with it for the time being b/c we can not modify source code.
    Before the upgrade this particular transaction and many similar ones in nature, were only taking 1-2 seconds. However after the upgrade it is now taking over 2-3 minutes!!! Nothing has changed in terms of network, personal computers or database. Only that we upgraded the OS to Windows 2003 64x Enterprise and loaded SQL 2005 64x Std w/ SP2. We have also deployed Windows 2003 32x server w/ SQL 2005 32x on another server to make sure this has nothing to do with hardware and tested ERP against a copy of database there with same poor performance result. Then we captured all SQL commands via profiler and encapsulated them to a dummy stored procedure and ran same statements, and then it took only 2 seconds...
    Any ideas would be appreciated, thanks.
  2. Luis Martin Moderator

    Welcome to the forum!.
    Did you run full update statistics after upgrade?
  3. Depa New Member

    Thank you for quick response, yes we did run after the upgrade the following:
    DBCC UPDATEUSAGE (database) , and
    sp_updatestats, and
    DBCC FREEPROCCACHE
    However this did not help. Following suggestions from other forums we had also set degree of parallelism to from 0 to 1.
    Our server hardware is as follows (if it helps):
    HP Proliant DL385
    AMD 2 single-core 2.4Ghz 250
    16Gb physical RAM
    System volume on RAID1 w/ enough space
    Data volume on RAID5 w/ enough space
    SQL Version:
    Microsoft SQL Server 2005 - 9.00.3054.00 (X64)
    Mar 23 2007 18:41:50
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
    We monitored potential memory issues and cache hit rate appears to be always at 100%
    CPU utilization is for the most part around 4%, peaking out at 95% on some queries. However honestly I don't see why same database on same server will become slow after SQL upgrade...
    Please let me know if there is any else piece of info will be helpfull. Thanks again.
    P.S. from the traces I can see that our ERP system uses many API cursors, and I even found an MS KB article (http://support.microsoft.com/kb/917905/en-us), however patch seems to only apply to SP1, whereas we are on SP2 SQL...
  4. Luis Martin Moderator

    Pick that query and using SMS run:
    Set statistics io on
    Your query
    Set statistics io off
    and post the statistics results.
  5. Depa New Member

    yes, i tried that in SMS, the batch contains >300 sql statements so result text is rather large... The batch ran almost instantly from SMS query window, however same thing consistently takes ~1 min from the ERP application... Note that I test both ERP and SMS batch from the same workstation to the same SQL server, so there should be no difference in terms of networking or OS layer. I'm on a gigabit LAN. Are you looking for something specific in the IO output? Perhaps I could post a fragment which interests you most rather than entire thing? Thanks for all your help.
  6. Luis Martin Moderator

    I don't need the results from the query.
    In one windows you have results. In the other window you have the statistics results. Contain tables, reads, reads ahead, etc.

    That is what I like to see.
    On more thing, before running the query as I wrote, run
    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS
    After that we will look the difference between running from SMS and from application.
  7. Depa New Member

    ok, I'l' gladly try that. One thing, I had to switch from grid to text output, b/c with the grid the output was so large that it reported error that only the first 100 ros will be shown. When I run text-only I end-up with one tab "Result" and there the output is quite large. MS Word blows it on 413 pages. I can post it, but I'm affraid it will beat out brains off this entire page.. Here's some samples I see from the output:
    Table 'OR030100'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0....
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'fulltext_index_map_1627152842'. Scan count 0, logical reads 32, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SC010100'. Scan count 0, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'SC01ZZ00'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'PTSyncData01'. Scan count 0, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  8. Luis Martin Moderator

    Nothing wrong what that. More, is optimized.
    So the problem is running from the application. In this case I suggest to talk with developers. Show him the difference between executing using SMS and from appl.


  9. Depa New Member

    Ok, it took 11 seconds to execute from SMS query window. The operation in ERP system (with equivalent set of SQL commands) took 1 minute and 17 seconds... In our operating environment this is quite disturbing. As you recommended I fired both dbccs before each test. I tend to think this has something to do with negotiation between ERP and new database engine, perhaps something has changed in exchange protocol, I don't know... The ERP uses an ODBC datasource to connect and SQL authentication using a MDAC SQL Server driver SQLSRV32.DLL ver. 2000.85.1117.00. I tried to change datasource to use SQL Native Client SQLNCLI.DLL 2005.90.3042.00 and it works too, but offers no improvement... Connection works over TCP/IP port 1433. We don't have unfortunately a whole lot of other Windows apps which connect to database and so I could test with, other than ASP.Net apps, which run noticeably faster after the upgrade... go figure.[:S]

Share This Page