Analyzing SQL Server With New Performance Tools

oracle performance check

Analyzing database performance on Microsoft SQL Server is quite a complex issue. In dbWatch Control Center, our primary aim is first to provide structure and toolsets to avoid running into performance problems in the first place.

Consistency in deploying preventative maintenance routines should take care of many problems that occur over time, such as index and table fragmentation. Bad statistics are a foundation that will ensure that we don’t waste time investigating preventable causes.

 

As most DBAs will know, unexpected performance issues can occur even with the best foundation, and we will need to be able to investigate the underlying causes.

 

For this reason, dbWatch Control Center has a lot of monitoring jobs focused on monitoring the behavior of different performance metrics. At its core, all performance issues occur when resource limits are met. It can be the CPU, disk, memory, or internal database limits.

 

The first we need to do to start our investigation is to find the time window where the performance issues occurred. The typical problem is that one or more SQL statements cause an overload of the database resources in the time period in which they were running. In this blog, we will briefly investigate performance issues due to SQL statements.

 

In the SQL Performance section in the management module of dbWatch Control Center, you can look at historical resource usage, together with the SQL statements that were performed in that time period.

 

Picture1-1

In this section, we can see spikes in logical/physical reads and writes. This often, but not always, coincide with performance loads. We can also look at other metrics in the same time frame, such as Time, the elapsed time for all queries in a time frame, which could indicate slow query response. The Waits could indicate spikes where the database is waiting for specific resources. CPU, where we can see any spike in CPU load. Data cache where we can see if there was a fight for memory resources and other graphs focusing on metrics that could be relevant.

 

 

Once a peak has been identified, you can use the mouse to zoom in on the time period. Picture2

The SQL performance dashboard will then zoom in, and looking at the SQL handle statistics, it’s easy to see what SQL handle is responsible for this peak.

Picture3

You can then right-click and select show statistics, to open the SQL handle and view the SQL code, statistics, and when the SQL statement is inside that SQL handle.

Picture4

If we here are interested in the SQL statement in this SQL handle that is causing the most logical reads, we can quickly identify it in the list and right-click to show this SQL statement or open the query plan in SQL Server Management Studio for further investigation.

Picture5

If we choose the “Show SQL statement and statistics,” we can open the SQL statement.

Picture6

In this case, it was the fetch statement in the procedure. We can see in the SQL code for the procedure that this is the select_cur which is defined as:

 

/*cursors*/

DECLARE select_cur CURSOR FOR

SELECT * FROM dbw_test_tab

DECLARE @col1 INT, @col2 VARCHAR(1000)

 

This allows us quickly to see what is going on, in this case, a select statement from a performance load routine, so nothing is out of what we expected.

If this was interesting, we go more in-depth on the SQL performance dashboard on the wiki page, wiki.dbwatch.com (https://wiki.dbwatch.com/ControlCenter/1.0/en/topic/using-sql-performance-package)

 

With dbWatch Control Center, SQLServer DBAs can now monitor their resource usage trend and analyze the peak hours in their database environment based on available charts.   

 

dbWatch Control Center assists SQLServer DBAs in efficiently running their databases, from proactive database performance monitoring to managing SQLServer tasks.  

 

Control Center also helps SQLServer DBAs play back their historical performance to drill down and pinpoint problematic queries during the past few weeks. 

 

Control Center offers the following:  

 

  • Farm overview – consolidated views for performance, capacity, and maintenance for your entire database farm 
  • Monitor in-depth all your instances and platforms 
  • Performance – identify performance bottlenecks within the farm 
  • Scalability – ability to adapt to your growing database farm 
  • Alerting – alerting and third-party extensions tailored to your business requirements 
  • Security – role-based access controls, Active Directory, and Kerberos integration encrypts connections and support security certificates 

 

You can try dbWatch Control Center today. Use this link to download a version of Control Center that can monitor up to 5 instances for free:  Download dbWatch Control Center  

 

If you have any questions or you would like to know more about how dbWatch Control Center can assist you in your current enterprise database monitoring and database management situation, feel free to contact me directly:  per.christopher@dbwatch.com     

 

For more information, visit www.dbWatch.com or the dbWatch wiki pages   

 

 

 

 

Running Databases: Docker vs. Alternatives

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?

pexels-soumil-kumar-735911

 

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.

Understand more about database management, book a demo of dbWatch today.

SQL Server Uptime: When to Restart Your SQL Server Instances

A pair of glasses in front of a screen showing SQL Server Uptime

If you are a database-savvy developer or even a database administrator, you probably know how important it is to always keep an eye out on your database servers. Some database servers might need more of your attention and upkeep than others, of course, but after all, even if they are facing some sorts of issues, none of them should be neglected – when your database instances are facing issues, one of the things you might want to keep an eye out on is SQL Server uptime. Sometimes your database instances might also need to be restarted.

An out of focus screen calculating sql-server uptime.

As easy as it sounds, the restarting of your database instances might be a pretty tedious task: you might need to shut down all of the running processes, keep an eye out on your database schemas, and also keep an eye out on their uptime – thankfully,  we have tools that can help us with that. 

dbWatch for Improving SQL Server Uptime

dbWatch, for example, is a fully-fledged database management solution letting you keep an eye on all kinds of database instances: from Oracle and Sybase to PostgreSQL. However, what we’re interested in the most is the capabilities dbWatch provides for Microsoft SQL server (MSSQL) to successfully run. If you’re using SQL Server Express, you will be able to make use of dbWatch as well! 

Open up dbWatch and import a SQL Server instance (in this example we will use SQL Server 2017) – you will see a bunch of “Folders.”) Those folders house categories of database jobs – database jobs that can help you monitor your database availability, capacity, replication capabilities, maintenance, or performance: 

Screen shot showing Database server uptime in dbWatch.

In this case, you might have noticed that we marked a job called “Database Server uptime” – check what it says and you will notice that it depicts the amount of time your SQL Server has been running: 

Message about how long instances run.

When to Restart SQL Server Instances

Seeing this shouldn’t be surprising – however, when do you decide when it’s time to restart your SQL Server instances? In that kind of scenario, we would need to take care of a few aspects. We should think about: 

  1. Do we really need to restart our SQL Server instances in the first place? Perhaps we could do without a restart and just change a couple of queries around here and there? 
  2. When we should go about restarting our SQL Server instances (i.e. what kind of time of restart would be most beneficial for both us and not interrupt the work for our clients, employees, etc.?) 
  3. How we should restart our database (SQL Server) instances? Manually? Should we make use of the help of certain tools (e.g. dbWatch, etc.)? How should we use such tools to our advantage? 

Solve SQL Server Issues

Answering these three questions shouldn’t be too difficult – however, what might be more difficult is the fact that,even if you restart your database (in this case, SQL Server) instances while aiming to solve some kind of an issue you are facing, you might find that you might have the need to keep an eye on a couple of different things related to your database instances ranging from availability to maintenance at the same time as well. How do you do that?

How Monitoring Can Help You

Probably, you should use software that is up for the task, such as dbWatch. For example, expand the Performance section of database jobs in dbWatch and you will see a couple of different jobs that will be suitable to improve your database performance – dbWatch in this case can monitor your databases for high activity (which might be useful to decide when to restart your database instances – you should do so at the lowest point of usage to not disrupt anybody using your services): 

Message about activity monitor.

dbWatch can also provide you with the statistics of the usage of your indexes inside of a certain database instance, etc.: 

Menu from dbWatch about performance.

Deciding your Next Steps

However, that’s only when performance is related. dbWatch also has other similar MS SQL database jobs (simply expand the categories of Availability, Capacity, and the like), and it can also show you a bunch of really colourful statistics to help you decide what should the next step be for your database instances (simply click Monitor -> Management to observe the following screen): 

Screen shot showing instance information regarding SQL instances.

This output might also be useful when deciding when to restart your SQL Server database instances. You should restart your database instances once they are at the lowest point of usage (for that, you could make use of the logical reads per second and the sessions graphs.) See how everything starts to work together so nicely? If you ever need any support, the dbWatch team is always here for you too – ping them and they will get back to you to help you solve your database issues sooner than you think. 

Conclusion on Improving SQL Server Uptime with Restarts

We hope that this blog post has been a little of an eye-opener of what restarting SQL Server and other database instances usually comes down.

 

Interested in trying dbWatch to help with SQL Server management?  Book a demo today.

 

 

4 Ways Database Management Tools Reduce IT Costs

graph on computer showing how to keep database it costs down

For many organizations today, spiraling IT costs are a major concern. From the cost of operating large server farms, to staffing and maintenance costs, IT budgets are growing exponentially in many industries. Now it’s becoming more important to reduce database management costs.

 

To help businesses to help mitigate some of the costs of working with large server farms, and to help their staff be more efficient, several solutions now exist for monitoring and managing databases. From cutting down on unnecessary bills to making DBA teams more productive and streamlining workflows, these tools are built from the ground up for efficiency, scalability and use by large enterprises.

Here, we’ll cover four ways that these solutions can keep your IT costs in check and improve efficiency.

1. Fewer Serious Incidents

Disasters and serious incidents represent a very high financial cost for businesses, both in the time spent planning and the cost of restoring services and maintaining security.

 

For many IT teams, a lack of oversight over their systems means that emerging incidents aren’t discovered until it’s too late. By making use of database monitoring solutions, DBAs can utilise proactive monitoring and analytics to detect and correct problems before they become serious.

 

Monitoring tools can also be used to discover security weaknesses, to identify struggling infrastructure, and to institute regular preventative maintenance across your servers and instances.

2. Lower Licensing Costs

Many software providers offer several forms of license for organisations to choose from, with an enterprise license usually being the most expensive and offering access to the full range of software features. Quite often, however, DBAs won’t use or need the full suite of features that enterprise licenses unlock. Despite that, many businesses still pay for enterprise licenses which they don’t need, either by accident or because their needs have changed and they lack the time, tools or expertise to detect and correct the situation.

 

Database management tools help to solve this problem. By analysing actual usage and reporting on which software and features are being used versus licensing level, DBAs can leverage management tools to track and adjust their licensing levels.

 

In the case of Oracle, in particular, many DBAs overstep the standard license terms inadvertently or by accident. Oracle always supplies its full suite of tools to DBAs regardless of license, and later demanding more money if they discover via audit that a company used tools or features that they hadn’t been licensed for – something easily done when changing just one option can trigger full and costly enterprise licenses.

3. Optimised Performance

The performance of your IT systems is vital to your business, not just because it provides your customers and staff with a smooth service but because it saves you money too. Poorly performing servers can lead to slowdowns at work, potentially disrupting services to customers which could cost your organisation valuable business and hurt your reputation.

 

With the help of database management tools, DBAs can optimise the performance of your organisation’s IT systems, improving your setup to ensure that it constantly runs smoothly.

4. Be More Productive

DBA productivity is one of the key areas that can be improved by any enterprise looking to cut costs and improve operational expenses. With the right solutions, DBAs can get access to the analytics, insight and monitoring that they need to perform their jobs efficiently and to allocate their time where it’s most needed.

 

Many database management tools offer streamlined interfaces that allow DBAs easy access to all the information that they need and the ability to monitor multiple instances at once. Tools like this also combine the features of many other individual server tools, saving time for DBAs and reducing the amount of time spent training to use new software.

 

How to reduce IT and Database Management Costs

Navigating the complexities of escalating IT and database expenditures causes concern for many organizations. The financial burdens associated with maintaining extensive server infrastructures, along with personnel and upkeep expenses, are on an upward trajectory.

 

To combat these rising costs, a variety of database monitoring and management technologies have emerged, designed to enhance operational efficiency and scalability while curbing  spending and amplifying the effectiveness of database administration teams.

 

This discussion delved into four pivotal approaches:

  • Deploying preemptive monitoring to avert critical system failures
  • Optimizing license expenditures by aligning software usage with actual needs
  • Enhancing the efficiency of IT infrastructure to avoid performance-related disruptions
  • Improving the productivity of database administrators through sophisticated tools.

 

These strategies collectively contribute to a more streamlined operational workflow, significantly reducing the financial strain associated with IT and database management and offering a sustainable competitive advantage.

 

Learn how dbWatch can help you gain a better operational workflow, book a demo today.

Analyze Oracle Performance With New Performance Tools