SQL Server Performance

SQL 2K on Win 2k3 64Bit

Discussion in 'Performance Tuning for Hardware Configurations' started by KPMoore, Feb 21, 2006.

  1. KPMoore New Member

    Hi

    I am having a problem with a client's server that is running our application on their Win 2003 64 bit server with SQL Server 2000. Process execution for sps can range from <1 second to over 5 minutes. Normally the sp should execute in the <1 second time and I can't figure out what the problem is.

    Can someone help with how I can debug this situation?

    BTW, this is our only client running 64bit Win 2k3 and they are the only one with problems.

    Kevin
  2. joechang New Member

    are you saying a single stored proc can take anywhere from 1sec to 5min, or different sprocs can span this range
    is there any way you can reproduce this from a single client connection?
    ie, if so, run profiler, get the Performance->Show Plan Statistics event
    add the Binary Data and Integer Data columns
    then see if there is a difference in execution plans
  3. KPMoore New Member

    I apologize if I wasn't clearer. The sp in question normally, before today, runs in <1 second time. Currently it takes anywhere from 5 - 6 minutes. At one point today it ran <1 second but now it is back up to 5 - 6 minutes. It just seems to be really slow.

    I also don't have the SQL Server counters in the Performance application.

  4. joechang New Member

    I am talking about Profiler
    it is in All Programs -> Microsoft SQL Server -> Profiler
    you need to learn how to use this and the QA execution plan
    or you will forever be at the mercy of anomalies like this
    see url for more direction, but use the above events
    http://www.sql-server-performance.com/qdpma/inst_4_profiler.asp
  5. KPMoore New Member

    joe,<br /><br />thanks for the link but I have used SQL Profiler before extensively and viewed the execution plan and nothing seems out of the ordinary it just is taking a really, really long time to execute.<br /><br />I was referring to the Control Panel-&gt;Administrative Tools-&gt<img src='/community/emoticons/emotion-4.gif' alt=';P' />erformance application that I don't have any reference to SQL Server counters.<br /><br />
  6. joechang New Member

    ok, but i did not ask for the SQL Server performance counters on this.,
    i said to chase down (using Profiler) the actual execution plan for fast & slow, with estimated and actual row counts
  7. KPMoore New Member

    joe,

    I am working on providing the profile info you requested.

    as a side note I did some searching on this site and people suggest monitor sql server performance using perfmon but I can't because I don't have those counters.
  8. joechang New Member

    yes i do recommend run Performance Monitor on a routine basis, and you do want to figure out why the SQL counters are missing.

    but that would be useful in solving other types of problems
    for the problem you described, this is best solved from the actual execution plan, with both estimated and actual row counts,
    either from Query Analyzer or Profiler.

    if any one wants to take bets, i am saying this problem as described will yield no meaningful clues (as to resolution) in Perfmon mon, Perfmon should indicate that you have problems, but you already know this.
  9. KPMoore New Member

    so I have the data in both query analyzer and profiler but I can't seem to save the output to a text file so that I can copy it here.
  10. joechang New Member

    if you do this from Query Analyzer, save the results to Excel, or else they will be difficult to read,
    if profiler, save as a trace file
  11. KPMoore New Member

    here is the output from QA

    Application Profile Statistics,,
    Timer resolution (milliseconds),0,0
    Number of INSERT, UPDATE, DELETE statements,26,26
    Rows effected by INSERT, UPDATE, DELETE statements,301,301
    Number of SELECT statements,0,0
    Rows effected by SELECT statements,0,0
    Number of user transactions,82,82
    Average fetch time,0,0
    Cumulative fetch time,0,0
    Number of fetches,0,0
    Number of open statement handles,0,0
    Max number of opened statement handles,0,0
    Cumulative number of statement handles,0,0
    ,,
    Network Statistics,,
    Number of server roundtrips,6,6
    Number of TDS packets sent,6,6
    Number of TDS packets received,378,378
    Number of bytes sent,300,300
    Number of bytes received,1.00274e+006,1.00274e+006
    ,,
    Time Statistics,,
    Cumulative client processing time,3,3
    Cumulative wait time on server replies,1.20192e+009,1.20192e+009
  12. KPMoore New Member

    here is the startup log also:<br /><br />DateSourceMessage<br />2006-02-21 10:34:58.27 serverCopyright (C) 1988-2000 Microsoft Corporation.<br />2006-02-21 10:34:58.27 serverMicrosoft SQL Server 2000 - 8.00.194 (Intel X86) ...<br />2006-02-21 10:34:58.29 serverSQL Server is starting at priority class 'high'(4 CPUs detected).<br />2006-02-21 10:34:58.29 serverLogging SQL Server messages in file 'C:program Files (x86)Microsoft SQL Serve<br />2006-02-21 10:34:58.29 serverServer Process ID is 4672.<br />2006-02-21 10:34:58.29 serverAll rights reserved.<br />2006-02-21 10:34:59.34 serverWorking Set size set to 1687552 kilobytes.<br />2006-02-21 10:34:59.55 serverSQL Server configured for fiber mode processing.<br />2006-02-21 10:34:59.57 serverProcessor affinity turned ON, processor mask 0x3<br />2006-02-21 10:34:59.63 serverUsing dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.<br />2006-02-21 10:34:59.69 serverAttempting to initialize Distributed Transaction Coordinator.<br />2006-02-21 10:35:07.57 spid4Starting up database 'master'.<br />2006-02-21 10:35:07.77 serverUsing 'SSNETLIB.DLL' version '8.0.311'.<br />2006-02-21 10:35:07.77 spid5Starting up database 'model'.<br />2006-02-21 10:35:07.79 spid9Starting up database 'pubs'.<br />2006-02-21 10:35:07.79 spid8Starting up database 'msdb'.<br />2006-02-21 10:35:07.79 spid4Server name is 'SERVER20'.<br />2006-02-21 10:35:07.80 spid12Starting up database 'mooreLumber'.<br />2006-02-21 10:35:07.80 spid11Starting up database 'LogPro'.<br />2006-02-21 10:35:07.80 spid10Starting up database 'Northwind'.<br />2006-02-21 10:35:07.85 spid13Starting up database 'LSRepository'.<br />2006-02-21 10:35:07.98 spid5Clearing tempdb database.<br />2006-02-21 10:35:08.01 spid12Recovery is checkpointing database 'mooreLumber' (<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />2006-02-21 10:35:09.24 serverSQL server listening on 10.10.20.22: 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 10.10.10.20: 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 10.10.20.20: 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 10.10.30.20: 1433.<br />2006-02-21 10:35:09.26 serverSQL server listening on 127.0.0.1: 1433.<br />2006-02-21 10:35:09.26 serverSQL server listening on 10.10.10.22: 1433.<br />2006-02-21 10:35:09.55 serverSQL Server is ready for client connections<br />2006-02-21 10:35:09.55 serverSQL server listening on TCP, Shared Memory.<br />2006-02-21 10:35:15.62 spid5Starting up database 'tempdb'.<br />2006-02-21 10:35:15.68 spid5Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more second<br />2006-02-21 10:35:15.76 spid4Recovery complete.<br />2006-02-21 10:35:44.62 spid51Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure '<br />2006-02-21 10:36:14.37 spid51Configuration option 'show advanced options' changed from 1 to 1. Run the RECON<br />2006-02-21 10:36:14.37 spid51Error: 15457, Severity: 0, State: 1<br />2006-02-21 10:36:15.60 spid51Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure <br />2006-02-21 10:52:34.38 spid59Process ID 58 killed by hostname REMOTE1, host process ID 3644.<br />
  13. joechang New Member

    you can't be serious?
    some one installed SQL Server 2000 without applying any service packs or hotfixes?
    wasn't there are a warning, something about a minimum service for the 64-bit OS

    somebody look this up for me,

    i don't even remember the performance characteristics for build 194,
    get to atleast 818,
    if SP4 is required, also get the later hotfix, build 2140?

    if you have an open MS PSS case, bug them for the latest pre-SP4 build (1029?) and post SP4 build (2170's?)

    anyways, i said to get the execution plan
  14. KPMoore New Member

    joe,

    I can't save the execution plan to a file? any ideas?

    I am going to look into apply SP4 to this server.
  15. joechang New Member

    if you are using query analyzer from a client with MS Office
    and use SET STATISTICS PROFILE ON

    the last result is the plan, put the mouse in the results, ctrl-A to select all, ctrl-C to copy
    put the mouse in an open excel spreadsheet, ctrl-v to paste,
    i do this all the time
  16. dtipton New Member

Share This Page