The size of the SQL plan cache in bytes: plancachesize specifies the SQL plan cache size in bytes and the default value is 2,147,483,648 (2G). Previously cached plans can be queried from MSQLPLANCACHE and statistics data on SQL plan cache can be queried from MSQLPLANCACHEOVERVIEW. Am I the only one who finds the Microsoft SQL server best practice guides to be a little painful to trawl through? Somehow, I doubt it. After being frustrated reading numerous technical guides, best practice guides, TechNet articles, and blog posts written by SQL experts, I thought it would be helpful to compile a simple post around SQL server best practices.
-->Once you have identified a performance issue that you are facing with SQL Database, this article is designed to help you:
- Tune your application and apply some best practices that can improve performance.
- Tune the database by changing indexes and queries to more efficiently work with data.
This article assumes that you have already worked through the Azure SQL Database database advisor recommendations and the Azure SQL Database auto-tuning recommendations. It also assumes that you have reviewed An overview of monitoring and tuning and its related articles related to troubleshooting performance issues. Additionally, this article assumes that you do not have a CPU resources, running-related performance issue that can be resolved by increasing the compute size or service tier to provide more resources to your database.
Tune your application
In traditional on-premises SQL Server, the process of initial capacity planning often is separated from the process of running an application in production. Hardware and product licenses are purchased first, and performance tuning is done afterward. When you use Azure SQL Database, it's a good idea to interweave the process of running an application and tuning it. With the model of paying for capacity on demand, you can tune your application to use the minimum resources needed now, instead of over-provisioning on hardware based on guesses of future growth plans for an application, which often are incorrect. Some customers might choose not to tune an application, and instead choose to over-provision hardware resources. This approach might be a good idea if you don't want to change a key application during a busy period. But, tuning an application can minimize resource requirements and lower monthly bills when you use the service tiers in Azure SQL Database.
Application characteristics
Although Azure SQL Database service tiers are designed to improve performance stability and predictability for an application, some best practices can help you tune your application to better take advantage of the resources at a compute size. Although many applications have significant performance gains simply by switching to a higher compute size or service tier, some applications need additional tuning to benefit from a higher level of service. For increased performance, consider additional application tuning for applications that have these characteristics:
- Applications that have slow performance because of 'chatty' behaviorChatty applications make excessive data access operations that are sensitive to network latency. You might need to modify these kinds of applications to reduce the number of data access operations to the SQL database. For example, you might improve application performance by using techniques like batching ad hoc queries or moving the queries to stored procedures. For more information, see Batch queries.
- Databases with an intensive workload that can't be supported by an entire single machineDatabases that exceed the resources of the highest Premium compute size might benefit from scaling out the workload. For more information, see Cross-database sharding and Functional partitioning.
- Applications that have sub-optimal queriesApplications, especially those in the data access layer, that have poorly tuned queries might not benefit from a higher compute size. This includes queries that lack a WHERE clause, have missing indexes, or have outdated statistics. These applications benefit from standard query performance-tuning techniques. For more information, see Missing indexes and Query tuning and hinting.
- Applications that have sub-optimal data access designApplications that have inherent data access concurrency issues, for example deadlocking, might not benefit from a higher compute size. Consider reducing round trips against the Azure SQL Database by caching data on the client side with the Azure Caching service or another caching technology. See Application tier caching.
Tune your database
In this section, we look at some techniques that you can use to tune Azure SQL Database to gain the best performance for your application and run it at the lowest possible compute size. Some of these techniques match traditional SQL Server tuning best practices, but others are specific to Azure SQL Database. In some cases, you can examine the consumed resources for a database to find areas to further tune and extend traditional SQL Server techniques to work in Azure SQL Database.
Identifying and adding missing indexes
A common problem in OLTP database performance relates to the physical database design. Often, database schemas are designed and shipped without testing at scale (either in load or in data volume). Unfortunately, the performance of a query plan might be acceptable on a small scale but degrade substantially under production-level data volumes. The most common source of this issue is the lack of appropriate indexes to satisfy filters or other restrictions in a query. Often, missing indexes manifests as a table scan when an index seek could suffice.
In this example, the selected query plan uses a scan when a seek would suffice:
Azure SQL Database can help you find and fix common missing index conditions. DMVs that are built into Azure SQL Database look at query compilations in which an index would significantly reduce the estimated cost to run a query. During query execution, SQL Database tracks how often each query plan is executed, and tracks the estimated gap between the executing query plan and the imagined one where that index existed. You can use these DMVs to quickly guess which changes to your physical database design might improve overall workload cost for a database and its real workload.
You can use this query to evaluate potential missing indexes:
In this example, the query resulted in this suggestion:
After it's created, that same SELECT statement picks a different plan, which uses a seek instead of a scan, and then executes the plan more efficiently:
The key insight is that the IO capacity of a shared, commodity system is more limited than that of a dedicated server machine. There's a premium on minimizing unnecessary IO to take maximum advantage of the system in the DTU of each compute size of the Azure SQL Database service tiers. Appropriate physical database design choices can significantly improve the latency for individual queries, improve the throughput of concurrent requests handled per scale unit, and minimize the costs required to satisfy the query. For more information about the missing index DMVs, see sys.dm_db_missing_index_details.
Query tuning and hinting
The query optimizer in Azure SQL Database is similar to the traditional SQL Server query optimizer. Numark mixtrack pro ii traktor. Most of the best practices for tuning queries and understanding the reasoning model limitations for the query optimizer also apply to Azure SQL Database. If you tune queries in Azure SQL Database, you might get the additional benefit of reducing aggregate resource demands. Your application might be able to run at a lower cost than an un-tuned equivalent because it can run at a lower compute size.
An example that is common in SQL Server and which also applies to Azure SQL Database is how the query optimizer 'sniffs' parameters. During compilation, the query optimizer evaluates the current value of a parameter to determine whether it can generate a more optimal query plan. Although this strategy often can lead to a query plan that is significantly faster than a plan compiled without known parameter values, currently it works imperfectly both in SQL Server and in Azure SQL Database. Sometimes the parameter is not sniffed, and sometimes the parameter is sniffed but the generated plan is sub-optimal for the full set of parameter values in a workload. Microsoft includes query hints (directives) so that you can specify intent more deliberately and override the default behavior of parameter sniffing. Often, if you use hints, you can fix cases in which the default SQL Server or Azure SQL Database behavior is imperfect for a specific customer workload.
The next example demonstrates how the query processor can generate a plan that is sub-optimal both for performance and resource requirements. This example also shows that if you use a query hint, you can reduce query run time and resource requirements for your SQL database:
The setup code creates a table that has skewed data distribution. The optimal query plan differs based on which parameter is selected. Unfortunately, the plan caching behavior doesn't always recompile the query based on the most common parameter value. So, it's possible for a sub-optimal plan to be cached and used for many values, even when a different plan might be a better plan choice on average. Then the query plan creates two stored procedures that are identical, except that one has a special query hint.
We recommend that you wait at least 10 minutes before you begin part 2 of the example, so that the results are distinct in the resulting telemetry data.
Each part of this example attempts to run a parameterized insert statement 1,000 times (to generate a sufficient load to use as a test data set). When it executes stored procedures, the query processor examines the parameter value that is passed to the procedure during its first compilation (parameter 'sniffing'). The processor caches the resulting plan and uses it for later invocations, even if the parameter value is different. The optimal plan might not be used in all cases. Sometimes you need to guide the optimizer to pick a plan that is better for the average case rather than the specific case from when the query was first compiled. In this example, the initial plan generates a 'scan' plan that reads all rows to find each value that matches the parameter:
Because we executed the procedure by using the value 1, the resulting plan was optimal for the value 1 but was sub-optimal for all other values in the table. The result likely isn't what you would want if you were to pick each plan randomly, because the plan performs more slowly and uses more resources.
If you run the test with
SET STATISTICS IO
set to ON
, the logical scan work in this example is done behind the scenes. You can see that there are 1,148 reads done by the plan (which is inefficient, if the average case is to return just one row):The second part of the example uses a query hint to tell the optimizer to use a specific value during the compilation process. In this case, it forces the query processor to ignore the value that is passed as the parameter, and instead to assume
UNKNOWN
. This refers to a value that has the average frequency in the table (ignoring skew). The resulting plan is a seek-based plan that is faster and uses fewer resources, on average, than the plan in part 1 of this example:You can see the effect in the sys.resource_stats table (there is a delay from the time that you execute the test and when the data populates the table). For this example, part 1 executed during the 22:25:00 time window, and part 2 executed at 22:35:00. The earlier time window used more resources in that time window than the later one (because of plan efficiency improvements).
Note
Although the volume in this example is intentionally small, the effect of sub-optimal parameters can be substantial, especially on larger databases. The difference, in extreme cases, can be between seconds for fast cases and hours for slow cases.
You can examine sys.resource_stats to determine whether the resource for a test uses more or fewer resources than another test. When you compare data, separate the timing of tests so that they are not in the same 5-minute window in the sys.resource_stats view. The goal of the exercise is to minimize the total amount of resources used, and not to minimize the peak resources. Generally, optimizing a piece of code for latency also reduces resource consumption. Make sure that the changes you make to an application are necessary, and that the changes don't negatively affect the customer experience for someone who might be using query hints in the application.
If a workload has a set of repeating queries, often it makes sense to capture and validate the optimality of your plan choices because it drives the minimum resource size unit required to host the database. After you validate it, occasionally reexamine the plans to help you make sure that they have not degraded. You can learn more about query hints (Transact-SQL).
Very large database architectures
Before the release of Hyperscale service tier for single databases in Azure SQL Database, customers used to hit capacity limits for individual databases. These capacity limits still exist for pooled databases in elastic pools and instance database in managed instances. The following two sections discuss two options for solving problems with very large databases in Azure SQL Database when you cannot use the Hyperscale service tier.
Cross-database sharding
Because Azure SQL Database runs on commodity hardware, the capacity limits for an individual database are lower than for a traditional on-premises SQL Server installation. Some customers use sharding techniques to spread database operations over multiple databases when the operations don't fit inside the limits of an individual database in Azure SQL Database. Most customers who use sharding techniques in Azure SQL Database split their data on a single dimension across multiple databases. For this approach, you need to understand that OLTP applications often perform transactions that apply to only one row or to a small group of rows in the schema.
Note
SQL Database now provides a library to assist with sharding. For more information, see Elastic Database client library overview.
For example, if a database has customer name, order, and order details (like the traditional example Northwind database that ships with SQL Server), you could split this data into multiple databases by grouping a customer with the related order and order detail information. You can guarantee that the customer's data stays in an individual database. The application would split different customers across databases, effectively spreading the load across multiple databases. With sharding, customers not only can avoid the maximum database size limit, but Azure SQL Database also can process workloads that are significantly larger than the limits of the different compute sizes, as long as each individual database fits into its DTU.
Although database sharding doesn't reduce the aggregate resource capacity for a solution, it's highly effective at supporting very large solutions that are spread over multiple databases. Each database can run at a different compute size to support very large, 'effective' databases with high resource requirements.
Functional partitioning
SQL Server users often combine many functions in an individual database. For example, if an application has logic to manage inventory for a store, that database might have logic associated with inventory, tracking purchase orders, stored procedures, and indexed or materialized views that manage end-of-month reporting. This technique makes it easier to administer the database for operations like backup, but it also requires you to size the hardware to handle the peak load across all functions of an application.
If you use a scale-out architecture in Azure SQL Database, it's a good idea to split different functions of an application into different databases. By using this technique, each application scales independently. As an application becomes busier (and the load on the database increases), the administrator can choose independent compute sizes for each function in the application. At the limit, with this architecture, an application can be larger than a single commodity machine can handle because the load is spread across multiple machines.
Batch queries
For applications that access data by using high-volume, frequent, ad hoc querying, a substantial amount of response time is spent on network communication between the application tier and the Azure SQL Database tier. Even when both the application and Azure SQL Database are in the same data center, the network latency between the two might be magnified by a large number of data access operations. To reduce the network round trips for the data access operations, consider using the option to either batch the ad hoc queries, or to compile them as stored procedures. If you batch the ad hoc queries, you can send multiple queries as one large batch in a single trip to Azure SQL Database. If you compile ad hoc queries in a stored procedure, you could achieve the same result as if you batch them. Using a stored procedure also gives you the benefit of increasing the chances of caching the query plans in Azure SQL Database so you can use the stored procedure again.
Some applications are write-intensive. Sometimes you can reduce the total IO load on a database by considering how to batch writes together. Often, this is as simple as using explicit transactions instead of auto-commit transactions in stored procedures and ad hoc batches. For an evaluation of different techniques you can use, see Batching techniques for SQL Database applications in Azure. Experiment with your own workload to find the right model for batching. Be sure to understand that a model might have slightly different transactional consistency guarantees. Finding the right workload that minimizes resource use requires finding the right combination of consistency and performance trade-offs.
Application-tier caching
Some database applications have read-heavy workloads. Caching layers might reduce the load on the database and might potentially reduce the compute size required to support a database by using Azure SQL Database. With Azure Cache for Redis, if you have a read-heavy workload, you can read the data once (or perhaps once per application-tier machine, depending on how it is configured), and then store that data outside your SQL database. This is a way to reduce database load (CPU and read IO), but there is an effect on transactional consistency because the data being read from the cache might be out of sync with the data in the database. Although in many applications some level of inconsistency is acceptable, that's not true for all workloads. You should fully understand any application requirements before you implement an application-tier caching strategy.
Next steps
- For more information about DTU-based service tiers, see DTU-based purchasing model.
- For more information about vCore-based service tiers, see vCore-based purchasing model.
- For more information about elastic pools, see What is an Azure elastic pool?
- For information about performance and elastic pools, see When to consider an elastic pool
title | ms.date | ms.topic | f1_keywords | helpviewer_keywords | ms.assetid | author | ms.author | manager | ms.workload |
---|---|---|---|---|---|---|---|---|---|
11/04/2016 |
| 67c82ae5-fddd-49df-baec-8e7498b156f3 | corob |
![Auto Tune Max Cached Translation Units Auto Tune Max Cached Translation Units](/uploads/1/3/3/9/133909921/383177061.jpg)
By changing these options, you can change the behavior related to IntelliSense and the browsing database when you're programming in C or C++.
To access this page, in the Options dialog box, in the left pane, expand Text Editor, expand C/C++, and then choose Advanced.
[!NOTE]Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. See Personalize the Visual Studio IDE.
Browsing/Navigation
You should never choose these options except in the rare case where a solution is so large that the database activity consumes an unacceptable amount of system resources.
Disable Database
Vst plugin fpc download. All use of the code browsing database (SDF), all other Browsing/Navigation options, and all IntelliSense features except for #include Auto Complete are disabled.
Disable Database Updates
The database will be opened read-only, and no updates will be performed as files are edited. Most features will still work. However, as edits are made, the data will become stale, and you'll get incorrect results.
Disable Database Auto Updates
The code browsing database won't be automatically updated when source files are modified. However, if you open Solution Explorer, open the shortcut menu for the project, and then choose Rescan Solution, all out-of-date files will be checked, and the database will be updated.
Disable Implicit Files
The code browsing database doesn't collect data for files that aren't specified in a project. A project contains source files and header files that are explicitly specified. Implicit files are included by explicit files (for example, afxwin.h, windows.h, and atlbase.h). Normally, the system finds these files and also indexes them for various browsing features (including Navigate To). If you choose this option, those files aren't indexed, and some features aren't available for them. If you choose this option, 'Disable Implicit Cleanup' and 'Disable External Dependencies' are also implicitly chosen.
Disable Implicit Cleanup
The code browsing database doesn't clean up implicit files that are no longer referenced. This option prevents implicit files from being removed from the database when they're no longer used. For example, if you add an
#include
directive that references mapi.h to one of your source files, mapi.h will be found and indexed. If you then remove the #include and the file isn't referenced elsewhere, information about it will eventually be removed unless you choose this option. (See the Rescan Solution Interval option.) This option is ignored when you explicitly rescan the solution.Disable External Dependencies Folders
The External Dependencies folder for each project isn't created or updated. In Solution Explorer, each project contains an External Dependencies folder, which contains all implicit files for that project. If you choose this option, that folder doesn't appear.
Recreate Database
Recreate the code browsing database from nothing the next time that the solution loads. If you choose this option, the SDF database file is deleted the next time you load the solution, thus causing the database to be recreated and all files indexed.
Rescan Solution Interval
A 'Rescan Solution Now' job is scheduled for the interval that you specify. You must specify between 0 and 5000 minutes. The default value is 60 minutes. While the solution is rescanned, file timestamps are checked to determine whether a file was changed outside of the IDE. (Changes that are made in the IDE are automatically tracked, and files are updated.) Implicitly included files are checked to determine whether they're all still referenced.
Diagnostic Logging
These options are provided in case Microsoft asks you to collect advanced information to diagnose an issue. The logging information isn't useful for users, and we recommend that you leave it disabled.
Enable Logging
Enables diagnostic logging to the output window.
Logging Level
Set the log verbosity, from 0 to 5.
Logging Filter
Filters displayed event types by using a bitmask.
Set by using a sum of any of the following options:
- 0 - None
- 2 - Idle
- 4 - WorkItem
- 8 - IntelliSense
- 16 - ACPerf
- 32 - ClassView
Fallback Location
The fallback location is where the SDF and IntelliSense support files (for example, iPCH) are put when the primary location (same directory as solution) isn't used. This situation could occur the user doesn't have the permissions to write to the solution directory or the solution directory is on a slow device. The default fallback location is in the user's temp directory.
Always Use Fallback Location
Indicates that the code browsing database and IntelliSense files should always be stored in a folder that you specify as your 'Fallback Location', not next to the .sln file. The IDE will never try to put the SDF or iPCH files next to the solution directory and will always use the fallback location.
Do Not Warn If Fallback Location Used
You aren't informed or prompted if a 'Fallback Location' is used. Normally, the IDE will tell you if it had to use the fallback location. This option turns off that warning.
Fallback Location
This value is used as a secondary location to store the code browsing database or IntelliSense files. By default, your temporary directory is your fallback location. The IDE will create a subdirectory under the specified path (or the temp directory) that includes the name of the solution along with a hash of the full path to the solution, which avoids issues with solution names being identical.
IntelliSense
Auto Quick Info
Enables QuickInfo tooltips when you move the pointer over text.
Disable IntelliSense
Disables all IntelliSense features. The IDE does not create VCPkgSrv.exe processes to service IntelliSense requests, and no IntelliSense features will work (QuickInfo, Member List, Auto Complete, Param Help). Semantic colorization and reference highlighting are also disabled. This option doesn't disable browsing features that rely solely on the database (including Navigation Bar, ClassView, and Property window).
Disable Auto Updating
IntelliSense updating is delayed until an actual request for IntelliSense is made. This delay can result in a longer execution time of the first IntelliSense operation on a file, but it may be helpful to set this option on very slow or resource-constrained machines. If you choose this option, you also implicitly choose the 'Disable Error Reporting' and 'Disable Squiggles' options.
Disable Error Reporting
Disables reporting of IntelliSense errors through squiggles and the Error List window. Also disables the background parsing that's associated with error reporting. If you choose this option, you also implicitly choose the 'Disable Squiggles' option.
Disable Squiggles
Disables IntelliSense error squiggles. The red 'squiggles' don't show in the editor window, but the error will still appear in the Error List window.
Auto Tune Max Cached Translation Units
The maximum number of translation units that will be kept active at any one time for IntelliSense requests. You must specify a value between 2 and 15. This number directly relates to the maximum number of VCPkgSrv.exe processes that will run (for a given instance of Visual Studio). The default value is 2, but if you have available memory, you can increase this value and possibly achieve slightly better performance on IntelliSense.
For more information about translation units, see Phases of Translation.
Disable #include Auto Complete
Disables auto-completion of
#include
statements.Use Forward Slash in #include Auto Complete
Triggers auto-completion of
#include
statements when '/' is used. The default delimiter is backslash '. The compiler can accept either, so use this option to specify what your code base uses.Disable Aggressive Member List
The member list doesn't appear while you type the name of a type or variable. The list appears only after you type one of the commit characters, as defined in the Member List Commit Characters option.
Disable Member List Keywords
Language keywords such as
void
, class
, switch
don't appear in member list suggestions.Disable Member List Code Snippets
Code snippets don't appear in member list suggestions.
![Auto Tune Max Cached Translation Units Auto Tune Max Cached Translation Units](/uploads/1/3/3/9/133909921/893149543.jpg)
Member List Filter Mode
Sets the type of matching algorithm. Fuzzy finds the most possible matches because it uses an algorithm that's similar to a spell-checker to find matches that are similar but not identical. Smart filtering matches substrings even if they're not at the start of a word. Prefix only matches on identical substrings that start at the beginning of the word.
Disable Semantic Colorization
Turns off all code colorization except for language keywords, strings, and comments. Auto tune with vocals.
Member List Commit Characters
Specifies the characters that cause the currently highlighted Member List suggestion to be committed. You can add or remove characters from this list.
Smart Member List Commit
Adds a line when you choose the Enter key at the end of a fully typed word.
Enable Member List Dot-To-Arrow
Replaces '.' with '->' when applicable for Member List.
References
Disable Resolving
For performance reasons, 'Find All References' displays raw textual search results by default instead of using IntelliSense to verify each candidate. You can clear this check box for more accurate results on all find operations. To filter on a per-search basis, open the shortcut menu for the result list, and then choose 'Resolve Results.'
Hide Unconfirmed
Hide unconfirmed items in the 'Find All References' results. If you unset the 'Disable Resolving' option, you can use this option to hide unconfirmed items in the results.
Disable Reference Highlighting
By default, when you select some text, all instances of the same text are automatically highlighted in the current document. You can disable this feature by setting Disable Reference Highlighting to True.
Text Editor
Enable Surround with Braces
If enabled, you can surround selected text with curly braces by typing '{' into the text editor.
Enable Surround with Parentheses
Auto Tune Max Cached Translation Units 1
If enabled, you can surround selected text with parentheses by typing '(' into the text editor.