Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday 27 September 2018

TSQL Query to find analyze SQL memory and utilization and other important Querys for DBA

--------------Currently user running Query-------

use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, A.last_batch, GETDATE()) RunningTime,
A.spid, A.blocked, A.waittime, db_name(A.dbid) As dbname,B.text as sqlstatement,
A.hostname, A.loginame,A.program_name,A.last_batch,A.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE A.dbid > 4


---------------------------------Batch requests/sec

DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:01'
SELECT  @@SERVERNAME AS ServerName,(cntr_value-@BRPS)/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'



-------------------------------------find most consumed objects/statements info------------------------------

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--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

----------------------------------------------find most consumed stored procedure info
 SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
where database_id<>32767 and DB_NAME(database_id) not in ('master','msdb')
ORDER BY AVG_LOGICAL_READS DESC




----------------------------------------------------------Table Row count----------

SELECT @@servername as servername,DB_NAME() AS [Database Name],T.name      AS [TABLE NAME],
       I.row_count AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.dm_db_partition_stats AS I
               ON T.object_id = I.object_id
 
                  AND I.index_id < 2
ORDER  BY I.row_count DESC


-------------------------------------------blocking info with dbcc inputbuffer

select distinct top 1 @id=spid from sys.sysprocesses where blocked<>0

set @x=@id
if @x>0

begin

select @x as blockingspid

dbcc inputbuffer(@x)

end

else

select  'nothing blocked' as blockingstatus

---------------------------------------Page life expectancy

SELECT @@SERVERNAME AS ServerName,[object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.

check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.


--------------------------------------Lazy writer------------

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'

The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory.

When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs

If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache

The Lazy writes metric is defined as "Number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk"


The threshold value for Lazy Writes is 20

If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.

------------------------------check point--------------------------------------------------------------------------------------------------------------------------------------------------------

 A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction

at a check point, which can be automatic (occurs automatically to meet the recovery interval request) , indirect (occurs automatically to meet the database target recovery time),
manual (occurs when the CHECKPOINT command is executed), and internal (occurs along with some server-level operations, such as backup creation)

--------------------------------------------------------Latch--------------------------

a latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server.

3 Different types of Latches:

IO Latches
Buffer Latches (BUF)
Non-Buffer Latches (Non-BUF)

Latch Modes

Keep
Shared
Update
Exclusive
Destroy

-------------------------------Free list stalls/sec---------------------------

SELECT  'Free list stalls/sec',
        cntr_value
FROM    sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' and counter_name LIKE  '%Free list stalls/sec%'

----------------------------------------find number of core is SQL using-----------

select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

------------------------------------To find out Unused DB info------------


SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS queryexecutedtimestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
     




SELECT TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]


  FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
  where d.database_id > 4
  order by d.name


select d.name, x1 =

(select X1= max(bb.xx)

from (

    select xx = max(last_user_seek)

        where max(last_user_seek) is not null

    union all

    select xx = max(last_user_scan)

        where max(last_user_scan) is not null

    union all

    select xx = max(last_user_lookup)

        where max(last_user_lookup) is not null

    union all

        select xx = max(last_user_update)

        where max(last_user_update) is not null) bb)

FROM master.dbo.sysdatabases d

left outer join

sys.dm_db_index_usage_stats s

on d.dbid= s.database_id

 where d.dbid > 4

group by d.name

-----------------------DMV query that will tell us high level information of disk latency


SELECT
    *
   ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms',(wait_time_ms/waiting_tasks_count)/1000 as 'AVG Wait in  Sec',
(wait_time_ms/waiting_tasks_count)/1000/60 as 'AVG Wait in Mint'
FROM
   sys.dm_os_wait_stats
WHERE
   waiting_tasks_count > 0
ORDER BY
   wait_time_ms DESC

----------------------------------------Number of user connections info ---------------

SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
    INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END;

---------------------------------------------Adding tempdb files based on core/processor-------------------------


Select  @@servername as servername,DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'

SELECT  @@servername as servername,cpu_count AS logicalCPUs FROM sys.dm_os_sys_info



--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev5.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev6.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
---ETC, add files as per processors count

------------------------------------------Enable all Agent Jobs-----------------

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above


------------------------------------------Disable all Agent Jobs

Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above


-----------------------------------Kill the unused session on db bulk------------------------------------------


ALTER DATABASE teis_wfl SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE teis_wfl SET MULTI_USER



----------------------------------------------------------------DB Mail for Backup status report---------------------------------

  DECLARE @tableHTML  NVARCHAR(MAX) ;
  DECLARE @SER NVARCHAR(20);
  DECLARE @StrSubject VARCHAR(100);
  SET @SER=@@SERVERNAME

  SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'


SET @tableHTML =
    N'<H1>Database Backup Status Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
    N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th></tr>' +
 
    CAST ( ( SELECT td = @SER,       '',td = B.name,       '',
                    td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
                    td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
                    td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end,d.state_desc
                                  FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
 GROUP BY B.name,B.status,d.state_desc
  ORDER BY B.name
              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

PRINT @tableHTML

EXEC msdb.dbo.sp_send_dbmail  @profile_name='Database Administrator',
    @recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
--@recipients = 'JAR@texmo.net',
    @subject = @StrSubject,
    @body = @tableHTML,
    @body_format = 'HTML' ;




DECLARE @tableHTML  NVARCHAR(MAX) ;
  DECLARE @SER NVARCHAR(20);
  DECLARE @StrSubject VARCHAR(100);
  SET @SER=@@SERVERNAME

  SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'


SET @tableHTML =
    N'<H1>Database Backup Status Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
    N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th><th>Backup_type</th><th>Backup Size in MB</th><th>Backup Size in GB</th></tr>' +
 
    CAST ( ( SELECT  td = @SER,       '',td = LTRIM(RTRIM(B.name)),       '',
                    td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
                    td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
                    td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end,  '',td=d.state_desc,    '',
td= CASE MST.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'     
   END,  '',
   td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as money),10))),  '',td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as INT),10)))/1024,    ''
 
                                  FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
and  (CONVERT(datetime, MST.backup_start_date, 102) >= GETDATE() - 1)
 GROUP BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
  ORDER BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

PRINT @tableHTML

EXEC msdb.dbo.sp_send_dbmail  @profile_name='Database Administrator',
    --@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
@recipients = 'JAR@texmo.net',
    @subject = @StrSubject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
----------------------------------------------------Backup status query----------------------------------------------------


SELECT     B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
    ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end as BackupStatus
    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST

    ON MST.database_name = B.name AND MST.type = 'D'
where B.name not in ('tempdb')
 GROUP BY B.name,B.status
  ORDER BY B.name


SELECT     @@SERVERNAME AS ServerName,B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
    ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end as Backup_Status_Today,d.state_desc,
  CASE MST.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'     
   END AS backup_type,
   convert(varchar,cast(MST.backup_size/1024/1024 as money),10) as 'Backup Size in MB',convert(varchar,cast(MST.backup_size/1024/1024 as INT),10) /1024 as 'Backup Size in GB'
    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name


 --------------------------------------Databases not backed up within 24 hrs----------


DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'D'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END

----------------------------------------------Missing index Bulk-------------------------

EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '

------------------------------------To send DB offline status report via db mail------------------------------------

if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin

DECLARE @table  NVARCHAR(MAX) ;

SET @table =
    N'<H1>Offline Databases Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
    CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )    +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
    @subject = 'Offline Databases Report',
    @body = @table,
    @body_format = 'HTML' ;

END
Else Print 'All Databases are Online'

-------------------------------------Index fragmentation report-----------------------


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (14, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = 14 and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc

Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
Fragmentation is between 10-30% – it is suggested to perform index reorganization
Fragmentation is higher than 30% – it is suggested to perform index rebuild
-------------------------------------------------To view statistics status info against Database-------------------------
SELECT Distinct @@servername as Servername,db_name(db_id()) as DB_Name, SCHEMA_NAME(schema_id) SchemaName,t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated ,'['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'

SELECT  Distinct 'Update Statistics'+ '['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'

-------------------------------------------Recovery pending----------------------------


SELECT name, state_desc from sys.databases
where state_desc='RECOVERY_PENDING'

exec sp_resetstatus dbname

ALTER DATABASE dbname SET ONLINE

DBCC CHECKDB('dbname') WITH NO_INFOMSGS

ALTER DATABASE dbname SET SINGLE_USER

DBCC CHECKDB('dbname',REPAIR_REBUILD)

ALTER DATABASE dbname SET MULTI_USER

https://promx.net/en/2016/11/microsoft-sql-server-database-repair-following-recovery-pending-status/
-----------------------------------------Alter Query for move tempdb secondary files-----------------------

Transact-SQL

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

------------------------------------------------to check what job is running at a particular time----------------------


SELECT * FROM
(
 SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds,RunSeconds/60 Mints,RunSeconds/60/60 hrs
 FROM
 (
  SELECT j.name AS 'JobName',
    msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
    ((jh.run_duration/1000000)*86400)
    + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
    + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
    + (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
  FROM msdb.dbo.sysjobs j
  INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
  WHERE jh.step_id=0 --The Summary Step
 ) AS H
) AS H2
WHERE '2018-10-22 03:30:00' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd

------------------------------------------------Log reuse monitor---------------------------------------------

dbcc sqlperf(logspace)

Select  @@SERVERNAME AS ServerName,name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases
where log_reuse_wait_desc<>'NOTHING'

SELECT instance_name
 , [Data File(s) Size (KB)] * 1.0 / 1024 [Data File(s) Size (MB)]
 , [Log File(s) Size (KB)] * 1.0 / 1024 [Log File(s) Size (MB)]
 , [Log File(s) Used Size (KB)] * 1.0 / 1024 [Log File(s) Used Size (MB)]
 , [Percent Log Used]
 , log_reuse_wait_desc
FROM (   SELECT os.counter_name, os.instance_name, os.cntr_value
 , db.log_reuse_wait_desc
   FROM sys.dm_os_performance_counters os
   JOIN sys.databases db ON os.instance_name = db.name
   WHERE os.counter_name IN
   (
       'Data File(s) Size (KB)'
       , 'Log File(s) Size (KB)'
       , 'Log File(s) Used Size (KB)'
       , 'Percent Log Used'
   )
     --AND os.instance_name = 'TLogTruncate'
) as SourceTable
PIVOT (
MAX(cntr_value) FOR counter_name IN
  ([Data File(s) Size (KB)]
       , [Log File(s) Size (KB)]
       , [Log File(s) Used Size (KB)]
       , [Percent Log Used])) as PivotTable



--------------------------------------------To see DB growth for all DB-----------------------------------------------

SELECT   'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id



SELECT @@SERVERNAME as ServerName,*
FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
        convert(varchar(10),[Dateofobservation],121) [Date],
       SUM([OccupiedinMB]) [OccupiedinMB]
      FROM [db_admin].[dbo].[DBGrowth]
      GROUP BY [DBName],YEAR([Dateofobservation]),
       convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData
PIVOT( SUM([OccupiedinMB]) 
    FOR [Date] IN (  [2018-11-29],[2018-11-30],[2018-12-01],[2018-12-02],[2018-12-03],[2018-12-04],[2018-12-05], [2018-12-06],[2018-12-07],[2018-12-08],[2018-12-09],[2018-12-10],[2018-12-11]
,[2018-12-12],[2018-12-13],[2018-12-14],[2018-12-15],[2018-12-16],[2018-12-17],[2018-12-18],[2018-12-19],[2018-12-20],[2018-12-21],[2018-12-22],[2018-12-23],[2018-12-24]
,[2018-12-25],[2018-12-26],[2018-12-27],[2018-12-28],[2018-12-29],[2018-12-30],[2018-12-31]
     
       )) AS MNamePivot




Declare @s varchar(max)=''
Declare @i int
Declare @j int

set @i=1

select @j=right(convert(varchar(7),getdate(),121),2)

select @j as j

set @s='SELECT @@SERVERNAME  as ServerName,*'
set @s=@s +'FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
        convert(varchar(10),[Dateofobservation],121) [Date],
       SUM([OccupiedinMB]) [OccupiedinMB]
      FROM [db_admin].[dbo].[DBGrowth]
      GROUP BY [DBName],YEAR([Dateofobservation]),
       convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData'
set @s=@s +' PIVOT( SUM([OccupiedinMB]) 
    FOR [Date] IN ( '
set @s=@s+' [2018-'+cast(@j as varchar)+'-01],[2018-'+cast(@j as varchar)+'-02],[2018-'+cast(@j as varchar)+'-03],[2018-'+cast(@j as varchar)+'-04]
,[2018-'+cast(@j as varchar)+'-05],[2018-'+cast(@j as varchar)+'-06],[2018-'+cast(@j as varchar)+'-07],[2018-'+cast(@j as varchar)+'-08]
,[2018-'+cast(@j as varchar)+'-09],[2018-'+cast(@j as varchar)+'-10],[2018-'+cast(@j as varchar)+'-11],[2018-'+cast(@j as varchar)+'-12]
,[2018-'+cast(@j as varchar)+'-13],[2018-'+cast(@j as varchar)+'-14],[2018-'+cast(@j as varchar)+'-15],[2018-'+cast(@j as varchar)+'-16]
,[2018-'+cast(@j as varchar)+'-17],[2018-'+cast(@j as varchar)+'-18],[2018-'+cast(@j as varchar)+'-19],[2018-'+cast(@j as varchar)+'-20]
,[2018-'+cast(@j as varchar)+'-21],[2018-'+cast(@j as varchar)+'-22],[2018-'+cast(@j as varchar)+'-23],[2018-'+cast(@j as varchar)+'-24]
,[2018-'+cast(@j as varchar)+'-25],[2018-'+cast(@j as varchar)+'-26],[2018-'+cast(@j as varchar)+'-27],[2018-'+cast(@j as varchar)+'-28]
,[2018-'+cast(@j as varchar)+'-29],[2018-'+cast(@j as varchar)+'-30],[2018-'+cast(@j as varchar)+'-31]
) '
set @s =@s+') as MNamePivot'

print @s
Exec(@s)


-----------------------------------------To Create and test linked server connection----------------------------------------------------

EXEC sp_addlinkedserver 
   @server=N'TPCDB1\TPCSQL',             -- Remote Computer Name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'TPCDB1\TPCSQL';  -- Server Name And Instance


declare @srvr nvarchar(128), @retval int;
set @srvr = '192.168.231.108';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

------------------------------------------To see MSDB table size info-----------------------------

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC


------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM   sys.server_principals AS usr1
                                INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
                                INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
                                + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM   sys.server_permissions AS server_permissions WITH (NOLOCK)
                                INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
                                server_permissions.state_desc ,
                                server_permissions.permission_name;


------------------------------sp_help_revlogin-------

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO


--EXEC sp_help_revlogin

-------------------------------------------------------------TSQL query to stop long running SQL Job  and start it back---------------------------


--if elapsed time is actually needed, uncomment
DECLARE @elapsed_minutes_max int
SET @elapsed_minutes_max = 240 --4 hours, adjust as needed

IF OBJECT_ID('tempdb.dbo.#jobactivity') IS NOT NULL
DROP TABLE #jobactivity
CREATE TABLE #jobactivity (
session_id int NULL,
job_id uniqueidentifier NULL,
job_name sysname NULL,
run_requested_date datetime NULL,
run_requested_source sysname NULL, --1=via schedule; 2=via alert; 3=via startup; 4=via user; 6=via CPU idle.
queued_date datetime NULL,
start_execution_date datetime NULL,
last_executed_step_id int NULL,
last_executed_step_date datetime NULL,
stop_execution_date datetime NULL,
next_scheduled_run_date datetime NULL,
job_history_id int NULL,
message nvarchar(1024) NULL,
run_status int NULL,
operator_id_emailed int NULL,
operator_id_netsent int NULL,
operator_id_paged int NULL
)

INSERT INTO #jobactivity
EXEC msdb.dbo.sp_help_jobactivity

IF EXISTS(
SELECT *
FROM #jobactivity ja
WHERE
    ja.start_execution_date IS NOT NULL AND
    ja.stop_execution_date IS NOT NULL AND
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) >= @elapsed_minutes_max
AND ja.job_name = N'Order Planning2')
BEGIN
 
    EXEC msdb.dbo.sp_stop_job N'Order Planning2'

print 'stop it'
END

ELSE
BEGIN


PRINT 'No JOB FOUND'

END

drop table #jobactivity


---------------------------------------Shrink TEMPDB without restart(last try before restart)--------------------------------------------

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO

-----------------------------------------TSQL code to find table with backedup with date----------------------

sp_tables '%_[1-30][1-12][1991-2020]%'

--------------------------------------General Guidelines to improve query performance-------------

Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table based on query which is running
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Do not to use Views or replace views with original source table
Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty – it improves the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

-------------- Disable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

----------------------- Enable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

---------To find column name of collation

Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test

----Change Collation of a SQL Server Table Column---------------------
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

------------------------------Change collation of database----------------------------
USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO

Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script

USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

before change collation of column you need to verify following

You cannot change the collation of a column that is currently referenced by any one of the following:

A computed column

An index

Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

A CHECK constraint

A FOREIGN KEY constraint

When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

see for more

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017


---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';
------------------------------------------------------------------------------------

ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [msdb] SET DISABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET ENABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET MULTI_USER

EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

-------------------------table with rowcount and size------------------

SELECT
    t.NAME AS TableName, 
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages)  * 8 / 1024 AS UsedSpaceMB ,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
--WHERE
--    t.is_ms_shipped = 0
--    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    UsedSpaceMB DESC, t.Name

------------------------------How much Memory SQL DB used----------------------------------------------

SELECT TOP 10 DB_NAME(database_id) Database_Name,
COUNT (*) * 8 / 1024 AS MBUsed,COUNT (*) * 8 / 1024 /1024 GBused
FROM sys.dm_os_buffer_descriptors
where DB_NAME(database_id) is not null
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
---------------------------------------------index fragment based on conditions-----


Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)

If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag, page_count

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

-----------------------------------------------------------------------------------------------


Phases of recovery(SQL Server)

the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.

Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.

Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).


---------------------------------------------------------VLF  and log file Info------------------------------
Understanding the VLF(Virtual Log File)

A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs  in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB          = 4  VLF
From 64 MB to 1 GB       = 8   VLF
Larger than 1 GB             = 16 VLF

dbcc loginfo--------VLF info

dbcc sqlperf(logspace)-------------


ref:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/09/30/sql-server-part-1-architecture-of-transaction-log/


--From SQL 2017 To know VLF Count below DMV can be used

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO

-----------------------------Change collation of SQL Server---------------


To rebuild system databases for an instance of SQL Server:

Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]



Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P2\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P1\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014

https://www.sanssql.com/2012/10/how-to-change-server-collation.html

https://www.sanssql.com/2013/10/change-sql-server-collation-without.html

-------------------------------------------Permuations and Combinations----------------------------------

--option 1

Create table #pk(col1 varchar(100),col2 varchar(100),primary key(col1,col2))

declare @t1 table  (col1 varchar(100))
insert @t1
    select 'Data1' UNION
    select 'Data2' UNION
    select 'Data3'  UNION
    select 'Data4' UNION
    select 'Data5'
Declare @t2 table  (col2 varchar(100))
insert @t2
    select 'Relation1' UNION
    select 'Relation2' UNION
    select 'Relation3' UNION
    select 'Relation4' UNION
    select 'Relation5'UNION
    select 'Relation6'



;with cteAllColumns as (
    select col1 as col
        from @t1
    union
    select col2 as col
        from @t2

)
insert into #pk
select c1.col , c2.col
    from cteAllColumns c1
        cross join cteAllColumns c2
    where c1.col < c2.col
order by c1.col

select * from #pk


--option 2


--Create table #pk(col1 varchar(4000),col2 varchar(4000),primary key(col1,col2))

DECLARE   @ConcatString1 VARCHAR(4000)
SELECT   top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM master.dbo.sysdatabases

DECLARE   @ConcatString2 VARCHAR(4000)


DECLARE @tags NVARCHAR(4000) = 'Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10,Data11'

SELECT @ConcatString2 = COALESCE(@ConcatString2 + ', ', '') +value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';


Create table #CSV1(Data1 VARCHAR(4000))
Insert into #CSV1
Values(@ConcatString1)

Create table #CSV2(Data2 VARCHAR(4000))
Insert into #CSV2
Values(@ConcatString2)


select * from #CSV1
select * from #CSV2

--drop table #CSV1
--drop table #CSV2


SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data1, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV1) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C2
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data2, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV2) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

Select * from #C1
Select * from #C2

declare @t1 table  (Info1 varchar(8000),col1 varchar(8000))

insert into @t1
Select * FROM #CSV1
    CROSS APPLY STRING_SPLIT(Data1, ',');

declare @t2 table  (Info2 varchar(8000),col2 varchar(8000))

insert into @t2
Select * FROM #CSV2
    CROSS APPLY STRING_SPLIT(Data2, ',');


Select * from @t1
Select * from @t2

;with cteAllColumns as (
    select col1 as col
        from @t1
    union
    select col2 as col
        from @t2

)
select c1.col, c2.col
    from cteAllColumns c1
        cross join cteAllColumns c2
    where c1.col < c2.col
order by c1.col
--select * from #pk

drop table #CSV1
drop table #CSV2
--drop table #pk
drop table #C1
drop table #C2

--option 3

DECLARE   @ConcatString VARCHAR(4000)
SELECT   top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + name
FROM master.dbo.sysdatabases

DECLARE   @ConcatString1 VARCHAR(4000)

SELECT   top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM ts4.master.dbo.sysdatabases
where name not in ('Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6', 'Data7', 'Data8', 'Data9', 'Data10', 'Data11')

Create table #CSV(Data VARCHAR(4000))
Insert into #CSV
Values(@ConcatString)

Insert into #CSV
Values(@ConcatString1)

select * from #CSV

--drop table #CSV


--select parsename(Data,4)as [4],parsename(Data,3)as [3],parsename(Data,2)as [2],parsename(Data,1)as [1] from
--(Select replace(Data,',','.') Data from #CSV) t



SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name5
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name5
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)



drop table #CSV

-super query-------------------

declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4


create table #c1(Id int,Name varchar(300),Value int)
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from t11 Order by Id'
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'



create table #c2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from t12 Order by Id'
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'

create table #c3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from t13 Order by Id'
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'


create table #c4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from t14 Order by Id'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'



;with cteAllColumns as (
    select Name as col
        from #c1
    union
    select Name as col
        from #c2
union
    select Name as col
        from #c3
union
    select Name as col
        from #c4


)
select  *
into #fmtb4
  from cteAllColumns cte1

order by col desc


;with cteAllColumns as (
    select Name as col
        from #c1
    union
    select Name as col
        from #c2
union
    select Name as col
        from #c3
union
    select Name as col
        from #c4

)
select  cte1.col col1,cte2.col col2
into #fmtbl
  from cteAllColumns cte1
        cross join cteAllColumns cte2
    where cte1.col < cte2.col
order by cte1.col

       

Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)


DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl

OPEN CC

FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN

set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'

update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'

update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'




 FETCH NEXT FROM CC INTO  @Column1,@Column2



  END


--select ''[afterfmtb4],* from  #fmtbl
--order by col1,col2

  SELECT Distinct top 2
stuff(
(
    SELECT ','+ col FROM #fmtb4  FOR XML PATH('')
),1,1,'')  Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4  ) #fmtb4

CLOSE CC
DEALLOCATE CC


select col1,col2,Results
Into #CP
 FROM #fmtbl P CROSS Join
#commatbl C

Select ''BEFORE,* from #CP


--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)

update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)

update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)



Select 'AFTER'AFTER,* from #CP
Order by col1,col2

SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl