difference between FOR INSERT and AFTER INSERT triggers

I've been reading the docs and playing around, but I'm still not
getting the difference. For instance,

create table a(i int check(i>0))
create table a_src(i int)
go
create unique index ai on a(i) with IGNORE_DUP_KEY
go
insert into a_src values(1)
insert into a_src values(1)
insert into a_src values(2)
--insert into a_src values(-1)
go
create trigger a4ins on a
for insert
as
select * from inserted
go
create trigger afterins on a
after insert
as
select * from inserted
go
insert into a select * from a_src
go
drop table a
drop table a_src

I'm getting

i
----
1
2

(2 row(s) affected)

Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4
Duplicate key was ignored.
i
----
1
2

(2 row(s) affected)

even the inserted quasi tables are identical.
If I uncomment insert into a_src values(-1), I'm getting

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint
'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',
table 'a', column 'i'.
The statement has been terminated.

without any output from either trigger.
So,
in which situations will FOR INSERT be useful while AFTER INSERT won't
do?
in which situations will AFTER INSERT be useful while FOR INSERT won't
do?Ford Desperado (ford_desperado@.yahoo.com) writes:
> I've been reading the docs and playing around, but I'm still not
> getting the difference.

That is because there isn't any!

If memory servers, FOR was the original syntax. I suspect that AFTER
has been added to align with ANSI standards. FOR is not very precise,
where as AFTER tells us that the trigger fires after the statement.
That in difference to BEFORE and INSTEAD OF triggers. (Of which SQL
Server has the latter, but not the former.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks Erlandsql

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping b
y
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:[vbcol=seagreen]
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in
a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impac
t
> than trying to take seconds (or even minutes) off of long running procedur
es
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...

Diagnosing source of SQL Server activity in high-volume system

Hi everyone,
We are running a SQL Server 2000 instance which is getting hit with
hundreds of queries a minute. The vast majority of these are very
short-lived, low overhead queries. Some of them are highly resouce
intensive (< 1%). My problem is that lately, our SQL Server CPU
utilization has climbed way up and I can't begin to figure out what is
causing it... the high volume of quick queries, or the low volume of
slow queries.
I'm fairly skilled at SQL Server performance tuning, but this has got
me stumped. How can I isolate the category of queries are causing all
of the activity? I've tried using profiler, but it displays each of the
queries individually ... there's no way to group activity together by
meaningful categories (that I'm aware of).
Can anyone point me in the right direction?
Thanks!"Rich" <rich@.adgooroo.com> wrote in message
news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> Hi everyone,
> We are running a SQL Server 2000 instance which is getting hit with
> hundreds of queries a minute. The vast majority of these are very
> short-lived, low overhead queries. Some of them are highly resouce
> intensive (< 1%). My problem is that lately, our SQL Server CPU
> utilization has climbed way up and I can't begin to figure out what is
> causing it... the high volume of quick queries, or the low volume of
> slow queries.
> I'm fairly skilled at SQL Server performance tuning, but this has got
> me stumped. How can I isolate the category of queries are causing all
> of the activity? I've tried using profiler, but it displays each of the
> queries individually ... there's no way to group activity together by
> meaningful categories (that I'm aware of).
> Can anyone point me in the right direction?
>
The basic technique here is to use profiler or a server trace to gather
execution statistics for individual queries over a window of time. Then
load the results into a table and analyze them. For instance, grouping by
query text (or truncated or scrubbed text) and then summing the IO and CPU
statistics. This will isolate the queries driving the CPU use.
David|||Hi David,
Great suggestion! I didn't know you could export this information from
Profiler into another format.
One more question. Can you suggest which performance monitors I should
select in profiler to get just the query CPU and IO information for
completed queries? Or better yet, do you know of any online tutorials
with this information?
Thanks!
-Rich
David Browne wrote:
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> > Hi everyone,
> >
> > We are running a SQL Server 2000 instance which is getting hit with
> > hundreds of queries a minute. The vast majority of these are very
> > short-lived, low overhead queries. Some of them are highly resouce
> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> > utilization has climbed way up and I can't begin to figure out what is
> > causing it... the high volume of quick queries, or the low volume of
> > slow queries.
> >
> > I'm fairly skilled at SQL Server performance tuning, but this has got
> > me stumped. How can I isolate the category of queries are causing all
> > of the activity? I've tried using profiler, but it displays each of the
> > queries individually ... there's no way to group activity together by
> > meaningful categories (that I'm aware of).
> >
> > Can anyone point me in the right direction?
> >
> The basic technique here is to use profiler or a server trace to gather
> execution statistics for individual queries over a window of time. Then
> load the results into a table and analyze them. For instance, grouping by
> query text (or truncated or scrubbed text) and then summing the IO and CPU
> statistics. This will isolate the queries driving the CPU use.
> David|||Expanding on David's comments:
Run the profiler trace remotely and let your profiler trace store data in a
table on a different SQL Server.
One of the things that I will do is capture data over a length of time and
then I can calculate the 'effect' of a stored procedure by multiplying
number of times used per time segment (hour, etc.) times the duration,
perhaps also weighted for I/O.
Often, I have been able to determine that making the effort to shave a 10
milliseconds off of a high usage frequency stored procedure has more impact
than trying to take seconds (or even minutes) off of long running procedures
that are not used very often.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Rich" <rich@.adgooroo.com> wrote in message
news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> Hi David,
> Great suggestion! I didn't know you could export this information from
> Profiler into another format.
> One more question. Can you suggest which performance monitors I should
> select in profiler to get just the query CPU and IO information for
> completed queries? Or better yet, do you know of any online tutorials
> with this information?
> Thanks!
> -Rich
>
> David Browne wrote:
>> "Rich" <rich@.adgooroo.com> wrote in message
>> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
>> > Hi everyone,
>> >
>> > We are running a SQL Server 2000 instance which is getting hit with
>> > hundreds of queries a minute. The vast majority of these are very
>> > short-lived, low overhead queries. Some of them are highly resouce
>> > intensive (< 1%). My problem is that lately, our SQL Server CPU
>> > utilization has climbed way up and I can't begin to figure out what is
>> > causing it... the high volume of quick queries, or the low volume of
>> > slow queries.
>> >
>> > I'm fairly skilled at SQL Server performance tuning, but this has got
>> > me stumped. How can I isolate the category of queries are causing all
>> > of the activity? I've tried using profiler, but it displays each of the
>> > queries individually ... there's no way to group activity together by
>> > meaningful categories (that I'm aware of).
>> >
>> > Can anyone point me in the right direction?
>> >
>> The basic technique here is to use profiler or a server trace to gather
>> execution statistics for individual queries over a window of time. Then
>> load the results into a table and analyze them. For instance, grouping
>> by
>> query text (or truncated or scrubbed text) and then summing the IO and
>> CPU
>> statistics. This will isolate the queries driving the CPU use.
>> David
>|||Hi everyone,
Thanks for your responses. This technique worked amazingly well for us!
In about 30 minutes of work, we were able to diagnose the problem and
shaved CPU usage from 44% down to an average 14%. We also got a nice
little reduction in disk I/O as well.
I ran profiler and saved everything to a local table. Then ran the
following query to group things together:
select substring(textdata, 1, 24), count(rowNumber) as transactions,
sum(Duration) as Duration, sum(CPU) as CPU, sum(Reads) as Reads,
Sum(Writes) as Writes
from profilerresults
group by substring(textdata, 1, 24)
order by sum(CPU) desc
It turns out there are actually three different sets of queries which
are all combining to cause the problem, but the top offender was
responsible for 50% of the CPU utilization in all queries.
Thanks!!
-Rich
Arnie Rowland wrote:
> Expanding on David's comments:
> Run the profiler trace remotely and let your profiler trace store data in a
> table on a different SQL Server.
> One of the things that I will do is capture data over a length of time and
> then I can calculate the 'effect' of a stored procedure by multiplying
> number of times used per time segment (hour, etc.) times the duration,
> perhaps also weighted for I/O.
> Often, I have been able to determine that making the effort to shave a 10
> milliseconds off of a high usage frequency stored procedure has more impact
> than trying to take seconds (or even minutes) off of long running procedures
> that are not used very often.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Rich" <rich@.adgooroo.com> wrote in message
> news:1156888113.125756.277830@.p79g2000cwp.googlegroups.com...
> > Hi David,
> >
> > Great suggestion! I didn't know you could export this information from
> > Profiler into another format.
> >
> > One more question. Can you suggest which performance monitors I should
> > select in profiler to get just the query CPU and IO information for
> > completed queries? Or better yet, do you know of any online tutorials
> > with this information?
> >
> > Thanks!
> > -Rich
> >
> >
> > David Browne wrote:
> >> "Rich" <rich@.adgooroo.com> wrote in message
> >> news:1156887265.027415.184190@.p79g2000cwp.googlegroups.com...
> >> > Hi everyone,
> >> >
> >> > We are running a SQL Server 2000 instance which is getting hit with
> >> > hundreds of queries a minute. The vast majority of these are very
> >> > short-lived, low overhead queries. Some of them are highly resouce
> >> > intensive (< 1%). My problem is that lately, our SQL Server CPU
> >> > utilization has climbed way up and I can't begin to figure out what is
> >> > causing it... the high volume of quick queries, or the low volume of
> >> > slow queries.
> >> >
> >> > I'm fairly skilled at SQL Server performance tuning, but this has got
> >> > me stumped. How can I isolate the category of queries are causing all
> >> > of the activity? I've tried using profiler, but it displays each of the
> >> > queries individually ... there's no way to group activity together by
> >> > meaningful categories (that I'm aware of).
> >> >
> >> > Can anyone point me in the right direction?
> >> >
> >>
> >> The basic technique here is to use profiler or a server trace to gather
> >> execution statistics for individual queries over a window of time. Then
> >> load the results into a table and analyze them. For instance, grouping
> >> by
> >> query text (or truncated or scrubbed text) and then summing the IO and
> >> CPU
> >> statistics. This will isolate the queries driving the CPU use.
> >>
> >> David
> >

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would lik
e to create a named instance.
The installation appears to work OK but I can't register the NAMED instance
in Enterprise Manager. The LOCAL instance is working OK and is registered in
Enterprise Manager.
Each time I try to register the NAMED instance I get the error SQL SERVER do
es not exist or access denied. ConnectionOpen(Connect()).
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows XP
Any help is appreciated.
jimThis may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I corrected the problem. I needed to clear my registry entries for a prior i
nstallation of INSTANCE.
thanks for your help

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would like to create a named instance.
The installation appears to work OK but I can't register the NAMED instance in Enterprise Manager. The LOCAL instance is working OK and is registered in Enterprise Manager.
Each time I try to register the NAMED instance I get the error SQL SERVER does not exist or access denied. ConnectionOpen(Connect()).
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows XP
Any help is appreciated.
jim
This may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I corrected the problem. I needed to clear my registry entries for a prior installation of INSTANCE.
thanks for your help

Developer Edition Installation - Named Instance can't be opened

I am trying to install the Developer's Edition of SQL SERVER and I would like to create a named instance
The installation appears to work OK but I can't register the NAMED instance in Enterprise Manager. The LOCAL instance is working OK and is registered in Enterprise Manager
Each time I try to register the NAMED instance I get the error SQL SERVER does not exist or access denied. ConnectionOpen(Connect())
I see the instance existing in my SQL SERVER installation directory.
I am installing this in Windows X
Any help is appreciated
jimDid you do an advanced install?
>--Original Message--
>I am trying to install the Developer's Edition of SQL
SERVER and I would like to create a named instance.
>The installation appears to work OK but I can't register
the NAMED instance in Enterprise Manager. The LOCAL
instance is working OK and is registered in Enterprise
Manager.
>Each time I try to register the NAMED instance I get the
error SQL SERVER does not exist or access denied.
ConnectionOpen(Connect()).
>I see the instance existing in my SQL SERVER installation
directory.
>I am installing this in Windows XP
>Any help is appreciated.
>jim
>.
>|||This may be basic but are you trying to register it using the following
name:
<machinename\instancename>.
If you are and it is still failing. Look at the SQL Server errorlog form
that instance and verify that it is listening on shared memory, TCP/IP and
named pipes. If you are trying to register it on the server itself shared
memory is used by default. Trying regiistering it uisng the IP address and
port number that SQL Server is listening on.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I corrected the problem. I needed to clear my registry entries for a prior installation of INSTANCE
thanks for your help