Performance Tuning Tips for SQL Server 2000

Dear All:
Can somebody help me on Performance tuning the SQL Server 2000 as I have
just begin learning the SQL server 2000. The current server configuration
stands as follows:
Windows 2000 Server
Dual CPU with Hyperthreading
2 GB RAM
SQL Server 2000 Standard Edition
The CPU shoots to 100% generally during peak hours. It would be grateful if
somebody could help me in setting up correct performance monitors and tweak
settings in terms of SQL Server with Hardware and Windows 2000.
Thanks
M Z.
Hi
My guess is 1 word: Indexes
Check that appropriate indexes are inplace. Look at
http://www.sql-server-performance.com for good information.
Regards
Mike
"Mitul Z." wrote:

> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful if
> somebody could help me in setting up correct performance monitors and tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.
|||How's your memory usage look? Got any other apps running on the same box?
Here's an MS Article on how to Troubleshoot SQL Server performance:
http://support.microsoft.com/kb/298475
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.
|||In addition to all the other great suggestions, run SQL Profiler and do a
default trace.. Just watch the counters for a while. Look for high cpu,
high duration, high reads/writes.. (in comparison to all the other request
being made). There is no hard and fast rule, but my personal goal is
that no query / update should take more than 1 second, optimally 100ms.
Obviously this is not always obtainable, but if you see queries taking 10 to
20 seconds, it's definately a place to start looking.
Bill
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M
|||Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://www.microsoft.com/mspress/ind.../book17654.htm
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.

Performance Tuning Tips for SQL Server 2000

Dear All:
Can somebody help me on Performance tuning the SQL Server 2000 as I have
just begin learning the SQL server 2000. The current server configuration
stands as follows:
Windows 2000 Server
Dual CPU with Hyperthreading
2 GB RAM
SQL Server 2000 Standard Edition
The CPU shoots to 100% generally during peak hours. It would be grateful if
somebody could help me in setting up correct performance monitors and tweak
settings in terms of SQL Server with Hardware and Windows 2000.
Thanks
M Z.Hi
My guess is 1 word: Indexes
Check that appropriate indexes are inplace. Look at
http://www.sql-server-performance.com for good information.
Regards
Mike
"Mitul Z." wrote:
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful if
> somebody could help me in setting up correct performance monitors and tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.|||How's your memory usage look? Got any other apps running on the same box?
Here's an MS Article on how to Troubleshoot SQL Server performance:
http://support.microsoft.com/kb/298475
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.|||In addition to all the other great suggestions, run SQL Profiler and do a
default trace.. Just watch the counters for a while. Look for high cpu,
high duration, high reads/writes.. (in comparison to all the other request
being made). There is no hard and fast rule, but my personal goal is
that no query / update should take more than 1 second, optimally 100ms.
Obviously this is not always obtainable, but if you see queries taking 10 to
20 seconds, it's definately a place to start looking.
Bill
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M|||Microsoft® SQL Server 2000 Performance Tuning Technical Reference
http://www.microsoft.com/mspress/india/books/book17654.htm
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with Hardware and Windows 2000.
> Thanks
> M Z.

Performance Tuning Tips for SQL Server 2000

Dear All:
Can somebody help me on Performance tuning the SQL Server 2000 as I have
just begin learning the SQL server 2000. The current server configuration
stands as follows:
Windows 2000 Server
Dual CPU with Hyperthreading
2 GB RAM
SQL Server 2000 Standard Edition
The CPU shoots to 100% generally during peak hours. It would be grateful if
somebody could help me in setting up correct performance monitors and tweak
settings in terms of SQL Server with hardware and Windows 2000.
Thanks
M Z.Hi
My guess is 1 word: Indexes
Check that appropriate indexes are inplace. Look at
http://www.sql-server-performance.com for good information.
Regards
Mike
"Mitul Z." wrote:

> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful i
f
> somebody could help me in setting up correct performance monitors and twea
k
> settings in terms of SQL Server with hardware and Windows 2000.
> Thanks
> M Z.|||How's your memory usage look? Got any other apps running on the same box?
Here's an MS Article on how to Troubleshoot SQL Server performance:
http://support.microsoft.com/kb/298475
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with hardware and Windows 2000.
> Thanks
> M Z.|||In addition to all the other great suggestions, run SQL Profiler and do a
default trace.. Just watch the counters for a while. Look for high cpu,
high duration, high reads/writes.. (in comparison to all the other request
being made). There is no hard and fast rule, but my personal goal is
that no query / update should take more than 1 second, optimally 100ms.
Obviously this is not always obtainable, but if you see queries taking 10 to
20 seconds, it's definately a place to start looking.
Bill
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with hardware and Windows 2000.
> Thanks
> M|||Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://www.microsoft.com/mspress/in...s/book17654.htm
"Mitul Z." <Mitul Z.@.discussions.microsoft.com> wrote in message
news:82B959A3-5E7C-4F7B-81C9-9D02726B7F79@.microsoft.com...
> Dear All:
> Can somebody help me on Performance tuning the SQL Server 2000 as I have
> just begin learning the SQL server 2000. The current server configuration
> stands as follows:
> Windows 2000 Server
> Dual CPU with Hyperthreading
> 2 GB RAM
> SQL Server 2000 Standard Edition
> The CPU shoots to 100% generally during peak hours. It would be grateful
> if
> somebody could help me in setting up correct performance monitors and
> tweak
> settings in terms of SQL Server with hardware and Windows 2000.
> Thanks
> M Z.

Performance Tuning SQL query in Trigger

i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in

CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.

please find the structure of CORP_CAGE table:

1.CORP_CAGE_SEQ_NO
2.RECEIVED_DATE
3.EMP_SEQ_NO

CORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following

query from application which updates 10,000 records.

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using

UPDATE FUNCTION.
But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum

records,this trigger is working fine.

Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS
BEGIN
IF UPDATE(EMP_SEQ_NO)
BEGIN
INSERT CORP_CAGE_LOG_HIST
(
CAGE_LOG_SEQ_NUM,
BEFORE_VALUE,
AFTER_VALUE,
ENTRY_USER,
FIELD_UPDATED
)
SELECT
i.CAGE_LOG_SEQ_NUM,
d.RECEIVED_DATE,
i.RECEIVED_DATE,
i.UPDATE_USER,
"EMP_SEQ_NO"
FROM
inserted i,
deleted d
WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM
END

END

please help me on this for performance tuning the below query.

I don't have the schema of your table, which in this case is critical. However, if this statement:

Code Snippet

UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111

is updating 10,000 records then your join is going to cause an update that is the cross product of 10,000 x 10,000 or 100,000,000 logical records. This cannot be right. Look at your trigger WHERE condition:

Code Snippet

WHERE
i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM

since you are updating ONLY for SEQ_NO = 111 and you are joining the INSERTED pseudo table -- with 10,000 records -- with the DELETE pseudo table -- also with 10,000 records -- and since all records of the DELETED pseudo and all records of the DELETED pseudo have the same SEQ_NO -- specifically 111 you end up with the cross product. You need to linclude the KEY information as part of the join condition.

sql

performance tuning on high volume server

I am looking to improve the performance of my sql server databases.

I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.

The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.

My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?

I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?

Hi Gavin. What type of replication scheme have you implemented? Transactional/merge? Peer-to-peer, read-only subscriber, queud/immediate updating subscriber, etc.? Where to start on the research would have a lot to do with what type of topology you are using.

How are you getting to the conclusion that replication is responsible for the slowdown?

|||

Hi - it is a transactional type replication.

Chad Boyd MSFT wrote:

How are you getting to the conclusion that replication is responsible for the slowdown?

When turing off replication, the performance was improved. It seems that the bandwidth between servers would explain this.

What can I tell you about the topology?

|||

Hi Gavin. Is it a bi-directional replication setup? i.e. are the subscribers set to replicate updates back to the publisher, or are the subscribers simply read-only? What type of link exists between the sites (T1, T3, partial T, etc.)?

From the general sounds of things, you don't have an extremely busy write server, so a decent link between the 2 sites sounds sufficient for what you have, which is why it would be surprising to hear that the bottleneck is the network bandwidth...of course, it most certainly could be depending on the types of transactions you are seeing, this is just me thinking out loud.

You mentioned when turning off replication that performance improved, do you mean that end-users received responses to queries faster? Or you noticed particular counters drop significantly? Or possibly blocking/locking issues disipated?

I'd be surprised if the link is the bottleneck, since when you say performance improved I'm going to assume you mean end-users started seeing faster response times to requests...if that's the case, it would seem that there is something occuring on the box itself that is slowing down the response times (of course, that could be the replication agent keeping a lock on something because it is waiting for a response from the subscriber across a slow link, but in transactional replication, that's not as common as with merge, where the agents are querying tables directly...in transactional replication, the log is read directly).

Anything you can post that explains what you are seeing in terms of what is showing you performance is improved? Counters, query response times, etc.?

|||

I am also working with large volumes of data average of 12million records per table and a total of 23million record.No cluster or Indexes, this is because the data is to bulk to change.While running queries i find that my application hangs even if i set the ODBC timeout to 0. Unlike you i working with a normal X86 2.86 GHZ and 504MB RAM.

Please advice

|||

thank you for all your replies and assistance so far.

Chad - to answer your questions the subscribers are all read-only and there is a T1 link between sites.

As I am a developer and not a database specialist I have decided that I need to bring in some outsourced consultancy. Before I do this I would like to do some research so that I can learn as much as possible I would like to be up to speed on this and have as much background knowledge as possible.

I think that the first thing that I should do is to measure the facts as much as possible. Could you please advise me as to what tools and applications I can utilise to gather statistical facts?

What can I learn from my log files? What monitoring tools can I install?

performance tuning on high volume server

I am looking to improve the performance of my sql server databases.

I currently have a dual location system, the database server setup is basically a quad xeon with 4gb at my office and a double xeon with 4gb at a remote webhosting location. There are separate application/web/intranet servers at each site. The two databases servers are replicated with the local server publishing to the remote server.

The relational database holds circa 26 million records, growing by a volume of 10,000 per day, there are approximately 50,000 queries performed per day.

My theory is that the replication of the two databases is causing a slowdown; despite fast network connections (averaging 200ms between servers) the replication seems to place a large load on the local server. Would it be sensible to replicate to a second local server and then replicate to the remote server, placing any burden on the second server?

I am planning to upgrade the local server to a high capacity 4+ cpu 64bit server, my problem is that although I have noticed a slow down in performance over time, I am unsure how to go about measuring and quantifying this in order to diagnose the bottlenecks and ensure that investing in a new server would be worthwhile. Where would one be best advised to start this project?

Hi Gavin. What type of replication scheme have you implemented? Transactional/merge? Peer-to-peer, read-only subscriber, queud/immediate updating subscriber, etc.? Where to start on the research would have a lot to do with what type of topology you are using.

How are you getting to the conclusion that replication is responsible for the slowdown?

|||

Hi - it is a transactional type replication.

Chad Boyd MSFT wrote:

How are you getting to the conclusion that replication is responsible for the slowdown?

When turing off replication, the performance was improved. It seems that the bandwidth between servers would explain this.

What can I tell you about the topology?

|||

Hi Gavin. Is it a bi-directional replication setup? i.e. are the subscribers set to replicate updates back to the publisher, or are the subscribers simply read-only? What type of link exists between the sites (T1, T3, partial T, etc.)?

From the general sounds of things, you don't have an extremely busy write server, so a decent link between the 2 sites sounds sufficient for what you have, which is why it would be surprising to hear that the bottleneck is the network bandwidth...of course, it most certainly could be depending on the types of transactions you are seeing, this is just me thinking out loud.

You mentioned when turning off replication that performance improved, do you mean that end-users received responses to queries faster? Or you noticed particular counters drop significantly? Or possibly blocking/locking issues disipated?

I'd be surprised if the link is the bottleneck, since when you say performance improved I'm going to assume you mean end-users started seeing faster response times to requests...if that's the case, it would seem that there is something occuring on the box itself that is slowing down the response times (of course, that could be the replication agent keeping a lock on something because it is waiting for a response from the subscriber across a slow link, but in transactional replication, that's not as common as with merge, where the agents are querying tables directly...in transactional replication, the log is read directly).

Anything you can post that explains what you are seeing in terms of what is showing you performance is improved? Counters, query response times, etc.?

|||

I am also working with large volumes of data average of 12million records per table and a total of 23million record.No cluster or Indexes, this is because the data is to bulk to change.While running queries i find that my application hangs even if i set the ODBC timeout to 0. Unlike you i working with a normal X86 2.86 GHZ and 504MB RAM.

Please advice

|||

thank you for all your replies and assistance so far.

Chad - to answer your questions the subscribers are all read-only and there is a T1 link between sites.

As I am a developer and not a database specialist I have decided that I need to bring in some outsourced consultancy. Before I do this I would like to do some research so that I can learn as much as possible I would like to be up to speed on this and have as much background knowledge as possible.

I think that the first thing that I should do is to measure the facts as much as possible. Could you please advise me as to what tools and applications I can utilise to gather statistical facts?

What can I learn from my log files? What monitoring tools can I install?

Performance Tuning methods

Hello everyone:
Can anyone list the general performance tuning methods? Thanks a lot.
ZYTRefer to http://www.sql-server-performance.com website for all tips and goodies on PERFORMANCE section.