Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.com/performance_monitor_counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
Simon
Hi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.co...ounters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>
|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

Bench Mark on Performance

Hi
I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
I understand that the profiler looks at the logical reads and physical
writes.
What I'm trying to understand is what people use as a guide as to
whether something should be dedicated or if it could be shared on an
active/passive clustered environment.
If I'm recieving a million reads per hour, they are logical, hence in
the buffer cache, but does that mean the server is performing well? I
know that it's not always down to just read and writes but that is the
essence of a db system... so that's where I start.
Your thoughts would be most appreciated.
SimonHi
I'd also look at Performance Monitor to examine PhysicalDisk Object: Avg.
Disk Queue Length
http://www.sql-server-performance.c...counters_io.asp
Per a query you can use SET STATISTICS IO command
When looking at individual queries ,the Logical Reads is more interesting
because the ibfo is consistent
Pay close attention as well to chache-hit ratio. (>=98%)
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>|||Simon,
This is where a benchmark for your system would help. This would provide
you with how the system operates during normal operations. The performance
values derived from the benchmark can be compared against over time to
establish changes in the environment and to proactively predict future HW
upgrades/additions.
HTH
Jerry
<simonaarons@.yahoo.co.uk> wrote in message
news:1129024856.929599.263150@.o13g2000cwo.googlegroups.com...
> Hi
> I'm looking quite heavily at SQL Server Profiler (2000 sp3a).
> I understand that the profiler looks at the logical reads and physical
> writes.
> What I'm trying to understand is what people use as a guide as to
> whether something should be dedicated or if it could be shared on an
> active/passive clustered environment.
> If I'm recieving a million reads per hour, they are logical, hence in
> the buffer cache, but does that mean the server is performing well? I
> know that it's not always down to just read and writes but that is the
> essence of a db system... so that's where I start.
> Your thoughts would be most appreciated.
> Simon
>

BEGIN TRAN . . . WITH MARK . . .

Hallo All,

Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?
On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).
On both systems I try to execute the following statements:
BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'
USE PROD
INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
USE ACCT
INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])
VALUES('PROT_COMMENT', 1004, 1004)
COMMIT TRAN TRAN_01
After executing the statements I start the following query:
SELECT * FROM [msdb].[dbo].[logmarkhistory]
On MS SQL 2000 I get as results:
PROD TRAN_01 My TRAN_01 SUPPORT\Administrator 3944000000107600001 2006-03-29 17:15:13.930
ACCT TRAN_01 My TRAN_01 SUPPORT\Administrator 8000000009200001 2006-03-29 17:15:13.930
Seems to be correct. I think it is the way it should work according to the documentation.

On MS SQL 2005 I only get the following results:
PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283
There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.
It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.See the Books Online Topic "Ensuring Recoverability of Related Databases". I believe that there is a change in SQL Server 2005.|||Hello ALL,
I have the solution now.
It have nothing to do with some changes in SQL Server 2005.
It seems just to be the following. I habe never made a backup
of the ACCT database in the past. After I executed the full
online backup of the ACCT database for the first time I get
exactly the same results as with MS SQL 2000.