SELECT TOP (5) b.name AS TableName, a.database_id, a.singleton_lookup_count, a.range_scan_count FROM sys.dm_db_index_operational_stats(...

--Query to find the sql authentication Scheme SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

SELECT [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END, [WriteLatency] = CASE WHEN...

--Fix Orphan Users EXEC sp_change_users_login 'Report' EXEC sp_change_users_login 'Auto_Fix', 'user' EXEC sp_change_users_login...

--Page Split/Sec using DMV SELECT ms_ticks FROM sys.dm_os_sys_info; Select * from sys.dm_os_performance_counters where object_name =...

---Find who uses the most query memory grant: SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan FROM...

-- Calculates average stalls per read, per write, and per total input/output for each database file. SELECT DB_NAME(fs.database_id) AS...

--Find last backup time for all the DB SELECT sdb.Name AS DatabaseName, COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date),...

SELECT @@servername AS INSTANCE ,[object_name] ,[counter_name] , UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy' THEN...

sp_helpindex 'index_name'

ALTER INDEX ALL ON <TABLE NAME> REBUILD WITH (DATA_COMPRESSION = None);

-- Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data...

DBCC useroptions

sp_spaceused ‘Tablename’

1 a). SELECT name FROM sys.schemas 1 b). SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

--Auditing Scripts 1). Find Sysadmin accounts USE master go SELECT name FROM syslogins WHERE sysadmin = 1 2). find logins account that...

--Check DB file and its filegroup select * from sys.filegroups go select * from sys.database_files

select name, physical_name, size*0.000008 Size_GB, growth, is_percent_growth, growth*0.0078125 Growth_MB from sys.master_files order by...

----Get execution plan of cached queries orderd by Total Elapsed Time Desc SELECT total_logical_reads, total_logical_reads /...

--Check if index for the table is being used. SELECT i.index_id, i.name, u.user_seeks, u.user_lookups, u.user_scans FROM...