Profiling Data

Telerik Data Access Profiler and Tuning Advisor is a graphical user interface for monitoring of all the Telerik Data Access activity in your application. It makes it easy for developers to see how Telerik Data Access is working behind the scenes. You can see all SQL queries sent to the database server, and the LINQ statement that generated them. In addition, the profiler has a built in alert system that will notify about potential issues, and suggest resolutions.

This article will show you how to configure your applications for real-time and offline profiling.

Configuring An Application For Real-Time Monitoring

Telerik Data Access Profiler offers live performance monitoring of your Data Access application via a dedicated web service. This section will guide you through the process of configuring a Telerik Data Access application to consume it. The example solution it works with is the one created through the Code Only Scenario article.

First, you need to install the Telerik.OpenAccess.Profiler NuGet package in the FluentModelClient project.
The second step is to configure your model. In the SampleFluentModel project, open the code file that holds your context class - FluentModel.cs(vb). You need to set a few options in the method that supplies the backend configuration - GetBackendConfiguration().
1. By default, only application Errors (exceptions) are logged. Therefore, in order to see any events and metrics, you need to adjust the Log Level setting. The event logging levels are as follows:
  1. None - logs no events.
  2. Errors - logs only serious errors (such as connections timing out). This is the default value.
  3. Normal - logs the most useful events that do not reduce performance significantly (e.g. SQL executed). This is the value used in this demo.
  4. Verbose - logs lots of events (this slows down the performance, could be used while debugging).
  5. All - logs all possible events (this slows down the performance significantly, could be used while debugging).
2. The StackTrace property controls if the logging facilities append stack trace information. The default value for this option is False, and for the purpose of this demo you have to set it to True.
3. Telerik Data Access produces two different kinds of data - metrics and events. Metrics are similar to the operating system counters. They produce snapshots of the system status like counting insert, update and delete statements per second. Log events contain the real operation information, including query parameters and stack traces. Compared to metrics, the production of log events is far more expensive and can slow down the application. To keep track of the events and the metrics, you need to provide values for the following properties:
  1. EventStoreCapacity - controls the maximum number of log events stored in the ring buffer. The value used in this demo is 10000.
  2. MetricStoreCapacity - controls the capacity of the metric snapshot store. The value used in this demo is 3600.
  3. MetricStoreSnapshotInterval - controls the interval to perform metric snapshots in milliseconds. The value used in this demo is 1000.
4. The last required settings are those of the log downloader background thread. You need to provide values for the EventPollSeconds and MetricPollSeconds properties.
  1. EventPollSeconds - controls the time in seconds between polls of the event ring buffer. The store interval for events is important. If the process generates more events than the configured buffer can store before the background process wakes up, the events are lost. By default, events are written in interval of 1 second, and this is the value used in this demo.
  2. MetricPollSeconds - controls the time in seconds between polls of the metric snapshot ring buffer. The value used in this demo is 1.
The code of the method is available below:
```
public static BackendConfiguration GetBackendConfiguration()
{
    BackendConfiguration backend = new BackendConfiguration();
    backend.Backend = "MsSql";
    backend.ProviderName = "System.Data.SqlClient";

    backend.Logging.LogEvents = LoggingLevel.Normal;
    backend.Logging.StackTrace = true;
    backend.Logging.EventStoreCapacity = 10000;
    backend.Logging.MetricStoreCapacity = 3600;
    backend.Logging.MetricStoreSnapshotInterval = 1000;
    backend.Logging.Downloader.EventPollSeconds = 1;
    backend.Logging.Downloader.MetricPollSeconds = 1;

    return backend;
}
```
```
Public Shared Function GetBackendConfiguration() _
As BackendConfiguration
    Dim backend As BackendConfiguration =
        New BackendConfiguration()
    backend.Backend = "MsSql"
    backend.ProviderName = "System.Data.SqlClient"

    backend.Logging.LogEvents = LoggingLevel.Normal
    backend.Logging.StackTrace = True
    backend.Logging.EventStoreCapacity = 10000
    backend.Logging.MetricStoreCapacity = 3600
    backend.Logging.MetricStoreSnapshotInterval = 1000
    backend.Logging.Downloader.EventPollSeconds = 1
    backend.Logging.Downloader.MetricPollSeconds = 1

    Return backend
End Function
```
Save the FluentModel.cs(vb) file.
Next, you need to start the web service host from the application. In the application start-up, paste the code shown below. This will start the service communication on the port 15555. You can use different port, by passing a different port number to StartService method. In Web applications, the service could be started in the Application_Start event handlers in the Global.asax.cs class. In Windows applications, you could start the service before showing the first form. In this demo, you can start the service on the first line of the Main() method in the FluentModelClient application.
```
Telerik.OpenAccess.ServiceHost.ServiceHostManager.StartProfilerService(15555);
```
```
Telerik.OpenAccess.ServiceHost.ServiceHostManager.StartProfilerService(15555)
```
You could optionally shut down the service, it is not mandatory. You need to call the StopProfilerService method. In Web applications, the service could be stopped in the Application_End event handlers in the Global.asax.cs class. In Windows applications, you could stop the service in the closing event of the main form.
```
Telerik.OpenAccess.ServiceHost.ServiceHostManager.StopProfilerService();
```
```
Telerik.OpenAccess.ServiceHost.ServiceHostManager.StopProfilerService()
```

For the purpose of this example, in the Main() method of the the FluentModelClient application, you can execute a few CRUD operations in a loop. For example:

Before running the sample, you need to ensure that the FluentModelClient application is configured according the the tutorial in the Consuming a Model - Configuration article, and that the database will be available on the server. The latter can be achieved through exposing the UpdateSchema method as a method of your context class.

public static void Main()
{
    Telerik.OpenAccess.ServiceHost.ServiceHostManager.StartProfilerService(15555);

    // Ensure that the database is available
    using (FluentModel dbContext = new FluentModel())
    {
        dbContext.UpdateSchema();
    }

    while (!Console.KeyAvailable)
    {
        using (FluentModel dbContext = new FluentModel())
        {
            // Add a new customer.
            Customer newCustomer = new Customer
            {
                ID = 1,
                Name = "John Smith",
                EmailAddress = "john@smith.com",
                DateCreated = DateTime.Now
            };
            dbContext.Add(newCustomer);

            // Commit changes to the database.
            dbContext.SaveChanges();

            // Get the first customer using LINQ and modify it.
            var customer = dbContext.Customers.FirstOrDefault();

            customer.EmailAddress = "john.smith@domain.com";

            // Commit changes to the database.
            dbContext.SaveChanges();

            // Use LINQ to retrieve a customer named John.
            var CustomertoDelete = dbContext.Customers.
                Where(c => c.Name.StartsWith("John")).
                FirstOrDefault();

            // Delete the customer from the database.
            dbContext.Delete(CustomertoDelete);

            // Commit changes to the database.
            dbContext.SaveChanges();
        }
    }

    Telerik.OpenAccess.ServiceHost.ServiceHostManager.StopProfilerService();
}

Sub Main()
    Telerik.OpenAccess.ServiceHost.ServiceHostManager.StartProfilerService(15555)

    Using dbContext As New FluentModel
        dbContext.UpdateSchema()
    End Using
    While Console.KeyAvailable
        Using dbContext As New FluentModel()
            ' Add a new customer.
            Dim newCustomer As Customer = New Customer _
                With
                {
                    .Id = 1,
                    .Name = "John Smith",
                    .EmailAddress = "john@smith.com",
                    .DateCreated = Date.Now
                }

            dbContext.Add(newCustomer)

            ' Commit changes to the database.
            dbContext.SaveChanges()

            ' Get the first customer using LINQ and modify it.
            Dim customer = dbContext.Customers.FirstOrDefault()

            customer.EmailAddress = "john.smith@domain.com"

            ' Commit changes to the database.
            dbContext.SaveChanges()

            ' Use LINQ to retrieve a customer named John.
            Dim CustomertoDelete = dbContext.Customers.
                Where(Function(c) c.Name.StartsWith("John")).
                FirstOrDefault()

            ' Delete the customer from the database.
            dbContext.Delete(CustomertoDelete)

            ' Commit changes to the database.
            dbContext.SaveChanges()
        End Using
    End While
    Telerik.OpenAccess.ServiceHost.ServiceHostManager.StopProfilerService()
End Sub

Run the application.

Starting the Profiler

While the application is running, open Telerik Data Access Profiler and Tuning Advisor. You could do this from the Windows start menu.
In order to start a real-time monitoring in the Telerik Data Access Profiler, you need to use the Connect To toolbar command.
This will open the Open Connection dialog. Here you need to enter the connection string name (from the app/web config file) and the server name you are using in your application, as well as the port number you have passed to the web service. To monitor the performance of the FluentModelClient application, leave the server name and the port number unchanged, and from the Connection Name combo-box, select connectionId.
Once configured click Test Connection at which point the profiler will indicate whether or not it was able to successfully connect to the service host. If Test Connection reports a failure, ensure that the application is running, and that you specified correct info on the Open Connection dialog. Once Test Connection comes back successful, click Connect.
At this point you are ready to use your application, and all activity will be reported to the profiler.

Configuring An Application For Offline Monitoring

Telerik Data Access allows you to generate files with snapshots of all the Telerik Data Access activities in your application, which you can use for later analysis by loading the files into Telerik Data Access Profiler And Tuning Adviser. This is known as offline profiling.

This section will show you how to configure your application for offline monitoring.

The first step is to configure your model. In the SampleFluentModel project, open the code file that holds your context class - FluentModel.cs(vb). Besides the options required for real-time profiling, you need to set a few additional ones in the method that supplies the backend configuration - GetBackendConfiguration().

MaxFileSizeKB - specifies the max size of the log file in KB.
NumberOfBackups - the number of old log files that Telerik Data Access keeps on the file system. After that number is exceeded, Telerik Data Access deletes the first log file before creating a new one. By default the maximum size of the log files is 1000KB and the maximum amount of historical files is 3. If the log file reaches the configured maximum size, it is renamed to fileName.1. If a file with name fileName.1 already exists, then it is renamed to fileName.2. If the maximum number of files has been reached, the oldest file is deleted. The new data will be stored in a new file.
Filename - specifies the file name for the log output file.
EventBinary - specifies if events should be logged in binary form.
MetricBinary - specifies if the metric snapshots are logged in binary form.

The code of the method is available below:

public static BackendConfiguration GetBackendConfiguration()
{
    BackendConfiguration backend = new BackendConfiguration();
    backend.Backend = "MsSql";
    backend.ProviderName = "System.Data.SqlClient";

    backend.Logging.LogEvents = LoggingLevel.Normal;
    backend.Logging.StackTrace = true;
    backend.Logging.EventStoreCapacity = 10000;
    backend.Logging.MetricStoreCapacity = 3600;
    backend.Logging.MetricStoreSnapshotInterval = 1000;
    backend.Logging.Downloader.EventBinary = true;
    backend.Logging.Downloader.MetricBinary = true;
    backend.Logging.Downloader.Filename = "C:\\MyFileName";
    backend.Logging.Downloader.MaxFileSizeKB = 1000;
    backend.Logging.Downloader.NumberOfBackups = 3;
    backend.Logging.Downloader.EventPollSeconds = 1;
    backend.Logging.Downloader.MetricPollSeconds = 1; 

    return backend;
}

Public Shared Function GetBackendConfiguration() _
As BackendConfiguration
    Dim backend As BackendConfiguration =
        New BackendConfiguration()
    backend.Backend = "MsSql"
    backend.ProviderName = "System.Data.SqlClient"

    backend.Logging.LogEvents = LoggingLevel.Normal
    backend.Logging.StackTrace = True
    backend.Logging.EventStoreCapacity = 10000
    backend.Logging.MetricStoreCapacity = 3600
    backend.Logging.MetricStoreSnapshotInterval = 1000
    backend.Logging.Downloader.EventBinary = True
    backend.Logging.Downloader.MetricBinary = True
    backend.Logging.Downloader.Filename = "C:\MyFileName"
    backend.Logging.Downloader.MaxFileSizeKB = 1000
    backend.Logging.Downloader.NumberOfBackups = 3
    backend.Logging.Downloader.EventPollSeconds = 1
    backend.Logging.Downloader.MetricPollSeconds = 1

    Return backend
End Function

Save the FluentModel.cs(vb) file.

For the purpose of this example, in the Main() method of the the FluentModelClient application, you can execute a few CRUD operations in a loop. For example:

Before running the sample, you need to ensure that the FluentModelClient application is configured according the the tutorial in the Consuming a Model - Configuration article, and that the database will be available on the server. The latter can be achieved through exposing the UpdateSchema method as a method of your context class.

public static void Main()
{
    // Ensure that the database is available
    using (FluentModel dbContext = new FluentModel())
    {
        dbContext.UpdateSchema();
    }

    for (int i = 0; i < 5; i++)
    {
        using (FluentModel dbContext = new FluentModel())
        {
            // Add a new customer.
            Customer newCustomer = new Customer
            {
                ID = 1,
                Name = "John Smith",
                EmailAddress = "john@smith.com",
                DateCreated = DateTime.Now
            };
            dbContext.Add(newCustomer);

            // Commit changes to the database.
            dbContext.SaveChanges();

            // Get the first customer using LINQ and modify it.
            var customer = dbContext.Customers.FirstOrDefault();

            customer.EmailAddress = "john.smith@domain.com";

            // Commit changes to the database.
            dbContext.SaveChanges();

            // Use LINQ to retrieve a customer named John.
            var CustomertoDelete = dbContext.Customers.
                Where(c => c.Name.StartsWith("John")).
                FirstOrDefault();

            // Delete the customer from the database.
            dbContext.Delete(CustomertoDelete);

            // Commit changes to the database.
            dbContext.SaveChanges();
        }
    }
}

Sub Main()
    Using dbContext As New FluentModel
        dbContext.UpdateSchema()
    End Using
    For i As Integer = 0 To 4
        Using dbContext As New FluentModel()
            ' Add a new customer.
            Dim newCustomer As Customer = New Customer _
                With
                {
                    .Id = 1,
                    .Name = "John Smith",
                    .EmailAddress = "john@smith.com",
                    .DateCreated = Date.Now
                }

            dbContext.Add(newCustomer)

            ' Commit changes to the database.
            dbContext.SaveChanges()

            ' Get the first customer using LINQ and modify it.
            Dim customer = dbContext.Customers.FirstOrDefault()

            customer.EmailAddress = "john.smith@domain.com"

            ' Commit changes to the database.
            dbContext.SaveChanges()

            ' Use LINQ to retrieve a customer named John.
            Dim CustomertoDelete = dbContext.Customers.
                Where(Function(c) c.Name.StartsWith("John")).
                FirstOrDefault()

            ' Delete the customer from the database.
            dbContext.Delete(CustomertoDelete)

            ' Commit changes to the database.
            dbContext.SaveChanges()
        End Using
    Next i
End Sub

Run the application. All Telerik Data Access activity will be logged to the specified file.

Starting the Profiler

Open Telerik Data Access Profiler and Tuning Advisor. You could do this from the Windows start menu.
In order to load log files in the Telerik Data Access Profiler, you need to use the Open Folder toolbar command.
Navigate to the directory the log was saved, for example, in this guide it was saved to the root of the C drive.
Select the .oalog file. The Telerik Data Access logger produces two files:
1. .oalog file - contains all of the operation information.
2. .oametrics file - contains all of the counters for the OpenAccessContext. Things like context lifetime, inserts per second, selects per second, cache hits, cache misses, etc. are stored in this file.
The log should now be ready for you to explore in the Telerik Data Access Profiler and Tuning Advisor.

Next Steps

For more information about the Telerik Data Access Profiler, please refer to the Telerik Data Access Profiler and Tuning Advisor section.