How to find out root blocker in SQL server

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

How to check table update stats for particular table or object

SELECT OBJECT_NAME(object_id) AS [ObjectName]
      ,[name] AS [StatisticName]
      ,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats
where OBJECT_NAME(object_id) in ('tablename')
order by STATS_DATE([object_id], [stats_id]) desc

How to check sub query running through any session id

SELECT  SUBSTRING(detail.text,
                  requests.statement_start_offset / 2,
                  (requests.statement_end_offset - requests.statement_start_offset) / 2)
FROM    sys.dm_exec_requests requests
CROSS APPLY sys.dm_exec_sql_text (requests.plan_handle) detail
WHERE   requests.session_id = 2534

How to check query execution plan

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts > 1
AND text LIKE '%put any text%'
AND objtype = 'Proc'
ORDER BY usecounts ASC;

Check tempdb used by different processes

SELECT TOP 10 session_id, database_id, user_objects_alloc_page_count + internal_objects_alloc_page_count / 129 AS tempdb_usage_MB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC

How to check AG delay in SQL Server

SELECT  ar.replica_server_name, max(datediff(MINUTE, drs.last_commit_time, getdate())) as Delay_inMin

FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id

AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id

AND drs.replica_id = ar.replica_id WHERE adc.database_name = 'DatabaseName'  --And replica_server_name <> 'UHSCMPRDROSQL01' And drs.is_local <> 1

group by  ar.replica_server_name 

How to move ms access database to SQL server

MOVE ACCESS TO SQL

A)  How to migrate access database to SQL server.

Steps:

1)      Copy all the access files from below path and keep it on another folder

2)      Run access migration tool and browse new access file path

3)      Set the target SQL version and click next.

 

4)      Click on Add databases and add access files and click next.

5) Select target SQL server and provide database name( new database will create). Click Next

6) Click on Link Tables and Click Next

7)      Click on Convert, Load and Migrate.

8)      Once migration done, please save the project and close.

9)      Now access database will act as front-end where SQL server will act as backend.