Monitor user login connected to SQL Server

Monitor user login connected to SQL Server

 

SA and Windows Administrator login should be disabled in SQL Server due to security reason. I am not going to discuss those reasons in this article as there are many pro/cons and rules/regulations of some industry associated with it. Once we start debate on this, it is never ending so there is no meaning to raise this issue in this article.

 

I, personally, believe that SA and Administrator account either should be disabled or should not be used until real emergency comes when we MUST have to use it.

 

I have my monitoring application developed in Asp.Net which keeps an eye on every aspects of SQL Server I need. One of them is to check whether any developer or application is using SA or Administrator account. I use following SQL Server view which keeps an eye on this.

[sourcecode language=”sql”]

CREATE VIEW View_GetLoginDetails

AS

SELECT TOP 5

Host_Name as HostName,

login_name AS LoginName,

count(*) as Connections,

CASE

WHEN login_name = ‘sa’ OR login_name LIKE ‘%administrator%’ THEN ‘red’

ELSE NULL

END AS BackColor

FROM

sys.dm_exec_sessions

GROUP BY

Host_Name, login_name

ORDER BY

BackColor DESC, Connections DESC

[/sourcecode]

Few other links for DBA to check for different important things:

  • Capture Schema Change in SQL Server to keep up history (Click Here)
  • Capture long running query in SQL Server (Click Here)
  • Analyze IO disk pressure in SQL Server (Click Here)
  • Transactions/Sec and Batch Requests/Sec performance counter in SQL Server (Click Here)
  • Find Disk performance with sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions (Click here)
  • Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server (Click Here)
  • Find locked table name with duration and count in SQL Server (Click Here)
  • Find Blocking in SQL Server and use it to send an alert (Click Here)

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .

Capture long running query in SQL Server

Capture long running query in SQL Server

Capture long running query in SQL Server, finds most resource intensive query/SP and fix it to meet optimal performance is one of the prime responsibility as DBA has to keep up the performance of SQL Server and use the ideal resource available on the server. Database Administrator (DBA) man not be in front of computer 24*7 but it is his duty to capture  all long running processes / query / Stored Procedure to find and fix later on.

There are many way to do this task and out which, I am going to show one of my favorite way to capture long running query.

Note: Before we move further for this article, I would like to update all of you that I have used the view “WhatIsGoingOn” as a base script for this article so if you don’t have that view or you have not read that article, I will highly recommend to read that article, click here to read it.

Once you read the article I recommended in above paragraph, let us continue with this article.

Create table “LongRunningCapture” in your database along with two indexes (one clustered index and one non clustered index). “LongRunningCapture” is going to be huge table once you collect your long running data for few weeks or months so these indexes will help you to select data faster from your table “LongRunningCapture”. Data growth will be depending on how many time you will run the stored procedure we will create and how many long running queries you get based on the criteria you select.

Anyway, have a look at the table along with all its fields and index to create it in your server.

[sourcecode language=”sql”]
CREATE TABLE LongRunningCapture
(
ID INT IDENTITY(1,1)
,ObjectName VARCHAR(100)
,statement_text  VARCHAR(MAX)
,DatabaseName  VARCHAR(50)
,CPU_Time  INT
,RunningMinutes  INT
,Percent_Complete  INT
,RunningFrom  VARCHAR(100)
,RunningBy  VARCHAR(250)
,SessionID  INT
,BlockingWith  INT
,reads  INT
,writes  INT
,[program_name]  VARCHAR(250)
,login_name  VARCHAR(50)
,status  VARCHAR(25)
,last_request_start_time  DATETIME
,logical_reads  BIGINT
)
GO

CREATE CLUSTERED INDEX IDX_LongRunningCapture_ID ON LongRunningCapture(ID)
GO

CREATE NONCLUSTERED INDEX IDX_LongRunningCapture_last_request_start_time ON LongRunningCapture(last_request_start_time)
GO
[/sourcecode]

Once you will done with creating the table “LongRunningCapture” and index, make sure you have “WhatIsGoingOn” view available. If you don’t have it, create it with following script.

[sourcecode language=”sql”]
CREATE VIEW WhatIsGoingOn
AS
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads

FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
[/sourcecode]

Once you have “LongRunningCapture” table and “WhatIsGoingOn” view ready with you, please create stored procedure given in following TSQL snippet.

[sourcecode language=”sql”]CREATE PROC SPGet_LongRunning
AS
IF OBJECT_ID(‘tempdb..#CaptureLongProcessWithObjectName’) IS NOT NULL
DROP TABLE #CaptureLongProcessWithObjectName

–Capturing All long running operations in Temp table
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
INTO
#CaptureLongProcessWithObjectName
FROM
WhatIsGoingOn
WHERE
–want to capture each query/SP which is taking more than 1 min.
–you can increase limit as per your need
RunningMinutes>1

–Please note that full/differential/log backup may take more time
–if you wish, you can exclude it by filtering either by ObjectName
–or by Statement_Text as per your need

–separating record in two temp table
–based on with and without ObjectName
IF OBJECT_ID(‘tempdb..#CaptureLongProcessWithoutObjectName’) IS NOT NULL
DROP TABLE #CaptureLongProcessWithoutObjectName

SELECT
*
INTO
#CaptureLongProcessWithoutObjectName
FROM
#CaptureLongProcessWithObjectName
WHERE
ObjectName IS NULL

DELETE FROM
#CaptureLongProcessWithObjectName
WHERE
ObjectName IS NULL

ALTER TABLE #CaptureLongProcessWithObjectName
ADD AutoID INT IDENTITY(1,1)

ALTER TABLE #CaptureLongProcessWithoutObjectName
ADD AutoID INT IDENTITY(1,1)

DECLARE @Count INT,@CountMax INT
SET @Count=1

SELECT @CountMax=COUNT(*) FROM #CaptureLongProcessWithoutObjectName

–if there is no ObjectName came by default
–generating ObjectName,
–generally it happens in Ad-Hoc query of in some system processes
WHILE @Count <= @CountMax
BEGIN
DECLARE @Session INT
SELECT
@Session=SessionID
FROM
#CaptureLongProcessWithoutObjectName
WHERE AutoID=@Count

DECLARE
@InputBuffer TABLE
(
EventType NVARCHAR(MAX),
Parameters NVARCHAR(MAX),
EventInfo NVARCHAR(MAX)
)
DECLARE @Command NVARCHAR(MAX),@ObjectName VARCHAR(MAX)

SELECT  @Command = ‘DBCC INPUTBUFFER(‘ + CAST(@Session AS VARCHAR) + ‘) WITH NO_INFOMSGS’

INSERT INTO @InputBuffer
EXEC (@Command)

SELECT
@ObjectName = LEFT(REPLACE(REPLACE(REPLACE(EventInfo, CHAR(13), ”), CHAR(10),”), ‘  ‘, ‘ ‘),50) + ‘…..’
FROM @InputBuffer

UPDATE
#CaptureLongProcessWithoutObjectName
SET
ObjectName=@ObjectName
WHERE
AutoID=@Count

SELECT @Count=@Count+1
END

–inserting all long running query/sp into table
INSERT INTO LongRunningCapture (
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
)
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
FROM
#CaptureLongProcessWithObjectName
UNION ALL
SELECT
ObjectName
,statement_text
,DatabaseName
,CPU_Time
,RunningMinutes
,Percent_Complete
,RunningFrom
,RunningBy
,SessionID
,BlockingWith
,reads
,writes
,[program_name]
,login_name
,status
,last_request_start_time
,logical_reads
FROM
#CaptureLongProcessWithoutObjectName
GO [/sourcecode]

Now you are almost ready to start capturing your long running queries / stored procedure / processes. You are just one step behind; let us complete that last step.

You have to schedule above given SP “SPGet_LongRunning” in your SQL Server Job or Windows Schedule tasks with help of SSIS package to run it at every 2 minutes to capture any process / job / stored procedure / query which are running for more than 1 minute.

You increase your limit of capture from stored procedure “SPGet_LongRunning” as well as in Job. I generally prefer to run it at every 2 minute but in this case you will have too much data to handle in “LongRunningCapture” is you have slow performing server. You have to make policy to clean up old data in “LongRunningCapture” table.

You might delete two-week old data from “LongRunningCapture” table.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Monitoring SQL Azure Database & Server

Monitoring SQL Azure Database & Server

By far I have written quite a few articles to monitor and manage SQL Server (find it here under “DBA Related Articles” section) but what about SQL Azure?

Note: if you are new to Cloud and SQL Azure, do look at “Microsoft Windows / SQL Azure:” section to get understanding from scratch.

Microsoft has very good concept of system views to provide the very important and crucial information to the admin. SQL Azure is not an exception. Herewith, I am going to explain some of common and very powerful system views which cater you with so many important information you required to accomplish your admin task in SQL Azure.

sys.dm_exec_connections : This view will return a row per each connection made toward SQL Azure database. Some of the important fields are session_id, connect_time, num_read, num_write, last_read, last_write and many more.

sys.dm_exec_sessions: This DMV will return all the active connection no matter whether it is user connection or system’s connection for internal task. Important fields from this DMV are session_id, login_time,  host_name, program_name, login_name

sys.dm_exec_requests: This system view will cater your need to know each request comes to your database. Some of the important fields are session_id, start_time, status, command, database_id, user_id, blocking_session_id, wait_type, percent_complete.

sys.dm_tran_database_transactions: This DMV will return database level transaction; here is the list of important columns from this DMV: transaction_id, database_id, database_transaction_begin_time, database_transaction_type, database_transaction_state.

sys.dm_db_partition_stats: This DMV returns page & row-count information for each partition in current database. Some of the important fields from this DMV are partition_id, object_id, used_page_count, reserve_page_count, row_count to name a few.

I will have detail follow up article which will return meaningful information by joining few of the DMVs together till then have fun !!!

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Find Blocking in SQL Server and use it to send an alert

Find Blocking in SQL Server and use it to send an alert:

As a DBA, I am always interested to find whether blocking is going on, in my environment or not? How long blocking exists? Which are the queries/SPs creating blocking etc.

Blocking generally occurs when one SQL Server connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

Sometime It may happens that blocking gets converted into deadlock situation where neither of both connection able to finish its job and one of the connection identified as deadlock victim by SQL Server. In short, Locking, Blocking and deadlocking is not good to keep. This is the reason I always eager to know the details about blocking happening in my system.

Few days back, I have created one SQL Server View “WhatIsGoingOn” which was using sys.dm_exec_requests and sys.dm_exec_sessions DMVs to find information about currently running processes in SQL Server instance along with sessionID and blocking Session ID, if any. I am going to use “WhatIsGoingOn” SQL Server View in one SP to find only blocked session and insert its detail into following SQL Server table.

[sourcecode language=”sql”]
CREATE TABLE tblBlockingDetails

(

ID INT IDENTITY(1,1),

ObjectName SYSNAME NULL,

BlockingObjectName SYSNAME NULL,

StatementText VARCHAR(MAX),

BlockingStatementText VARCHAR(MAX),

SessionID INT,

BlockingSessionID INT,

Status VARCHAR(50),

BlockingStatus VARCHAR(50),

DateCreated DATETIME

)[/sourcecode]

After having above table in my database, I will create following Stored Procedure which will capture details of any session being locked and insert that detail in “tblBlockingDetails” table.

[sourcecode language=”sql”]
CREATE PROC uspInsertBlockingRecords

AS

INSERT INTO tblBlockingDetails

SELECT

W1.ObjectName AS ObjectName,

W2.ObjectName AS BlockingObjectName,

W1.Statement_Text AS StatementText,

W2.Statement_Text AS BlockingStatementText,

W1.sessionid AS SessionID,

W2.sessionid AS BlockingSessionID,

W1.status AS Status,

W2.status AS BlockingStatus,

GETDATE() AS DateCreated

FROM

WhatIsGoingOn W1 INNER JOIN WhatIsGoingOn W2

ON

W1.BlockingWith > 0

AND W1.BlockingWith = W2.sessionid

[/sourcecode]

Now, I can schedule SP “uspInsertBlockingRecords” every few minutes for business hours in SQL Server Job Agent so that I can see details of blocking anytime I wish from table “tblBlockingDetails”. I can even set email schedule either to get information about blocking, if I wish.

If you want to create DeadLock situation forcefully to check its behavior, you can use the script given in one of my past article here.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find locked table name with duration and count in SQL Server

Finding LOCK table in SQL Server is one of essential task of DBA. Locked table for long time in SQL Server adversary affect on performance of the application, especially in pick business hours.

I have one SQL Server VIEW which help us to find all the table which are being locked currently along with duration since it is being locked.

[sourcecode language=”sql”]
CREATE VIEW WhatIsLocked
AS
SELECT
Locks.request_session_id AS SessionID,
Obj.Name AS LockedObjectName,
DATEDIFF(second,ActTra.Transaction_begin_time, GETDATE()) AS Duration,
ActTra.Transaction_begin_time,
COUNT(*) AS Locks
FROM    sys.dm_tran_locks Locks
JOIN sys.partitions Parti ON Parti.hobt_id = Locks.resource_associated_entity_id
JOIN sys.objects Obj ON Obj.object_id = Parti.object_id

JOIN sys.dm_exec_sessions ExeSess ON ExeSess.session_id = Locks.request_session_id
JOIN sys.dm_tran_session_transactions TranSess ON ExeSess.session_id = TranSess.session_id
JOIN sys.dm_tran_active_transactions ActTra ON TranSess.transaction_id = ActTra.transaction_id
WHERE   resource_database_id = db_id() AND Obj.Type = ‘U’
GROUP BY ActTra.Transaction_begin_time,Locks.request_session_id, Obj.Name
[/sourcecode]

You can use this view to keep automated watch. you can query this view every few minutes in database in business hours and if find lock bigger then certain duration, send email with detail. You can even keep watch by putting this query in web/windows application which keeps refreshing every few seconds and show details of locking.

I have written one more article in past which displays lock table, table with  ‘SCH-M’ lock and which command causing the lock. read article in detail, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

use sys.dm_exec_requests and sys.dm_exec_sessions to find who are logged in to your SQL Server Instance

Many time we need to find who are logged in to SQL Server instance with which tool/program/software. SQL Server gives this very useful information to us with following two DMVs.

sys.dm_exec_requests
sys.dm_exec_sessions

I have written VIEW in my SQL Server Instance to capture all these necessary information.

here is the TSQL script for the same:

[sourcecode language=”sql”]
CREATE VIEW WhoisLoggedIn
AS
SELECT
ISNULL(sess.host_name, ”) as host_name,
ISNULL(DB_NAME(req.database_id) , ”)  as database_Name,
ISNULL(sess.program_name, ”)   as ProgramName,
ISNULL(sess.login_name, ”)  as login_name,
ISNULL(CAST(req.start_time AS VARCHAR), ”)  as start_time,
DATEDIFF(MINUTE,ISNULL(req.start_time,GETDATE()),GETDATE())  as RunningMinutes,
req.Session_id

FROM
sys.dm_exec_requests req
INNER JOIN
sys.dm_exec_sessions sess
ON
req.session_id=sess.session_id
WHERE
–will eliminate all system processes
req.session_id >= 50
AND
sess.host_name IS NOT NULL
[/sourcecode]

Once you create this VIEW in your SQL Server, you don’t need to remember complex query but to use only user friendly name “WhoisLoggedIn” with standard SELECT statement like this:

[sourcecode language=”sql”]
SELECT * FROM WhoisLoggedIn
[/sourcecode]

Note: I have written one blog post in past which was finding currently running processes in SQL Server with the help of above given two DMVs.  click here to read it.

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations

Find currently running session/process in SQL Server database/instance with help of sys.dm_exec_requests and sys.dm_exec_sessions

As a DBA, it is mandatory to keep an eye on your database and server too, especially during the business hour. SQL Server providing very helpful Dynamic Management View to help in this situation. Dynamic Management View (DMV) are as follow:

sys.dm_exec_requests
sys.dm_exec_sessions

I have created one SQL Server view with the help of following script which I used to use, often, during business hours to check whether everything is going smooth or not.

 

[sourcecode language=”sql”]
CREATE VIEW WhatIsGoingOn
AS
SELECT
OBJECT_NAME(objectid) as ObjectName
,SUBSTRING(stateText.text, (statement_start_offset/2)+1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(stateText.text)
ELSE statement_end_offset
END – statement_start_offset)/2) + 1) AS statement_text
,DB_Name(database_id) as DatabaseName
,req.cpu_time AS CPU_Time
,DATEDIFF(minute, last_request_start_time, getdate()) AS RunningMinutes
,req.Percent_Complete
,sess.HOST_NAME as RunningFrom
,LEFT(CLIENT_INTERFACE_NAME, 25) AS RunningBy
,sess.session_id AS SessionID
,req.blocking_session_id AS BlockingWith
,req.reads
,req.writes
,sess.[program_name]
,sess.login_name
,sess.status
,sess.last_request_start_time
,req.logical_reads

FROM
sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions sess ON sess.session_id = req.session_id
AND sess.is_user_process = 1
CROSS APPLY
sys.dm_exec_sql_text(sql_handle) AS stateText
[/sourcecode]

Now, whenever I want to see the status whether anything is blocking to my query/queries or which query/SP is being executed from which time. How many reads/writes happening by each objects. if there is a task like backup/restore, I can even see, how many percent complete by just executing one simple SELECT statement:

 

[sourcecode language=”sql”]
SELECT * FROM WhatIsGoingOn
[/sourcecode]

If you wish you can, even, use this VIEW in your .NET web page/windows application so that you don’t even need to connect to database directly to see the status.

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations