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
http://www.sql-server-performance.com/qdpma/inst_4_profiler.asp

joe, 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. I was referring to the Control Panel->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.

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: DateSourceMessage 2006-02-21 10:34:58.27 serverCopyright (C) 1988-2000 Microsoft Corporation. 2006-02-21 10:34:58.27 serverMicrosoft SQL Server 2000 – 8.00.194 (Intel X86) … 2006-02-21 10:34:58.29 serverSQL Server is starting at priority class ‘high'(4 CPUs detected). 2006-02-21 10:34:58.29 serverLogging SQL Server messages in file ‘C

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

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

]]>