--DMV to View Index fragmentation
SELECT
OBJECT_SCHEMA_NAME(i.object_id),
OBJECT_NAME(i.object_id),
*
FROM
sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) ips
JOIN
sys.indexes i ON ips.OBJECT_ID = i.OBJECT_ID AND ips.index_id = i.index_id
--sys.dm_os_ = SQL Server OS Information
SELECT * FROM sys.dm_os_wait_stats
--sys.dm_io_ = i/O Information
SELECT * FROM sys.dm_io_pending_io_requests
--sys.dm_exec_ = Session & Query Information
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests
--sys.dm_db__ = Database & Index Information
SELECT * FROM sys.dm_db_task_space_usage
SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
/* Informational Commands */
DBCC SQLPERF(LOGSPACE) --View transaction log size info
GO
DBCC SHOW_STATISTICS('Sales.SalesOrderDetail', 'IX_SalesOrderDetail_ProductID') --View query stats for table or indexed view object
GO
DBCC USEROPTIONS --View current connections user set options
GO
/* Maintenance Commands */
DBCC DBREINDEX('Person.Person') --Rebuilds indexes on a table
GO
DBCC CLEANTABLE(AdventureWorks2012, 'Production.ProductDescription') --Reclaims unused spaced from tables
GO
/* Validation Commands */
DBCC CHECKTABLE('Person.Person') --Integrity check of table pages/structure
GO
DBCC CHECKFILEGROUP --Integrity check of filegroup structure/allocation
GO
DBCC CHECKDB(AdventureWorks2012) --Integrity check of database objects
GO
/* Misc. Commands */
DBCC HELP('CHECKDB') --Syntax information for DBCC statements.. use '?' for list
GO
DBCC TRACEON(610) --Turn on trace flag 610 (minimally logged inserts into indexed tables)
GO
DBCC TRACEOFF(610) --Turn off trace flag 610
GO
DBCC TRACESTATUS(610) --Check trace flag status
GO
--Turn on advanced options
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--Configure min server memory
sp_configure 'min server memory', 0
GO
RECONFIGURE
GO
--Configure max server memory
sp_configure 'max server memory', 2048
GO
RECONFIGURE
GO
--Configure processor affinity
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 1, 2 --AUTO
GO
--Configure fill factor
sp_configure 'fill factor', 75
GO
RECONFIGURE
GO
DECLARE @PortNumber VARCHAR(10), @KeyValue VARCHAR(100)
IF CHARINDEX('\',@@SERVERNAME,0) <>0
BEGIN
SET @KeyValue = 'SOFTWARE\MICROSOFT\Microsoft SQL Server\'+@@SERVICENAME+'\MSSQLServer\Supersocketnetlib\TCP'
END
ELSE
BEGIN
SET @keyValue = 'SOFTWARE\MICROSOFT\MSSQLServer\MSSQLServer \Supersocketnetlib\TCP'
END
--How to read Registry in T-SQL Terms - ROOTKEY, KEY, VALUENAME,VALUE
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE',@KeyValue,'Tcpport',@value=@PortNumber OUTPUT
SELECT 'SQLSERVER_NAME: '+@@SERVERNAME + ' : SQL SERVER Port Number:'+CONVERT(VARCHAR(10),
@PortNumber)
The script below will retrieve the number of rows, reserved size, data size, index size and unused size. The reserved size is the overall size of the table (it is the total of data size, index size and unused size).
--DROP TABLE #tmpTableSizes
CREATE TABLE #tmpTableSizes
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
insert #tmpTableSizes
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
select * from #tmpTableSizes
order by cast(LEFT(dataSize, LEN(dataSize) - 4) as int) desc
--Make Database Read Only
USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT
GO
--Make Database Read/Write
USE [master]
GO
ALTER DATABASE [TESTDB] SET READ_WRITE WITH NO_WAIT
GO
--If you face error that if database is already in use, you can resolve the same by making database in single user mode
--1. Set the database to single mode:
ALTER DATABASE [TESTDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--2. Set the database to Multi-user mode
ALTER DATABASE [TESTDB]
SET MULTI_USER WITH ROLLBACK IMMEDIATE
To view the current isolation level of SQL Server, execute the script below.
SELECT
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS TRANSACTION_ISOLATION_LEVEL
FROM
sys.dm_exec_sessions
WHERE
session_id = @@SPID
Below are some very useful Change Data Capture (CDC) T-SQL Commands.
--Check if cdc is enabled
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO
--Enabled CDC at database level
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO
--Check which tables are CDC enabled
USE AdventureWorks
GO
SELECT [name], is_tracked_by_cdc
FROM sys.tables
GO
--Cnabled CDC for a table
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
GO
--Disable CDC on a table
USE AdventureWorks;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@capture_instance = N'HumanResources_Shift';
GO
--CaptureSelectedColumns
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL,
@captured_column_list = '[ShiftID],[Name]'
GO
