SSMS Slowness

I’ve had some Hulk-like moments dealing with SSMS’s tendency to burn through all available memory, push usage to near 100%, and then freeze when I try to continue working in it. Classic “turn it off and turn it on again” fix works sometimes. I’ve even threatened it with uninstalling it (I think it knows I was bluffing). But today – today – I found a silver bullet to kill the beast. This post recommends turning off Auto-recovery. I tried it – and it works. Before, memory for SSMS was allocated at around 2.5gb, but now it’s a sweet and cuddly half gig. Yup, 80% reduction. Now, you may flinch at turning off Auto-recovery, and in principle I agree. The thing about that is, when SSMS has completely crashed with it on, it doesn’t always recover my most important documents. So, if you’re sick of SSMS slowness, consider this.

CREATE TABLE Script for Temp Table

Following up on the earlier queries that interrogated INFORMATION_SCHEMA.TABLES to get metadata on temp tables, this script builds on those to produce a CREATE TABLE statement. I find this useful for capturing the table-typed output of a stored procedure, especially if I need to capture that output in an INSERT..EXEC statement.

DECLARE @sql varchar(max); SET @sql = 'CREATE TABLE #capture_output (' + CHAR(13) + CHAR(10)
; WITH struct AS (
SELECT *
FROM TempDb.INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = OBJECT_NAME(OBJECT_ID('TempDb..#tmp'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))
)
SELECT
@sql += CHAR(9) + ',' + QUOTENAME(struct.[COLUMN_NAME]) + ' ' + struct.[DATA_TYPE]
+ ISNULL('(' + CASE struct.[DATA_TYPE]
WHEN 'varchar' THEN CONVERT(varchar, struct.[CHARACTER_MAXIMUM_LENGTH])
WHEN 'decimal' THEN CONVERT(varchar, struct.[NUMERIC_PRECISION]) + ', ' + CONVERT(varchar, struct.[NUMERIC_SCALE])
END
+ ')', '')
+ CHAR(13) + CHAR(10)
FROM struct
SET @sql = REPLACE(@sql, '(' + CHAR(13) + CHAR(10) + CHAR(9) + ',', '(' + CHAR(13) + CHAR(10) + CHAR(9)) + ')'
PRINT @sql

I realized that I don't have a link to a tip on the FOR XML trick for concatenating values over rows into one value. I like this one: https://www.mytecbits.com/microsoft/sql-server/concatenate-multiple-rows-into-single-string