{
"metadata": {
"kernelspec": {
"name": "SQL",
"display_name": "SQL",
"language": "sql"
},
"language_info": {
"name": "sql",
"version": ""
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"# **DBA ToolKit**\n",
"\n",
"This notebook is designed for the Production DBA needing answers to typical questions that will be asked of you from your colleagues or managers. \n",
"\n",
"Nothing in here is rocket science and there was no \"secret sauce\" applied. This is a collection of scripts I have accumulated over the last 10+ years being a production DBA. \n",
"\n",
"I can not take credit for all of these scripts aka \"code snippets\". I will site the source of the script when applicable.\n",
"\n",
"\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\n",
"\n",
"\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*\\*"
],
"metadata": {
"azdata_cell_guid": "00186375-42b5-4491-9848-69bd3d1cd836"
},
"attachments": {}
},
{
"cell_type": "markdown",
"source": [
"## **Transaction Log Management**\n",
"\n",
"- Will help you determine the size of your Transaction logs\n",
"- Checks if File Growth is set to percentage or a hard value and provides the statement to set to a hard value. \n",
"- Checks if a log backup is required to clear the log. Will return the log\\_reuse\\_wait\\_desc value and recovery model\n",
"- Some times you might want to set the DB to Simple Recovery mode to clear the log. That statement is provided for you. \n",
"- If needed the Shrink command is also a part of the output.\n",
"\n",
"This was written as a challenge from my Senior DBA (Sean McCown) at the time, to all of the junior and mid-level DBAs to help us understand TLog Management"
],
"metadata": {
"azdata_cell_guid": "c9f3bc41-9f40-41b1-b881-215e01457cc7"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"Use Master; \r\n",
"\r\n",
"SET NOCOUNT ON; \r\n",
"\r\n",
"IF OBJECT_ID(N'tempdb..#BackupSet') IS NOT NULL\r\n",
"BEGIN\r\n",
"DROP TABLE #BackupSet\r\n",
"END\r\n",
"\r\n",
"declare @bup_date datetime2\r\n",
"set @bup_date =dateadd(hour,-4, getdate())\r\n",
"\r\n",
"SELECT REVERSE(SUBSTRING(REVERSE(physical_device_name),\r\n",
" CHARINDEX('\\',\r\n",
" REVERSE(physical_device_name)),\r\n",
" LEN(physical_device_name))) as BUP_Path, \r\n",
"\t\t\t\t\t\t\t\t\t database_name, ROW_NUMBER() over (partition by Database_name order by backup_start_date desc, physical_device_name) as RowNumb\r\n",
"into #BackupSet\r\n",
"FROM msdb.dbo.backupmediafamily\r\n",
" INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id\r\n",
"WHERE type = 'L'\r\n",
" AND backup_start_date > @bup_date\r\n",
"\r\n",
"Delete #BackupSet\r\n",
"where RowNumb <> 1\r\n",
"\r\n",
"\r\n",
"SET NOCOUNT ON \r\n",
"\r\n",
"IF OBJECT_ID('tempdb..#TempForLogSpace') IS NOT NULL\r\n",
" BEGIN\r\n",
" DROP TABLE #TempForLogSpace;\r\n",
" END;\r\n",
"\r\n",
"CREATE TABLE #TempForLogSpace\r\n",
" (\r\n",
" DataBaseName VARCHAR(100) ,\r\n",
" LogSize NUMERIC(18, 4) ,\r\n",
" LOgPercentage NUMERIC(18, 4) ,\r\n",
" Status INT\r\n",
" );\r\n",
"\r\n",
"INSERT INTO #TempForLogSpace\r\n",
" EXEC ( 'DBCC sqlperf(logspace) WITH NO_INFOMSGS'\r\n",
" );\r\n",
"\r\n",
"SELECT t.DataBaseName,\r\n",
"\t\tsmf.name AS 'Logical Log File Name' ,\r\n",
" CASE WHEN smf.is_percent_growth = 0\r\n",
" THEN 'file is set to a fixed value and is considered best practice'\r\n",
" WHEN smf.is_percent_growth = 1\r\n",
" THEN 'See modify_log_file_statement---->'\r\n",
" END AS log_growth_setting ,\r\n",
" CASE WHEN smf.is_percent_growth = 0 THEN '--no alter DB statement needed'\r\n",
" WHEN smf.is_percent_growth = 1\r\n",
" THEN 'ALTER DATABASE '+sd.name + CHAR(13)\r\n",
" + 'MODIFY FILE ' + CHAR(13) + '(NAME = ''' + smf.name\r\n",
" + ''',' + CHAR(13) + 'FILEGROWTH = 512MB)'\r\n",
" END AS Modify_Log_File_statement ,\r\n",
" t.LogSize / 1024 AS 'Log Size (GB)' ,\r\n",
" t.LOgPercentage AS 'Log Space Used(%)' ,\r\n",
" CASE WHEN sd.log_reuse_wait_desc = 'LOG_BACKUP'\r\n",
" THEN 'please backup the log---->'\r\n",
" WHEN sd.log_reuse_wait_desc = 'ACTIVE_TRANSACTION'\r\n",
"THEN 'you have an active transaction..it must complete or be killed before log will clear'\r\n",
"ELSE 'no log backup required'\r\n",
" END AS Log_Backup_Action ,\r\n",
"sd.log_reuse_wait_desc ,\r\n",
"sd.recovery_model_desc,\r\n",
" 'Backup log '+ quotename(sd.name) +' to disk = ''' + ISNULL(BUP_Path,'c:\\')\r\n",
" +sd.name+'_'+replace(replace(replace(replace(convert(varchar(20),getdate(),126),'-',''),'T',''),':',''),'.','')\r\n",
"\t\t +'_Tlog_backup.trn'' with stats = 5' AS Backup_Statement ,\r\n",
" 'Use ' + quotename(t.DataBaseName) + ';' + CHAR(13) + 'DBCC SHRINKFILE ('\r\n",
" + smf.name + ' , 1024)' AS SHRINKFILE_statement ,\r\n",
" 'Alter Database '+sd.name+' SET Recovery Simple' AS Change_Recovery_Model_to_Clear_log ,\r\n",
" smf.physical_name AS Log_File_Location\r\n",
"FROM #TempForLogSpace AS t\r\n",
" INNER JOIN sys.databases AS sd ON t.DataBaseName = sd.name\r\n",
" INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id\r\n",
"\t\tLeft Join #BackupSet b on t.DataBaseName = b.database_name\r\n",
"WHERE smf.type_desc = 'log'\r\n",
"Order by [Log Size (GB)] desc"
],
"metadata": {
"azdata_cell_guid": "a1fee9a1-7585-4b75-85fb-afec79e6c51f",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Backup and Restore Estimated Completion Time**\n",
"\n",
"- Sometimes you want to know how much longer a backup or restore statement will take. This will give you a rough estimate."
],
"metadata": {
"azdata_cell_guid": "d9361549-99a0-48e0-8624-8e9c194f7b14"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"Select Command, \r\n",
"\t\tPercent_Complete, \r\n",
"\t\tDATEADD(ss,Estimated_Completion_Time/1000,getdate()) AS Estimated_Completion_Time,\r\n",
"\t\tSqlText.Text AS SQL_Statement\r\n",
"From sys.dm_exec_requests \r\n",
"CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext\r\n",
"\tWhere Command Like '%Restore%' or Command Like '%Backup%'\t "
],
"metadata": {
"azdata_cell_guid": "f53f4158-b6b2-481a-a5bb-f4a75d342960",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Backup History**\n",
"\n",
"- Query to find your backup history. I based my initial script from this Microsoft query. \n",
" - [https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-ver16)"
],
"metadata": {
"azdata_cell_guid": "3e8bb61c-2d2c-4461-897a-631f01dcdcf5"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"--How far back to you want to go?\r\n",
"--Set @daysback to how many days back you want to go\r\n",
"\r\n",
"DECLARE @daysback int \r\n",
" SET @daysback = 90\r\n",
"\r\n",
"SELECT \r\n",
" CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, \r\n",
" msdb.dbo.backupset.database_name, \r\n",
" msdb.dbo.backupset.backup_start_date, \r\n",
" msdb.dbo.backupset.backup_finish_date, \r\n",
" CASE msdb..backupset.type \r\n",
" WHEN 'D' THEN 'Database' \r\n",
" WHEN 'L' THEN 'Log' \r\n",
"\t WHEN 'I' THEN 'Diff'\r\n",
"\t WHEN 'F' THEN 'FileGroup' \r\n",
" END AS backup_type, \r\n",
" msdb.dbo.backupset.backup_size/1024.0/1024.0/1024.0 as backup_size_GBs, \r\n",
" msdb.dbo.backupset.compressed_backup_size/1024.0/1024.0/1024.0 as compressed_backup_size_GBs, \r\n",
" msdb.dbo.backupmediafamily.physical_device_name\r\n",
"FROM msdb.dbo.backupmediafamily \r\n",
" INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id\r\n",
" --find backup history based on getdate() minus a specific number of days \r\n",
"WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - @daysback) AND msdb.dbo.backupset.database_name NOT IN ('master','msdb','tempdb','model')\r\n",
"ORDER BY \r\n",
" msdb.dbo.backupset.database_name, \r\n",
" msdb.dbo.backupset.backup_finish_date\r\n",
""
],
"metadata": {
"azdata_cell_guid": "3126f477-95b9-4041-9118-da0f81b3909b",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Last Backup** \n",
"\n",
"Find the last backup of all DBs on an instance"
],
"metadata": {
"azdata_cell_guid": "011b134b-1e5a-4656-a881-a2312cd948af"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"\r\n",
";with backup_cte as\r\n",
"(\r\n",
" select\r\n",
" database_name,\r\n",
" backup_type =\r\n",
" case type\r\n",
" when 'D' then 'database'\r\n",
" when 'L' then 'log'\r\n",
" when 'I' then 'differential'\r\n",
" else 'other'\r\n",
" end,\r\n",
" backup_finish_date,\r\n",
"\t\tphysical_device_name,\r\n",
" rownum = \r\n",
" row_number() over\r\n",
" (\r\n",
" partition by database_name, type \r\n",
" order by backup_finish_date desc\r\n",
" )\r\n",
" FROM msdb.dbo.backupmediafamily \r\n",
"\t\tINNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id\r\n",
")\r\n",
"select\r\n",
" Database_Name,\r\n",
" Backup_Type,\r\n",
"\tphysical_device_name as Backup_Location,\r\n",
" Backup_Finish_Date\r\n",
"from backup_cte\r\n",
"where rownum = 1;"
],
"metadata": {
"azdata_cell_guid": "ef714984-c0f2-46f0-b3d8-8b5337893671",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Finding the Size of your Databases**\n",
"\n",
"- I have seen a lot of folks do this in the GUI. Why do that when its so easy via code.\n",
"- There are 3 result sets. \n",
" - Total Database Size for the instance\n",
" - Database Size for each Database\n",
" - Size of each Data File with a Running total per Database"
],
"metadata": {
"azdata_cell_guid": "23580afb-a38f-4402-bd47-1a3caf277a15"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SELECT \r\n",
"\t@@ServerName as Instance_Name, SUM((size*8)/1024.0/1024.0) Total_Database_Size_GBs\r\n",
"FROM sys.master_files\r\n",
"\r\n",
"SELECT \r\n",
"\tDB_NAME(database_id) AS Database_Name,\r\n",
"\tSUM((size*8)/1024.0/1024.0) Database_Size_GBs\r\n",
"FROM sys.master_files\r\n",
"GROUP BY database_id\r\n",
"\r\n",
"SELECT \r\n",
"\tDB_NAME(database_id) AS Database_Name,\r\n",
"\tName as Logical_Name,\r\n",
"\tType_Desc as Data_File_Type,\r\n",
"\t(size*8)/1024.0/1024.0 as Database_Size_GBs,\r\n",
"\tSUM((size*8)/1024.0/1024.0) over (partition by DB_NAME(database_id) order by size) as Running_Total_Per_DB \r\n",
"FROM sys.master_files\r\n",
"order by Database_Name "
],
"metadata": {
"azdata_cell_guid": "f5ebf503-0351-4f63-bea1-c3d2a30afd60",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Resource vs Signal Waits**\n",
"\n",
"SQL Server has 3 states that can be assigned to a query\n",
"\n",
"- Running..query that is currently running on a CPU\n",
" \n",
"- Runnable.. query that is ready to execute but waiting for an available CPU\n",
" \n",
"- Suspended..query is waiting for a third-party to become available\n",
" \n",
"- Resources waits refers to a query waiting on aresource to go to the runnable queue\n",
" \n",
"- Signal waits refers to a query waiting to move onto the CPU with a state of runnable\n",
" \n",
"\n",
"The higher percentage of Resource Waits, as compared to the Signal Waits is an indication of a healthy SQL Server. \n",
"\n",
"If the percentage is much higher for the Signal waits, then we need to determine if the bottleneck is the CPU processors themselves. \n",
"\n",
"The query below is taken from this post.\n",
"\n",
"- [Signal and Resource Waits Explained.](http://www.sqlservercentral.com/blogs/pearlknows/2010/12/3/signal-waits-and-resource-waits-a-clarification/)"
],
"metadata": {
"azdata_cell_guid": "7e71a604-7057-44ea-a353-b7cadd175160"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"Select \r\n",
"\tsum(signal_wait_time_ms) as Signal_Wait_Time_ms,\r\n",
" cast(100.0 * sum(signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) as Signal_Waits_Percent,\r\n",
" sum(wait_time_ms - signal_wait_time_ms) as Resource_Wait_Time_ms,\r\n",
" cast(100.0 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) as numeric(20,2)) as Resource_Waits_Percent\r\n",
"from sys.dm_os_wait_stats"
],
"metadata": {
"azdata_cell_guid": "8468bbae-5243-47cc-949d-66576647156e",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Fixing Orphaned Users**\n",
"\n",
"When using SQL Auth, orphaned users can be an issue when restoring database from one server to another. \n",
"\n",
"_\\*uncomment the --EXEC (@sql) to execute the code._\n",
"\n",
"- Create temp table #login with ident column to loop on\n",
"- Insert into temp table from sp\\_change\\_users\\_login'report'\n",
"- Declare and populate variables \n",
"- Loop on the ID column\n",
"- And your done"
],
"metadata": {
"azdata_cell_guid": "c559091f-5279-4454-80a0-f7613f6e8164"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"IF OBJECT_ID('tempdb..#logins') IS NOT NULL\r\n",
"BEGIN\r\n",
"DROP TABLE #logins\r\n",
"END\r\n",
"\r\n",
"CREATE TABLE #logins\r\n",
"(id INT IDENTITY,\r\n",
"NAME VARCHAR(50),\r\n",
"usersid VARBINARY(max)\r\n",
")\r\n",
"\r\n",
"INSERT INTO #logins\r\n",
"EXEC sp_change_users_login 'report'\r\n",
"\r\n",
"DECLARE @i SMALLINT \r\n",
"set @i = (SELECT MIN(id) FROM #logins)\r\n",
"\r\n",
"DECLARE @sql VARCHAR(500)\r\n",
"\r\n",
"--loop to loop\r\n",
"\r\n",
"WHILE @I <=(SELECT MAX(id) FROM #logins)\r\n",
"BEGIN\r\n",
"\tSELECT @sql = 'sp_change_users_login ''update_one'','''+name+''','+''''+name+''''\r\n",
"\tFROM #logins\r\n",
"\tWHERE id = @i \r\n",
"\r\n",
"Select @sql \r\n",
"--EXEC(@sql)\r\n",
"SET @i = @i +1\r\n",
"END"
],
"metadata": {
"azdata_cell_guid": "985a99b7-5004-41c3-99cc-ef8337da1211",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Statistics**\n",
"\n",
"**F**ind the current state of your statistics for a specific database.\n",
"\n",
"- Must change DB context\n",
"- Some of columns returned\n",
" - Rows Sampled at Last Stats Update\n",
" - Percentage Sampled \n",
" - Percent Changed via the modification\\_counter"
],
"metadata": {
"azdata_cell_guid": "3d174c4f-2f57-471b-98e9-92b77a8aedfb"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"Use SQLSentry;\r\n",
"\r\n",
"SELECT sp.Stats_Id ,\r\n",
" sc.name AS Schema_Name ,\r\n",
" OBJECT_NAME(s.object_id) AS Table_Name ,\r\n",
" s.name AS Stats_Name ,\r\n",
" sp.Last_Updated ,\r\n",
" sp.Rows as Rows_In_Table ,\r\n",
" sp.rows_sampled as Rows_Sampled_at_Last_Stats_Update,\r\n",
" CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,\r\n",
" CASE WHEN sp.modification_counter = 0 THEN 0\r\n",
" ELSE CAST(sp.modification_counter AS NUMERIC)\r\n",
" / CAST(sp.rows AS NUMERIC) * 100\r\n",
" END AS Percent_Changed ,\r\n",
" CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'\r\n",
" ELSE CAST(sp.modification_counter AS VARCHAR(20))\r\n",
" END AS Modification_Counter\r\n",
"FROM sys.stats AS s\r\n",
" CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp\r\n",
" JOIN sys.tables t ON t.object_id = s.object_id\r\n",
" JOIN sys.schemas sc ON sc.schema_id = t.schema_id\r\n",
"WHERE sp.rows is not null \r\n",
"\tORDER BY s.object_id"
],
"metadata": {
"azdata_cell_guid": "1af17867-9274-46c2-90fc-457766df0b0f",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Cursor Syntax Example**\n",
"\n",
"I never can remember cursor syntax and I always have to look it up. Cursors are not bad, if used correctly!!\n",
"\n",
"Here is a very simple example of a cursor. \n",
"\n",
"Check out this link for an \"under the covers\" look at cursors. There are some good performance tips in the article. \n",
"\n",
"- [Performance Considerations when using Cursors](http://bradsruminations.blogspot.com/2010/05/truth-about-cursors-part-1.html)"
],
"metadata": {
"azdata_cell_guid": "7f5eafdf-5ac8-4035-a4b5-88db673cba54"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"/************************************\r\n",
"DECLARE @var int\r\n",
"DECLARE @cmd varchar(200)\r\n",
"\r\n",
"DECLARE C CURSOR LOCAL STATIC READ_ONLY\r\n",
"FOR\r\n",
"SELECT col1 from dbo.sometable\r\n",
"OPEN C\r\n",
"\r\n",
"FETCH NEXT FROM C INTO @var\r\n",
"\r\n",
"WHILE (@@FETCH_STATUS=0)\r\n",
"\tBEGIN\r\n",
"\r\n",
"\tSet @cmd = ('Do Something '+ @var)\r\n",
"\r\n",
"\tExec (@cmd);\r\n",
"\r\n",
"\tFETCH NEXT FROM C INTO @var\r\n",
"\tEND\r\n",
"\r\n",
"CLOSE C\r\n",
"DEALLOCATE C\r\n",
"GO\r\n",
"\r\n",
"************************************/"
],
"metadata": {
"azdata_cell_guid": "f94ad991-95a3-4a76-bc34-11ec53534edd",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Kill All Sessions for a User DB**\n",
"\n",
"Here is a way, using a cursor to kill sessions for a User DB. I have commented out the execute statement. If you are brand new to SQL Server might want to hold off on executing this script until you are sure you know what you are doing."
],
"metadata": {
"azdata_cell_guid": "4127bf71-95db-462d-b5a1-bfa10eb46412"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"DECLARE @user_spid int\r\n",
"DECLARE @cmd varchar(200)\r\n",
"\r\n",
"DECLARE C CURSOR LOCAL STATIC READ_ONLY\r\n",
"FOR\r\n",
"SELECT session_id as 'SPID'\r\n",
"FROM sys.dm_exec_requests\r\n",
"where database_id = db_id('StackOverFlow')\r\n",
"\r\n",
"OPEN C\r\n",
"\r\n",
"FETCH NEXT FROM C INTO @user_spid\r\n",
"\r\n",
"WHILE (@@FETCH_STATUS=0)\r\n",
"\tBEGIN\r\n",
"\t\r\n",
"\t\tPRINT 'Killing '+CONVERT(VARCHAR,@user_spid)\r\n",
"\t\tset @cmd = 'KILL ' +CONVERT(VARCHAR,@user_spid)+';'\r\n",
"\t\t--Execute (@cmd)\r\n",
"\tFETCH NEXT FROM C INTO @user_spid\r\n",
"\tEND\r\n",
"\r\n",
"CLOSE C\r\n",
"DEALLOCATE C\r\n",
"GO\r\n",
""
],
"metadata": {
"azdata_cell_guid": "87d5002f-bd95-43a7-b22b-43f1b1bdfcb4",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Disk Space Query**\n",
"\n",
"Not really sure where I got this query. Pretty standard stuff."
],
"metadata": {
"azdata_cell_guid": "4be20e5d-7eeb-468b-b40c-fffeaf0cc8e7"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SELECT DISTINCT\r\n",
" vs.volume_mount_point,\r\n",
" vs.file_system_type,\r\n",
" vs.logical_volume_name,\r\n",
" CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)],\r\n",
" CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)],\r\n",
" CONVERT(DECIMAL(18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]\r\n",
"FROM sys.master_files AS f WITH (NOLOCK)\r\n",
" CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs\r\n",
"ORDER BY vs.volume_mount_point\r\n",
"OPTION (RECOMPILE);"
],
"metadata": {
"azdata_cell_guid": "20a14cdf-3dd5-4cd7-a03e-755f378cc1fd",
"language": "sql",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Finding the Median Value for Query Cost from the Plan Cache**\n",
"\n",
"When trying to set a value for Cost Threshold for Parallelism, most folks these days will set it at 50 or 75. If you want to have more of a scientific approach, check out this query. \n",
"\n",
"This will return the Median value of statement subtree cost from the plan cache for queries that went parallel. Median being the middle value when a data set is ordered from least to greatest. \n",
"\n",
"I can not take credit for this code, not sure where I got it."
],
"metadata": {
"azdata_cell_guid": "a9d9e29a-73f3-4601-8d4b-b7275e200efc"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"\tIf OBJECT_ID('tempdb..#SubtreeCost') is not null \r\n",
"\tBegin \r\n",
"\tDrop Table #SubtreeCost \r\n",
"\tEnd \r\n",
"\r\n",
" CREATE TABLE #SubtreeCost(StatementSubtreeCost decimal(18, 2));\r\n",
" WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')\r\n",
" INSERT INTO #SubtreeCost\r\n",
" SELECT\r\n",
" CAST(n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS decimal(18, 2))\r\n",
" FROM sys.dm_exec_cached_plans AS cp\r\n",
" CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp\r\n",
" CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)\r\n",
" WHERE n.query('.').exist('//RelOp[@PhysicalOp=\"Parallelism\"]') = 1;\r\n",
"\r\n",
"select Count(*) NumberofParallelPlansFound from #SubtreeCost\r\n",
"\r\n",
" SELECT\r\n",
" (\r\n",
" (\r\n",
" SELECT TOP 1\r\n",
" StatementSubtreeCost\r\n",
" FROM\r\n",
" (\r\n",
" SELECT TOP 50 PERCENT\r\n",
" StatementSubtreeCost\r\n",
" FROM #SubtreeCost\r\n",
" ORDER BY\r\n",
" StatementSubtreeCost ASC\r\n",
" ) AS A\r\n",
" ORDER BY\r\n",
" StatementSubtreeCost DESC\r\n",
" ) +\r\n",
" (\r\n",
" SELECT TOP 1\r\n",
" StatementSubtreeCost\r\n",
" FROM\r\n",
" (\r\n",
" SELECT TOP 50 PERCENT\r\n",
" StatementSubtreeCost\r\n",
" FROM #SubtreeCost\r\n",
" ORDER BY\r\n",
" StatementSubtreeCost DESC\r\n",
" ) AS A\r\n",
" ORDER BY\r\n",
" StatementSubtreeCost ASC\r\n",
" )) / 2 as MedianStatementSubTreeCost\r\n",
"\r\n",
"\r\n",
""
],
"metadata": {
"azdata_cell_guid": "ffefc49d-1b2e-403e-b32c-6e5973b77528",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Last Startup Time for all SQL Server Services**\n",
"\n",
"This will save you a lot of time trying to answer this question. \n",
"\n",
"This code can be found in Glen Berry's set of diagnostic scripts. \n",
"\n",
"- [Glen Berry Diagnostic Scripts](https://glennsqlperformance.com/resources/)"
],
"metadata": {
"azdata_cell_guid": "6ff6d27e-a25a-4890-a156-6467cb53ab1f"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SELECT\tServiceName, \r\n",
"\t\tStartup_Type_Desc, \r\n",
"\t\tStatus_Desc, \r\n",
"\t\tLast_StartUp_Time, \r\n",
"\t\tService_Account, \r\n",
"\t\tIs_Clustered, \r\n",
"\t\tCluster_NodeName, \r\n",
"\t\tFileName as BinaryLocation, \r\n",
"\t\tInstant_File_Initialization_Enabled \r\n",
"FROM sys.dm_server_services"
],
"metadata": {
"azdata_cell_guid": "6334d9f3-871b-4d0d-91a6-4c83064480aa",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **SQL Server Memory Consumption and Memory State**\n",
"\n",
"I have often times found diving into memory consumption in SQL Server to be a bit tricky. Here is a query that I have tweaked over the years to give me better insights into whats going on from a memory perspective. \n",
"\n",
"Check out the following links regarding the underlying DMVs used in the query. \n",
"\n",
"- [sys.dm\\_os\\_sys\\_memory](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql?view=sql-server-ver16)\n",
"- [sys.dm\\_os\\_process\\_memory](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-process-memory-transact-sql?view=sql-server-ver16)"
],
"metadata": {
"azdata_cell_guid": "57a1706d-f292-4f2a-825c-917c81888443"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SELECT \r\n",
" SERVERPROPERTY('SERVERNAME') AS 'Instance',\r\n",
" (SELECT cast(value_in_use as int)/1024.0 FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory GB',\r\n",
" (SELECT physical_memory_in_use_kb/1024.0/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (GB)',\r\n",
" (SELECT total_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (GB)',\r\n",
" (SELECT available_physical_memory_kb/1024.0/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (GB)',\r\n",
" (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',\r\n",
" (SELECT ((total_physical_memory_kb - available_physical_memory_kb)*1.0/total_physical_memory_kb*1.00 )*100.00 FROM sys.dm_os_sys_memory) as Mem_Consumed_Percentage,\r\n",
" (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',\r\n",
" GETDATE() AS 'Data Sample Timestamp'"
],
"metadata": {
"azdata_cell_guid": "675fe6ad-f65d-48e4-ae9d-b168a3bc1cb2",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Percentage of RollBack Complete**\n",
"\n",
"How long is that rollback going to take. This will give you an estimation"
],
"metadata": {
"azdata_cell_guid": "cdd027f2-9b28-4164-bce3-2d53a69b5dac"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SELECT \r\n",
" session_id, \r\n",
" percent_complete, \r\n",
" DATEADD(ss,Estimated_Completion_Time/1000,getdate()) AS Estimated_Completion_Time,\r\n",
" last_wait_type, \r\n",
" wait_resource, \r\n",
" wait_time, \r\n",
" wait_type \r\n",
"FROM sys.dm_exec_requests \r\n",
" WHERE status = 'rollback'"
],
"metadata": {
"azdata_cell_guid": "2151ec18-f612-450c-a8d7-875714eac3c3",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **AG Status**\n",
"\n",
"I got this query from a customer, Adrian Mioduszewski, for giving you some insight into the health of your AGs. It is a lot of the data that you will find in the AG dashboard."
],
"metadata": {
"azdata_cell_guid": "d1fe4eb1-6b63-4fae-b8d2-2731bddfbf94"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"SET NOCOUNT ON;\r\n",
" \r\n",
"DECLARE @AGname NVARCHAR(128);\r\n",
" \r\n",
"DECLARE @SecondaryReplicasOnly BIT;\r\n",
" \r\n",
"SET @AGname = NULL; --SET AGname for a specific AG for SET to NULL for ALL AG's\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpag_availability_groups') IS NOT NULL\r\n",
"DROP TABLE [#tmpag_availability_groups];\r\n",
" \r\n",
"SELECT *\r\n",
"INTO [#tmpag_availability_groups]\r\n",
"FROM [master].[sys].[availability_groups];\r\n",
" \r\n",
"IF(@AGname IS NULL\r\n",
"OR EXISTS\r\n",
"(\r\n",
"SELECT [Name]\r\n",
"FROM [#tmpag_availability_groups]\r\n",
"WHERE [Name] = @AGname\r\n",
"))\r\n",
"BEGIN\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpdbr_availability_replicas') IS NOT NULL\r\n",
"DROP TABLE [#tmpdbr_availability_replicas];\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpdbr_database_replica_cluster_states') IS NOT NULL\r\n",
"DROP TABLE [#tmpdbr_database_replica_cluster_states];\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states') IS NOT NULL\r\n",
"DROP TABLE [#tmpdbr_database_replica_states];\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpdbr_database_replica_states_primary_LCT') IS NOT NULL\r\n",
"DROP TABLE [#tmpdbr_database_replica_states_primary_LCT];\r\n",
" \r\n",
"IF OBJECT_ID('TempDB..#tmpdbr_availability_replica_states') IS NOT NULL\r\n",
"DROP TABLE [#tmpdbr_availability_replica_states];\r\n",
" \r\n",
"SELECT [group_id],\r\n",
"[replica_id],\r\n",
"[replica_server_name],\r\n",
"[availability_mode],\r\n",
"[availability_mode_desc]\r\n",
"INTO [#tmpdbr_availability_replicas]\r\n",
"FROM [master].[sys].[availability_replicas];\r\n",
" \r\n",
"SELECT [replica_id],\r\n",
"[group_database_id],\r\n",
"[database_name],\r\n",
"[is_database_joined],\r\n",
"[is_failover_ready]\r\n",
"INTO [#tmpdbr_database_replica_cluster_states]\r\n",
"FROM [master].[sys].[dm_hadr_database_replica_cluster_states];\r\n",
" \r\n",
"SELECT *\r\n",
"INTO [#tmpdbr_database_replica_states]\r\n",
"FROM [master].[sys].[dm_hadr_database_replica_states];\r\n",
" \r\n",
"SELECT [replica_id],\r\n",
"[role],\r\n",
"[role_desc],\r\n",
"[is_local]\r\n",
"INTO [#tmpdbr_availability_replica_states]\r\n",
"FROM [master].[sys].[dm_hadr_availability_replica_states];\r\n",
" \r\n",
"SELECT [ars].[role],\r\n",
"[drs].[database_id],\r\n",
"[drs].[replica_id],\r\n",
"[drs].[last_commit_time]\r\n",
"INTO [#tmpdbr_database_replica_states_primary_LCT]\r\n",
"FROM [#tmpdbr_database_replica_states] AS [drs]\r\n",
"LEFT JOIN [#tmpdbr_availability_replica_states] [ars] ON [drs].[replica_id] = [ars].[replica_id]\r\n",
"WHERE [ars].[role] = 1;\r\n",
" \r\n",
"SELECT [AG].[name] AS [AvailabilityGroupName],\r\n",
"[AR].[replica_server_name] AS [AvailabilityReplicaServerName],\r\n",
"[dbcs].[database_name] AS [AvailabilityDatabaseName],\r\n",
"ISNULL([dbcs].[is_failover_ready],0) AS [IsFailoverReady],\r\n",
"ISNULL([arstates].[role_desc],3) AS [ReplicaRole],\r\n",
"[AR].[availability_mode_desc] AS [AvailabilityMode],\r\n",
"CASE [dbcs].[is_failover_ready]\r\n",
"WHEN 1\r\n",
"THEN 0\r\n",
"ELSE ISNULL(DATEDIFF([ss],[dbr].[last_commit_time],[dbrp].[last_commit_time]),0)\r\n",
"END AS [EstimatedDataLoss_(Seconds)],\r\n",
"ISNULL(CASE [dbr].[redo_rate]\r\n",
"WHEN 0\r\n",
"THEN-1\r\n",
"ELSE CAST([dbr].[redo_queue_size] AS FLOAT) / [dbr].[redo_rate]\r\n",
"END,-1) AS [EstimatedRecoveryTime_(Seconds)],\r\n",
"ISNULL([dbr].[is_suspended],0) AS [IsSuspended],\r\n",
"ISNULL([dbr].[suspend_reason_desc],'-') AS [SuspendReason],\r\n",
"ISNULL([dbr].[synchronization_state_desc],0) AS [SynchronizationState],\r\n",
"ISNULL([dbr].[last_received_time],0) AS [LastReceivedTime],\r\n",
"ISNULL([dbr].[last_redone_time],0) AS [LastRedoneTime],\r\n",
"ISNULL([dbr].[last_sent_time],0) AS [LastSentTime],\r\n",
"ISNULL([dbr].[log_send_queue_size],-1) AS [LogSendQueueSize],\r\n",
"ISNULL([dbr].[log_send_rate],-1) AS [LogSendRate_KB/S],\r\n",
"ISNULL([dbr].[redo_queue_size],-1) AS [RedoQueueSize_KB],\r\n",
"ISNULL([dbr].[redo_rate],-1) AS [RedoRate_KB/S],\r\n",
"ISNULL(CASE [dbr].[log_send_rate]\r\n",
"WHEN 0\r\n",
"THEN-1\r\n",
"ELSE CAST([dbr].[log_send_queue_size] AS FLOAT) / [dbr].[log_send_rate]\r\n",
"END,-1) AS [SynchronizationPerformance],\r\n",
"ISNULL([dbr].[filestream_send_rate],-1) AS [FileStreamSendRate],\r\n",
"ISNULL([dbcs].[is_database_joined],0) AS [IsJoined],\r\n",
"[arstates].[is_local] AS [IsLocal],\r\n",
"ISNULL([dbr].[last_commit_lsn],0) AS [LastCommitLSN],\r\n",
"ISNULL([dbr].[last_commit_time],0) AS [LastCommitTime],\r\n",
"ISNULL([dbr].[last_hardened_lsn],0) AS [LastHardenedLSN],\r\n",
"ISNULL([dbr].[last_hardened_time],0) AS [LastHardenedTime],\r\n",
"ISNULL([dbr].[last_received_lsn],0) AS [LastReceivedLSN],\r\n",
"ISNULL([dbr].[last_redone_lsn],0) AS [LastRedoneLSN]\r\n",
"FROM [#tmpag_availability_groups] AS [AG]\r\n",
"INNER JOIN [#tmpdbr_availability_replicas] AS [AR] ON [AR].[group_id] = [AG].[group_id]\r\n",
"INNER JOIN [#tmpdbr_database_replica_cluster_states] AS [dbcs] ON [dbcs].[replica_id] = [AR].[replica_id]\r\n",
"LEFT OUTER JOIN [#tmpdbr_database_replica_states] AS [dbr] ON [dbcs].[replica_id] = [dbr].[replica_id]\r\n",
"AND [dbcs].[group_database_id] = [dbr].[group_database_id]\r\n",
"LEFT OUTER JOIN [#tmpdbr_database_replica_states_primary_LCT] AS [dbrp] ON [dbr].[database_id] = [dbrp].[database_id]\r\n",
"INNER JOIN [#tmpdbr_availability_replica_states] AS [arstates] ON [arstates].[replica_id] = [AR].[replica_id]\r\n",
"WHERE [AG].[name] = ISNULL(@AGname,[AG].[name])\r\n",
"ORDER BY [AvailabilityReplicaServerName] ASC,\r\n",
"[AvailabilityDatabaseName] ASC;\r\n",
" \r\n",
"/*********************/\r\n",
" \r\n",
"END;\r\n",
"ELSE\r\n",
"BEGIN\r\n",
"RAISERROR('Invalid AG name supplied, please correct and try again',12,0);\r\n",
"END;"
],
"metadata": {
"azdata_cell_guid": "f6bbfc74-f1a8-4d07-8af7-952ff0899d8d",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Table Size**\n",
"\n",
"Fastest, most efficient way to return table size metadata."
],
"metadata": {
"azdata_cell_guid": "662071fb-0a45-44ae-99f5-89301a2132e1"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"Use SQLSentry \r\n",
"go \r\n",
"\r\n",
"SELECT\r\n",
"GETDATE() AS time_collected,\r\n",
"@@SERVERNAME AS server_name,\r\n",
"DB_NAME() AS database_name,\r\n",
"SCH.name AS schema_name,\r\n",
"TBL.name AS table_name, \r\n",
"SUM(PART.ROWS) AS row_count\r\n",
"FROM sys.tables TBL\r\n",
"INNER JOIN sys.schemas SCH ON SCH.SCHEMA_ID = TBL.SCHEMA_ID\r\n",
"INNER JOIN sys.partitions PART ON TBL.OBJECT_ID = PART.OBJECT_ID\r\n",
"INNER JOIN sys.indexes IDX ON PART.OBJECT_ID = IDX.OBJECT_ID\r\n",
"AND PART.index_id = IDX.index_id\r\n",
"AND IDX.index_id < 2\r\n",
"GROUP BY SCH.name, TBL.OBJECT_ID, TBL.name\r\n",
"ORDER BY row_count desc, SCH.name, TBL.OBJECT_ID, TBL.name;"
],
"metadata": {
"azdata_cell_guid": "ec4ba645-faab-4474-8378-777ccba8a977",
"language": "sql",
"tags": []
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Find TempDB Contention**\n",
"\n",
"How to see if anything is waiting on TempDB data pages. Queries waiting on TempDB can also be seen in the Wait\\_Info column of SP\\_WhoIsActive e.g... PFS, GAM, SGAM waits."
],
"metadata": {
"azdata_cell_guid": "7cfaf445-647a-44bf-a00f-5a2a0228172e"
},
"attachments": {}
},
{
"cell_type": "markdown",
"source": [
"SELECT \\*\n",
"\n",
"FROM sys.dm\\_os\\_waiting\\_tasks\n",
"\n",
" WHERE resource\\_description like '2:%'"
],
"metadata": {
"azdata_cell_guid": "8dc193b7-9425-4d73-b6f1-10f026021761"
},
"attachments": {}
},
{
"cell_type": "markdown",
"source": [
"## **CPU Utilization**\n",
"\n",
"Easy way to find out how much CPU, SQL Server is consuming for the last 256 minutes. \n",
"\n",
"This query can be found in Glen Berry's set of diagnostic scripts. See the link below\n",
"\n",
"[Glenn Berry Diagnostic Scripts](https://glennsqlperformance.com/resources/)"
],
"metadata": {
"azdata_cell_guid": "61157044-1cad-4be7-b0df-345d04d8a2a6"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); \r\n",
"\r\n",
"SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization], \r\n",
" SystemIdle AS [System Idle Process], \r\n",
" 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], \r\n",
" DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] \r\n",
"FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, \r\n",
"\t\t\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') \r\n",
"\t\t\tAS [SystemIdle], \r\n",
"\t\t\trecord.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') \r\n",
"\t\t\tAS [SQLProcessUtilization], [timestamp] \r\n",
"\t FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] \r\n",
"\t\t\tFROM sys.dm_os_ring_buffers WITH (NOLOCK)\r\n",
"\t\t\tWHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' \r\n",
"\t\t\tAND record LIKE N'%%') AS x) AS y \r\n",
"ORDER BY record_id DESC OPTION (RECOMPILE);"
],
"metadata": {
"azdata_cell_guid": "247a1de1-4f5c-49ec-a398-001a9a652b06",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Using SP\\_BlitzCache to investigate the plan cache**\n",
"\n",
"Using the Blitz Family of scripts is a must for any DBA. See the links below for my info on these scripts. \n",
"\n",
"Find the Top 10 Consuming queries in your plan cache. If you want to narrow the result set down to a specific database then populate the @databasename variable. \n",
"\n",
"- CPU\n",
"- Reads\n",
"- Memory Grant \n",
"- Executions Per Minute\n",
"\n",
"[Sp\\_BlitzCache](https://www.brentozar.com/blitzcache/)\n",
"\n",
"[First Responder Tool Kit](https://www.brentozar.com/responder/)"
],
"metadata": {
"azdata_cell_guid": "e4fd24f7-1f09-4899-9189-9dac0b149051"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"--Find top 10 CPU consuming queries\r\n",
" exec sp_blitzcache @top = 10, @ignoresystemdbs = 1, @sortOrder = 'CPU' --, @hidesummary = 1, @databasename = ''\r\n",
"--find top 10 Read consuming queries\r\n",
" --exec sp_blitzcache @top = 10, @ignoresystemdbs = 1, @sortOrder = 'Reads' --, @hidesummary = 1, @databasename = ''\r\n",
"--find top 10 largest Memory Grants\r\n",
" --exec sp_blitzcache @top = 10, @ignoresystemdbs = 1, @sortOrder = 'Memory Grant' --, @hidesummary = 1, @databasename = ''\r\n",
"--find top 10 most executed queries\r\n",
" --exec sp_blitzcache @top = 10, @ignoresystemdbs = 1, @sortOrder = 'XPM' --, @hidesummary = 1, @databasename = ''\r\n",
""
],
"metadata": {
"azdata_cell_guid": "026da8ec-2139-4a97-8d51-f014066ab32e",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Using SP\\_PressureDetector**\n",
"\n",
"Erik Darling has put together a really great SP to help you identify issues around Memory and CPU pressure. \n",
"\n",
"If you are having ThreadPool or Resource\\_Semaphore waits you are going to want to check this out. \n",
"\n",
"See the link below for the documentation and a video on how to use. \n",
"\n",
"[SP\\_PressureDetector](https://erikdarlingdata.com/sp_pressuredetector/)"
],
"metadata": {
"azdata_cell_guid": "c012cd6a-bc1a-43e2-8797-9c727e0a93a6"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"sp_PressureDetector @what_to_check = 'Memory'\r\n",
"\r\n",
"--sp_PressureDetector @what_to_check = 'CPU'"
],
"metadata": {
"azdata_cell_guid": "4a1e5dd2-e49b-4dfd-8855-f9a5968650f8",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **Using SP\\_WhoIsActive**\n",
"\n",
"Working with different DBAs over the years, everyone uses SP\\_WhoIsActive differently. Here are a few examples of how I use it.\n",
"\n",
"First example is the wide open version of the SP. \n",
"\n",
"Other three examples are to find..\n",
"\n",
"- Lead Blockers\n",
"- Help find most read intensive query\n",
"- Help find most CPU intensive query\n",
"\n",
"Link to the documentation.\n",
"\n",
"[SP\\_WhoIsActive Documentation](http://whoisactive.com/docs/)"
],
"metadata": {
"azdata_cell_guid": "f62d461c-6838-49ba-8f4e-5064be282b56",
"language": ""
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
"EXEC sp_WhoIsActive\r\n",
"\r\n",
"/********************\r\n",
"EXEC sp_WhoIsActive\r\n",
" @find_block_leaders = 1,\r\n",
" @sort_order = '[blocked_session_count] DESC'\r\n",
"\r\n",
"EXEC sp_WhoIsActive\r\n",
" @delta_interval = 5,\r\n",
"\t @sort_order = '[reads_delta] DESC'\r\n",
"\r\n",
"EXEC sp_WhoIsActive\r\n",
" @delta_interval = 5,\r\n",
"\t @sort_order = '[CPU_delta] DESC'\r\n",
"\r\n",
"/******************** "
],
"metadata": {
"azdata_cell_guid": "ce8bc73b-cfd6-4473-83cc-60f61f96da61",
"language": "sql",
"tags": [
"hide_input"
]
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"## **What are my top waits?**\n",
"\n",
"Glen Berry has giving us a treasure trove of diagnostic scripts and his query for looking into Wait Stats is a gem. Also if you are not familiar with a specific wait type, SQLSkills has it for you. \n",
"\n",
"[Glenn Berry Diagnostic Scripts](https://glennsqlperformance.com/resources/)\n",
"\n",
"[SQLSkills Wait Types LIbrary](https://www.sqlskills.com/help/waits/)"
],
"metadata": {
"azdata_cell_guid": "411c0a58-3cc0-4979-8fc0-523f8dd5438a"
},
"attachments": {}
},
{
"cell_type": "code",
"source": [
""
],
"metadata": {
"azdata_cell_guid": "ba87a4c5-b25a-4f38-8159-2e39bccaf874",
"language": "sql"
},
"outputs": [],
"execution_count": null
}
]
}