March 2022

March 22, 2022January 24, 2025

Monitor your SQL Server Indexes

Problem

I want to view the performance of my SQL Server indexes across the database farm and determine the top instances by index disk usage. I also want to look for servers that encounter problems when running the update index statistic job.

Solution

dbWatch Control Center can monitor your Microsoft SQL Server’s index usage rate and perform database index maintenance to make sure that your servers are running smoothly.

Microsoft SQL Server recommends reorganizing indexes with fragmentation level between 5% to 30% but if it is greater than 30% you will need to perform an index rebuild instead.  In this blog, you will learn how you can analyze your database indexes within your database farm and determine which instance has its SQL Server maintenance plans or index maintenance jobs running longer than the usual by using dbWatch Control Center features.

Example 1: Index Analyze Statistics Job

On the monitoring view, right click on your SQL Server instance. Click Configure Jobs.

A new window will appear, tick the job Index usage statistics (all databases). Click Install.

As seen in the screenshot below, this job collects database index and table usage, shows information about the object/table name type of index total seeks and scans. It gives you information about how big your tables are and if it frequently uses an index scan or index seek. Ideally, you would want your indexes using an index seek.

In the job – Update index statistics, within this view you will be able to see useful information such as the database name, database index count per database and total indexes (clustered/non clustered) analyzed per database. It will also show you the number of errors within the execution of the update statistics task, if there are any.

In this view you can easily sort the columns in ascending or descending order. This will help to determine which instance within the database farm has the longest/shortest elapsed time when analyzing indexes, this is useful if some maintenance jobs are taking a longer time to finish than usual.

An example application of the jobs shown is when you have a scheduled index rebuild job weekly, from Week one  to Week seven , your average elapsed time was around 15-20 mins. to finish.

But suddenly, when you look at the dbWatch Control Center view shows that this week (Week 8) it took 80-90 mins. to finish. The average time increased 4 times its usual execution, this tells you that there is something going on within your SQL Server indexes – from there on, you will start investigating the cause of the problem. 

Example 2: Index Reorganize and Rebuild View

dbWatch Control Center also provides you with index maintenance jobs such as Index rebuild and reorganize.

This view displays the index rebuild/reorganize statistics. Here you will see the list of instances and the number of databases checked within the instance, and it also shows which databases was rebuilt and which was ignored.

In this view you will be able to sort the column to determine which instance consumes the most disk space.

The important part of this view is the size before and after the index rebuild took place and the disk usage reduced by the operation.

When you right- click on a selected instance, you have an option to execute the task and start an index reorganize/rebuild task on the instance databases’ or configure the task and its schedule.

If you choose Details, you will see a report regarding the history of the reorganize or rebuild execution within that instance, it also shows the information about the table and index fragmentation average and the disk usage before and after the reorganize or rebuild operation.

As you can see from the examples, you can determine which instances have the biggest database index disk usage within your database farm. You will also be able to assess if a certain index operation schedule should be adjusted based on the performance and other details shown in the reporting view.

With dbWatch Control Center, database admin’s can now keep track of their SQL Server indexes and their performance. As a DBA, you can now analyze how your database indexes are being used in your environment and plan on how you will maintain these SQL Server indexes efficiently.  

dbWatch Control Center assists database administrators to efficiently run their databases, from proactive monitoring of database performance to the management of tasks.  

Discover how dbWatch Control Center can monitor your SQL Server Indexes, try dbWatch Control Center today.

March 20, 2022January 24, 2025

Working with MySQL vs. MariaDB – What’s Different?

Everyone has heard of MySQL. Well, probably not everyone – say, everyone in the development and database monitoring space. Do you ever wonder why?

MySQL is everywhere. The industries that use MySQL as their primary database management system include the automobile industry (think Volvo, BMW, etc.), the fashion industry (think SheIn and the like), the finance industry uses it heavily, it’s prevalent in the education industry.

Do we even need to talk about software developers? In the software industry, MySQL is used even more heavily. We mean, think about it. Have you ever heard of anybody in the industry saying “I know of a software developer with experience who hasn’t heard of MySQL”? Chances are, you did not. And chances are, most developers would laugh at that statement – MySQL is one of the most widely available database management systems.

Most developers more familiar with MySQL probably know of MySQL flavors as well. Well, we’re talking Percona Server and MariaDB – you know the drill. In many cases, those are similar, but they have their own differences as well. In this blog, we are going to tell you how one of those (MariaDB in particular) differs from MySQL.

What’s MariaDB and MySQL?

Believe it or not, this question is one of the popular ones that hiring managers ask junior database administrators. Why? Because that’s a good way to determine if a beginner knows a little of their craft. Most novices think that both MariaDB and MySQL must be the one and the same – and the reason behind that is that they think that MySQL and MariaDB are built to solve the same issue, so the queries that might be run are the same, the way the databases are configured is pretty much the same, it’s just a question of the name.

Ask a junior DBA such a question and you will hear a “the same DBMS, just with a different name” without much consideration. That’s not the ideal answer, though. In reality, MariaDB is a flavor of MySQL. A flavor that comes with many of its own variations of the features available in MySQL.

That’s not to say that MySQL is a bad DBMS though – far from that – it’s just that MariaDB has a set of features not available in MySQL, and vice versa.

MySQL vs. MariaDB

Here are some of the key differences between both of the database management systems so you could better understand how database farm management might work with those:

As you can see, the two might not seem too far apart – at least not until you look deeper into them. Many database administrators electing to use MariaDB instead of its counterpart do it because MariaDB comes with a load balancer unique to its own, though that’s not the core reason MariaDB is a competitor of MySQL – the main reason is the fact that MariaDB comes with more storage engines to choose from.

Think about it:

Is it possible to work with petabytes of data in MariaDB? Yes, but not in MySQL, one should use ColumnStore.
Is it possible to have a crash-safe MyISAM that’s known for unreliability? Yes, but not in MySQL – one could use Aria.
Want a read-only storage engine that would store all of its data inside of S3 storage in MySQL? Mission impossible. Turn to MariaDB and use S3 instead.
Want an actively-developed version of FEDERATED? Turn to MariaDB and use FederatedX, an option not available in MySQL.

Is it Worth Using MySQL?

With all of these unique storage engines available in MariaDB, a natural question arises – should one still use MySQL for daily ops? Shouldn’t MariaDB be the option that is always used, given that it should have more use cases? Isn’t database monitoring more easy on MariaDB? No?

Well, don’t jump to conclusions too quickly. There are a couple of things MySQL doesn’t have that MariaDB does, indeed, but there are a couple of things exclusive to MySQL as well. For example, have you heard of the Enterprise edition of MySQL?

It’s not cheap, but it comes with a nice amout of features including the ability to deliver “hot”, online backups of your databases, Enterprise data masking features to protect your organizations protect your most sensitive data by hiding sensitive data when it’s required, but perhaps two of the most widely known features in this space are the firewall and the audit plugin.

The MySQL Enterprise Firewall protects your MySQL installations from a wide range of cyber security threats including SQL injection. By using the firewall, you can create a “whitelist” (a list of allowed values) of SQL statements and block the rest, monitor the database for unauthorized activity, etc.: that’s one feature MariaDB doesn’t offer, and users who use MariaDB miss out on this subject.

What to Choose?

If you want to weigh your options before choosing, we have good news – it’s not that hard of a choice! If you’re a novice just learning about MySQL, go with MySQL, then transition into MariaDB when you know a little more about the DBMS. If you’re looking to work with big data sets or more advanced features, go with MariaDB, however, if you’re looking for high-level security measures (we’re talking enterprise level here) you’re better off using MySQL.

Neither of those choices are “wrong” though – you can easily switch to MariaDB and vice versa if you get bored or if the feature list doesn’t satisfy you. Moreover, all of the monitoring tools that are available on the market, including dbWatch, let you monitor both database management systems – so you never lose out after choosing one of them.

As far as monitoring goes, though, we have good news – monitoring those two database management systems almost never differs. Seriously – one can improve performance in MySQL in the exact same way that one improves performance in MariaDB, and vice versa.

The reason tools like dbWatch exist is plain and simple, though – it saves you both time and money that you will need when monitoring databases manually. Look at these database jobs: do you think you would have time to keep an eye out on each and every one of them? Not even talking about tens of database instances here.

Tools like dbWatch help you schedule these kinds of database jobs and make sure they always run at the exact time specified – not only that, dbWatch will run your jobs in a proper way, always making sure that they never miss deadlines and do the work that they are supposed to do.

Do you have the time to take care of everything yourself? Chances are, you do not – try dbWatch for free and tell us what you think. We are sure – you will be impressed. Give dbWatch a spin and we will see you in the next one!

March 19, 2022January 29, 2025

Running Databases: Docker vs. Alternatives

For many developers, Docker is a frequent friend. Some even turn to Docker to run their database systems – applications are safe in containers, containers usually are lightweight, and generally, containers allow Devs to bundle all the software they use into “boxes” called Docker containers. But how are they compared to other alternatives?

Running Databases in Containers

Running software in containers is generally mainstream, but what about databases or database servers (for example, SQL Server, MySQL, Oracle, PostgreSQL, MariaDB)? Have you ever heard of a dev running his databases in containers? Most likely not, because doing so requires extra resources and dedication toward the craft of containers. To run your databases in a containerized environment and utilize database monitoring properly, here’s what you must know:

Containers let you approach your databases as an on-demand utility (each application can have its own database.)
Containers let you have less “mess” on your machines – you always know what’s where.
Multiple containers can run on the same OS.

Running databases in containers is not very easy, though: if we think of containers as a lightweight alternative to VMs, we will quickly realize that containers, in this case, would let us “engulf” our applications in containers that each might run different operating systems. In the case of databases, that most likely would look like each DBMS is “encapsulated” into its own container image. Don’t forget that when dealing with Docker, we’d usually need .yml files that would define the services our containers run, etc. Everything looks pretty complex, doesn’t it? How do we decide whether we need that in regards to databases? It might help to look at alternatives before answering this question.

Alternatives to Containerization with Databases

The most frequent alternative to containerization is simply running a database on a standalone server. This process is quite a lot easier than running databases on containers, and it can easily be accomplished both locally and in a live environment.

On a local server, you need to spin up WAMP or its alternative if you’re using Linux, and when you’re in a live environment, buy access to shared hosting, a VPS, or a dedicated server if you’re building services requiring more hardware resources, and you should be good to go. In many cases, databases will even come pre-installed to the services you use.

What’s Better?

If you ask a developer or even a database administrator which option is better – should you run your databases on Docker or a more “natural” environment involving servers – you will probably hear an answer like “Don’t bother with Docker.” Want to know why? Running databases in a containerized environment (using Docker) isn’t a very smart choice due to complexity – Docker might bring less clutter.

Still, it will certainly bring more headaches just by the way it’s structured. Running your databases on Linux is the traditional and straightforward way to get on the web. When monitoring is concerned, everything can be set up in a more “traditional” fashion when containers don’t get in the way. Look at dbWatch: define your connection details, and you’re done! Isn’t that awesome?

After your database instances (of whatever database management system flavor you’re using) are imported into dbWatch, they then could be monitored. In a span of minutes, a bunch of expert-made database jobs could be run:

With a wide variety of database jobs, dbWatch proves to be a perfect choice for many business owners and database administrators – just a couple of clicks, and your database issues will be solved.

There’s no need to reinvent the wheel and run databases in a containerized environment using Docker: in fact, not many people run their databases on Docker at all – there’s a good reason for that: doing so is pretty complex, even to advanced database administrators. In many cases, database administrators are not sysadmins, so the traditional way is preferred here. Did we talk about junior developers and DBAs? Docker is not a good choice for them either: in fact, it might steer them away from development altogether, not even talking about choices

Summary of Running Databases: Docker vs. Alternatives

As far as containers are concerned, they certainly have their own upsides, but running databases inside of them isn’t one of them.

Running your databases in a traditional, non-containerized way lets you develop and roll out features faster, and you will be able to solve issues faster as well: for example, post a problem you’re having with your databases on Stack Overflow.

Chances are, you will instantly be bombarded with questions like “Why are you using Docker?” “Isn’t the traditional way – a good way for you?” etc., so go with the traditional path instead. Whatever path you choose, remember that there are a bunch of database monitoring tools – and dbWatch is one of them.

Try out dbWatch for yourself with a free 90-day trial.