473,739 Members | 4,930 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

CPU usage comparison for MS SQL Server 2000

kim
Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded
from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for
Computer B, I installed SQL Server 2000 from scratch. These two have a
same RAM(512MB) and CPU speed...so the setup for hardware is almost
identical...

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL
Server...

The database for SQL Server B is a copy from SQL Server A.... I
restored the database on SQL Server B from the backup dump of Server
A...

We have a store procedure called usp_GetMemo(ID Interger) ... we feed
ID number to this store procedure and return its return Description...
this table has ID column and it's Non-Cluster index, allow the
duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this
store procedure(usp_G etMemo(ID)) continuously, the CPU usage of
Computer A is 3 times bigger than computer B....

I did run the command "DBCC DBReindex" , "Update Statistics" for this
table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000
while Computer B installed from scratch... would that make difference
somehow?? Before the upgrading, the CPU usage of computer A is the
same as Computer B...

Any help will be really appreciated,
Thanks alot,
Kim,
Jul 20 '05 #1
7 4257
kc*@emia.com.au (kim) wrote in message news:<5c******* *************** ***@posting.goo gle.com>...
Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded
from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for
Computer B, I installed SQL Server 2000 from scratch. These two have a
same RAM(512MB) and CPU speed...so the setup for hardware is almost
identical...

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL
Server...

The database for SQL Server B is a copy from SQL Server A.... I
restored the database on SQL Server B from the backup dump of Server
A...

We have a store procedure called usp_GetMemo(ID Interger) ... we feed
ID number to this store procedure and return its return Description...
this table has ID column and it's Non-Cluster index, allow the
duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this
store procedure(usp_G etMemo(ID)) continuously, the CPU usage of
Computer A is 3 times bigger than computer B....

I did run the command "DBCC DBReindex" , "Update Statistics" for this
table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000
while Computer B installed from scratch... would that make difference
somehow?? Before the upgrading, the CPU usage of computer A is the
same as Computer B...

Any help will be really appreciated,
Thanks alot,
Kim,


Have you checked that the database compatibility level of the upgraded
database is set to 80, not 65? See sp_dbcmptlvl in Books Online.

Simon
Jul 20 '05 #2
In article <60************ **************@ posting.google. com>,
sq*@hayes.ch says...
kc*@emia.com.au (kim) wrote in message news:<5c******* *************** ***@posting.goo gle.com>...
Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded
from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for
Computer B, I installed SQL Server 2000 from scratch. These two have a
same RAM(512MB) and CPU speed...so the setup for hardware is almost
identical...

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL
Server...

The database for SQL Server B is a copy from SQL Server A.... I
restored the database on SQL Server B from the backup dump of Server
A...

We have a store procedure called usp_GetMemo(ID Interger) ... we feed
ID number to this store procedure and return its return Description...
this table has ID column and it's Non-Cluster index, allow the
duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this
store procedure(usp_G etMemo(ID)) continuously, the CPU usage of
Computer A is 3 times bigger than computer B....

I did run the command "DBCC DBReindex" , "Update Statistics" for this
table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000
while Computer B installed from scratch... would that make difference
somehow?? Before the upgrading, the CPU usage of computer A is the
same as Computer B...

Any help will be really appreciated,
Thanks alot,
Kim,


Have you checked that the database compatibility level of the upgraded
database is set to 80, not 65? See sp_dbcmptlvl in Books Online.


On top of that, never do an in-place upgrade, you leave sooo many things
behind when you do that.

There are many things to consider -

Hard drive arrays and cache - Are they the same?

Drive fragmentation? Did you defrag both servers?

Users, not just DB users, but total users on both systems - are they the
same?

Did you set the SQL memory at 400MB fixed or did you allow it to use as
much as it wants? Never allow it to do it on it's own, make sure that
you leave 128MB for the OS to use.

Did you run the maintenance plan on both systems?

--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #3
Kim,
I don't think the upgrade is the problem. I think the problem is the
drives in server A. If they are identical drives, configured in the same
format as server B, then try defragging the drive(s) on server A. They have
probably been in use for a while and are probably very fragmented. Make
sure you bring down the database before defragging. Also, recommend you use
a 3rd party defragger like Norton speed disk as the defragger that comes
with Windows doesn't do as good a job.
Hope this helps,
Best regards,
Chuck Conover
www.TechnicalVideos.net

"kim" <kc*@emia.com.a u> wrote in message
news:5c******** *************** **@posting.goog le.com...
Hi All,

I had two Window NT 4.0 Server, say A and B... We recently upgraded
from MS SQL Server 6.5 to MS SQL Server 2000 on Computer A, and for
Computer B, I installed SQL Server 2000 from scratch. These two have a
same RAM(512MB) and CPU speed...so the setup for hardware is almost
identical...

For server A, there are around 10 connection to this SQL Server...
For server B, there are also around 10 connection to this SQL
Server...

The database for SQL Server B is a copy from SQL Server A.... I
restored the database on SQL Server B from the backup dump of Server
A...

We have a store procedure called usp_GetMemo(ID Interger) ... we feed
ID number to this store procedure and return its return Description...
this table has ID column and it's Non-Cluster index, allow the
duplicate row... there are 1.7 million records for this table...

Here is problem... whenever i execute more than 100 times for this
store procedure(usp_G etMemo(ID)) continuously, the CPU usage of
Computer A is 3 times bigger than computer B....

I did run the command "DBCC DBReindex" , "Update Statistics" for this
table on computer A, but did not make any difference...

Keep in mind that Computer A has been upgraded to SQL server 2000
while Computer B installed from scratch... would that make difference
somehow?? Before the upgrading, the CPU usage of computer A is the
same as Computer B...

Any help will be really appreciated,
Thanks alot,
Kim,

Jul 20 '05 #4
Hi simon,

Thanks alot for your respond....
The database compatibility level for both server is 80...

Thanks again


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5
Hi,

Thanks a lot for your respond....

For the Hard drive arrays and cache,How can i check it.. i'm no expert
in hardware stub..

Yeah, for Server A has not been defrag for a while.. Yes, I should do
fragmentation on it....

Thanks a lot
Kim,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #6
about the defragmentation , which software is good to degrag Windows NT
4.0 server?

Thanks alot,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #7
In article <40************ ***********@new s.frii.net>, kc*@emia.com.au
says...
about the defragmentation , which software is good to degrag Windows NT
4.0 server?

Diskeeper Server version
--
--
sp*********@rro hio.com
(Remove 999 to reply to me)
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3372
by: Erik Miller | last post by:
Hi Guys, I find there is memory leak in our SQL 2000+SP3 databass. We have a client-server system, and the there are Jrun4.0 running JSP and someother Java application talks to the database. However, for only less than 40 users, the SQL 2000 memory usage will increase consistantly until it use all the memory it can get ,about 1.7G memory. Then eventually the SQL server process will hang in couple days.
1
4354
by: Jeff Roughgarden | last post by:
We are hosting a 140 GB database on SQL Server Version 7 and Windows 2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5 SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to the OS) and a 4 GB swap file. (The PeopleSoft CIS application will not permit us to upgrade to SQL 2K.) We recently upgraded the server from 4 to 8 cpus and the SAN disks from 10K to 15K drives. But we still have heavy SAN disk usage,...
2
8662
by: w2jin | last post by:
Hi All Some my SQL Server are experience high memory usage. 1. How can I detect which process which process cause the big memory usage and not released? 2. Which sql server components in this memory, and what are their usage distribution?
7
4996
by: Magnus ÷sterberg | last post by:
I am experiencing the following problem; I boot my Windows 2000 sp#4 machine. I check sqlservr.exe's memory usage in Task manager. It is ca 20 mb, everything is OK. Then I run this query; select postoffice, count(*) as counter from tblTable where postoffice is not null group by postoffice order by counter DESC
9
19383
by: Raziq Shekha | last post by:
Hello all, How can I tell how much memory SQL Server is using on a server. On Windows 2000, whenever I go to Task manager/processes/memory usage SQL Server seems to be showing 1,744,124K. On all of my servers with various size, usage of databases, all of them show SQL Server to be using about the same amount of memory. Can someone explain this to me? Shouldn't it use more for larger databases, heavy hitting databases? Also, I...
7
7172
by: Clement | last post by:
hi, i have an asp.net site and is using SQL Server 2k. i realize the aspnet_wp.exe memory usage keep growing and i will receive an error for the pages that call the sql connection. others page with no sql connection is fined. At the time when i encounter the error, i check the memory usage for aspnet_wp.exe = 60000kb sqlmangr.exe = 40000kb
22
31974
by: Paul | last post by:
Hi I am having a real issue with CPU usage by SQL Server, and it is not related to a poor query. I have a clients database that I am currently investigating some issues with. After I perform a standard task using the application, and the results have been returned to the application the cpu usage remains at 100%.
1
9986
by: Raziq Shekha | last post by:
Hello All, On all of my SQL servers (2000 with SP3), when I go to task manager and look at memory usage, sqlservr.exe is always at 1.7gigs. If I reduce the maximum to let's say 1 gig it will go down to 1 gig. But if it is even at 2gigs or 3 gigs it will be showing 1.7 gigs. Why? Is is ok to reduce the memory usage of sqlservr.exe? Raziq.
0
2435
by: alastairb | last post by:
Hi I have an SQL Server which is of the following spec HP ML350 G3 2 * 3Ghz Pentium Xeon 2Gb Ram 1 * Raid 1 set for OS and Logs 1 * Raid 5 set for Database files
0
8969
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9483
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9341
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9211
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4572
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3282
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2748
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.