ronascentes

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the DELETE statement succeeds, commit the transaction.

ronascentes

-- got from https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
DECLARE @rows INT, @ErrorMessage NVARCHAR(4000);

SET NOCOUNT, XACT_ABORT ON;
SET @rows = 1;

BEGIN TRY
	WHILE (@rows > 0)
	BEGIN
		BEGIN TRANSACTION;

ronascentes

CONVERT(VARCHAR(10), LAST_LOAD_DATE, 101) < CONVERT(VARCHAR(10), GETDATE(), 101)

ronascentes

CREATE TABLE dbo.mock_table
(
	MyKeyField VARCHAR(10) NOT NULL,
	MyDate1 DATETIME NOT NULL,
	MyDate2 DATETIME NOT NULL,
	MyDate3 DATETIME NOT NULL,
	MyDate4 DATETIME NOT NULL,
	MyDate5 DATETIME NOT NULL
)

ronascentes

-- Trace flag 1118 turns off mixed page allocations. Preventing mixed page allocations reduces the risk of page latch contention 
-- on the SGAM allocation bitmatps that track mixed extents; which Paul says is one of the leading causes for contention in tempdb.
-- When doing allocations for user tables always allocate full extents.  Reducing contention of mixed extent allocations
DBCC TRACEON (1118,-1)

-- simply stops SQL Server from writing backup successful messages to the error log.
DBCC TRACEON (3226,-1)

-- Changes to automatic update statistics
DBCC TRACEON (2371,-1)

ronascentes

-- show advanced options
-- cost threshold for parallelism
-- max degree of parallelism
-- max server memory (MB)
-- optimize for ad hoc workloads

SELECT name, description, value, value_in_use
FROM sys.configurations
WHERE configuration_id IN (518,1538,1539,1544,1581)

ronascentes

-- Find Missing Indexes by Index Advantage
-- Look at index advantage, last user seek time, number of user seeks to help determine source and importance
-- SQL Server is overly eager to add included columns, so beware
-- Do not just blindly add indexes that show up from this query!!!
SET NOCOUNT ON
GO
SELECT TOP 25
DB_NAME(dm_mid.database_id) AS Database_Name,
user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
dm_migs.last_user_seek AS Last_User_Seek,

ronascentes

-- releases all unused cache entries from all caches
DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS;

-- flushes the distributed query connection cache used by distributed queries against an instance
DBCC FREESESSIONCACHE WITH NO_INFOMSGS;         

-- manually removes a single plan or all plans from the cache
DBCC FREEPROCCACHE WITH NO_INFOMSGS;

-- clear the data buffer

ronascentes

USE <db_name>
GO
SELECT SUM((size/128.0))/1024 AS [Total Size in GB],
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/1024 AS [Space Used in GB],
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/1024 AS [Available Space in GB],
sum((((size)/128.0) - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)) / sum(((size)/128.0)) * 100  as '% Available'
FROM sys.database_files WITH (NOLOCK) 
--WHERE type = 1 -- log

ronascentes

/*
xp_readerrrorlog - extended stored procedure accepts at least 7 parameters
Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to further refine the results
Search from start time  
Search to end time
Sort order for results: N'asc' = ascending, N'desc' = descending
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc'