How to Check Database Performance Facets in SQL Server? – Interview Question of the Week #270

Question: How to Check Database Performance Facets in SQL Server?

How to Check Database Performance Facets in SQL Server? - Interview Question of the Week #270 Performance-Facets-800x196

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.

Solarwinds

First, open SQL Server Management Studio and right-click over the database name and go-to option facets.

How to Check Database Performance Facets in SQL Server? - Interview Question of the Week #270 perfromancefacet1

Next, there will be a drop-down menu for the facet. Select here the option Database Performance.

How to Check Database Performance Facets in SQL Server? - Interview Question of the Week #270 perfromancefacet2

Once you select database performance as an option, it will display a few options related to performance for the database.

How to Check Database Performance Facets in SQL Server? - Interview Question of the Week #270 perfromancefacet3

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)

Solarwinds
, , ,
Previous Post
What is the Difference Between sql_handle and plan_handle?- Interview Question of the Week #269
Next Post
What is Stored in TempDB? – Interview Question of the Week #271

Related Posts

Leave a Reply

Menu