How To Check The Status OF Trace Flags in SQL Server??

As per BOL-
"Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior"

For example Trace Flags 1222 and 1204 are used to identify the deadlock information in SQL server. While trace flags 1211 and 1224 disable lock escalation in different ways.

Once we have enabled that Trace and later on we want to check the satus of that Trace. i.e. It is ON or OFF. we can do this with DBCC TRACESTATUS. 

The syntax for same is as follows:

DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) 
[ WITH NO_INFOMSGS ]

In DBCC TRACESTATUS if you will pass the Trace numberer then you get back the status of that flag only. If you do not pass a flag number in then you get all active trace flags. 

I'm not sure why -1 is there. As it doesn’t seem to do anything. 
As per BOL If -1 is specified without trace#, all the global trace flags that are enabled are displayed. But I do not think this statement is true as it displays the session level flags also when we pass -1.

Let see the Result for same in SSMS:

 --IF WE DO NOT PASS THE TRACE NUMBER THEN IT WILL RETURN THE STATUS FOR ALL TRACE FLAGS 

 DBCC TRACESTATUS() 

Output:

TraceFlag Status Global Session
  1204             1            0             1
  1222             1            1               0
  3604             1            1             0

 --IF WE WILL PASS TRACE NUMBER THEN IT WILL RETURN THE STATUS ONLY FOR THAT TRACE FLAG

 DBCC TRACESTATUS(1222) 

Output:
TraceFlag Status Global Session
1222            1            1              0

Reference: Microsoft BOL

Thanks For Reading this Post!!

How to know when statistics were last updated in SQL Server?

Sometimes the query behaves very badly because of outdated statistics.There are two ways in which we can see the time of last updated statistics for a table or indexed view. 

1: We can get last updated statistics information using DBCC SHOW_STATISTICS().
2: We can  also get last updated statistics information through STATS_DATE() function 
     and sys.stats system catalog view.

Using DBCC SHOW_STATISTICS:

According to Microsoft Books Online, DBCC SHOW_STATISTICS returns the header, histogram, and vector density based on the data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name as below:

USE [<Database_Name>]
GO

DBCC SHOW_STATISTICS (table_or_indexed_view_name 
                     ,index_or_statistics_name_coloumn_name') 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM:

How to Use:

The following TSQL code will returns all statistics information for the PK_RG_REGISTRATION index of the Rg_Registration table.

USE [TestVK]
GO 
DBCC SHOW_STATISTICS('RG_REGISTRATION',PK_RG_REGISTRATION)

The first result set of the DBCC SHOW_STATISTICS command returns the header information, including when the statistics were last updated. In case you need information only about the statistic, you can executed  with STATS_HEADER option.

Using sys.stats system catalog view

According to Microsoft BOL, sys.stats system catalog view is the best way to see each statistics object information that exists for the tables, indexes, and indexed views in the database. This catalog view exists in SQL Server 2005 and later. You can use this system catalog view with STATS_DATE() function, to view most recent update date for each statistics object that exists for the tables, indexes, and indexed views in the database. This function accepts two parameters, that is, object_id, stats_id. To determine date when the statistics where last updated, you execute sys.stats system catalog view with STATS_DATE() function as below:

SELECT OBJECT_NAME(object_id) AS [ObjectName],[name] AS [StatisticName],
STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate] 
FROM sys.stats WHERE OBJECT_ID=OBJECT_ID('RG_REGISTRATION');

Now we can see the query returns date of the  recent update for statistics on a table RG_Registration.


Reference: Microsoft Books Online

Thanks For Reading This Post!!!

How To Identify Sessions With Context Switching In SQL Server??

Context switching is the act of executing T-SQL code under the guise of another user connection, in order to utilize their credentials and level of rights.

By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement.  

We can use our DMV "sys.dm_exec_sessions" To identify the sessions with context switching as below:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

To explain this I have a sample database TestVK. I have logged in with credentials of "vimal". But Now I want to access the rights of user "sa1". T-SQL code for same are as below:

 EXECUTE AS LOGIN ='SA1';
 SELECT TOP 1 * FROM Item_Name

 Now lets execute the DMV "sys.dm_exec_sessions" to see the context switching sessions:

SELECT session_id , login_name , original_login_name
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND login_name <> original_login_name

Output:
session_id login_name original_login_name
80             sa1             vimal

Reference: Microsoft BOL, Red Gate

Thanks For Reading this Post!!!

Books which I Read in last weekend!! Wonderful Books.. You should try to read it once..I'm Sure you will love it!!!

1: Who Moved My Cheese  By: Spencer Johnson

This book will definitely Wakes you up from the sleep in which we are going through life. A short and simple book with powerful message.

2: One Minute Manager By: Kenneth Blanchard 

Just simple steps to work better. I really enjoyed reading this book. I think new and young managers should read it once who want to develop a habit in effective management of people and teams. The techniques are very simple and useful.

Thanks!!!!

How to know “the last time a DBCC CHECKDB was run without errors” in a Database

We all know the importance of DBCC CheckDB command in sql server. If data corruption occurred in a database and you want to know When was the last time DBCC CHECKDB reported no errors for the corrupted database.

If you have saved all CHECKDB results or the ERRORLOG files you can find it from there. 

But in case you have deleted these files the from where you will get to know the information about same???

Not to worry about this question as Along with CHECKDB reports and error log, SQL server saves information about the last time a DBCC CHECKDB was run without errors in the database also.

Let see how can we get that information:

I have a sample database TestVK for this post. Now to see the last known good information of TestVK database we will use dbcc command as below.

DBCC TRACEON(3604,-1)
DBCC DBINFO(TestVK)
DBCC TRACEOFF(3604,-1)

Go to Result and search for "LastKnownGood". You will see the output as below:

dbi_dbccLastKnownGood = 2014-10-27 10:20:33.833

So this is the last time when a DBCC CHECKDB was run without errors.

Thanks For Reading This Post!!