Hexadecimal condition check and Identity columns

hi , i am using transactional replication between two Sql server 2000 . my
tables have identity columns as the primary keys with the identity property
set to Yes( not for replication). Now when I am generating the replication
intrinsic stored procs for the subscriber , I have a problem with the update
stored procs which are generated at the subscriber side. Each of these Update
command SPs ( which are each for a table) have two update statements in them
and a IF statement which chooses one of the udpate statements to run
depending on a binary hexa decimal field. If the condition satifies , it run
the first update query which unlike the second update query updates the
identity column as well and this update query is always selected by the
Update statement to run. The second if stement is run when the hexadicmal
conditions is not met and the funny part is that it never ever happens. I had
to comment out the part of the first update query which tried to update the
identity column. Now my queston are
1.why is replication trying to update the identity columns when it know
that they can not be updated.
2.What is the binary hexadecimal field used for, why is this the condition
in each and every case select statement in every field?
Please help me with this hexadecimal paradigm
The IF statement checks to see if the PK is being updated, and if so
executes the update statement differently. This is not related to identity
columns. If you have an identity column on the subscriber, compilation of
this procedure may fail. That's because the identity attribute shouldn't be
there - it's implicitly treated as read-only. If you had queued updating
subscribers the logic in these sps is different and there is no update or
insert into the identity columns because the identity attribute is expected
on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Help: Replication stops with no error or warnings!

hi all,
I have sql server 2005 sp2 running transactional replication between 2
production servers on different domains. Every once in a while
transaction stop being delivered to the target with no warnings or
errors being generated by any of the servers involved. Using
replication monitor on the the publisher and viewing the tab "
Publisher to Distributor" shows transactions being delivered
successfully ("n transaction(s) with n command(s) were delivered
successfully") but the transactions do not show up on the target
server?
Any thoughts much appreciated as the only resolution i've found is to
wait until off hours and reinitialize the subscriptions. i'm going to
have to put a custom alert system in place to check table counts but i
really need to find the cause of this.
-Thanks! DF
Difficult to tell exactly what is getting replicated in repl monitor. Best
tool to troubleshoot is to run profiler on subscriber and see if you can find
anything. Best to do after hours if you can repeat problem w/ just you as
user.
What is your distribution agent profile? How did you initialize subscriber -
snapshot agent or backup/restore? If backup init - make sure you disable
triggers or set to NFR and drop FK's at subscriber. Perhaps your agent is
ignoring errors caused by FK constraints and not delivering rows changes...
ChrisB MCDBA
MSSQLConsulting.com
"dfateman@.yahoo.com" wrote:

> hi all,
> I have sql server 2005 sp2 running transactional replication between 2
> production servers on different domains. Every once in a while
> transaction stop being delivered to the target with no warnings or
> errors being generated by any of the servers involved. Using
> replication monitor on the the publisher and viewing the tab "
> Publisher to Distributor" shows transactions being delivered
> successfully ("n transaction(s) with n command(s) were delivered
> successfully") but the transactions do not show up on the target
> server?
> Any thoughts much appreciated as the only resolution i've found is to
> wait until off hours and reinitialize the subscriptions. i'm going to
> have to put a custom alert system in place to check table counts but i
> really need to find the cause of this.
> -Thanks! DF
>

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to the
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations are
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detail
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been run
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>> We have a SQL Server 2000 transactional business production system that
>> is installed at 20 different locations and has been running very well in
>> all but 1 location that was installed over the weekend. The hardware
>> setup "seems" to be rock solid according to the MS SQL Server performance
>> guidelines.
>> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
>> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
>> Server, the only other database being a Veritas backup DB. There are 4 gb
>> of 400 mhz RAM.
>> The Data files are located on a Raid 5 high speed disk array and the
>> Transaction log is on a Raid 1 mirrored pair. The server is connected to
>> the network by a Broadcom NetXtreme Gigab: Network controller configured
>> for 100Mb full-duplex link.
>> Once again let me state that this same system is installed on much less
>> capable hardware at other locations with up to 30 users accessing it via
>> a VB Client application running on the workstations. Those other
>> locations are not experiencing the problem.
>> Now, the differences...
>> This location has 4 time as much data as the other locations and 40
>> client workstations. One of the tables has 2.2 million records (the
>> Account Detail table which tracks all activity to Invoices) and the other
>> 13 tables have record counts that vary but mostly in the 100,000 to
>> 600,000 range. The system data was converted from a DOS DBF system (on a
>> less capable server) that held these very same records and was running
>> slowly but did not time out. We ran multiple reports after the data
>> migration and everything seems to have come over as expected. The same
>> data migration process has been run at all the other installations
>> without problem.
>> Here is an example of what happens. When the Server has been freshly
>> rebooted a simple query on the largest table takes about 3 seconds. After
>> the 40 clients run the system for about 2 hours the system gradually
>> slows to a crawl. When I try to run the same test query it can take up to
>> 3 minutes.
>> Here is the strange part. Looking at the Task Manager, the Processors are
>> barely taxed and there is 2GB of free RAM. I don't know how to monitor
>> the disk activity. This is where it gets stranger. After all users have
>> logged off, the system STILL runs dog slow using the same test query, BUT
>> after about 2 hours it is back up to good performance. I am puzzled. It
>> is as if the Transaction queue is getting backed up, but resolves itself
>> after time. Or is it maybe the Disk arrays having trouble synchronizing?
>> But, a Server reboot seems to clear the problem almost immediately.
>> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
>> in.
>> Thanks for your help...
>>
>|||John Kotuby wrote:
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (order by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memory. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/defaul...kb;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to t
he
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations a
re
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detai
l
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been ru
n
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegroups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/defaul...kb;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||John Kotuby wrote:

> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time
.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in
.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (o
rder by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memo
ry. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed

HELP..Installed production system slows over time

We have a SQL Server 2000 transactional business production system that is
installed at 20 different locations and has been running very well in all
but 1 location that was installed over the weekend. The hardware setup
"seems" to be rock solid according to the MS SQL Server performance
guidelines.
It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
Windows Server 2003 standard with SP1. The machine is dedicated to SQL
Server, the only other database being a Veritas backup DB. There are 4 gb of
400 mhz RAM.
The Data files are located on a Raid 5 high speed disk array and the
Transaction log is on a Raid 1 mirrored pair. The server is connected to the
network by a Broadcom NetXtreme Gigab: Network controller configured for
100Mb full-duplex link.
Once again let me state that this same system is installed on much less
capable hardware at other locations with up to 30 users accessing it via a
VB Client application running on the workstations. Those other locations are
not experiencing the problem.
Now, the differences...
This location has 4 time as much data as the other locations and 40 client
workstations. One of the tables has 2.2 million records (the Account Detail
table which tracks all activity to Invoices) and the other 13 tables have
record counts that vary but mostly in the 100,000 to 600,000 range. The
system data was converted from a DOS DBF system (on a less capable server)
that held these very same records and was running slowly but did not time
out. We ran multiple reports after the data migration and everything seems
to have come over as expected. The same data migration process has been run
at all the other installations without problem.
Here is an example of what happens. When the Server has been freshly
rebooted a simple query on the largest table takes about 3 seconds. After
the 40 clients run the system for about 2 hours the system gradually slows
to a crawl. When I try to run the same test query it can take up to 3
minutes.
Here is the strange part. Looking at the Task Manager, the Processors are
barely taxed and there is 2GB of free RAM. I don't know how to monitor the
disk activity. This is where it gets stranger. After all users have logged
off, the system STILL runs dog slow using the same test query, BUT after
about 2 hours it is back up to good performance. I am puzzled. It is as if
the Transaction queue is getting backed up, but resolves itself after time.
Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
reboot seems to clear the problem almost immediately.
Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
Thanks for your help...
John,
Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
sp_blockcnt.
Also, take a look at:
How to monitor SQL Server 2000 blocking
http://support.microsoft.com/default...b;en-us;271509
HTH
Jerry
"John Kotuby" <jkotuby@.snet.net> wrote in message
news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb
> of 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to
> the network by a Broadcom NetXtreme Gigab: Network controller configured
> for 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations
> are not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account
> Detail table which tracks all activity to Invoices) and the other 13
> tables have record counts that vary but mostly in the 100,000 to 600,000
> range. The system data was converted from a DOS DBF system (on a less
> capable server) that held these very same records and was running slowly
> but did not time out. We ran multiple reports after the data migration and
> everything seems to have come over as expected. The same data migration
> process has been run at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after
> time. Or is it maybe the Disk arrays having trouble synchronizing? But, a
> Server reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh
> in.
> Thanks for your help...
>
>
|||John Kotuby wrote:
> We have a SQL Server 2000 transactional business production system that is
> installed at 20 different locations and has been running very well in all
> but 1 location that was installed over the weekend. The hardware setup
> "seems" to be rock solid according to the MS SQL Server performance
> guidelines.
> It is a Dell PE6800 Quad Zeon Processor machine at 3.16 ghz running
> Windows Server 2003 standard with SP1. The machine is dedicated to SQL
> Server, the only other database being a Veritas backup DB. There are 4 gb of
> 400 mhz RAM.
> The Data files are located on a Raid 5 high speed disk array and the
> Transaction log is on a Raid 1 mirrored pair. The server is connected to the
> network by a Broadcom NetXtreme Gigab: Network controller configured for
> 100Mb full-duplex link.
> Once again let me state that this same system is installed on much less
> capable hardware at other locations with up to 30 users accessing it via a
> VB Client application running on the workstations. Those other locations are
> not experiencing the problem.
> Now, the differences...
> This location has 4 time as much data as the other locations and 40 client
> workstations. One of the tables has 2.2 million records (the Account Detail
> table which tracks all activity to Invoices) and the other 13 tables have
> record counts that vary but mostly in the 100,000 to 600,000 range. The
> system data was converted from a DOS DBF system (on a less capable server)
> that held these very same records and was running slowly but did not time
> out. We ran multiple reports after the data migration and everything seems
> to have come over as expected. The same data migration process has been run
> at all the other installations without problem.
> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
|||Your problem is an interesting one. A question I would have relating to
it has to do with the outcome of your migration data validation. You
did not elaborate on the process utilized which would lead me to
suspect it may have been quite an abbreviated validation. Can you be
quite certain that stray charactors did not populate some table space
along with valid data? I am currently doing a small bit of research
around validation methodoligy. Would be quite interested in learning
more about the process you empoloyed for this.
|||We ran Invoice balance reports for every Company (Division) in the original
DOS system and again after the data migration. All companies balanced before
and after the migration to the penny. The report output was identical for 5
years of data.
<jeff.livingston@.philips.com> wrote in message
news:1129655929.952358.319620@.f14g2000cwb.googlegr oups.com...
> Your problem is an interesting one. A question I would have relating to
> it has to do with the outcome of your migration data validation. You
> did not elaborate on the process utilized which would lead me to
> suspect it may have been quite an abbreviated validation. Can you be
> quite certain that stray charactors did not populate some table space
> along with valid data? I am currently doing a small bit of research
> around validation methodoligy. Would be quite interested in learning
> more about the process you empoloyed for this.
>
|||Thanks Jerry,
I have been looking at the results of the SPs and finding some strange
things, like a single workstation having 30 SELECT statements running
simultaneously. We are digging deeper.. it's a start.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23EXw0Z$0FHA.1108@.TK2MSFTNGP14.phx.gbl...
> John,
> Could be a blocking issue. Check out blocking using sp_who, sp_who2 and
> sp_blockcnt.
> Also, take a look at:
> How to monitor SQL Server 2000 blocking
> http://support.microsoft.com/default...b;en-us;271509
> HTH
> Jerry
> "John Kotuby" <jkotuby@.snet.net> wrote in message
> news:eGraKE90FHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||John Kotuby wrote:

> Here is an example of what happens. When the Server has been freshly
> rebooted a simple query on the largest table takes about 3 seconds. After
> the 40 clients run the system for about 2 hours the system gradually slows
> to a crawl. When I try to run the same test query it can take up to 3
> minutes.
> Here is the strange part. Looking at the Task Manager, the Processors are
> barely taxed and there is 2GB of free RAM. I don't know how to monitor the
> disk activity. This is where it gets stranger. After all users have logged
> off, the system STILL runs dog slow using the same test query, BUT after
> about 2 hours it is back up to good performance. I am puzzled. It is as if
> the Transaction queue is getting backed up, but resolves itself after time.
> Or is it maybe the Disk arrays having trouble synchronizing? But, a Server
> reboot seems to clear the problem almost immediately.
> Has anyone seen such a problem? Any ideas? Microsoft MVPs, please weigh in.
> Thanks for your help...
Do you update statistics? Auto-update?
There may be contention for tempdb, especially if you have a lot of sorts (order by and group by) in you SQL statements.
Supposedly this happens on servers with multiple processors and lots of memory. Search MS knowledge base for
concurrency and tempdb to see if it fits your situation.
Ed