Become a Better Database Administrator [A How-To Guide]

With cutting-edge technology emerging every year the future of database and cloud technologies look secure. Every organisation whether it big or small need database to store all the relatable data because data is more valuable than money itself.

These days most of the data related work is done on the SQL server and there are several methods to get the best out of SQL server without even upgrading to the latest versions. Here in this article, we have mentioned the top 4 strategies for a Database Administrator (DBA) to improve the SQL server performance. Before we jump to the strategies let’s discuss what does a Database Administrator do?

If you are reading this article you probably known about DBA, but if not, here we have put layout the basic definition of DBA working.

A Database Administrator is a professional in charge of all the functions related to the database development, storage security and data retrieval. The main job of a DBA is, use the Database Management System software such as SQL and evaluate the ways to design and organize the data in the database.

Now let’s discuss the 4 major strategies a DBA should follow to improve the SQL server Performance.

1. Patch your Server

Security of the database should be the major concern of an organisation, but many organisations do not take it seriously because for security they have to spend extra capital, and companies do not want extravagant on the additional Security Services.

For some reason not spending on the security makes more sense because security stance requires regular updates and with each update, the security service provider can charge you extra money.

However, there are some standard measures you can take to secure your database, the most common approach is Update OS, SQL server and Service packs. Updation SQL server and Service pack just not increase security but also bring better features and performance to the server.

2. Review Your Indexes

More than 93 percent of SQL Server instances have a ratio of 3.5 to 4 read for every writes and this ratio goes far depend on the system. So, you will definitely see more benefits if you carefully add indexes to your table rather than writing activities.

Here we are suggesting adding indexes to your table but it does not mean you go crazy and add every sing index suggested by the SQL server, rather you should review your frequent run queries and understand which is giving you the most benefits.

It may also possible that more than 80 per cent of your database is from third-party service vendor and you do not have move control over the database object and you cannot make any changes on it either. So, make sure that you use the naming standard for your indexes which let you identify them easily.

3. Execution Plan and Query Store

SQL Server 2016 and higher versions do not support the query store feature, basically, it is a database performance tool. It is used to capture the running queries, estimate execution plans and runtime statistic.

This was a very useful tool which was capable of isolating your most expensive queries and observe changes in execution plans that may negatively impact your query performance.

But now we do not have this feature, but there are some third-party performance tools which act as a Query store tool and help you to get information about the execution plans. You can also use the SQL Server’s plan cache to extract the information like query store, but that could be tricky for a DBA.

4. Build a Baseline

The query store would probably help you to improve the SQL performance, apart from it a DBA should have a performance baseline tool. To build a simple counter profile you can even use a Window Performance Monitor.

• CPU Percentage
• I/O Sec/Read
• I/O Sec/Write
• I/O Writes/Sec
• I/O Reads/Sec
• SQL Server Page Life Expectancy

Mostly all the Operating system memory counter is irrelevant on the database server because a database always eats all the available RAM.

The Page Life Expectancy show how rapidly the SQL server goes through data pages in its buffer pool, and this could lead to the poor performing queries and under-resourced server, there are many third-party tools to solve this problem.

Conclusion

Here we have tried to mention all the measures that a DBA could follow, these tips will definitely help you to improve to the overall performance of your SQL server. do not forget to do backups and check the database consistency on a regular bases.