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

How to check fragmentation status in SQL server

IF OBJECT_ID('tempDB..#bbc') IS NOT NULL
    DROP TABLE #bbc

CREATE TABLE #bbc
    (
      DatabaseName VARCHAR(100) ,
      ObjectName VARCHAR(100) ,
      Index_id INT ,
      indexName VARCHAR(100) ,
      avg_fragmentation_percent FLOAT ,
      Page_Count INT ,
      IndexType VARCHAR(100) ,
      Action_Required VARCHAR(100) DEFAULT 'NA'
    )
go
INSERT  INTO #bbc
        ( DatabaseName ,
          ObjectName ,
          Index_id ,
          indexName ,
          avg_fragmentation_percent ,
 Page_Count,
          IndexType
        )
        EXEC master.sys.sp_MSforeachdb ' USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent,page_count, index_type_desc

-- , record_count, avg_page_space_used_in_percent --(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'
go

UPDATE  #bbc
SET     Action_Required = 'Rebuild'
WHERE   avg_fragmentation_percent > 30
go

UPDATE  #bbc
SET     Action_Required = 'Reorganize'
WHERE   avg_fragmentation_percent BETWEEN 5 AND 30
go

DELETE #bbc
WHERE Page_Count < 25

go


SELECT  *
FROM    #bbc
WHERE   DatabaseName NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) AND Page_Count > 10000
ORDER BY avg_fragmentation_percent

Find Index Name and columns

SELECT  S.NAME SCHEMA_NAME ,
        T.NAME TABLE_NAME ,
        I.NAME INDEX_NAME ,
        C.NAME COLUMN_NAME
FROM    SYS.TABLES T
        INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
        INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID
                                    AND IC.INDEX_ID = I.INDEX_ID
                                    AND IC.COLUMN_ID = C.COLUMN_ID
WHERE t.name = 'name'
ORDER BY I.NAME ,
        I.INDEX_ID ,
        IC.KEY_ORDINAL

GO

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
WHERE ind.name like 'idx_name_id_first_middle_last%'
ORDER BY TableName

How to find duplicate and overlapping indices in SQL server

;WITH CTE_INDEX_DATA AS (
       SELECT
              SCHEMA_DATA.name AS schema_name,
              TABLE_DATA.name AS table_name,
              INDEX_DATA.name AS index_name,
              STUFF((SELECT  ', ' + COLUMN_DATA_KEY_COLS.name + ' ' + CASE WHEN INDEX_COLUMN_DATA_KEY_COLS.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END -- Include column order (ASC / DESC)

                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_KEY_COLS
                                                ON T.object_id = INDEX_DATA_KEY_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_KEY_COLS
                                                ON INDEX_DATA_KEY_COLS.object_id = INDEX_COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA_KEY_COLS.index_id = INDEX_COLUMN_DATA_KEY_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_KEY_COLS
                                                ON T.object_id = COLUMN_DATA_KEY_COLS.object_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.column_id = COLUMN_DATA_KEY_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_KEY_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_KEY_COLS.index_id
                                                AND INDEX_COLUMN_DATA_KEY_COLS.is_included_column = 0
                                  ORDER BY INDEX_COLUMN_DATA_KEY_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS key_column_list ,
          STUFF(( SELECT  ', ' + COLUMN_DATA_INC_COLS.name
                                  FROM    sys.tables AS T
                                                INNER JOIN sys.indexes INDEX_DATA_INC_COLS
                                                ON T.object_id = INDEX_DATA_INC_COLS.object_id
                                                INNER JOIN sys.index_columns INDEX_COLUMN_DATA_INC_COLS
                                                ON INDEX_DATA_INC_COLS.object_id = INDEX_COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_DATA_INC_COLS.index_id = INDEX_COLUMN_DATA_INC_COLS.index_id
                                                INNER JOIN sys.columns COLUMN_DATA_INC_COLS
                                                ON T.object_id = COLUMN_DATA_INC_COLS.object_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.column_id = COLUMN_DATA_INC_COLS.column_id
                                  WHERE   INDEX_DATA.object_id = INDEX_DATA_INC_COLS.object_id
                                                AND INDEX_DATA.index_id = INDEX_DATA_INC_COLS.index_id
                                                AND INDEX_COLUMN_DATA_INC_COLS.is_included_column = 1
                                  ORDER BY INDEX_COLUMN_DATA_INC_COLS.key_ordinal
                                  FOR XML PATH('')), 1, 2, '') AS include_column_list,
       INDEX_DATA.is_disabled -- Check if index is disabled before determining which dupe to drop (if applicable)
       FROM sys.indexes INDEX_DATA
       INNER JOIN sys.tables TABLE_DATA
       ON TABLE_DATA.object_id = INDEX_DATA.object_id
       INNER JOIN sys.schemas SCHEMA_DATA
       ON SCHEMA_DATA.schema_id = TABLE_DATA.schema_id
       WHERE TABLE_DATA.is_ms_shipped = 0
       AND INDEX_DATA.type_desc IN ('NONCLUSTERED', 'CLUSTERED')
)
SELECT
       *
FROM CTE_INDEX_DATA DUPE1
WHERE EXISTS
(SELECT * FROM CTE_INDEX_DATA DUPE2
 WHERE DUPE1.schema_name = DUPE2.schema_name
 AND DUPE1.table_name = DUPE2.table_name
 AND (DUPE1.key_column_list LIKE LEFT(DUPE2.key_column_list, LEN(DUPE1.key_column_list)) OR DUPE2.key_column_list LIKE LEFT(DUPE1.key_column_list, LEN(DUPE2.key_column_list)))
 AND DUPE1.index_name <> DUPE2.index_name)

How to find restore database percentage in SQL server

select percent_complete, DATEADD(ss,estimated_completion_time/1000,getdate()) AS estimated_completion_time
from sys.dm_exec_requests where command like '%Restore%'

How to check expensive queries in SQL server

select top 50 qs.creation_time,
              qs.execution_count,
              qs.total_worker_time as total_cpu_time,
              qs.max_worker_time   as max_cpu_time,
              qs.total_elapsed_time,
              qs.max_elapsed_time,
              qs.total_logical_reads,
              qs.max_logical_reads,
              qs.total_physical_reads,
              qs.max_physical_reads,
              t.[text],
              qp.query_plan,
              t.dbid,
              t.objectid,
              t.encrypted,
              qs.plan_handle,
              qs.plan_generation_num
from   sys.dm_exec_query_stats qs
       cross apply sys.Dm_exec_sql_text(plan_handle) as t
       cross apply sys.Dm_exec_query_plan(plan_handle) as qp
--order  by qs.total_worker_time desc
--ORDER BY qs.total_logical_reads DESC -- logical reads
 ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Last modified date of database in SQL Server

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union
   SET @sqlString = @sqlString + '
    SELECT
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'
CLOSE crs;
DEALLOCATE crs;
EXEC(@sqlString)

How to set Read routing list in Always on - SQL server 2012

ALTER AVAILABILITY GROUP [AG-NAME]
MODIFY REPLICA ON
N'SERVER1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER3','SERVER2','SERVER4','SERVER5')));