Distributed Replay: همگام‌سازی Target و ساخت Trace

PAGE-777

همگام‌سازی Target و آماده‌سازی Trace

چون Workload شامل DML است، Target باید بلافاصله پیش از Capture/Replay با Database منبع همگام شود تا Keyها و State داده یکسان باشند. کتاب با Backup گرفتن از Chapter21 روی Controller و Restore روی Target این همگامی را انجام می‌دهد.

Listing 21-6 — Backup/Restore Synchronization

Synchronizing the Target
Because we replay a trace that includes DML statements, we need to synchronize our
target database immediately before we start capturing the trace to ensure that the IDs
align. We perform this task by backing up the Chapter21 database on the controller and
restoring it on the target. We can achieve this by using the script in Listing 21-6.
Tip  Remember to change the file locations to match your own configuration
before running the script.
Listing 21-6.  Synchronizing the Chapter21 Database
--Part 1 - To be run on the controller
BACKUP DATABASE Chapter21
TO  DISK = N'F:\MSSQL\Backup\Chapter21.bak'
WITH NOFORMAT, NOINIT,  NAME = N'Chapter21-Full Database Backup',
SKIP,  STATS = 10
GO
--Part 2 - To be run on the client, after moving the backup file across
RESTORE DATABASE Chapter21
FROM  DISK = N'F:\MSSQL\Backup\Chapter21.bak'
WITH  FILE = 1, STATS = 5
GO
Ideally, the database should have the same DatabaseID on each server, but if this is
not feasible, then ensure that the Database_name action is captured in the event session
for mapping purposes. You should also ensure that any logins contained within the
trace are created on the Target server, with the same permissions and the same default
database. If you fail to do this, you get replay errors.

PAGE-778

Indexهای پیشنهادی روی Target

در سناریوی Performance Test، پس از Restore می‌توان Indexهای پیشنهادی را روی نسخه Target ساخت تا اثر Optimization اندازه‌گیری شود. نکته کلیدی این است که Data State ثابت بماند و فقط متغیر مورد آزمون، مانند Index Design، تغییر کند.

Listing 21-7 — Index Suggestions

Since our demonstration is for testing performance enhancements, you may wish
to take this opportunity to create appropriate indexes on the Customers and Addresses
tables in the synchronized database. You can find a script including index suggestions
that are appropriate for our trace workload in Listing 21-7.
Listing 21-7.  Index Suggestions
USE Chapter21
GO
CREATE INDEX IDX_Customers_LastName ON dbo.Customers(LastName)
INCLUDE(FirstName) ;
GO
CREATE INDEX IDX_Customers_AddressID ON dbo.Customers(DeliveryAddressID) ;
GO
CREATE INDEX IDX_Addresses_AddressID ON dbo.Addresses(AddressID) ;
GO
CREATE INDEX IDX_Customers_LastName_CustomerID ON dbo.Customers(LastName,
CustomerID) ;
GO
Creating a Trace
Let’s now create our Extended Event session and start the trace. You should
configure the Event session to capture the events, event fields, and actions detailed
in Table 21-4.

PAGE-779