SQL 2K on Win 2k3 64Bit | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL 2K on Win 2k3 64Bit

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
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
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.
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
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 />
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
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.
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.
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.
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
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

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 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 1433.<br />2006-02-21 10:35:09.24 serverSQL server listening on 1433.<br />2006-02-21 10:35:09.26 serverSQL server listening on 1433.<br />2006-02-21 10:35:09.26 serverSQL server listening on 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 />
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
joe, I can’t save the execution plan to a file? any ideas? I am going to look into apply SP4 to this server.
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
Check out this thread for the missing perfmon counters http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12363