No, transactions are written to data pages in the cache and these pages are
dirty. When a checkpoint happens all dirty pages whether they are part of a
committed or uncommitted transaction are written to disk.
When you query you query pages in the data cache and sometimes on disk and
the query engine knows to query committed transactions or uncommitted ones
(nolock).
The transaction log is just a record of what is dirtied and when checkpoints
happen (and other events like the replication point - what has been read
from the log and written to the distribution database).
From what I understand it is more efficient to query committed transactions
as opposed to using the nolock option but the no locking option does not
respect held locks.
Database mirroring, while consuming minimal host and network resources
really does not factor in here at all.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com [quoted text, click to view] "Methodology" <Methodology@discussions.microsoft.com> wrote in message
news:BA752336-BDC2-4E2A-8B4F-A3019A56211C@microsoft.com...
> Hi
>
> So imagine the scenario - async SQL 2005 mirroring and the mirror server
> goes offline. accordingly the transaction log of uncommitted stuff grows
> on
> the primary.
>
> To what extent does this effect the efficency of running queries against
> the
> primary?
>
> Any query will need to check data in both the hardened database on disk
> and
> the trans log to make sure that it retrieves the right info, correct?
> surely
> uncommited trans may affect the committed data and so both sources need to
> be
> chekced? logically something like this MUST happen? and the bigger the
> trans
> log grows ie the longer the mirror remains offline the more inefficient
> the
> primary would become at servicing client query requests?
>
> still trying to dig my way out of being stuck with sync miroring on sql
> 2005
> standard. if the efficiency of answering queries takes a nose dive in this
> scenario, then im stuffed.
>
> THanks
> Alastair
> Methodology
