Running SQL Queries with Tools in dbWatch Control Center

If you find yourself developing any kinds of applications focused more towards the web or even mobile applications, it is inevitable that sometimes you might find yourself running a couple of SQL queries – you need to interact with your database systems anyway, don’t you? 

1-Sep-28-2021-07-53-02-71-AM

Don’t fret – all database users run SQL queries. It’s important to run them in a proper medium though – while most, say, MySQL database administrators, find themselves running their queries inside of phpMyAdmin, some database administrators might find themselves searching for different alternatives.

Indeed, there a quite a couple – if you find yourself deeply immersed inside of the database management world, you might have already noticed that you can run your SQL queries together with some database management tools. Most database management tools that allow you to run SQL queries inside of your database instances also allow you to do a couple of different things – for example, run a couple of database jobs that let you observe the full power of your database instances or let you see the amount of logical reads (writes) going on in your database instance: 

Today we are going to be focusing on another one for this blog – we are going to be telling you why it’s important to get assistance while running SQL queries inside of your database instance together with tools like the one provided by dbWatch. 

Running SQL Queries with dbWatch Control Center 

You see, while dbWatch isn’t a SQL client, it does have an ability to help you craft your SQL queries – simply observe the left-hand side of dbWatch and choose the fourth option from the top: 

Once you’re done, make sure dbWatch is actually connected to the database instance you want to run queries on – click on the Reload Auths button: 

Once the authentication is reloaded, you should be able to see the username next to your database instance: 

Tick the checkbox next to your database instance and you should be more than ready to run queries inside of a given database instance (do note that if you do not, dbWatch will provide you with an error): 

Now it’s time to write your queries that interact with your database instance – for that, dbWatch will provide you with a very nice user interface and underneath – the ability to see the results of a given database query – input your query inside of the SQL field, and click “Execute” if you want to run it – if not, you can also clear all of the input, output (also save them.)

While your query is running, you will be able to see the amount of time your database query is running – if you don’t see any results, but keep seeing that the query continues executing (see example below), you might want to add a couple of partitions or indexes on top of your table – that should fix the issue: 

If your query ran successfully, you will be able to observe the output (in this case, our column is called “column”, so we needed to escape it with backticks (note the `column` inside of the query)): 

See underneath the SQL window displayed by dbWatch – you will be able to observe what queries failed to run and what queries ran successfully: 

See the “!” in the triangle-shaped box? Those queries failed. See the tick next to the last query in the list? That query ran successfully! 

The ability to simply run queries is not everything that dbWatch comes with though – as already noted, you will also be able to “load input” to run (e.g. import SQL files that contain SQL queries for dbWatch to run), clear input (the SQL queries you just ran) or output (the results of the SQL queries), if you want, you will be able to save them as well. For example, click “Clear Input” and you will be able to see that your SQL queries disappeared:

See? Easy as that! No more deletion – who needs that? dbWatch will delete all of the things for you. 

Output Panels 

In case you didn’t know that, dbWatch will also provide you with one output panel per every result set returned. Here’s how that looks like: 

Summary 

It doesn’t matter whether you are a high-end developer that deals with databases on-and-off or a full-fledged DBA of Oracle, MySQL, MariaDB, or even Sybase – you will need to be running queries focused at a specific database instance sooner or later. dbWatch comes with a SQL query feature – underneath the query you will be able to observe the results of it. You will also be able to see how many queries of yours have run successfully and how many failed – do so and your database instances should be headed towards a more performant future! 

If you find yourself running into any kinds of issues when using the tool, keep in mind that dbWatch has a support team consisting of top-notch database administrators – they can solve all of the problems you face within no time, so don’t be afraid to reach out and you will receive the help you need very quickly. We hope you enjoyed reading this blog and will stick around – we have a lot more content coming up. 

Track your SQLServer database compatibility level

Two people work to improve their sql compatibility level

 

In this blog,I will discuss SQL Servers’ database compatibility level. By using dbWatch, we will check the proper setting and configure the right compatibility level based on the version you are using. 

 

Each new version of SQL Server introduces new functionalities and performance benefits. These are identified by compatibility level and cardinality estimators.  

1-Dec-05-2020-11-37-39-59-PM

As you can see each database version is associated with a compatibility level, so if you want to utilize the newest features available for your database, you need to check and set the right compatibility level for your instance. 

 

As DBA, you must ensure the compatibility level of each database is matched with your instance current SQLServer version. 

 

The usual problems of enterprises are some take too long to migrate to a newer version of SQLServer. 

 

Some DBAs end up taking two steps. For example –they first migrate the SQL Server 2000 database to SQL Server 2008 R2, and then migrate it to SQL Server 2019. This is the reason NOT to wait too long before migrating a SQL Server database to its latest version.  

 

Sooner or later, your compatibility level will have to change, and this will impact your applications and processes. By doing it sooner, you will reduce the amount of work you will have to do later.

In this view, you can manually check for each databases compatibility level, by opening the database properties window in your SQL Server management studio.

But on a database farm level, especially, when managing hundreds up to thousands of databases, this is not the ideal way to investigate for the compatibility level of your databases.

 

dbWatch Control Center provides you the overview of your database’s compatibility level and displays other attributes of the SQLServer database they are using. 

 

Let us start by clicking on the Farm Management Icon, which is at the left side of your Control Center monitor as seen in the image below. 

 

Expand Inventory Overview Performance. 


Click on the Per platform option and choose MS SQL Server. 

 

In this view displays different attributes, you have the instance status, name, database version, edition, port #, instance name, host, host IP, OS type, the creation date of your database and total DB count 

 With this information you will have a quick understanding of the different versions of SQL server that is running within your database farm. 

 

On top, choose the compatibility tab. 

 On the topmost part of this view, you can see the servers and databases compatibility level.  

 

It has 2 groups: 

  • Databases with lower compatibility than the server 
  • Databases with the same compatibility as the server

As you can see, there are 2 databases that use lower compatibility level than the servers’ version, and 60 databases with the same compatibility level as the server. 

 

On the right side, you have a pie chart that displays the different compatibility levels configured on your databases. We have 2 databases with SQL Server 2014 (120) compatibility level, 50 databases with SQL Server 2016 (130) compatibility level and 10 databases with SQL Server 2019 (150). 

 

At the bottom of the view, displays the instance name, database, status, create date, recovery model, database compatibility, server compatibility, server version. 

 With this information, you will be able to analyze which databases you need to reconfigure and update their assigned compatibility level based on the servers compatibility level.  

 

In the image below, you can see a database farm with a total of 903 SQL Server databases with version SQLServer 2008 R2. This feature is very handy when managing these huge numbers of databases, as it helps you keep track of your databases compatibility level when it comes to the latest available version. 

The change in compatibility level tells the instance to change its feature sets, it might add new features or remove some old features so be wary of the changes that might affect your existing application.  

 

The compatibility report view enables DBAs to find where they have the potential for performance improvements by upgrading the compatibility level of a database which might be a version behind. 

 

 

For more info visit the website www.dbWatch.com 

For more tutorials visit wiki.dbWatch.com/ControlCenter 

Follow us on FacebookLinkedIn, and Twitter - dbWatch 

Also check out our YouTube channel dbWatch