Lonely Secondary Log Shipping

Have you ever had that rare situation where you need log shipping to another server, but have zero access to the primary server? I encountered that issue recently and my solution was two parts.

A Secondary Log Shipping Target without a Primary. Pretty Cool.

I had the need to consume transaction logs into a StandBy(ReadOnly) database, and continue apply logs to keep it in sync. I could then write code to extract data from that database to the Data Warehouse / Operational Data Store. The original Setup was a traditional Log shipping setup, but the decision was made to allow the Vendor to host the data in their own data center instead of on-prem. The vendor agreed to take an initial backup and put transaction log backups into some Azure blob storage for me to pickup and consume.

My solution ended up to use azcopy.exe and PowerShell to grab the files form blob storage featuring a Shared Access Signature to put them in a folder local to the secondary server, and the to configure the “Lonely Secondary Log Shipping”, so that only the secondary was aware of the arrangement. This has a huge advantage, because the log shipping already has the logic to read the headers of the backups, decide which files with the desired lsn needs to be restored, and cleans up older files automatically. There was no need to re-invent the wheel here.

The PowerShell Portion was fairly simple and clean. I put azcopy.exe in a specific folder on my secondary server, and tracked down the specifics I would need:

  • Created the Shared Access Signature for the storage account with a ten year expiry so I don’t have to look at it again.
  • copied the url for the storage account.
  • Identified the specific Blob and sub directory where the files are being placed.
##copied to this location
$azcopy = 'D:\Data\PowerShell\Azure\azcopy.exe'

## provided, unique
$SharedAccessSignature = '?sv=2020-08-04&ss=bfqt&srt=sco&sp=rwdlacupitfx&se=2032-04-27T09:51:47Z&st=2022-04-27T01:51:47Z&spr=https&sig=nzU%2AnRK6iDC1YYehK7HvoZtPxNVTwpnfxhgGboF6lZc%3D'

## Variables and Defaults
$SourceContainer = "https://totallyfakestorageblobname.blob.core.windows.net/allscripts-pm-db-replication/DataSourceDB/*"
$FinalContainer = $SourceContainer + $SharedAccessSignature
$DestinationDirectory = "D:\logshipping\DataSourceDB"
if(![System.IO.Directory]::Exists($DestinationDirectory)){[System.IO.Directory]::CreateDirectory($DestinationDirectory)}

## Copy Command from Blob to Local Disk, only File extensions common to SQL Backups
&$azcopy copy $FinalContainer $DestinationDirectory --include-pattern '*.bak;*.trn' --overwrite=false

For the secondary Log shipping, I adapted code I had already automated via PowerShell to create only the secondary portions, and parameterized it for SQL instead. little improvements over the default log shipping, like customized names, and staggering the restore to be five minutes after the copy are just pieces that make this a bit more robust. the Primary Server name can be totally fake, we never will connect to it, but it should at least imply where it really comes from. I of course had to restore the full backup first, but after that, there are only four @parameters to change to make this work for yourself.





--#################################################################################################
-- leverage Log Shipping Secondary where the primary may be doing full and transaction logs,
-- but no access between the two
--#################################################################################################
--restore the full backup first, in standby read only
USE [master]
RESTORE DATABASE [DataSourceDB] 
FROM  DISK = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB\DataSourceDB_backup_2022_04_27_071611.bak' 
WITH  FILE = 1,  
MOVE N'DataSourceDB' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\DataSourceDB.mdf',  
MOVE N'DataSourceDB_log' 
TO N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\DataSourceDB_log.ldf',  
--NORECOVERY,
STANDBY = N'\\mydomain\DataManagement_Backup\SQLBackups\MyProdServer\DataSourceDB_RollbackUndo_2022-04-27_07-17-25.bak',  
NOUNLOAD,  
STATS = 5
GO
--On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
--On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
--On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
--SKIPPED AS No AccessOn the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
--On the secondary server, enable the copy and restore jobs. For more information, see Disable or Enable a Job.
DECLARE 
@LSPrimaryServer NVARCHAR(128) = 'OffDomainSourceServer',
@LSPrimaryDatabase NVARCHAR(128) = 'DataSourceDB',
--@LSSecondaryServer NVARCHAR(128) = @@SERVERNAME,
@LSSecondaryDatabase NVARCHAR(128) = 'DataSourceDB',
--this is the folder where we expect the log shipping files
@BasePath VARCHAR(8000) = 'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\logshipping\'

DECLARE @copyjobname NVARCHAR(128)    = N'LSCopy_' + @LSPrimaryServer + '_for_' + @LSSecondaryDatabase,
        @restorejobname NVARCHAR(128) = N'LSRestore ' + '_for_' + @LSPrimaryDatabase + '_' + @LSSecondaryDatabase
  DECLARE @ErrorSeverity INT,
                @ErrorNumber INT,
                @ErrorMessage NVARCHAR(4000),
                @ErrorState INT,
                @ErrorLine INT;
DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId  AS uniqueidentifier 
DECLARE @LS_Add_RetCode             AS int 


EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
        @primary_server = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@backup_source_directory = @BasePath 
        ,@backup_destination_directory = @BasePath 
        ,@copy_job_name = @copyjobname
        ,@restore_job_name = @restorejobname
        ,@file_retention_period = 1440 
        ,@overwrite = 1 
        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier 
DECLARE @LS_SecondaryCopyJobScheduleID  AS int 


EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultCopyJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 000500 --note copy @ 05 min, restore @10 min
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__CopyJobId 
        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  

DECLARE @LS_SecondaryRestoreJobScheduleUID  As uniqueidentifier 
DECLARE @LS_SecondaryRestoreJobScheduleID   AS int 


EXEC msdb.dbo.sp_add_schedule 
        @schedule_name =N'DefaultRestoreJobSchedule' 
        ,@enabled = 1 
        ,@freq_type = 4 
        ,@freq_interval = 1 
        ,@freq_subday_type = 4 
        ,@freq_subday_interval = 60 --every 60 minutes
        ,@freq_recurrence_factor = 0 
        ,@active_start_date = 20090505 
        ,@active_end_date = 99991231 
        ,@active_start_time = 001000 --note copy @ 05 min, restore @10 min
        ,@active_end_time = 235900 
        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 

EXEC msdb.dbo.sp_attach_schedule 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  


END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

DECLARE @LS_Add_RetCode2    As int 


IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
        @secondary_database = @LSSecondaryDatabase 
        ,@primary_server = @LSPrimaryServer 
        ,@primary_database = @LSPrimaryDatabase 
        ,@restore_delay = 0 
        ,@restore_mode = 1  --standby read only
        ,@disconnect_users = 1 --hell yes disconnect those guys
        ,@restore_threshold = 180   
        ,@threshold_alert_enabled = 1 
        ,@history_retention_period = 2880
        ,@overwrite = 1

END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

IF (@@error = 0 AND @LS_Add_RetCode = 0) 
BEGIN 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__CopyJobId 
        ,@enabled = 1 

EXEC msdb.dbo.sp_update_job 
        @job_id = @LS_Secondary__RestoreJobId 
        ,@enabled = 1 

END 
ELSE
BEGIN
        SELECT @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorNumber   = ERROR_NUMBER(),
               @ErrorMessage  = ERROR_MESSAGE(),
               @ErrorState    = ERROR_STATE(),
               @ErrorLine     = ERROR_LINE()
RAISERROR('ErrorNumber %d, Level %d, State %d, Line %d Err: %s',@ErrorSeverity,@ErrorState,@ErrorNumber,@ErrorSeverity,@ErrorState,@ErrorLine,@ErrorMessage);
END

Performance Tuning

Code Performance improvements are a layered approach. I think of it as multiple passes and focii to

  • Statistics. slightly out of date stats on huge tables can greatly affect performance.
  • Index Optimization: Review missing indexes, remove unused indexes.
  • Data Management Views: find what is slow on the whole server, and start digging in. Not just procedures, but ad-hoc, triggers, Scalar functions all have DMVs.
  • Execution Plans. Lots of great information about what it is doing, and with experience, you would know what specific operators imply as far as performance enhancement opportunities .
  • the Code itself. I have a long list of the things I look for in the code to avoid performance bottlenecks.

Some of the items we check for:
• Joins are Sarg-able, meaning all data types exactly match on both sides of the equals, no functions on columns, no implicit conversions
• WHERE is Sarg-able, meaning all data types exactly match on both sides of the equals, no functions on columns, no implicit conversions
• @Parameter data types and sizes exactly match the size of columns they are used against
• Replace @TableVariables with #Temp tables to leverage statistics, unless the @TableVariable has less than 100 rows in every situation.
• multiple CTEs or cascading CTEs should be replaced with #Temp tables to divide and conquer for better performance
• anything that uses a VIEW or multiple views needs to be replaced with the underlying tables instead to eliminate duplicate or extra tables, remove convenience for performance.
• convert any scalar functions to inline table value functions, and changes to use OUTER APPLY/CROSS APPLY for values.
• Because FOR XML concatenation is performed early, any concatenation statements must come from a #temp table and not the source, as the WHERE statements are applied after FOR XML is performed.
• Relevant recent comment on every deployed procedure or view
• Every table should be fully qualified as SchemaName.TableName
• Every table should have an alias with Table AS AliasName
• Every column should have alias.columnname
• uses SET NOCOUNT ON in first lines of the procedure
• uses SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in first lines of the procedure as appropriate –remove WITH(NOLOCK) hints in favor of this isolation level
• Handles temp tables proactively, and in both try and catch due to connection pooling issues from java drivers
• Try-Catch is being used as a best practice
• Uses ContentManagement.EXCEPTION.spLogException in the catch statement for better error reporting
• ProcedureParameters is populated with relevant data for better error reporting
• No nvarchars if not relevant
• avoid SELECT *(Enumerate columns, avoid unneeded)
• avoid INSERT INTO #temp WITH ORDER BY(Adds unneeded sort operation)
• avoid EXISTS featuring TOP (WHERE EXISTS(SELECT TOP 1 1…..
• avoid UPDATE on OriginalTableName instead of Alias to avoid an implicit accidental cross join of original table

Without touching the code, I have an incredibly helpful script that finds missing index opportunities, and conveniently scripts out the create command, and keeps a comment header with the script so I know just how valuable it was when i did the analysis.

After that, it’s straight to the DMV’s. I have a nice collection to review performance of Procedures, Ad Hoc queries with plans, and other for lesser impact like the dmvs for trigger performance and scalar function performance. All of them are designed to show me the top x poorest performers, and let me see what tuning opportunities there might be.

Who is this Lowell guy anyway?

Hi I am Lowell Izaguirre, and I’ve am a cutting edge Senior Database Administrator for SQL Server. I’ve got the paper pedigree for those who care, two MCSA’s and an MCSE as well, but the key is my experience, deep scripting capabilities, and the tools I use to make myself the fastest, most efficient DBA possible.

My Daily work is using PowerShell , SSIS or jobs and procedures to automate tasks to free up my time for other things; this has had the side effect of eliminating swaths of work that used to be done manually by my other DBA’s or analysts. I am not automating you out of a job, I am automating so you have more time to work on other tasks.

A lot of people get hung up on titles, but I find a title kind of pigeon holes what people think of you. Enterprise Architect is presumptuous and arrogant, Operational DBA makes it seem like you only monitor jobs and push deployments, Performance DBA, seems to say you just code and look at missing indexes , and Developer makes it seem like you don’t know how to DBA. Where does all the experience with Snowflake, Azure and AWS and Aurora/MySQL fit? Cloud Database Administrator? Code Monkey works better for me. I would love to hear what you think. My Job calls me a “Senior Data Engineer” to imply we do more than just SQL server.


Here’s just some of the tools I use to make me one of the top DBA’s in the country.

SQL Server Management Studio, but on steroids. I use a lot of paid third party plug ins to make my code bullet proof and keep my massive 300+ Snippet collection at my finger tips on every work station I touch. SSMS Tools Pack keeps my Snippets at my fingertips, and also helps me capture and save every SQL statement I execute in SSMS, in a nice searchable format, and also build a custom header rich with information about the server connection. It has to be seen to be really understood how valuable that is.

Redgate’s SQL Prompt boosts my productivity daily, as I use it for these things daily:

  • Help write code faster with it’s Intellisense enhancements
  • Make my code work in case sensitive environments.
  • Add square brackets for better code formatting
  • Qualify object names for better code formatting
  • Export result sets to Excel for fast ad-hoc reports and info dumps.

A Suite of Automatically generated Reports from SSIS package I wrote, as well as PowerShell Open XML scripting I have written. Those reports execute on a regular schedule, and are often reference for the hundreds of various report focus items I need to review or cross check. Each report is well named, so a DBA can know it’s purpose at a glance, and the report contains information about every SQL server in Central Management Servers’ list of servers. Very helpful when you need to go back and find how something was or was not in place over time.

sp_getddla and sp_getddl

If you have ever worked with SQL Server, and wanted to script a table, you know it is a GUI process, that behind the scenes, is calling SQL Management Objects(SMO) to create the script for you, which you can then copy and paste.

But what if you want to script the table and put it in a column in some table some place? can it be done via TSQL?

Well the answer may surprise you. Microsoft does not make it possible as a built in feature,

Yuo can google it up yourself: Every other solution out there involves using something OUTSIDE of sql server to do it: Common Language Runtime, PowerShell, xp_cmdshell etc to do the work elsewhere and return some results.

but if you look hard enough, if you dig deep enough, you can find all the elements of the table, or any object in fact, in the meta data tables like sys.tables, sys.columns, and a whole slew of system objects, and actually script it yourself.

Coming up with this solution was not for the faint of heart; it requires a stored procedure that is over a thousand lines of code,thousands of hours over the years in order to pull all the elements together into a single cohesive output. This works on everything 2008 and above. Tested in SQL2008 through SQL2022, as well as Azure SQL Database/Azure Managed Instances(but not as a system proc), AWS RDS SQL Versions as well.

sp_GetDDLa_Latest.txt sp_GetDDL_Latest.txt

I am constantly improving this ten-plus year old procedure with more improvements as I trip over yet one more feature that I find I need to include.

Here is a scripted table as an example of some of this scripts actions:

[dbo].[USZipCodes]

The bad news: I don’t take unpaid requests for enhancements. If you suggest something, I’ll think about it, but we all have our own priorities. What I do ask, is that if you make a change and feel it is valuable , send me a copy, so I can consider merging it into my code base.

What it does

  • sp_getddla returns multi line results, perfect for copying and pasting
  • sp_getddl returns a varchar(max) string, perfect for automation, inserting into a column for example.
  • when placed in master and marked as system object, it works in every database.
  • Handles CaSe SensITive Collations
  • Scripts any Table in the current database
  • Scripts any in scope #temp table
  • Scripts any Schema
  • Scripts any object(procedure, function, view, trigger)
  • all tables are fully qualified as [schemaname].[tablename]
  • includes a drop if exists…create format. be careful!
  • Scripts includes all constraints: default/primary key/unique/foreign keys/check constraints.
  • includes the indexes on a table definition
  • includes the trigger(s) on the table definition

What it does not do

  • Does not script NVARCHAR tables like [データベース](Database in Japanese), because , well, because I don’t NEED it to.
  • Does not handle partitioned tables…script currently creates an ugly stub of the table, with maybe one column.
  • memory optimized tables
  • tables with temporal logging tables (that is a work in progress, partially complete)

Usage Notes and Improvements Over Time

--#################################################################################################
-- Real World DBA Toolkit version 5.08 Lowell Izaguirre lowell@stormrage.com
--#################################################################################################
-- USAGE: exec sp_GetDDLa GMACT
--   or   exec sp_GetDDLa 'bob.example'
--   or   exec sp_GetDDLa '[schemaname].[tablename]'
--   or   exec sp_GetDDLa #temp
--#################################################################################################
-- copyright 2004-2022 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
--developer utility function added by Lowell, used in SQL Server Management Studio 
-- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt
--Purpose: Script Any Table, Temp Table or Object(Procedure Function Synonym View Table Trigger)
--#################################################################################################
-- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx
-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced .The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#################################################################################################
--
-- V300  uses String concatination and sys.tables instead of a cursor
-- V301  enhanced 07/31/2009 to include extended properties definitions
-- V302  fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found
-- V303  fixes an issue where all rules are appearing, instead of just the rule related to a column
-- V304  testing whether vbCrLf is better than just CHAR(13) or CHAR(10), some formatting cleanup with GO statements
--       also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]
-- V305  user feedback helped me find that the type_name function should call user_type_id instead of system_type_id
--       also fixed issue where identity definition missing from numeric/decimal definition
-- V306  fixes the computed columns definition that got broken/removed somehow in V300
--       also formatting when decimal is not an identity
-- V307  fixes bug identified by David Griffiths-491597 from SSC where the  @TABLE_ID
--       is reselected, but without it's schema  , potentially selecting the wrong table
--       also fixed is the missing size definition for varbinary, also found by David Griffith
-- V308  abstracted all SQLs to use Table Aliases
--       added logic to script a temp table.
--       added warning about possibly not being marked as system object.
-- V309  added logic based on feedback from Vincent Wylenzek @SSC to return the definition from sys.sql_modules for
--       any object like procedure/view/function/trigger, and not just a table. 
--       note previously, if you pointed sp_GetDDLa at a view, it returned the view definition as a table...
--       now it will return the view definition instead.
-- V309a returns multi row recordset, one line per record 
-- V310a fixed the commented out code related to collation identified by moadh.bs @SSC
--       changed the DEFAULT definitions to not include the default name.
-- V310b Added PERSISTED to calculated columns where applicable
-- V310b fixed COLLATE statement for temp tables
-- V310c fixed NVARCHAR size misreported as doubled.
-- V311  fixed issue where indexes did not identify if the column was ASC or DESC found by nikus @ SSC
-- V311a fixed issue where indexes did not identify if the index was CLUSTERED or NONCLUSTERED found by nikus @ SSC 02/22/2013
-- V312  got rid of all upper casing, and allowing all scripts to generate the exact object names in cases of case sensitive databases.
--       now using the case sensitive name of the table passed: so of you did 'exec sp_getDDLA invoicedocs , it might return the script for InvoiceDocs, as that is how it is spelled in sys.objects.
--       added if exists(drop table/procedure/function) statement to the scripting automatically.
--       toggled the commented out code to list any default constraints by name, hopefully to be more accurate..
--       formatting of index statements to be multi line for better readability
--V314   03/30/2015
--       did i mention this scripts out temp tables too? sp_getDDLa #tmp
--       scripts any object:table,#temptable procedure, function, view or trigger
--       added ability to script synonyms
--       moved logic for REAL datatype to fix error when scripting real columns
--       added OmaCoders suggestion to script column extended properties as well.
--       added matt_slack suggestion to script schemaname as part of index portion of script.
--       minor script cleanup to use QUOTENAME instead of concatenating square brackets.
--       changed compatibility to 2008 and above only, now filtered indexes with WHERE statements script correctly
--       foreign key tables and columns  in script now quotenamed to account for spaces in names; previously an error for Application ID instead of [Application ID]
--V315   Fixes Aliases and column names that prevented Case Sensitive collations from working.
--       Adds code if the procedure scripted is a system object
--       index scripts featuring filtered indexes is now included
--       index scripts now include filegroup name and compression settings
--       foreign key cascade delete/update settings now included as identified by Alberto aserio@SSC)
--       Fixes related to scripting extended events  as identified by Alberto aserio@SSC)
--V316   Fixes Identified 07/27/2016 by mlm( m.martinelli@SSC)
--       Added logic  resolving error when custom data type are defined using name greater than 16 char.
--       Added handling for data types: binary, datetime2, datetimeoffset, time
--       Added Set Based logic for Handling FOREIGN KEYS when one foreign key is define on more then one field
--       Added SPARSE column property
--V317   Fixes Identified 03/30/2017 by Lowell
--       Scripting of Foreign key column(s) are now quotenamed
--       Scripting column store indexes was broken, now fixed for column store indexes
--V318   Fixes Identified 02/14/2018 by Lowell
--       Scripting of with collation added/required for scripting SharePoint/ReportServer , or databases with non standard collations
--       Scripting enhanced to definitively handle case sensitive collations as well.

Massive tempdb usage implies a missing index opportunity

Tripped over a great example yesterday, where an application was recently upgraded, and ran out of temp db space for the tables, and not the log. The TLDR was that an inefficient query was sticking 500MB of data into a tempdb table to satisfy a query, and the query was called hundreds of times, with various parameters.

How it all happened: I had participated in a third party application upgrade over the weekend, so I was a bit more alert for anything related to that newly upgraded application than I otherwise would be for a long owned app stack.

I got an automated space alert about tempdb log running out of space. By the time I connected, the mount point that contains tempdb files and log was already at zero space. Normally, you would look and say “tempdb log out of space, it has gotta be a long running transaction” I look and the mdf/ndf files are 2.5x their normal size, 250GB instead of 90GB, and the log file is a measly 3GB. yes, there was an open transaction, but that was because log space ran out, and it could not write anymore. so the mdf/ndf were bloating like crazy for some reason.

I used my envwho wrapper for Adam Machanic’s sp_whoisactive to review current queries. I found a stack /dozens of queries running effectively the same query from the application, but with different parameters, and all of them had huge numbers related to CPU, tempdb allocations, reads and writes, as seen below.

Grabbing the query itself, it looks very simple, and really implies no indexes, right? I see a table, the columns in the WHERE will map as the columns in the index i need, and two columns in the INCLUDE portion.

<?query --
SELECT DataID, Name FROM DTree WHERE Status = 1 AND SubType = 482
--?>

My next step was to review the indexes on the table, I doubted there was a matching index but maybe there was one that looked the same, and i could modify it. I ran my EXECUTE sp_GetDDLa ‘DTree’ via a keyboard shortcut to get the table definition, which I found was actually a VIEW instead.

IF OBJECT_ID('[dbo].[DTree]') IS NOT NULL 
DROP  VIEW      [dbo].[DTree] 
GO
CREATE VIEW DTree AS SELECT * FROM DTreeCore WHERE Deleted = 0
GO

And now the core issue jumped right into my face. a view, on a column with no selectivity, [Deleted] = 0. I know that requires a table scan, and with the other columns from the WHERE clause, that’s going to spool into tempdb. yuck. how big is the table? 7.8 M rows.

So At this point, the app is now effectively shut down, no queries going through, and I loop in the lead developer to let him see what I see via Teams. My options are ask for more space, maybe fail the instance over to the other node on it’s cluster to clear all bloated connections that have jammed up tempdb, and then fix it via this indexing opportunity. I got our engineering team to add some space, and as soon as that was added, I got a salute of ran index from the Lead developer, and created an index(my initials _LI_ in the index name so I can differentiate it form pre-existing indexes)

CREATE INDEX IX_LI_DTreeCore_Status_SubType ON [dbo].[DTreeCore] (Status,SubType) INCLUDE (DataID, NAME) WHERE (Deleted = 0)

I immediately went back to my previous query, and see the index greatly satisfied the query and performance; CPU very low, often no tempdb, reads and writes near zero.

Problem solved, and I sent a typical email notification tot he business groups about my findings and solution.