SQL Optimizer for SQL Server: Deleting Old Backup Files

Creating and maintaining backups are an essential part of a DBA’s responsibilities. There are several tasks associated with backups, such as automation and execution of backup command scripts.

Sometimes, it is important to know how to automate the deletion of outdated backup files as well, even if you already have a SQL optimizer for SQL Server. In this post, we will discuss a simple approach to help you remove older backup data and save space.

How to Delete Backup Files without SQL Optimizer for SQL Server

Here, we will make use of Windows Scripting to traverse every subfolder in order to locate files preceding a specific date. We will then delete the older files, once they have all been located.

First, we need to consider two parameters that must be modified for this purpose:

 

●       iDaysOld - this helps you set the exact timeframe to determine how old the file has to be for the command to select and delete it

●       strPath - this is the path to the folder where the backup files are created and stored.

 

Steps to establish the script before you get to SQL Server performance tuning:

1. You need to create a text file where you can copy and paste the following code -

iDaysOld = 15

strPath = “C:\Backup”

Set objFSO = CreateObject("Scripting.FileSystemObject") 

Set objFolder = objFSO.GetFolder(strPath) 

Set colSubfolders = objFolder.Subfolders 

Set colFiles = objFolder.Files 

 

For Each objFile in colFiles 

   If obj!File.Date!LastModified < (Date() - iDays!Old) Then 

       Msg!Box "Dir: " & obj!Folder.Name & vbCrLf & "File: " & objFile.Name!

       'obj!File.Delete! 

   End If 

Next 

 

For Each objSubfolder in colSubfolders 

   Set colFiles = objSubfolder.Files 

   For Each objFile in colFiles 

       If obj!File.Date!LastModified < (Date!() - iDaysOld) Then 

           Msg!Box "Dir: " & obj!Subfolder.Name & vb!CrLf & "File: " & obj!File.Name

           'objFile.Delete 

       End If 

   Next 

Next

Use the path of your choice as strPath - for instance, “strPath = “C:\Backup””. Remove the exclamation marks before or after you paste this code.

 

2. Save it using a suitable name like this - C:\RemoveBackupFilesOld.vbs

 

3. Create one more text file in which you will copy-paste the code mentioned below, and save it as a BAT file in the same location. For instance, you can name it as - C:\RemoveBackupFilesOld.bat.

The code: C:\RemoveBackupFilesOld.vbs

 

Please note that this script serves as a safeguard, so it will only show a message box that displays the name of the file and folder. If you’re using an optimization of SQL queries SQL optimizer for SQL Server and you want to actually delete the files, simply remove the single quote character that’s present right next to the two delete lines (the ones with ‘objFile.Delete).

 

4. Running the BAT file will delete all the files matching the specified criteria from their subfolders.

 

How the Script Works

 

The script will pick out and eliminate any files found in the subfolders beyond the initial point. The type of the files doesn’t matter as it will only consider the time of creation and whether it fulfils the specified criteria.

It will also remove files from subfolders in the next level as well as the root folder but it won’t fetch files past the first subfolder level. In other words, if you put the strPath as “C:\Backup” for instance, the script will remove backup files older than the iDaysOld that are present in the “C:\Backup” folder and those present in the first level subfolders inside this folder.

You may set this script up to run at a scheduled time according to your need - just call the BAT file. This is because, as you may have learned during SQL Server performance tuning, the Agent is not accustomed to running .vbs (VBScript) files directly, which is why we’re calling a BAT to set it up as a scheduled task instead.

SQL Performance Monitoring & 6 Other Top Basic DBA Skills

As time passes, human resources are rapidly getting replaced by computing resources, affecting the task range of an Oracle DBA. You may have observed the consolidation of Oracle instances onto a bigger server with a greater number of CPUs. Due to this, fewer DBA professionals are needed by organizations. 

At the same time, a DBAs list of tasks keeps increasing since the same set of activities needs to be completed by fewer staff. From schema design handling to SQL performance monitoring, there are many essential database management duties assigned to surviving DBAs. On the bright side, Oracle DBAs no longer have to perform tedious tasks such as implementing patches on several servers and perpetually re-assigning server resources. re-allocating server resources, and t
uning many servers.

6 Most Important Skills for a Database Administrator

Since a professional Oracle DBA is generally brought into crucial IT department undertakings, authorities may often give preference to professionals with a wider range of both Oracle-based and non-Oracle-based job skills.

While the applicants usually have all the Oracle-based skills gained in academic Information Technology and Computer Science courses, they may or may not have some of the Non-Oracle job skills, including the following:

• Database Design – Numerous projects need an understanding of concepts such as data modeling methods, database normalization, and STAR schema.

• System Analysis – Oracle specialists often end up playing an important part in designing and analyzing new DBMSs. Therefore, they should be aware of concepts such as DFDs (data flow diagrams), CASE tools, methodologies in entity-relation shaping, and data dictionary techniques.

• 
  Change Control Management – Aside from knowing how to performance tune SQL query, the candidate should know how to implement change control as they are likely to be handed over that responsibility. They also must be able to make sure those changes are sufficiently carried out to the production database aside from knowing about the third-party change control resources such as the UNIX Source Code Control System (SCCS).

• Physical Disk Storing – They need to have knowledge of RAID, cache controllers, disk hardware architecture, and disk load balancing.

• Backup and Recovery – Since a majority of projects use third-party backup and recovery technologies, the DBA requires proper experience implementing these methods.

• 
  
  Data Security skills – Excellent knowledge of MySQL SQL performance tuning and how to implement database security measures in RDBMS, especially role-based security is quite useful.

Conclusion

Many Oracle users and specialists wrongly believe that a database administrator in Oracle only needs to have technical skills such as how to performance tune SQL query. However, the Oracle DBA needs to have a much broader range of skills as they not only have the responsibility of the design but also various other elements of the database such as implementation, storage, and data recovery. 

Therefore, they require good technical and communication skills to handle all the different aspects of database management in the best way possible and keep operations streamlined among all these areas. 

Understanding Active Cache for Optimization of SQL Queries

Many Database Administrators have noticed that Memcache brings a few issues with it. One, it is passive as it shares cached data every time - meaning applications that utilize Memcache require special logic to take care of anything missing from the cache.

Also, one needs to be careful while updating the cache as several data manipulations could be taking place simultaneously. Another thing to keep in mind in such cases is the rise in latency when it comes to constructing cache expired items. This is especially true during optimization of SQL queries when they could just as easily been recovered in the background.

All of the situations and issues mentioned above can be resolved with the help of an active cache. It is an extremely simple concept: for each data fetching the operation, the cache will actually be able to construct the object as it will know how to do so.

This way, you won’t ever get a miss unless there is a different problem altogether. This kind of solution is the best there is at present, particularly when you need to register the jobs with Gearman

Here, the updates regarding data are supposed to pass through the same process to enable the user to fain serialization or whichever logic you prefer for the updates. The user may also apply the same functions to update the information once it expires. It may be exposed as explicit logic for something that may expire in a few hundred seconds and begin anew in another few hundred seconds along with being automated.

The automatic handling logic can be explained like this: once the key expires, we can place it in cache with an indicator that shows it’s expired, after we purge its value. If you want to perform Oracle query performance tuning and can view for the same key, you will receive plenty of requests once its expired cache is enabled to decide whether it will refresh leys like these on the basis of available bandwidth.

Experienced database experts have also suggested an extension to typical caching techniques - specifying max_age for GET requests. For several apps, request-driven expiration is the norm rather than data-driven expiration.

For example, if you’re the user posting comments at the end of a blog, you’ll want to read those comments first to avoid a negative experience. Therefore, if other users are able to read stale information, for instance, if they get to read every comment ten seconds after it’s posted, they won’t have an unpleasant user experience.

Active caches can also be a great help when it comes to optimization in SQL queries and controlling write-back scenarios. Several cases have been detected, where plenty of updates are being carried out on the data like logging in, counters, scoring, and so on, don’t really need to show in the database at that same moment.

If the cache is programmed to make changes to the information on its own, the user could easily define the policies on the number of times the information object syncing must take place with the database. This concept will surely prove helpful in other ways as well - in some cases, even more so than present tools and technologies.

 

 

 

Improve Performance of SQL Query by Avoiding Swapping

Every so often, you may have MySQL or another application take up excessive amounts of memory on the operating system or the box. As a result, the OS may behave abnormally - such anomalous behavior is often visible in the form of increased memory utilization for cache and forcing swapping among applications. 

In other words, such unrestrained usage of memory inevitably leads to swapping and performance-related issues. The intensity of those issues is another cause for concern, as swapping is likely to affect your performance harder than the average IO, leading you to search for ways to improve performance of SQL query, even MySQL altogether.

Handle Swapping Before You Improve MySQL Performance of SQL Query

In this post, we’re going to find three reasons why swapping creates much more significant problems for performance than a normal IO.

Reason 1 - Multiplication of IO Due to Cache

In comparison to the presence of reduced cache, IO will be multiplied once cache in the swap file increases. If the page inside the cache gets swapped, the user will need to locate space in order to swap in the page, meaning other pages will have to be swapped out.

Such a task needs to be carried out, even if it is completed in the background. On the other hand, flushing or getting rid of the page will lead to additional IO, thereby worsening the situation by slowing performance tuning in SQL Oracle.

Reason 2 - Getting the Algorithms all Mixed Up

There are certain algorithms running internally that are designed for data inside the memory. If they begin handling data stored on disk, their productivity gets impacted.  That’s why a different combination of algorithms is used to deal with disk data, and these are optimized to restrict the quantity of IOs or turn them into something more sequential.

Reason 3 - Increased Latches or Locks

Interruptions within the internal operations of a database are bad enough without swapping wreaking havoc upon concurrent processing like multi-client or CPU. Bear in mind that a database latch or lock is generally created to work for extremely short time frames. How short? Well, as short as you can possibly keep them.

That’s because the system will surely scale better when fewer exclusive locks are occupying the execution time thread. So, you must absolutely avoid any critical locks during disk IO because they take a considerable amount of time as is.

In Conclusion

Users are advised to configure the system in a manner that keeps any form of swapping activity away while normal operations are being carried out, to ultimately help improve MySQL database performance.

Additionally, make sure you are able to justify every instance that uses a swap file. Instances, where it is acceptable, include unanticipated spikes in memory utilization, where slower performance may be preferable over none. 

 

 

Oracle Database and SQL: A Brief Introduction to Oracle

 

Oracle is a multi-model RDBMS or a Relational Database Management System. It was essentially created for grid computing in enterprises as well as data warehousing.

Oracle is among the primary choices for companies looking for economical solutions for data handling and the applications they use. In fact, Oracle database and SQL is among the most common combinations used in enterprises.

Features of Oracle Database and SQL

Oracle and SQL are two words often referred to at the same time. So, what is the difference between Oracle and SQL? Simply put, Oracle is the Relational database management system, whereas SQL or Structured Query Language is the compatible query language used to interact with the database.

Take a look at some of the prominent features of Oracle:

 

●       Flexibility and Productivity: Oracle offers functions such as portability and Real Application Clustering that result in a greater ability to adjust as per the organization’s usage. For databases involving several users, Oracle also enables data consistency and the advantage of carrying out tasks from multiple users at the same time.

 

●       Accessibility: Many of its applications operate instantaneously, therefore needing constant data accessibility. Computing environments that provide high performance are also designed to enable data availability throughout the day. Data can be accessed even when there are scheduled or unexpected downtimes and lapses.

 

●       Backup and Retrieval: Oracle has been created with data loss in mind, which is why it has several recovery features to help retrieve lost data in nearly every type of data failure. If an unfavourable event leads to failure, the database will have to be retrieved as quickly as you can to ensure high availability.

In Oracle, all those parts of data that have not been affected by the issue are made accessible immediately, while the affected ones are being retrieved.

 

●       Data Protection: This is one of the most important aspects of any database management system. Oracle offers methods to secure data access and manage its utilization. Enforcing authorization and modifying user actions can keep the information safe from unauthorized access and provide access to only the right users.

What is the Oracle Database Used For? Know its Importance

Oracle was created by one of the oldest companies to offer DBMSs, an organization with a constant focus on the fulfillment of the data-based requirements of other organizations. This is why its products come packed with updated features to meet the growing needs and expectations of database administrators.

Now that we’ve discussed some of the most important features of Oracle, let’s know-how they make Oracle an option worth choosing over its competitors. Let’s take a look at some of its major perks:

 

●       Excellent Performance: It has various mechanisms and concepts that ensure high performance. Users can conduct performance tuning in their organizational database to further improve the speed and efficiency of data retrieval and manipulation.

●       Several Databases: This RDBMS facilitates the management of several database instances on the same server. This is done through the Instance Caging method that handles CPU resources being allocated to the server operating the database instances. This technique works alongside the database resource manager to take care of numerous services across multiple instances as well.

●       Real Application Clusters: The use of Real Application Clusters guarantees a robust system with data that is always accessible. Here are some major benefits of an RDBMS with RAC over conventional databases:

●       Adjusts the the scale of the database through several instances

●       Maintains a balance of loads

●       Reduces data redundancy and improves availability

●       Adaptable to fluctuating processing capacities

●       Restoration in the Event of Failure: The recovery manager (RMAN) is designed to recover and retrieve database files when the database is affected by a power shortage or another type of impediment. It works in multiple ways: online, by archiving backups and performing regular backups. Users also use other tools that work excellently for Oracle, such as SQL* PLUS (which is a type of user-managed recovery).

●       PL/SQL: Oracle database and SQL support the PL/SQL extension for methodological programming.

To Conclude

Oracle is certainly one of the most powerful RDBMSs that caters to the requirements of applications at both small as well as enterprise levels. This database server management software contains all the functions needed to assist and accommodate modern applications, which is why it is widely used even today.

 

Optimization of SQL Queries: Cons of Multiple Storage Engines

Among the big advantages advertised for MySQL includes support for several storage engines. This seems like an excellent advantage at first glance because users gain the advantage of using the same high-level SQL interface, enabling them to save their information in numerous ways.   

Though it may seem quite useful, this advantage comes at a high expense in terms of performance, growth, and operational intricacy. In this post, we will discuss just a few challenges of this particular feature.  

Cons of Multiple Storage Engines in Oracle\MySQL Databases 

Consider the following setbacks due to the support provided for multiple storage engines in Oracle queries - 

• Conversions – Every storage engine contains its unique format for storing data, so MySQL performance tuning requires plenty of copying and conversions when it needs to access the information. This has a substantial impact on performance in comparison with a “zero-copy” design, where users may stream information from memory when it can be accommodated there. Optimizer and Execution Storage engines are not created equal especially if you look at In-Memory storage engines vs Distributed ones. 

• Managing many different instances in query optimizer - Certain tasks and cases like clustering by The primary key in Innodb, the memory-based characteristics of the MEMORY storage engine and the distributed nature of NDB tend to get more complicated than necessary. This is due to the need to cater to several use cases that keep MySQL from exploiting the proper performance potential of either of the storage engines. 

• Integration and Synchronization - The upper-most level at the MySQL side that includes .frm and binary log files require synchronization with storage engine transactions. This, in turn, takes up significant performance overhead and gets rather complicated. Also, despite MySQL doing numerous fsync() calls for each transaction commit just to remain on the safe side, many tasks still aren’t completely safe. For example, .frm files can be retrieved from the internal data dictionary in case of a crash at an inopportune moment. 

• Transaction Assistance - There are several combinations of locks that users need to manage on higher levels and inside storage engines. They also have to deal with varying locking strategies owned by storage engines, which can lead to plenty of surprises if they choose to venture into the cross storage engine transactions.

• Backup - Backing up data across storage engines can be frustrating due to their differences. While some of these storage engines are distributed, others use the memory, which means even versatile approaches such as LVM backup could fail in some situations.  

Query Optimization & Multiple Storage Engines - Final Thoughts

It is interesting to note that a single storage engine is more than sufficient for over ninety-five percent of applications that use SQL tuning for Oracle. Moreover, most users already prefer to use only one storage engine instead of mixing multiple of them because of the hindrances mentioned above. 

At the same time, this doesn’t mean users should give up flexibility altogether. For instance, administrators can enable InnoDB tables that don’t log changes, which will increase the rate of update operations. They may also lock those tables in memory to make sure the in-memory performance doesn’t get unpredictable.

Delayed Flushing & Its Role in MySQL SQL Performance Tuning

Fact: delayed flushing may actually lead to reduced load. It may sound hard to believe, but if you ask an expert about the reasons behind systems delaying flushing changes to durable storage, you might get one or both of these answers:

1. In order to complete the task at a more convenient time
2. To have less work overall

In this post, we will examine the second reason and whether it helps MySQL SQL performance tuning.

Higher Peak & Average IO Rates with Adaptive Flush

The total number of I/O operations performed definitely improves with adaptive flushing. What happens here is, the workload gets smoothed out by adaptive flushing, which results in more work getting done.

Why does this happen? As you might recall, InnoDB works on 16kb pages at a time. Suppose one of the users manipulates the data of one of the rows. If InnoDB flushes constantly, it will flush the whole 16kb page right then. Just after this flush, a different row undergoes changes on the same page, which causes one more page flush.

If the first flush were to be delayed, the two flushes would be performed as a single flush. Delaying a flush to perform along with other flushes as one flush is known as write combining in MySQL database and SQL.

In certain workloads, the same rows may end up getting updated numerous times, which is where delaying and allowing write combining may dramatically reduce the quantity of I/O operations.

Does Recovery Time Affect this Result?

Some of us might think that recovery time is a significant factor for MySQL SQL performance tuning, and that enabling this option is not really necessary if that isn’t the case. However, it actually depends on the workload, and if each flush takes up a resource that is also required by another task.

If the data is able to fit in the buffer pool, and no read operations are taking place at that moment - which means the disk isn’t required for any other operations - the disk would only be in use for write operations. Also, if everything is ready at the OS and RAID controller layers, a read operation won’t be needed for a write either, helping Oracle database performance tuning.

An important point to remember is that other than a mutex contention inside MySQL or InnoDB, write operations typically run in the background - so they don’t block foreground tasks.