Category: Database
MMS • Raul Salas
Ok, so the latest database platform for Artificial Intelligence is Neo4j (www.neo4j.com), 
a knowledge graph database that provides in-depth analytics and insight into your data. It is a relatively newcomer to the database world and with that comes less than mature functionality that is taken for granted in the relational database industry. Operations such as dropping and restoring databases need to be performed manually across all cluster nodes and the cluster will require a full outage. This means loss of High Availability for a period of time. This is a less than ideal situation, even for development environments, especially in the offshore around the clock development cycles that are the norm in many development environments.
Be prepared that developers will be treating your database environment as disposable as they iterate thru their development cycle troubleshooting the new technology. This usually results in deleting databases as well as restoring databases multiple times a day. The following steps outline deleting and restoring databases that would get an administrator up to speed.
Developers may want to start out with a full new load from an external source such as Kafka and will want you to delete database data so they can restart a batch load from an external source like Kafka or Hadoop.
Delete NEO4j Data
1. make sure you bring down all instances on the cluster before doing any other steps!
cd /neo4j-enterprise-3.5.4/bin
./neo4j stop
2. after all neo instances are down, issue the following commands on each node
cd /neo4j-enterprise-3.5.4/bin
./neo4j stop
./neo4j-admin unbind
rm -rf /neo4j/data/databases/*
Once data directory on all nodes are deleted then startup the cluster nodes
./neo4j status
./neo4j start
tail -f /neo4j/logs/neo4j.log (logs should eventually show the instance coming up waiting for additional cluster members)
You should at this point have a fully blank slate cluster ready for testing!
Developers may want to freeze data with a backup and revert back to it at will. So here are the steps to restore a neo4j database.
RESTORE NEO4J database operations
On each host issue the following commands to refresh
1. make sure you bring down all instances on the cluster before doing any other steps!
cd /neo4j/bin/neo4j-enterprise-3.5.5/bin
./neo4j stop
2. after all neo instances are down, issue the following commands on each node and wait for neo to start up without error before moving to the next node (of course you will need to pre-stage your backup to the restore location below)
cd /neo4j/bin/neo4j-enterprise-3.5.5/bin
./neo4j stop
./neo4j-admin unbind
./neo4j-admin restore –from=/neo4j/data/test_restore/graph.db-backup –database=graph.db3.5 –force
./neo4j status
./neo4j start
tail -f /neo4j/logs/neo4j.log (logs should eventually show the instance coming up waiting for additional cluster members)
3. if for some reason the node does not come up delete the data directory /neo4j/data/databases
and re-execute tasks in step #2 above.
Raul Salas
Raul@mobilemonitoringsolutions.com
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
|
Featured Database ArticlesPosted June 11, 2018 By Rob Gravelle
Back in 2015, I presented a slideshow of the Top 10 MySQL Reporting Tools. Since then, many readers shared their own favorite reporting tools for MySQL. Moreover, the reporting landscape has changed in the past three years. These two developments have informed me that this might just be the right time to revisit this topic. So now, for your viewing pleasure, here are another 8 of the Top Reporting Tools for MySQL. Please enable Javascript in your browser, before you post the comment! Now Javascript is disabled. |
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
In recent years, technology landscape has undergone dramatic changes, driven primarily by cloud computing and a continuously increasing level of attention dedicated to security, privacy, and compliance. As recent revelations related to improper use of consumer data by social network providers and research firms have shown, this attention is well warranted. Similarly, surging numbers of increasingly sophisticated cyber attacks further underscore the importance of proper protection and detection mechanisms guarding individuals and organizations alike. One of the more significant initiatives that attempts to address these challenges is General Data Protection Regulation (GDPR), which is the regulation imposed by the European Union (EU) on May 25, 2018. While its primary purpose is to legislate data protection and privacy within the boundaries of EU, it applies to any business or organization (regardless of their location) that conducts business with the European Economic Area. In this article, we will explore how Azure SQL Database could help with addressing the GDPR requirements. Note however, that you should always consult with a legal professional in order to ensure full compliance.
One of the crucial aspects of GDPR regulations deals with measures that must be implemented in order to facilitate control and protection of personal data. These measures are described in the following sections of the GDPR legislation:
- GDPR article 25 stipulates that data must be protected by design and by default. This includes implementing controls in regard to data access, data collection scope, and data retention.
- GDPR article 30 dictates that all data processing operations must be logged and monitored.
- GDPR article 32 references security (including pseudo-randomization and encryption), resiliency (including recoverability, high availability, and disaster recovery), and fidelity (including regular testing) mechanisms that data storage and processing involve.
- GDPR article 35 describes the requirements regarding performing and documenting risk assessment.
Complying with these measures typically requires significant effort, which translates into additional cost and management overhead. In order to minimize this effort, it is worthwhile considering the use of technologies that incorporate mechanisms that GDPR demands. Azure SQL Database is an example of such technology. Let’s review some of its features that illustrate this point.
To start, in order to properly control and protect customer data, it is critical to be able to properly identify it. The table-based structure of Azure SQL Database yields itself to cursory examination of the data content based on column names. An in-depth analysis can be performed by relying on the Full-Text Search capability of the database engine, which has been part of the Azure SQL Database offering since April 2015.
The first line of defense when controlling access to Azure SQL Databases involves IP address and port filtering on the Azure SQL Server instance level, implemented as firewall rules defined in its master database. The firewall allows inbound traffic from designated IP addresses or address ranges on TCP port 1433 only. By default, all external connections are blocked, so you need to explicitly enable them by specifying the public IP address (or IP address range) assigned to your Internet entry point. You also have the option of controlling network-level access by using firewall rules applied to individual databases (for more information, refer to Microsoft Azure SQL Database Security – Firewall Configuration). In addition, Virtual Network Service Endpoints allow you to restrict inbound traffic to an Azure SQL Database server to specific Azure virtual network subnets that you designate. Even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the designated subnets is routed via the Microsoft backbone network, rather than via public Internet (for more information, refer to Azure SQL Database Virtual Network Service Endpoints and Rules).
The subsequent levels of defenses are facilitated by features in other, security-related areas, such as authentication and authorization, encryption in transit and at rest, as well as data integrity and confidentiality. Azure SQL Database supports two modes of authentication. The traditional one relies on server-level logins with the corresponding user accounts in individual user databases. The recommended approach takes advantage of the newer authentication method, which integrates with Azure Active Directory (Azure AD). With Azure AD authentication, each server instance includes an Azure AD-based administrative account, which corresponds to either an Azure AD user or an Azure AD group. You can use this administrative account to create database-level users. This new approach to authentication provides a number of benefits. The ability to use the same user name and password to obtain access to multiple Azure SQL Database server and database instances eliminates the credential sprawl, automatically improving security and lowering the management overhead associated with user account management. By virtue of integration with Azure AD, you also can implement centralized management of these credentials, either in the cloud (for Azure AD managed accounts) or on-premises (for synchronized and federated accounts originating from on-premises Active Directory). For more information, regarding this topic, refer to Azure SQL Database – Azure AD Authentication
From the authorization standpoint, Azure SQL Database implements granular object model and role-based security, which facilitate implementing the principle of least privilege. This not only helps limit the risk of unintended data exposure but also minimizes the impact of a potential exploit. Data protection is further enhanced by leveraging such features as:
- Transport Layer Security encrypts all network connections to Azure SQL Database. This is a default, enforced functionality, which provides automatic protection of data in transit.
- Dynamic Data Masking limits the visibility of sensitive data stored in relational database tables accessible to non-privileged users. This functionality, supported starting with SQL Server 2016 and present in the current version of Azure SQL Database relies on a custom policy defined on the database level. The policy specifies one or more filters that should be applied to result sets returned in response to user or application initiated queries. This facilitates scenarios where database-resident content should not be fully exposed to database users. This is commonly required when referencing data containing Personally Identifiable Information (PII), such as credit card numbers, or email addresses (for more information, refer to Azure SQL Database – Dynamic Data Masking).
- Row-Level Security implements a predicate-based access control. This means that its underlying mechanism relies on an arbitrary condition to evaluate whether requests to perform specific actions on individual rows of a database table should be granted or denied. These actions are determined based on the type of predicate (for more information, refer to Azure SQL Database – Row-Level Security).
- Transparent Data Encryption encrypts content of the database, its transaction logs, as well as backups while at rest. Encryption and decryption are performed in real-time, at the individual page level, as the database content is being written to and read from storage, without necessitating changes to applications accessing their data. In the case of Azure SQL Database, its performance impact is minimized by relying on the Intel AES-NI hardware acceleration, included as an inherent part of the service (for more information, refer to Azure SQL Database – Transparent Data Encryption).
- Always Encrypted offers end-to-end encryption of customer data by handing control over the encryption process to that customer. The encryption is implemented by a client-side driver, with encryption keys never revealed to the management layer on the Azure SQL Database side. This delivers the highest assurance of data confidentiality, without the need for changes to application code.
- Azure SQL Database auditing tracks database-level and server-level security-related events, allowing you to identify instances of unauthorized changes and access attempts, addressing at the same time regulatory compliance requirements. Logging can be selectively turned on or off based on the event types, which include Data Access, Schema Changes, Data Changes, Security Failures, and Grant/Revoke Permissions (for more information, refer to Azure SQL Database Auditing). In order to track all data changes, you can take advantage of Temporal Tables. Their primary purpose is to provide the full visibility of historical changes of content stored in Azure SQL Database tables. Effectively, you have the ability to identify the state of your data at an arbitrary point in time. This comes in handy in a variety of scenarios, including performing analysis of usage trends of database-bound applications, identifying data lineage, or carrying compliance and regulatory audits. In addition, you have a rather unique ability of granular recovery of individual table entries, without resorting to traditional restore methods. All of these benefits can be realized without the need for programming or database design changes. Existing applications interact with their data sources in the same manner as they would without having temporal tables in place (for more information, refer to Introduction to Azure SQL Database Temporal Tables).
- Azure SQL Database Threat Detection automatically detects and alerts on suspicious database activities, which might be a sign of a cyber attack or a security breach. This functionality relies on machine learning and behavioral analytics technologies. It integrates with Azure Security Center, which additionally provides guidance regarding threat remediation.
- Point-In Time Restore relies on automatic backups to facilitate database restore to an arbitrary point in time (for more information, refer to Azure SQL Database Resiliency – Point-in-Time Restore).
- Active Geo-Replication provides disaster recovery capabilities. It supports up to four read-only secondaries located in arbitrarily chosen Azure regions, which allows you to fail over to any of the four locations. In addition, since each of the replicas operates in read-only mode, you not only can mitigate the impact of datacenter-wide disasters, but you also gain the ability to facilitate online application upgrades or migrations as well as offloading data analysis or reporting tasks from your production instance. (for more information, refer to Azure SQL Database Resiliency – Business Continuity).
This concludes our overview of Azure SQL Database features that could help you with addressing GDRP requirements.
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Every DBA needs to appropriately manage database growth. No DBA wants an application to fail because the database is out of space. A database can run out of space a number of different ways. One of those ways is to set the MAXSIZE of a database. When the database grows to the MAXSIZE the database will not grow anymore and if someone tries to add more rows they will get an error saying the database is full.
There may be legitimate reasons to set the MAXSIZE of a database. But most of the time you will want your databases to always autogrow when they run out of space. If you are new to your SQL Server environment, or inherited a new database server, you should consider reviewing the databases that have their MAXSIZE limit set. This way you can review the appropriateness of setting the MAXSIZE for any databases you identify.
Here is some code that will find all database files that have their max_size set:
-- Database files that have maximum size limit set SELECT db_name(database_id) DatabaseName, type_desc, name, physical_name, size, max_size FROM sys.master_files WHERE -- File will grow until the disk is full max_size <> -1 -- Log file not set to 2TB and (type_desc = 'LOG' and max_size <> 268435456);
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
We all know indexes are important for improving your query performance, but to store and maintain indexes SQL Server requires disk space and compute resources. If you have indexes that are not being used, they are costing you valuable resources and causing your update statements to run longer. Those updates run longer because they have to maintain those unused indexes. If your application is not using an index, then there is no value in having that index.
Periodically you should review your index usage statistics to determine how your indexes are being used. You can do that by using the sys.dm_db_index_usage_stats dynamic management view (DMV). This view will show you if your indexes are being used in a seek, scan, or lookup operation, and whether or not the indexes have been updated. Remember DMVs only track information since SQL Server started. Therefore, you need to consider running this DMV after SQL Server has been up for a reasonable time in order to get an accurate picture of how your indexes have been used.
By using the sys.dm_db_index_usage_stats you can identify those indexes that have never been used. In the following code there are two SELECT statements with a UNION clause between them. The first SELECT statement identifies those indexes that have never been used but have been update in your databases. The second SELECT statement identifies those indexes that have never been used and have not been updated. By “updated” I mean the index has either been maintained due to an INSERT, UPDATE or a DELETE operation. You should consider running this query periodically to identify those indexes that are providing your application no value but are costing you compute and disk space resources. Once you know what indexes are not being used, then you can determine whether or not you even need those indexes.
-- indexes that have been updated and not used select SCHEMA_NAME(o.schema_id) as [schema_name], OBJECT_NAME(s.object_id) table_name, i.name index_name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates, 'yes' Index_updated_but_not_used from sys.dm_db_index_usage_stats s join sys.objects o on s.object_id = o.object_id join sys.indexes i on s.index_id = i.index_id and s.object_id = i.object_id where (s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 UNION -- indexes that have not been updated or used SELECT SCHEMA_NAME(o.schema_id) as [schema_name], OBJECT_NAME(o.object_id) table_name, i.name index_name, 0 as user_seeks, 0 as user_scans, 0 as user_lookups, 0 as user_updates, 'no' as Index_updated_but_not_used FROM sys.indexes i JOIN sys.objects o on i.object_id = o.object_id WHERE i.index_id NOT IN ( SELECT s.index_id FROM sys.dm_db_index_usage_stats s WHERE s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID(DB_NAME())) and OBJECTPROPERTY(o.object_id,'IsUserTable') = 1 order by Index_updated_but_not_used desc;
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Oracle has decided that Solaris needs to retire, giving those who are running Oracle on Solaris roughly two years to move to another operating system. A possible choice involves commodity hardware (x86) and Linux. Since OEL is a ‘ready to use’ version of Red Hat Linux it would be a reasonable presumption that many companies will be choosing it as a Solaris replacement. Available as a full release for a bare-metal system and as a pre-configured VM it’s positioned to be a ‘go-to’ option for migration.
Solaris, on SPARC, is a big-endian operating system, where the most-significant byte is stored last. Linux, on the other hand, is a little-endian system where the bytes are stored in the order of significance (most-significant is stored first). This can make moving to Linux from Solaris a challenge, not for the software installation but for the database migration as the existing datafiles, logfiles and controlfiles can’t be used directly; they must be converted to the proper endian format. Time and space could be important issues with such conversions/migrations as there may be insufficient disk space to contain two copies of the database files, on big-endian and on little-endian. The actual conversion shouldn’t be difficult as RMAN can convert the files from a recent backup. Other considerations include access to the new servers (since they may not be on the production LAN while the build and conversions are taking place) and replicating the current Oracle environment, including scripts, NFS mounts, other shared file systems and utilities, from Solaris to Linux. RMAN isn’t the only method of migration as expdp/impdp can be used to transfer users, tables and data from the source database to its new home; using datapump means the destination database can’t be rolled forward after the import has completed, which can cause a longer outage than the enterprise may want to endure since all user access to the application data must be stopped prior to the export.
Looking at the ‘worst-case scenario’ let’s proceed with the migration using Oracle Enterprise Linux and the datapump transfer of data and tables. (Installing the software should be a fairly simple task so that portion of the migration will not be covered here.) Once the server is running and Oracle is installed the first step in this migration is to create an “empty” Oracle database as the destination. This takes care of the endian issue since the new datafiles are created with the proper endian format. If the Linux server is created with the same file system structure as the source then it’s simply a matter of performing the import and checking the results to verify nothing went awry. Any tnsnames.ora files that applications use for connectivity need to be modified to point to the replacement database server and remote connections need to be tested to verify they do, indeed, work as expected. After successful application testing has completed the database and its new ‘home’ should be ready to replace the original Solaris machines.
It’s possible that the new Linux server will use a different storage configuration; it’s also possible that the DBA team, in an effort to simplify tasks, decides to use Oracle Managed Files. In either case the import may not succeed since datapump can’t create the datafiles. This is when the tablespaces need to be created ahead of the datapump import; using the SQLFILE parameter to datapump import will place all of the DDL into the specified file. Once this file is created it can be edited to change the file locations or to use OMF when creating tablespaces. It’s critical that file sizes are sufficient to contain the data and allow for growth. Datapump will create users if the necessary tablespaces exist so the only DDL that should be necessary to run prior to the import will be the CREATE TABLESPACE statements; all other DDL should be removed prior to running the script. It’s also possible to create a DBCA template to create the destination database by modifying an existing template. Creating the database manually or with a template is a DBA team decision; in the absence of database creation scripts that can be modified it might be a good decision to modify an existing template to minimize errors.
Presuming the storage configuration for the x86 system is different from that on Solaris, and that the file system structure doesn’t match the source server, the tablespace DDL has been extracted, modified and is ready to run. After the tablespaces are in place the import can be run; it is always good practice to make a test run into the new database before the final import is executed, to verify that the process runs smoothly. The final import should put the new database in ‘proper working order’ for the applications that use it so all insert/update/delete activity must be stopped prior to the export. This will ensure data consistency across the tables.
One area that may be an issue is application account passwords in the new database. It’s a good idea to verify that the passwords from the source database work in the new database. If not, they can be reset to the current values; end-users and batch jobs will be happier if logins are successful.
Connectivity to remote systems is also critical, so any tnsnames.ora files that are in use on the source system need to be copied to the destination system and database links need to be tested. This may involve the security and system administration teams to open ports, set firewall rules and ensure that any software packages not included with the operating system are installed. There should be no surprises once the new server and database are brought online.
Other choices, such as Unified Auditing or Database Vault, that require an Oracle kernel relink need to be discussed and decided upon before the destination database is created. Re-linking the Oracle kernel before any database exists reduces overall downtime for the migration.
No document can cover every conceivable issue or problem that may arise so it’s possible the first pass at moving from Solaris to x86 may reveal issues that weren’t obvious at the beginning of this process. It may also take more than one pass to “iron out” all of the “kinks” to get a smoothly running process. The time and effort expended to ensure a smoothly running migration will pay off handsomely when the day finally arrives.
Moving from Solaris to x86 (or any other operating system) may not be at the top of your wish list, but it will become a necessity when Solaris is no longer supported. Getting a start on this now will provide the needed time to fix any issues the migration may suffer so that when the fateful weekend arrives the DBA team is ready to provide a (mostly) painless move.
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Introduction
Adaptive query processing is the latest, improved query processing feature introduced in the SQL Server database engine. This method is available in SQL Server (starting with SQL Server 2017 (14.x)) and Azure SQL Database.
Query performance is always a subject of research and innovation. Every so often, Microsoft includes new feature and functionality in SQL Server to improve query performance to help their users. In SQL Server 2016, Microsoft introduced Query Store. Query Store monitors query performance and provides methods to choose better query plans for execution. Query store is a feature to persist query execution plans and analyze the history of query plans to identify the queries that can cause performance bottlenecks. Any point in time these plans can be reviewed, and the user can use plan forcing to enforce the query processor to select and use a specific query plan for execution. You can read more about Query Store in Monitor Query Performance Using Query Store in SQL Server.
In SQL Server 2017, Microsoft went one step further and worked on improving the query plan quality. This improved query plan quality is Adaptive Query Processing.
Query Execution Plan
Prior to SQL Server 2017, query processing was a uniform process with a certain set of steps. The SQL Server query optimizer first generates a set of feasible query plans for a query or batch of T-SQL code submitted by the database engine to query optimizer. Based on the cost of the query plan, optimizer selects the lowest-cost query plan.
Once the query execution plan is ready and available to execute, SQL Server storage engines get the query plan and execute it based on the actual query plan and return the data set.
This end to end process (query plan generation, and submitting the plan for processing) once started, has to run to completion. Please see Understanding a SQL Server Query Execution Plan for more information.
In this process, query performance depends on the overall quality of the query plan, join order, operation order and selection of physical algorithm like nested loop join or hash join, etc. Also, cardinality estimates play a vital role to decide the quality of the query plan. Cardinality represents the estimated number of rows returned on execution of a query.
The accuracy of cardinality drives a better quality of query execution plan and more accurate allocation of memory, CPU to the query. This improvement leads to a better and improved execution of query, known as adaptive query processing. We will discuss exactly how SQL Server achieves this in rest of the section,.
Adaptive Query Processing
Prior to SQL Server 2017, the behavior of Query Optimizer was not optimal; it bound to select the lowest cost query plan for execution, despite poor initial assumptions in estimating inaccurate cardinality, which led to bad query performance.
In Adaptive Query Processing, SQL Server optimized the query optimizer to generate more accurate and quality query plans with more accurate cardinality. Adapting Query Processing gets a better-informed query plan. There are three new techniques for adapting to application workload characteristics.
Adapting Query Processing Techniques
- Batch Mode Memory Grant Feedback. This technique helps in allocating the required memory to fit in all returning rows. The right allocation of memory helps in, 1) reducing excessive memory grants that avoid concurrency issues and, 2) fixing underestimated memory grants that avoid expensive spills to disk.
Query performance suffers when memory allocation sizes are not sized correctly. In Batch Mode Memory Grant Feedback, optimizer repeats the workload and recalculates the actual memory required for a query and then updates the grant value for the cached plan. When an identical query statement is executed, the query uses the revised memory grant size and improves the query performance.
- Batch Mode Adaptive Join. This technique provides the choice of algorithm selection and the query plan dynamically switches to a better join strategy during plan execution. This defers either hash join or nested loop join selection until after first input has been scanned.
The new operator in the family, Adaptive Join operator helps in defining a threshold. This threshold is used to compare with the row count of the build join input; if the row count of the build join input is less than the threshold, a nested loop join would be better than a hash join and query plan switches to nested loop join. Otherwise, without any switches query plan continues with a hash join.
This technique mainly helps the workload with frequent fluctuations between small and large join input scans.
- Interleaved Execution. Interleaved execution is applicable to multi-statement table valued functions (MSTVFs) in SQL Server 2017 because MSTVFs have a fixed cardinality guess of “100” in SQL Server 2014 and SQL Server 2016, and an estimate of “1” for earlier versions, which leads to passing through inaccurate cardinality estimates to the query plan. In Interleaved execution technique, whenever SQL Server identifies MSTVFs during the optimization process, it pauses the optimization, executes the applicable subtree first, gets accurate cardinality estimates and then resumes optimization for rest of the operations. This technique helps in getting actual row counts from MSTVFs instead of fixed cardinality estimates to make plan optimizations downstream from MSTFs references; this results in improvement in workload performance. Also, Interleaved execution enables plans to adapt based on the revised cardinality estimates as MSTVFs changes the unidirectional boundary of single-query execution between the optimization and execution phase.
Additional facts for Adaptive Query Processing Techniques
There are certain facts associated with each Adaptive Query Processing technique while optimizer improves the query performance using these techniques.
Batch Mode Memory Grant Feedback. Feedback is not persisted once a plan is evicted from cache. You will not be able to get the history of the query plan in Query Store, as Memory Grant Feedback will change only the cached plan and changes are currently not captured in the Query Store. Also, in case of failover, feedback will be lost.
Batch Mode Adaptive Join. SQL Server can dynamically switch between the two types of joins and always choose between nested loop and hash join; merge join is not currently part of the adaptive join. Also, adaptive joins introduce a higher memory requirement than an indexed nested loop Join equivalent plan.
Interleaved Execution. There are some restrictions; 1) the MSTVFs must be read-only, 2) the MSTVFs cannot be used inside of a CROSS APPLY operation, 3) the MSTVFs are not eligible for interleaved execution if they do not use runtime constants.
Configure Adapting Query Processing
There are multiple options to configure Adaptive Query Processing. These options are as follows:
Option-1: SQL Server by default enables Adaptive Query Processing and executes the queries under compatibility level 140.
Option-2: T-SQL is available to enable and disable all three Adaptive Query Processing techniques:
T-SQL to enable Adaptive Query Processing techniques
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;
T-SQL to disable Adaptive Query Processing techniques
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;
-
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;
Option-3:Enforce Adaptive Query Processing technique using query hint. Query hint takes precedence over a database scoped configuration and other settings.
-
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
-
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS'));
-
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));
Summary
The adaptive query processing improves the quality of a query plan. This helps in selecting the right join, right order of operation and more accurate memory allocation to fit all rows. The three techniques of adaptive query processing make SQL Server 2017 significantly faster at processing the workload. Also, adaptive query processing provides significant improvements without refactoring T-SQL code.
Overall, adaptive query processing is a great addition in SQL Server and enables SQL Server to generate well informed quality query plans to manage application workload processing.
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Sometimes it’s desired to move data from production to test or development environments, and if done with the original exp/imp utilities, issues can arise since these were written for database versions older than 9.x. Those utilities don’t support features found in newer database versions, which can create performance problems.
Tables with Top-n or hybrid histograms, when exported with exp, won’t get those histograms replicated to the destination database; both Top-n and hybrid histograms will be converted to Frequency histograms. Looking at a table in 12.1.0.2 (from an example by Jonathan Lewis) let’s see what histograms are present:
COLUMN_NAME Distinct HISTOGRAM Buckets
-------------------- ------------ --------------- ----------
FREQUENCY 100 FREQUENCY 100
TOP_N 100 TOP-FREQUENCY 95
HYBRID 100 HYBRID 50
Using legacy exp the table is exported. Importing this into another 12.1.0.2 database using legacy imp the histogram types have changed:
COLUMN_NAME Distinct HISTOGRAM Buckets
-------------------- ------------ --------------- ----------
FREQUENCY 100 FREQUENCY 100
TOP_N 100 FREQUENCY 95
HYBRID 100 FREQUENCY 50
Note that the Oracle release is the same in both databases; it’s the exp/imp utilities creating the problem. Using datapump to transfer the data would have preserved the histograms. If there are scripts in use that use these old utilities it’s probably time to rewrite them to take advantage of datapump export and import.
It’s sometimes easier to use what’s already written but in the case of conventional export and import it’s time to retire these scripts when using Oracle releases that support datapump.
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Configuring Navicat Monitor on Windows 10
One of the hottest trends in database administration is agentless monitoring. Tools that employ an agentless architecture collect performance metrics from your database server(s) without requiring the installation of any agent software on the server(s). From the perspective of database administrators (DBAs), the agentless approach is a lot easier to manage than agent-based monitoring because you don’t need to deploy agent software on every server and can monitor your server(s) remotely. The latest player to enter the database monitoring arena is Navicat, whose line of database administration tools for MySQL, MariaDB, SQL Server, SQLite, Oracle and PostgreSQL are widely regarded as industry leaders. Today’s article will provide one of the first looks at this promising new tool by exploring some of its many features as well as how to configure it, with an emphasis on Windows 10.
Requirements
Navicat Monitor monitors your MySQL, MariaDB and Percona servers, and collects metrics at regular intervals using an agentless architecture. It’s also compatible with a variety of cloud databases like Amazon RDS, Amazon Aurora, Oracle Cloud, Google Cloud, Microsoft Azure and Alibaba Cloud. The Monitoring software is installed on a local computer, server, or virtual machine and then collects a number of process metrics such as CPU load, RAM usage, and a variety of other resources over SSH/SNMP.
Navicat Monitor employs a dedicated database to store alerts and metrics for historical analyses. The repository database can be any existing MySQL, MariaDB, PostgreSQL, or Amazon RDS instance.
Supported Operating Systems (OSes)
Navicat Monitor will run on much the same OSes as their other products. As such, there are installation packages available for Windows, macOS, and Linux. Here are all of the specific supported OS editions:
- Microsoft Windows:
- Windows 8
- Windows 8.1
- Windows 10
- Server 2008
- Server 2012
- Server 2016
- macOS:
- Mac OS X 10.10 Yosemite
- Mac OS X 10.11 El Capitan
- macOS 10.12 Sierra
- macOS 10.13 High Sierra
- Linux:
- Red Hat Enterprise Linux 6.6 or later
- CentOS 6.6 or later
- Oracle Linux 6.6 or later
- Fedora 20 or later
- Debian 8.0 or later
- SuSE Linux Enterprise 12 or later
- openSUSE 42.3
- Ubuntu 14.04 LTS
- 16.04 LTS or 17.10
- Amazon Linux 2
Supported Browsers
Navicat Monitor is a server-based software, which can be accessed from anywhere via a web browser. With Web access, you can easily and seamlessly keep track of your servers from anywhere in the world, around the clock.
You should consider using one of the following browsers before any other. Of course, other browsers will probably work as well, but these have been tested and are recommended by Navicat:
- Firefox (Latest Version)
- Chrome (Latest Version)
- Internet Explorer 11 or later
- Microsoft Edge 39 or later
- Safari 9.1.3 or later
Features at a Glance
We’ve already established that Navicat Monitor employs an agentless architecture and can be installed on any workstation/device OR server running one of the main three (Windows, macOS, Linux) OSes. There are two ways to configure the Navicat software:
- You can install the Navicat Monitor software directly on your workstation/device and then access it locally via your preferred browser.
- You can also install the Navicat software on a server, which would allow you to access the monitor tools from any device that can communicate with your server.
Now let’s take a bird’s eye view of Navicat Monitor’s other pertinent features. Some are integral to any professional monitoring tool, while others go above and beyond, offering additional value in one complete package.
-
Real-time Performance Monitoring
Real-time data monitoring (RTDM) is a process through which an administrator can review and evaluate the overall database processes and functions performed on the data in real time, i.e., as it happens, through graphical charts on a central interface/dashboard. Navicat Monitor includes both a set of real-time and historical graphs that allow DBAs to drill down into the details of server statistics. It provides a fine-grained view of server load and performance metrics relating to availability, disk usage, network I/O, table locks and more. Moreover, by showing side-by-side statistics for each server, DBAs can easily spot and track deviations in performance and traffic among servers. Using the metrics provided by Navicat’s interactive dashboard, DBAs can determine possible remedies and adjust server settings accordingly.
Real-time Performance Monitoring -
Alerts
Even the most dedicated DBA can’t be watching server performance 24/7. Even if it was feasible, that wouldn’t be a an enviable existence! That’s where database alerts come in. An alert is raised when a threshold value for a System Monitor counter has been reached. For example, you could create an alert that is raised when the number of deadlocks exceeds a specific value. In response to the alert, Navicat Monitor’s built-in Alert feature notifies you before bigger problems arise to ensure your databases are constantly available and performing in optimal capacity. Notifications can be sent via email, SMS or SNMP whenever a warning or critical condition occurs in your infrastructure.
After an alert has been raised, Navicat Monitor provides advanced root cause analysis that enables you to drill down and find more in-depth information when an issue is found – server metrics, performance statistics, hardware usage, and historical data. You can also monitor your alerts in the Alert Details screen, which provides an overview of the selected alerts that comprises its summary, timeline, metric charts, and more.
Navicat Monitor Alerts - Query Analyzer
The query analyzer monitors queries in real time to quickly improve the performance and efficiency of your server. It shows the summary information of all executing queries and lets you easily uncover problematic queries, for instance:
- identifying top queries with cumulative execution time count
- slow queries with unacceptable response time
- detecting deadlocks when two or more queries permanently block each other
Query Analyzer - Reporting
In addition to collect metrics of multiple instances on the same chart to compare and analyze data, Navicat Monitor can also generate professional reports at lightning speed. You can also save your report as a high-quality PDF document via the PDF button.
Reporting - Replication
Monitoring a replication topology is an important aspect of deploying replication. Because replication activity is distributed, it is essential to track activity and status across all computers involved in replication.
Using Navicat Monitor, you can apply schematic diagrams to visually represent the relationship between master servers and slaves. You can monitor the overall health of a replication topology as a whole, each individual node, and each replication subsystem to make sure the data on the replicated servers is always up to date.
Monitored Replications - Manage Users and Roles
Navicat Monitor allows the creation of local users, or external users using LDAP or AD authentication, along with their access rights and restrictions by assigning roles to users. You can customize the pre-defined roles to best fit your needs or create new roles with customized privileges settings to restrict access to selected pages in Navicat Monitor.
Manage Users and Roles
- Instances Management
Organize your servers into groups to make it easier for users to find them by category. Assign users to specific groups as members and apply the same alert configuration settings to them with minimal effort. All members will get notified when an alert is raised.
Instances Management - Export Monitor Settings
Exporting Navicat Monitor settings can be useful if you want to migrate an application server or for backup purposes. The exported zip file includes Token Keys, Instance Settings, Repository Database Settings and all the files necessary for the restoration.
Export Monitor Settings
MMS • RSS
Article originally posted on Database Journal – Daily Database Management & Administration News and Tutorials. Visit Database Journal – Daily Database Management & Administration News and Tutorials
Zero-latency HTAP
As big data applications matured one thorny performance issue remained: the data in the appliance was huge, and required time-consuming data load processes. For some customers it became like a large data warehouse, providing analytics against day-old data. Other customers attempted to load the appliance as soon as operational data was available. Still, there was a time delay while loading applications executed. Also, at times several loads were required to several tables to maintain data consistency. For example, one couldn’t load new orders into the Order table until the corresponding customers were loaded into the Customer table.
The latest versions of IBM’s Db2 version 12 and IDAA can now be configured to allow concurrent transactional and analytical processing. IBM’s new patented concurrent replication protocol is a “just-in-time” method of propagating operational Db2 table changes to the IDAA. The result is that transaction data in the operational application systems is up-to-date for business analytics requests executed against data in the appliance.
The result is a hybrid of both transactional and analytical processing.
With these new features (and more) of their flagship relational database management system, IBM has redefined the infrastructure of enterprise information technology to better align with digital business, cloud computing and mobile applications. New access path management methods such as dynamic SQL plan stability allow for more efficient capacity planning while reducing the risks of rogue SQL statements running for extended periods while locking data and consuming large amounts of CPU time. Finally, synergy with advances in IBM z14 hardware such as taking advantage of the hardware acceleration of cryptographic processing and data compression means that Db2 on IBM Z will continue to provide industry-leading scalability, availability and performance.