Difference between 2000 and 2005

Hello,
do you know defined performance differences between SQL 2000 and SQL
2005?
thanks in advance
I can't compare directly - there are loads of enhancements in the
architecture, some of which have no direct comparison eg a lot of what was
originally done in post snapshot scripts can now be done directly and merge
has unidirectional articles which aren't logged using triggers etc - also we
have different hardware between the installations.. For a quantitative
comparison where things are held constant as far as possible, have a look at
this article:
http://www.microsoft.com/technet/prodtechnol/sql/2005/mergrepl.mspx. If
applies to merge, but some of the snapshot details will equally apply to
transactional also.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Diff. performance in Query Analyzer than when using stored procedure

Hi group,

I have a select statement that if run against a 1 million record
database directly in query analyzer takes less than 1 second.
However, if I execute the select statement in a stored procedure
instead, calling the stored proc from query analyzer, then it takes
12-17 seconds.

Here is what I execute in Query Analyzer when bypassing the stored
procedure:

USE Verizon
GO
DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Here is what I execute in Query Analyzer when calling the stored
procedure:

DECLARE @.phonenumber varchar(15)
SELECT @.phonenumber = '6317898493'
EXEC Verizon.dbo.baddebt_phonelookup @.phonenumber

Here is the script that created the stored procedure itself:

CREATE PROCEDURE dbo.baddebt_phonelookup @.phonenumber varchar(15)
AS

SELECT Source_Identifier,
BADD_Sequence_Number,
Record_Type,
BAID ,
Social_Security_Number ,
Billing_Name,
Billing_Address_1,
Billing_Address_2,
Billing_Address_3,
Billing_Address_4,
Service_Connection_Date,
Disconnect_Date,
Date_Final_Bill,
Behavior_Score,
Account_Group,
Diconnect_Reason,
Treatment_History,
Perm_Temp,
Balance_Due,
Regulated_Balance_Due,
Toll_Balance_Due,
Deregulated_Balance_Due,
Directory_Balance_Due,
Other_Category_Balance

FROM BadDebt
WHERE (Telephone_Number = @.phonenumber) OR (Telephone_Number_Redef =
@.phonenumber)
order by Service_Connection_Date desc

RETURN
GO

Using SQL Profiler, I also have the execution trees for each of these
two different ways of running the same query.

Here is the Execution tree when running the whole query in the
analyzer, bypassing the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Bookmark Lookup(BOOKMARK:([Bmk1000]),
OBJECT:([Verizon].[dbo].[BadDebt]))
|--Sort(DISTINCT ORDER BY:([Bmk1000] ASC))
|--Concatenation
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Index]),
SEEK:([BadDebt].[Telephone_Number]=[@.phonenumber]) ORDERED FORWARD)
|--Index
Seek(OBJECT:([Verizon].[dbo].[BadDebt].[Telephone_Redef_Index]),
SEEK:([BadDebt].[Telephone_Number_Redef]=[@.phonenumber]) ORDERED
FORWARD)
------------

Finally, here is the execution tree when calling the stored procedure:

------------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
------------

Thanks for any help on my path to optimizing this query for our
production environment.

Regards,

Warren Wright
Scorex Development Teamwarren.wright@.us.scorex.com (Warren Wright) wrote in message news:<8497c269.0308051401.2e65bb80@.posting.google.com>...
> Hi group,
> I have a select statement that if run against a 1 million record
> database directly in query analyzer takes less than 1 second.
> However, if I execute the select statement in a stored procedure
> instead, calling the stored proc from query analyzer, then it takes
> 12-17 seconds.

<snip
One possible reason is parameter sniffing - see here:

http://groups.google.com/groups?sel...7&output=gplain

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0308060118.46c12f2e@.posting.google.com>...
> One possible reason is parameter sniffing - see here:
> http://groups.google.com/groups?sel...7&output=gplain
> Simon

Wow. Thats a bit of an eye opener. It makes me wonder how best to
make sure a decent plan is chosen by SQL Server, and the answer seems
to be to make it recompile the stored procedure every time ?

or is there a way to make SQL simply use the index at all times? I'd
hate to spend a lot of time on my dev machine getting the stored
procedure to run correctly on a million record table, only to port it
to my production machine and have it take forever on the 33 million
record database because of some magically crafted execution plan :-)

Thanks,

Warren|||Here is something else that I don't understand. The stored procedure
I listed above compares a phone number that is passed in against a
Telephone_Number column that is 15 digits long, and against a computed
column (Telephone_Number_Redef), that is the left 10 digits of the
Telephone_Number column.

This is because sometimes our client passes in a 10 digit number, and
sometimes a 15 digit version that includes some check digits on the
end (Don't ask).

Anyway, in the execution plan for when the stored proc is executing, I
see the following:

----------
Sort(ORDER BY:([BadDebt].[Service_Connection_Date] DESC))
|--Filter(WHERE:([BadDebt].[Telephone_Number]=[@.phonenumber] OR
[BadDebt].[Telephone_Number_Redef]=[@.phonenumber]))
|--Compute Scalar(DEFINE:([BadDebt].[Telephone_Number_Redef]=substring(Convert([BadDebt].[Telephone_Number]),
1, 10)))
|--Table Scan(OBJECT:([Verizon].[dbo].[BadDebt]))
----------

It appears to be recomputing the Telephone_Number_Redef column values
on the fly, instead of using the values already present. The
Telephone_Number_Redef column is indexed specifically to allow that
second comparison in the WHERE statement to be a SARG, but it seems
this is being ignored.

Is it being ignored because SQL had already decided to do a table
scan, and so though it might as well speed things up by not scanning
both columns? or is SQL doing a table scan because it thinks it needs
to re-compute the values for Telephone_Number_Redef on the fly?

Argh.

Thanks,

Warren Wright
Scorex Development Team
Dallas|||More follow-up on this issue, to help you experts analyze what's going
on here.

I've spent the day trying various things, with no success. I tried
using hints to suggest that the index on Telephone_Number_Redef be
used, which results in an error stating the stored procedure couldn't
be executed due to an unworkable hint.

I've tried declaring a new variable in the stored proc with a value
set equal to the @.phonenumber input, so SQL couldn't optimize based on
the actual value being passed in.

I've tried changing the index for the computed column to be a
clustered index.

I only wish I could simply tell SQL to use the same execution plan it
uses when I run the query from the analyzer!! All problems would be
solved!

No matter what, if I run the query from query analyzer, the response
time is a few milliseconds. If I run the stored procedure, the
response time is at least 17 seconds due to a completely suboptimal
execution plan (where the Telephone_Number_Redef's index isn't used at
all).

Introducing the new version of the stored proc with the OR statement
that checks against the computed column as well bogs down the
production server, and results in timeouts and app errors for our
client.

Frustrated,

Warren|||[posted and mailed, please reply in news]

Warren Wright (warren.wright@.us.scorex.com) writes:
> Here is something else that I don't understand. The stored procedure
> I listed above compares a phone number that is passed in against a
> Telephone_Number column that is 15 digits long, and against a computed
> column (Telephone_Number_Redef), that is the left 10 digits of the
> Telephone_Number column.

Computed column? Which you have an index on? Aha!

While Bart's article on parameter sniffing is good reading it is not
the answer here. Index on computed columns (as well on views) can
only be used if these SET options are ON: ANSI_NULLS, QUOTED_IDENTIFIER,
ANSI_WARNINGS, ARITHABORT, ANSI_PADDING and CONCAT_NULLS_YIELDS_NULL.
And NUMERIC_ROUNDABORT be OFF.

The killer here is usually QUOTED_IDENTIFIER. That option, together
with ANSI_NULLS is saved with the procedure, so that the run-time
setting does not apply, but the setting saved with the procedure.
QUOTED_IDENTIFIER is ON by default with ODBC and OLE DB, as well
with Query Analyzer. But OSQL and Enterprise Manager turns it off.
So you need to make sure that the procedure is created with
QUOTED_IDENTIFIER on.

You can review the current setting with

select objectproperty(object_id('your_sp'), 'IsQuotedIdentOn')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

diff. between named pipe & TCP-IP

Hi
Is there any diff. between Named Pipe& TCP-IP protocol from performance
point of view there by I can force all the users connecting to server with
TCP-IP proctocol only .
Regards
Ajay RengunthwarFrom BOL:
Named Pipes vs. TCP/IP Sockets
In a fast local area network (LAN) environment, Transmission Control
Protocol/Internet Protocol (TCP/IP) Sockets and Named Pipes clients are
comparable in terms of performance. However, the performance difference
between the TCP/IP Sockets and Named Pipes clients becomes apparent with
slower networks, such as across wide area networks (WANs) or dial-up
networks. This is because of the different ways the interprocess
communication (IPC) mechanisms communicate between peers.
For named pipes, network communications are typically more interactive. A
peer does not send data until another peer asks for it using a read command.
A network read typically involves a series of peek named pipes messages
before it begins to read the data. These can be very costly in a slow
network and cause excessive network traffic, which in turn affects other
network clients.
It is also important to clarify if you are talking about local pipes or
network pipes. If the server application is running locally on the computer
running an instance of Microsoft® SQL ServerT 2000, the local Named Pipes
protocol is an option. Local named pipes runs in kernel mode and is
extremely fast.
For TCP/IP Sockets, data transmissions are more streamlined and have less
overhead. Data transmissions can also take advantage of TCP/IP Sockets
performance enhancement mechanisms such as windowing, delayed
acknowledgements, and so on, which can be very beneficial in a slow network.
Depending on the type of applications, such performance differences can be
significant.
TCP/IP Sockets also support a backlog queue, which can provide a limited
smoothing effect compared to named pipes that may lead to pipe busy errors
when you are attempting to connect to SQL Server.
In general, sockets are preferred in a slow LAN, WAN, or dial-up network,
whereas named pipes can be a better choice when network speed is not the
issue, as it offers more functionality, ease of use, and configuration
options.
"AJAY R" <dba_pune@.hotmail.com> wrote in message
news:er$fIigRDHA.3132@.tk2msftngp13.phx.gbl...
> Hi
> Is there any diff. between Named Pipe& TCP-IP protocol from performance
> point of view there by I can force all the users connecting to server with
> TCP-IP proctocol only .
> Regards
> Ajay Rengunthwar
>
>

Diagnosing Performance Problem?

I have a database server that has is acting very sluggish. Using the performance monitor, I can see that disk use is steadily at 100% usage.

The system is so overburdened that attempting to do even basic tasks (open folders, look in Enterprise Manager, get screen repaints) has become impractical.

Even if I could get to the Enterprise Manager "Process Info" display, what would I look for to tell me what is wrong?From a remote PC, use Profiler to monitor all queries with over say 1000 reads. Copy those queries out to Query Analyzer, and see if you can get some indexes on the underlying tables to speed them up.

Another thing to check is how much memory is in the box? SQL Server will default to taking all it wants, but if you have only 64MB in your box, then you are in for a world-o-hurt.|||Originally posted by MCrowley
From a remote PC, use Profiler to monitor all queries with over say 1000 reads.

How do I do this? What is Profiler?

thanks for the feedback!|||Originally posted by RogerWilco
How do I do this? What is Profiler?

thanks for the feedback!

Found it. This is definitely helping.

Thankfully, we just hired a real DBA who starts Thursday.

Thanks again!

Diagnosing Memory Use

I've noticed high memory consumption on some of our workstations that eventually cause unacceptable performance problems. Through Task Manager, I see that SQL Server is consuming the bulk of this memory.

A coworker has implemented a system to periodically restart SQL Server (using netstop/netstart). This does hold down memory usage but I'd like to avoid doing this.

I'd like to diagnose SQL Server and find exactly what it is doing that consumes so much memory.

I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler

Which is the best tool to do this and how would I approach this scenario?The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.|||Originally posted by joejcheng
The bottleneck may not be the memory consumption. The Task Manager is showing you the max memory sql is allowed to allocate, not the actual memory sql is currently using. You may have something else hosted your system.

Task Manager shows both Mem Usage (actively paged in) and VM Size (reserved but not necessarily paged in) for the sqlservr.exe process. Those are 651MB/670MB respectively. Using Performance Monitor I see that the disk use is high, and the "Pages/sec" is high.

Shortly after doing a netstop/netstart restart of SQL Server Mem Usage/VM Size = 36MB/44MB respectively and performance is great. Our application has already reconnected to SQL Server and is operating at full speed.

This really has to be SQL Server related.|||Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.|||Originally posted by MCrowley
Just to make sure, the users all have SQL or MSDE installed on their workstations or laptops, right? You can cap the amount of memory that SQL Server uses, but that will probably increase the amount of disk I/O and may cause even more problems. Anyway, try running this from a command line

osql /S(local) /E /Q"sp_configure 'max server memory (MB)', 256"
osql /S(local) /E /Q"reconfigure with override"

You may need to modify that a bit with the proper workstation\instancename, but it will keep SQL Server from hogging memory. Good luck.

Thanks for the feedback!

Actually, there are no users just an internal app that runs 24/7.

We could cut SQL Server's memory allotment but as you said, that will increase disk use which is the main problem. What I want to know is why it is using the memory that it is. Once I identify that, I can redesign to use less resources. I suspect a design problem rather than simply a lack of resources.|||Ahh. Then your problem seems to be that your database has grown to more than probably 500 MB. More likely to more than 1GB. SQL Server (and Oracle, Informix, DB2, etc.) will store information in memory for faster access. You can cut down on the amount of I/O you need to perform by reducing redundancy in the data, or applying indexes on the tables that are queried most often.

The first method may be beyond help, as reconfiguring the database is near impossible without a total re-write of the application. As for the second, open Profiler in the SQL program group, and connect to your server. Under Events, remove Security Audit and Sessions. On the filters page, add a limit under Reads of greater than or equal say 100 reads. 1 read is only a logical I/O rather than a physical one, but you should be able to figure out quickly which queries are your problem children. Good luck.|||Hi,

I had similar doubts and i would like you to see the following link, which is a discussion on the same forum
http://www.dbforums.com/showthread.php?threadid=966886&referrerid=47114

regards,
henry

Diagnosing Memory Problems?

I've noticed high memory consumption on some of our workstations that
eventually cause unacceptable performance problems. Through Task
Manager, I see that SQL Server (sqlsrvr.exe) is consuming the bulk of
this memory.
We've found that restarting SQL Server (using netstop/netstart)
dramatically reduces memory usage but it gradually creeps back to an
unacceptable level and becomes a problem (high pages/sec thrashing). We
are setting up a system that will periodically restart SQL Server. This
is a stopgap solution.
I'd like to identify what in SQL Server is using this memory and why.
Ideally, I would redesign my applications so that this wouldn't happen.
I suspect some kind of design flaw or usage error and I want to identify
it. Adding physical RAM isn't an option since this is running on a 70
workstation farm.
I don't want to simply limit SQL Server's memory allotment since this
leads to increased disk use which is the main problem.
I am aware of three tools to diagnose this:
- Performance Monitor
- Enterprise Manager "Current Activity"
- SQL Server Profiler
Which is the best tool to do this and how would I approach this scenario?
Thank You!SQL Server and most other high-end database products are
essentially disk caching engines (that provide
transactions and management etc)
so under most circumstances, its suppose to use all
available memory for most effective disk caching
it will also release memory to other apps if the available
system memory falls below 4-6MB (i do wish MS would
increase this setting to 16-32MB)
however, if you are not using it as a dedicated DB server,
use Ent Manager to restrict the max memory to some desired
amount, 64-128M is good for a development system.
don't trying to figure out which queries are using memory,
unless you want to reduce the amount of data.
>--Original Message--
>I've noticed high memory consumption on some of our
workstations that
>eventually cause unacceptable performance problems.
Through Task
>Manager, I see that SQL Server (sqlsrvr.exe) is consuming
the bulk of
>this memory.
>We've found that restarting SQL Server (using
netstop/netstart)
>dramatically reduces memory usage but it gradually creeps
back to an
>unacceptable level and becomes a problem (high pages/sec
thrashing). We
>are setting up a system that will periodically restart
SQL Server. This
>is a stopgap solution.
>I'd like to identify what in SQL Server is using this
memory and why.
>Ideally, I would redesign my applications so that this
wouldn't happen.
>I suspect some kind of design flaw or usage error and I
want to identify
>it. Adding physical RAM isn't an option since this is
running on a 70
>workstation farm.
>I don't want to simply limit SQL Server's memory
allotment since this
>leads to increased disk use which is the main problem.
>I am aware of three tools to diagnose this:
>- Performance Monitor
>- Enterprise Manager "Current Activity"
>- SQL Server Profiler
>Which is the best tool to do this and how would I
approach this scenario?
>Thank You!
>.
>|||Excellent insight. Thank you!
joe chang wrote:
> SQL Server and most other high-end database products are
> essentially disk caching engines (that provide
> transactions and management etc)
> so under most circumstances, its suppose to use all
> available memory for most effective disk caching
> it will also release memory to other apps if the available
> system memory falls below 4-6MB (i do wish MS would
> increase this setting to 16-32MB)
> however, if you are not using it as a dedicated DB server,
> use Ent Manager to restrict the max memory to some desired
> amount, 64-128M is good for a development system.
> don't trying to figure out which queries are using memory,
> unless you want to reduce the amount of data.|||I am consistantly getting Physical Memory Usage High errors. My Buffer Cache is running at 99% and the Server Memory is at 87%. Any thoughts on what's causing this?

Development Vs Enterprise | Performance

We're trying to work out why the same SQL Script takes much longer to run within the Enterprise environment compared to the Development?

What makes matters worse is the development version is on a standard desktop (P4 1.8 256MB RAM) compared to the Enterprise version which sits on a dual Xeon with 1Gig of RAM.

Is is related to the SQL product or am I missing something here? :confused:What level of user activity is occuring on your "Enterprise" (I assume you mean "Production") environment?

What does the script do? Heavy inserts/updates/deletes? These could all be affected by other users accessing the same data tables simultaneously.

Does indexing match between the two systems?

Have you run UPDATE STATISTICS on your production environment?

Are the two databases the same size?

Lots of things that could cause this...|||I'm trying to work out why our SQL production server is so slow.
Using Select statements everything is fine but using an Insert statement causes immense slow down.

I created a new database in each of the environments listed below then ran a test script via Query Analyser.

The Desktop running the development edition took just over 1 minute, the server - running the full production app - took nearly 7 minutes.

The script is basic and simply created a table then inserts 100,000 records - I've copied the script below.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test_Table]
GO

CREATE TABLE [dbo].[Test_Table] (
[ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

DECLARE @.i int
SET @.i=100000
WHILE @.i>0
BEGIN
INSERT INTO Test_Table values (@.i)
SET @.i=@.i-1
END|||You have to make the tests with the same load on both servers, that's the only way to get a value that you can compare between those two...|||There's zero load on either machine as I perform these tests.
Using Task Manager I monitored CPU and Memory usage which remains at a minimum.
Well and truly stuck here. Do you think is could be a configuration issue in SQL? I've checked and both machines are running Service Pack 3a.

Does the developer edition process differently compared to the full Enterprise/Production Edition?|||There's zero load on either machine as I perform these tests.
...

So you have put the production server "offline" during testing?
...and you are 100% sure that nobody is accessing the server during the performance test!?

Does the developer edition process differently compared to the full Enterprise/Production Edition?
I don't think that there should be big differences, and if there were they should be the other way round...|||We have resolved this issue - thanks to all those who helped.
The production servers are RAID enabled, and we've had to fit a battery to enable write caching. So ultimatley is was an I/O issue but hardware related.

Developer Server performance on WinXP

Hi all,
I have a strange performance issue on one of our developer stations. This
station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
it.
The problems are:
1) The tools (like QA for instance) cannot connect to other SQL Servers on
the network.
2) EM will take at least 1/2 minute to do any task. Any task like expand
the Databases tree to show all the databases... or ... expand one of the
databases to show the items(tables, storedproc, roles, etc) for that
database .. or .. expand the tables tree to show all the tables ... etc
This is a major problem since item 1 prevents this developer from testing
against another SQL Server. And, at the same time item 2 prevents the
developer from testing against his own local test server.
I have tried all the common things:
1) Reinstall SQL Server (and Client Tools) on this station
2) scan the disk (for lost clusters and such)
3) remove temp and other useless files
4) defrag the disk
Even the EM itself seems to repaint its windows slowly .. you can actually
see the drawing process.
The computer can otherwise access the network .. and no slow downs there
either.
Anyone have any thoughts?
Thanks in advance...
-BBHi Brian,
My first though is that its not SQL Server thats causing this but perhaps
the machine iteself.
If you go to Administration Tools -> Performance and set up the following
counters.
Pages/sec
Avg. Disk Queue Length
% Processor time
Network -> Bytes Total / Second.
My own personal opionon here is you may need more memory.
Peter
"Brian Burgess" wrote:

> Hi all,
> I have a strange performance issue on one of our developer stations. Thi
s
> station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
> it.
> The problems are:
> 1) The tools (like QA for instance) cannot connect to other SQL Servers o
n
> the network.
> 2) EM will take at least 1/2 minute to do any task. Any task like expan
d
> the Databases tree to show all the databases... or ... expand one of the
> databases to show the items(tables, storedproc, roles, etc) for that
> database .. or .. expand the tables tree to show all the tables ... etc
> This is a major problem since item 1 prevents this developer from testing
> against another SQL Server. And, at the same time item 2 prevents the
> developer from testing against his own local test server.
> I have tried all the common things:
> 1) Reinstall SQL Server (and Client Tools) on this station
> 2) scan the disk (for lost clusters and such)
> 3) remove temp and other useless files
> 4) defrag the disk
> Even the EM itself seems to repaint its windows slowly .. you can actually
> see the drawing process.
> The computer can otherwise access the network .. and no slow downs there
> either.
> Anyone have any thoughts?
> Thanks in advance...
> -BB
>
>|||Hi Peter,
I'm inclined to agree that it is more likely a hardware issue. However the
amount of RAM installed is 512MB. Memory and disk scan report no problems
(although these scans are not infallible).
I set these parameters up on the PerfMon ... any thing I should watch for?
Any SQL Server settings I can/should 'tweak'?
Thx
-BB
"Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
news:11490686-81F5-462A-85A1-9804A25301C1@.microsoft.com...[vbcol=seagreen]
> Hi Brian,
> My first though is that its not SQL Server thats causing this but perhaps
> the machine iteself.
> If you go to Administration Tools -> Performance and set up the following
> counters.
> Pages/sec
> Avg. Disk Queue Length
> % Processor time
> Network -> Bytes Total / Second.
> My own personal opionon here is you may need more memory.
> Peter
>
> "Brian Burgess" wrote:
>
This[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
expand[vbcol=seagreen]
testing[vbcol=seagreen]
actually[vbcol=seagreen]

Developer Server performance on WinXP

Hi all,
I have a strange performance issue on one of our developer stations. This
station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
it.
The problems are:
1) The tools (like QA for instance) cannot connect to other SQL Servers on
the network.
2) EM will take at least 1/2 minute to do any task. Any task like expand
the Databases tree to show all the databases... or ... expand one of the
databases to show the items(tables, storedproc, roles, etc) for that
database .. or .. expand the tables tree to show all the tables ... etc
This is a major problem since item 1 prevents this developer from testing
against another SQL Server. And, at the same time item 2 prevents the
developer from testing against his own local test server.
I have tried all the common things:
1) Reinstall SQL Server (and Client Tools) on this station
2) scan the disk (for lost clusters and such)
3) remove temp and other useless files
4) defrag the disk
Even the EM itself seems to repaint its windows slowly .. you can actually
see the drawing process.
The computer can otherwise access the network .. and no slow downs there
either.
Anyone have any thoughts?
Thanks in advance...
-BB
Hi Brian,
My first though is that its not SQL Server thats causing this but perhaps
the machine iteself.
If you go to Administration Tools -> Performance and set up the following
counters.
Pages/sec
Avg. Disk Queue Length
% Processor time
Network -> Bytes Total / Second.
My own personal opionon here is you may need more memory.
Peter
"Brian Burgess" wrote:

> Hi all,
> I have a strange performance issue on one of our developer stations. This
> station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
> it.
> The problems are:
> 1) The tools (like QA for instance) cannot connect to other SQL Servers on
> the network.
> 2) EM will take at least 1/2 minute to do any task. Any task like expand
> the Databases tree to show all the databases... or ... expand one of the
> databases to show the items(tables, storedproc, roles, etc) for that
> database .. or .. expand the tables tree to show all the tables ... etc
> This is a major problem since item 1 prevents this developer from testing
> against another SQL Server. And, at the same time item 2 prevents the
> developer from testing against his own local test server.
> I have tried all the common things:
> 1) Reinstall SQL Server (and Client Tools) on this station
> 2) scan the disk (for lost clusters and such)
> 3) remove temp and other useless files
> 4) defrag the disk
> Even the EM itself seems to repaint its windows slowly .. you can actually
> see the drawing process.
> The computer can otherwise access the network .. and no slow downs there
> either.
> Anyone have any thoughts?
> Thanks in advance...
> -BB
>
>
|||Hi Peter,
I'm inclined to agree that it is more likely a hardware issue. However the
amount of RAM installed is 512MB. Memory and disk scan report no problems
(although these scans are not infallible).
I set these parameters up on the PerfMon ... any thing I should watch for?
Any SQL Server settings I can/should 'tweak'?
Thx
-BB
"Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
news:11490686-81F5-462A-85A1-9804A25301C1@.microsoft.com...[vbcol=seagreen]
> Hi Brian,
> My first though is that its not SQL Server thats causing this but perhaps
> the machine iteself.
> If you go to Administration Tools -> Performance and set up the following
> counters.
> Pages/sec
> Avg. Disk Queue Length
> % Processor time
> Network -> Bytes Total / Second.
> My own personal opionon here is you may need more memory.
> Peter
>
> "Brian Burgess" wrote:
This[vbcol=seagreen]
on[vbcol=seagreen]
on[vbcol=seagreen]
expand[vbcol=seagreen]
testing[vbcol=seagreen]
actually[vbcol=seagreen]

Developer Server performance on WinXP

Hi all,
I have a strange performance issue on one of our developer stations. This
station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
it.
The problems are:
1) The tools (like QA for instance) cannot connect to other SQL Servers on
the network.
2) EM will take at least 1/2 minute to do any task. Any task like expand
the Databases tree to show all the databases... or ... expand one of the
databases to show the items(tables, storedproc, roles, etc) for that
database .. or .. expand the tables tree to show all the tables ... etc
This is a major problem since item 1 prevents this developer from testing
against another SQL Server. And, at the same time item 2 prevents the
developer from testing against his own local test server.
I have tried all the common things:
1) Reinstall SQL Server (and Client Tools) on this station
2) scan the disk (for lost clusters and such)
3) remove temp and other useless files
4) defrag the disk
Even the EM itself seems to repaint its windows slowly .. you can actually
see the drawing process.
The computer can otherwise access the network .. and no slow downs there
either.
Anyone have any thoughts?
Thanks in advance...
-BBHi Brian,
My first though is that its not SQL Server thats causing this but perhaps
the machine iteself.
If you go to Administration Tools -> Performance and set up the following
counters.
Pages/sec
Avg. Disk Queue Length
% Processor time
Network -> Bytes Total / Second.
My own personal opionon here is you may need more memory.
Peter
"Brian Burgess" wrote:
> Hi all,
> I have a strange performance issue on one of our developer stations. This
> station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed on
> it.
> The problems are:
> 1) The tools (like QA for instance) cannot connect to other SQL Servers on
> the network.
> 2) EM will take at least 1/2 minute to do any task. Any task like expand
> the Databases tree to show all the databases... or ... expand one of the
> databases to show the items(tables, storedproc, roles, etc) for that
> database .. or .. expand the tables tree to show all the tables ... etc
> This is a major problem since item 1 prevents this developer from testing
> against another SQL Server. And, at the same time item 2 prevents the
> developer from testing against his own local test server.
> I have tried all the common things:
> 1) Reinstall SQL Server (and Client Tools) on this station
> 2) scan the disk (for lost clusters and such)
> 3) remove temp and other useless files
> 4) defrag the disk
> Even the EM itself seems to repaint its windows slowly .. you can actually
> see the drawing process.
> The computer can otherwise access the network .. and no slow downs there
> either.
> Anyone have any thoughts?
> Thanks in advance...
> -BB
>
>|||Hi Peter,
I'm inclined to agree that it is more likely a hardware issue. However the
amount of RAM installed is 512MB. Memory and disk scan report no problems
(although these scans are not infallible).
I set these parameters up on the PerfMon ... any thing I should watch for?
Any SQL Server settings I can/should 'tweak'?
Thx
-BB
"Peter Nolan" <PeterNolan@.discussions.microsoft.com> wrote in message
news:11490686-81F5-462A-85A1-9804A25301C1@.microsoft.com...
> Hi Brian,
> My first though is that its not SQL Server thats causing this but perhaps
> the machine iteself.
> If you go to Administration Tools -> Performance and set up the following
> counters.
> Pages/sec
> Avg. Disk Queue Length
> % Processor time
> Network -> Bytes Total / Second.
> My own personal opionon here is you may need more memory.
> Peter
>
> "Brian Burgess" wrote:
> > Hi all,
> >
> > I have a strange performance issue on one of our developer stations.
This
> > station is Trad Chinese WinXP Pro with SQLServer 2000 Dev Ed installed
on
> > it.
> > The problems are:
> > 1) The tools (like QA for instance) cannot connect to other SQL Servers
on
> > the network.
> > 2) EM will take at least 1/2 minute to do any task. Any task like
expand
> > the Databases tree to show all the databases... or ... expand one of the
> > databases to show the items(tables, storedproc, roles, etc) for that
> > database .. or .. expand the tables tree to show all the tables ... etc
> >
> > This is a major problem since item 1 prevents this developer from
testing
> > against another SQL Server. And, at the same time item 2 prevents the
> > developer from testing against his own local test server.
> >
> > I have tried all the common things:
> > 1) Reinstall SQL Server (and Client Tools) on this station
> > 2) scan the disk (for lost clusters and such)
> > 3) remove temp and other useless files
> > 4) defrag the disk
> >
> > Even the EM itself seems to repaint its windows slowly .. you can
actually
> > see the drawing process.
> >
> > The computer can otherwise access the network .. and no slow downs there
> > either.
> >
> > Anyone have any thoughts?
> >
> > Thanks in advance...
> >
> > -BB
> >
> >
> >

Developer edition performance

Hi,
Is the developer edition of SQL Server 2000 *exactly* the same as the
enterprise edition - just without the production license?
It is mainly the performance aspect I am interested in. Standard edition is
limited to a low number of concurrent users & worker processes - is this
true with the deve edition.
Thanks in advance,
Stu> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
Yes.

> Standard edition is
> limited to a low number of concurrent users & worker processes
Who told you that? SE is not limited in concurrent users etc, it lacks a few
features that EE has,
like failover clustering, using lots of memory, some parallelization etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stu Lock" <s.lock@.cergis.com> wrote in message news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl..
.
> Hi,
> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
> It is mainly the performance aspect I am interested in. Standard edition i
s
> limited to a low number of concurrent users & worker processes - is this
> true with the deve edition.
> Thanks in advance,
> Stu
>|||It is the developer edition and personal edition which have some
limitations, not the standard edition... The limitation is not the number of
concurrent users, but limited in the number of worker threads. This prevents
you from using the dev or personal edition as a ig production server,
because performance will not be acceptable...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRaAFYBYEHA.2364@.TK2MSFTNGP12.phx.gbl...
> Yes.
>
> Who told you that? SE is not limited in concurrent users etc, it lacks a
few features that EE has,
> like failover clustering, using lots of memory, some parallelization etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stu Lock" <s.lock@.cergis.com> wrote in message
news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl...
is[vbcol=seagreen]
>

Developer edition performance

Hi,
Is the developer edition of SQL Server 2000 *exactly* the same as the
enterprise edition - just without the production license?
It is mainly the performance aspect I am interested in. Standard edition is
limited to a low number of concurrent users & worker processes - is this
true with the deve edition.
Thanks in advance,
Stu
> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
Yes.

> Standard edition is
> limited to a low number of concurrent users & worker processes
Who told you that? SE is not limited in concurrent users etc, it lacks a few features that EE has,
like failover clustering, using lots of memory, some parallelization etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stu Lock" <s.lock@.cergis.com> wrote in message news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
> It is mainly the performance aspect I am interested in. Standard edition is
> limited to a low number of concurrent users & worker processes - is this
> true with the deve edition.
> Thanks in advance,
> Stu
>
|||It is the developer edition and personal edition which have some
limitations, not the standard edition... The limitation is not the number of
concurrent users, but limited in the number of worker threads. This prevents
you from using the dev or personal edition as a ig production server,
because performance will not be acceptable...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRaAFYBYEHA.2364@.TK2MSFTNGP12.phx.gbl...
> Yes.
>
> Who told you that? SE is not limited in concurrent users etc, it lacks a
few features that EE has,
> like failover clustering, using lots of memory, some parallelization etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stu Lock" <s.lock@.cergis.com> wrote in message
news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
is
>

Developer edition performance

Hi,
Is the developer edition of SQL Server 2000 *exactly* the same as the
enterprise edition - just without the production license?
It is mainly the performance aspect I am interested in. Standard edition is
limited to a low number of concurrent users & worker processes - is this
true with the deve edition.
Thanks in advance,
Stu> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
Yes.
> Standard edition is
> limited to a low number of concurrent users & worker processes
Who told you that? SE is not limited in concurrent users etc, it lacks a few features that EE has,
like failover clustering, using lots of memory, some parallelization etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stu Lock" <s.lock@.cergis.com> wrote in message news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Is the developer edition of SQL Server 2000 *exactly* the same as the
> enterprise edition - just without the production license?
> It is mainly the performance aspect I am interested in. Standard edition is
> limited to a low number of concurrent users & worker processes - is this
> true with the deve edition.
> Thanks in advance,
> Stu
>|||It is the developer edition and personal edition which have some
limitations, not the standard edition... The limitation is not the number of
concurrent users, but limited in the number of worker threads. This prevents
you from using the dev or personal edition as a ig production server,
because performance will not be acceptable...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eRaAFYBYEHA.2364@.TK2MSFTNGP12.phx.gbl...
> > Is the developer edition of SQL Server 2000 *exactly* the same as the
> > enterprise edition - just without the production license?
> Yes.
>
> > Standard edition is
> > limited to a low number of concurrent users & worker processes
> Who told you that? SE is not limited in concurrent users etc, it lacks a
few features that EE has,
> like failover clustering, using lots of memory, some parallelization etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stu Lock" <s.lock@.cergis.com> wrote in message
news:evIfU7AYEHA.3536@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Is the developer edition of SQL Server 2000 *exactly* the same as the
> > enterprise edition - just without the production license?
> >
> > It is mainly the performance aspect I am interested in. Standard edition
is
> > limited to a low number of concurrent users & worker processes - is this
> > true with the deve edition.
> >
> > Thanks in advance,
> >
> > Stu
> >
> >
>