Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuNí thacaítear leis an mbrabhsálaí seo a thuilleadh.
Uasghrádú go Microsoft Edge chun leas a bhaint as na gnéithe is déanaí, nuashonruithe slándála, agus tacaíocht theicniúil.
Nóta
This tool will be retired on December 15, 2024. We will stop supporting this tool for any issues that arise, and will not issue any bug fixes or further updates.
This article describes how to capture a trace in Database Experimentation Assistant (DEA) and then analyze the results, all from a command prompt.
Nóta
To learn more about each DEA operation, run the following command:
Deacmd.exe -o <operation> --help
An operation name is required. Valid operations are Analysis, StartCapture, and StopCapture.
To start a new workload capture, at a command prompt, run the following command:
Deacmd.exe -o StartCapture -n <Trace FileName> -x <Trace Format> -h <SQLServerInstance> -f <database name> -e <Encrypt Connection> -m <Authentication Mode> -u <user name> -p <password> -l <Location of Output Folder> -d <duration>
For example:
Deacmd.exe -o StartCapture -n sql2008capture -x 0 -h localhost -f adventureworks -e --trust -m 0 -l c:\test -d 60
When you start a new workload capture with the Deacmd.exe
command, you can use the following options:
Option | Description |
---|---|
-n , --name |
Required. Trace file name. |
-x , --format |
Required. Format of the trace (0 = Trace, 1 = XEvents). |
-d , --duration |
Required. Maximum duration for the capture, in minutes. |
-l , --location |
Required. Location of output folder for storing trace or XEvent files on the host computer. |
-t , --type |
Default: 0. Type of the SQL Server (0 = SqlServer, 1 = AzureSQLDB, 2 = Azure SQL Managed Instance). |
-h , --host |
Required. SQL Server host name or instance name to start the capture. |
-e , --encrypt |
Default: True. Encrypt connection to SQL Server instance. |
--trust |
Default: False. Trust server certificate while connecting to SQL Server instance. |
-f , --databasename |
Name of the database to filter your traces, if not specified the capture starts on all the databases. |
-m , --authmode |
Default: 0. Authentication mode (0 = Windows, 1 = Sql Authentication). |
-u , --username |
User name for connecting to the SQL Server. |
-p , --password |
Password for connecting to the SQL Server. |
If you're using Distributed Replay, perform the following steps.
Sign in to the Distributed Replay controller computer.
To convert the workload trace that you captured using the DEA command to an IRF file, run the following command:
DReplay preprocess -m "dreplaycontroller" -i "Path to first trace file" -d "<Folder path on controller>\IrfFolder"
Start a trace capture on the target computer running SQL Server using StartReplayCaptureTrace.sql.
a. In SQL Server Management Studio (SSMS), open <Dea_InstallPath>\Scripts\StartReplayCaptureTrace.sql.
b. Run Set @durationInMins=0
so that the trace capture doesn't stop automatically after a specified time.
c. To set the maximum file size per trace file, run Set @maxfilesize
. The recommended size is 200 MB.
d. Edit @Tracefile
to set a unique name for your trace file.
e. Edit @dbname
to specify a database name if the workload must be captured only on a specific database. By default, the workload on the entire server is captured.
To replay the IRF file against the target SQL Server instance, run the following command:
DReplay replay -m "dreplaycontroller" -d "<Folder Path on Dreplay Controller>\IrfFolder" -o -s "SQL2016Target" -w "dreplaychild1,dreplaychild2,dreplaychild3,dreplaychild4"
a. To monitor the status, run the following command:
DReplay status -f 1
b. To stop the replay, for example if you see that the pass percentage is lower than expected, run the following command:
DReplay cancel
Stop the trace capture on the target SQL Server instance.
In SSMS, open <Dea_InstallPath>\Scripts\StopCaptureTrace.sql.
Edit @Tracefile
to match the trace file path on the target computer running SQL Server.
Run the script against the target computer running SQL Server.
If you're using InBuilt Replay, you won't have to set up Distributed Replay. The ability to use InBuilt Replay at the command prompt is on the way. Currently, you can use our GUI to run replay using InBuilt Replay.
To start a new trace analysis, run the following command:
Deacmd.exe -o analysis -a <Target1 trace filepath> -b <Target2 trace filepath> -r reportname -h <SQLserverInstance> -e <encryptconnection> -u <username>
For example:
Deacmd.exe -o analysis -a C:\Trace\SQL2008Source\Trace.trc -b C:\ Trace\SQL2014Trace\Trace.trc -r upgrade20082014 -h localhost -e
To view the analysis reports of these trace files, you need to use the GUI to view charts and organized metrics. However, the analysis database is written to the SQL Server instance specified, so you can also query the generated analysis tables directly.
When analyzing traces using the DEA command, you can use the following options:
Option | Description |
---|---|
-a , --traceA |
Required. File path to the event file for the A instance. Example: C:\traces\Sql2008trace.trc. If there's a batch of files, select the first file and DEA checks for rollover files automatically. If files are in blob, provide the folder path where you want the event files stored locally. Example: C:\traces\ |
-b , --traceB |
Required. File path to the event file for the B instance. Example: C:\traces\Sql2014trace.trc. If there's a batch of files, select the first file and DEA checks for rollover files automatically. If files are in blob, provide the folder path where you want the event files stored locally. Example: C:\traces\ |
-r , --ReportName |
Required. Name for current analysis. The analysis report that gets generated is identified by this name. |
-t , --type |
Default: 0. Type of the SQL Server (0 = SqlServer, 1 = AzureSQLDB, 2 = Azure SQL Managed Instance). |
-h , --host |
Required. SQL Server host name or instance name. |
-e , --encrypt |
Default: True. Encrypt connection to SQL Server instance. |
--trust |
Default: False. Trust server certificate while connecting to SQL Server instance. |
-m , --authmode |
Default: 0. Authentication mode (0 = Windows, 1 = Sql Authentication). |
-u , --username |
User name for connecting to the SQL Server. |
--p |
Password for connecting to the SQL Server. |
--ab |
Default: False. Storage location of trace A is in blob. If used, must also specify --abu (Trace A Blob Url) |
--bb |
Default: False. Storage location of trace B is in blob. If used, must also specify --bbu (Trace B Blob Url) |
--abu |
Blob URL for A instance with SAS key. |
--bbu |
Blob URL for B instance with SAS key. |
Ócáid
Mar 31, 11 PM - Apr 2, 11 PM
An ócáid foghlama SQL, Fabric and Power BI is mó. Márta 31 – 2 Aibreán. Bain úsáid as cód FABINSIDER chun $ 400 a shábháil.
Cláraigh inniuOiliúint
Modúl
Use the SQL Server Query Tuning Assistant - Training
Learn how the Query Tuning Assistant (QTA) works alongside the Query Store to compare query performance statistics and find queries that are regressing due to changing compatibility levels.
Deimhniú
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Doiciméadúchán
Capture a trace for SQL Server upgrades - Database Experimentation Assistant
Use Database Experimentation Assistant (DEA) to create a trace file with a log of captured server events.
Replay a trace for SQL Server upgrades - Database Experimentation Assistant
Learn how to replay a captured trace with Database Experimentation Assistant for SQL Server upgrades.
Overview of the workload comparison process - Database Experimentation Assistant
Database Experimentation Assistant (DEA) is an A/B testing solution for changes in SQL Server environments, such as upgrades or new indexes.
Configure replay for SQL Server upgrades - Database Experimentation Assistant
Use Database Experimentation Assistant (DEA) to access the Distributed Replay tools. Use the tools to replay a captured trace against an upgraded test environment.