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.

How to find out recent differential backup history in SQL Server

-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() and s.type<>'L' and s.type='I'-- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

How to fix orphaned SQL database users

To find out Orphaned users

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user then run below command:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by below command

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

update stats modified date in SQL server

SELECT  sp.stats_id ,
        OBJECT_NAME(s.object_id) AS tablename ,
        s.name ,
sp.last_updated,
        sp.rows ,
        sp.rows_sampled ,
        CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
        CASE WHEN sp.modification_counter = 0 THEN 0
             ELSE CAST(sp.modification_counter AS NUMERIC)
                  / CAST(sp.rows AS NUMERIC) * 100
        END AS Percent_changed ,
        CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM    sys.stats AS s
        CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
        AS sp
WHERE   sp.last_updated <= DATEADD(DAY, -1, GETDATE())
        AND sp.rows > 10000