Friday, March 23, 2012
Prod database counters
How can I get "Transactions per second" and "write transactions percent"
values for a production database ?. I tried to find in the profiler but did
not see an event that would give me this info.
Thanks for any help.Those come from Perfmon not profiler.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> SQL Server 2000 SP4
> How can I get "Transactions per second" and "write transactions percent"
> values for a production database ?. I tried to find in the profiler but
> did
> not see an event that would give me this info.
> Thanks for any help.
>|||Thanks Andrew............ What do I need to choose in Performance
Monitoring ?
"Andrew J. Kelly" wrote:
> Those come from Perfmon not profiler.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> > SQL Server 2000 SP4
> >
> > How can I get "Transactions per second" and "write transactions percent"
> > values for a production database ?. I tried to find in the profiler but
> > did
> > not see an event that would give me this info.
> >
> > Thanks for any help.
> >
>|||Well the databases counters will have Transactions Per Second for each
database but I don't know what you mean by the other. If you want to see the
difference between requests and things that actually begin a transaction you
can monitor Batch Requests Per Second and Trans Per Second.
Some of these may be of interest:
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> Thanks Andrew............ What do I need to choose in Performance
> Monitoring ?
>
> "Andrew J. Kelly" wrote:
>> Those come from Perfmon not profiler.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
>> > SQL Server 2000 SP4
>> >
>> > How can I get "Transactions per second" and "write transactions
>> > percent"
>> > values for a production database ?. I tried to find in the profiler but
>> > did
>> > not see an event that would give me this info.
>> >
>> > Thanks for any help.
>> >
>>|||Thanks.................
"Andrew J. Kelly" wrote:
> Well the databases counters will have Transactions Per Second for each
> database but I don't know what you mean by the other. If you want to see the
> difference between requests and things that actually begin a transaction you
> can monitor Batch Requests Per Second and Trans Per Second.
> Some of these may be of interest:
> http://www.sql-server-performance.com/sql_server_performance_audit10.asp
> Performance Audit
> http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.com/sql_server_performance_audit.asp
> Hardware Performance CheckList
> http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
> Disk Monitoring
> http://sqldev.net/misc/WaitTypes.htm
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:8515ED62-75BF-4B44-AF03-6112174664A7@.microsoft.com...
> > Thanks Andrew............ What do I need to choose in Performance
> > Monitoring ?
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Those come from Perfmon not profiler.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> >> news:1456F10A-B293-48C1-9AFF-BB3FB5906F76@.microsoft.com...
> >> > SQL Server 2000 SP4
> >> >
> >> > How can I get "Transactions per second" and "write transactions
> >> > percent"
> >> > values for a production database ?. I tried to find in the profiler but
> >> > did
> >> > not see an event that would give me this info.
> >> >
> >> > Thanks for any help.
> >> >
> >>
> >>
>
Friday, March 9, 2012
Process SQL Transactions? Easy Question :)
Environment - VB.NET, ASP.NET, SQL Server 2000.
In SQL 2000, I am sending an XML, which carries data for two tables. Let's say, I am inserting half of the fields in TABLE1 and rest in TABLE2.
Specifically, I want to use Transaction Processing for inserting the rows in both tables. If insertion in one table fails, any inserted data should ROLLBACK and come out of procedure with relevant error code.
Please advice or send me any example links. Thanks
PankajHave you looked at the Begin Tansaction examples in the BOL? That contains an example with the commit transaction but not rollback transaction. I'm assuming you'll do the inserts in one SP. If that's true then you can follow those examples to help do your inserts and then test for errors and rollback if errors are encountered. Something like
SP...
BEGIN TRANSACTION
Insert code here
IF @.@.Error <> 0
ROLLBACK TRANS
ELSE
COMMIT TRANS
That's a rough idea but hopefully will get you started.