As data continues to explode, DBAs must constantly stay a step ahead by finding new ways to improve database efficiency and performance. Managing data in today’s complex IT environment requires creative approaches to a number of different DBA processes. Here are useful tips in five main categories that can help DBAs keep their heads above water and allow their companies to more effectively utilize their data to drive business value.
Know what needs to be managed, from Express to Enterprise
Discovery is a crucial part of every DBA’s job, and when it’s not done right, the resulting issues can be hugely detrimental. If you do not have a good foundational understanding of what is in your environment, nothing else you do as a DBA will be complete. Because everything starts with discovery, make sure you choose the right tool to support you.
Many DBAs rely on SQL browser services to discover SQL Server instances. However, relying solely on the browser service is inadequate as SQL Express instances will not be seen. This can cause problems later on because many applications may take advantage of a locally installed instance of SQL Express. It’s often not until a user’s workstation gets re-imaged or replaced that we know what we’ve lost. Multiple means of discovery are required to find these instances since SQL Express only communicates locally, is installed as a named instance, and has the browser service turned off by default.
Perform regular, basic health checks
Database corruption can happen at any time and have disastrous effects on your data. Everyone knows you need to backup your databases, but backing up corrupt data is useless. To prevent this, perform regular, basic health checks using a consistent process.
DBCC CHECKDB is responsible for validating the integrity and consistency of a database. As a rule of thumb, you should check database integrity with DBCC CHECKDB command frequently enough, almost daily, that you can be sure you have a full backup available in case you need to restore. Keep tabs on this and other health checks in an online portal that you make available to your team as a matter of best practice.
Know your SQL Server database KPIs
A DBA is frequently challenged on how to improve, optimize, and maintain the performance of SQL server databases. SQL performance tuning is one of many ways to optimize the performance. Looking at tuning a busy system, but don't know where to start? The sheer number of KPIs you can manage is a bit nerve-wracking, but there are many products and online guides that can help you find those special metrics that reveal previous diagnostic data. For example, consider Page Life Expectancy. A decline in page life expectancy is an indication of an increase in the physical I/O requirements for a user database. If it seems to fall suddenly at regular intervals, see what process is running at that time and what improvements you can make.
Learn how to analyze data like a boss
Cubes have proven to be a valuable tool to analyze data, but some DBAs are not taking full advantage of their capabilities. For example, it’s always a good idea to use views as the data source for dimension and fact tables in your Analysis Services Cubes. Views will let you leverage the expertise with relational databases that most likely already exists in your environment.
When you use a view as a fact table, you can manage incremental updates by altering the WHERE clause within the view instead of assigning the WHERE clause to an OLAP partition. Calculating columns in a view will take more time and resources during cube processing. If this becomes an issue with processing, add a persisted computed column directly to the fact table so that the calculation is done during the insert/update of the fact table.
Using views as the data source for dimension and fact tables in your Analysis Services Cubes provides an abstraction layer between your data tables and the cube. Some advantages of doing so are:
- Allows you to use reference tables for clarity in your cube
- You can easily create derived attributes for dimensions in the views
- Deriving new measures from existing ones is fairly simple
- You have the ability to provide enhanced security through the views
- Business friendly terminology can be used to rename columns within the views
Keep it all compliant, please
We all know compliance can be a drain on resources that can cause ongoing stress for DBAs worrying about missing something on their watch. While it’s tempting to audit every transaction to ensure you don’t miss anything, this can kill performance. Besides, it requires a large amount of storage space. Keeping an eye on your SQL Servers shouldn’t bring down your SQL enterprise.
Creating an audit strategy is essential to any successful auditing effort. Define what needs to be audited (data and events) before you start. Revisit this over time and adjust as needed to keep up with changes. As an example, you should audit and alert on sensitive columns by tracking who has issued “SELECT” statements against any table. Keep track of all your results over time for quarterly and annual audit events.
The only constant is change
Today’s DBAs are facing unprecedented challenges with the exponential increases in data, its importance to businesses, and its complexity. The only thing that’s certain in this database era is constant change, so DBAs need all the help they can get to stay a step ahead. By implementing a few of these recommendations, DBAs can address some issues that might be impacting their ability to manage databases effectively.
Image courtesy of Shutterstock