Author: Latheesh NK

Cached Plan Analysis, SQL

How to identify Missing Index from Cached Plan in SQL Server

Here, we are going to find a simple and very efficient way to identify missing indexes in SQL Server. The method is used to query cached plan in SQL Server and identify the missing index information from the Cached plan.

Please have a look and share your thoughts on the same.

Script


;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
	query_plan, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
	n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
	DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
	OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
	n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
	(   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY' FOR  XML PATH('')) AS equality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY' FOR  XML PATH('')) AS inequality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE' FOR  XML PATH('')) AS include_columns,
		tab.text,ObjectName
FROM
(
   SELECT query_plan,text,ObjectName   FROM (
			SELECT usecounts,cacheobjtype,objtype,query.text,object_name(query.objectid) ObjectName ,executionplan.query_plan
			FROM sys.dm_exec_cached_plans
			OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
			OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
			WHERE [text] NOT LIKE '%sys%'  AND cacheobjtype ='compiled plan' ) qs
   WHERE qs.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan,text,ObjectName)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1
Date & Time, SQL

First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.

SCRIPT GALLERY, SQL

How to Identify Referenced Tables(Child) recursively and its Keys for a Parent Table in SQL Server

Question:

Identify foreign key references OR Referenced Tables and its key(s) for a Parent table in SQL Server.

Script:


;WITH CTE AS
(

	SELECT OBJECT_NAME(PARENT_OBJECT_ID) CHILDTABLE, TYPE,NAME,OBJECT_NAME(REFERENCED_OBJECT_ID) PARENTTABLE,
	DELETE_REFERENTIAL_ACTION_DESC, UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS
	
	--SEARCH TABLENAME (PARENT TABLE)
	WHERE OBJECT_NAME(REFERENCED_OBJECT_ID) = 'ParentTable Name'

	UNION ALL

	SELECT OBJECT_NAME(PARENT_OBJECT_ID), A.TYPE,A.NAME,OBJECT_NAME(REFERENCED_OBJECT_ID),
	A.DELETE_REFERENTIAL_ACTION_DESC, A.UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS A
	INNER JOIN CTE B ON OBJECT_NAME(A.REFERENCED_OBJECT_ID) = B.CHILDTABLE
	WHERE PARENTTABLE !=  CHILDTABLE
	
)
,CTE1 AS
(
	SELECT DISTINCT A.*,B.COLUMN_NAME  FROM CTE A
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
		ON A.NAME = B.CONSTRAINT_NAME
)
SELECT 
	DISTINCT CHILDTABLE,PARENTTABLE,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE1 A
					WHERE A.NAME = B.NAME AND A.CHILDTABLE = B.CHILDTABLE AND A.PARENTTABLE = B.PARENTTABLE
					FOR XML PATH ('') )
					, ' ', ',') 
FROM CTE1 B OPTION(MAXRECURSION 32767)
SCRIPT GALLERY, SQL

sp_msforeachdb: Shrinking log files for all databases in SQL Server

This post is mainly to answer a question in one of my earlier post https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/

Question:

Mr Yagnesh was looking for a script to shrink log file for all of his databases using sp_msforeachdb.

To answer the question, Yes, we can very well modify the script in the post to shrink the log files. However, SHRINKFILE file is not advisable in all situations, I would suggest to take that option with extreme case.

You may find the below script to *ONLY* generate the SHRINKFILE DBCC commands for all databases except system databases. The reason for generating the DBCC commands is to verify the commands before you execute in your environment as a first level of verification to avoid any surprises.

Script:

 --To avoid/exception on applying the change, you may add your databases in the list. 
EXEC sp_msforeachdb 'Declare @logname varchar(500) 
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
        set @logname = (Select name From Sys.master_files where DB_ID(''?'') = database_id and type=1) 
        print ''Use [?];'' 
        print ''DBCC SHRINKFILE (['' + @logname + ''] ,100)'' 
end 
'

Alternatively, there is very simple method as below:


Select 'Use [' + db_name(database_id) + '];DBCC SHRINKFILE(['+ name + '],100)' 
	From sys.master_files where type=1
		and db_name(database_id) not in ('tempdb','master','msdb','model','Reportserver','ReportserverTempDB')

EDIT: 19th May 2017

One of my friend had difficulty to change the above to a dynamic execution statement. The below is my try to help those find it difficult:



EXEC sp_msforeachdb ' Declare @logname varchar(500) = '''';

IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'') 
begin 
	set @logname = (Select name From [?].Sys.database_files where  type=1) 

	EXEC(''Use [?];
		DBCC SHRINKFILE (['' + @logname + ''] ,100)'')  
end 
'

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48
https://latheeshnk.wordpress.com/2015/05/06/identify-references-of-an-object-in-sql-server/
https://latheeshnk.wordpress.com/2015/06/02/sp_msforeachdb-changing-recovery-model-for-all-databases/

SCRIPT GALLERY, SQL

How to find Primary Key and Key columns for tables in SQL Server

Its a very regular question in many of the forums “How to identify the primary key and its key column information for tables in SQL Server“.

Method 1:

1. Select the table in SSMS – (Query window)
2. Press – ALT-F1

The limitation is that, the above method can be useful only for a single table. If you are looking for a list of tables, then you can refer the second method.

Method 2:

T-SQL Script to identify the Primary key and Key column information for all tables.


;WITH CTE 
AS
(
	SELECT A.TABLE_SCHEMA,A.TABLE_NAME,A.CONSTRAINT_NAME,B.COLUMN_NAME,B.ORDINAL_POSITION
	FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
		ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME 
			AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
	WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
		--AND A.TABLE_NAME LIKE 'Tablename%' -- To filter for a particular table/name starts with
)
SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE A
					WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME =B.TABLE_NAME AND A.TABLE_SCHEMA = B.TABLE_SCHEMA
					ORDER BY A.ORDINAL_POSITION
					FOR XML PATH ('') )
					, ' ', ',')
FROM CTE B

References:
You may refer the below link to get more information for a table.
Handy Table Info Script