Hello,
I have configured replication in a test lab using three identical two processor based servers. One server is being used as the distributor. I am running an application on a fourth machine(laptop) that is directly connected to the Source Server. This appli
cation inserts records into the source database at a designated rate. when I query against the source and target tables simultaneaously, there is huge latency in the number of records that is being replicated (Target is minutes behind the source). I have
modified the the log agent and distribution agent properties (polling interval, the maxbcpthreads, etc.). After doing that, I did notice some improvement but it is not enough. I need to have the two database synchronized within seconds of each other. Is t
here any other modifications that I can make to speed up the synchronization of data? By the time I have inserted 10K records into the source, the target has only half 7K.
it is possible that you are loading your server too much. Latency is a
function of throughput, the more through put you put on your system the
greater your overall latency.
Can you run this command in your distribution database so we can get an idea
of undelivered commands?
select * from MSdistribution_status
Also can you check to see if you can use the replication of stored
procedure's execution. This can radically improve performance.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:7B6AC640-D49F-4EB7-8EBF-4C394D6D0F5F@.microsoft.com...
> Hello,
> I have configured replication in a test lab using three identical two
processor based servers. One server is being used as the distributor. I am
running an application on a fourth machine(laptop) that is directly
connected to the Source Server. This application inserts records into the
source database at a designated rate. when I query against the source and
target tables simultaneaously, there is huge latency in the number of
records that is being replicated (Target is minutes behind the source). I
have modified the the log agent and distribution agent properties (polling
interval, the maxbcpthreads, etc.). After doing that, I did notice some
improvement but it is not enough. I need to have the two database
synchronized within seconds of each other. Is there any other modifications
that I can make to speed up the synchronization of data? By the time I have
inserted 10K records into the source, the target has only half 7K.
Showing posts with label processor. Show all posts
Showing posts with label processor. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Performance problems, SP 3 and SP 4
We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedure
s
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLT
P
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries an
d
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardwa
re
> is actually the answer. SQL Server is very heavily dependent on RAM and t
he
> disk subsystem (regardless of the service pack level). IMHO, the processo
r
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in
a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>sql
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedure
s
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLT
P
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries an
d
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardwa
re
> is actually the answer. SQL Server is very heavily dependent on RAM and t
he
> disk subsystem (regardless of the service pack level). IMHO, the processo
r
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in
a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>sql
Performance problems, SP 3 and SP 4
We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.
Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>
|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.
Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>
|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
>
>
Performance problems, SP 3 and SP 4
We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> > We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> > The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> > 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> > a
> > little behind the current technology.
> >
> > The main application is a Dairy records processing system that was
> > converted
> > from mainframe COBOL to SQL Server. The data resides in 30 user
> > databases,
> > with hundreds of tables, some of them with 10 - 15 million rows.
> >
> > OLTP applications are a very small part of the system. The vast majority
> > is
> > large Transact SQL procedures, many of them 5 - 10,000 lines long with
> > 100's
> > of internal variables and multiple cursor driven processes. These
> > procedures
> > are executed by ACCESS 2000 client applications. The ultimate output is
> > packets of very complex statistical-type paper, pdf, and text file reports
> > that are mailed or emailed to individual dairy farmers and agribusiness
> > consultants.
> >
> > There are many hierarchical table relationships with cascading updates and
> > deletes and lots of smaller procedures inside update, delete, and
> > instead-of-update triggers. The entire system is basically batch driven
> > and
> > there is NO WAY that it could ever be re-written in an OLTP environment.
> >
> > That said, we started production in July of 2003 and had no problems
> > completing an entire days production between 7:00 a.m and 5:30 p.m. We
> > were
> > able to downsize our operations staff and have automated nightly jobs and
> > backups that run unattended overnight.
> >
> > In April of 2003 we installed SP 3 and things went to hell in a hand
> > basket.
> > We had to extend operations till 11:00 p.m to finish the daily production.
> > Faced with having to hire and train a third shift operator, we reverted
> > back
> > to SP 2 and all went well until we installed SP 4 3 weeks ago.
> >
> > I have to admit that SP 4 was a big improvement over SP 3, but it still
> > extended our operations day by 3 hours. I again reverted back to SP 2.
> >
> > I was told by MS technical support that each new version of SQL Server has
> > a
> > differently tuned query optimizer and that our site with it's heavy
> > reliance
> > on complex multi-table outer joins, cursors and procedural processing is
> > the
> > problem. It appears that each new version is being tuned more for small
> > OLTP
> > transactions.
> >
> > I was also told that large "traditional installations" have test-bed
> > systems that exactly duplicate production environments, and that each new
> > service pack is tested by a team of programmers who re-tune all SQL
> > statements to match the parameters of the new query optimizer.
> >
> > We are a small shop with very tight budget, very small staff, and a large
> > application that took us 4 years to convert from mainframe COBOL. We
> > cannot
> > afford to tear our procedures appart, rebuild them, and then re-validate
> > the
> > very complex data, every time a new service pack comes out.
> >
> > Short of buying new more powerful hardware (which we have budgeted for in
> > 2007) is there anything that we can do to make SP 4 as fast and efficient
> > as
> > SP 2?
> >
> > I would appreciate hearing from anyone who has experienced performance
> > problems with SP 3 and SP 4, and what if anything you were able to do
> > abouit
> > it.
> >
>
>
The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly a
little behind the current technology.
The main application is a Dairy records processing system that was converted
from mainframe COBOL to SQL Server. The data resides in 30 user databases,
with hundreds of tables, some of them with 10 - 15 million rows.
OLTP applications are a very small part of the system. The vast majority is
large Transact SQL procedures, many of them 5 - 10,000 lines long with 100's
of internal variables and multiple cursor driven processes. These procedures
are executed by ACCESS 2000 client applications. The ultimate output is
packets of very complex statistical-type paper, pdf, and text file reports
that are mailed or emailed to individual dairy farmers and agribusiness
consultants.
There are many hierarchical table relationships with cascading updates and
deletes and lots of smaller procedures inside update, delete, and
instead-of-update triggers. The entire system is basically batch driven and
there is NO WAY that it could ever be re-written in an OLTP environment.
That said, we started production in July of 2003 and had no problems
completing an entire days production between 7:00 a.m and 5:30 p.m. We were
able to downsize our operations staff and have automated nightly jobs and
backups that run unattended overnight.
In April of 2003 we installed SP 3 and things went to hell in a hand basket.
We had to extend operations till 11:00 p.m to finish the daily production.
Faced with having to hire and train a third shift operator, we reverted back
to SP 2 and all went well until we installed SP 4 3 weeks ago.
I have to admit that SP 4 was a big improvement over SP 3, but it still
extended our operations day by 3 hours. I again reverted back to SP 2.
I was told by MS technical support that each new version of SQL Server has a
differently tuned query optimizer and that our site with it's heavy reliance
on complex multi-table outer joins, cursors and procedural processing is the
problem. It appears that each new version is being tuned more for small OLTP
transactions.
I was also told that large "traditional installations" have test-bed
systems that exactly duplicate production environments, and that each new
service pack is tested by a team of programmers who re-tune all SQL
statements to match the parameters of the new query optimizer.
We are a small shop with very tight budget, very small staff, and a large
application that took us 4 years to convert from mainframe COBOL. We cannot
afford to tear our procedures appart, rebuild them, and then re-validate the
very complex data, every time a new service pack comes out.
Short of buying new more powerful hardware (which we have budgeted for in
2007) is there anything that we can do to make SP 4 as fast and efficient as
SP 2?
I would appreciate hearing from anyone who has experienced performance
problems with SP 3 and SP 4, and what if anything you were able to do abouit
it.Not what you want to hear, but this may be one of those times where hardware
is actually the answer. SQL Server is very heavily dependent on RAM and the
disk subsystem (regardless of the service pack level). IMHO, the processor
has less to do with it than RAM and disk speed.
If you can't throw hardware at it, you should definitely run SQL Profiler
and determine what processes and queries are taking the longest and start
tuning those to see if you can get some performance boosts. You may be in a
situation where the 80/20 rule comes into play...80% of the time spent
running 20% of the procedures...
Are the Access clients doing pass-through queries, or is it all moving to
the desktop to process in teh Jet engine?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> a
> little behind the current technology.
> The main application is a Dairy records processing system that was
> converted
> from mainframe COBOL to SQL Server. The data resides in 30 user
> databases,
> with hundreds of tables, some of them with 10 - 15 million rows.
> OLTP applications are a very small part of the system. The vast majority
> is
> large Transact SQL procedures, many of them 5 - 10,000 lines long with
> 100's
> of internal variables and multiple cursor driven processes. These
> procedures
> are executed by ACCESS 2000 client applications. The ultimate output is
> packets of very complex statistical-type paper, pdf, and text file reports
> that are mailed or emailed to individual dairy farmers and agribusiness
> consultants.
> There are many hierarchical table relationships with cascading updates and
> deletes and lots of smaller procedures inside update, delete, and
> instead-of-update triggers. The entire system is basically batch driven
> and
> there is NO WAY that it could ever be re-written in an OLTP environment.
> That said, we started production in July of 2003 and had no problems
> completing an entire days production between 7:00 a.m and 5:30 p.m. We
> were
> able to downsize our operations staff and have automated nightly jobs and
> backups that run unattended overnight.
> In April of 2003 we installed SP 3 and things went to hell in a hand
> basket.
> We had to extend operations till 11:00 p.m to finish the daily production.
> Faced with having to hire and train a third shift operator, we reverted
> back
> to SP 2 and all went well until we installed SP 4 3 weeks ago.
> I have to admit that SP 4 was a big improvement over SP 3, but it still
> extended our operations day by 3 hours. I again reverted back to SP 2.
> I was told by MS technical support that each new version of SQL Server has
> a
> differently tuned query optimizer and that our site with it's heavy
> reliance
> on complex multi-table outer joins, cursors and procedural processing is
> the
> problem. It appears that each new version is being tuned more for small
> OLTP
> transactions.
> I was also told that large "traditional installations" have test-bed
> systems that exactly duplicate production environments, and that each new
> service pack is tested by a team of programmers who re-tune all SQL
> statements to match the parameters of the new query optimizer.
> We are a small shop with very tight budget, very small staff, and a large
> application that took us 4 years to convert from mainframe COBOL. We
> cannot
> afford to tear our procedures appart, rebuild them, and then re-validate
> the
> very complex data, every time a new service pack comes out.
> Short of buying new more powerful hardware (which we have budgeted for in
> 2007) is there anything that we can do to make SP 4 as fast and efficient
> as
> SP 2?
> I would appreciate hearing from anyone who has experienced performance
> problems with SP 3 and SP 4, and what if anything you were able to do
> abouit
> it.
>|||Thanks for the quick response. I will definitely try SQL Profiler to
identify the bottle-necks. FYI, our ACCESS apps use pass through queries and
linked tables.
"Kevin3NF" wrote:
> Not what you want to hear, but this may be one of those times where hardware
> is actually the answer. SQL Server is very heavily dependent on RAM and the
> disk subsystem (regardless of the service pack level). IMHO, the processor
> has less to do with it than RAM and disk speed.
> If you can't throw hardware at it, you should definitely run SQL Profiler
> and determine what processes and queries are taking the longest and start
> tuning those to see if you can get some performance boosts. You may be in a
> situation where the 80/20 rule comes into play...80% of the time spent
> running 20% of the procedures...
> Are the Access clients doing pass-through queries, or is it all moving to
> the desktop to process in teh Jet engine?
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Kevin Kuzdas" <Kevin Kuzdas@.discussions.microsoft.com> wrote in message
> news:18414DAF-34B0-418D-A446-A621CAA82704@.microsoft.com...
> > We are running SQL Server 2000, SP 2 on a 2 processor Windows 2000 server.
> > The processors are 1.1 GH, with 2 GB RAM and 3 RAID, 0 + 1 disk arrays of
> > 70, 100, and 135 GB. The system is 2 years, 10 months old, and admittedly
> > a
> > little behind the current technology.
> >
> > The main application is a Dairy records processing system that was
> > converted
> > from mainframe COBOL to SQL Server. The data resides in 30 user
> > databases,
> > with hundreds of tables, some of them with 10 - 15 million rows.
> >
> > OLTP applications are a very small part of the system. The vast majority
> > is
> > large Transact SQL procedures, many of them 5 - 10,000 lines long with
> > 100's
> > of internal variables and multiple cursor driven processes. These
> > procedures
> > are executed by ACCESS 2000 client applications. The ultimate output is
> > packets of very complex statistical-type paper, pdf, and text file reports
> > that are mailed or emailed to individual dairy farmers and agribusiness
> > consultants.
> >
> > There are many hierarchical table relationships with cascading updates and
> > deletes and lots of smaller procedures inside update, delete, and
> > instead-of-update triggers. The entire system is basically batch driven
> > and
> > there is NO WAY that it could ever be re-written in an OLTP environment.
> >
> > That said, we started production in July of 2003 and had no problems
> > completing an entire days production between 7:00 a.m and 5:30 p.m. We
> > were
> > able to downsize our operations staff and have automated nightly jobs and
> > backups that run unattended overnight.
> >
> > In April of 2003 we installed SP 3 and things went to hell in a hand
> > basket.
> > We had to extend operations till 11:00 p.m to finish the daily production.
> > Faced with having to hire and train a third shift operator, we reverted
> > back
> > to SP 2 and all went well until we installed SP 4 3 weeks ago.
> >
> > I have to admit that SP 4 was a big improvement over SP 3, but it still
> > extended our operations day by 3 hours. I again reverted back to SP 2.
> >
> > I was told by MS technical support that each new version of SQL Server has
> > a
> > differently tuned query optimizer and that our site with it's heavy
> > reliance
> > on complex multi-table outer joins, cursors and procedural processing is
> > the
> > problem. It appears that each new version is being tuned more for small
> > OLTP
> > transactions.
> >
> > I was also told that large "traditional installations" have test-bed
> > systems that exactly duplicate production environments, and that each new
> > service pack is tested by a team of programmers who re-tune all SQL
> > statements to match the parameters of the new query optimizer.
> >
> > We are a small shop with very tight budget, very small staff, and a large
> > application that took us 4 years to convert from mainframe COBOL. We
> > cannot
> > afford to tear our procedures appart, rebuild them, and then re-validate
> > the
> > very complex data, every time a new service pack comes out.
> >
> > Short of buying new more powerful hardware (which we have budgeted for in
> > 2007) is there anything that we can do to make SP 4 as fast and efficient
> > as
> > SP 2?
> >
> > I would appreciate hearing from anyone who has experienced performance
> > problems with SP 3 and SP 4, and what if anything you were able to do
> > abouit
> > it.
> >
>
>
Saturday, February 25, 2012
Performance monitoring
Hi,
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.We need to see the real trigger and preferably an error message from SQL Server if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
> Hi
> You need to select from the Virtaul table called "Inserted" to get the rows
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
> > Hi,
> >
> > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > (named Performances). All data are stored in the table CounterData.
> >
> > I tried to set a trigger on this table to copy inserted data to another
> > table (TB_LastCounters). This trigger does not work when data are
> > automatically inserted from the Win 2003 counter log. But, the trigger works
> > well when the data is inserted manually.
> >
> > Could you explain me this behavior?
> >
> > Here is a simplified example of my trigger :
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > VALUES (1, 'toto', 2)
> > END
> >
> > Thanks a lot,
> >
> > Eric.|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does not
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
> > Hi
> >
> > You need to select from the Virtaul table called "Inserted" to get the rows
> > that were inserted by the statement.
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> > FROM INSERTED
> > END
> >
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "itparis" wrote:
> >
> > > Hi,
> > >
> > > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > > (named Performances). All data are stored in the table CounterData.
> > >
> > > I tried to set a trigger on this table to copy inserted data to another
> > > table (TB_LastCounters). This trigger does not work when data are
> > > automatically inserted from the Win 2003 counter log. But, the trigger works
> > > well when the data is inserted manually.
> > >
> > > Could you explain me this behavior?
> > >
> > > Here is a simplified example of my trigger :
> > >
> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > FOR INSERT
> > > AS
> > > BEGIN
> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > VALUES (1, 'toto', 2)
> > > END
> > >
> > > Thanks a lot,
> > >
> > > Eric.|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
> Hi
> If it does not work, then the odds are good that it is failing, and rolling
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
> > Thank you for your responses.
> >
> > When Win 2003 counter log fills the CounterData table, the trigger does not
> > work but there are no errors.
> > Here is my real trigger :
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> > SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> > SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> > CounterDate, CounterValue FROM INSERTED
> > END
> >
> > This trigger works when I manually add data to CounterData table but does
> > not work when it is Win2003 counter log.
> >
> > Thank you,
> >
> > Eric.
> >
> > "Mike Epprecht (SQL MVP)" a écrit :
> >
> > > Hi
> > >
> > > You need to select from the Virtaul table called "Inserted" to get the rows
> > > that were inserted by the statement.
> > >
> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > FOR INSERT
> > > AS
> > > BEGIN
> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> > > FROM INSERTED
> > > END
> > >
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "itparis" wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > > > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > > > (named Performances). All data are stored in the table CounterData.
> > > >
> > > > I tried to set a trigger on this table to copy inserted data to another
> > > > table (TB_LastCounters). This trigger does not work when data are
> > > > automatically inserted from the Win 2003 counter log. But, the trigger works
> > > > well when the data is inserted manually.
> > > >
> > > > Could you explain me this behavior?
> > > >
> > > > Here is a simplified example of my trigger :
> > > >
> > > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > > FOR INSERT
> > > > AS
> > > > BEGIN
> > > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > > VALUES (1, 'toto', 2)
> > > > END
> > > >
> > > > Thanks a lot,
> > > >
> > > > Eric.|||How does perfmon insert the data? Run a profiler trace to see whether perfmon does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass triggers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can see
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>> Hi
>> If it does not work, then the odds are good that it is failing, and rolling
>> back the transaction too.
>> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> FOR INSERT
>> AS
>> BEGIN
>> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
>> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
>> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
>> CounterDate, CounterValue FROM INSERTED
>> IF @.@.ERROR <> 0
>> PRINT 'Error Occurred'
>> END
>> END
>> Do an insert manually and see what happens.
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "itparis" wrote:
>> > Thank you for your responses.
>> >
>> > When Win 2003 counter log fills the CounterData table, the trigger does not
>> > work but there are no errors.
>> > Here is my real trigger :
>> >
>> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > FOR INSERT
>> > AS
>> > BEGIN
>> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
>> > SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
>> > SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
>> > CounterDate, CounterValue FROM INSERTED
>> > END
>> >
>> > This trigger works when I manually add data to CounterData table but does
>> > not work when it is Win2003 counter log.
>> >
>> > Thank you,
>> >
>> > Eric.
>> >
>> > "Mike Epprecht (SQL MVP)" a écrit :
>> >
>> > > Hi
>> > >
>> > > You need to select from the Virtaul table called "Inserted" to get the rows
>> > > that were inserted by the statement.
>> > >
>> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > > FOR INSERT
>> > > AS
>> > > BEGIN
>> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
>> > > FROM INSERTED
>> > > END
>> > >
>> > > --
>> > > --
>> > > Mike Epprecht, Microsoft SQL Server MVP
>> > > Zurich, Switzerland
>> > >
>> > > MVP Program: http://www.microsoft.com/mvp
>> > >
>> > > Blog: http://www.msmvps.com/epprecht/
>> > >
>> > >
>> > >
>> > > "itparis" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I'm using the counter log of Win 2003 to record the server activity (ex : %
>> > > > processor time). This activity is stored in an SQL Server 2000 SP3a database
>> > > > (named Performances). All data are stored in the table CounterData.
>> > > >
>> > > > I tried to set a trigger on this table to copy inserted data to another
>> > > > table (TB_LastCounters). This trigger does not work when data are
>> > > > automatically inserted from the Win 2003 counter log. But, the trigger works
>> > > > well when the data is inserted manually.
>> > > >
>> > > > Could you explain me this behavior?
>> > > >
>> > > > Here is a simplified example of my trigger :
>> > > >
>> > > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > > > FOR INSERT
>> > > > AS
>> > > > BEGIN
>> > > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > > > VALUES (1, 'toto', 2)
>> > > > END
>> > > >
>> > > > Thanks a lot,
>> > > >
>> > > > Eric.
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.We need to see the real trigger and preferably an error message from SQL Server if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
> Hi
> You need to select from the Virtaul table called "Inserted" to get the rows
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
> > Hi,
> >
> > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > (named Performances). All data are stored in the table CounterData.
> >
> > I tried to set a trigger on this table to copy inserted data to another
> > table (TB_LastCounters). This trigger does not work when data are
> > automatically inserted from the Win 2003 counter log. But, the trigger works
> > well when the data is inserted manually.
> >
> > Could you explain me this behavior?
> >
> > Here is a simplified example of my trigger :
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > VALUES (1, 'toto', 2)
> > END
> >
> > Thanks a lot,
> >
> > Eric.|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does not
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
> > Hi
> >
> > You need to select from the Virtaul table called "Inserted" to get the rows
> > that were inserted by the statement.
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> > FROM INSERTED
> > END
> >
> > --
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> >
> >
> > "itparis" wrote:
> >
> > > Hi,
> > >
> > > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > > (named Performances). All data are stored in the table CounterData.
> > >
> > > I tried to set a trigger on this table to copy inserted data to another
> > > table (TB_LastCounters). This trigger does not work when data are
> > > automatically inserted from the Win 2003 counter log. But, the trigger works
> > > well when the data is inserted manually.
> > >
> > > Could you explain me this behavior?
> > >
> > > Here is a simplified example of my trigger :
> > >
> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > FOR INSERT
> > > AS
> > > BEGIN
> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > VALUES (1, 'toto', 2)
> > > END
> > >
> > > Thanks a lot,
> > >
> > > Eric.|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
> Hi
> If it does not work, then the odds are good that it is failing, and rolling
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
> > Thank you for your responses.
> >
> > When Win 2003 counter log fills the CounterData table, the trigger does not
> > work but there are no errors.
> > Here is my real trigger :
> >
> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > FOR INSERT
> > AS
> > BEGIN
> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> > SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> > SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> > CounterDate, CounterValue FROM INSERTED
> > END
> >
> > This trigger works when I manually add data to CounterData table but does
> > not work when it is Win2003 counter log.
> >
> > Thank you,
> >
> > Eric.
> >
> > "Mike Epprecht (SQL MVP)" a écrit :
> >
> > > Hi
> > >
> > > You need to select from the Virtaul table called "Inserted" to get the rows
> > > that were inserted by the statement.
> > >
> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > FOR INSERT
> > > AS
> > > BEGIN
> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> > > FROM INSERTED
> > > END
> > >
> > > --
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > >
> > >
> > > "itparis" wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm using the counter log of Win 2003 to record the server activity (ex : %
> > > > processor time). This activity is stored in an SQL Server 2000 SP3a database
> > > > (named Performances). All data are stored in the table CounterData.
> > > >
> > > > I tried to set a trigger on this table to copy inserted data to another
> > > > table (TB_LastCounters). This trigger does not work when data are
> > > > automatically inserted from the Win 2003 counter log. But, the trigger works
> > > > well when the data is inserted manually.
> > > >
> > > > Could you explain me this behavior?
> > > >
> > > > Here is a simplified example of my trigger :
> > > >
> > > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> > > > FOR INSERT
> > > > AS
> > > > BEGIN
> > > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> > > > VALUES (1, 'toto', 2)
> > > > END
> > > >
> > > > Thanks a lot,
> > > >
> > > > Eric.|||How does perfmon insert the data? Run a profiler trace to see whether perfmon does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass triggers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can see
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>> Hi
>> If it does not work, then the odds are good that it is failing, and rolling
>> back the transaction too.
>> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> FOR INSERT
>> AS
>> BEGIN
>> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
>> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
>> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
>> CounterDate, CounterValue FROM INSERTED
>> IF @.@.ERROR <> 0
>> PRINT 'Error Occurred'
>> END
>> END
>> Do an insert manually and see what happens.
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "itparis" wrote:
>> > Thank you for your responses.
>> >
>> > When Win 2003 counter log fills the CounterData table, the trigger does not
>> > work but there are no errors.
>> > Here is my real trigger :
>> >
>> > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > FOR INSERT
>> > AS
>> > BEGIN
>> > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
>> > SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
>> > SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
>> > CounterDate, CounterValue FROM INSERTED
>> > END
>> >
>> > This trigger works when I manually add data to CounterData table but does
>> > not work when it is Win2003 counter log.
>> >
>> > Thank you,
>> >
>> > Eric.
>> >
>> > "Mike Epprecht (SQL MVP)" a écrit :
>> >
>> > > Hi
>> > >
>> > > You need to select from the Virtaul table called "Inserted" to get the rows
>> > > that were inserted by the statement.
>> > >
>> > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > > FOR INSERT
>> > > AS
>> > > BEGIN
>> > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > > SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
>> > > FROM INSERTED
>> > > END
>> > >
>> > > --
>> > > --
>> > > Mike Epprecht, Microsoft SQL Server MVP
>> > > Zurich, Switzerland
>> > >
>> > > MVP Program: http://www.microsoft.com/mvp
>> > >
>> > > Blog: http://www.msmvps.com/epprecht/
>> > >
>> > >
>> > >
>> > > "itparis" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I'm using the counter log of Win 2003 to record the server activity (ex : %
>> > > > processor time). This activity is stored in an SQL Server 2000 SP3a database
>> > > > (named Performances). All data are stored in the table CounterData.
>> > > >
>> > > > I tried to set a trigger on this table to copy inserted data to another
>> > > > table (TB_LastCounters). This trigger does not work when data are
>> > > > automatically inserted from the Win 2003 counter log. But, the trigger works
>> > > > well when the data is inserted manually.
>> > > >
>> > > > Could you explain me this behavior?
>> > > >
>> > > > Here is a simplified example of my trigger :
>> > > >
>> > > > CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
>> > > > FOR INSERT
>> > > > AS
>> > > > BEGIN
>> > > > INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
>> > > > VALUES (1, 'toto', 2)
>> > > > END
>> > > >
>> > > > Thanks a lot,
>> > > >
>> > > > Eric.
Monday, February 20, 2012
Performance monitoring
Hi,
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.
We need to see the real trigger and preferably an error message from SQL Server if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> You need to select from the Virtaul table called "Inserted" to get the rows
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
[vbcol=seagreen]
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does not
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> If it does not work, then the odds are good that it is failing, and rolling
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||How does perfmon insert the data? Run a profiler trace to see whether perfmon does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can see
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.
We need to see the real trigger and preferably an error message from SQL Server if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex : %
> processor time). This activity is stored in an SQL Server 2000 SP3a database
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger works
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.
|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> You need to select from the Virtaul table called "Inserted" to get the rows
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
[vbcol=seagreen]
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does not
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> If it does not work, then the odds are good that it is failing, and rolling
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2) +
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
|||How does perfmon insert the data? Run a profiler trace to see whether perfmon does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can see
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
Performance monitoring
Hi,
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.We need to see the real trigger and preferably an error message from SQL Ser
ver if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex :
%
> processor time). This activity is stored in an SQL Server 2000 SP3a databa
se
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger wor
ks
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex :
%
> processor time). This activity is stored in an SQL Server 2000 SP3a databa
se
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger wor
ks
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> You need to select from the Virtaul table called "Inserted" to get the row
s
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
>|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
[vbcol=seagreen]
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does no
t
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> If it does not work, then the odds are good that it is failing, and rollin
g
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
>|||How does perfmon insert the data? Run a profiler trace to see whether perfmo
n does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass
triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can s
ee
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>
I'm using the counter log of Win 2003 to record the server activity (ex : %
processor time). This activity is stored in an SQL Server 2000 SP3a database
(named Performances). All data are stored in the table CounterData.
I tried to set a trigger on this table to copy inserted data to another
table (TB_LastCounters). This trigger does not work when data are
automatically inserted from the Win 2003 counter log. But, the trigger works
well when the data is inserted manually.
Could you explain me this behavior?
Here is a simplified example of my trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
VALUES (1, 'toto', 2)
END
Thanks a lot,
Eric.We need to see the real trigger and preferably an error message from SQL Ser
ver if you have such. My
guess is that the trigger doesn't handle multi-row modifications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:67B984F3-DB2A-4F79-ABBC-67338F57F873@.microsoft.com...
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex :
%
> processor time). This activity is stored in an SQL Server 2000 SP3a databa
se
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger wor
ks
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Hi
You need to select from the Virtaul table called "Inserted" to get the rows
that were inserted by the statement.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
FROM INSERTED
END
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
> Hi,
> I'm using the counter log of Win 2003 to record the server activity (ex :
%
> processor time). This activity is stored in an SQL Server 2000 SP3a databa
se
> (named Performances). All data are stored in the table CounterData.
> I tried to set a trigger on this table to copy inserted data to another
> table (TB_LastCounters). This trigger does not work when data are
> automatically inserted from the Win 2003 counter log. But, the trigger wor
ks
> well when the data is inserted manually.
> Could you explain me this behavior?
> Here is a simplified example of my trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> VALUES (1, 'toto', 2)
> END
> Thanks a lot,
> Eric.|||Thank you for your responses.
When Win 2003 counter log fills the CounterData table, the trigger does not
work but there are no errors.
Here is my real trigger :
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
END
This trigger works when I manually add data to CounterData table but does
not work when it is Win2003 counter log.
Thank you,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> You need to select from the Virtaul table called "Inserted" to get the row
s
> that were inserted by the statement.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT INSERTED.CounterID, INSERTED.CounterDateTime, INSERTED.CounterValue
> FROM INSERTED
> END
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
>|||Hi
If it does not work, then the odds are good that it is failing, and rolling
back the transaction too.
CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
FOR INSERT
AS
BEGIN
INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
CounterDate, CounterValue FROM INSERTED
IF @.@.ERROR <> 0
PRINT 'Error Occurred'
END
END
Do an insert manually and see what happens.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"itparis" wrote:
[vbcol=seagreen]
> Thank you for your responses.
> When Win 2003 counter log fills the CounterData table, the trigger does no
t
> work but there are no errors.
> Here is my real trigger :
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> END
> This trigger works when I manually add data to CounterData table but does
> not work when it is Win2003 counter log.
> Thank you,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>|||Hi Mike,
I've written your trigger. I made your test but I don't know where I can see
the result of PRINT 'Error occured'. Can you tell me where I could see it?
Thanks,
Eric.
"Mike Epprecht (SQL MVP)" a écrit :
[vbcol=seagreen]
> Hi
> If it does not work, then the odds are good that it is failing, and rollin
g
> back the transaction too.
> CREATE TRIGGER CopyInsertions ON [dbo].[CounterData]
> FOR INSERT
> AS
> BEGIN
> INSERT INTO TB_LastCounters (CounterID, CounterDateTime, CounterValue)
> SELECT CounterID, LEFT(CounterDateTime,4) + SUBSTRING(CounterDateTime,6,2)
+
> SUBSTRING(CounterDateTime,9,2) + SUBSTRING(CounterDateTime,12,2) +
> SUBSTRING(CounterDateTime,15,2) + SUBSTRING(CounterDateTime,18,2) As
> CounterDate, CounterValue FROM INSERTED
> IF @.@.ERROR <> 0
> PRINT 'Error Occurred'
> END
> END
> Do an insert manually and see what happens.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "itparis" wrote:
>|||How does perfmon insert the data? Run a profiler trace to see whether perfmo
n does regular INSERTs
or uses some form of bulk loading API. Of the later, these API's can bypass
triggers.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:81DAD60B-7D64-45AB-965E-31C58426AA6C@.microsoft.com...[vbcol=seagreen]
> Hi Mike,
> I've written your trigger. I made your test but I don't know where I can s
ee
> the result of PRINT 'Error occured'. Can you tell me where I could see it?
> Thanks,
> Eric.
> "Mike Epprecht (SQL MVP)" a écrit :
>
Performance Monitor Miscalculates SQLSERVR Time?
When I run perfmon against the sqlservr.exe process, its
percent of processor time goes over 350% percent. Is this
a bug in perfmon. If so, how do I report it? We are
running a 4-cpu box, WINNT 4 with SQL2K sp3a.the percent processor time reported for the sqlservr
process is relative to a single processor, not the entire
system,
hence 350% on a 4-CPU system is 350/400 = 87.5% of the
overall system
>--Original Message--
>When I run perfmon against the sqlservr.exe process, its
>percent of processor time goes over 350% percent. Is
this
>a bug in perfmon. If so, how do I report it? We are
>running a 4-cpu box, WINNT 4 with SQL2K sp3a.
>.
>
percent of processor time goes over 350% percent. Is this
a bug in perfmon. If so, how do I report it? We are
running a 4-cpu box, WINNT 4 with SQL2K sp3a.the percent processor time reported for the sqlservr
process is relative to a single processor, not the entire
system,
hence 350% on a 4-CPU system is 350/400 = 87.5% of the
overall system
>--Original Message--
>When I run perfmon against the sqlservr.exe process, its
>percent of processor time goes over 350% percent. Is
this
>a bug in perfmon. If so, how do I report it? We are
>running a 4-cpu box, WINNT 4 with SQL2K sp3a.
>.
>
Performance Monitor log files
For the SQL Server running here we have setup a log file that monitors all the activities while the server is running, such as processor percentage being used over the day, CPU percentage taken by SQL Server etc. The problem I'm facing is that the log files (which are in PERFMON format) are recorded over a period of a business day for 30 days. Now I want to get the measurements for all the 30 days combined into one file where i can analyze all the measurement over 30 days.
Thanks in advance.HI
I dont know of any way to combine log files. The best you can do is establish numeric values then manually put these into a spreadsheet if you want a graph.
In future, just run the perfmon monitoring for 30 days......
Hope this helps,
SG
Thanks in advance.HI
I dont know of any way to combine log files. The best you can do is establish numeric values then manually put these into a spreadsheet if you want a graph.
In future, just run the perfmon monitoring for 30 days......
Hope this helps,
SG
Performance Monitor Data (Sysperfinfo)
I have serveral 2000 databases on a Winwords 2000 Advanced server.
I would like to collect server,processor, disk, performance data monitor
data within SQL Server.
I would like to collect the same data that you can get from perfmon but
withinQL Sever. I know SQL Server has sysperfinfo table but missing several
counters from perfmon that I would like to collect.
Please help me this issue.Hi Joe,
try using SQL profiler and set up a new trace. You can choose the data
fields you wish to see within SQL.
Hope this helps,
Isobel
"Joe K." wrote:
> I have serveral 2000 databases on a Winwords 2000 Advanced server.
> I would like to collect server,processor, disk, performance data monitor
> data within SQL Server.
> I would like to collect the same data that you can get from perfmon but
> withinQL Sever. I know SQL Server has sysperfinfo table but missing several
> counters from perfmon that I would like to collect.
> Please help me this issue.|||Nothing built in to do this with Win 2K. You can log the
Perf Mon counters to a csv and import the data into SQL
Server from there. The operating systems after 2000 have an
option to log directly to a database table but that will
have a negative impact on performance. It's generally a
better idea to log to files rather than databases and then
import the data from the files.
-Sue
On Mon, 1 May 2006 09:45:02 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have serveral 2000 databases on a Winwords 2000 Advanced server.
>I would like to collect server,processor, disk, performance data monitor
>data within SQL Server.
>I would like to collect the same data that you can get from perfmon but
>withinQL Sever. I know SQL Server has sysperfinfo table but missing several
>counters from perfmon that I would like to collect.
>Please help me this issue.
I would like to collect server,processor, disk, performance data monitor
data within SQL Server.
I would like to collect the same data that you can get from perfmon but
withinQL Sever. I know SQL Server has sysperfinfo table but missing several
counters from perfmon that I would like to collect.
Please help me this issue.Hi Joe,
try using SQL profiler and set up a new trace. You can choose the data
fields you wish to see within SQL.
Hope this helps,
Isobel
"Joe K." wrote:
> I have serveral 2000 databases on a Winwords 2000 Advanced server.
> I would like to collect server,processor, disk, performance data monitor
> data within SQL Server.
> I would like to collect the same data that you can get from perfmon but
> withinQL Sever. I know SQL Server has sysperfinfo table but missing several
> counters from perfmon that I would like to collect.
> Please help me this issue.|||Nothing built in to do this with Win 2K. You can log the
Perf Mon counters to a csv and import the data into SQL
Server from there. The operating systems after 2000 have an
option to log directly to a database table but that will
have a negative impact on performance. It's generally a
better idea to log to files rather than databases and then
import the data from the files.
-Sue
On Mon, 1 May 2006 09:45:02 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have serveral 2000 databases on a Winwords 2000 Advanced server.
>I would like to collect server,processor, disk, performance data monitor
>data within SQL Server.
>I would like to collect the same data that you can get from perfmon but
>withinQL Sever. I know SQL Server has sysperfinfo table but missing several
>counters from perfmon that I would like to collect.
>Please help me this issue.
Performance Monitor Data (Sysperfinfo)
I have serveral 2000 databases on a Winwords 2000 Advanced server.
I would like to collect server,processor, disk, performance data monitor
data within SQL Server.
I would like to collect the same data that you can get from perfmon but
withinQL Sever. I know SQL Server has sysperfinfo table but missing several
counters from perfmon that I would like to collect.
Please help me this issue.Hi Joe,
try using SQL profiler and set up a new trace. You can choose the data
fields you wish to see within SQL.
Hope this helps,
Isobel
"Joe K." wrote:
> I have serveral 2000 databases on a Winwords 2000 Advanced server.
> I would like to collect server,processor, disk, performance data monitor
> data within SQL Server.
> I would like to collect the same data that you can get from perfmon but
> withinQL Sever. I know SQL Server has sysperfinfo table but missing sever
al
> counters from perfmon that I would like to collect.
> Please help me this issue.|||Nothing built in to do this with Win 2K. You can log the
Perf Mon counters to a csv and import the data into SQL
Server from there. The operating systems after 2000 have an
option to log directly to a database table but that will
have a negative impact on performance. It's generally a
better idea to log to files rather than databases and then
import the data from the files.
-Sue
On Mon, 1 May 2006 09:45:02 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have serveral 2000 databases on a Winwords 2000 Advanced server.
>I would like to collect server,processor, disk, performance data monitor
>data within SQL Server.
>I would like to collect the same data that you can get from perfmon but
>withinQL Sever. I know SQL Server has sysperfinfo table but missing severa
l
>counters from perfmon that I would like to collect.
>Please help me this issue.
I would like to collect server,processor, disk, performance data monitor
data within SQL Server.
I would like to collect the same data that you can get from perfmon but
withinQL Sever. I know SQL Server has sysperfinfo table but missing several
counters from perfmon that I would like to collect.
Please help me this issue.Hi Joe,
try using SQL profiler and set up a new trace. You can choose the data
fields you wish to see within SQL.
Hope this helps,
Isobel
"Joe K." wrote:
> I have serveral 2000 databases on a Winwords 2000 Advanced server.
> I would like to collect server,processor, disk, performance data monitor
> data within SQL Server.
> I would like to collect the same data that you can get from perfmon but
> withinQL Sever. I know SQL Server has sysperfinfo table but missing sever
al
> counters from perfmon that I would like to collect.
> Please help me this issue.|||Nothing built in to do this with Win 2K. You can log the
Perf Mon counters to a csv and import the data into SQL
Server from there. The operating systems after 2000 have an
option to log directly to a database table but that will
have a negative impact on performance. It's generally a
better idea to log to files rather than databases and then
import the data from the files.
-Sue
On Mon, 1 May 2006 09:45:02 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have serveral 2000 databases on a Winwords 2000 Advanced server.
>I would like to collect server,processor, disk, performance data monitor
>data within SQL Server.
>I would like to collect the same data that you can get from perfmon but
>withinQL Sever. I know SQL Server has sysperfinfo table but missing severa
l
>counters from perfmon that I would like to collect.
>Please help me this issue.
Subscribe to:
Posts (Atom)