Get database statistics from EntityFramework Core after calling DbContext.SaveChanges

Question

I'd like to write some automated tests to measure how much database IO is occurring during particular processes in our application and ensure they never go above a certain threshold.

Outside of EF, you would:

Open SQL Server Management Studio
set io statistics on
...run your queries
... look at the response returned in the Messages window see the statistics.

So I'm wondering if there is some way to get these same measurements as part of the call to DbContext.SaveChanges. I think you could override the SaveChangesAsync and SaveChanges methods to execute the "set io statistics" statement, but doing it that way would result in an extra database hit and also I wouldn't know how to get the statistics back after SaveChangesAsync was complete.

Any thoughts or ideas?

David Browne - Microsoft · Accepted Answer · 2017-11-16 19:00:51Z

The statistics messges come back as Info Messages, which you can aggregate. Eg in EF Core, something like:

            db.Database.OpenConnection();
            var con = db.Database.GetDbConnection() as SqlConnection;
            db.Database.ExecuteSqlCommand("set statistics io on");
            con.InfoMessage += (s, a) =>
            {
                if (a.Errors[0].Number == 3615)
                {
                    Console.WriteLine(a.Message);
                }
            };

A better approach is probably to monitor on the server. You can use an XEvents session to capture the logout event, which carries summary statistics for each session that ends ( or is reused from the connection pool). EG

CREATE EVENT SESSION [Session Stats] ON SERVER 
ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.username))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Collectives™ on Stack Overflow

Get database statistics from EntityFramework Core after calling DbContext.SaveChanges

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related