This is such a broad topic, and most of the DMV's work together to give you a better insight into performance problems. The Microsoft whitepaper "Troubleshooting Performance Problems...

The sys.dm_exec_query_stats is a central DMV to find the statistics of the current performance of cached query plans.

The transaction log is used for all modifications to guarantee atomicity, durability and consistency. Read Gail's thorough article.

http://qa.sqlservercentral.com/articles/Administration/64582/

Atomicity is a database principle(all or nothing), it is not to say it can/cant belong to something like a statement. You probably meant that they...

GilaMonster (9/20/2012)

The create table doesn't roll back for the same reason the create table doesn't roll back in this example

BEGIN TRAN

CREATE TABLE SillyTable (SomeVal int)

INSERT INTO SillyTable

SELECT 1/0

COMMIT TRANSACTION

A...

Yes, I think you are right on the "extreme" definition, but he is clearly wrong if he states...

Yes, for...

The other possible query I can think of is SELECT INTO. This wont guarantee Atomicity and Consistency as the INTO table might still be created even if the transaction...

I do not understand what you mean by batch based. You can issue only one delete statement with the datetimestamp in the WHERE clause and it will be one batch.

If...

Try to see which transaction(s) are taking up the space and kill it if possible..

You can find out with the DMV's with something like this (ordered by log space used)

Select...

Try using the INTO statement if you can. This will only log the allocations and not the individual rows in the simple recovery as well as the bulked loged model:

SELECT

column1,

column2

INTO...

If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and...

Hi Gail,

Thanks for the great article..

One question:

One side effect of this requirement that both log records and modified data pages are written to disk before the transaction commits is that...

DECLARE @ParentChild AS TABLE

(

RowID INT

, ParentID INT

, ChildID INT

, WantThisRow VARCHAR(3)

)

DECLARE @GrandChild AS TABLE

(

RowID INT

, ChildParentIDINT

, GrandchildID INT

)

INSERT INTO @ParentChild

(RowID, ParentID, ChildID, WantThisRow)

SELECT 2310, 22402, 22403,...

I really don’t blame you. Transactions and isolation can be a very difficult and confusing, and BOL does...