It's particularly important to note the costs associated with each node of the plan, and to identify how indexes are used (or not) in the various nodes. While execution plans may seem complicated at first, it's worth spending a bit of time getting familiar with them. The typical analysis workflow would be to use SQL Server Management Studio, pasting the SQL of a slow query identified via one of the means above, and producing a graphical execution plan: To get started on SQL Server, see the documentation on query execution plans. Relational databases typically provide a way for users to see the query plan, along with calculated costing for different parts of the query this is invaluable for improving your queries. (incidentally, the plan itself should usually be cached at the server for optimal performance). When databases receive a SQL statement, they typically produce a plan of how that plan is to be executed this sometimes requires complicated decision-making based on which indexes have been defined, how much data exists in tables, etc. Once you've pinpointed a problematic query that requires optimization, the next step is usually analyzing the query's execution plan. More information on this is available in the Application Insights performance tutorial, and in the Azure SQL analytics page. If you are using Azure, then Azure Application Insights provides such powerful monitoring out of the box, integrating database performance and query execution times in the analysis of how quickly your web requests are being served. The SQL Server documentation on monitoring provides more information on these features, as well as others.Īnother approach for capturing performance data is to collect information automatically emitted by either EF or the database driver via the DiagnosticSource interface, and then analyze that data or display it on a dashboard. It's beyond the scope of this section to go into the details, but two capabilities worth mentioning are the Activity Monitor, which provides a live dashboard of server activity (including the most expensive queries), and the Extended Events (XEvent) feature, which allows defining arbitrary data capture sessions which can be tailored to your exact needs. Databases typically come with their own tracing and performance analysis tools, which usually provide much richer, database-specific information beyond simple execution times the actual setup, capabilities and usage vary considerably across databases.įor example, SQL Server Management Studio is a powerful client that can connect to your SQL Server instance and provide valuable management and performance information. There are various alternatives to EF's logging feature for capturing command execution times, which may be more powerful. Other interfaces for capturing performance data It's often worth tagging the major queries of an application in this way, to make the command execution logs more immediately readable. The tag shows up in the logs: - This is my spatial query! Orderby f.Location.Distance(myLocation) descending Var nearestPeople = (from f in ("This is my spatial query!") To help with this difficulty, you may want to use EF's query tags feature, which allows you to inject a small, identifying comment into the SQL query: var myLocation = new Point(1, 2) One problem with command execution logging is that it's sometimes difficult to correlate SQL queries and LINQ queries: the SQL commands executed by EF can look very different from the LINQ queries from which they were generated. Correlating database commands to LINQ queries It's recommended to only keep logging on for a short interval of time to gather data - while carefully monitoring your application - or to capture logging data on a pre-production system. The logging itself slows down your application, and may quickly create huge log files which can fill up your server's disk. Leaving command execution logging enabled in your production environment is usually a bad idea. Command logging can also reveal cases where unexpected database roundtrips are being made this would show up as multiple commands where only one is expected. If a certain command takes more than expected, you've found a possible culprit for a performance issue, and can now focus on it to understand why it's running slowly. Protected override void OnConfiguring(DbContextOptionsBuilder the logging level is set at LogLevel.Information, EF emits a log message for each command execution with the time taken: info: 09:12:36.117 RelationalEventId.CommandExecuted ()Įxecuted DbCommand (4ms), CommandType='Text', CommandTimeout='30'] => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information)) Private static ILoggerFactory ContextLoggerFactory Protected override void OnConfiguring(DbContextOptionsBuilder LogLevel.Information)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |