Created
October 12, 2022 14:21
-
-
Save gwalkey/a3a740b59d3dd0fef21ddd3b4f2c7a32 to your computer and use it in GitHub Desktop.
Azure Data Studio Notebook for DBAs
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "metadata": { | |
| "kernelspec": { | |
| "name": "SQL", | |
| "display_name": "SQL", | |
| "language": "sql" | |
| }, | |
| "language_info": { | |
| "name": "sql", | |
| "version": "" | |
| } | |
| }, | |
| "nbformat_minor": 2, | |
| "nbformat": 4, | |
| "cells": [ | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Query Store - Queries that run longer than 30 Seconds" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "2b0f009d-af26-4b63-9b2e-15b153683186" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "SELECT TOP (100)\r\n", | |
| " p.query_id query_id,\r\n", | |
| "\tp.plan_id plan_id,\r\n", | |
| " q.object_id,\r\n", | |
| "\tISNULL(OBJECT_NAME(q.object_id),'Unknown') object_name,\r\n", | |
| " qt.query_sql_text query_sql_text,\r\n", | |
| "\tROUND(CONVERT(float, SUM(rs.max_duration*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2) max_duration_ms,\r\n", | |
| "\tMAX(rs.last_execution_time) AS 'LastExecutiontime',\r\n", | |
| " SUM(rs.count_executions) count_executions,\r\n", | |
| " COUNT(DISTINCT p.plan_id) num_plans\r\n", | |
| "FROM sys.query_store_runtime_stats rs\r\n", | |
| " JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id\r\n", | |
| " JOIN sys.query_store_query q ON q.query_id = p.query_id\r\n", | |
| " JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id\r\n", | |
| "WHERE \r\n", | |
| "\t(rs.last_execution_time >= '01-01-2022' AND rs.last_execution_time <= getdate())\r\n", | |
| "GROUP BY \r\n", | |
| "\tp.query_id, p.plan_id, qt.query_sql_text, q.object_id\r\n", | |
| "-- 15 Second Timeouts\r\n", | |
| "HAVING \r\n", | |
| "\tROUND(CONVERT(float, SUM(rs.max_duration*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2)>15000\r\n", | |
| "ORDER BY \r\n", | |
| "\t6 DESC\r\n", | |
| "" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "c7d5d61a-2d46-470e-b537-c886397b6f13", | |
| "language": "sql", | |
| "tags": [] | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Whats Running Now" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "c2b58876-52ea-46e5-bf1f-e49564ae7b4f" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "--- 1 Work In Process\r\n", | |
| "--- Ignore me - This is not the query you are looking for\r\n", | |
| "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED\r\n", | |
| "USE master\r\n", | |
| "GO\r\n", | |
| "\r\n", | |
| "SELECT\r\n", | |
| "\tsp.session_Id AS 'Spid'\r\n", | |
| "\t,er.blocking_session_id AS 'BlockedBy'\r\n", | |
| "\t,DB_NAME(er.database_id) AS 'Database'\r\n", | |
| "\t,Host_name AS 'HostName'\r\n", | |
| "\t,sp.login_name AS 'User'\r\n", | |
| "\t,er.command AS 'Command'\r\n", | |
| "\t,CASE er.transaction_isolation_level\r\n", | |
| "\t\tWHEN 1 THEN 'ReadUncommitted'\r\n", | |
| "\t\tWHEN 2 THEN 'ReadCommitted'\r\n", | |
| "\t\tWHEN 3 THEN 'Repeatable'\r\n", | |
| "\t\tWHEN 4 THEN 'Serializable'\r\n", | |
| "\t\tWHEN 5 THEN 'Snapshot'\r\n", | |
| "\t\tELSE 'Unspecified'\r\n", | |
| "\tEND\tAS TrnIsoLevel\r\n", | |
| "\t,er.dop AS 'Dop'\r\n", | |
| "\t,sp.open_transaction_count AS 'OpenTrans'\r\n", | |
| "\t,er.cpu_time AS 'CPUTime'\r\n", | |
| "\t,er.Logical_reads AS 'Logical Reads'\r\n", | |
| "\t,er.reads\r\n", | |
| "\t,er.writes\r\n", | |
| "\t,er.row_count AS 'Rows'\r\n", | |
| "\t,er.granted_query_memory as 'MemGrantKB'\r\n", | |
| "\t,er.status AS [Status]\r\n", | |
| "\t,er.wait_type AS [WaitType]\r\n", | |
| "\t,program_name AS 'Application'\r\n", | |
| "\t,p.query_plan as 'QueryPlan'\r\n", | |
| "\t,CAST('<?query --'+CHAR(13)+SUBSTRING(qt.text,\r\n", | |
| "\t(er.statement_start_offset / 2)+1,\r\n", | |
| "\t((CASE er.statement_end_offset\r\n", | |
| "\t\tWHEN -1 THEN DATALENGTH(qt.text)\r\n", | |
| "\t\tELSE er.statement_end_offset\r\n", | |
| "\t END - er.statement_start_offset)/2) + 1)+CHAR(13)+'--?>' AS XML) AS SqlStmt\r\n", | |
| "\t, qt.text AS [ParentQuery]\r\n", | |
| "\t,er.request_id AS 'RequestID'\r\n", | |
| "\t,er.percent_complete\r\n", | |
| "\t,start_time AS 'Started'\r\n", | |
| "\t,DATEADD(ms,er.estimated_completion_time,GETDATE()) AS [ETA Completion]\r\n", | |
| "\t,CONVERT(NUMERIC(10,2),er.estimated_completion_time/1000.0/60.0) AS [ETA Mins]\r\n", | |
| "FROM\r\n", | |
| "\tsys.dm_exec_requests er\r\n", | |
| "INNER JOIN\r\n", | |
| "\tsys.dm_exec_sessions sp\r\n", | |
| "ON\r\n", | |
| "\ter.session_id = sp.session_id\r\n", | |
| "OUTER APPLY\r\n", | |
| "\tsys.dm_exec_sql_text(er.sql_handle)AS qt\r\n", | |
| "OUTER APPLY\r\n", | |
| "\tsys.dm_exec_query_plan(er.plan_handle) p\r\n", | |
| "WHERE\r\n", | |
| "\tsp.is_user_process = 1\r\n", | |
| "\t/* sp.session_Id > 50\r\n", | |
| "\t-- Ignore system spids. -- */\r\n", | |
| "\tAND sp.session_Id <> @@SPID\r\n", | |
| "\t-- Search for Specific Query Text\r\n", | |
| "\t-- AND qt.text like N'%'+N'vStatement'+N'%'\r\n", | |
| "\t--ORDER BY \r\n", | |
| "\t--1, 2\r\n", | |
| "\t--cpu_time desc\r\n", | |
| "\tAND\tsp.program_name NOT LIKE 'DatabaseMail%'\r\n", | |
| "\r\n", | |
| "--- 2 Locking\r\n", | |
| "--- Look for any Locks blocking others \r\n", | |
| "SELECT\r\n", | |
| "\t*\r\n", | |
| "FROM\r\n", | |
| "\tsys.dm_os_waiting_tasks t\r\n", | |
| "inner join\r\n", | |
| "\tsys.dm_exec_connections c\r\n", | |
| "ON\r\n", | |
| "\tc.session_id = t.blocking_session_id\r\n", | |
| "cross apply\r\n", | |
| "\tsys.dm_exec_sql_text(c.most_recent_sql_handle) as h1\r\n", | |
| "\r\n", | |
| "--- 3 Current Sessions\r\n", | |
| "exec sp_who2\r\n", | |
| "\r\n", | |
| "--- 4 show all threads, use Process Explorer to suspend the thread KPID\r\n", | |
| "--select * from master..sysprocesses --where spid = 64\r\n", | |
| "" | |
| ], | |
| "metadata": { | |
| "azdata_cell_guid": "22349c03-a5c8-4a3c-a436-1027dfb3fc76", | |
| "language": "sql", | |
| "tags": [] | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Current Locks" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "c4c59a0f-cb3d-4ab0-bf96-f0da5513ef56" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "SELECT \r\n", | |
| " HostName,\r\n", | |
| " \"OS UserName\",\r\n", | |
| " Login, \r\n", | |
| " spid, \r\n", | |
| " \"Database\", \r\n", | |
| " TableID,\r\n", | |
| " \"Table Name\", \r\n", | |
| " IndID, \r\n", | |
| " \"Lock Type\", \r\n", | |
| " \"Lock Mode\", \r\n", | |
| "\tLM,\r\n", | |
| " Status, \r\n", | |
| " Resource,\r\n", | |
| " COUNT(*) AS \"Lock Count\"\r\n", | |
| "FROM (\r\n", | |
| " SELECT\r\n", | |
| " CONVERT(VARCHAR(30), RTRIM(P.HostName)) AS 'HostName',\r\n", | |
| " CONVERT(VARCHAR(30), RTRIM(P.nt_UserName)) AS 'OS UserName',\r\n", | |
| " CONVERT(VARCHAR(30), SUSER_SNAME(p.sid)) AS 'Login', \r\n", | |
| " CONVERT(SMALLINT,req_spid) AS 'spid',\r\n", | |
| " CONVERT(VARCHAR(30), DB_NAME(rsc_dbid)) AS 'Database',\r\n", | |
| " rsc_objid AS 'TableID',\r\n", | |
| "\t\tCONVERT(VARCHAR(30), OBJECT_NAME(rsc_objid, rsc_dbid)) AS 'Table Name',\r\n", | |
| " rsc_indid AS 'IndID', \r\n", | |
| " CASE SUBSTRING (lock_type.name, 1, 4) \r\n", | |
| " WHEN '' THEN 'None'\r\n", | |
| " WHEN 'DB' THEN 'Database'\r\n", | |
| " WHEN 'FIL' THEN 'File'\r\n", | |
| " WHEN 'IDX' THEN 'Index'\r\n", | |
| " WHEN 'TAB' THEN 'Table'\r\n", | |
| " WHEN 'PAG' THEN 'Page'\r\n", | |
| " WHEN 'KEY' THEN 'Key'\r\n", | |
| " WHEN 'EXT' THEN 'Extent'\r\n", | |
| " WHEN 'RID' THEN 'Row ID'\r\n", | |
| " WHEN 'APP' Then 'Application'\r\n", | |
| " Else SubString (lock_type.name, 1, 4)\r\n", | |
| " END AS 'Lock Type',\t\t\r\n", | |
| " Case SubString (lock_mode.name, 1, 12)\r\n", | |
| " When NULL Then 'N/A'\r\n", | |
| " When 'Sch-S' Then 'SCHEMA (Stability)'--'SCHEMA stability lock'\r\n", | |
| " When 'Sch-M' Then 'SCHEMA (Modification)'--'SCHEMA modification lock'\r\n", | |
| " When 'S' Then 'SHARED'--'SHARED Lock acquisition'\r\n", | |
| " When 'U' Then 'UPDATE'--'UPDATE lock acquisition'\r\n", | |
| " When 'X' Then 'EXCLUSIVE'--'EXCLUSIVE lock granted'\r\n", | |
| " When 'IS' Then 'SHARED (Intent)'--'INTENT for SHARED lock'\r\n", | |
| " When 'IU' Then 'UPDATE (Intent)'--'INTENT for UPDATE lock'\r\n", | |
| " When 'IX' Then 'EXCLUSIVE (Intent)'--'INTENT for EXCLUSIVE lock'\r\n", | |
| " When 'SIU' Then 'SHARED (Intent UPDATE)'--'SHARED lock with INTENT for UPDATE'\r\n", | |
| " When 'SIX' Then 'SHARED (Intent EXCLUSIVE)'--'SHARED lock with INTENT for EXCLUSIVE'\r\n", | |
| " When 'UIX' Then 'UPDATE'--'UPDATE lock with INTENT for EXCLUSIVE'\r\n", | |
| " When 'BU' Then 'UPDATE (BULK)'--'BULK UPDATE lock'\r\n", | |
| " Else SubString (lock_mode.name, 1, 12)\r\n", | |
| " END AS 'Lock Mode', \r\n", | |
| "\t\tSubString (lock_mode.name, 1, 12) AS 'LM',\r\n", | |
| " SubString(lock_status.name, 1, 5) AS 'Status',\r\n", | |
| " SubString (rsc_text, 1, 16) AS 'Resource'\r\n", | |
| " FROM \r\n", | |
| " Master..SysLockInfo S\r\n", | |
| " JOIN Master..spt_values lock_type on S.rsc_type = lock_type.number\r\n", | |
| " JOIN Master..spt_values lock_status on S.req_status = lock_status.number\r\n", | |
| " JOIN Master..spt_values lock_mode on S.req_mode = lock_mode.number -1\r\n", | |
| " JOIN Master..SysProcesses P on S.req_spid = P.spid\r\n", | |
| " WHERE\r\n", | |
| " lock_type.type = 'LR'\r\n", | |
| " AND lock_status.type = 'LS'\r\n", | |
| " AND lock_mode.type = 'L'\r\n", | |
| " AND DB_Name(rsc_dbid) NOT IN ('master', 'msdb', 'model')\r\n", | |
| " ) AS X\r\n", | |
| "WHERE TableID > 0\r\n", | |
| "GROUP BY \r\n", | |
| " [HostName],\r\n", | |
| " [OS UserName],\r\n", | |
| " [Login], \r\n", | |
| " [spid], \r\n", | |
| " [Database], \r\n", | |
| " [TableID],\r\n", | |
| " [Table Name], \r\n", | |
| " [IndID], \r\n", | |
| " [Lock Type], \r\n", | |
| " [Lock Mode], \r\n", | |
| "\t[LM],\r\n", | |
| " [Status],\r\n", | |
| "\tresource\r\n", | |
| "ORDER BY\r\n", | |
| " [spid], [Database], [Table Name], [Lock Type], [Login]\r\n", | |
| "\r\n", | |
| "--- Check the Messages Tab\r\n", | |
| "DBCC opentran" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "89c38cdd-03a5-41a4-9503-54b16f95eb4d" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Index Performance" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "49e3476e-f7f7-47f1-8b90-2d95d058b7af" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "--- Index Scan/Seek/Lookup/Update Breakdown\r\n", | |
| "\r\n", | |
| "SELECT\r\n", | |
| "\tDB_NAME([ddius].[database_id]) AS [database name],\r\n", | |
| "\ts.[name] AS 'Schema_Name',\r\n", | |
| "\tOBJECT_NAME([ddius].[object_id]) AS [Table name],\r\n", | |
| "\tCASE\r\n", | |
| "\t\tWHEN ddius.index_id=1 THEN '*'+[i].[name]+'*'\r\n", | |
| "\t\tELSE [i].[name]\r\n", | |
| "\tEND AS [index name],\r\n", | |
| "\ti.is_unique,\t\r\n", | |
| "\tddius.database_id,\r\n", | |
| "\t--ddius.object_id,\r\n", | |
| "\tddius.index_id,\r\n", | |
| "\tp.partition_number,\r\n", | |
| "\tddius.user_seeks,\r\n", | |
| "\tddius.user_scans,\r\n", | |
| "\tddius.user_lookups,\r\n", | |
| "\tddius.user_updates,\r\n", | |
| "\tddius.system_seeks,\r\n", | |
| "\tddius.system_scans,\r\n", | |
| "\tddius.system_lookups,\r\n", | |
| "\tddius.system_updates,\r\n", | |
| "\tCAST(p.used_page_count * 0.0078125 AS NUMERIC(18,2)) AS 'UsedPages_MB',\r\n", | |
| "\tp.[ROW_COUNT] AS 'RowCount'\r\n", | |
| "FROM\r\n", | |
| "\t[sys].[dm_db_index_usage_stats] AS ddius\r\n", | |
| "INNER JOIN\r\n", | |
| "\t[sys].[indexes] AS i\r\n", | |
| "ON\r\n", | |
| "\t[ddius].[index_id] = [i].[index_id] AND [ddius].[object_id] = [i].[object_id]\r\n", | |
| "JOIN\r\n", | |
| "\tsys.dm_db_partition_stats p\r\n", | |
| "ON\t\r\n", | |
| "\ti.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id\r\n", | |
| "JOIN\r\n", | |
| "\tsys.objects O\r\n", | |
| "ON O.object_id = ddius.object_id\r\n", | |
| "JOIN\r\n", | |
| "\tsys.schemas S\r\n", | |
| "ON S.schema_id = O.schema_id\r\n", | |
| "WHERE\r\n", | |
| "\tOBJECT_NAME([ddius].[object_id])<>'sysdiagrams' -- filter out sysdiagrams UML table\r\n", | |
| " AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1 -- User Tables Only\r\n", | |
| "\tAND ddius.index_id > 0 -- filter out heaps\r\n", | |
| "\tAND ddius.database_id = db_id() -- current DB only\r\n", | |
| "\tAND ddius.index_id>1 -- NCIX only\r\n", | |
| "ORDER BY\r\n", | |
| "\t1,2,3" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "3d81f509-f920-4341-a41f-fa5d672baf01" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| }, | |
| { | |
| "cell_type": "markdown", | |
| "source": [ | |
| "# Connections Per Database" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "5c431d77-886a-4c66-877f-a2f33c7ad1a3" | |
| }, | |
| "attachments": {} | |
| }, | |
| { | |
| "cell_type": "code", | |
| "source": [ | |
| "--- Simple\r\n", | |
| "SELECT\r\n", | |
| "\t@@SERVERNAME AS 'Server',\r\n", | |
| "\tDB_NAME(dbid) as 'Database',\r\n", | |
| "\tCOUNT(dbid) as 'Number Of Open Connections'\r\n", | |
| "FROM\r\n", | |
| "\tsys.sysprocesses\r\n", | |
| "WHERE\r\n", | |
| "\tdbid > 0\r\n", | |
| "GROUP BY\r\n", | |
| "\tdbid\r\n", | |
| "with rollup\r\n", | |
| "order by\r\n", | |
| "\t2\r\n", | |
| "\r\n", | |
| "--- Detailed\r\n", | |
| "SELECT\r\n", | |
| "\t@@SERVERNAME AS 'Server',\r\n", | |
| "\tsd.name DBName,\r\n", | |
| "\tsp.dbid,\r\n", | |
| " loginame [Login],\r\n", | |
| " hostname,\r\n", | |
| "\t[program_name] 'Program Name',\r\n", | |
| "\t[spid],\r\n", | |
| " last_batch LastBatch,\r\n", | |
| " blocked BlkBy,\r\n", | |
| "\tsp.[status],\r\n", | |
| "\tcmd Command,\r\n", | |
| " cpu CPUTime,\r\n", | |
| " physical_io DiskIO\r\n", | |
| "FROM sysprocesses sp \r\n", | |
| "JOIN sysdatabases sd ON sp.dbid = sd.dbid\r\n", | |
| "where sp.loginame<>'sa'\r\n", | |
| "ORDER BY 2, 4" | |
| ], | |
| "metadata": { | |
| "language": "sql", | |
| "azdata_cell_guid": "a093cba9-a724-4910-a9a1-a7ae488d36f8" | |
| }, | |
| "outputs": [], | |
| "execution_count": null | |
| } | |
| ] | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment