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 question: Opteron Dual core vs Xeon

I can't find any reference off hand. But a 2.2GHz Opteron is about at par
with a 3.0GHZ Xeon because of the architectural differences. Operton has its
memory and L2 cache right 'on the processor' whereas Xeon has some overhead
with its southbridge/northbridge etc. So in your case, DL585 can give you
more processing room because you can go dual cores.
Also, you can actually get faster processors on DL585, approximately 2.4GHz
for dual cores and 2.8GHz for single cores.
But whether or not you choose DL585 or DL580 depends on the requirements and
growth forecast of your application. It's possible that DL580 may be
sufficient for your particular app.
Linchi
"Bastian" wrote:

> Hello,
> we are looking for new systems to run SQL20005 on Windows 2003 64-bit.. Ou
r
> supplier made a proposal of two machines.
> Number one:
> HP Proliant DL585 (R01) with 4x Opteron - 2.2 Ghz - 1 MB cache and 8 GB
> memory
> Number two:
> HP Proliant DL580 (G3) with 4x Xeon - 3 Ghz - 2 MB cache and 8 GB memory
>
> The advantage of box one is that it can hold up to eight cpu's, but my
> question is about the CPU power. What are the relative performance
> statistics between a slower (2.2 Ghz) dual core machine and a faster (3.0
> Ghz) single core Xeon machine and especially when running SQL2005.
> tia,
> Bastian
>
>Hello,
we are looking for new systems to run SQL20005 on Windows 2003 64-bit.. Our
supplier made a proposal of two machines.
Number one:
HP Proliant DL585 (R01) with 4x Opteron - 2.2 Ghz - 1 MB cache and 8 GB
memory
Number two:
HP Proliant DL580 (G3) with 4x Xeon - 3 Ghz - 2 MB cache and 8 GB memory
The advantage of box one is that it can hold up to eight cpu's, but my
question is about the CPU power. What are the relative performance
statistics between a slower (2.2 Ghz) dual core machine and a faster (3.0
Ghz) single core Xeon machine and especially when running SQL2005.
tia,
Bastian|||I can't find any reference off hand. But a 2.2GHz Opteron is about at par
with a 3.0GHZ Xeon because of the architectural differences. Operton has its
memory and L2 cache right 'on the processor' whereas Xeon has some overhead
with its southbridge/northbridge etc. So in your case, DL585 can give you
more processing room because you can go dual cores.
Also, you can actually get faster processors on DL585, approximately 2.4GHz
for dual cores and 2.8GHz for single cores.
But whether or not you choose DL585 or DL580 depends on the requirements and
growth forecast of your application. It's possible that DL580 may be
sufficient for your particular app.
Linchi
"Bastian" wrote:

> Hello,
> we are looking for new systems to run SQL20005 on Windows 2003 64-bit.. Ou
r
> supplier made a proposal of two machines.
> Number one:
> HP Proliant DL585 (R01) with 4x Opteron - 2.2 Ghz - 1 MB cache and 8 GB
> memory
> Number two:
> HP Proliant DL580 (G3) with 4x Xeon - 3 Ghz - 2 MB cache and 8 GB memory
>
> The advantage of box one is that it can hold up to eight cpu's, but my
> question is about the CPU power. What are the relative performance
> statistics between a slower (2.2 Ghz) dual core machine and a faster (3.0
> Ghz) single core Xeon machine and especially when running SQL2005.
> tia,
> Bastian
>
>|||FYI - (my $.02 )
My experience has been that a DL585 at 2.0Ghz performs slighter better
than a DL580 running 3.0Ghz processors(on Win2k adv & SQL2000 Ent). I
am a little surprised that the supplier is limiting your config to 8GB
as memory would have equal (or more) impact as processor speed to SQL
performance. (What size DB's?)
I think with X64/SQL2005 the AMD architecture will widen the
performance gap. For myself I would go with 2.2 dual-core (now at
2.6Ghz if you have the $$$) and 16GB in PC3200 RAM in 2GB modules
upgradeable to 32GB total.
You will pay the same licensing as the single core and you can disable
them at the SQL level if you want to see four processor performance
(slightly inflated as OS will use all cores).
-Kismif
Linchi Shea wrote:[vbcol=seagreen]
> I can't find any reference off hand. But a 2.2GHz Opteron is about at par
> with a 3.0GHZ Xeon because of the architectural differences. Operton has i
ts
> memory and L2 cache right 'on the processor' whereas Xeon has some overhea
d
> with its southbridge/northbridge etc. So in your case, DL585 can give you
> more processing room because you can go dual cores.
> Also, you can actually get faster processors on DL585, approximately 2.4GH
z
> for dual cores and 2.8GHz for single cores.
> But whether or not you choose DL585 or DL580 depends on the requirements a
nd
> growth forecast of your application. It's possible that DL580 may be
> sufficient for your particular app.
> Linchi
> "Bastian" wrote:
>|||Oh, forgot.
One correction both the DL585 & DL580 are 4-way servers.
Both can be purchased with dual core processors (e.g. 8 cores) neither
is a true 8-way server.
-Kismif
Kismif wrote:[vbcol=seagreen]
> FYI - (my $.02 )
> My experience has been that a DL585 at 2.0Ghz performs slighter better
> than a DL580 running 3.0Ghz processors(on Win2k adv & SQL2000 Ent). I
> am a little surprised that the supplier is limiting your config to 8GB
> as memory would have equal (or more) impact as processor speed to SQL
> performance. (What size DB's?)
> I think with X64/SQL2005 the AMD architecture will widen the
> performance gap. For myself I would go with 2.2 dual-core (now at
> 2.6Ghz if you have the $$$) and 16GB in PC3200 RAM in 2GB modules
> upgradeable to 32GB total.
> You will pay the same licensing as the single core and you can disable
> them at the SQL level if you want to see four processor performance
> (slightly inflated as OS will use all cores).
> -Kismif
> Linchi Shea wrote:|||FYI - (my $.02 )
My experience has been that a DL585 at 2.0Ghz performs slighter better
than a DL580 running 3.0Ghz processors(on Win2k adv & SQL2000 Ent). I
am a little surprised that the supplier is limiting your config to 8GB
as memory would have equal (or more) impact as processor speed to SQL
performance. (What size DB's?)
I think with X64/SQL2005 the AMD architecture will widen the
performance gap. For myself I would go with 2.2 dual-core (now at
2.6Ghz if you have the $$$) and 16GB in PC3200 RAM in 2GB modules
upgradeable to 32GB total.
You will pay the same licensing as the single core and you can disable
them at the SQL level if you want to see four processor performance
(slightly inflated as OS will use all cores).
-Kismif
Linchi Shea wrote:[vbcol=seagreen]
> I can't find any reference off hand. But a 2.2GHz Opteron is about at par
> with a 3.0GHZ Xeon because of the architectural differences. Operton has i
ts
> memory and L2 cache right 'on the processor' whereas Xeon has some overhea
d
> with its southbridge/northbridge etc. So in your case, DL585 can give you
> more processing room because you can go dual cores.
> Also, you can actually get faster processors on DL585, approximately 2.4GH
z
> for dual cores and 2.8GHz for single cores.
> But whether or not you choose DL585 or DL580 depends on the requirements a
nd
> growth forecast of your application. It's possible that DL580 may be
> sufficient for your particular app.
> Linchi
> "Bastian" wrote:
>|||Oh, forgot.
One correction both the DL585 & DL580 are 4-way servers.
Both can be purchased with dual core processors (e.g. 8 cores) neither
is a true 8-way server.
-Kismif
Kismif wrote:[vbcol=seagreen]
> FYI - (my $.02 )
> My experience has been that a DL585 at 2.0Ghz performs slighter better
> than a DL580 running 3.0Ghz processors(on Win2k adv & SQL2000 Ent). I
> am a little surprised that the supplier is limiting your config to 8GB
> as memory would have equal (or more) impact as processor speed to SQL
> performance. (What size DB's?)
> I think with X64/SQL2005 the AMD architecture will widen the
> performance gap. For myself I would go with 2.2 dual-core (now at
> 2.6Ghz if you have the $$$) and 16GB in PC3200 RAM in 2GB modules
> upgradeable to 32GB total.
> You will pay the same licensing as the single core and you can disable
> them at the SQL level if you want to see four processor performance
> (slightly inflated as OS will use all cores).
> -Kismif
> Linchi Shea wrote:

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>sql

Performance Problems - HELP, PLEASE

One of my SQL2000 sp3 servers has seems to be responding slower.
Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
on the C drive is the MASTER, there are no master files on the E drive.
One of the prime functions for this server is Report services.
I began looking around after one of the report writers mentioned that things
were responding slower then in the past, I run this query >
use master
select * From sysdatabases
I get this line for Master>
master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
00:00:00.000 0 80
e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
If I use EM and look at Properties for the Master DB it shows the data file
where it should be>
C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
I then ran
use master
dbcc checkdb ('master')
and there were no errors , the final line read like this>
"CHECKDB found 0 allocation errors and 0 consistency errors in database
'master'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Any and ALL recommendations and suggestions would be most appreciated!!!"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>
The location of the master database, as recorded in a table in the master
database is not necessarily correct. The location of the master database is
specified in the registry on the command line when starting SQL. Master, in
turn, stores the locations of all the other databses.
This is not your problem.
Davud|||> One of the prime functions for this server is Report services.
To expand on David's response, be aware that Reporting Services and SQL
Server will compete for the same resources when both are on the same server.
Large Reports can consume significant CPU and memory resources, contributing
to slowness of both SQL Server and RS.
You need to identify the immediate reason for the slowness (e.g. high CPU,
excessive paging) so that you can more easily identify and correct the
cause. Simply poking around for misconfigured items is like throwing darts
blindfolded.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
> One of my SQL2000 sp3 servers has seems to be responding slower.
> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual processors.
> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
> on the C drive is the MASTER, there are no master files on the E drive.
> One of the prime functions for this server is Report services.
> I began looking around after one of the report writers mentioned that
> things were responding slower then in the past, I run this query >
> use master
> select * From sysdatabases
> I get this line for Master>
> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
> 00:00:00.000 0 80
> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
> If I use EM and look at Properties for the Master DB it shows the data
> file where it should be>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
> I then ran
> use master
> dbcc checkdb ('master')
> and there were no errors , the final line read like this>
> "CHECKDB found 0 allocation errors and 0 consistency errors in database
> 'master'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Any and ALL recommendations and suggestions would be most appreciated!!!
>|||Thank you both, very much....
You are absolutle right about throwing darts... I have started researching
how to use performance monitor and SQL profiler to better pin point the
source of the problem. I will post a new request for best resources to
understand these 2 tools..
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eG2vRWAfGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> One of the prime functions for this server is Report services.
> To expand on David's response, be aware that Reporting Services and SQL
> Server will compete for the same resources when both are on the same
> server. Large Reports can consume significant CPU and memory resources,
> contributing to slowness of both SQL Server and RS.
> You need to identify the immediate reason for the slowness (e.g. high CPU,
> excessive paging) so that you can more easily identify and correct the
> cause. Simply poking around for misconfigured items is like throwing
> darts blindfolded.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "msnews.microsoft.com" <breichenbach@.istate.com> wrote in message
> news:u1JmUo4eGHA.2076@.TK2MSFTNGP04.phx.gbl...
>> One of my SQL2000 sp3 servers has seems to be responding slower.
>> Server is Win2000 SP4 running on IBM eServer xSeries 360, dual
>> processors.
>> There are 58 DBs on the E Drive, and 2 DBs on the C drive, 1 of those
>> on the C drive is the MASTER, there are no master files on the E drive.
>> One of the prime functions for this server is Report services.
>> I began looking around after one of the report writers mentioned that
>> things were responding slower then in the past, I run this query >
>> use master
>> select * From sysdatabases
>> I get this line for Master>
>> master 1 0x01 0 24 1090519040 2000-08-06 01:29:12.250 1900-01-01
>> 00:00:00.000 0 80
>> e:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf 539
>> If I use EM and look at Properties for the Master DB it shows the data
>> file where it should be>
>> C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf
>> I then ran
>> use master
>> dbcc checkdb ('master')
>> and there were no errors , the final line read like this>
>> "CHECKDB found 0 allocation errors and 0 consistency errors in database
>> 'master'.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator."
>> Any and ALL recommendations and suggestions would be most appreciated!!!
>>
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.
When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.
|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek
|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance problems

Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.When this happens, did you try running the query manually from Query
Analyzer and see if the execution plan is any different, compared to the one
when everything was okay? It could be that the statistics changed or not
up-to-date. Do you recall any data changes that correspond with the times of
bad performance?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
Hi.
We have a system with approximately 1000 users running on a dual Pentium
with hyperthreading and 2Gb memory. The server is ultra fast and is
performing great - most of the time. The problem is that sometimes a query
that usually takes 2 seconds to run, suddenly will take 40 seconds or more.
It's like the system clutters up and everything becomes very slow.
We have been running a profiler on the system, and found several stored
procedures that needed optimizing and have done so - but we are still
experiencing the same problem. We have looked at sp_lock and found that most
of the time there are about 200 locks but suddenly the number of locks will
increase to as much as 200.000 mostly on one table and for one spid.
This leads us to believe that we have a query, an application or some other
unknown that prevents sql server from escalating a row level lock to table
level.
How do we find out if this is the case - is profiler the only way to go or
are we missing something here? Any ideas or similar experiences?
Your help will be greatly appreciated.|||"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> This leads us to believe that we have a query, an application or some
> other unknown that prevents sql server from escalating a row level lock to
> table level.
I had this ince, by a simple DTS table export, got _millions_ of locks.
Defragmented the database in question, and it helped, operation done exactly
the same way suddenly went without any problems.
Seems like some hard-to-reproduce SQL Server problem. I monitor the lock
count closely since then, and defragmenting is my first resort in such a
case, but it never happened again since then.
Hope it helps.
Regards
Wojtek|||Hi
Locks are managed by memory. Have you checked how much memory SQL Server
consumes?
Also make sure that you don't have along running transactions. Have you
checked that your tables have propely defined indexes and moreover an
optimizer is able to use them when it creates an execution plan.
Try to identify by DBCC INPUTBUFFER (spid) what query is running by this
spid.
"Thomas Jespersen" <tje@.nospam.mentum.dk> wrote in message
news:uLzi1GObFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi.
> We have a system with approximately 1000 users running on a dual Pentium
> with hyperthreading and 2Gb memory. The server is ultra fast and is
> performing great - most of the time. The problem is that sometimes a query
> that usually takes 2 seconds to run, suddenly will take 40 seconds or
more.
> It's like the system clutters up and everything becomes very slow.
> We have been running a profiler on the system, and found several stored
> procedures that needed optimizing and have done so - but we are still
> experiencing the same problem. We have looked at sp_lock and found that
most
> of the time there are about 200 locks but suddenly the number of locks
will
> increase to as much as 200.000 mostly on one table and for one spid.
> This leads us to believe that we have a query, an application or some
other
> unknown that prevents sql server from escalating a row level lock to table
> level.
> How do we find out if this is the case - is profiler the only way to go or
> are we missing something here? Any ideas or similar experiences?
> Your help will be greatly appreciated.
>

Performance problem on copy of db

I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:

quote:

---

> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, a
re
> what slow things down. The good db has a "subtree cost" of 3.43 whereas t
he
> bad one has something like 250.

---

sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).

Performance problem on copy of db

I have a performance problem here I could really do with some help on.
I am running SQL Server 7 on Win2k on a Dual Xeon box.
I moved the database from a much older machine and had some initial problems
running a particular complex query because of parallelism. Essentially,
doing an sp_updatestats and using only 1 of the 2 CPUs for parallel
execution of queries solved these problems. Clearly, this was an issue with
the more powerful processors on the new box.
Anyhow, once the performance problem was solved I made a copy of the
database using Backup/Restore and then changes were made to the data in the
new database copy (essentially, the application that uses the db was rolled
out to a different group of users).
All was well when I was last involved in this project back in October.
Recently, however, the same performance problems reappeared following a
reboot. I managed today to solve the performance problems on the original
database by running sp_updatestats.
However, running sp_updatestats on the second database did not solve the
performance problems. I've tried making another copy of the well-performing
database and the copy performs fine. Not sure what I was trying to prove by
doing this, but I was running out of ideas!
I also did a copy of the badly performing db using backup and restore and,
surprise, surprise, this made no difference.
Now that several months have passed, the copy I made contains different data
to the original so I can't simply overwrite it.
Apart from running sp_updatestats, I'm at a bit of a loss in terms of what
else to try. To give you some idea of the magnitude of the problem, the
query in question takes 30 seconds to run on the "good" database and 30
minutes to run on the "bad" one.
Looking at the execution plan, SQL Server does it completely differently.
The good db involves lots of "nested loops/inner join" processes. The bad
db involves lots of "has match/inner join" processes and these, I think, are
what slow things down. The good db has a "subtree cost" of 3.43 whereas the
bad one has something like 250.
Any ideas appreciated.Paul Welsh wrote:
> Looking at the execution plan, SQL Server does it completely differently.
> The good db involves lots of "nested loops/inner join" processes. The bad
> db involves lots of "has match/inner join" processes and these, I think, are
> what slow things down. The good db has a "subtree cost" of 3.43 whereas the
> bad one has something like 250.
sounds like you could have different indexes on the two databases.
you should also read up on fragmentation (dbcc showcontig, dbreindex,
dbindexdefrag, and defragging heaps).