by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
Monitoring your database is one of the most crucial tasks to ensure a continued healthy and steady workload. Azure Database for PostgreSQL, our managed database service for Postgres, provides a wealth of metrics to monitor your Postgres database on Azure. But what if the very metric that you are after is not yet available?
Worry not because there are ample options to easily create and monitor custom metrics with Azure Database for PostgreSQL. One solution you can use with Postgres on Azure is Datadog’s custom metrics.
If you are not familiar with Datadog, it is one of many solid 3rd party solutions that provides a set of canned metrics for various technologies, including PostgreSQL. Datadog also enables you to poll our databases with the help of custom queries to emit custom metrics data to a central location where you can monitor how well your workload is doing.
If you don’t yet have a Datadog account, no problem, you can use a free trial Datadog account to try out everything I’m going to show you in this post.
What is bloat in Postgres & why should you monitor it?
As a proud owner of a PostgreSQL database, you will inevitably have to experience and manage bloat, which is a product of PostgreSQL’s storage implementation for multi-version concurrency control. Concurrency is achieved by creating different versions of tuples as they receive modifications. As you can imagine, PostgreSQL will keep as many versions of the same tuple as the number of concurrent transactions at any time and make the last committed version visible to the consecutive transactions. Eventually, this creates dead tuples in pages that later need to be reclaimed.
To keep your database humming, it’s important to understand how your table and index bloat values progress over time—and to make sure that garbage collection happens as aggressively as it should. So you need to monitor your bloat in Postgres and act on it as needed.
Before you start, I should clarify that this post is focused on how to monitor bloat on Azure Database for PostgreSQL – Single Server. On Azure, our Postgres managed database service also has a built-in deployment option called Hyperscale (Citus)—based on the Citus open source extension—and this Hyperscale (Citus) option enables you to scale out Postgres horizontally. Because the code snippets and instructions below are a bit different for monitoring a single Postgres server vs. monitoring a Hyperscale (Citus) server group, I plan to publish the how-to instructions for using custom monitoring metrics on a Hyperscale (Citus) cluster in a separate/future blog post. Stay tuned! Now, let’s get started.
First, prepare your monitoring setup for Azure Database for PostgreSQL – Single Server
If you do not already have an Azure Database for PostgreSQL server, you may create one as prescribed in our quickstart documentation.
Create a read-only monitoring user
As a best practice, you should allocate a read-only user to poll your data from database. Depending on what you want to collect, granting pg_monitor role, which is a member of pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables starting from Postgres 10, could be sufficient.
For this situation, we will also need to GRANT SELECT for the role to all the tables that we want to track for bloat.
CREATE USER metrics_reader WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT 1 PASSWORD 'xxxxxx';
GRANT pg_monitor TO metrics_reader;
--Rights granted here as blanket for simplicity.
GRANT SELECT ON ALL TABLES IN SCHEMA public to metrics_reader;
Create your bloat monitoring function
To keep Datadog configuration nice and tidy, let’s first have a function to return the bloat metrics we want to track. Create the function below in the Azure Database for PostgreSQL – Single Server database you would like to track.
If you have multiple databases to track, you can consider an aggregation mechanism from different databases into a single monitoring database to achieve the same objective. This how-to post is designed for a single database, for the sake of simplicity.
The bloat tracking script used here is a popular choice and was created by Greg Sabino Mullane. There are other bloat tracking scripts out there in case you want to research a better fitting approach to track your bloat estimates and adjust your get_bloat function.
CREATE OR REPLACE FUNCTION get_bloat ()
RETURNS TABLE (
database_name NAME,
schema_name NAME,
table_name NAME,
table_bloat NUMERIC,
wastedbytes NUMERIC,
index_name NAME,
index_bloat NUMERIC,
wastedibytes DOUBLE PRECISION
)
AS $$
BEGIN
RETURN QUERY SELECT current_database() as databasename, schemaname, tablename,ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::FLOAT/otta END)::NUMERIC,1) AS tbloat,CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,iname, ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::FLOAT/iotta END)::NUMERIC,1) AS ibloat,CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes FROM (SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs,CEIL((cc.reltuples*((datahdr+ma-(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::FLOAT)) AS otta,COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::FLOAT)),0) AS iotta FROM (SELECT ma,bs,schemaname,tablename,(datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,(maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM (SELECT schemaname, tablename, hdr, ma, bs,SUM((1-null_frac)*avg_width) AS datawidth,MAX(null_frac) AS maxfracsum,hdr+(SELECT 1+COUNT(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename) AS nullhdr FROM pg_stats s, (SELECT(SELECT current_setting('block_size')::NUMERIC) AS bs,CASE WHEN SUBSTRING(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo) AS constants GROUP BY 1,2,3,4,5) AS foo) AS rs JOIN pg_class cc ON cc.relname = rs.tablename JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' LEFT JOIN pg_index i ON indrelid = cc.oid LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid) AS sml WHERE schemaname NOT IN ('pg_catalog') ORDER BY wastedbytes DESC;
END; $$
LANGUAGE 'plpgsql';
Confirm your read-only Postgres user can observe results
At this point, you should be able to connect to your Azure Database for PostgreSQL server with your read-only user and run SELECT * FROM get_bloat(); to observe results.
get_bloat function’s sample output
If you don’t get anything in the output, see if the following steps remedy this:
- Check your pg_stat records with
SELECT * FROM pg_stats WHERE schemaname NOT IN ('pg_catalog','information_schema');
- If you don’t see your table and columns in there, make sure to run
ANALYZE <your_table> and try again
- If you still don’t see your table in the result set from #1, your user very likely does not have select privilege on a table that you expect to see in the output
Then, setup your 3rd party monitoring (in this case, with Datadog)
Once you confirm that your read-only user is able to collect the metrics you want to track on your Azure Postgres single server, you are now ready to set up your 3rd party monitoring!
For this you will need two things. First, a Datadog account. Second, a machine that will host your Datadog agent, to do the heavy lifting of connecting to your database to extract the metrics you want and to push the metrics into your Datadog workspace.
For this exercise, I had an Azure Linux virtual machine handy that I could use as the agent host, but you can follow quickstart guides available for Azure Virtual Machines to create a new machine or use an existing one. Datadog provides scripts to set up diverse environments, which you can find after you log in to your Datadog account and go to the Agents section in Datadog’s Postgres Integrations page. Following the instructions, you should get message similar to the following.
datadog agent setup success state
Next step is to configure datadog agent for Postgres specific collection. If you aren’t already working with an existing postgres.d/conf.yaml, just copy the conf.yaml.example in /etc/datadog-agent/conf.d/postgres.d/ and adjust to your needs.
Once you follow the directions and set up your host, port, user, and password in /etc/datadog-agent/conf.d/postgres.d/conf.yaml, the part that remains is to set up your custom metrics section with below snippet.
custom_queries:
- metric_prefix: azure.postgres.single_server.custom_metrics
query: select database_name, schema_name, table_name, table_bloat, wastedbytes, index_name, index_bloat, wastedibytes from get_bloat();
columns:
- name: database_name
type: tag
- name: schema_name
type: tag
- name: table_name
type: tag
- name: table_bloat
type: gauge
- name: wastedbytes
type: gauge
- name: index_name
type: tag
- name: index_bloat
type: gauge
- name: wastedibytes
type: gauge
Once this step is done, all you need to do is to restart your datadog-agent sudo systemctl restart datadog-agent for your custom metrics to start flowing in.
Setup your new bloat monitoring dashboard for Azure Database for PostgreSQL – Single Server
If all goes well, you should be able to see your custom metrics in Metrics Explorer shortly!
azure postgresql custom metrics flowing successfully into datadog workspace
From above you can export these charts to a new or existing dashboard and edit the widgets to your needs to show separate visuals by dimensions as table or index or you can simply overlay them as below. Datadog documentation is quite rich to help you out.
custom metrics added to a new dashboard
Knowing how your bloat metrics are trending will help you investigate performance problems and help you to identify if bloat is contributing to performance fluctuations. Monitoring bloat in Postgres will also help you evaluate whether your workload (or your Postgres tables) are configured optimally for autovacuum to perform its function.
Using custom metrics makes it easy to monitor bloat in Azure Database for PostgreSQL
You can and absolutely should track bloat. And with custom metrics and Datadog, you can easily track bloat in your workload for an Azure Database for PostgreSQL server. You can track other types of custom Postgres metrics easily in the same fashion.
One more thing to keep in mind: I recommend you always be intentional on what and how to collect, as metric polling can impact your workload.
If you have a much more demanding workload and are using Hyperscale (Citus) to scale out Postgres horizontally, I will soon have a post on how you can monitor bloat with custom metrics in Azure Database for Postgres – Hyperscale (Citus). I look forward to seeing you there!
by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
In my introductory post we saw that there are many different ways in which you can host a GraphQL service on Azure and today we’ll take a deeper look at one such option, Azure App Service, by building a GraphQL server using dotnet. If you’re only interested in the Azure deployment, you can jump forward to that section. Also, you’ll find the complete sample on my GitHub.
Getting Started
For our server, we’ll use the graphql-dotnet project, which is one of the most common GraphQL server implementations for dotnet.
First up, we’ll need an ASP.NET Core web application, which we can create with the dotnet cli:
Next, open the project in an editor and add the NuGet packages we’ll need:
<PackageReference Include="GraphQL.Server.Core" Version="3.5.0-alpha0046" />
<PackageReference Include="GraphQL.Server.Transports.AspNetCore" Version="3.5.0-alpha0046" />
<PackageReference Include="GraphQL.Server.Transports.AspNetCore.SystemTextJson" Version="3.5.0-alpha0046" />
At the time of writing graphql-dotnet v3 is in preview, we’re going to use that for our server but be aware there may be changes when it is released.
These packages will provide us a GraphQL server, along with the middleware needed to wire it up with ASP.NET Core and use System.Text.Json as the JSON seralizer/deserializer (you can use Newtonsoft.Json if you prefer with this package).
We’ll also add a package for GraphiQL, the GraphQL UI playground, but it’s not needed or recommended when deploying into production.
<PackageReference Include="GraphQL.Server.Ui.Playground" Version="3.5.0-alpha0046" />
With the packages installed, it’s time to setup the server.
Implementing a Server
There are a few things that we need when it comes to implementing the server, we’re going to need a GraphQL schema, some types that implement that schema and to configure our route engine to support GraphQL’s endpoints. We’ll start by defining the schema that’s going to support our server and for the schema we’ll use a basic trivia app (which I’ve used for a number of GraphQL demos in the past). For the data, we’ll use Open Trivia DB.
.NET Types
First up, we’re going to need some generic .NET types that will represent the underlying data structure for our application. These would be the DTOs (Data Transfer Objects) that we might use in Entity Framework, but we’re just going to run in memory.
public class Quiz
{
public string Id
{
get
{
return Question.ToLower().Replace(" ", "-");
}
}
public string Question { get; set; }
[JsonPropertyName("correct_answer")]
public string CorrectAnswer { get; set; }
[JsonPropertyName("incorrect_answers")]
public List IncorrectAnswers { get; set; }
}
As you can see, it’s a fairly generic C# class. We’ve added a few serialization attributes to help converting the JSON to .NET, but otherwise it’s nothing special. It’s also not usable with GraphQL yet and for that, we need to expose the type to a GraphQL schema, and to do that we’ll create a new class that inherits from ObjectGraphType<Quiz> which comes from the GraphQL.Types namespace:
public class QuizType : ObjectGraphType<Quiz>
{
public QuizType()
{
Name = "Quiz";
Description = "A representation of a single quiz.";
Field(q => q.Id, nullable: false);
Field(q => q.Question, nullable: false);
Field(q => q.CorrectAnswer, nullable: false);
Field<NonNullGraphType<ListGraphType<NonNullGraphType>>>("incorrectAnswers");
}
}
The Name and Description properties are used provide the documentation for the type, next we use Field to define what we want exposed in the schema and how we want that marked up for the GraphQL type system. We do this for each field of the DTO that we want to expose using a lambda like q => q.Id, or by giving an explicit field name (incorrectAnswers). Here’s also where you control the schema validation information as well, defining the nullability of the fields to match the way GraphQL expects it to be represented. This class would make a GraphQL type representation of:
type Quiz {
id: String!
question: String!
correctAnswer: String!
incorrectAnswers: [String!]!
}
Finally, we want to expose a way to query our the types in our schema, and for that we’ll need a Query that inherits ObjectGraphType:
public class TriviaQuery : ObjectGraphType
{
public TriviaQuery()
{
Field<NonNullGraphType<ListGraphType<NonNullGraphType<QuizType>>>>("quizzes", resolve: context =>
{
throw new NotImplementedException();
});
Field<NonNullGraphType<QuizType>>("quiz", arguments: new QueryArguments() {
new QueryArgument<NonNullGraphType<StringGraphType>> { Name = "id", Description = "id of the quiz" }
},
resolve: (context) => {
throw new NotImplementedException();
});
}
}
Right now there is only a single type in our schema, but if you had multiple then the TriviaQuery would have more fields with resolvers to represent them. We’ve also not implemented the resolver, which is how GraphQL gets the data to return, we’ll come back to that a bit later. This class produces the equivalent of the following GraphQL:
type TriviaQuery {
quizzes: [Quiz!]!
quiz(id: String!): Quiz!
}
Creating a GraphQL Schema
With the DTO type, GraphQL type and Query type defined, we can now implement a schema to be used on the server:
public class TriviaSchema : Schema
{
public TriviaSchema(TriviaQuery query)
{
Query = query;
}
}
Here we would also have mutations and subscriptions, but we’re not using them for this demo.
Wiring up the Server
For the Server we integrate with the ASP.NET Core pipeline, meaning that we need to setup some services for the Dependency Injection framework. Open up Startup.cs and add update the ConfigureServices:
public void ConfigureServices(IServiceCollection services)
{
services.AddTransient<HttpClient>();
services.AddSingleton<QuizData>();
services.AddSingleton<TriviaQuery>();
services.AddSingleton<ISchema, TriviaSchema>();
services.AddGraphQL(options =>
{
options.EnableMetrics = true;
options.ExposeExceptions = true;
})
.AddSystemTextJson();
}
The most important part of the configuration is lines 8 – 13, where the GraphQL server is setup and we’re defining the JSON seralizer, System.Text.Json. All the lines above are defining dependencies that will be injected to other types, but there’s a new type we’ve not seen before, QuizData. This type is just used to provide access to the data store that we’re using (we’re just doing in-memory storage using data queried from Open Trivia DB), so I’ll skip its implementation (you can see it on GitHub).
With the data store available, we can update TriviaQuery to consume the data store and use it in the resolvers:
public class TriviaQuery : ObjectGraphType
{
public TriviaQuery(QuizData data)
{
Field<NonNullGraphType<ListGraphType<NonNullGraphType<QuizType>>>>("quizzes", resolve: context => data.Quizzes);
Field<NonNullGraphType<QuizType>>("quiz", arguments: new QueryArguments() {
new QueryArgument<NonNullGraphType<StringGraphType>> { Name = "id", Description = "id of the quiz" }
},
resolve: (context) => data.FindById(context.GetArgument<string>("id")));
}
}
Once the services are defined we can add the routing in:
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseGraphQLPlayground();
}
app.UseRouting();
app.UseGraphQL<ISchema>();
}
I’ve put the inclusion GraphiQL. within the development environment check as that’d be how you’d want to do it for a real app, but in the demo on GitHub I include it every time.
Now, if we can launch our application, navigate to https://localhost:5001/ui/playground and run the queries to get some data back.
Deploying to App Service
With all the code complete, let’s look at deploying it to Azure. For this, we’ll use a standard Azure App Service running the latest .NET Core (3.1 at time of writing) on Windows. We don’t need to do anything special for the App Service, it’s already optimised to run an ASP.NET Core application, which is all this really is. If we were using a different runtime, like Node.js, we’d follow the standard setup for a Node.js App Service.
To deploy, we’ll use GitHub Actions, and you’ll find docs on how to do that already written. You’ll find the workflow file I’ve used in the GitHub repo.
With a workflow committed and pushed to GitHub and our App Service waiting, the Action will run and our application will be deployed. The demo I created is here.
Conclusion
Throughout this post we’ve taken a look at how we can create a GraphQL server running on ASP.NET Core using graphql-dotnet and deploy it to an Azure App Service.
When it comes to the Azure side of things, there’s nothing different we have to do to run the GraphQL server in an App Service than any other ASP.NET Core application, as graphql-dotnet is implemented to leverage all the features of ASP.NET Core seamlessly.
Again, you’ll find the complete sample on my GitHub for you to play around with yourself.
This post was originally published on www.aaron-powell.com .
by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
In part one of this two-part series, Shreya Verma discusses the different backups options (point-in-time restore, long-term retention) Azure SQL provides and how to effectively manage them.
Watch on Data Exposed
Additional Resources:
Overview of Business Continuity
Automated Backups
Backup Cost Management
PiTR and Geo-Restore
Long-Term Retention
Recovery Drills
Azure SQL Blogs
View/share our latest episodes on Channel 9 and YouTube!
by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
|
Microsoft partners like Confluent, NextGate, and CBX Software deliver transact-capable offers, which allow you to purchase directly from Azure Marketplace. Learn about these offers below:
 |
Apache Kafka on Confluent Cloud for Azure: Confluent Cloud is a fully managed cloud-native event streaming service powered by Apache Kafka. Stream confidently with plans featuring no hourly compute cost and 99.5 percent to 99.95 percent uptime service-level agreements. Start streaming in minutes with on-demand provisioning and elastic scaling for a serverless Kafka experience.
|
 |
NextGate Enterprise Master Patient Index (EMPI): NextGate’s fully automated Enterprise Master Patient Index (EMPI) helps healthcare organizations overcome the clinical, operational, and financial challenges of poor identity management, duplicate medical records, and disparate data to enable a longitudinal, enterprise view of patient information across the care continuum. Get it here or at its page on Microsoft AppSource. |
 |
TradeBeyond: TradeBeyond by CBX Software isn’t just another finished-goods online marketplace: Rather, it’s an exclusive network in which retailers, brands, and their suppliers and factories can connect, source, extend innovation, and bring private-label products to market faster. TradeBeyond is home to thousands of validated suppliers, and its retail sourcing app gives you the tools to make connections and win in the industry.
|
|
by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
Executive Summary
To improve customer workload performance and reduce unnecessary resource utilization, the default MAXDOP setting for new databases in Azure SQL Database is changing from the previous default of 0 (unlimited) to 8.
Why MAXDOP matters
The “max degree of parallelism” (a.k.a. MAXDOP) SQL Server configuration option controls the ability of the database engine to use parallel threads during query processing. When MAXDOP is other than 1, the database engine may execute queries using multiple concurrent threads. This often results in shorter query duration due to additional CPU resources used for query processing. The number of concurrently executing threads, and the resulting CPU utilization, depends on the MAXDOP value. Higher values generally result in more threads and higher resource utilization.
If a query is using parallelism, it often gets faster as MAXDOP is increased, though after some point additional threads may just use extra CPU cycles without reducing query duration. To learn more about query parallelism, see the Degree of Parallelism section in the Query Processing Architecture Guide.
Azure SQL uses the SQL Server database engine, thus MAXDOP considerations and recommendations for SQL Server are also applicable to Azure SQL. This includes single databases, databases in elastic pools, and managed instances. For all of these deployment options, customers can control MAXDOP at the database level using the MAXDOP database-scoped configuration. For managed instances, customers can also set the server ‘max degree of parallelism’ configuration option, and can control MAXDOP at the Resource Governor workload group level. For all Azure SQL deployment options, MAXDOP can additionally be controlled at the individual query level by using the OPTION (MAXDOP) query hint, which overrides MAXDOP configurations set in the database or instance scope (though without exceeding the Resource Governor workload group cap, if any).
Changing MAXDOP can have major impact on query performance and resource utilization, both positive and negative. However, there is not a single MAXDOP value that is optimal for all workloads. The recommendations for setting MAXDOP are nuanced, and depend on many factors. Customers wishing to achieve optimal performance for their workloads may need to fine-tune MAXDOP, using published recommendations as the starting point.
Importantly, while increasing MAXDOP often reduces duration for long-running queries, excessive parallelism can cause unnecessary CPU and worker utilization, and make the overall workload performance worse by starving other queries of these resources. In extreme cases, excessive parallelism can consume all database or elastic pool resources, causing query timeouts, errors, and application outages.
Historically, the default MAXDOP value in SQL Server has always been set to 0. This means that query parallelism is limited only by the hardware bounds of the machine running the SQL Server instance (up to the maximum MAXDOP of 64). For backward compatibility reasons, this remains the default value in SQL Server, though starting with SQL Server 2019, it is possible to change the instance level MAXDOP during initial instance setup.
In Azure SQL Database, the default MAXDOP value has also been set to 0 (unlimited) for every new database, for similar reasons. However, over time, and especially as databases and elastic pools with many cores became widely used, performance problems due to excessive parallelism caused by MAXDOP set to 0 became more frequent. This particularly impacts customers who are not familiar with the MAXDOP option, and leave it at the default of 0.
To address these performance problems, we are making the following change:
For every new single database and elastic pool database, the MAXDOP database-scoped configuration will be set to 8 by default.
Frequently Asked Questions
Why are you changing default MAXDOP for new Azure SQL databases?
This change is to reduce the frequency and severity of incidents caused by excessive query parallelism, and to improve customer workload performance by reducing unnecessary resource utilization. We expect that this change will benefit the overwhelming majority of customers using Azure SQL Database.
When will this change happen?
We expect this change to roll out worldwide in August of 2020.
Will this change impact my existing Azure SQL databases?
No. Existing databases will not be modified. This change applies only to new databases created after this change is rolled out.
Does this change apply to Azure SQL Managed Instance?
No, at this time this change only applies to single databases and databases in elastic pools in Azure SQL Database.
I know that MAXDOP setting different from 8 is optimal for my workload. What should I do?
Customers who have determined that a specific MAXDOP setting is optimal for their workloads, or customers who prefer to keep MAXDOP 0 used previously by default, can add a step to their database deployment processes to change MAXDOP after database creation.
Should I change MAXDOP on my existing Azure SQL databases?
Customers with existing databases that use the default MAXDOP 0 should consider changing MAXDOP according to standing recommendations. This may improve performance, reduce the risk of performance and availability incidents, and in some cases reduce costs by being able to avoid unnecessary resource utilization, and thus scale down to a lower service objective.
We recommend that customers avoid MAXDOP 0 even if it does not appear to cause problems currently. This will reduce the risk of potential future problems due to excessive parallelism if a database is scaled up to use more cores, or if future hardware generations in Azure SQL provide more cores for the same database service objective.
As with most configuration changes, thorough testing is recommended before making changes in critical environments.
How can I change MAXDOP for an existing database?
MAXDOP can be changed using the ALTER DATABASE SCOPED CONFIGURATION statement in the scope of a database.
To execute the necessary T-SQL statement, customers can use any client tool or programming language that can connect to the database and execute T-SQL commands. The following client tools are commonly used:
- Query editor, which is a part of Azure portal.
- SQL Server Management Studio.
- Azure Data Studio.
For example, to change MAXDOP to 4, use the following T-SQL command when connected to the target database:
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
How do I know if a change to MAXDOP made a difference for my workload?
Customers can see the impact of a change in MAXDOP by making sure that Query Store is enabled for a representative period of time before and after the change is made, to capture resource utilization and performance statistics for each query. For more details, see A/B testing.
What are the symptoms of excessive query parallelism in Azure SQL Database?
In Azure SQL Database, one common symptom of excessive parallelism is exceeding the resource governance limits on the number of worker threads. When this happens, error 10928, “Resource ID : 1. The request limit for the database is N and has been reached” is raised, where N stands for the worker thread limit for the database or elastic pool (note, however, that there are other possible causes for this error).
CXPACKET waits may be another symptom of excessive parallelism. Some amount of CXPACKET waits is normal whenever query parallelism is used; however, if CXPACKET waits dominate other waits types while CPU utilization is greater than 80-90%, it likely also indicates excessive parallelism.
Why did you choose 8 as the new default MAXDOP?
Our telemetry data and our experience running the Azure SQL Database service show that MAXDOP 8 is the optimal value for the widest variety of customer workloads. It is a safe option that reduces the likelihood of performance problems due to excessive parallelism, while still allowing queries to execute faster by using more threads.
At the same time, workloads where a different MAXDOP value is optimal do exist. Customers can experiment with different MAXDOP settings to determine what works best for them.
Shouldn’t the database engine automatically choose the optimal degree of parallelism for every query it executes?
Improvements in Azure SQL and the SQL Server database engine are made continuously. Automatic Tuning, and the Intelligent Query Processing family of features are two examples of improvements in query processing and query performance made in recent years. Today, the database engine already reduces query parallelism at query startup time in response to a shortage of available worker threads. Query processing improvements in future releases of the database engine may adjust parallelism dynamically in a broader set of scenarios.
Why are you changing the default MAXDOP regardless of the database service objective? Shouldn’t this only apply to databases with more than 8 cores?
Limiting MAXDOP to 8 is safe for databases with 8 or less cores (or scheduler threads). In this case, parallelism will be limited by the smaller number of available cores, rather than by the MAXDOP setting. Limiting MAXDOP to 8 protects smaller databases from being exposed to excessive parallelism if scaled up to use more than 8 cores, or if future hardware generations in Azure SQL Database provide more cores for the same database service objective.
What is the impact of this change on index rebuild?
By default, indexes are rebuilt with parallelism, using the database-scoped MAXDOP setting. On databases with more than 8 cores, this change may cause index rebuilds to take longer due to lower degree of parallelism used. If this happens, you can increase parallelism for each index rebuild operation by using the MAXDOP option in the ALTER INDEX … REBUILD statement.
How can I ask questions or provide feedback on this change?
Customers can ask questions or provide feedback via comments to this blog post, or via email to IntelligentQP [at] microsoft.com.
by Scott Muniz | Jul 23, 2020 | Alerts, Microsoft, Technology, Uncategorized
This article is contributed. See the original author and article here.
Now released for general availability (GA), M-series and Fsv2-series are new hardware generations in Azure SQL Database specialized for workloads requiring very high compute or memory scale or very fast CPU speed. M-series and Fsv2-series are supported for both single database and elastic pool deployments. Gen5 hardware in SQL Database continues to provide a balanced compute and memory option that is well-suited for typical workloads.
M-series: memory optimized hardware
M-series is a new memory optimized hardware option in SQL Database for workloads demanding more memory and higher compute limits than provided by Gen5. M-series provides 29 GB per vcore and up to 128 vcores which increases the previous memory limit in SQL Database by 8x to nearly 4 TB. Also, now available are smaller M-series compute sizes starting from 8 vcores on up for workloads requiring less total CPU and memory, but still providing the same high memory-to-vcore ratio for less cost.
Fsv2-series: compute optimized hardware
Fsv2-series is a new compute optimized hardware option in SQL Database delivering low CPU latency and high clock speed for the most CPU demanding workloads. Depending on the workload, Fsv2-series can deliver more CPU performance per vcore than Gen5. The 72 vcore size can also provide more CPU performance for less cost than 80 vcores on Gen5. Also, now available are smaller Fsv2-series compute sizes starting from 8 vcores on up for workloads requiring less total CPU and memory, but still providing the same low CPU latency and high clock speed per vcore for less cost. Note that Fsv2 provides less memory and tempdb per vcore than other hardware so workloads sensitive to those limits may want to consider Gen5 or M-series instead.
Learn more
Recent Comments