tcartwright

<UserSettings>
	<ApplicationIdentity version="10.0"/>
	<ToolsOptions>
		<ToolsOptionsCategory name="Environment" RegisteredName="Environment"/>
	</ToolsOptions>
	<Category name="Environment_Group" RegisteredName="Environment_Group">
		<Category name="Environment_FontsAndColors" Category="{1EDA5DD4-927A-43a7-810E-7FD247D0DA1D}" Package="{DA9FB551-C724-11d0-AE1F-00A0C90FFFC3}" RegisteredName="Environment_FontsAndColors" PackageName="Visual Studio Environment Package">
			<PropertyValue name="Version">2</PropertyValue>
			<FontsAndColors Version="2.0">
				<Categories>

tcartwright

@setlocal enabledelayedexpansion
@echo off

@set path=%~dp0
@set folders=

@for /f "tokens=*" %%G in ('dir /b /a:d "%path%"') do @set folders=!folders! .\%%G

@REM @echo %folders%

tcartwright

%windir%\System32\inetsrv\appcmd.exe list apppool /xml | %windir%\System32\inetsrv\appcmd.exe recycle apppool /in

tcartwright

/***************************************************************************/
/******************************TEST FOR ARITHABORT ON***********************/
/***************************************************************************/
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int);

INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure 'user_options';

SELECT 'ARITHABORT ' + CASE WHEN ([config_value] & 64) = 64 THEN 'ON' ELSE  'OFF' END 

tcartwright

/*
	Author: Tim Cartwright
	Purpose: Allows you to check the server, and client SET options

	https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option
	1	DISABLE_DEF_CNST_CHK	Controls interim or deferred constraint checking.
	2	IMPLICIT_TRANSACTIONS	For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.
	4	CURSOR_CLOSE_ON_COMMIT	Controls behavior of cursors after a commit operation has been performed.
	8	ANSI_WARNINGS	Controls truncation and NULL in aggregate warnings.
	16	ANSI_PADDING	Controls padding of fixed-length variables.

tcartwright

IF OBJECT_ID (N'dbo.fnBetterQuoteName') IS NOT NULL
   DROP FUNCTION dbo.fnBetterQuoteName
GO

CREATE FUNCTION dbo.fnBetterQuoteName(
	@NameSet NVARCHAR(MAX)
)
RETURNS nvarchar(max)
AS
BEGIN

tcartwright

	SELECT [jobs].[name], 
		[jobs].[step_name], 
		[jobs].[run_status], 
		[jobs].[run_status_desc], 
		[jobs].[run_date], 
		[jobs].[run_time], 
		[jobs].[run_duration] 
	FROM (
		SELECT [RowNbr] = ROW_NUMBER() OVER (PARTITION BY sj.[name] ORDER BY fn.[run_date] DESC, fn.[run_time] DESC),
			sj.name,

tcartwright

-- https://msdn.microsoft.com/en-us/library/cc293624.aspx?f=255&MSPPError=-2147217396

SELECT TOP(10) [type] AS [ClerkType], SUM(pages_kb) / 1024 AS [SizeMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC

/*
Cache Stores
SQL Server’s plan cache is made up of four separate memory areas, called cache stores. There are actually other stores in SQL Server’s memory, which can be seen in the Dynamic Management View (DMV) called sys.dm_os_memory_cache_counters, but there are only four that contain query plans. The names in parentheses below are the values that can be seen in the type column of sys.dm_os_memory_cache_counters:

tcartwright

SELECT [cached_pages_count] = COUNT(*),
	   [cached_pages_count_mb] = CAST(COUNT(*) / 128.0 AS money),
	   [database_name] = CASE dobd.[database_id]
		   WHEN 32767 THEN 'ResourceDb'
		   ELSE DB_NAME([database_id])
	   END  
FROM   [sys].[dm_os_buffer_descriptors] dobd
GROUP BY dobd.[database_id]
ORDER BY [cached_pages_count] DESC;

tcartwright

SELECT [schema_name] = s.[name],
	[table_name] = t.[name],
	[index_name] = i.[name],
	ips.[avg_fragmentation_in_percent], 
	ips.[avg_fragment_size_in_pages], 
	[i].[type], 
	ips.[index_type_desc], 
	ips.[alloc_unit_type_desc], 
	ips.[page_count], 
	[page_count_mb] = CAST(ips.[page_count] / 128.0 as money),