The Positive Impact of Intelligent Query Processing

by Contributed | Jan 26, 2021 | Technology

This article is contributed. See the original author and article here.

Don’t fall off a cliff!

The positive impacts of Azure SQL and SQL Server’s Intelligent QP

The scenario above describes what I call “Falling off a cliff”.  Your database is doing fine.  Great even.  Then something changes, and performance, response time, everything, changes in a blink of an eye.  All the time, databases are changing – data is being inserted, loaded, archived, updated…etc.  Most often, the number of rows in an active database is steadily increasing.  Sometimes, the number of rows passes a threshold – and the way the queries are executed changes.  In the general case, this is good.  You want your database to adapt to changing data loads and query loads.  Unfortunately for the developer in the story above, the change was not smooth, and did not yield better overall workload performance, in fact, things got very bad very quickly. 

In this blog, I will attempt to explain, at a high level, why Query Processing (QP) matters, how it works, what can go wrong when QP makes a mistake, and what exciting features are present in the Azure SQL and SQL Server QP that will improve end-user experience, and ultimately, allow you to use resources smarter.  Your developers will be able to get work done faster and with fewer middle of the night emergencies.

Why Good QP Matters

Typically, QP is silent and unseen.  Not thinking about QP means things are going well.  It’s only when you see a sudden degradation in performance, or when queries stop completing, that you must think about QP.  While every database requires some planning and setup, once that is complete you want business to move forward without investing substantial time fixing issues or making tweaks.  To this goal, having a good quality QP is essential.

A good QP:

1. Expands the capability of the database


2. Uses resources effectively


3. Reduces manual intervention


4. Adapts to changing conditions

Expands the capability of the database: When a query doesn’t undergo a good optimization process, it may never complete (or will take too long to complete to be reasonable), but, with good optimization, the same query can complete quickly.  This means that a good optimizer allows queries that may be impossible, or require extensive re-writing, or even need multiple execution steps in an un-optimized environment. 

Uses resources effectively: When an optimizer is able to grant the correct amount of resources to a query, parallelization can occur.  If a query is given too many resources, it could block other queries from executing simultaneously.  If given too few, it could execute too slowly. 

Reduces manual intervention: Oftentimes, when a query runs too slowly (or doesn’t complete), someone need to go do some work, or tweaks, etc. to fix the problem.  Not only is manual intervention tedious, it can be error prone and may not adapt well to changing conditions inside the database.  Further, sometimes these tweaks can slow down other operations.

Adapts to changing conditions: As table sizes change, or as plan parameters change, the query plan will often automatically adjust to these new conditions.  These changes reflect the most optimal plan for the query given the database as it is now.  If QP only used the very first plan it created for a query, it would be blind to the changes in data or plan parameters, and thus may executions would be far from optimal. 

Figure 1: Plan space diagram showing different plans

You can see in Figure 1 a diagram called a “plan space” diagram.  As the number of rows selected by a query from various tables increases, the plan changes.  The diagram in figure 1 is just a cartoon, with the intent of showing what the Plan Space for a given query might be, as the number of rows being processed increases.  In this diagram, each colored area represents a different plan for the same query, as the number of rows being processed changes.  The plan space for this imaginary query and tables contains 7 plans, each of which is ideal based on the number of rows being processed. 

The general trend with plan spaces is that there are more plans when the number of rows is smaller, and fewer plans when the number of rows gets very large.  The reason for this trend is that QP has a limited set of tools – some work better for large data sets, some for smaller.  When the number of rows is small, tools can be combined and balanced to create different plans.  However, when the data size becomes large enough, the QP chooses all of the best operators for large data.  For example, nested loops join is a join technique that is well-suited to smaller data sets.  Hash join is preferred for processing larger numbers of rows.  It is easy to keep making the data size larger, but once the QP is choosing hash join (and other operational equivalents for large data), the options for further improvement are limited.
 

What a good QP does

But how does a good QP even find this “plan space” and execute queries?  First, the QP needs to determine how it is going to execute a query, then it needs to execute efficiently. 

Upon receipt of a query, the QP first determines how the query will be executed.  The result of this step is a query plan.   A few of the steps involved are:

Reorganizing the query:  Some operators can be moved around to occur at different times during execution for faster results, but with no difference in the result set (semantically equivalent).

Choosing specific execution strategies: With knowledge about the various operators and by using statistics about the data, the optimizer can choose, for example, the way a join will be executed, considering memory usage and the estimated size of the data.

To do these things, may underlying pieces play a part, the details of which I will skip here.  A few examples would be: selecting good indexes to operate on and estimating the data size for each step of execution.   In Azure SQL and SQL Server, you can optimize a query without executing it.  This way you can see the query plan without waiting for execution to complete.  Once the QP creates a plan for a query, that plan can be executed multiple times.  Since these plans adjust based on the estimated size of the data, it makes sense that there are a number of different plans that could be selected for the same query.

Query execution involves manipulating the rows from the source tables through the query and into the result set.  Query execution accesses the indexes, performs the joins, filters the rows according to the query plan, and ultimately provides as output the qualifying rows from the query.

What can go wrong?

Sometimes, however, query processing makes reasonable, understandable choices that simply don’t work once the query starts to execute.  This is typically due to lack of complete information, or due to the necessary assumptions that the must be made: finding a plan works well in the general case, even if it doesn’t work well in a specific case.

At the highest level, either query processing has chosen one or more operators incorrectly based on the actual size of the data (plan generation relies on estimates only), or it has requested the wrong amount of memory for the plan to execute with.  Both problems come from the generalizations and assumptions that are necessary for planning a query without having executed it. 

Figure 2: Plan space diagram showing transitions between plans 

Another thing that can go wrong is when plans change gracelessly.  As you can see if Figure 2, we should take note of the transition points between plans.  In some cases, users experience a performance change at those points.  I often refer to it as going off a cliff.  You are chugging along with a decent plan, the data size changes, and boom! Now your query takes a lot longer, or a lot more memory to complete. 

Intelligent QP to the Rescue

Azure SQL and SQL Server have released a number of improvements that improve upon this standard workflow in an attempt to lessen or correct common errors. 

Briefly, Intelligent QP can:

1. Remember past query execution’s memory consumption and allocate the proper amount in future executions using memory grant feedback (1, 2).


2. Adjust on the fly when a join operator turns out to be the wrong one for the job using Adaptive Join.


3. Postpone decision making during optimization while it gathers more information by using both Interleaved execution and Table variable deferred computation.


4. Invest less time and fewer resources optimizing when the exact answer is not needed with Approximate QP.


5. Use Scalar UDF inlining replace function calls with the body of the function, thus allowing for more optimization. 


6. Execute things in batches, even when data is not compressed, using Batch mode on row store.

Another way of looking at these elements of Intelligent QP is that they are designed to:

• Learn (1) – Behavior is modified based on past performance.
  
  


• Adapt (2, 3) – Instead of generating a plan and executing it with no changes, these improvements allow for on-the-fly adjustments so that queries execute more smoothly.
  
  


• Enhance Efficiency (4, 5, 6) – New capabilities allowing the QP to execute in the most efficient way possible.  These new techniques are designed to improve the efficiency of plan execution.

These features of intelligent QP have one goal in mind:  Better plans, fewer surprises.  By learning, adjusting, and adapting to the changing context of the database, intelligent QP means that:

• DBAs don’t have to spend so much time on manual adjustment and workarounds for poor query performance.


• Queries, on average, execute faster.  Faster queries save time and machine hours.


• A small team can be successful with Azure SQL’s QP without hiring a full time DBA.


• Fewer customer incidents when plans go south unexpectedly.

In this way, Intelligent QP reduces the likelihood of sudden, abrupt plan regressions, so you can walk smoothly along the heights without worrying about falling off a cliff.

Sync Up – a OneDrive podcast : Episode 17, “Consolidating OneDrive and SharePoint admin centers”

by Contributed | Jan 26, 2021 | Technology

This article is contributed. See the original author and article here.

Sync Up is your monthly podcast hosted by the OneDrive team taking you behind the scenes of OneDrive, shedding light on how OneDrive connects you to all your files in Microsoft 365 so you can share and work together from anywhere. You will hear from experts behind the design and development of OneDrive, as well as customers and Microsoft MVPs. Each episode will also give you news and announcements, special topics of discussion, and best practices for your OneDrive experience.

So, get your ears ready and Subscribe to Sync up podcast!

Migrate your database from PostgreSQL Single Server to PostgreSQL Flexible Server using Azure DMS

by Contributed | Jan 26, 2021 | Technology

This article is contributed. See the original author and article here.

Overview:

Azure Database for PostgreSQL recently announced Flexible Server,  Flexible Server is a new deployment option for Azure Database for PostgreSQL that gives you the control you need with multiple configuration parameters for fine-grained database tuning along with a simpler developer experience to accelerate end-to-end deployment. With Flexible Server, you will also have a new way to optimize cost with stop/start capabilities. 

In this article, we will provide a guideline on how to migrate your database from PostgreSQL Single Server to PostgreSQL Flexible Server using Azure DMS.

Important – Azure Database for PostgreSQL – Flexible Server is still in preview as of Jan 2021.

1 – Prepare the Source – Azure PostgreSQL – Single Server

You will need to set some Postgresql Parameters on the source as follows:

— parameter wal_level should be logical, on Azure PgSQL this can be done from Azure Portal as shown blow:

— max_replication_slots = [number of slots], recommend setting to 5 slots
— max_wal_senders =[number of concurrent tasks] – The max_wal_senders parameter sets the number of concurrent tasks that can run, recommend setting to 10 tasks

2- Prepare the target for the migration – Flexible Server

You will need to migrate database schema from source to target using commands:

— on the source, create a schema dump file for a database

pg_dump -o -h hostname -U db_username -d db_name -s > your_schema.sql

— Import the schema into the target database

psql -h hostname -U db_username -d db_name < your_schema.sql

— Remove foreign keys in schema at target Azure Database for PostgreSQL

SELECT Q.table_name
    ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' DROP CONSTRAINT ', foreignkey), ','), ';') as DropQuery
        ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' ADD CONSTRAINT ', foreignkey, ' FOREIGN KEY (', column_name, ')', ' REFERENCES ', foreign_table_schema, '.', foreign_table_name, '(', foreign_column_name, ')' ), ','), ';') as AddQuery
FROM
    (SELECT
    S.table_schema,
    S.foreignkey,
    S.table_name,
    STRING_AGG(DISTINCT S.column_name, ',') AS column_name,
    S.foreign_table_schema,
    S.foreign_table_name,
    STRING_AGG(DISTINCT S.foreign_column_name, ',') AS foreign_column_name
FROM
    (SELECT DISTINCT
    tc.table_schema,
    tc.constraint_name AS foreignkey,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY'
    ) S
    GROUP BY S.table_schema, S.foreignkey, S.table_name, S.foreign_table_schema, S.foreign_table_name
    ) Q
    GROUP BY Q.table_schema, Q.table_name;

— Disable triggers at target Azure Database for PostgreSQL

SELECT DISTINCT CONCAT('ALTER TABLE ', event_object_schema, '.', event_object_table, ' DISABLE TRIGGER ', trigger_name, ';')
FROM information_schema.triggers

— Provision Database Migration Service and create a migration task

More details in Tutorial: Migrate PostgreSQL to Azure Database for PostgreSQL online via the Azure CLI – Azure Database Migration Service | Microsoft Docs

3 – Create Migration Project

You will need to use your Postgresql as source and provide the single server endpoint and credentials 

Note:- You will need to make sure the connectivity between the source single server and DMS service on side and the DMS service and the target flexible server another side.

Details steps: Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online via the Azure portal – Azure Database Migration Service | Microsoft Docs

4- Monitoring the migration project progress and you can perform the migration once it’s ready for cutover

Note:- After migration is complete, you might need to re-enable the triggers and create foreign keys. 

I hope you find this article helpful. If you have any feedback please do not hesitate to provide it in the comment section below.

Ahmed S. Mazrouh

Windows Update Baseline joins the Security Compliance Toolkit

by Contributed | Jan 26, 2021 | Technology

This article is contributed. See the original author and article here.

We are excited to announce the Update Baseline is now a part of the Security Compliance Toolkit! The Update Baseline is a new security baseline to ensure devices on your network get the latest Windows security updates on time while also providing a great end user experience through the update process.  

The Update Baseline covers Windows Update policies as well as some additional Power and Delivery Optimization policies to improve the update process and ensure devices stay secure. 

Why do I need the Update Baseline? 

We recommend using the Update Baseline to improve your patch compliance and keep devices on your network up to date and secure. The Update Baseline is Microsoft’s set of recommended policy configurations for Windows Updates to ensure devices on your network receive the monthly security update in a timely manner. Devices that are configured for the Update Baseline reach on average a compliance rate between 80-90% within 28 days. 

What is included in the Update Baseline? 

For Windows Update policies, the Update Baseline ensures: 

• Setting deadlines. Deadlines are the most powerful tool in the IT administrator’s arsenal for ensuring devices get updated on time. 


• Downloading and installing updates in the background without disturbing end users. This also removes bottlenecks from the update process. 


• A great end user experience. Users don’t have to approve updates, but they get notified when an update requires a restart. 


• Accommodating low activity devices (which tend to be some of the hardest to update) to ensure the best-possible user experience while respecting compliance goals. 

Learn more about common policy configuration mistakes for managing Windows updates and what you can do to avoid them to improve update adoption and provide a great user experience. 

How do I apply the Update Baseline? 

If you manage your devices via Group Policy, you can apply the Update Baseline using the familiar Security Compliance Toolkit framework. With a single PowerShell command, the Update Baseline Group Policy Object (GPO) can be loaded into Group Policy Management Center (GPMC).  

The MSFT Windows Update GPO that implements the Update Baseline is added to GPMC with a single command. 

You will then be able to view the Update Baseline GPO (MSFT Windows Update) in GPMC. 

That’s it! It’s that simple. 

Other cool tidbits? The Update Baseline will continue to be updated and improved as needed, and a Microsoft Endpoint Manager solution to apply the Update Baseline is coming soon! Let us know your thoughts and leave a comment below.