Hi! I’m your SQL Server. I know you don’t usually listen to me, so I’ve decided to take a drastic step and find you where you spend all your time – Brent’s blog. Seriously, you need to spend less time on the intertubes and more time on the error logs. You’re lucky this post is about me, not about you, or else I’d also have to divulge the fact that you installed Adobe Flash on my desktop. Whoops, I just did.
Resolution 1: Keep in Touch with Loved Ones
Bad things have been happening to me all year long, but I’ve been keeping it secret from you. It’s time you and I were more honest with each other. To do this, I want you to write your name in my heart – and by that I mean, set yourself up as my operator. The below script sets you up as an operator and sends you alert emails whenever bad things happen. Before you run it, make these two changes:
- Change YourEmailAddress@Hotmail.com to your actual email
- Change 8005551212@cingularme.com to your actual phone’s email address (look up your provider)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
USE [msdb] GO EXEC msdb.dbo.sp_add_operator @name = N'The Database Administrator', @enabled = 1, @weekday_pager_start_time = 0, @weekday_pager_end_time = 235959, @saturday_pager_start_time = 0, @saturday_pager_end_time = 235959, @sunday_pager_start_time = 0, @sunday_pager_end_time = 235959, @pager_days = 127, @email_address = N'YourEmailAddress@Hotmail.com', @pager_address = N'8005551212@cingularme.com', @category_name = N'[Uncategorized]' GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 016', @message_id = 0, @severity = 16, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 016', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 017', @message_id = 0, @severity = 17, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 017', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 018', @message_id = 0, @severity = 18, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 018', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 019', @message_id = 0, @severity = 19, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 019', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 020', @message_id = 0, @severity = 20, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 020', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 021', @message_id = 0, @severity = 21, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 021', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 022', @message_id = 0, @severity = 22, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 022', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 023', @message_id = 0, @severity = 23, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 023', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 024', @message_id = 0, @severity = 24, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 024', @operator_name = N'The Database Administrator', @notification_method = 7 GO EXEC msdb.dbo.sp_add_alert @name = N'Severity 025', @message_id = 0, @severity = 25, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 1, @job_id = N'00000000-0000-0000-0000-000000000000' GO EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 025', @operator_name = N'The Database Administrator', @notification_method = 7 GO |
Resolution 2: Do a Data Detox Diet
Over the years, I’ve eaten a lot of bad stuff. It’s time to do a little cleansing ritual to purge myself. People have dumped in heaps of temporary backup tables that never got queried again. This query looks for heaps (tables without clustered indexes) that haven’t been accessed since the last server restart (or database restore/attach). Run it in my largest or most important databases:
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT SCHEMA_NAME(o.schema_id) AS [schema] ,OBJECT_NAME(i.object_id) AS [table] ,p.rows ,'EXEC sp_rename ''[' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(i.object_id) + ']'', ''[' + SCHEMA_NAME(o.schema_id) + '].[_ToBeDropped_' + OBJECT_NAME(i.object_id) + ']''' AS ScriptToRename ,'DROP TABLE ''[' + SCHEMA_NAME(o.schema_id) + '].[' + OBJECT_NAME(i.object_id) + ']''' AS ScriptToDrop FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE i.type_desc = 'HEAP' AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL ORDER BY rows desc |
These heaps are slowing down my backup times, my DBCC times, and my 100 meter sprint times. We can make some quick judgment calls based on table names – if they’re names like Sales_Backup2009BeforeDeployment, it’s probably a table someone made once as a backup – and then forgot about it. We could either rename them temporarily and then drop ’em in a few days, or just drop ’em outright. Standard warning about deleting data, blah blah blah. (Hey, my resolution wasn’t to be more cautious.)
Resolution 3: Lose Waits
Users love me, and they show it to me by feeding me huge dinners of data. I love my life, don’t get me wrong, but sometimes I see those new guys with their solid state drives and their 512GB of memory, and I think, wow, they must be able to handle anything. I’ll never be that well-endowed, but there’s still some easy things I can do to get in better shape. Right now, I’m in heart attack territory, and it’s time to fix that.
I’ll start by working out with Brent Ozar and Buck Woody, watching this video where Brent is dressed up like Richard Simmons. It’s an oldie but a goodie, and it’s better than being seen in public with a ShakeWeight.
Now, weren’t those resolutions easy? What, we’re not done yet? You haven’t run the scripts? What the hell, you lazy meatbag? I can’t pull this off by myself – I need you to do ’em. What do you think this is, the cloud? And wasn’t one of your resolutions this year to make sure the boss doesn’t think the cloud is better than us? Hop to it before we lose our jobs.
6 Comments. Leave new
Thanks much for the handy scripts Brent.
Because I often deal with environments with MANY databases per server, I’ve modified your 2nd script to iterate through the sys.databases collection with a firehose and generate the rename and drop scripts for ALL the databases at once.
Hope you don’t mind me posting that here.
Thanks…Troy
use master
set nocount on
declare cur cursor fast_forward read_only for
select [name] from sys.databases
where [name] not in (‘master’,’msdb’,’tempdb’,’model’) — ADD EXCEPTIONS HERE
declare @dbName varchar(100), @sql nvarchar(max)
open cur
fetch next from cur into @dbName
while @@FETCH_STATUS -1
begin
set @sql = ‘SELECT ”’ + @dbName + ”’ as [database]
,s.name AS [schema]
,o.name AS [table]
,p.rows
,”EXEC sp_rename ””[” + s.name + ”].[” + o.name + ”]””, ””[” + s.name + ”].[_ToBeDropped_” + o.name + ”]””” AS ScriptToRename
,”DROP TABLE ””[” + s.name + ”].[” + o.name + ”]” AS ScriptToDrop
FROM ‘ + @dbname + ‘.sys.indexes i
INNER JOIN ‘ + @dbname + ‘.sys.objects o ON i.object_id = o.object_id
INNER JOIN ‘ + @dbname + ‘.sys.schemas s on s.schema_id = o.schema_id
INNER JOIN ‘ + @dbname + ‘.sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc = ”HEAP”
AND COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL
ORDER BY rows desc’
–print @sql
exec sp_executesql @sql
fetch next from cur into @dbName
end
close cur
deallocate cur
In pasting it here, the format got a bit whack in several places. Sorry about that.
The comments will need to be dash-dash…
The FETCH_STATUS will need to be a not-equals…
Single quotes will prob need to be fixed as well, as they turned into slanted single ticks.
why the cursor loop?
One of my servers db’s needs a real diet, so I updated script to look at clustered tables and tables where counts of use sum to 0. I had to break the coalesce into parts, otherwise you get an error with date and int showing up in the coalesce.
SELECT SCHEMA_NAME(o.schema_id) AS [schema],OBJECT_NAME(i.object_id) AS [table],
p.rows,
‘EXEC sp_rename ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[‘ + OBJECT_NAME(i.object_id) + ‘]”, ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[_ToBeDropped_’ + OBJECT_NAME(i.object_id) + ‘]”’ AS ScriptToRename,
‘DROP TABLE ”[‘ + SCHEMA_NAME(o.schema_id) + ‘].[‘ + OBJECT_NAME(i.object_id) + ‘]”’ AS ScriptToDrop
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE i.type_desc in (‘HEAP’,’CLUSTERED’)
and (COALESCE(ius.user_seeks, ius.user_scans, ius.user_lookups,ius.user_updates) IS NULL or ius.user_seeks+ ius.user_scans+ ius.user_lookups+ius.user_updates= 0)
and Coalesce( ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) IS NULL
ORDER BY rows desc
Thanks for the useful tip,
I’m curious to know What will be your resolutions next year 🙂
Everyone knows there is more than one way to do the same thing and this query is based on Brent’s above yet will get a different set of tables to look at and consider. I found a list of 34 tables that are likely to be out of date tables or in other words not used anymore.
SELECT
SCHEMA_NAME(oo.schema_id) AS [schema]
,OBJECT_NAME(oo.object_id) AS [table]
,pp.rows
,’EXEC sp_rename ”[‘ + SCHEMA_NAME(oo.schema_id) + ‘].[‘ + OBJECT_NAME(oo.object_id) + ‘]”, ”[‘ + SCHEMA_NAME(oo.schema_id) + ‘].[_ToBeDropped_’ + OBJECT_NAME(oo.object_id) + ‘]”’ AS ScriptToRename
,’DROP TABLE ”[_ToBeDropped_’ + SCHEMA_NAME(oo.schema_id) + ‘].[‘ + OBJECT_NAME(oo.object_id) + ‘]”’ AS ScriptToDrop
,ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup
FROM sys.objects as oo
INNER JOIN sys.partitions as pp ON oo.object_id = pp.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON oo.object_id = ius.object_id
WHERE oo.type = ‘U’
AND ius.user_seeks + ius.user_scans + ius.user_lookups + ius.user_updates = 0
and coalesce(ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup) is null
order by SCHEMA_NAME(oo.schema_id), OBJECT_NAME(oo.object_id)