Multiple ALTER Trigger Statements

I was trying to update multiple triggers in a single job step. The solution took me a while to come up with so I thought I would throw it in here. When running mulitple dynamic ALTER TRIGGER statements you quickly discover that you lose context. By using the sp_executesql stored procedure and specifying the database name you can preserv context during each execution.

IF OBJECT_ID('tempdb..#databases') IS NOT NULL
 DROP TABLE #databases
GO

CREATE TABLE #databases (
dbname VARCHAR(255))

DECLARE @dbname VARCHAR(255)
DECLARE @SQL NVARCHAR(MAX)

INSERT INTO #databases
SELECT name
FROM sys.databases
WHERE name LIKE ('Dbname%')
AND DATABASEPROPERTY(Name,'IsOffline') = 0
AND DATABASEPROPERTY(Name,'IsInLoad') = 0
AND DATABASEPROPERTY(Name,'IsInRecovery') = 0

WHILE EXISTS(SELECT dbname from #databases)
BEGIN
 SELECT TOP 1 @dbname = dbname FROM #databases

 SET @SQL = 'ALTER TRIGGER [dbo].[TriggerName] ON ' + @dbname + '.[dbo].[Table_Name] FOR UPDATE AS UPDATE Table_Name SET Col1=''''Value'''
 EXEC (@dbname + '..sp_executesql N''' + @SQL +'''''')
 SET @SQL = 'ALTER TRIGGER [dbo].[TriggerName2] ON ' + @dbname + '.[dbo].[TableName2] FOR UPDATE AS UPDATE TableName2 SET Col1=''''Value'''
 EXEC (@dbname + '..sp_executesql N''' + @SQL +'''''')

 DELETE #databases where dbname = @dbname
END

You can also add whatever else you need in the script. Sometimes the quotes are tough to nail on the first try. Convert those EXEC statements to PRINT so you can view and test the output to make sure you get the syntax correct.

Advertisements

Buffer Pool Throughput

Was watching a training vid by Paul Randall and stumbled on some of his Buffer Pool Throughput Queries.

First you find the total BufferPool size for your databases by running this query:


SELECT count(*)*8/1024)/1024 AS 'Data Cache Size(GB)',
 CASE database_id
 WHEN 32767 THEN 'RESOURCEDB'
 ELSE db_name(database_id)
 END AS 'DatabaseName'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Data Cache Size(GB)' DESC

88.69 GB was the value for one of my production database servers. Then you view your Page Life Expectancy counter to determine how quickly your buffer pool is being completely flushed:


SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

At the time of this article I was sitting at a PLE of 476,848 seconds, roughly 5.5 Days. Of course this number moves around but it gives a good indication of how much data is moving in/out of the buffer pool. I am flushing and refilling 88.69GB of data every 5.5 days.

Using Jonathan Kehayias’s PLE calculation of DataCacheSize / 4 * 300, I arrive at 26,609 seconds which is 7 hours or so. I will need to do some trending to see if that number holds true. But interesting calculations regardless.