tcartwright

/*
	Author: Tim Cartwright
	1) Leave the @dbname variable empty or null for all databases
	2) Changed the @dbname variable to a specific variable to only get the queries for that database.

	RETURNS: The queries with the highest cost, and longest working time with the worst offenders being at the top of the list.
*/


DECLARE @dbname sysname = '', -- '',

tcartwright

/*=============================================
	  File: SQL_Server_config_check.sql
 
	  Author: Thomas LaRock, http://thomaslarock.com/contact-me/
	  http://thomaslarock.com/2014/08/sql-server-configuration-check/
 
	  Summary: This script will check the values of your sys.configurations table
	   and compare it to the default values. The script should return a row for any 
	   configuration option that is currently set to a non-default value. 
 

tcartwright

Converted into a module: https://github.com/tcartwright/tcdbtools

PS Gallery link: https://www.powershellgallery.com/packages/tcdbtools/

tcartwright

SELECT n.[Name], o.[type_desc], p.[perms]  
FROM sys.[objects] o
INNER JOIN sys.[schemas] s ON [o].[schema_id] = [s].[schema_id]
CROSS APPLY (SELECT [Name] = QUOTENAME(s.[name]) + '.' + QUOTENAME(o.[name])) n
OUTER APPLY (
	SELECT perms = STUFF((
		SELECT ',' + ip.[permission_name] 
		FROM sys.fn_my_permissions(n.[Name], 'OBJECT') ip
		GROUP BY ip.[permission_name] 
		FOR XML PATH('')

tcartwright

############################################################### 
# Eric Ligmans Amazing Free Microsoft eBook Giveaway 
# https://blogs.msdn.microsoft.com/mssmallbiz/2017/07/11/largest-free-microsoft-ebook-giveaway-im-giving-away-millions-of-free-microsoft-ebooks-again-including-windows-10-office-365-office-2016-power-bi-azure-windows-8-1-office-2013-sharepo/
# Link to download list of eBooks 
# http://ligman.me/2sZVmcG 
# Thanks David Crosby for the template (https://social.technet.microsoft.com/profile/david%20crosby/)
# Changes by Tim Cartwright:
#  -exception handling for the header webrequests
#  -grouped titles so as to skip downloading duplicate files
#  -altered path handling to be more stable, and not require ending with a backslash

tcartwright

	DECLARE @user_name nvarchar(256) = null 

	SET @user_name = ISNULL(@user_name, SUSER_NAME())

	IF @user_name NOT LIKE '%\%' AND NOT EXISTS (SELECT 1 FROM master.sys.server_principals sp WHERE name = @user_name AND sp.[type_desc] = 'SQL_LOGIN') BEGIN
		SET @user_name = DEFAULT_DOMAIN() + '\' + @user_name
	END 

	IF IS_SRVROLEMEMBER('sysadmin', @user_name) = 1 BEGIN
		SELECT UPPER(@user_name) + ' IS SYSADMIN';

tcartwright

-- FIND ALL PRIMARY KEYS WHERE THE FIRST COLUMN IS AN IDENTITY AND THE FILL FACTOR ADDS EMPTY SPACE

SELECT [db_name] = DB_NAME(),
	[schema_name] = OBJECT_SCHEMA_NAME(i.[object_id], DB_ID()),
	[table_name] = OBJECT_NAME(i.[object_id]),  
	[index_name] = [i].Name, 
	[i].[type_desc], 
	[i].[fill_factor], 
	[index_in_row_size_in_mb] = CAST(fn1.[IndexInRowSizeInMB] AS DECIMAL(19,4)),
	[index_in_row_empty_mb] = CAST(fn2.[IndexInRowEmptyMB] AS DECIMAL(19,4)),

tcartwright

SELECT [schema_name] = s.[name],
	[table_name] = t.[name],
	[index_name] = i.[name],
	st.[user_seeks], 
	st.[user_scans], 
	st.[user_lookups], 
	st.[user_updates],
    STUFF(
        (
            SELECT ', ' + QUOTENAME([c2].[name])

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

-- 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: