Question: How to Check Database Performance Facets in SQL Server?
Answer: I received this course the other day from my client while working with them on the Comprehensive Database Performance Health Check. The question was very simple they wanted to know what are the various database performance facets available for SQL Server and what is the current values of it. The word facet means aspect or one of the features of many.
Well, it is very simple to find out the facets of SQL Server Just follow the diagrams listed over here.
First, open SQL Server Management Studio and right-click over the database name and go-to option facets.
Next, there will be a drop-down menu for the facet. Select here the option Database Performance.
Once you select database performance as an option, it will display a few options related to performance for the database.
On this screen, you will see seven different facets that are related to performance. The one which I find the most interesting is DataAndLogFilesOnSeparateLogicalVolumes. I have been discussing this for quite a while with my clients, however not every client understands the importance of this feature.
Since SQL Server team has added these facets here, when I see an argument from client, I usually bring them here and show the screen. Please note that it is the best practice that you keep your Data and Log files to different logical volumes. It is even a better practice if both the data and log files are on two different physical volumes.
When we talk about SQL Server Performance Tuning we have lots of things to consider. There is not a single magic bullet that will help you fix the performance problem there are multiple things and each has its own impact on the performance.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
