SQL Server tuning

See a list of all the indexes in the database

select * from sys.dm_db_index_physical_stats(null,null,null,null,null)

Index fragmentation

SELECT DB_NAME() AS DatabaseName,
Object_name(i.object_id) AS TableName,
i.index_id,
name AS IndexName,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'Limited') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
ORDER BY avg_fragmentation_in_percent desc

Show filegroups and db files

select * from sys.filegroups
select * from sys.database_files

Additional commands

SET SHOWPLAN_XML OFF
GO
SET STATISTICS PROFILE OFF
SET STATISTICS IO ON
-- clear query plans
dbcc freeproccache;
-- clear buffer cache
dbcc dropcleanbuffers;

Examples using AdventureWorks2016

use AdventureWorks2016;

select pp.name
from Production.Product pp
join Sales.SalesOrderDetail ss
on pp.ProductID = ss.ProductID
join Sales.SalesOrderHeader oh
on ss.SalesOrderID = oh.SalesOrderID;

select check_clause from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
where CONSTRAINT_NAME = 'CK_Employee_SickLeaveHours';

SET STATISTICS PROFILE on

SELECT  sql_text.text, last_execution_time, creation_time
FROM    sys.dm_exec_query_stats AS stats
        CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS sql_text
order by last_execution_time DESC

SELECT * FROM sys.dm_exec_query_transformation_stats

EXEC sys.sp_updatestats @resample = '' -- char(8)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION

	SELECT TOP 100 * FROM AdventureWorks2016CTP3.Person.Person-- WITH(TABLOCKX);

	SELECT resource_type, request_mode, COUNT(*) AS lock_count
	FROM sys.dm_tran_locks
	WHERE request_session_id = @@SPID
	GROUP BY resource_type, request_mode;

ROLLBACK

DBCC SHOW_STATISTICS ('Person.Person', 'IX_Person_LastName_FirstName_MiddleName')

SELECT * FROM sys.stats
WHERE OBJECT_ID = OBJECT_ID('Person.Person')
ORDER BY stats_id;

DBCC SHOW_STATISTICS ('Person.Person', 'PK_Person_BusinessEntityID')

DBCC SHOW_STATISTICS ('Person.Person', 'IX_Person_LastName_FirstName_MiddleName')

SELECT * FROM Person.Person WHERE LastName = 'Alonso';

SELECT * FROM Person.Person WHERE LastName = 'Acca';

See the query plans

SELECT 		databases.name,
	dm_exec_sql_text.text AS TSQL_Text,
	dm_exec_query_stats.creation_time, 
	dm_exec_query_stats.execution_count,
	dm_exec_query_stats.total_worker_time AS total_cpu_time,
	dm_exec_query_stats.total_elapsed_time, 
	dm_exec_query_stats.total_logical_reads, 
	dm_exec_query_stats.total_physical_reads, 
	dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats 
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%t_item_master%';