SQL Server – Does SET STATISTICS…ON Affect All Queries or Just Current Connection?

sql serversql-server-2016statistics

In regards to SET STATISTICS TIME ON and SET STATISTICS IO ON I noticed in the remarks of the Microsoft doc, it says the following:

When STATISTICS IO is ON, statistical information is displayed, and
when OFF, the information isn't displayed.

After this option is set ON, all Transact-SQL statements return the
statistical information until the option is set to OFF.

Does that mean all Transact-SQL statements that execute in the current connection, or across the whole server?
When I tested it myself, it seemed to be only within the scope of my connection.

Best Answer

The answer to the question can be found higher up the document tree:

SET statements:

The Transact-SQL programming language provides several SET statements that change the current session handling of specific information. The SET statements are grouped into the categories shown in the following table.

Which explains that all these set statements are applied to the current session.

With the category for SET STATISTICS IO,TIME ON; being statistics statements

Related Solutions

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) = 
   N'DECLARE @C INT; 
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in that instead of providing a reader with the last committed version of the row that was available when the transaction started (SNAPSHOT ISOLATION), a reader gets the last committed version of the row that was available when the statement started (READ_COMMITTED_SNAPSHOT).

~Uri Dimant (emphasis mine), here

Best Answer

Related Solutions

Shared Lock on IsolationLevel.ReadUncommitted in SQL Server

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

Related Question