Indexes are very crucial part of a table and to be able to decide on which index is required, which one should be dropped and which one should be changed is one of the most important part of DBA’s job, especially if you are into performance tuning of an application connecting to your database. In …
I thought of doing one session for SQL Server DBA interview Questions and Answers and share with you. These questions are of different levels and check your overall understanding of SQL Server database administration and approach towards solving issues. Interview typically lasts for 20-40 minutes, nobody can find everything about candidate however these questions and …
MS SQL Server DBA Interview Questions and Answers For Experienced Read More »
As you know Index recommendation is very handy when you execute a query and check the execution plan and SQL Servers suggest to create an index and this much gain will be there in query performance, Its not always correct however very helpful. All the suggestions shouldn’t be implemented neither it should be ignored completely. …
You would have heard from many in DBA community or in development community that if there is index seek in the execution plan, its always good. Lets see today whether its a myth or fact. To clarify further, Index seeks are mostly good when it is with equality operator however when it comes to inequality …
Index Seek is Necessarily Always Great – Myth or Fact? Read More »
Recently our server where MSSQL Server database resides was upgraded and SSD disk was installed. Let us understand the performance improvement on different processes of SQL Server where it had positive impact and where it didn’t have much. Sudden Improvement was observed at below Proesses: Backup of database Maintenance Job Report Generation Database Consistency CHECKDB …
Impact of SSD Disk over HDD on MSSQL Performance Read More »
This scenario could be faced by someone who is having multiple instances running on the same server. Mostly we try to have dedicated server for Production environment however for Test environments(SIT, UAT, Dev) – Based on criticality of application, we tend to install multiple instances of SQL Server on the same server. As you are …
What If Mismatched Instance Name & Port – Which One Takes Precedence Read More »
You would have seen number of articles describing the importance of MAXDOP(MAXimum Degree Of Parallelism) and why should it be changed and shouldn’t be left to its default value, which is set at 0 after installation of SQL Server – I have covered them in one of my earlier post – Why MAXDOP shouldn’t be …
MAXDOP and CTP Value – Should You Change Them In Working Hour At Production Server Read More »
As we have covered recently why MAXDOP value should not be left to default and must be changed after installation of SQL Server(Why MAXDOP shouldn’t be left to default(0) and Must Be Changed). Similarly, when Maximum Server Value should be looked after doing the fresh installation. We can check the value of this by right …
Best practice for Maximum Server Memory Value in SQL Server Read More »
There are multiple way of finding size of a table and its indexes in a database. First and foremost, lets understand that table size contains multiple components – Size of data, size of index and unused space. If we add all of them, we could derive the value of reserved size, which is consumed by …
Find Size of Table(s) & Its Index(es) In A Database Read More »
There are many instances when same query is executed number of times on our database server and every time optimizer has to compile the same and create query execution plan and same is stored in cache so that it would be used next time and so. When server is heavily used and cache keeps getting …
How to Improve Performance When Adhoc Queries are Executed Frequently Read More »