Performance hit on subscribers under merge replication

I have a setup where a single 1.5 million row table is published to 6
separate databases using merge replication. On 4 out of the 6 I have no
problem, but on 2 there seems to be an excessive performance hit on the
subscribing server's CPU when the distributor attempts to apply updates AND
the merge agents never complete running successfully.
Here are some more details:
The Publisher and Distributor are on same server (2x800MHz, very little load
on server)
All 6 subscriptions are Push subscription, with merge agent running once an
hour, there are no filters on the article
When there are changes to synchronize they are almost always Updates, and
thousands of rows will be updated at each subscriber within a few hours on a
Saturday night. Then there will be no activity all week so the 7 tables can
synchronize before the next Saturday.
Subscriber #1: on same LAN as publisher. Very fast to synchronize with
Publisher
Subscribers #2, 3 & 4: each on a different LANs from the Publisher with 256K
DSL connections to Publisher. Synchronize with Publisher within a few hours.
Subscribers #5 & 6: each on a different LANs from the Publisher with < 128K
DSL connections to Publisher. Never seem to synchronize with Publisher. The
merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They serve
another DB application and the users complain that this application has
become much slower since the replication was set up. I assume this is caused
by high CPU because the subscribing database and this application's
databases are separate (i.e. the slowdown is not caused by table locks being
held)
How can I minimize the hit on Subscribers #5 & 6 and ensure the updates are
delivered to the subscribing database?
I have not figured this problem out yet. To work around the performance hit
problem, I want to find a way to have the merge agent only run during the
night. I know how to schedule the agent to run between certain hours, but is
there a way to ensure the agent stops at 7AM if it is running at that time?
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:%23Lvtc$DeFHA.2288@.TK2MSFTNGP14.phx.gbl...
>I have a setup where a single 1.5 million row table is published to 6
>separate databases using merge replication. On 4 out of the 6 I have no
>problem, but on 2 there seems to be an excessive performance hit on the
>subscribing server's CPU when the distributor attempts to apply updates AND
>the merge agents never complete running successfully.
> Here are some more details:
> The Publisher and Distributor are on same server (2x800MHz, very little
> load on server)
> All 6 subscriptions are Push subscription, with merge agent running once
> an hour, there are no filters on the article
> When there are changes to synchronize they are almost always Updates, and
> thousands of rows will be updated at each subscriber within a few hours on
> a Saturday night. Then there will be no activity all week so the 7 tables
> can synchronize before the next Saturday.
> Subscriber #1: on same LAN as publisher. Very fast to synchronize with
> Publisher
> Subscribers #2, 3 & 4: each on a different LANs from the Publisher with
> 256K DSL connections to Publisher. Synchronize with Publisher within a few
> hours.
> Subscribers #5 & 6: each on a different LANs from the Publisher with <
> 128K DSL connections to Publisher. Never seem to synchronize with
> Publisher. The merge agents' histories both show a long list of messages
> saying "100 updates downloaded" then "The process could not deliver
> update(s) at the 'Subscriber'. ... The merge process timed out while
> executing a query. Reconfigure the QueryTimeout parameter and retry the
> operation."
> Subscribers #5 & 6 are not very fast boxes (500 MHz Pentium III). They
> serve another DB application and the users complain that this application
> has become much slower since the replication was set up. I assume this is
> caused by high CPU because the subscribing database and this application's
> databases are separate (i.e. the slowdown is not caused by table locks
> being held)
> How can I minimize the hit on Subscribers #5 & 6 and ensure the updates
> are delivered to the subscribing database?
>
|||Laurence,
you could have another job that calls sp_stop_job, and which runs at 7am. I
have something similar for a job load which couldn't conflict with business
hours. The problem is that if the main job isn't running, the sp_stop_job
will fail and give the fail icon in EM. So, I check the job status prior to
deciding whether to stop or not. The Proc I've created is below.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
OUTPUT)
/************************************************** **************************************
Description: Script to return the job state of the data load job so
if it is running we can stop it.
Returns: (None)
Author: Paul Ibison (xt 26163)
Date Created: 22/12/2004
Revisions:
************************************************** ***************************************/
AS
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname collate database_default
null,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
DROP TABLE #xp_results
GO
declare @.retstatus int
exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
@.retstatus output
if @.retstatus = 1 --executing
begin
exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
end
|||Paul,
Thanks very much, I will use this.
Do you have any suggestions about my other problem: merge agents failing to
deliver updates to subscribers that have slow connections to the publisher,
also causing a high load on the subscriber CPU at the same time? Are there
configuration options I can set for slow connections, or to reduce the load
on the CPU?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> you could have another job that calls sp_stop_job, and which runs at 7am.
> I have something similar for a job load which couldn't conflict with
> business hours. The problem is that if the main job isn't running, the
> sp_stop_job will fail and give the fail icon in EM. So, I check the job
> status prior to deciding whether to stop or not. The Proc I've created is
> below.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
> CREATE PROCEDURE spReturnJobState (@.JobID uniqueidentifier, @.JobState int
> OUTPUT)
> /************************************************** **************************************
> Description: Script to return the job state of the data load job so
> if it is running we can stop it.
> Returns: (None)
> Author: Paul Ibison (xt 26163)
> Date Created: 22/12/2004
> Revisions:
> ************************************************** ***************************************/
> AS
> CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
> last_run_date INT NOT NULL,
> last_run_time INT NOT NULL,
> next_run_date INT NOT NULL,
> next_run_time INT NOT NULL,
> next_run_schedule_id INT NOT NULL,
> requested_to_run INT NOT NULL, -- BOOL
> request_source INT NOT NULL,
> request_source_id sysname collate database_default
> null,
> running INT NOT NULL, -- BOOL
> current_step INT NOT NULL,
> current_retry_attempt INT NOT NULL,
> job_state INT NOT NULL)
> INSERT INTO #xp_results
> EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'dbo'
> SELECT @.JobState = job_state FROM #xp_results WHERE job_id = @.JobID
> DROP TABLE #xp_results
> GO
>
> declare @.retstatus int
> exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
> @.retstatus output
> if @.retstatus = 1 --executing
> begin
> exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
> end
>
|||Laurence,
what is the error when there is a failure?
Cheers,
Paul
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Paul,
> Thanks very much, I will use this.
> Do you have any suggestions about my other problem: merge agents failing
> to deliver updates to subscribers that have slow connections to the
> publisher, also causing a high load on the subscriber CPU at the same
> time? Are there configuration options I can set for slow connections, or
> to reduce the load on the CPU?
> Thanks
> Laurence
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uwGwsT8eFHA.3932@.TK2MSFTNGP12.phx.gbl...
>
|||The merge agents' histories both show a long list of messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ... The merge process timed out while executing a query.
Reconfigure the QueryTimeout parameter and retry the operation."
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ug9mi3BfFHA.3936@.TK2MSFTNGP14.phx.gbl...
> Laurence,
> what is the error when there is a failure?
> Cheers,
> Paul
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:OSUG9oBfFHA.1148@.TK2MSFTNGP12.phx.gbl...
>
|||OK - try creating a custom merge agent profile with a large value of
QueryTimeout and resynchronizing.
Cheers,
Paul Ibison
|||OK I am trying that out.
Question - like I said before, what I typically see in the agent history is
a long listof messages saying "100
updates downloaded" then "The process could not deliver update(s) at the
'Subscriber'. ..." When the agent runs again, do the updates that were
previously downloaded to the subscriber still exist on the subscriber (I
imagine them being in some "holding area" waiting to be applied to the
database) , or does the download start back at the beginning? In other
words, will all the updates eventually be downloaded (even if they are not
successfully applied to the database each time), or is the process always
going back to square one when it fails?
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eTdCnqCfFHA.2372@.TK2MSFTNGP14.phx.gbl...
> OK - try creating a custom merge agent profile with a large value of
> QueryTimeout and resynchronizing.
> Cheers,
> Paul Ibison
>
|||Laurence,
Merge downloads the changes in batches and these batches are groups of
records which are applied as a transaction. An interruption to a batch will
cause a rollback of that particular batch while previous ones have been
already committed. Resynchronization will continue with the same batch
afterwards at the start of the batch and then do all the subsequent batches.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||When I see "100 updates downloaded" in the merge agent history, is that one
batch (i.e. 100 changes)? From reading Books Online that seems to be the
case ("By default, the Merge Agent processes 100 generations in each batch
uploaded and downloaded between the Publisher and Subscriber. ")
Next question: when I look at a given session's history and see 20 messages
saying "100 updates downloaded" then a single message saying "The process
could not deliver update(s) at the 'Subscriber'. ..." (either because the
merge process timed out, or because the job automatically gets stopped at
7AM - thanks for telling me how to do that!) does that mean that all 20
batches have failed to be applied and will be redownloaded in the next
session? If so, is there a way to limit the number of batches downloaded in
a session? I am looking for any techniques that will result in some changes
being applied to the subscriber, however slowly! I am also trying out the
Query Timeout parameter that you mentioned a few messages ago.
Thanks
Laurence
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Or$gnsUfFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Laurence,
> Merge downloads the changes in batches and these batches are groups of
> records which are applied as a transaction. An interruption to a batch
> will cause a rollback of that particular batch while previous ones have
> been already committed. Resynchronization will continue with the same
> batch afterwards at the start of the batch and then do all the subsequent
> batches.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Performance different

I have 2 databases that are identical in schema and structure, one hold June
data and one hold July data. I ran the query that hits 4 tables. Tables on
both databases are identical including indexes and keys. It took 40 seconds
when I run under June database. When I run under July, it did not return
the result set and it been running more than 2 hours. I checked the number
of data on those 4 tables and they are basically the same. Is there a way
for me to know what is going on? I used Profiler but no help. The query is
single commit transaction. Please help. Thanks!Your most likely blocked. Run sp_who2 to see who is blocking you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> I have 2 databases that are identical in schema and structure, one hold
June
> data and one hold July data. I ran the query that hits 4 tables. Tables
on
> both databases are identical including indexes and keys. It took 40
seconds
> when I run under June database. When I run under July, it did not return
> the result set and it been running more than 2 hours. I checked the
number
> of data on those 4 tables and they are basically the same. Is there a way
> for me to know what is going on? I used Profiler but no help. The query
is
> single commit transaction. Please help. Thanks!
>|||I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
found was shared locks. This is for the data warehouse environment and we
only have select statments. Thanks for the recommendation.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> Your most likely blocked. Run sp_who2 to see who is blocking you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > I have 2 databases that are identical in schema and structure, one hold
> June
> > data and one hold July data. I ran the query that hits 4 tables.
Tables
> on
> > both databases are identical including indexes and keys. It took 40
> seconds
> > when I run under June database. When I run under July, it did not
return
> > the result set and it been running more than 2 hours. I checked the
> number
> > of data on those 4 tables and they are basically the same. Is there a
way
> > for me to know what is going on? I used Profiler but no help. The
query
> is
> > single commit transaction. Please help. Thanks!
> >
> >
>|||Is the estimated query plan the same for both? What is the status of the
spid while it is running? Is there activity going on (disk, cpu etc)?
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> found was shared locks. This is for the data warehouse environment and we
> only have select statments. Thanks for the recommendation.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > Your most likely blocked. Run sp_who2 to see who is blocking you.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > I have 2 databases that are identical in schema and structure, one
hold
> > June
> > > data and one hold July data. I ran the query that hits 4 tables.
> Tables
> > on
> > > both databases are identical including indexes and keys. It took 40
> > seconds
> > > when I run under June database. When I run under July, it did not
> return
> > > the result set and it been running more than 2 hours. I checked the
> > number
> > > of data on those 4 tables and they are basically the same. Is there a
> way
> > > for me to know what is going on? I used Profiler but no help. The
> query
> > is
> > > single commit transaction. Please help. Thanks!
> > >
> > >
> >
> >
>|||I was not able to see the execution plan for the slow one because it didn't
stop. But the query structure, tables, indexes are identical. The SPID for
both are SELECT and using parallelism. The CPU for both are spiking, disk
write/sec, page write/sec, data map hits, and lazy write pages/sec are at
constant flat lines.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Is the estimated query plan the same for both? What is the status of the
> spid while it is running? Is there activity going on (disk, cpu etc)?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks I
> > found was shared locks. This is for the data warehouse environment and
we
> > only have select statments. Thanks for the recommendation.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > I have 2 databases that are identical in schema and structure, one
> hold
> > > June
> > > > data and one hold July data. I ran the query that hits 4 tables.
> > Tables
> > > on
> > > > both databases are identical including indexes and keys. It took 40
> > > seconds
> > > > when I run under June database. When I run under July, it did not
> > return
> > > > the result set and it been running more than 2 hours. I checked the
> > > number
> > > > of data on those 4 tables and they are basically the same. Is there
a
> > way
> > > > for me to know what is going on? I used Profiler but no help. The
> > query
> > > is
> > > > single commit transaction. Please help. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>|||Kevin,
You can do an Estimated query plan without actually running the query. In
Query Analyzer you can highlight the query and press Ctrl + L to see it. By
"all flat lines" do you mean maxed out? If so then your are most likely
doing a full table scan. The estimated query plan will tell you.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <kevin@.noemail.com> wrote in message
news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> I was not able to see the execution plan for the slow one because it
didn't
> stop. But the query structure, tables, indexes are identical. The SPID
for
> both are SELECT and using parallelism. The CPU for both are spiking, disk
> write/sec, page write/sec, data map hits, and lazy write pages/sec are at
> constant flat lines.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > Is the estimated query plan the same for both? What is the status of
the
> > spid while it is running? Is there activity going on (disk, cpu etc)?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Kevin" <kevin@.noemail.com> wrote in message
> > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > I ran sp_lock & sp_who2 and found no exclusive locks. The only locks
I
> > > found was shared locks. This is for the data warehouse environment
and
> we
> > > only have select statments. Thanks for the recommendation.
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > >
> > > > --
> > > >
> > > > Andrew J. Kelly
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > I have 2 databases that are identical in schema and structure, one
> > hold
> > > > June
> > > > > data and one hold July data. I ran the query that hits 4 tables.
> > > Tables
> > > > on
> > > > > both databases are identical including indexes and keys. It took
40
> > > > seconds
> > > > > when I run under June database. When I run under July, it did not
> > > return
> > > > > the result set and it been running more than 2 hours. I checked
the
> > > > number
> > > > > of data on those 4 tables and they are basically the same. Is
there
> a
> > > way
> > > > > for me to know what is going on? I used Profiler but no help.
The
> > > query
> > > > is
> > > > > single commit transaction. Please help. Thanks!
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thank you for your suggestion. After figuring what the Estimated query plan
does, it is caused by recursive loops. One month return 2,500 rows while
the other one return 15,000. These recursively 3 times and that make the
query run on 2nd database longer. I was able fix the query so it run
faster.
THANK YOU Andrew for your help !!!!!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ufQspyXrDHA.2964@.tk2msftngp13.phx.gbl...
> Kevin,
> You can do an Estimated query plan without actually running the query. In
> Query Analyzer you can highlight the query and press Ctrl + L to see it.
By
> "all flat lines" do you mean maxed out? If so then your are most likely
> doing a full table scan. The estimated query plan will tell you.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Kevin" <kevin@.noemail.com> wrote in message
> news:OHKakiXrDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > I was not able to see the execution plan for the slow one because it
> didn't
> > stop. But the query structure, tables, indexes are identical. The SPID
> for
> > both are SELECT and using parallelism. The CPU for both are spiking,
disk
> > write/sec, page write/sec, data map hits, and lazy write pages/sec are
at
> > constant flat lines.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eIDOVpUrDHA.4004@.TK2MSFTNGP11.phx.gbl...
> > > Is the estimated query plan the same for both? What is the status of
> the
> > > spid while it is running? Is there activity going on (disk, cpu etc)?
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Kevin" <kevin@.noemail.com> wrote in message
> > > news:OkdUVCUrDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > > I ran sp_lock & sp_who2 and found no exclusive locks. The only
locks
> I
> > > > found was shared locks. This is for the data warehouse environment
> and
> > we
> > > > only have select statments. Thanks for the recommendation.
> > > >
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:O1RyAzJrDHA.3320@.tk2msftngp13.phx.gbl...
> > > > > Your most likely blocked. Run sp_who2 to see who is blocking you.
> > > > >
> > > > > --
> > > > >
> > > > > Andrew J. Kelly
> > > > > SQL Server MVP
> > > > >
> > > > >
> > > > > "Kevin" <kevin@.noemail.com> wrote in message
> > > > > news:eDLkUUJrDHA.2632@.TK2MSFTNGP09.phx.gbl...
> > > > > > I have 2 databases that are identical in schema and structure,
one
> > > hold
> > > > > June
> > > > > > data and one hold July data. I ran the query that hits 4
tables.
> > > > Tables
> > > > > on
> > > > > > both databases are identical including indexes and keys. It
took
> 40
> > > > > seconds
> > > > > > when I run under June database. When I run under July, it did
not
> > > > return
> > > > > > the result set and it been running more than 2 hours. I checked
> the
> > > > > number
> > > > > > of data on those 4 tables and they are basically the same. Is
> there
> > a
> > > > way
> > > > > > for me to know what is going on? I used Profiler but no help.
> The
> > > > query
> > > > > is
> > > > > > single commit transaction. Please help. Thanks!
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

performance degradation

I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
You have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server_...nce_tuning.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you
|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you

performance degradation

I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank youYou have to identify what exactly is resulting in high CPU utilization.
I would use Profiler to track down the long running and most CPU intensive
stored procedures and queries, and tune them.
Often, the lack of useful indexes results in performance problems.
You will probably find the following link helpful:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
Microsoft SQL Server 2000 Performance Tuning Technical Reference
http://vyaskn.tripod.com/sql_server...ance_tuning.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"RobinMC" <RobinMC@.discussions.microsoft.com> wrote in message
news:BD5E933B-33C3-4A57-8FEE-C53D3505E1D3@.microsoft.com...
I am running a windows 2003 server with sql 2000 sp3.
I have 2 80GB databases. I also have an 8 way processor.
cpu is consistently above 80%.
what can i do to gain some performance improvements?
Thank you|||RobinMC,
Have a look at the articles at www.sql-server-performance.com. Your
question is very general, and as such cannot be answered satisfactorily
here.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
RobinMC wrote:
> I am running a windows 2003 server with sql 2000 sp3.
> I have 2 80GB databases. I also have an 8 way processor.
> cpu is consistently above 80%.
> what can i do to gain some performance improvements?
> Thank you

Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!
Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the 5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
[vbcol=seagreen]
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
Anith
|||> On complex queries, esp. ones that involve larger underlying datasets, the performance could be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would be determined by file
configuration. I.e., one could use filegroups so that the file placement over the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has the same information
and options whether or not we go across database boundary - assuming in the same instance of
course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>
> Unless he tests out his queries and compare them, we cannot be sure. On complex queries, esp. ones
> that involve larger underlying datasets, the performance could be very different due to
> significant changes in disk I/O.
> --
> Anith
>
|||>> But that wouldn't be specific to inter-database traffic, right? That[vbcol=seagreen]
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
[vbcol=seagreen]
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
Anith
|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)

> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the same information
regardless of whether the tables involved are in the same database or are from several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the optimizer does not
factor disk layout or characteristica when creating an execution plan. Perhaps I should have said:
You can define a database using file groups so you get the same structure as if you had that set of
tables spread over several databases. (Assuming you don't introduce any table partitioning when
spreading over several databases.) If you do end up with a similar file placement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if, for
instance, a table is partitioned over different filegroups compared to the same filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tables in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
> Can we have two databases placed on the same filegroup? Otherwise, it would have to be distinct
> physical file access. You are right in that one could have the underlying files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is why he'll have to test
> out his queries and compare them.
>
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>
|||>> Can we have two databases placed on the same filegroup?[vbcol=seagreen]
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
[vbcol=seagreen]
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
[vbcol=seagreen]
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
[vbcol=seagreen]
... which is all the more reason for the OP to test out his queries and see
it for himself.
[vbcol=seagreen]
Indeed
Anith
|||Thanks for the comments, Anith. Seems we are in agreement here, even if it took a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:

> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of 7.0, share the same
file over several databases. One file is owned by a database (a true subset of the database).
The old architecture was different, where you first created a database device (the file) and then
allocated storage ("segment", similar to a file group) from that file for the database. Thus, you
could end up with two databases using storage from the same file. This model doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW devices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architecture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
>
> I was just emphasising on the fact that physical I/O could be a contributing factor to performance
> differences. If the databases are on distinct files ( distributed or otherwise ) then it can
> contribute to the overall performance of queries when the underlying implementation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>
> Agreed. On the same token if the underlying file placement of the files are different, the
> performance could be different as well.
>
> .. which is all the more reason for the OP to test out his queries and see it for himself.
>
> Indeed
> --
> Anith
>

Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the 5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
>> There is no performance penalty for cross-database queries, to my
>> knowledge.
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
--
Anith|||> On complex queries, esp. ones that involve larger underlying datasets, the performance could be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would be determined by file
configuration. I.e., one could use filegroups so that the file placement over the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has the same information
and options whether or not we go across database boundary - assuming in the same instance of
course).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>> There is no performance penalty for cross-database queries, to my knowledge.
> Unless he tests out his queries and compare them, we cannot be sure. On complex queries, esp. ones
> that involve larger underlying datasets, the performance could be very different due to
> significant changes in disk I/O.
> --
> Anith
>|||>> But that wouldn't be specific to inter-database traffic, right? That
>> would be determined by file configuration. I.e., one could use filegroups
>> so that the file placement over the tables is the same as when using
>> several databases and we get the same result.
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
>> ..but my point is that the optimizer has the same information and options
>> whether or not we go across database boundary - assuming in the same
>> instance of course
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
--
Anith|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)
>> ..but my point is that the optimizer has the same information and options whether or not we go
>> across database boundary - assuming in the same instance of course
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the same information
regardless of whether the tables involved are in the same database or are from several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the optimizer does not
factor disk layout or characteristica when creating an execution plan. Perhaps I should have said:
You can define a database using file groups so you get the same structure as if you had that set of
tables spread over several databases. (Assuming you don't introduce any table partitioning when
spreading over several databases.) If you do end up with a similar file placement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if, for
instance, a table is partitioned over different filegroups compared to the same filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tables in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
>> But that wouldn't be specific to inter-database traffic, right? That would be determined by file
>> configuration. I.e., one could use filegroups so that the file placement over the tables is the
>> same as when using several databases and we get the same result.
> Can we have two databases placed on the same filegroup? Otherwise, it would have to be distinct
> physical file access. You are right in that one could have the underlying files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is why he'll have to test
> out his queries and compare them.
>> ..but my point is that the optimizer has the same information and options whether or not we go
>> across database boundary - assuming in the same instance of course
> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all potential
> physical I/O information in execution plans, esp. if the multiple files are distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>|||>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
>> ..but my point is that the optimizer has the same information and
>> options whether or not we go across database boundary - assuming in
>> the same instance of course
>> Sure, as far as the query optimizations go, agreed. But it cannot
>> possibly factor in all potential physical I/O information in execution
>> plans, esp. if the multiple files are distributed over the network or
>> even on external drives, or am I wrong here?
>> Hmm, you confuse me a bit here.
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
>> My original point was the optimizer has the same information regardless
>> of whether the tables involved are in the same database or are from
>> several databases. At least, that is how I believe it work. Also, to the
>> best of my knowledge, the optimizer does not factor disk layout or
>> characteristica when creating an execution plan.
>> Perhaps I should have said:
>> You can define a database using file groups so you get the same structure
>> as if you had that set of tables spread over several databases. (Assuming
>> you don't introduce any table partitioning when spreading over several
>> databases.) If you do end up with a similar file placement of the tables,
>> the optimizer should produce similar plans.
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
>> Above is speculation to some degree. Who knows, perhaps the optimizer
>> will take into account if, for instance, a table is partitioned over
>> different filegroups compared to the same filegroup (just an example)?
.. which is all the more reason for the OP to test out his queries and see
it for himself.
>> However, there are more important factors, IMO. Having a related set of
>> tables in the same database has many advantages, IMO. Backup is only one
>> of them, IMO a major one.
Indeed
--
Anith|||Thanks for the comments, Anith. Seems we are in agreement here, even if it took a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:
>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of 7.0, share the same
file over several databases. One file is owned by a database (a true subset of the database).
The old architecture was different, where you first created a database device (the file) and then
allocated storage ("segment", similar to a file group) from that file for the database. Thus, you
could end up with two databases using storage from the same file. This model doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW devices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architecture.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
>> Can we have two databases placed on the same filegroup?
>> Not unless you go back to 6.5 ;-)
> Somebody kill me.......! Actually I meant a single file, which I assume is not possible. ( or is
> it? )
>>> ..but my point is that the optimizer has the same information and options whether or not we
>>> go across database boundary - assuming in the same instance of course
>> Sure, as far as the query optimizations go, agreed. But it cannot possibly factor in all
>> potential physical I/O information in execution plans, esp. if the multiple files are
>> distributed over the network or even on external drives, or am I wrong here?
>> Hmm, you confuse me a bit here.
> I was just emphasising on the fact that physical I/O could be a contributing factor to performance
> differences. If the databases are on distinct files ( distributed or otherwise ) then it can
> contribute to the overall performance of queries when the underlying implementation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>> My original point was the optimizer has the same information regardless of whether the tables
>> involved are in the same database or are from several databases. At least, that is how I
>> believe it work. Also, to the best of my knowledge, the optimizer does not factor disk layout or
>> characteristica when creating an execution plan.
>> Perhaps I should have said:
>> You can define a database using file groups so you get the same structure as if you had that set
>> of tables spread over several databases. (Assuming you don't introduce any table partitioning
>> when spreading over several databases.) If you do end up with a similar file placement of the
>> tables, the optimizer should produce similar plans.
> Agreed. On the same token if the underlying file placement of the files are different, the
> performance could be different as well.
>> Above is speculation to some degree. Who knows, perhaps the optimizer will take into account if,
>> for instance, a table is partitioned over different filegroups compared to the same filegroup
>> (just an example)?
> .. which is all the more reason for the OP to test out his queries and see it for himself.
>> However, there are more important factors, IMO. Having a related set of tables in the same
>> database has many advantages, IMO. Backup is only one of them, IMO a major one.
> Indeed
> --
> Anith
>

Performance and inter-database joins

We have ALOT of procs with joins of many tables spanning 2-4 databases at
times. Many of these procs are hit HARD during our busiest times. This
seems to me that it would be not the best way to do things. I understand
that sometimes there may be needs to go to other db's for data but shouldn't
that be an exception and not the normal rule?
Myself I'm pretty convinced that we don't have enough reasons to have the 5
different databases we have. They would all fit nicely into one db and
still only be 5-6GB...there are heavy dependencies between any combination
of these databases which seems to tell me they really should be one...
Any thoughts here? Am I concerned about performance unnecessarily? Our
server is running fine but our user base is growing consistently and I'd
like to keep it that way.
Thanks!Tim Greenwood wrote:
> We have ALOT of procs with joins of many tables spanning 2-4 databases at
> times. Many of these procs are hit HARD during our busiest times. This
> seems to me that it would be not the best way to do things. I understand
> that sometimes there may be needs to go to other db's for data but shouldn
't
> that be an exception and not the normal rule?
> Myself I'm pretty convinced that we don't have enough reasons to have the
5
> different databases we have. They would all fit nicely into one db and
> still only be 5-6GB...there are heavy dependencies between any combination
> of these databases which seems to tell me they really should be one...
> Any thoughts here? Am I concerned about performance unnecessarily? Our
> server is running fine but our user base is growing consistently and I'd
> like to keep it that way.
> Thanks!
>
There is no performance penalty for cross-database queries, to my
knowledge. Cross-SERVER queries, on the other hand, can suffer
significant penalties.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
[vbcol=seagreen]
Unless he tests out his queries and compare them, we cannot be sure. On
complex queries, esp. ones that involve larger underlying datasets, the
performance could be very different due to significant changes in disk I/O.
Anith|||> On complex queries, esp. ones that involve larger underlying datasets, the performance cou
ld be
> very different due to significant changes in disk I/O.
But that wouldn't be specific to inter-database traffic, right? That would b
e determined by file
configuration. I.e., one could use filegroups so that the file placement ove
r the tables is the same
as when using several databases and we get the same result.
(I realize this is a bit theoretical, but my point is that the optimizer has
the same information
and options whether or not we go across database boundary - assuming in the
same instance of
course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%233O3hvm3GHA.2152@.TK2MSFTNGP06.phx.gbl...
> Tracy,
>
> Unless he tests out his queries and compare them, we cannot be sure. On co
mplex queries, esp. ones
> that involve larger underlying datasets, the performance could be very dif
ferent due to
> significant changes in disk I/O.
> --
> Anith
>|||>> But that wouldn't be specific to inter-database traffic, right? That[vbcol=seagreen]
Can we have two databases placed on the same filegroup? Otherwise, it would
have to be distinct physical file access. You are right in that one could
have the underlying files/filesgroup spread out similarly, but then it is
hard to prove one way or the other which is why he'll have to test out his
queries and compare them.
[vbcol=seagreen]
Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential physical I/O information in execution plans, esp. if
the multiple files are distributed over the network or even on external
drives, or am I wrong here?
Anith|||> Can we have two databases placed on the same filegroup?
Not unless you go back to 6.5 ;-)

> Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential
> physical I/O information in execution plans, esp. if the multiple files ar
e distributed over the
> network or even on external drives, or am I wrong here?
Hmm, you confuse me a bit here. My original point was the optimizer has the
same information
regardless of whether the tables involved are in the same database or are fr
om several databases. At
least, that is how I believe it work. Also, to the best of my knowledge, the
optimizer does not
factor disk layout or characteristica when creating an execution plan. Perha
ps I should have said:
You can define a database using file groups so you get the same structure as
if you had that set of
tables spread over several databases. (Assuming you don't introduce any tabl
e partitioning when
spreading over several databases.) If you do end up with a similar file plac
ement of the tables, the
optimizer should produce similar plans.
Above is speculation to some degree. Who knows, perhaps the optimizer will t
ake into account if, for
instance, a table is partitioned over different filegroups compared to the s
ame filegroup (just an
example)?
However, there are more important factors, IMO. Having a related set of tabl
es in the same database
has many advantages, IMO. Backup is only one of them, IMO a major one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:u41w4$X4GHA.696@.TK2MSFTNGP06.phx.gbl...
> Can we have two databases placed on the same filegroup? Otherwise, it woul
d have to be distinct
> physical file access. You are right in that one could have the underlying
files/filesgroup spread
> out similarly, but then it is hard to prove one way or the other which is
why he'll have to test
> out his queries and compare them.
>
> Sure, as far as the query optimizations go, agreed. But it cannot possibly
factor in all potential
> physical I/O information in execution plans, esp. if the multiple files ar
e distributed over the
> network or even on external drives, or am I wrong here?
> --
> Anith
>|||>> Can we have two databases placed on the same filegroup?[vbcol=seagreen]
Somebody kill me.......! Actually I meant a single file, which I assume is
not possible. ( or is it? )
[vbcol=seagreen]
I was just emphasising on the fact that physical I/O could be a contributing
factor to performance differences. If the databases are on distinct files
( distributed or otherwise ) then it can contribute to the overall
performance of queries when the underlying implementation access distinct
physical files as opposed to a single one.
However I do appreciate your point. It can be the other around as well.
[vbcol=seagreen]
Agreed. On the same token if the underlying file placement of the files are
different, the performance could be different as well.
[vbcol=seagreen]
.. which is all the more reason for the OP to test out his queries and see
it for himself.
[vbcol=seagreen]
Indeed
Anith|||Thanks for the comments, Anith. Seems we are in agreement here, even if it t
ook a couple of posts to
determine... :-)
On more thing, to answer one of your outstanding questions:

> Somebody kill me.......! Actually I meant a single file, which I assume
is not possible. ( or is
> it? )
To be honest, I read your original question as "file". No, you cannot, as of
7.0, share the same
file over several databases. One file is owned by a database (a true subset
of the database).
The old architecture was different, where you first created a database devic
e (the file) and then
allocated storage ("segment", similar to a file group) from that file for th
e database. Thus, you
could end up with two databases using storage from the same file. This model
doesn't really add
anything useful in the PC world, especially since we don't tend to use RAW d
evices. So, I'm glad MS
made the storage architecture much cleaner and simpler in the new architectu
re.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OWVx4mY4GHA.292@.TK2MSFTNGP02.phx.gbl...
> Somebody kill me.......! Actually I meant a single file, which I assume
is not possible. ( or is
> it? )
>
> I was just emphasising on the fact that physical I/O could be a contributi
ng factor to performance
> differences. If the databases are on distinct files ( distributed or other
wise ) then it can
> contribute to the overall performance of queries when the underlying imple
mentation access
> distinct physical files as opposed to a single one.
> However I do appreciate your point. It can be the other around as well.
>
> Agreed. On the same token if the underlying file placement of the files ar
e different, the
> performance could be different as well.
>
> .. which is all the more reason for the OP to test out his queries and see
it for himself.
>
> Indeed
> --
> Anith
>

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !
Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:

> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>
|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ
|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com
|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.

> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application whic
h
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the serve
r
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:

> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.

> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

Performance and Detection

I have a sql server with 2 databases and being used from 8 AM- 5PM. The
Server has a Memory of 512 K. There are different users using different
applications like access, Crystal reports and other general application which
accesses these database in Production enviornment.
Problem:
Sometime we experience slowdowns and then after about 5 MIN
everything starts moving again. Sometimes I have to disconnect users from
the SQL SERVER to make sure everything is working again. Reboot of the server
also helps and will work as normal. But time and again we experience
slowdowns.
Number of users accessing this server is about 35. Not sure how do I fix
this problem. How do I know who is the culprit and whats locking it up.
Sp_WHO and SP_lock2 doesn't tell anything.
Please advice.
Jazzman !Sounds like you don't have enough memory for everything you are doing. I
take it there are other apps running on that server than SQL Server? Try
limiting the max memory to say 350MB and see if that makes a difference.
--
Andrew J. Kelly SQL MVP
"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
>I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
> which
> accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
> server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !|||No other apps on the server except SQL SERVER.
"Andrew J. Kelly" wrote:
> Sounds like you don't have enough memory for everything you are doing. I
> take it there are other apps running on that server than SQL Server? Try
> limiting the max memory to say 350MB and see if that makes a difference.
> --
> Andrew J. Kelly SQL MVP
>
> "Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
> news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> >I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> > Server has a Memory of 512 K. There are different users using different
> > applications like access, Crystal reports and other general application
> > which
> > accesses these database in Production enviornment.
> >
> > Problem:
> > Sometime we experience slowdowns and then after about 5 MIN
> > everything starts moving again. Sometimes I have to disconnect users from
> > the SQL SERVER to make sure everything is working again. Reboot of the
> > server
> > also helps and will work as normal. But time and again we experience
> > slowdowns.
> > Number of users accessing this server is about 35. Not sure how do I fix
> > this problem. How do I know who is the culprit and whats locking it up.
> > Sp_WHO and SP_lock2 doesn't tell anything.
> >
> > Please advice.
> >
> > Jazzman !
>
>|||It could be lots of things. Transaction Log Backups could be kicking in and
slowing things down, Auto_Update Statistics, etc,etc, etc.
When your server slows, WHAT is the bottleneck ?
The usual suspects:
RAM
CPU
DISK IO
My first guess is usually IO.
GAJ|||Jazzman wrote:
> I have a sql server with 2 databases and being used from 8 AM- 5PM.
> The Server has a Memory of 512 K. There are different users using
> different applications like access, Crystal reports and other general
> application which accesses these database in Production enviornment.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users
> from the SQL SERVER to make sure everything is working again. Reboot
> of the server also helps and will work as normal. But time and again
> we experience slowdowns.
> Number of users accessing this server is about 35. Not sure how do I
> fix this problem. How do I know who is the culprit and whats locking
> it up. Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !
Try using Profiler to detect high duration and high CPU executions. You
can trap SQL:BatchCompleted and RPC:Completed or SQL:StmtCompleted and
RPC:Completed for a little more detail.
If you can identify the processes eatiing your server resources
(assuming that is the problem) at least you can then address it.
David Gugick
Imceda Software
www.imceda.com|||"Jazzman" <Jazzman@.discussions.microsoft.com> wrote in message
news:B5E399DE-4565-4EC9-931E-EC5805A05441@.microsoft.com...
> I have a sql server with 2 databases and being used from 8 AM- 5PM. The
> Server has a Memory of 512 K. There are different users using different
> applications like access, Crystal reports and other general application
which
> accesses these database in Production enviornment.
At the very least can I recommend a memory upgrade. It's cheap and 512M (I
assume you mean Meg and not K) aint' much.
> Problem:
> Sometime we experience slowdowns and then after about 5 MIN
> everything starts moving again. Sometimes I have to disconnect users from
> the SQL SERVER to make sure everything is working again. Reboot of the
server
> also helps and will work as normal. But time and again we experience
> slowdowns.
> Number of users accessing this server is about 35. Not sure how do I fix
> this problem. How do I know who is the culprit and whats locking it up.
> Sp_WHO and SP_lock2 doesn't tell anything.
> Please advice.
> Jazzman !

Performance

Is there any difference between:
executing 2 queries in the same database at the same time
or
Executing 2 queries in 2 different databases at the same time
The same conditions for the 2 databases (same query, tables, indexes).
The concrete question is: is there any performance advantages in spliting
one database into 2.
--
Thanks
Regards.
JosemaYes there's a difference but there are a lot of variables to consider and
the answer is almost never simple. As a start on some of the things to
consider:
Advantages of separate databases:
Different lock spaces so no contention as long as the query stays within
a database
If the different databases are on different drives there may be some IO
advantages
Different logs so possibility for parallelism in log writes
Disadvantages of separate databases:
Any queries that span the databases are distributed queries and there
are significant performance penalties for distributed queries.
Each database requires a certain amount of memory for meta-data, query
execution space, etc. so two databases will require more memory than the
same data in a single database.
If the data isn't completely disjoint - there is common data that both
databases need, the data must be replicated or distributed queries are
required to obtain it - both of which hurt performance.
Generally there is more work required to maintain multiple databases.
So the answer is that splitting the data into two databases will make some
thing faster and some thing slower so the net change depends on how
independent the data is and what kind of queries your application does.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Josema" <Jestrade@.ocu.org> wrote in message
news:3CE8F364-6DE6-4BB1-8741-2660D62CB714@.microsoft.com...
> Is there any difference between:
> executing 2 queries in the same database at the same time
> or
> Executing 2 queries in 2 different databases at the same time
> The same conditions for the 2 databases (same query, tables, indexes).
> The concrete question is: is there any performance advantages in spliting
> one database into 2.
> --
> Thanks
> Regards.
> Josema

Performance

Is there any difference between:
executing 2 queries in the same database at the same time
or
Executing 2 queries in 2 different databases at the same time
The same conditions for the 2 databases (same query, tables, indexes).
The concrete question is: is there any performance advantages in spliting
one database into 2.
Thanks
Regards.
Josema"Josema" <Jestrade@.ocu.org> wrote in message
news:FA30EE5C-0959-456E-BDA6-080382700A51@.microsoft.com...
> Is there any difference between:
> executing 2 queries in the same database at the same time
> or
> Executing 2 queries in 2 different databases at the same time
> The same conditions for the 2 databases (same query, tables, indexes).
> The concrete question is: is there any performance advantages in spliting
> one database into 2.
"It depends."
On to many factors to really give a complete answer w/o knowing your schema,
your hardware, etc.
For example, if the dataset in DB 1 is cached, two queries against it will
be as fast as your memory can provide them. If you have it in two
databases, the chances both datasets will fit in memory drops which means
you increase your likelihood that you have to hit the disk which will
dramactically slow things down.
On the other hand, if both queries require a full table scan and you're
flooding your disk channel, putting the two databases on completely
different disk channels may improve throughput.
Generally though I'd say splitting a database into two pieces strictly for
performance reasons isn't going to help.

>
> --
> Thanks
> Regards.
> Josema

Performance

Is there any difference between:
executing 2 queries in the same database at the same time
or
Executing 2 queries in 2 different databases at the same time
The same conditions for the 2 databases (same query, tables, indexes).
The concrete question is: is there any performance advantages in spliting
one database into 2.
--
Thanks
Regards.
Josema"Josema" <Jestrade@.ocu.org> wrote in message
news:FA30EE5C-0959-456E-BDA6-080382700A51@.microsoft.com...
> Is there any difference between:
> executing 2 queries in the same database at the same time
> or
> Executing 2 queries in 2 different databases at the same time
> The same conditions for the 2 databases (same query, tables, indexes).
> The concrete question is: is there any performance advantages in spliting
> one database into 2.
"It depends."
On to many factors to really give a complete answer w/o knowing your schema,
your hardware, etc.
For example, if the dataset in DB 1 is cached, two queries against it will
be as fast as your memory can provide them. If you have it in two
databases, the chances both datasets will fit in memory drops which means
you increase your likelihood that you have to hit the disk which will
dramactically slow things down.
On the other hand, if both queries require a full table scan and you're
flooding your disk channel, putting the two databases on completely
different disk channels may improve throughput.
Generally though I'd say splitting a database into two pieces strictly for
performance reasons isn't going to help.
>
> --
> Thanks
> Regards.
> Josema

performace issue

In my company, we have a central database and 4 remote databases. the
central database replicate same database into 4 remote database in parallel.
the replication is transaction replication and the data is input in the
central database and replicated into remote databases. there are some
triggers in the remote database that retrieve data that was replicated from
central database into other table. the design is like this. it is wierd that
one of remote database is very slow and replication locking the table and
client application is blocked out. the client application is hang. the
replication is very slow, 4 rows/minute. other 3 remote db are ok. I don't
know why only one remote db is ofen happened such thing. The other remote
databases were no problem, and the replication doesn't lock the table. I know
the triggers are bad, especially work with replication. But why other remote
db were ok. I don't know if there are some other problem. can dbcc dbreindex
be help? Thanks.
I would make sure indexes on the keys exist. Replication is not magic
either, you can monitor it just like normal database activity. I would
suggest running the profiler. Use the duration template, but filter
for say >2000ms.

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***
I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.codecomments.com ***

PerfMon- SQL Server:Databases counter

I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***I checked this on Windows 2003 system monitor as well, and the problem is
still there. I am not sure if there's any other way out.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Toni" <teibner@.SQLallina.com> wrote in message
news:u3SamtejFHA.1416@.TK2MSFTNGP09.phx.gbl...
I'm trying to get some specific transactions/sec info on one of the
databases on an enterprise-wide SQL Server that has 120 databases on it.
It's SQL Server 2000 sp3a running on Win2k3 sp1.
In Performance Monitor, I am selecting the object SQLServer:Databases
and the Transactions/sec counter, I am only able to see 99 of the 120
databases available on the server. See kb 330088. The workaround in
the kb article is to install named instances which is not an acceptable
solution. Has anyone else run into this problem? What have you done to
get around it?
Thank you.
Toni
*** Sent via Developersdex http://www.developersdex.com ***