DEA 2.0 – Capture Trace FAQ


  1. What are the events captured while running trace on a production database?

    The following table provides the list of events and the corresponding column data that we collect for the traces

    Event Name Columns
    Text Data (1) Binary Data (2) Database ID (3) Host Name (8) Application Name (10) Login Name (11) SPID (12) Start Time (14) End Time (15) Database Name (35) Event Sequence (51) IsSystem (60)
    RPC:Completed (10) * * * * * * * * * * *
    RPC:Starting (11) * * * * * * * * * *
    RPC Output Parameter (100) * * * * * * * * * *
    SQL:BatchCompleted (12) * * * * * * * * * * *
    SQL:BatchStarting (13) * * * * * * * * * *
    Audit Login (14) * * * * * * * * * * *
    Audit Logout (15) * * * * * * * * * * *
    ExistingConnection (17) * * * * * * * * * * *
    CursorOpen (53) * * * * * * * * * *
    CursorPrepare (70) * * * * * * * * * *
    Prepare SQL (71) * * * * * * * * *
    Exec Prepared SQL (72) * * * * * * * * *
    CursorExecute (74) * * * * * * * * * *
    CursorUnprepare (77) * * * * * * * * * *
    CursorClose (78) * * * * * * * * * *
  2. Will there be any performance impact on my production server while capturing traces ?

    Yes, there will be minimal performance impact during the period of trace collection. Based on our tests we have found about ~3% memory pressure.

  3. What kind of permissions are required for capturing traces from production workload?

    1. The Windows user performing the trace operation in the DEA Application should have sysadmin privileges in the target SQL Server.
    2. The service account under which the SQL Server is running should have write access to the specified trace file path.
  4. Can I capture trace for the entire server vs one single database?

    Yes, DEA allows you to capture traces for both the server level (for all the databases in the server) or one single database.

  5. I have a linked server configured in my production environment, will those queries show up in the traces ?

    If you are running capture for the entire server (by not providing any value in the Database name field in the capture screen), the trace will capture all the queries including the linked server queries.

  6. What is the minimum recommended time for production workload traces?

    The general recommendation we offer is to choose a time that best represents the entirety of your workload so that we can run the analysis on all the queries in your workload.

  7. How important is to take database backup right before the start of the trace capture?

    It is very important to take backup right before you start the capture, we will be replaying the captured trace in Target 1 and Target 2 and if the database state is not the same, the results of the experimentation will be skewed.

  8. I am getting an error while capturing traces.

    Please review the following pre-requisites

    1. Confirm the SQL Server name is valid. To confirm, try connecting to the server using SSMS.
    2. Confirm your firewall configuration is not blocking connections to SQL Server.
    3. Confirm user has the permissions listed in <Link to permissions blog>.
    Possible Errors Solution
    Unable to start the trace on the target SQL Server, please check if you have required permissions and SQL Server account has write access to the specified trace file path | Sql Error Code (53) The user running the DEA Tool should have access to SQL Server and have ‘sysadmin’ role assigned to the user
    Unable to start the trace on the target SQL Server, please check if you have required permissions and SQL Server account has write access to the specified trace file path | Sql Error Code (19062) The trace path specified might not exists or the folder doesn’t have write permissions for the account under which SQL Server service are running (eg., NETWORK SERVICE). The path should exists with right permissions in order for the trace to start.
    A DEA trace is currently running on the target server. An active trace is already running on the target server, DEA will not allow to multiple traces when a server wide trace is already running.
    Cannot open database requested for capturing trace. This error may be due to incorrect database name The database specified doesn’t exists or it’s not accessible to the current user. Please correct the database name

    If you see any other errors with Sql Error Code, please visit https://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx for detailed description and resolution.

  9. Can we collect XEvents instead of traces and replay the same?

    XEvents is not yet supported by DEA, we are working on it, please stay tuned!!.

Comments (0)

Skip to main content