Navigation and service panel


Database performance analysis - Part II

By Ernst Joss on 22. April 2015, No comments

Usually one might not bother about database for Sitcore websites, especially not when the „normal“ SQL guidelines are taken into account during setup of your SQL server. But there might be situation where you think that maybe your database could be the limiting factor for your website. In this cases it can be helpful to know a little bit more about the state of your database used.
In Part I I described some techniques how to identify queries for further analysis. This second Part will now talk about how further analysis for particular queries can happen.

Query execution plans

One important thing about SQL Server is its ability to plan on how the current query should be executed in the most optimized way possible. Therefore SQL servers analyze each query and generate an execution plan on how to perform the query. By analyzing this execution plans, possible improvements may occur and can be tested.

A good thing about execution plans is, that they offer one thing immediately by carefully looking at. If there is an index missing or not in place which will help to boost the query, this is offered to you in form of the definition of the index he proposes to add:

SQL Query Plan with missing index hint

Further information can be found by searching one of the most time consuming operation and try to understand its purpose and the work happening behind. On mouse hover every operation will clearly tell you what is done and on what data he is relying in order to perform this operation. A good tool to help you better identify your queries weak point would be SQL Sentry Plan Explorer. It not only displays the execution plans as SQL server does, but offers more options like colored time consuming information together with options to get even more detailed information why this operation exactly happens. This is done by stating the sql fields asked for, but maybe not be available in an index. More options available are join diagrams as well as query columns and table i/o where you can look at. If you need even more like a seamless SQL Server integration you could get for the pro version.

Be aware, that your execution plan may completely change if just a small index is added or removed. This may be good or bad, but comparing these two plans together is almost impossible. The "only" comparison that might be meaningful is time needed to execute.

Categories  Tools  Troubleshooting Tags  SQL  diagnostics

No comments

Add your comment

Your email address will not be published. Required fields are marked *