{"id":547,"date":"2010-12-17T18:15:00","date_gmt":"2010-12-17T18:15:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(17-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-1).aspx"},"modified":"2017-04-13T14:41:05","modified_gmt":"2017-04-13T18:41:05","slug":"an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/","title":{"rendered":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)"},"content":{"rendered":"

\nToday’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup?<\/a> and the investigation of Database Engine Internals with Extended Events.  In today’s post we’ll look at how Backup’s work inside of SQL Server and how to track the throughput of Backup and Restore operations.  This post is not going to cover Backups in SQL Server as a topic; if that is what you are looking for see Paul Randal’s TechNet Article Understanding SQL Server Backups<\/a>.\n<\/p>\n

\nYesterday I mentioned that there is only one Event in Extended Events that has the word backup in it's name, and that Event is the sqlserver.backup_and_restore_throughput Event.  At first glance this Event looks pretty dull.  It only returns three columns, database_id, count, and increment, and doesn’t really tell us what count and increment mean in the metadata.\n<\/p>\n

\n\t-- Get the Event columns
\n\t<\/span>SELECT
\n\t    <\/span>OBJECT_NAME<\/span>,
\n\t    <\/span>name<\/span>,
\n\t    <\/span>type_name<\/span>,
\n\t    <\/span>description
\n\t<\/span>FROM <\/span>sys.dm_xe_object_columns
\n\t<\/span>WHERE <\/span>OBJECT_NAME <\/span>LIKE <\/span>'%backup%'
\n\t  <\/span>AND <\/span>column_type <\/span>= <\/span>'data'<\/span><\/code> <\/p>\n

\n\t<\/a>\"image\"<\/a>\n\t<\/p>\n<\/blockquote>\n

\nI could step you through what I did to look at this Event and figure out the meaning of things, but that would make an already long post longer.  Essentially I created an Event Session with just this Event and used the sqlserver.session_id Predicate to only capture it for a specific session_id that I was going to run a FULL backup from.  The count column is the total number of bytes that have been written to backups and the increment column is the current number of bytes that were written when the Event fired (we’ll see this more in a minute).  This was interesting to see so I started thinking about what kind of information I would want to know about Backups that related to the throughput and two items came to mind almost immediately; read operations from the database, and wait statistics related to the Backup occurring, both of which are available through Extended Events.  I also recalled that there were a few documented Trace Flags associated with Backup and Restore operations that output more verbose information through Trace Prints.  Trace Flag 3004, outputs what operations Backup and Restore are performing (How It Works: What is Restore\/Backup Doing<\/a>?).  Trace Flag 3213, outputs the Backup Buffer configuration information as discussed on the SQLCAT team blog series Tuning the Performance of Backup Compression in SQL Server 2008<\/a> and Tuning Backup Compression Part 2<\/a>.  Trace Flag 3014, outputs additional information about Backup and File operations (How It Works: How does SQL Server Backup and Restore select transfer sizes<\/a>).  There happens to be a sqlserver.trace_print Event that can capture the trace output as a part of our Event Session.\n<\/p>\n

\nUsing yesterday’s post as a foundation for the Event Session in today’s post, and the same Sample_Reporting Database, lets look at the Event Session that we’ll use to investigate Backups.\n<\/p>\n

\n\t-- Create the Event Session
\n\t<\/span>CREATE <\/span>EVENT SESSION BackupMonitoring
\n\t<\/span>ON <\/span>SERVER
\n\t<\/span>ADD <\/span>EVENT sqlserver.sql_statement_starting
\n\t<\/span>(   <\/span>ACTION <\/span>(<\/span>sqlserver.database_id<\/span>, <\/span>sqlserver.sql_text<\/span>)
\n\t    <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.sql_statement_completed
\n\t<\/span>(   <\/span>ACTION <\/span>(<\/span>sqlserver.database_id<\/span>, <\/span>sqlserver.sql_text<\/span>)
\n\t    <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.databases_backup_restore_throughput
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlos.wait_info
\n\t<\/span>(   <\/span>ACTION <\/span>(<\/span>sqlserver.database_id<\/span>)
\n\t    <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97  <\/span>AND <\/span>duration <\/span>> <\/span>0<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlos.wait_info_external
\n\t<\/span>(   <\/span>ACTION <\/span>(<\/span>sqlserver.database_id<\/span>)
\n\t    <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97  <\/span>AND <\/span>duration <\/span>> <\/span>0<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.trace_print
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.file_read
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.file_read_completed
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.physical_page_read
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>sqlserver.session_id <\/span>= <\/span>97<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.databases_log_cache_read
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>database_id <\/span>= <\/span>41<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.databases_log_cache_hit
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>database_id <\/span>= <\/span>41<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.databases_log_flush
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>database_id <\/span>= <\/span>41<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.checkpoint_begin
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>database_id <\/span>= <\/span>41<\/span>)),
\n\t<\/span>ADD <\/span>EVENT sqlserver.checkpoint_end
\n\t<\/span>(   <\/span>WHERE <\/span>(<\/span>database_id <\/span>= <\/span>41<\/span>))
\n\t<\/span>ADD <\/span>TARGET package0.asynchronous_file_target<\/span>(
\n\t     <\/span>SET <\/span>filename<\/span>=<\/span>'C:\\SQLBlog\\BackupMonitoring1.xel'<\/span>,
\n\t         <\/span>metadatafile <\/span>= <\/span>'C:\\SQLBlog\\BackupMonitoring1.xem'<\/span>)
\n\t<\/span>GO
\n\t<\/span>-- Alter the Session to Start it
\n\t<\/span>ALTER <\/span>EVENT SESSION BackupMonitoring
\n\t<\/span>ON <\/span>SERVER
\n\tSTATE<\/span>=<\/span>START
\n\tGO<\/span><\/code>\n<\/p><\/blockquote>\n

\nThere is a lot of information being collected in this Event Session.  We are going to get the sql_statement_starting and completed Events, the backup_restore_throughput Event, wait_info Event for SQLOS waits inside of SQL Server and the wait_info_external Event for preemptive waits outside of SQL Server, the trace_print Event to capture our Trace Flag outputs, the file_read, file_read_completed, and physical_page_read Events to capture read operations from the session_id performing the Backup, the database_log_cache_read, database_log_cache_hit, and database_log_flush Events to track transaction log cache operations during the Backup, and the checkpoint_begin and checkpoint_end Events to track checkpoint occurrence during the backup and how they might impact throughput.  If you notice, some of the Events are Predicated on the session_id, while others are predicated on the database_id, and this is very intentional in the definition of this Event Session.  Some Events do not fire in the context of a specific database_id, and some Events do not fire in the context of a specific session_id, and some will fire for both.  Where the database_id is a practical Predicate for the Event, and it is carried in the Events base payload, it is a natural item to use for a Predicate.  Restricting Events to a specific database_id or session_id will prevent Event capture from other operations occurring on the SQL Server.\n<\/p>\n

\nWith our Event Session defined and started, we can now run a Backup of the database and see what we capture.  I am going to show two different Backup configurations in this post, based on the information contained in the SQLCAT series on tuning Backup Performance in SQL Server 2008.  The first one uses a default configuration for the BUFFERCOUNT and MAXTRANSFERSIZE Backup options, but also uses Database Compression since it is available to minimize he backup file size, and maximize the throughput of the backup operation.\n<\/p>\n

\n\tBACKUP DATABASE <\/span>[Sample_Reporting]
\n\t<\/span>TO  DISK = <\/span>N'B:\\SQLData\\MSSQL10.MSSQLSERVER\\MSSQL\\Backup\\Sample_Reporting1.bak'
\n\t<\/span>WITH <\/span>NOFORMAT<\/span>,
\n\t    <\/span>NOINIT<\/span>,  
\n\t    <\/span>NAME <\/span>= <\/span>N'Sample_Reporting-Full Database Backup Number 1'<\/span>,
\n\t    <\/span>SKIP<\/span>,
\n\t    <\/span>NOREWIND<\/span>,
\n\t    <\/span>NOUNLOAD<\/span>,
\n\t    <\/span>COMPRESSION<\/span>,
\n\t    <\/span>STATS <\/span>= <\/span>5
\n\tGO<\/span><\/code>\n<\/p><\/blockquote>\n

\nOn this server, the backups are writing to a dedicated RAID1 disk array using two 146GB 15K RPM SAS drives.  When the backup completes we can begin our analysis of the Events captured by our Event Session.  To make it possible to perform various types of analysis of the data contained inside of the asynchronous_file_target, I am going to read the Raw XML Event data into a temporary table, and then shred the XML into a second temporary table, making it possible to just query the shredded data.\n<\/p>\n

\n\tDROP TABLE <\/span>#EventData
\n\t<\/span>DROP TABLE <\/span>#TestResults<\/p>\n

\t<\/span>-- Create intermediate temp table for raw event data
\n\t<\/span>CREATE TABLE <\/span>#EventData
\n\t<\/span>(<\/span>Rowid <\/span>INT <\/span>IDENTITY <\/span>PRIMARY KEY<\/span>, <\/span>event_data <\/span>XML<\/span>)<\/p>\n

\t<\/span>-- Create final results table for parsed event data
\n\t<\/span>CREATE TABLE <\/span>#TestResults
\n\t<\/span>(<\/span>Rowid <\/span>INT PRIMARY KEY<\/span>, <\/span>event_name <\/span>VARCHAR<\/span>(<\/span>50<\/span>), <\/span>package_name <\/span>VARCHAR<\/span>(<\/span>50<\/span>),
\n\t<\/span>[timestamp] datetime2<\/span>, <\/span>database_id <\/span>INT<\/span>, <\/span>trace_print <\/span>NVARCHAR<\/span>(<\/span>4000<\/span>),
\n\t<\/span>[count] bigint<\/span>, <\/span>increment bigint<\/span>, <\/span>wait_type <\/span>NVARCHAR<\/span>(<\/span>100<\/span>), <\/span>opcode <\/span>NVARCHAR<\/span>(<\/span>10<\/span>),
\n\t<\/span>duration bigint<\/span>, <\/span>max_duration bigint<\/span>, <\/span>total_duration bigint<\/span>, <\/span>signal_duration bigint<\/span>,
\n\t<\/span>completed_count bigint<\/span>, <\/span>source_database_id <\/span>INT<\/span>, <\/span>[object_id] <\/span>INT<\/span>, <\/span>object_type <\/span>INT<\/span>,
\n\t<\/span>[state] <\/span>NVARCHAR<\/span>(<\/span>50<\/span>), <\/span>offset bigint<\/span>, <\/span>offset_end <\/span>INT<\/span>, <\/span>nest_level <\/span>INT<\/span>, <\/span>cpu <\/span>INT<\/span>,
\n\t<\/span>reads bigint<\/span>, <\/span>writes bigint<\/span>, <\/span>mode <\/span>NVARCHAR<\/span>(<\/span>50<\/span>), <\/span>FILE_ID <\/span>INT<\/span>, <\/span>page_id <\/span>INT<\/span>,
\n\t<\/span>file_group_id <\/span>INT<\/span>, <\/span>sql_text <\/span>NVARCHAR<\/span>(<\/span>4000<\/span>))<\/p>\n

\t<\/span>-- Read the file data into intermediate temp table
\n\t<\/span>INSERT INTO <\/span>#EventData <\/span>(<\/span>event_data<\/span>)
\n\t<\/span>SELECT
\n\t    <\/span>CAST<\/span>(<\/span>event_data <\/span>AS XML<\/span>) <\/span>AS <\/span>event_data
\n\t<\/span>FROM <\/span>sys.<\/span>fn_xe_file_target_read_file<\/span>(<\/span>'C:\\SQLBlog\\BackupMonitoring1*.xel'<\/span>, <\/span>'C:\\SQLBlog\\BackupMonitoring1*xem'<\/span>, NULL, NULL)<\/p>\n

\t<\/span>-- Query the Event data from the Target.
\n\t<\/span>INSERT INTO <\/span>#TestResults
\n\t<\/span>(<\/span>Rowid<\/span>, <\/span>event_name<\/span>, <\/span>package_name<\/span>, <\/span>[timestamp]<\/span>, <\/span>database_id<\/span>, <\/span>trace_print<\/span>,
\n\t<\/span>[count]<\/span>, <\/span>increment<\/span>, <\/span>wait_type<\/span>, <\/span>opcode<\/span>, <\/span>duration<\/span>, <\/span>max_duration<\/span>, <\/span>total_duration<\/span>,
\n\t<\/span>signal_duration<\/span>, <\/span>completed_count<\/span>, <\/span>source_database_id<\/span>, <\/span>[object_id]<\/span>, <\/span>object_type<\/span>,
\n\t<\/span>[state]<\/span>, <\/span>offset<\/span>, <\/span>offset_end<\/span>, <\/span>nest_level<\/span>, <\/span>cpu<\/span>,  <\/span>reads<\/span>, <\/span>writes<\/span>, <\/span>mode<\/span>, <\/span>FILE_ID<\/span>,
\n\t<\/span>page_id<\/span>, <\/span>file_group_id<\/span>, <\/span>sql_text<\/span>)<\/p>\n

\t<\/span>SELECT
\n\t    <\/span>RowID<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/@package)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>package_name<\/span>,
\n\t    <\/span>DATEADD<\/span>(<\/span>hh<\/span>,
\n\t            <\/span>DATEDIFF<\/span>(<\/span>hh<\/span>, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>),
\n\t            <\/span>event_data.value<\/span>(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp]<\/span>,
\n\t    <\/span>COALESCE<\/span>(<\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>),
\n\t                <\/span>event_data.value<\/span>(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)) <\/span>AS <\/span>database_id<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="message"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>trace_print<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="count"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>[count]<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="increment"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>[increment]<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="wait_type"]\/text)[1]'<\/span>, <\/span>'nvarchar(100)'<\/span>) <\/span>AS <\/span>wait_type<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="opcode"]\/text)[1]'<\/span>, <\/span>'nvarchar(10)'<\/span>) <\/span>AS <\/span>opcode<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="duration"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>duration<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="max_duration"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>max_duration<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="total_duration"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>total_duration<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="signal_duration"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>signal_duration<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="completed_count"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>completed_count<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="source_database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>source_database_id<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="object_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>OBJECT_ID<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="object_type"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>object_type<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="state"]\/text)[1]'<\/span>, <\/span>'nvarchar(50)'<\/span>) <\/span>AS <\/span>state<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="offset"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>offset<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="offset_end"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>offset_end<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="nest_level"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>nest_level<\/span>,    
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="cpu"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>cpu<\/span>,    
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="reads"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>reads<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="writes"]\/value)[1]'<\/span>, <\/span>'bigint'<\/span>)  <\/span>AS <\/span>writes<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="mode"]\/text)[1]'<\/span>, <\/span>'nvarchar(50)'<\/span>) <\/span>AS <\/span>mmode<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="file_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>FILE_ID<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="page_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>page_id<\/span>,
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/data[@name="file_group_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)  <\/span>AS <\/span>file_group_id<\/span>,        
\n\t    <\/span>event_data.value<\/span>(<\/span>'(event\/action[@name="sql_text"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>sql_text
\n\t<\/span>FROM <\/span>#EventData
\n\t<\/span>ORDER BY <\/span>Rowid<\/p>\n

\t<\/span>-- Look at the Results.
\n\t<\/span>SELECT
\n\t    <\/span>Rowid<\/span>,
\n\t    <\/span>event_name<\/span>,
\n\t    <\/span>database_id<\/span>,
\n\t    <\/span>trace_print<\/span>,
\n\t    <\/span>[count]<\/span>,
\n\t    <\/span>increment<\/span>,
\n\t    <\/span>wait_type<\/span>,
\n\t    <\/span>duration<\/span>,
\n\t    <\/span>signal_duration<\/span>,
\n\t    <\/span>cpu<\/span>,
\n\t    <\/span>reads<\/span>,
\n\t    <\/span>writes<\/span>,
\n\t    <\/span>mode<\/span>,
\n\t    <\/span>FILE_ID<\/span>,
\n\t    <\/span>page_id<\/span>,
\n\t    <\/span>file_group_id<\/span>,
\n\t    <\/span>sql_text
\n\t<\/span>FROM <\/span>#TestResults
\n\t<\/span>ORDER BY <\/span>Rowid<\/span><\/code>\n<\/p><\/blockquote>\n

\nIn the above query, I am extracting all of the data elements from the Event data, even though in the final query I am not using all of the data.  I did this to have a complete example of how to shred the XML, and because we are storing it in a temp table, we may find that we want to come back and look at specific data elements that were excluded in the initial look at the results.  From our results we can begin to understand how Backup Operations work inside of SQL Server.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nHere we can see the statement starting to execute, and the first output from the trace_print Event showing that the backup of the database was starting, along with the external waits associated with performing file operations to create a Backup file for the database.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nHere the newly created Backup file is opened an 1K of write occur to the file before it becomes ready for the Backup.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nHere we can see two operations being performed.  In the red outlined box, since we are doing a full Backup of the database, the differential bitmaps, pages that track which extents in a GAM interval have been modified since the last full or differential backup (Inside the Storage Engine: Anatomy of an extent<\/a>), are cleared.  In the blue outlined box, we see the checkpoint triggered by the Backup operation begin, and in the four highlighted boxes in grey, we see two physical_page_reads occur for the database, one from the transaction log, and one from the primary data file.  These are the pages that are written to when Checkpoint occurs in the database.  The file_id 2 page_id 0 page is where the Log Sequence Number is written to the log file, and file_id 1 page_id 9 is the database boot page, where the LSN is also written to at checkpoint (Checkpoints and the Active Portion of the Log<\/a>).  We also see the wait_info event for the PAGEIOLATCH_UP wait to update of this information.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nNext the allocation bitmaps for the database are scanned and an estimate of the work is performed (red box) before writing 5K of metadata into the backup file. \n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nAt this point the Backup process is ready to begin copying data to the Backup file.  Since this particular database only has 1 data file, only one reader is assigned to the backup (Optimising Backup & Restore Performance in SQL Server<\/a>).  When the Backup starts on the file, an additional 1024 bytes (1K of information is written to the Backup file and the file read operations against the database data file(s) commences.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nAs the backup of the data file data begins, we see a change in the size of the increment being written to the Backup file, and now we have 1MB segments of data being written to the file.\n<\/p>\n

\n\"image\"<\/a>
\nWe can also see that multiple 1MB segments are written within milliseconds of each other.  Now I could spend a lot of time running through the entire set of Events showing the same thing, but with 187,698 Events for a 110 second Backup, that would take forever.  Instead I am going to skip over all the interim file reads and Backup file writes and get to the end of the data file section.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nHighlighted in black above, we see the completion of the first data file, followed by a trace_print event, in red, Padding MSDA with 196608 bytes (192K of space), and then the trace_print event, in blue, showing the completion of all Database files, which is also the beginning of the Transaction log portion of the backup.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nHere we can see that the size of the log information being backed up, highlighted in black, is significantly different from the data file information which is to be expected since log records are very different from data records in composition.  When the log files done trace_print Event in red, and the trailing configuration writres in blue and the trace_print Event in orange marking the completion of the trailing configuration.\n<\/p>\n

\n\"image\"<\/a>\n<\/p>\n

\nI don’t know what MBC done means, and I couldn’t find it online, but it completed here.  I think it might stand for MaxBufferCount, and the above line shows that all of the buffers have been written out for the backup. (Don’t quote me on that I am just taking a SWAG there!) <\/p>\n

\"image\"<\/a>\n<\/p>\n

\nAfter the MBC is done, the backup history records are written into MSDB.\n<\/p>\n

\n\"image\"<\/a> \n<\/p>\n

\nAnd finally the backup completes.  So far all that we’ve done is look at all of the information that we can get, and there is a lot of it, but unless we can do something actionable with all this information, there is no real point in gathering it.  I originally intended to only cover one post on this subject but its become quite large, so I am splitting it into two posts and in tomorrow’s post we’ll look at how we can use the information captured in today’s post to validate whether or not changes to our backup process have a positive or negative impact on backup times and throughput.<\/p>\n","protected":false},"excerpt":{"rendered":"

Today’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup? and the investigation of Database Engine Internals with Extended Events.  In today’s post we’ll look at how Backup’s work inside of SQL Server and how to track the throughput of Backup and Restore operations.  This post is not […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38,45],"tags":[],"class_list":["post-547","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Today’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup? and the investigation of Database Engine Internals with Extended Events.  In today’s post we’ll look at how Backup’s work inside of SQL Server and how to track the throughput of Backup and Restore operations.  This post is not […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-17T18:15:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:05+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"16 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)\",\"datePublished\":\"2010-12-17T18:15:00+00:00\",\"dateModified\":\"2017-04-13T18:41:05+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/\"},\"wordCount\":1819,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday17of31alookathowbackupsworka\\\/3ebb791e\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/\",\"name\":\"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday17of31alookathowbackupsworka\\\/3ebb791e\\\/image_thumb.png\",\"datePublished\":\"2010-12-17T18:15:00+00:00\",\"dateModified\":\"2017-04-13T18:41:05+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday17of31alookathowbackupsworka\\\/3ebb791e\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday17of31alookathowbackupsworka\\\/3ebb791e\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias","og_description":"Today’s post is a continuation of yesterday’s post How Many Checkpoints are Issued During a Full Backup? and the investigation of Database Engine Internals with Extended Events.  In today’s post we’ll look at how Backup’s work inside of SQL Server and how to track the throughput of Backup and Restore operations.  This post is not […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-17T18:15:00+00:00","article_modified_time":"2017-04-13T18:41:05+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"16 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)","datePublished":"2010-12-17T18:15:00+00:00","dateModified":"2017-04-13T18:41:05+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/"},"wordCount":1819,"commentCount":1,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/","name":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1) - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png","datePublished":"2010-12-17T18:15:00+00:00","dateModified":"2017-04-13T18:41:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday17of31alookathowbackupsworka\/3ebb791e\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-17-of-31-a-look-at-backup-internals-and-how-to-track-backup-and-restore-throughput-part-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"An XEvent a Day (17 of 31) \u2013 A Look at Backup Internals and How to Track Backup and Restore Throughput (Part 1)"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=547"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/547\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}