Skip to content

Instantly share code, notes, and snippets.

View ronascentes's full-sized avatar

Rodrigo Nascentes ronascentes

14 followers · 10 following
View GitHub Profile
@ronascentes
ronascentes / sendMail.sql
Last active July 5, 2018 17:27
Send a formatted HTML email from SQL Server
SET NOCOUNT ON;
DECLARE @body nvarchar(max)
SET @body = N'
<style type=''text/css''>
TABLE{border-width: 1px;border-style: solid;background-color: #E8E8E8; border-color: black;border-collapse: collapse;}
TH{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
TD{color:black; border-width: 1px;font-size: 11px; padding: 3px;border-style: solid;border-color: black;}
</style>' +
N'<h5> *** This is an automatic email, please do not reply *** </h5>' +
@ronascentes
ronascentes / get-failoverEvents.ps1
Last active November 26, 2018 15:50
Get last failover events
Get-winEvent -ComputerName <server_name> -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap
@ronascentes
ronascentes / Install-SqlServerPSModule.ps1
Last active April 26, 2024 09:24
Install SqlServer Powershell module to a machine without access to PowerShell Gallery
# download the module to local folder
Invoke-WebRequest -Uri powershellgallery.com/api/v2/package/sqlserver -Out C:\temp\sqlserver.zip
# unzip the file
Expand-Archive -LiteralPath 'C:\temp\sqlserver.zip' -DestinationPath C:\temp\sqlserver
# remove powershell gallery related files
Push-Location C:\temp\sqlserver
Remove-Item .\_rels\ -Recurse -Force
Remove-Item .\package\ -Recurse -Force
@ronascentes
ronascentes / readonly_routing_list.sql
Created January 24, 2018 15:55
Read-only routing list
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority
@ronascentes
ronascentes / get_resource_pool_pct_needed.sql
Created January 9, 2018 17:53
USE master
GO
DECLARE @memoryNeeded INT = 16
SELECT percentNeeded = @memoryNeeded /
ROUND(((committed_target_kb/1024)/1024) *
(CASE
WHEN (committed_target_kb/1024)/1024 <= 8 THEN 0.7
WHEN (committed_target_kb/1024)/1024 <= 16 THEN 0.75
WHEN (committed_target_kb/1024)/1024 <= 32 THEN 0.8
WHEN (committed_target_kb/1024)/1024 <= 96 THEN 0.85
@ronascentes
ronascentes / get_deadlock_info.sql
Last active October 4, 2021 19:45
Getting deadlock information
SELECT CONVERT(xml, event_data).query('/event/data/value/child::*') as xml1,
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime') as Execution_Time
into #temp
FROM sys.fn_xe_file_target_read_file('system_health*.xel', null, null, null)
WHERE object_name like 'xml_deadlock_report'
select Row_Number() OVER(ORDER BY execution_time desc) AS RowNumber,* into #temp2 from #temp
@ronascentes
ronascentes / perfmon.sql
Created November 24, 2017 18:12
SELECT N'Perfmon' as [Category], [object_name],counter_name, cntr_value
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N'%Memory Manager%'
AND (counter_name LIKE N'Total Server Memory (KB)%' OR counter_name LIKE N'Target Server Memory (KB)%'
OR counter_name = N'Memory Grants Outstanding' OR counter_name = N'Memory Grants Pending')
OR ([object_name] LIKE N'%Buffer Manager%' AND counter_name = N'Page life expectancy')
OR ([object_name] LIKE N'%General Statistics%' AND counter_name = N'Transactions')
OR ([object_name] LIKE N'%General Statistics%' AND counter_name = N'User Connections')
OR ([object_name] LIKE N'%SQL Statistics%' AND counter_name = N'Batch Requests/sec')
@ronascentes
ronascentes / enable_query_store.sql
Last active March 17, 2020 17:22
Query Store Best Practices
USE [master]
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE = ON
GO
ALTER DATABASE [<db_name>] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
DATA_FLUSH_INTERVAL_SECONDS = 3600,
MAX_STORAGE_SIZE_MB = 5120,
INTERVAL_LENGTH_MINUTES = 60,
SIZE_BASED_CLEANUP_MODE = AUTO,
@ronascentes
ronascentes / page_split_tracking.sql
Last active October 31, 2017 17:24
Excessive page splitting can have a significant effect on performance. The following query identifies the top 10 objects involved with page splits (ordering by leaf_allocation_count and referencing both the leaf_allocation_count and nonleaf_allocation_count columns). The leaf_allocation_count column represents page splits at the leaf and the non…
-- Got from https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats/
SELECT TOP 10
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
leaf_allocation_count,
nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
ORDER BY leaf_allocation_count DESC
@ronascentes
ronascentes / Lock_escalations.sql
Created October 31, 2017 17:20
Track how many attempts were made to escalate to table locks (index_lock_promotion_attempt_count), as well as how many times escalations actually succeeded (index_lock_promotion_count).
-- get from https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/06/11/three-usage-scenarios-for-sys-dm_db_index_operational_stats
SELECT TOP 3
OBJECT_NAME(object_id, database_id) object_nm,
index_id,
partition_number,
index_lock_promotion_attempt_count,
index_lock_promotion_count
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL)
ORDER BY index_lock_promotion_count DESC