by Contributed | Jan 27, 2021 | Technology
This article is contributed. See the original author and article here.
The ADX Web Explorer results grid is not just our way to display your query results, but it is a powerful data exploration tool set, that allows you to create various views of your data and quickly draw insights.
Become more productive today! Read this article to learn how it works.
In this article you’ll learn how to use the following result grid productivity tools:
- Expand cell and JSON format
- Expand row
- Column grouping
- Column filters
- Cell to query filter
- Pivot mode
- Cell statistics
Get set, ready, go!
Expand Cell
When dealing with long strings or dynamic fields such a JSON, cell expand functionality comes in handy.
Double clicking a cell opens an expanded view that allows you easily read long strings, and provides a JSON formating for dynamic data.
You can choose between 3 reading pane modes for expanded view, inline (like in above picture), right pane, and bottom pane. Use the icon on the top right of the result grid to switch reading pane modes.
The expanded mode is dynamic, allowing you to expand and collapse array items, and supports full in cell search. to search in a dynamic cell, expand it and hit ctrl+f to search it’s contents (in the image below expanded view is opened in right pane mode):
Expand Row
When approaching a table with dozens of columns it can save you time to expand the entire row for a single glance overview of the different columns and their content. I can assure you it beats endless scrolling to the right.
Click on the arrow (>) on the left of every row to expand it.
Expand row view is optimal when used with right reading pane mode.
Column grouping
Column level grouping allows you to run a quick on-the-fly analysis on your query results by grouping the results table by distinct values of the selected column.
To use column grouping mouse-over a column, select the menu, and select Group by <column name>.
The table will be grouped by distinct values of the selected column. Double-click a group or click on the arrow (>) on the right of each group to expand the grouping to display related records.
This feature is great for exploratory analysis.
To reset the grid to its original state mouse-over the Group column, then select Reset columns.
Column filters
The ADX column filter feature introduces a powerful set of multi condition filters to allow you create complex filter logic on your pre-rendered results.
To use column filters mouse-over a column, select the menu, and then select the filter icon.
In the filter builder select the desired operator:
Now type in the expression you’re wish to filter the column on (note that the filter is not case sensitive)
To create a multi-condition filter select a boolean operator to add additional condition
Results are filtered as you type in.
To remove the filter simply delete the text from your first filter condition.
Cell content to query filter
Another easy way to filter the grid, is to add a filter operator to the query directly from the grid.
Select a cell with content you wish to create a query filter for.
Right click to open the cell actions menu. Select Add selection as filter
A query clause will be added to your query in the query editor:
Pivot mode
Pivot mode feature is somewhat similar to Excel’s pivot table, enabling you to do advanced analysis in the grid itself.
Pivoting allows you to take a columns values and turn them into columns. For example you can pivot on State to make columns for Florida, Missouri, Alabama, etc.
Pivoting only makes sense when mixed with aggregation. If you turn a column into a pivot column, you must have at least one aggregation (value) active for the configuration to make sense. For example, if pivoting by State, you must provide something you are measuring such as ‘DamageProperty’ per state.
To enable it click on the Columns menu on the right
Now select Pivot Mode and drag and drop columns into the appropriate fields below:
- Row Groups – allows grouping by different columns.
- Values – aggregated data (supports SUM operator).
- Column labels – allows turning column values into columns and enables another level of grouping.
The result should look like the following pivot table;
Sum of all damage to property grouped by event type and displayed per country:
Search in results
Search in results enables you to look for a specific expression within the entire results table.
Click on the Search button on the right and type in the expression you’re looking for.
All mentions of your searched expression are now highlighted in the table. You can navigate between them by clicking Enter to go forward or Shift+Enter to go backward, or you can use the up and down buttons next to the search box.
Cell statistics
Cell statistics helps you to run quick calculations on numeric data in your grid.
Calculations such as MIN, MAX, AVG, SUM and COUNT.
To use this feature, select a few numerical cells. The grid allows you to select multiple rows, columns, and cells.
The calculated statistics will appear on bottom left of the grid.
Want to learn more about the ADX Web Explorer and have a hands on experience?
Use this guide to learn more.
by Contributed | Jan 27, 2021 | Technology
This article is contributed. See the original author and article here.
Azure’s AI portfolio has options for every developer and data scientist, and we’re committed to empowering you to develop applications and machine learning models on your terms. Azure enables you to develop in your preferred language, environment, and machine learning framework, and allows you to deploy anywhere – to the cloud, on-premises, or the edge. We help improve your productivity regardless of your skill level, with code-first and low code/no code options which can help you accelerate the development process. We’re also devoted to empowering you with resources to help you get started with Azure AI and machine learning, grow your skills, and start building impactful solutions.
Announcing new AI & ML resource pages for developers and data scientists
Today we’re excited to announce new resources pages on Azure.com, with a rich set of content for data scientists and developers. Whether you’re new to AI and ML, or new to Azure, the videos, tutorials, and other content on these pages will help you get started.
- Learn how your peers around the world are using Azure AI to develop AI and machine learning solutions on their terms to solve business challenges.
- Grow your skills with curated learning journeys to help your skill up on Azure AI and Machine Learning in 30 days. Each learning journey has videos, tutorials, and hands-on exercises to help prepare you to pass a Microsoft certification in just 4 weeks. Upon completing the learning journey, you’ll be eligible to receive 50% off a Microsoft Certification exam.
- Engage with our engineering teams and stay up to date with the latest innovations on our AI Tech Community, where you’ll find blogs, discussion forums, and more.
Pictured above: ML learning journey for developers and data scientists.
Register for the Azure AI Hackathon
Finally, put your skills to the test by entering the Azure AI Hackathon, which starts today and will run through March 22nd, 2021. Winners will be announced in early April. The most innovative and impactful projects will win prizes up to $10,000 USD. We look forward to seeing what you build with Azure AI.
Get started today
Check out the pages to get started with your 30-day learning journey, and register for the hackathon:
by Contributed | Jan 27, 2021 | Technology
This article is contributed. See the original author and article here.
In this video, we look at how to use Cloud Partner Portal CPP API for managing “Virtual Machine” offers in Azure Marketplace.
The document called “Cloud Partner Portal API Reference” describes seemingly older API that existed with the previous version of the publisher portal called Cloud Partner Portal or CPP.
If you had used CPP in the past, you know that in 2019–2020 all offers were migrated to the new Microsoft Partner Center. Therefore, we might expect that all offer types should now be manageable via some new API exposed by the Partner Center. However, as the note in the doc above says, “Cloud Partner Portal APIs are integrated with and will continue working with Partner Center” for many of the offer types including Virtual Machines. While, the newer Partner Center Ingestion API only supports “Azure Application” (i.e. solution template and managed app) offer types (see this related article).
In this video walkthrough, we look at how to use Postman to invoke a few REST methods of the Cloud Partner Portal CPP API to view and update a Virtual Machine offer and see the changes reflect in the Partner Center UI.
You can download my Postman collection here.
Note: This video is specifically about the “Virtual Machine” offer type. If you are looking at how to manage “Azure Application” offers, please see “Using Partner Center Ingestion API for managing Azure Application offers in Azure Marketplace”.
Video Walkthrough
Tip: Play the video full screen to see all of the details.
So, should we use Partner Center API or Cloud Partner Portal CPP API?
Answer: It depends on the “offer type”
- VM Offers (and a few other types): cloudpartner.azure.com (described in this article)
- Azure Application Offers: api.partner.microsoft.com/ingestion/v1 (described in the related article)
Originally published at https://arsenvlad.medium.com/using-cloud-partner-portal-cpp-api-for-managing-virtual-machine-offers-in-azure-marketplace-90c3787c21c4 on September 10, 2020.
by Contributed | Jan 27, 2021 | Technology
This article is contributed. See the original author and article here.
First published on MSDN on Jan 01, 2013
The Memory Consumer with Many Names
Have you ever wondered what Memory grants are? What about QE Reservations ? And Query Execution Memory ? Workspace memory ? How about Memory Reservations ?
As with most things in life, complex concepts often reduce to a simple one: all these names refer to the same memory consumer in SQL Server: memory allocated during query execution for Sort and Hash operations (bulk copy and index creation fit into the same category but a lot less common).
Allow me to provide some larger context: during its lifetime a query may request memory from different “buckets” or clerks, depending on what it needs to do. For example, when a query is parsed and compiled initially, it will consume compile or optimizer memory. Once the query is compiled that memory is released and the resulting query plan needs to be stored in cache. For that, the plan will consume procedure cache memory and will stay in that cache until server is restarted or memory pressure occurs. At that point, the query is ready for execution. If the query happens to be doing any sort operations or hash match (join or aggregates), then it will first reserve and later use part or all of the reserved memory to store sorted results or hash buckets. These memory operations during the execution of a query are what all these many names refer to.
Terminology and Troubleshooting Tools
Let’s review the different terms that you may encounter referring to this memory consumer. Again, all these describe concepts that relate to the same memory allocations:
Query Execution Memory (QE Memory): This term is used to highlight the fact that sort/hash memory is used during the execution of a query and is the largest memory consumption that may come from a query during execution.
Update(9/17): QE Memory is the very type of memory that Resource Governor actually limits, when used. See Resource Pools Max and Min Memory percent
Query Execution (QE) Reservations or Memory Reservations: When a query needs memory for sort/hash operations, during execution it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ that keep track of these memory allocations (check out DBCC MEMORYSTATUS or sys.dm_os_memory_clerks).
Memory Grants: When SQL Server grants the requested memory to an executing query it is said that a memory grant has occurred. There is a Perfmon counter that keeps track of how many queries have been granted the requested memory: Memory Grants Outstanding . Another counter shows how many queries have requested sort/hash memory and have to wait for it because the Query Execution memory has run out (QE Reservation memory clerk has given all of it away): Memory Grants Pending . These two only display the count of memory grants and do not account for size. That is, one query alone could have consumed say 4 GB of memory to perform a sort, but that will not be reflected in either of these.
To view individual requests, and the memory size they have requested and have been granted, you can query the sys.dm_exec_query_memory_grants DMV. This shows information about currently executing queries, not historically.
In addition, you can capture the Actual Query Execution plan and find an XML element called <Query plan> which will contain an attribute showing the size of the memory grant (KB) as in the following example:
<QueryPlan DegreeOfParallelism=”8″ MemoryGrant =”2009216″
Another DMV- sys.dm_exec_requests – contains a column granted_query_memory which reports the size in 8 KB pages. For example a value of 1000 would mean 1000 * 8 KB , or 8000 KB of memory granted.
Workspace Memory: This is yet another term that describes the same memory. Often you will see this in the Perfmon counter Granted Workspace Memory (KB) which reflects the overall amount of memory currently used for sort/hash operations in KB. The Maximum Workspace Memory (KB) accounts for the maximum amount of workspace memory ever used since the start of the SQL Server. In my opinion, the term Workspace Memory is a legacy one used to describe this memory allocator in SQL Server 7.0 and 2000 and was later superseded by the memory clerks terminology after SQL Server 2005.
Resource Semaphore: To add more complications to this concept, SQL Server uses a thread synchronization object called a semaphore to keep track of how much memory has been granted. The idea is this: if SQL Server runs out of workspace memory/QE memory, then instead of failing the query execution with an out-of-memory error, it will cause the query to wait for memory. In this context, the Memory Grants Pending Perfmon counter makes sense. And so do wait_time_ms , granted_memory_kb = NULL, timeout_sec in sys.dm_exec_query_memory_grants . BTW, this and compile memory are the only places in SQL Server where a query will actually be made to wait for memory if it is not available; in all other cases, the query will fail outright with a 701 error – out of memory.
There is a Wait type in SQL Server that shows that a query is waiting for a memory grant – RESOURCE_SEMAPHORE. As the documentation states, this “occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.” You will observe this wait type in sys.dm_exec_requests for individual sessions. Here is a KB article written primarily for SQL Server 2000 which describes how to troubleshoot this issue and also what happens when a query finally “gets tired” of waiting for a memory grant.
Why do you Care About Memory Grants or Workspace Memory or Query Execution Memory, or whatever you call it?
Over the years of troubleshooting performance problems, I have seen this to be one of the most common memory-related issues. Applications often execute seemingly simple queries that end up wreaking tons of performance havoc on the SQL Server side because of huge sort or hash operations. These not only end up consuming a lot of SQL Server memory during execution, but also cause other queries to have to wait for memory to become available – thus the performance bottleneck.
Using the tools I have outlined above (DMVs, Perfmon counters and actual query plan), you can investigate which queries are large-grant consumers and can have those tuned/re-written where possible.
What Can a Developer Actually Do about Sort/Hash Operations?
Speaking of re-writing queries, here are some things to look for in a query that may lead to large memory grants.
Reasons why a query would use a SORT operator (not all inclusive list):
ORDER BY (T-SQL)
GROUP BY (T-SQL)
DISTINCT (T-SQL)
Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is not available on that column.
Reasons why a query would use a Hash Match operator (not all inclusive list):
JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators – Hash Join. Look at query plan.
DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.
SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.
UNION – a Hash Aggregate could be used to remove the duplicates.
Knowing these common reasons can help an application developer eliminate, as much as possible, the large memory grant requests coming to SQL Server.
As always, basic query tuning starts with checking if your queries have appropriate indexes to help them reduce reads, minimize or eliminate large sorts where possible.
Update : Since SQL Server 2012 SP3, there exist a query hint that allows you to control the size of your memory grant. You can read about it in New query memory grant options are available (min_grant_percent and max_grant_percent) in SQL Server 2012 . Here is an example
SELECT * FROM Table1 ORDER BY Column1 OPTION (min_grant_percent = 3, max_grant_percent = 5 )
Memory Grant Internals:
Here is a great blog post on Memory Grant Internals
Summary of Ways to Deal with Large Grants:
- Re-write queries
- Use Resource Governor
- Find appropriate indexes for the query which may reduce the large number of rows processed and thus change the JOIN algorithms (see Database Engine Tuning Advisor and Missing Indexes DMVs)
- Use OPTION (min_grant_percent = XX, max_grant_percent = XX ) hint
- SQL Server 2017 and 2019 introduce Adaptive query processing allowing for Memory Grant feedback mechanism to adjust memory grant size dynamically at run-time.
Namaste!
Joseph
by Contributed | Jan 27, 2021 | Technology
This article is contributed. See the original author and article here.
Often I get asked by people how are looking for jobs if it makes sense to get certified. While certification is not a requirement nor a guarantee, it can definitely help to land a job working in a team doing cloud computing. I also use it often to make sure, I learn the right things. So we see that getting certified is a great thing, now the question is how do I prepare for a Microsoft Azure certification exam?
Since I passed a couple of the Azure exams, I would like to share how I prepared for these exams and passed. Hopefully, this will make it easier for you to pass them as well.
Passing exams is all about having the right strategy and preparation. If you are looking for tips and tricks to on how to take a Microsoft exam, check out my following blog.
Choose the right Azure exam and certification
To begin with, make sure you choose and pick the certification path and exam which is right for you. There are a lot of different exams and industry certifications out there. Microsoft’s approach of role-based certifications is aligned to relevant market and industry job-roles, to make it easier to find the right one. It makes a lot of sense to pick the right one for you, depending on where you are in your career and where you’re going. I wrote a blog post to give you an overview and pick the different Azure exam certification paths.
Identify the certification of your interest to find the required exams. To browse all the Microsoft Certification exams, check out the official website.
Start Small
If you are not 100% sure where and with which exam to start, I recommend that you start small by taking the AZ-900 Azure Fundamentals exam. This will help you understand how Microsoft exams work by not being too deep into technology. Having experience taking Microsoft exams helps you to focus on the actual topics and not on the testing process. Also, make sure that you have a look at these special offers, you can find more information on special offers further down.
Know the exam content, read what is measured
The first thing after and during picking the exam is to see what is asked during the exam. Every Microsoft exam page lists the “skills measured” in the exam. This list is usually very accurate and helps you to focus and study the right content. The page itself even lists available training and courses to prepare for the exam.
Microsoft Azure Exam Page – Skills measured and prepare for the exam
Understand the question types
Understanding the exam formats and question types before taking the exam can help you a lot. Microsoft does not mention which question types for exam formats are exactly in each exam, but you can find a list of exam and question samples here in this YouTube playlist. Understanding what questions types will be coming in your exam, will make it easier for you to answer them and get the most point per question.
Take free hands-on learning courses on Microsoft Learn
Microsoft Learn was introduced at Ignite 2018 as a free learning platform for a lot of different Microsoft technologies, not just Azure. Microsoft Learn provides you with various learning paths depending on your job role or the skills you are looking for. Most of the learning paths give you a hands-on learning opportunity so that you can develop practical skills through interactive training. And it is free! You get instant in-browser access to Microsoft tools and modules, no credit card required.
Microsoft Learn
Microsoft Learn :graduation_cap: – Up your game with a module or learning path tailored to today’s IT Pro, developer, and technology masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Hands-on experience
The best way to learn and pass the Microsoft Azure exams, or basically to learn anything, is most of the time through real hands-on experience with the technology. While Microsoft Learn gives you some free hands-on learning modules, there is also an Azure free account. The Azure free account will provide you with 12 months of free Azure services. You can find out more here. Make sure you dive into the skills measured and try the tutorials in Microsoft Docs.
Read the Microsoft Docs
Next, to Microsoft Learn and Hands-on experience, this is one of my main recommendations to prepare for a Microsoft Azure exam. Read the Microsoft Azure Documentation. Trust me on this, Azure and the topics which come up in the exams are very well documented. As mentioned, read the skills measured on the exam page, look up the specific Microsoft Docs pages and read through them and try out the tutorials.
Microsoft Docs
And have a look at my Azure Certification Exam Study Guides, which will help find you the right documentation.
Video courses and training
There are a lot of different video training courses out there, which allow you to do video-based Azure exam preparations. To mention a couple of them like LinkedIn Learning, Pluralsight, Whizlabs, ITPro.TV, Udemy, A Cloud Guru, CloudSkills.io, and many many more! Just browse through the different offers and read the review to find the best match for you. There are also a lot of Microsoft Learning Partners which offer online courses.
Choose instructor-led courses and learning partners
As you can see, there is a lot of self-study learning materials out there to prepare and pass the Azure exams. However, the classroom experience can be super beneficial and efficient, especially with the right trainer. You can find a list of official Microsoft Learning Partners with Microsoft Certified Trainers depending on your country here. A lot of them offer different courses for different technologies and in combination with in-person or online training.
Books
If you prefer to learn and prepare for an exam using books, Microsoft offers books written by the experts at Microsoft Press. There are some excellent books that will help you learn more and prepare and pass the Microsoft Azure exams. However, if you get a hard copy of the book, it won’t be updated in the future, to reflect changes in technology or in the exams.
Take a practice exam
Some of the exams also have official practice exams available. These are great to see where in the learning process you are standing and on which topics you need to spend a little bit more time. I highly recommend that you only do the official practice exams and don’t use brain dumps. Besides cheating on the exam and yourself, brain dumps are often simply wrong and contain a lot of mistakes. You can find Microsoft’s official practice tests here.
Study groups
If you have a couple of colleagues, friends, or people you met at an Azure User group meetup, it can help to build a study group. Study groups don’t just help you to get more structure in your learning. They also help you to gain a new perspective on the study material and reduce procrastination.
Conclusion
If you want to know more about how you can learn and get started with Microsoft Azure, check out my blog: How to learn Microsoft Azure in 2021.
I hope this gives you an overview of how you can prepare for a Microsoft Azure Certification exam. If you have any questions, please let me know in the comments.
Recent Comments