Tuesday, June 21, 2016

Memory Used

In an earlier post I mentioned some general guidelines for optimizing each instance of the SQL engine. Once you've got the memory set up it is quite interesting however to observe how the engine is actually using it. Run these two queries; they both show pretty much the same thing but you may find one more useful than the other.
SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [Page Count], [is_modified];
GO

SELECT count(*)AS cached_pages_count, (count(*) * 8)/1024 As Mbytes,  db_name(database_id)
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
GO
The amount of memory used naturally varies depending upon which queries are running at any particular time, but by monitoring this when you are under "memory pressure" you can easily identify the culprit database. This can then guide you whether it makes sense to reallocate that database to a different SQL instance, or perhaps allocate a deeper inspection to what queries are running against it.

To that end, you can run this script:
SELECT        session_id, text, requested_memory_kb,
    granted_memory_kb, used_memory_kb
FROM            sys.dm_exec_query_memory_grants MG
CROSS APPLY sys.dm_exec_sql_text(sql_handle) 
ORDER BY 3 DESC

that will show you the /current/ allocation for any running queries. If you need greater detail down at the table index level, you can run this script:
select
       count(*)as cached_pages_count, obj.name as objectname,
       ind.name as indexname, obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    (
SELECT     p.object_id AS objectid, object_name(p.object_id) AS name, p.index_id, au.allocation_unit_id
FROM       sys.allocation_units au INNER JOIN
sys.partitions p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)      
        union all
SELECT     p.object_id AS objectid, object_name(p.object_id) AS name, p.index_id, au.allocation_unit_id
FROM       sys.allocation_units au INNER JOIN
sys.partitions p ON au.container_id = p.partition_id AND au.type = 2
    ) as obj
on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind on  obj.objectid = ind.object_id and  obj.index_id = ind.index_id
where bd.database_id = db_id() and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc

Monday, June 6, 2016

Backup Restore

More than anything, a DBA provides a company a solid underpinning confidence that you are "protecting" the databases. Between periodic maintenance chores and the scheduled backups, you are supposed to make sure that come hell or high water the company's data will always be safe, reliable, and recoverable. In fact you can pretty safely assume that should you under any circumstances "lose" a database, you will also lose your job.

Setting up and then keeping track of the backups is a large part of this chore. Once you've determined whether to set each dB on Full or Simple recovery, it's time to create a maintenance plan on each instance. For some peculiar reason known only to Microsoft, I've only been able to consistently do this RDP'd into the server with SSMS fired up connected to (local). I like to keep my backup jobs separate from all the other maintenance, so when I run the maintenance Wizard I select just the three options of Log Backups, Database Backups, and Backup Cleanup. That's not the order in which the checkboxes appear, but when you arrange them, sequence them in that order.

Needless to say, you don't place your backup files on the same device where you host your log or mdf files. One lesser known useful fact however is, although you can't select it from clicking on treeviews, you can manually type in a path to a UNC share (\\myBackupSrv\sqlBackkups, or even \\10.10.44.44\myStuff) ahead of your backup file name. You can even address remote shares on the cloud this way.

Once your plans are in place, you need to verify they are working properly. Things get more complicated once you backup to a cloud vendor: not only do you have your cloud backup routines but for safety you should keep a local backup copy occasionally as well. Also if you are hosting gigantic databases supporting OLTP then chances are it makes more sense to run daily differential backups instead of full backups (with maybe a weekly or monthly full depending on how much the dB grows and changes). After a while therefore you can end up with a fairly complicated blended backup strategy.

If you're like me then, you'll want to open up your SSMS every morning and examine the Explorer Details window. Add the column "Last Backup" and sort it, and right away you know what needs some attention.  Also you will find this query helpful:

USE msdb
GO
SELECT        b.backup_set_id, b.name, b.user_name, b.database_creation_date, b.backup_start_date, b.backup_finish_date, b.type, b.backup_size, b.database_name,
                         b.server_name, b.machine_name, b.recovery_model, b.is_snapshot, b.is_readonly,
                         b.is_single_user, b.is_damaged, f.physical_drive, f.physical_name, f.state,
                         f.state_desc, f.is_present, b.is_copy_only, m.physical_device_name
FROM            backupset AS b INNER JOIN
                         backupfile AS f ON f.backup_set_id = b.backup_set_id INNER JOIN
                         backupmediafamily AS m ON b.media_set_id = m.media_set_id
WHERE        (f.physical_name LIKE '%.mdf%') AND (b.backup_start_date > GETDATE() - 14)
ORDER BY 1 DESC
GO

Often I'll add into the where-clause a specific b.database_name if I'm looking specifically for the backups for just one database. Note the results of this query show not only the dB backups but also the log, differential, and VM snapshots.  It's useful to recognize that when you do a Restore through the user-interface, these are the tables the system references to reach the correct restore file suggestions.

In some environments, as when you have another team moving reporting databases around, it's helpful to see the inverse condition (when were the restores performed?):

USE msdb
GO
SELECT  DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        SourceFile = physical_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
where physical_name like '%.mdf%'     
ORDER BY RestoreDate desc
GO

What's great about this query is it shows the name of what backupset was used to restore each database and the date that backup got created.

Naturally (or maybe I should phrase that as "unfortunately") if you yourself are asked to do a restore, a good percentage of time it will be under duress with the boss breathing down your neck. Hence I always keep this template by my side to pull up in times of emergency:

RESTORE DATABASE [ThisBizBI]
FROM  DISK = N'\\bkupStore\sql backups\DEV-ThisBizDB\ThisBizBI\ThisBizBI_backup_2014_08_21_040010_8176618.bak'
WITH  FILE = 1,  MOVE N'ThisBizBI' TO N'E:\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ThisBizBI.mdf', 
MOVE N'ThisBizBI_log' TO N'E:\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ThisBizBI_log.ldf',
NOUNLOAD,  STATS = 10

GO

\\*------------------------ *\\

ALTER DATABASE [ThisBizBIDW] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

RESTORE DATABASE [ThisBizBIDW]
FROM  DISK = N'\\bkupStore\sql backups\OLTP2SRV\ThisBizBIDW\ThisBizBIDW_backup_2014_09_08_040001_0041150.bak'
WITH  FILE = 1,  NOUNLOAD,  REPLACE, 
STANDBY = N'E:\XMPIEDB\MSSQL11.XMPIE\MSSQL\DATA\ThisBizBIDW_sby.BAK',
STATS = 10
GO

ALTER DATABASE [ThisBizBIDW] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

\\*------------------------ *\\

RESTORE LOG [ThisBizOSDEV_Restore]
FROM  DISK = N'\\bkupStore\sql backups\OLTP2SRV\ThisBizOSDev\ThisBizOSDev_backup_2015_05_10_010007_0679267.trn'
WITH  FILE = 1,  NOUNLOAD, 
STANDBY = N'E:\XMPIEDB\MSSQL11.XMPIE\MSSQL\DATA\ThisBizOSDev_Restore_sby2.BAK',
STATS = 10
GO

This template shows three different variations of a restore; I seldom use any one of them straight vanilla but may modify or copy a line or two from one to the other to achieve the results that I desire. The first is a restore of one database over another, the second example is a restore of a live production dB (kicking off the current users in the process) and leaving the database in "standby" mode, and the third example is a logfile restore. If you haven't yet, do read up on restoring a database with NoRecovery or Standby -- these are the options to use if you need to restore more than a single backup file to achieve currency.