tsql.tech Code snippets

List of contents:

• DMV (Estract useful informations from DMVs)

  • Availability Groups information
  • Find Active Locks
  • Find Key Lookup in Plan Cache
  • Find Long Running Queries in PlanCache
  • Find Most Expensive Queries in PlanCache
  • Find Plans of your query in PlanCache
  • Find implicit Conversion Queries in PlanCache
  • Measure IO Latency and Throughput in a period of time
  • Index Usage Statis
  • LatchesStats
  • SpinlockStats
  • Table Sizes
  • Waitstats

• Dynamic SQL

  • Execute and Track the performance and behavior of Dynamic SQL Statements REFERENCE ARTICLE

• Extended Events

  • Backup-Restore duration troubleshoot
  • Capture Plans with warning

• Functions

  • DelimitedSplit8K - The powerful splitting function by Jeff Moden
  • fn_KeepInString - Keep only certain values in a sting See Article
  • fn_RemoveFromString - remove certain patterns from a string See Article
  • fn_TrasposeString - Trasposes strings into tables See Article

• Maintenance

  • Align Columnstore Index - Automatically align columnstore indexes to enhance segment elimination (and hence performances)
  • Data type consistency check
  • Rebuild Fragmented Heaps
  • Truncate All Transaction Log
  • VLF_Shrink
  • Add SQL Server Agent Alerts
  • Track progress of Index (re)creation

• Notebooks

  • Jupyter Notebook to use with SQL Server and Azure Data Studio
  • The SQL Diagnostic (jupyter) Book

• PowerBI

  • Job Information - A report to analyze the Agent Job performance, comes with companion procedure
  • Permission Information - A report to analyze the granular permissions on your SQL Server instance

• PowerShell

  • Covert Notebook to HTML - Converts a Jupyter Notebook to HTML for easy export [Requires jupyter installed in the System]
  • Create Diagnostic Notebooks - Automatically creates Jupyter Notebooks from the Latest Glen Berry Diagnostic Queries
  • Create FirstRespodersKit notebook - Automatically creates Jupyter Notebooks from the Latest FirstRespondersKit scripts
  • Export to CSV - A sample, simple, export SQL Table to CSV file using SQLServer Powershell Modules
  • PIP upgrade all packages - A simple way to upgrade all the installed Python packages using PIP

• QueryStore

  • Query Tuning Reccomandations
  • QueryStore_Duration

• Schema-Related

  • Bulk Move Schema
  • Find Column by name
  • Find Stored Procedure
  • Find primary key candidates

• Session Materials

  • How to use PowerBI as a Monitoring Tool (Slides and Report examples)

• Troubleshooting

  • How Many Rows has a DML Operation modified until now
  • ParallelTasks of a Session
  • Read TransactionLog User Actions HOW TO FIND A SAFE RESTORE POINT (AND WHO MESSED UP) BY READING FROM THE TRANSACTION LOG
  • TempDB Page Contention
  • sp_whoisactive_find_longrunning_blocked_query
  • Tests the current database against its check constraints and reports any data that would fail a check were it enabled

• Views

  • BlitzCache_MissingIndexesView - Filters the BlitzCache historic table for misssing indexes reports
  • JobAndSchedules - Shows SQL Agent Jobs and Schedules, by M.Pearson