Skip to content

Instantly share code, notes, and snippets.

View usp_get_tls_connection_stats.sql
/*
* 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
@stummsft
stummsft / get_server_tls_certificate.sql
Created Feb 15, 2019
Scrape the currently loaded SQL Server TLS certificate from the available error logs
View get_server_tls_certificate.sql
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
stummsft / Set-SqlTlsCertificate.ps1
Created Apr 5, 2019
Programmatically set the TLS certificate to be used by a given SQL Server Instance
View Set-SqlTlsCertificate.ps1
function Set-SqlTlsCertificate {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
[Parameter()]
[Alias("CN", "MachineName")]
[String[]]$ComputerName = "localhost",
[Parameter()]
[String[]]$InstanceName,
View large_vlfs.sql
/*
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
stummsft / version_store_sentry.sql
Last active Jun 17, 2019
Identify sessions causing version store retention
View version_store_sentry.sql
/*
* 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
View find_query_plans_by_object.sql
/*
* 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
stummsft / long_running_snapshot_tran_info.sql
Last active Mar 5, 2019
Gather info about long running snapshot transactions
View long_running_snapshot_tran_info.sql
/*
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
stummsft / azure-pricing-managedinstance-IO-estimate.sql
Created Oct 11, 2018
Estimate the price of moving a database to Azure SQL Managed Instance
View azure-pricing-managedinstance-IO-estimate.sql
/*
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
stummsft / furthest-allocation.sql
Created Oct 1, 2018
Which table is blocking a shrinkfile operation
View furthest-allocation.sql
/*
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.
*/