How to check SQL Services status in SQL Server

As a DBA, it is very important for all of us to make sure SQL Services are up and running.But Is there an easy way to get this information in SQL Server?

The answer is simple, yes. Microsoft has introduced a new DMVs – sys.dm_server_services. dm_server_services DMV is available right from SQL Server 2008R2 (SP1). This has made life easy for most of DBAs from writing a bunch of codes.

SELECT 
	SERVICENAME, STARTUP_TYPE_DESC, STATUS_DESC,
        LAST_STARTUP_TIME,SERVICE_ACCOUNT,
        IS_CLUSTERED ,CLUSTER_NODENAME 
FROM SYS.DM_SERVER_SERVICES

ServiceCheck

Notes:

1. Login needs to be provided with VIEW SERVER STATE PERMISSION
2. Support is available for SQL Server 2008 R2(SP1) and later versions

Read Also:
SYS.DM_SERVER_SERVICES

Advertisements

Dissecting Blocked-process-Report in SQL Server

Objective:
This post explains How to analyze the blocked-process-report captured using Event Notification in detail,However, this does not cover the configuration/set up of the Event notification in SQL Server.

Let us look at a typical report captured. The report has been logically divided into three sections to make our analysis simple.

First Section:
BPR_Section1

The above image shows the first section which contains
EventType – Event type of the Report which is BLOCKED_PROCESS_REPORT
PostTime – It is the date and time that the event posts
SPID – Session ID(Server Process ID) information

Second Section:
BlockedProcess

Second section contains two major tags – Blocked-process and Blocking-process.

In Blocked Process, we can first look at the waitresource attribute. Here in the above image waitresource=”KEY: 160:72057595494989824 (09dec3797d4c)”.


KEY- represents the lock on the object is at Key level

160- The second section is the database id.

		To get the database name, you can use the below query:
		Select DB_NAME(160)--DBNAME

72057595494989824- This section can be used to identify the associated object with the blocking as below.
				
		Select OBJECT_NAME(object_id) From sys.partitions where hobt_id =72057595494989824


(09dec3797d4c)	- This represents the actual data. This can be queried on the associated objects using %%lockres%% 

		Select * From <> Where %%lockres%% = '(77576a23d4db)'


Note: %%lockres%% is an undocumented virtual column to identify the key hash value. As this is an undocumented feature, Microsoft will no longer support its usage in production environment, hence no warranties.

From the above, we would be able to get the type of lock,Databasename, the associated object and data.

Further, let us look at the inputbuf tag.


Proc [Database Id = 160 Object Id = 2114374238]
Proc [Database Id = 160 Object Id = 1354487904] 

- This section provides the query/procedure information.
Select * From sys.procedures where object_id=2114374238 
Select * From sys.procedures where object_id=1354487904 

Third Section:

BPR_Section3

Finally, the third section includes other basic information like DatabaseID, TransactionID, Duration, StartTime, EndTime, ObjectID, IndexID, ServerName, Mode, LoginSID, EventSequence, IsSystem and SessionLoginName.

Hope, this post will help you to identify some important information from the blocked process report that can be used for troubleshooting your blocking/locking issues.

Have your thoughts on the same as part of our learning an sharing....Have a good day ahead!

UNIQUEIDENTIFIER and Search behavior in SQL Server

This post explains about a different behavior with search for UNIQUEIDENTIFIER datatype column in SQL Server.

If you would have worked with UNIQUEIDENTIFIER before, you would have come across this scenario earlier. But for those not, let us see the scenario.

CREATE TABLE TEST (COLUMN1 UNIQUEIDENTIFIER)
INSERT INTO TEST VALUES(‘F7ABC3AA-0534-44F3-BDBB-0011CEFB6993′)

–Search a value in the column by adding “extra” characters
SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB6993.XML’

You will expect the search will not return any data as it can not find the exact match, however, it returns results!!!

Unique

What is reason for this strange behaviour?

No, this is not a strange behaviour, its an intended and default behaviour in SQL Server for UNIQUEIDENTIFIER.

Let us look at what happens behind the scene.First step is enable the execution plan and execute the SELECT query.

ExePlan_Unique

As per the execution plan, SQL Server does an Implicit conversion of the value to uniqueidentifier before the search happens.As a result, only first 36 characters is being used for the search condition and returns the results.Any characters more than 36, would be ignored in the search condition.

For completeness, Please note that if we are passing a value that can not be converted as unique identifier, it will throw an exception as below.

SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB699′

Msg 8169, Level 16, State 2, Line 5
Conversion failed when converting from a character string to uniqueidentifier.

UniqueError

Hope you enjoyed this post, have your thoughts on the same.