stummsft

/*
This script returns the further-allocated page per index per file in the database
This may be useful in reclaiming diskspace after a major data purge or migration operation

If a shrinkfile operation is not proceeding in a timely fashion or is being blocked,
this will help you target specific indexes to be rebuilt or migrated to a different filegroup
to help the space reclamation proceed.
*/

stummsft

/*
This script is intended to help estimate the expected pricing for a database to be migrated to an Azure SQL Managed Instance.

This only captures the I/O activity directly associated with user queries. Background processes are different between
Managed Instances and SQL Server on-premises, so this number should not be treated as an exact prediction.
Most workloads have periodic ups and downs at least weekly, and sample periods shorter than one month
or especially one week are much more likely to be estimated from a highly-skewed sample period.

If you are using preview pricing, GA, or have any other factor affecting the pricing, update the parameter below.
*/

stummsft

/*
This query grabs relevant information about active long-running snapshot transactions.
This can help identify what is preventing cleanup of tempdb version store records.
This can also help identify what is preventing long truncation in an Availability Group topology.
*/

DECLARE @old_transaction_cutoff_minutes INT = 60;

SELECT

stummsft

/*
 * This function will return all plan_handles that include a reference to
 * a given object. This can help with targeted purges of execution plans.
 */


CREATE OR ALTER FUNCTION find_query_plans_by_object (
	@database SYSNAME,
	@schema SYSNAME,
	@table SYSNAME

stummsft

/*
 * This script will identify which transactions are causing excessive growth of the version store.
 * It will output a list of how much version store retention each transaction is responsible
 * This retention is not "exclusive" to that single transaction and overlaps with others
 * if there are multiple long-lived transactions.
 */

--Meaningless magic numbers. Replace with thresholds meaningful to your system!
DECLARE @version_store_size_total_threshold BIGINT = 25 * POWER(2.0, 30); --GB
DECLARE @version_store_size_per_db_threshold BIGINT = 10 * POWER(2.0, 30); --GB

stummsft

/*
In SQL 2016, there is a feature to allow effective Backup Compression for databases protected by TDE.
There is however a caution that this feature should not be used when the database has VLFs which are
larger than 4GB. This short script can help identify if any of your databases would be affected.
*/

DECLARE @dbname SYSNAME = 'foo';
--TODO: Add switch to only show noncompliance
--TODO: Add switch to only evaluate databases with TDE active

stummsft

function Set-SqlTlsCertificate {
    [CmdletBinding(SupportsShouldProcess=$true)]
    param (
        [Parameter()]
        [Alias("CN", "MachineName")]
        [String[]]$ComputerName = "localhost",

        [Parameter()]
        [String[]]$InstanceName,

stummsft

CREATE PROCEDURE [dbo].[get_server_tls_certificate]
	@maximum_results INT = 1
AS
BEGIN;
	DECLARE @error_log_count INT = 0;
	DECLARE @i INT = 0;

	SET NOCOUNT ON;

	CREATE TABLE #error_log (

stummsft

/*
 * Creates an XEvent session utilizing the 'Trace' event added to SQL 2016
 * This event will capture the protocol negotiation details of all TLS connections
 * This allows you to capture how many connections are being made and with what
 * protocol version and cipher.
 * This information can be used to predict the impact of disabling a given
 * protocol version or cipher suite before taking such actions.
 */

CREATE OR ALTER PROCEDURE usp_get_tls_connection_stats