CPU Usage Comparison For MS SQL Server 2000
Jul 20, 2005
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_GetMemo(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,
View 7 Replies
ADVERTISEMENT
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Aug 29, 2006
Dear all,
I am developing a proposal to my company, which is a microsoft select client, for introducing SQL 2005 to a new BI project. The company already has SQL 2000. I would like to have a full tabular format of SQL 2005 SSIS Features comparison to SQL server 2000 DTS.
Most of feature enhancement paper has only tell what is new but not tell about what is already existed and how much it is improved (in quality and quantity). Yes, tabular format is more useful.
I tried to search through microsoft.com, technet but still cannot find the relavent one. Would you please give me a web link ?
Please suggest. I am not impersonated myself like a competitor. You can verify my id by sending e-mail to chaivat.jirapummin@th.standardchartered.com
Best regards,
CJira
View 5 Replies
View Related
May 29, 2006
Hi all,
I would like to ask if any research conducted on comparisons of SQL Server 2000 and SQL Server 2005? If yes, where can I find them?
Thanks a lot!
Gabriel
View 1 Replies
View Related
Mar 23, 2006
Good day to all of you
I am faced SQL SERVER 2000 Memory usage problem. I am using Windows 2000 Server SP4 and SQL SERVER 2000 SP4. When User running some in-house application software, the memory for sqlservr.exe was increased . But, when user logout from the software. The sqlservr.exe did not decrease the memory. I have around 100 Users in my company. SQL Memory will countinues increase till max memory usage in CPU.
May i know how to order SQL SERVER need to purge memory when USER was log off from the Program? Or my SQL SERVER was corrupted /missing file?
Please advice
Many thanks!
Melvin
View 4 Replies
View Related
Jan 28, 2004
Hello,
Can any one help me with this topic:
Comparison of SQL Server 7.0 Replication with SQL Server 2000
Replication
Thanks!
View 3 Replies
View Related
Feb 1, 2004
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_GetMemo(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,
View 7 Replies
View Related
Jan 30, 2014
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
View 7 Replies
View Related
Feb 4, 2008
I have several SQL 2000 servers that are running SQL 8.002162. Most are Windows 2003. Because of a note I saw in a non MS forum, I was testing these servers for the numeric comparison issue in MS article 899976, "FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs". All the servers act as expected (fixed) since they are running post SP4 and patches. But, one server fails the comparison test scripts in the KB. All these servers are running the same version of SQL. The server that still fails is windows 2003 SP1.
Why does one server reporting to be version 8.002162 fail a test that should be patched and does it mean that although the server is reporting it is version 8.00.2162 it has failed some updates and might be upatched for other issues?
View 1 Replies
View Related
Jan 16, 2007
I need to determine how often our SRS reports are being used. Is there any internal metric I can set and then check to find this out? We have a lot of reports and want to find out if some of them can be deleted for low usage. I understand SRS 2005 might have this capability but I have heard there's a way to do this in SRS 2000 as well.
If there is nothing internal to SRS 2000 that will do this, I wonder if anyone else has written some code to do this or has some ingenious method!
Thanks for any help!
View 1 Replies
View Related
Aug 3, 2007
We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
Thanks,
Geoff
WA POLICE
View 5 Replies
View Related
Nov 2, 2007
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
View 6 Replies
View Related
Sep 7, 2007
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
View 3 Replies
View Related
Dec 29, 2005
As many of you know I've been hunting down a SQL CE alternative / embedded DB that is easily portable among handhelds.
It turns out, that without paying for some other DB, there really aren't any good ones... Unless you go GPL, and the interpretation of GPL that most are using is that your code too is GPL even though you may only Dynamically Link to their app. I think this is likely wrong, but none the less I'm back here yet again.
What I'm looking for this time is a definition of how SQL Server CE is implemented, how it differs from a full blown DB, etc, so I can more thourally explain "why SQL CE" is an embedded DB, and how it isn't the full blown SQL Server that has a bad reputation with some.
My guess is:
SQL Server CE is implemented via DLL. There aren't sockets etc to allow you to connect, and it operates more like an advanced hash on the file system.
Please correct me if i'm wrong, or... send me a link where I can further research. Also, if you have another embedded DB as a suggestion, that's free of course, please point it my way.
Thanks!
View 4 Replies
View Related
Jul 23, 2005
I am currently in the process of making changes to an application usinga SQL Server database and have made changes to a development copy of thelive database which includes changing and adding columns to tables andupdating stored procedures.I was wondering if anyone knew of any free software that could produceat least a list of the changes between the databases and possibly ascript to allow for easy updating of the live database.*** Sent via Developersdex http://www.developersdex.com ***
View 5 Replies
View Related
Mar 8, 2006
Hi all,
I have the following select that is designed to compare values on parallel servers.SELECT TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate,TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate
FROM TradeAnalysis.dbo.SalesIncome_Quarterly AS TA1
FULL OUTER JOIN L_TA_MIRROR.TradeAnalysis.dbo.SalesIncome_Quarterl y AS TA2
ON ((TA1.OSID = TA2.OSID) AND (TA1.QtrYear = TA2.QtrYear) AND (TA1.QuarterNo = TA2.QuarterNo))
WHERE(BINARY_CHECKSUM(TA1.EPS, TA1.Sales, TA1.Income, TA1.EarningsReportDate) <>
BINARY_CHECKSUM(TA2.EPS, TA2.Sales, TA2.Income, TA2.EarningsReportDate))Disregard the full outer join, it is necessary in the full select, I have just pared it down to show here and for testing. A standard join will also have the same result.
The trouble is that the comparison (the BINARY_CHECKSUM) is failing on the EarningsReportDate column, which is defined on BOTH servers in DUPLICATE databases as a smalldatetime.
The data is actually the same in both rows of each table (as is all the other data being compared). What I am seeing is that the select returns milliseconds on the LINKED server (the L_TA_MIRROR reference), but not on the local database?
for example,
Local server data returned in above select is 2000-01-28 00:00:00
Linked server data returned in above select is 2000-01-28 00:00:00.000
Wassup with THAT? If I remove the date from the select/comparison, it says everything matches, so I am pretty sure this is the culprit that is causing the BINARY_CHECKSUM comparison to fail.
Is there some setting that I need to put on the Linked Server reference that says "treat this as the smalldatetime data type it IS, dammit!"????
I can get by the issue if I replace the date reference in the linked server part of the select with CAST(TA2.EarningsReportDate AS smalldatetime) but why do I have to do this?
Yes, I am SURE both servers have the column in question defined as a smalldatetime, as running the same select on either server causes the same failure, but only when referencing the LINKED server column.
View 1 Replies
View Related
Oct 6, 2005
Hi, I'm looking for a tool to compare 2 sql server database and makes a script of the differences (like an added table or record). We want to use it to generate a script which we can send to our customers to update their database if a change has been made. Example: We send our customers database A. After half a a year they request some changes to be made to the application that affects the database, say a table has to be added. We want the tool to automatically detect the difference between database A when it was first released and after the changes has been made and generate a script. Anyone suggestions? T.I.A., ratjetoes.
View 1 Replies
View Related
Oct 12, 2015
I want to compare SQL server Logins along with SID's across following 6 servers which includes Development, UAT, STG and Production.Is there any query available or is there any easy way to do so?
View 4 Replies
View Related
Jul 27, 2005
I am no expert in sql, but I keep stubbling on this problem:I have a table t1 with 2 columns (a,b)I have a table t2 with 2 columns (c,d)I need to delete all records from t1 which have the same value (a,b)than the value of (c,d) in all records in the t2 table.I oracle, this is simple:delete from t1where (a,b) in (select c,d from t2)because Oracle has support for this syntax. Dont remember how they callit. But this is not support in sql server. So I have to resort to:delete from t1where a + '+' + b in ( select c + '+' + d from t2)Of course, a,b,c,d must be varchar for this to work. Basically I fake aunique key for the records. Is there a better way to do this?Thanks
View 3 Replies
View Related
Jun 10, 2014
Is there a limit on the size of the strings on both sides of the '=' sign for string comparison? If I have two varchar(max) strings, will the comparison be done beyond 8,000 characters?
View 1 Replies
View Related
Mar 18, 2008
I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
View 2 Replies
View Related
Mar 18, 2008
I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
View 1 Replies
View Related
Nov 15, 2012
I would like to know what can be the equivalent update command in SQL server?
Code:
update Table1 set EndFlag = 1
where (c1,c2) in (select c1, MAX(c2) from Table1 group by c1)
View 2 Replies
View Related
Aug 10, 2015
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
View 9 Replies
View Related
Nov 2, 2014
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
View 7 Replies
View Related
Mar 1, 2015
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ;
GO
--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
View 3 Replies
View Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
Sep 25, 2006
Hello.
I am using a SQL Server 2005 devenv and each time I open my ssis
package, the server mem usage jumps up to almost 400mb ram. On opening,
tries to validate each data flow task and that takes forever, I have
delayed validation however still takes time to load. I am currently
using terminal services to access visual studio and the project has two
ssis packages that myself and another developer are working on, could
this have something to do with this?
In saying this, everything worked perfectly a week ago, now its
extremely frustrating.
Anyone with feedback would be greatly appreciated.
Cheers
View 1 Replies
View Related
Jun 7, 2006
I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.
SQL Server works fine when connecting to it using Mangement Studio on Windows XP.
However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).
When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.
The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.
The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.
On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).
Any ideas?
View 2 Replies
View Related
Jun 10, 2005
hey guys,could anyone help me out ..?i have a serious problem with my SQL server DB ..it uses up all my memory (i see it in task manager) after a whole day of asp's triggering the db .. it should go down when there are no triggers anymore, right?please help :)Stallema
View 4 Replies
View Related
May 9, 2003
Hi,
One of the production box running only sql server application, is showing 80% memory usage on the task manager-memory usage history right now.
We are running sql server 2000 standard version-sp3 with 2GB memory on this box. Server is not on the scheduled reboot at this point.
We have seen this behavior for this box last month that after task manager showing 90% memory usage contantly for several days, when server was manually rebooted, memory usage dropped to 35%. Now it's back to 80%.
Our DBA thinks that server should be rebooted on a regular schedule regardless of memory problem. Our network admin doesn't seem to agree with this. He is not ready to reboot the machine even with this high memory usage.
There is no noticable difference performancewise yet.
My questions are:
Is it bad that memory usage reaches from 35% constant to 80-90% or is it common? Should sql server be rebooted immediately to take care of it? Should sql server 2000 rebooted on regular basis regardless of any problems? Shouldn't sql server be releasing memory back to the OS even without rebooting? How do I find out whether server actually is going through memory problems and what is causing it?
Thanks in advance for your opinions,
Shaili
View 3 Replies
View Related
Feb 23, 2001
Has anybody encountered SQL SERVER 6.5 using 100% server CPU at startup with no other processes running on the NT server or accessing SQL Server?
I have tried starting up in minimal config mode to access the config values, but am having SQL Server hanging by that point as CPU usage has hit 100%
If anybody knows an answer please let me know.
Thanks
View 3 Replies
View Related
Dec 1, 2004
Hi All,
Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important.
What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.
Upgrading the memory is currently not an option :(
I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?
Can SQL release the memory and still act as normal?
Any input would be generously appreciated
Cheers!
View 2 Replies
View Related
