By migrating their databases to Azure, customers like Ernst and Young are modernizing their data estate and leveraging cutting-edge cloud innovations. However, the migration process can be complex, whether moving within the same database management system (homogeneous) or between different systems (heterogeneous). Microsoft offers a suite of tools for migration to simplify the migration process. To further enhance the user experience, we are streamlining the Azure database migration tools ecosystem. This involves retiring certain overlapping tools to simplify finding the right tool and provide unified migration experiences across all phases of migration. As part of this effort, effective 12/15/2024 we are replacing some tools with unified experiences that offer capabilities across various migration stages in the drive to modernize their data estate and take advantage of innovation in the cloud.

Azure Database Migration Guides

Step-by-step guidance for modernizing your data assets

Learn more

With a refined set of tools, you can confidently plan, assess, and execute your database migration with minimal downtime, ensuring a smooth transition to Azure SQL. Post the 12/15/24, retirement date, Microsoft will stop supporting these tools for any issues that arise and will not issue any bug fixes or further updates. Here is the list of tools that are planned for retirement and Microsoft recommended replacement tools.

Microsoft recommended tools for migration to Azure SQL

With the retirement of Database Migration Assistant for Oracle (DMAO), Database Schema Conversion Toolkit (DSCT), Data Access Migration Toolkit (DAMT), Database Experimentation Assistant (DEA), the Azure database migration tooling ecosystem is greatly simplified. Here is Microsoft’s recommendation for database migration tools for customers moving to Azure SQL. 

Homogenous migrations (SQL Server to Azure SQL) 

If the SQL Server that will be migrated is already enabled by Azure Arc, you can use Arc capabilities to perform a migration assessment and get optimal Azure SQL Target recommendations. Additionally, SQL Server enabled by Azure Arc provides multiple Azure benefits to SQL Servers outside Azure like automated backups and patching, Microsoft Defender for SQL, inventory of instances and databases, and Entra ID support. By enabling these Arc features, you can leverage cloud automation and security for Azure SQL Server even before you migrate. 

If the SQL Server outside Azure is not inventoried yet, you can use Azure Migrate for discovery, assessment and business case to know the right Azure SQL targets for your on-premises SQL Workloads and to get the projected cost savings of migrating to Azure SQL.

To migrate SQL Server into an Azure Virtual Machine with the same configuration as the source, users can use Azure Migrate to perform lift and shift migrations. SQL Server on Azure Virtual Machines allows you to easily migrate your SQL Server workloads to the cloud, offering SQL Server’s performance and security along with Azure’s flexibility and hybrid connectivity to address urgent business needs. Later you can evaluate one of the Azure SQL PaaS targets (Azure SQL Managed Instance, Azure SQL Database) and modernize to a PaaS service for better cost and workload performance optimizations. 

If you have completed an assessment and are ready to move to Azure SQL Managed Instance or Azure SQL Database, you can start your migration journey with Azure Migrate, you can use Azure Database Migration service or Azure SQL Migration extension for Azure Data Studio can be used. 

If the SQL Server estate is already inventoried, users can use Azure SQL Migration extension for Azure Data Studio to complete the entire migration journey i.e., perform assessment, get Azure SQL Target recommendations and perform migrations.

Heterogenous migrations (non-SQL Server databases to Azure SQL) 

With the availability of Target Assessment and SKU recommendation capabilities in SQL Server Migration Assistant (SSMA) along with existing code conversion and migration capabilities, SSMA becomes a single tool that you need to use to migrate from other source database platforms like Oracle, DB2, SAP ASE, MySQL, Access to Azure SQL or SQL Server. 

Learn more about modernizing your databases with Azure

• Get step-by-step guidance for modernizing your Oracle and SQL Server databases to Azure in the Azure Database Migration Guides.
• Read about the Azure Migrate Assessment for Azure SQL to get an overview of SQL Server assessment process using Azure Migrate.
• Know how to complete end to end SQL Server to Azure SQL migration journey with Azure SQL Migration extension for Azure Data Studio. 
• Find documentation for SQL Server Migration Assistant (SSMA), a tool for assessing and automating migration from Microsoft Access, DB2, MySQL, Oracle, and SAP ASE.

The post Modernize your database with the consolidation and retirement of Azure Database Migration tools appeared first on Microsoft SQL Server Blog.

“With our migration to PaaS, we got what we wanted: greater scalability, reliability, security, agility in managing our IT infrastructure—and greater peace of mind—all without the cost and hassle of doing this ourselves,” 

Daniel Engberg, Head of AI, Data, and Platforms at Scandinavian Airlines System  

Migrate to Microsoft Azure

Boost productivity and enable innovation.

Read more

This blog post will guide you through several best practices our customers employed when faced with the SQL Server end-of-support moment. Customers have three choices for handling their out-of-support SQL Server workloads: moving or updating to Azure, upgrading to SQL Server 2022, or getting Extended Security Updates (ESUs) for additional preparation time. 

Migrate and modernize to Azure, a smooth path, a more powerful destination 

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. What makes SQL Server and Microsoft Azure SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides. 

Modernizing to Microsoft Azure SQL Managed Instance offers cost savings, scalability, security, seamless migration, productivity, and always up-to-date features. Some of the recent product highlights include Azure SQL Managed Instance Next-gen General Purpose, now in public preview, which supports twice as many Azure VMs configurations, making migration and modernization faster and easier than ever before for a larger number of customer scenarios. Customers can experience the full capabilities of managed SQL Server in the cloud at no cost for the initial 12 months with access to a General Purpose instance capable of accommodating up to 100 databases, along with 720 vCore hours of compute per month (non-accumulative) and 64 GB of storage through Azure SQL Managed Instance Free Tier, now in public preview. 

Modernizing your SQL Server workloads to Azure also presents a chance to utilize cutting-edge innovation like Microsoft Copilot. Microsoft Copilot in Azure has extended its capabilities to Microsoft Azure SQL Database with new skills designed to enhance the management and operation of SQL-based applications.  

Extending end-of-support time

If you are ready to move to the cloud but feel challenged to upgrade or modernize before the end of the support timeline, Extended Security Updates are available for free in Azure for SQL Server 2014 and 2012 and Windows Server 2012. Secure your workloads for up to three more years after the end of the support deadline by migrating applications and SQL Server databases to Microsoft Azure Virtual Machines. Free Extended Security Updates are available for Azure Virtual Machines including Microsoft Azure Dedicated Host, Microsoft Azure VMWare Solution, Nutanix Cloud Clusters on Azure, and Microsoft Azure Stack (Microsoft Azure Stack Hub, Microsoft Azure Stack Edge, and Microsoft Azure Stack HCI). Combining Extended Security Updates in Azure with Azure Hybrid Benefit further reduces your costs. With these pricing advantages, AWS is up to five times more expensive than Azure for SQL Server and Windows Server end-of-support workloads. 

In-place upgrade to SQL Server 2022 

Another way to stay protected is to upgrade your SQL Server to SQL Server 2022, the most Azure-enabled release yet. Get more out of your data with enhanced security, industry-leading performance and availability, and business continuity through Azure. 

SQL Server 2022 is the most Azure-enabled release of SQL Server, with continued innovation across performance, security, and availability. Gain deeper insights, predictions, and governance from your data at scale. Take advantage of enhanced performance and scalability with built-in query intelligence. 

Stay protected on-premises or in multi-cloud environments with Azure Arc 

Just as with SQL Server 2012, Extended Security Updates for SQL Server 2014 offers an enhanced cloud experience through Microsoft Azure Arc. First year coverage from Extended Security Updates started on July 10, 2024. With this more customer-centric approach, security updates will be natively available in the Microsoft Azure portal through Azure Arc. This also provides Azure benefits and flexible subscription billing for SQL Server 2014 workloads on-premises or in multi-cloud environments. 

We’re continuing to enhance the capabilities Azure Arc offers to Extended Security Updates. Just recently, physical-core licensing with unlimited virtualization was released for SQL Server 2012 and 2014 ESUs. For customers who need to maximize database performance or require security isolation and better resource management, physical core licensing provides a more cost-effective way to leverage Extended Security Updates via Azure Arc. 

Also, if you enabled ESU subscription in your production environment managed through Azure Arc, you can enable SQL Server ESU subscription in the non-production environment for free, through SQL Server Developer Edition or an Azure dev/test subscription. 

We encourage all our customers running SQL Server 2014, Windows Server 2012, and Windows Server 2012 R2 to start planning for the end of support. We have migration resources, best practices, and more, as well as a rich ecosystem of partners ready to help. To get started, please visit the following pages to learn more. 

Learn More 

• Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers 
• Get the full ROI and TCO reports from IDC: The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload and The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads 
• Previous announcement on SQL Server 2014 End-of-Support moment: Protect SQL Server 2014 workloads with Azure flexibility – Microsoft SQL Server Blog

The post Modernize Microsoft SQL Server 2014 workloads with Azure appeared first on Microsoft SQL Server Blog.

We’ve been on the road the last couple of weeks at MVP Summit, SQLBits and Fabric Con, connecting with customers talking about the value of migration and modernization. We want to dig into specifically, how Azure can deliver real business value through cost optimization and streamlined productivity for their Windows Server and SQL Server deployments when they migrate to Azure. 

We’ve helped countless organizations migrate their SQL Server and Windows workloads to Azure a critical 1^st step in any transformation initiative. The move can help improve cybersecurity posture and business continuity, boost productivity, and lay the foundation for AI and other highly scalable data innovations, while automating updates, backups, and other time-consuming IT tasks. 

Modernize and lower total cost of ownership (TCO) 

Migration is a business strategy that pays off. In The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload,¹ organizations that migrated to Azure SQL Managed Instance and Microsoft Azure SQL Database can get up to 406 percent return on investment over 3 years and can expect a 30-percent reduction in TCO over 5 years, protecting an additional $6.85 million in annual revenue.

A separate study found that customers that migrated both Windows Server and SQL Server workloads to Azure generated more value. According to The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads,² by optimizing costs, operations, and business opportunities, companies gained $15.85 million in total annual benefits while also increasing IT security efficiency by 43 percent with cloud tools and automation.

Azure SQL

Migrate, modernize, and innovate with the modern SQL family of cloud database services.

Start today

A smooth path to migration, a more powerful destination

Migrating to a cloud platform is an essential step on the journey to modernization, and there are many choices. 

What makes SQL unique is that it’s built on the same engine, no matter where you deploy, which means you can build on your existing SQL experience while gaining the layered security, intelligent threat detection, and data encryption that Azure provides. And as we shared with customers at SQLBits, there’s now an even more powerful option available for customers looking to leverage the full PaaS experience. Azure SQL Managed Instance Next-gen GP  brings significantly improved performance and scalability to power up your existing Azure SQL Managed Instance fleet, and help bring more mission-critical SQL workloads to Azure. With close to 100 percent feature compatibility with SQL Server, Azure SQL Managed Instance is the recommended choice to migrate and modernize SQL apps at scale and at your own pace.

Another option many of our customers start with is by running their Windows Server workloads on Azure Virtual Machines, benefiting from a simplified, managed experience and cloud-native support for SQL Server, .NET apps, and Remote Desktop Services. Or you can modernize your entire Windows Server estate, choosing from more than 200 Azure services and capabilities, including support for hybrid environments. 

Take the first step or the next: You have choices

When it comes to migration, Azure meets you where you are with options for moving on-premises workloads and for developing new cloud solutions. For example, many organizations start by moving Windows Server workloads to Azure Virtual Machines, enabling them to easily scale to support new developments and more efficiently manage peak loads. Hokkoku Bank took this step, migrating its Windows Server–based estate to Azure as part of a cloud-first initiative. Azure supports the bank’s modernization plans and helps provide a disaster recovery solution in an earthquake-prone region.  

Correios de Portugal, the country’s postal service, migrated its Windows Server workloads to Azure Virtual Machines backed by Azure SQL, which provides a smooth path to a cost-effective, highly scalable, fully managed PaaS database. It’s the best choice for modernizing your apps and getting the most out of your existing investments.

Many of our database customers move to SQL Server on Azure Virtual Machines for the cost benefits on top of the scalability and resilience of Azure. As an example, healthcare software manufacturer Allscripts migrated on-premises applications to Azure SQL Database Managed Instance when possible, but another 600 on-premises VMs needed a different migration approach. Allscripts moved them to SQL Server on Azure Virtual Machines, a quick, low-risk step for workloads it plans to optimize and modernize later. The lift-and-shift approach can be an easy first   step in your cloud journey.

Azure also offers hybrid solutions that bridge your on-premises and cloud resources. For example, you can move or extend on-premises VMware environments using Azure VMWare Solution. You can even use the free Windows Admin Center tool to manage across Windows Server environments—physical, virtual, on-premises, in Azure, or in a hosted environment—at no additional cost. To get started with a Windows Server migration, start discovering and assessing on-premises resources using the free Azure Migrate tool.

Watch the Migrate to Innovate digital event on demand and learn the business benefits of migrating to Azure.

Try it for free 

If you want to know how your workload will perform before migrating, try these Azure offers and get started building that proof-of-concept.  

• Try Azure SQL Managed Instance for free. For 12 months, you can get up to two instances per Azure subscription, 750 vCore hours of compute per month, and 32 GB data storage and 32 GB backup storage per month. 

• Try Azure SQL Database for free. Test and develop applications or run small production workloads for free. This offer provides the first 100,000 vCore seconds, 32 GB of data, and 32 GB of backup storage per month at no charge for the lifetime of your subscription. 

• Try Windows Server in the cloud with an Azure free account. Extend your existing Windows Server datacenter and try out popular services free for 12 months. 

Learn more about Azure SQL

Stay tuned for more migration announcements in the coming months. To get started now: 

• Get the full ROI and TCO reports from IDC: The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workload and The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads.

• Review the benefits of migration in Better on Azure: The benefits of using Azure for Windows Server and SQL Server workloads. 

• Discover why cloud economics make sense and get greater return on your investment. 

1. IDC report, The Business Value of Microsoft Azure SQL Database and Azure SQL Managed Instance Workloads, IDC #US51073123, August 2023. 
2. The Business Value of Microsoft Azure for SQL Server and Windows Server Workloads

The post Why migrate Windows Server and SQL Server to Azure: ROI, innovation, and free offers appeared first on Microsoft SQL Server Blog.

We are excited to get back in front of customers at Microsoft Ignite 2023 and PASS Data Community Summit to announce powerful product enhancements across Microsoft Azure databases designed to help customers take the next step or the first step in their transformation journey, with databases that are intelligent, trusted, and ready for developers to build, without limits.   

Limitless innovation for cloud native applications

If you are an application developer looking for a flexible relational cloud database solution with performance and scalability to support your most demanding applications, you’ll want to check out Microsoft Azure SQL Database Hyperscale. Built on a unique architecture that splits the storage and compute nodes, these resources scale independently to meet the unique requirements of your apps. Plus, you can eliminate the need to pre-provision storage resources, as the storage automatically scales to meet demand, with support of up to 100 TB. We are thrilled to announce that we are introducing lower compute pricing on SQL Database Hyperscale, saving customers up to 35 percent on their compute bill. Effective December 15, 2023, customers will have competitive pricing on the resources they need to build scalable, secure, AI-ready applications. 

We’re also excited to share that the Microsoft Azure SQL Managed Instance feature wave has reached general availability. This set of features improves Azure SQL Managed Instance’s performance, reliability, and security. The latest release will deliver deeper integration with Microsoft SQL Server on-premises and the wider Azure service platform. And soon, customers will be able to start testing Azure SQL Managed Instance for free. Landing in December 2023, customers will be able to run proof of concepts, test applications or simply learn more about the operational benefits of a fully managed database-as-a-service. This is in addition to the free Azure SQL Database offer that launched in October 2023.  

Microsoft is also excited to share the newest updates for our fully managed community based open-source databases. These services help you manage your database and database infrastructure with automation, freeing you from the routine database management tasks so you can concentrate on what matters most.

Enhanced performance and scalability for Microsoft Azure Database for PostgreSQL

The latest enhancements for Azure Database for PostgreSQL deliver advanced storage and compute capabilities that enable optimal price-performance for enterprise production workloads. Customers can expect enhancements for advanced storage, compute capabilities, and flexibility for managing performance and cost.  

Azure Database for PostgreSQL extension for Azure AI

The PostgreSQL extension for Azure AI allows developers to use large language models (LLMs) and build rich PostgreSQL generative AI applications, meaning PostgreSQL queries on Azure can now power Azure AI applications. It enables calling into Microsoft Azure OpenAI Service to generate LLM-based vector embeddings that allow efficient similarity searches, which is particularly powerful for recommendation systems, as well as calling into Azure AI Language for a wide range of scenarios such as sentiment analysis, language detection, entity recognition, and more.

New performance enhancements in Microsoft Azure Database for MySQL Business Critical 

New performance enhancements in Azure Database for MySQL Business Critical service tier makes it ideal for high-performance transactional or analytical applications. In fact, a recent performance benchmark study by Principled Technologies shows that Azure Database for MySQL Business Critical service tier is up to 50 percent faster than MySQL on Amazon Web Services Relational Data Service and up to 2.26 times faster than Google Cloud Platform Cloud SQL for MySQL. These key innovations help make Azure Database for MySQL Business Critical the perfect option to run mission-critical, Tier 1 MySQL workloads.

Extend Azure to your entire data estate

For all the innovation that customers are driving in the cloud, we recognize much of the customer’s data remains on-premises. This is why Microsoft continues to invest heavily in ensuring that customers can get the most from their entire data estate with Microsoft Azure Arc. The latest monitoring capabilities from SQL Server-enabled by Azure Arc are designed to deliver critical insights across your entire SQL Server environments, optimizing database performance and delivering fast diagnostic times.  

Customers can also now improve SQL Server business continuity and consistency by viewing and managing Always On availability groups, failover cluster instances, and backups directly from the Azure portal. This capability provides better visibility and an easier, more flexible way to configure critical database operations.  

In addition, with Extended Security Updates as a service and automated patching, customers can always keep their apps secure, compliant, and up to date. Learn more about these latest features.

We look forward to the week ahead and connecting with you in person.

The post Power what’s next with limitless relational databases from Azure appeared first on Microsoft SQL Server Blog.

This year, DPS 2022 features eight tracks focusing on Azure Data: 

• Architecture 
• Azure Data Administration 
• Azure Data Development 
• Business Intelligence & Advanced Analytics 
• Data Science (AI/ML)
• Industry Solution 
• SQL Server 2022 
• Professional Development 
• Student Track 

The virtual platform offers a live Q and A session, a networking lounge, and a community zone. Additionally, attendees will receive lifetime on-demand access to session recordings. 

DPS 2022 offers an incredible opportunity to learn directly from our engineering teams, who will share the latest advances and insights on the Azure Data platform. 

• Bob Ward and Buck Woody from the Microsoft Data Platform team will deliver the keynote together. The keynote will highlight the latest innovations across the Microsoft Azure Data platform and share customer case studies. Bob and Buck will show you how to meet the needs of developers across the spectrum to leverage the new tools, processes, and platforms in the Intelligent Data Platform to create a data culture in your organization. Bob will go deep on the newest release in the Intelligent Data Platform—SQL Server 2022. 
• Microsoft Azure Data engineering teams will deliver over 65 sessions at DPS 2022. Hear the latest from the people who develop the tools you use every day and engage in live discussions. 
• Visit the virtual expo hall where you can connect with our team across SQL Server, Azure SQL, Microsoft Learn, Power BI, and more. 

Register now for a week of free training at the Data Platform Virtual Summit and receive all the session recordings also for free (streaming access). 

The post Register today—Free Azure Data training at Data Platform Summit 2022 appeared first on Microsoft SQL Server Blog.

Over the past several SQL Server releases, Microsoft has improved the concurrency and performance of the tempdb database. In SQL Server 2022 we are addressing one of the last areas of contention by introducing concurrent global allocation map (GAM) and shared global allocation map (SGAM) updates which will give SQL Server 2022 a big improvement for scalability as tempdb is arguably the most important database in your environment.

Tempdb performance challenges

Historically, tempdb has been one of those common pain points in SQL Server. Why was it a pain point? Well, usage is one of the key reasons. By usage, we are referring to creating temp tables and other user objects, but tempdb is also used internally to spill to disk when there isn’t enough memory available for a process or there is an inaccurate estimate that causes SQL Server to spill to tempdb.

What is the tempdb database?

Tempdb is a special purpose system database, but the structure is essentially just like any other user database. As the name suggests, the tempdb database was designed for temporary storage meaning that nothing written to tempdb is intended to be persisted.
What is important to know is that while SQL Server uses the tempdb database for nearly every SQL Server workload, there is only one tempdb per SQL Server instance and tempdb is recreated every time SQL Server is restarted.

Tempdb workloads

The main difference between tempdb and other databases is the workload. With tempdb we are constantly creating and destroying objects such as temp tables. This is especially true in heavy OLTP environments where you may have many threads doing all kinds of work and if you are already seeing resource contention on the system the impact will be amplified.

So, what is stored in tempdb?

Of course, temp tables and table variables will go into tempdb – this is usually the first object type we think about. So, whenever you create a temporary table in a stored procedure or in a regular batch, that is going to go to tempdb.

Row versions go into tempdb, if you are using snapshot isolation, read committed snapshot where every time a row is modified by a specific transaction, the database engine will store a version of the previously committed image of the row in tempdb.

Hash operations will spill to tempdb. Worktables are also used for spools, cursors, sorts, and temporary large object (LOB) storage – this will all go to tempdb.

Triggers use the row version store in tempdb—this will go to tempdb.

Online index operations—if you are maintaining your indexes with the ONLINE ON keyword then we are creating temporary shadow copies in tempdb.

DBCC CHECKDB also creates shadow copies in tempdb

As you can see, there’s a lot that goes into tempdb—tempdb will be used for user objects such as global or local temporary tables and indexes, stored procedures, table variables, table-valued functions, and cursors. But we also use tempdb for internal scenarios—such as spills to disk and as worktables that store intermediate results for spools and sorts.

What causes contention in tempdb?

Since tempdb is used for so many different scenarios, there is only one tempdb database per SQL Server instance, and we have started pushing towards bigger machines with larger workloads, we have started seeing concurrency issues emerge in the tempdb space in three key areas:

1. Object allocation contention
2. Metadata contention
3. Temp table cache contention

What is object allocation contention?

Again, tempdb is structured just like any other database, but remember—the workloads are different in tempdb, so object allocation contention matters more because of the constant creation and destruction of objects.

On a server that is experiencing object allocation contention, you may notice severe blocking especially when the server is experiencing a heavy load. As a result, SQL Server workloads will be slowed, but the server’s CPU may appear to be underutilized. This is because the contention resides in the system metadata. To help avoid these areas of contention, a number of long-standing best practices have been recommended by SQL Server support teams.

For tempdb, one of the key best practices has always been to create multiple primary data files (mdf) at the same size and same growth rate. The reason for this was to help alleviate the object allocation contention by distributing tempdb activity across multiple partitions.

SQL Server databases must have a primary data file that uses the .mdf file extension by default and a log data file that uses the .ldf file extension. The primary tempdb data file, tempdb.mdf has key pages that track how objects are allocated in SQL Server. As we see in the image below, the table under the tempdb.mdf title represents pages.

Page 0 is a header page, and this is true for any primary or secondary data files in SQL Server.

Page 1 is what is called a page free space (PFS) page which is used any time SQL needs to allocate space to an object. Basically, the PFS page contains info on how full the pages are, for the next 8088 pages, in the database. If SQL Server needs to add some data, SQL Server uses the PFS page to see how full the associated object is to see where the data can fit.

After 8088 pages, there is another PFS page in the same data file – it repeats itself. So, you will have more than one PFS page depending on how large the file is.

Page 2 is always the global allocation map (GAM), and this is where the extent allocation comes in as the GAM tracks when SQL Server needs to allocate a uniform extent.

An extent in SQL Server is comprised of 8 x 8KB pages, 64KB, and this is usually the unit of data allocation so if you have a table that’s larger than eight pages, any time we allocate space to that table we will create a full extent, and this is a uniform extent because all eight pages in that extent belong to that object.

So, any time SQL needs to allocate a uniform extent to an object SQL will go to the GAM page and check the availability. The GAM is a bitmap, so if the bit is 1 then that extent is available to be allocated, and if it is 0 then it is not available to be allocated. Once SQL Server has allocated the extent, it just flips the bit for that GAM page from 1 to 0 to show that it is no longer available.

The larger the file, the more GAM Pages you will have, for the GAM you will get another GAM page after 63,904 extents.

Page 3 is for the shared global allocation map (SGAM) and this page is used if SQL needs to allocate space on a mixed extent. This SGAM page tracks mixed extent usage for when an extent is being used by more than one object. So, if I have an object that is less than eight pages and I don’t want to allocate a full extent, we will use a mixed extent. By default, when a brand-new object is created the first eight pages will be allocated on a mixed extent.

The SGAM is a bitmap, so if the bit is 1 then it’s being used as a mixed extent and has space available to be allocated, then we would look for the corresponding PFS page to find the empty pages within that extent, and then we would allocate that page. The important point here is the SGAM is used in conjunction with the PFS page in order to allocate space on a mixed extent and again after about 64,000 extents you get another SGAM on the same file.

When you have multiple files in tempdb, you get another immediate header, PFS, GAM, and SGAM because all files will have the same structure and with multiple files we try and share the workload over these files.

SQL Server spreads out object allocations across files in the same filegroup based on the proportional fill algorithm. We try to keep the same percentage of free space in each file within the file group so if all the files in the file group start at the same size and they stay the same size and they have the same amount of free space then we turn that proportional fill algorithm into a round robin algorithm—each subsequent allocation will hit the next file and so on—and that was the reason we recommend having multiple files at the same size—spreading object allocations across all the files allows you to get around this object allocation bottleneck. That recommendation came out in SQL Server 2000, and it is still true in SQL Server 2019.

Multiple files equally sized is our best practice and this will stand until testing proves otherwise.

Tracking object allocation contention

Prior to SQL Server 2019, the best approach was to monitor the sys.dm_os_waiting_tasks dynamic management view and log the contention history over time.

Consider the SQL Server statement listed below:

When looking at the wait resource, you can monitor the contention knowing that the first number refers to the database, the second number is the file id, and the last number is the page type.

This means that contention on wait resource 2:7:2 is tempdb contention as tempdb database is always database id 2, there is contention on file id #7 with GAM contention as the figure illustrates (page #1 is the PFS, #2 is the GAM, and #3 is SGAM).

These wait resource references are commonly in the format 2:1:1, 2:1:3, and so on.

Any results found on database id 2 indicate that there are requests waiting for tempdb resources and the accumulation of these requests can help database administrators narrow down the root cause of the contention.

In SQL Server 2019 we created new functions to improve tempdb troubleshooting. The sys.fn_PageResCracker dynamic management function returns the db_id, file_id, and page_id for the given page_resource value and sys.dm_db_page_info dynamic management function returns page information like page_id, file_id, index_id, object_id, and more that are present in a page header. This information is useful for troubleshooting and debugging various performance (lock and latch contention) and corruption issues.

The example query below can be used to better resolve wait resource information for any SQL Server release post SQL Server 2019:

What is metadata contention?

The other main type of contention is called metadata contention. This type of contention is not about I/O. This contention occurs in memory when multiple threads are trying to modify the same page in memory at the same time.

You can track metadata contention using the same methods you would use to track object allocation contention, the difference is instead of the wait resource being 2:1:1, 2:1:2, 2:1:3 on the PFS, GAM, and SGAM, you are more likely to see the contention occurring on index and data pages and the page number in the wait resource will be a higher value such as 2:1:111, 2:1:118, or 2:1:122, for example.

For metadata contention it is useful to make note of page numbers greater than single digits, track the object name, and the page type description. The object names will show as system tables such as sysallocunits, syscolpars, sysjobactivity, sysscalartypes, sysschobjs, and so on.

Metadata contention was addressed in SQL Server 2019 with the memory-optimized tempdb metadata improvement.

Memory-optimized metadata tables for tempdb is basically a combination of the in-memory OLTP and the temp table metadata features. We took the system tables and the tempdb system tables—and we moved those into non-durable memory-optimized tables.

Remember, that tempdb is temporary—it gets dropped and recreated every time you restart SQL Server so there was no reason for the metadata to be durable. We converted the 12 system tables that are involved in object tracking and tempdb into memory optimized non-durable tables.

We don’t need a specialized memory-optimized file group for tempdb since it is non-durable anyway. All of it is “in memory”—no disk is needed and with memory-optimized tables there’s no latching and no locking. We can massively increase the concurrency against these metadata tables using these lock-free, latch-free data structures.

Enabling memory-optimized metadata tables does require a restart since we must configure the Hekaton DLLs. This was a big improvement in SQL Server 2019 that will eliminate a lot of the metadata contention.

Learn more about how this improvement removes the metadata contention bottleneck for tempdb-heavy workloads in our memory-optimized tempdb documentation.

Temp table cache contention

In past SQL Server releases starting with SQL Server 2005, we introduced temp table caching to get around some of the metadata caching contention. Basically, when you cache a temp table object—when you delete that table SQL Server doesn’t actually drop the metadata—we keep a cache of all the temporary objects that are used through a stored procedure and then we reuse the metadata for those objects when you call the same stored procedure with the same temp table again.

As a result, temp table caching has fewer hits to the metadata and alleviates some of that metadata contention—but not completely.

Temp table caching helped address metadata contention by allowing us to reuse tables that didn’t change between stored procedure executions. As long as the table was not altered after it was created, it would be eligible to be reused by another execution of the same stored procedure.  However, if the table is altered (by adding an index or a column, for example), then it can’t be reused and must be dropped when the stored procedure completes.

There are several different tables that we need to delete metadata from in order to completely drop the table, and this was all being done synchronously at the end of the stored procedure execution. Additionally, every time a new feature is added to SQL Server (ColumnStore indexes, temporal tables, In Memory OLTP, etc.) all these new features require new metadata to be tracked, therefore the number of system tables we need to delete from is increasing, which makes the process more impactful.

Temp table cache contention can be more prominent on larger SQL Server environments, larger core counts and as the size of the cache and the number of concurrent threads accessing the cache grows, this can introduce slower cache access as well as contention for the memory object associated with the cache.

This condition can manifest in two different ways: CMEMTHREAD waits and SOS_CACHESTORE spinlock waits. To address temp table cache contention, it is recommended to track these wait conditions for evidence and ensure you have installed the latest cumulative updates (CUs) for SQL Server.

Check out our Tech Community blog for more information on temp table caching in SQL Server.

SQL Server 2022 tempdb improvements

In SQL Server 2022 we have addressed the final common areas of contention by introducing concurrent GAM and SGAM updates similar to the Concurrent PFS updates.

We use the Global Allocation Map (GAM) pages when we are looking for uniform extents and the Shared Global Allocation Map (SGAM) pages when we are looking for mixed extents in tempdb.

In previous releases, under higher concurrent workloads we may have GAM contention where many different threads attempt to allocate extents on the same GAM page and each thread must first wait for another thread to release their UPDATE latch before they can obtain their own latch to allow them to make changes—so, we are just waiting in line.

As can be seen in the workload example below, on SQL Server 2019 there is a wall of GAM contention driving over 123,000 counts of contention with the longest wait taking 949 milliseconds.

The reason for this is that with the update latch, only one thread can modify the GAM page at a time, leading to contention. This is the primary reason we still need multiple data files and because of this contention, SQL Server throughput is decreased and workloads that require many updates to the GAM page will take longer to complete while the machine’s CPU will be underutilized. This contention is due to the workload volume and especially the use of repetitive create-and-drop operations.

Starting with SQL Server 2016, we changed the default behavior to always allocate uniform extents when creating new objects in tempdb. This helped avoid most of the SGAM contention, but we still use mixed extents for Index Allocation Map (IAM) page allocations. IAM pages are used to track all the pages that belong to an object, so every object that gets created has at least one IAM page. For most workloads, these IAM page allocations don’t cause any issues, but for extremely busy tempdb workloads with many threads of concurrent allocations, these IAM page allocations can still cause SGAM contention.

SQL Server 2022 addresses GAM and SGAM contention

SQL Server tempdb contention is near completely addressed in SQL Server 2022 and these benefits are on by default. With these improvements in SQL Server 2022 we allow concurrent updates to the GAM and SGAM under a shared latch rather than using the update latch. This improvement erases nearly all tempdb contention allowing parallel threads to be able to modify the GAM and SGAM pages as can be seen in the example below.

In the SQL Server 2022 workload example shown here, we only have 607 points of contention over the same time period compared to SQL Server 2019 with the longest wait at only 342 milliseconds. The only contention in the environment was metadata contention in this example because we did not enable the SQL Server optimized tempdb metadata improvement.

There are still possible points of metadata contention, but with SQL Server 2022, the points of contention will be rare and should not lead to any significant performance challenges.

If concurrent GAM and concurrent SGAM updates are some of the last areas of contention, do we still need the best practices to maintain multiple data files for tempdb?

Out of the gate, we are going to continue recommending the same best practices, but we may adjust if we find that it is no longer required through customer feedback.

Summary

In SQL Server 2022 we have improved tempdb performance to a factor that may need to revise the tempdb best practices that have stood true for nearly a quarter of a century.

We have greatly improved the performance of tempdb. So much of what runs on SQL Server relies on tempdb, these enhancements will likely be more than enough to make SQL Server 2022 a mandatory upgrade in most organizations.

The key point is that it’s important for DBAs to optimize tempdb performance, it’s important to track and address tempdb bottlenecks, and SQL Server has improved tempdb in every single release—SQL Server 2022 is no exception.

Next steps

System page latch concurrency enhancements in SQL Server 2022 are just one of the many benefits of migrating to SQL Server 2022.

Download the latest release of SQL Server 2022 if you haven’t already done so and check out the SQL Server 2022 Overview and What’s New references. There are many new features and improved functionality being added to this release.

Learn more

For more information and to get started, check out the following references:

Read What’s New in SQL Server 2022

Watch the Data Exposed SQL Server 2022 overview video: SQL Server 2022 Storage Engine Capabilities (Ep. 6) | Data Exposed

Additional useful resources:

• Memory-optimized tempdb metadata (SQL Server 2019)
• tempdb – Files and Trace Flags and Updates, Oh My! (SQL Server 2019)

Dynamic Management View (DMVs):

• sys.dm_os_waiting_tasks (Transact-SQL)
• sys.fn_PageResCracker (Transact-SQL)
• sys.dm_db_page_info (Transact-SQL)

The post Improve scalability with system page latch concurrency enhancements in SQL Server 2022 appeared first on Microsoft SQL Server Blog.

This year, DPS 2020 features five parallel tracks focusing on Azure Data:

• Advanced Analytics
• Artificial Intelligence
• Azure Data Administration
• Azure Data Development
• Power BI

The virtual platform offers live Q and A, a networking lounge, a community zone, and technical round tables. Additionally, attendees will receive 12 month on-demand access to session recordings.

DPS 2020 offers an incredible opportunity to learn directly from our engineering teams, who will share the latest advances and insights on the Azure Data platform.

• Rohan Kumar will deliver the keynote. Rohan will highlight the latest innovations across the Microsoft Azure Data platform and share customer case studies. The keynote will also feature demos from multiple Microsoft engineers, including Anitha Adusumilli, Anna Hoffman, Buck Woody, Travis Wright, and Vasiya Krishnan.
• Microsoft Azure Data engineering teams will deliver over 35 sessions at DPS 2020. Hear the latest from the people who develop the tools you use every day, and engage in live discussions.
• Visit the virtual expo hall where you can connect with our team across SQL Server, Azure SQL, Azure Synapse Analytics, Power BI, and more.

Register now for a week of training at Data Platform Virtual Summit and receive twelve months of on-demand access to the DPS 2020 sessions.

The post Microsoft Azure at Data Platform Virtual Summit 2020 appeared first on Microsoft SQL Server Blog.

This year, PASS is taking full advantage of the virtual platform and partnering with Microsoft to make your PASS Virtual Summit experience second to none. Learn directly from our engineering team, who will be sharing the latest insights and advances on the data platform, providing in depth training across key tools and topics, and engaging live for one to one support to help you solve your challenges in real time.

If you haven’t already heard, Rohan Kumar, Microsoft Corporate Vice President of Azure Data, will be back this year for the Day 1 keynote. Rohan will showcase the latest innovation across the Microsoft Data Platform, including exclusive announcements at PASS Virtual Summit. Additionally, in the Day 2 keynote, Technical Fellow, Hanuma Kodavalla will discuss SQL eco-system transformation and innovation at scale.

After the keynotes, ground your learning with in-depth training in one of the over thirty sessions Microsoft will be delivering. Hear the latest from the teams who develop the tools you use every day, and ask or upvote questions through the Interactive Platform. After your sessions, don’t forget to visit the virtual exhibit hall where you can connect with our team across SQL Server, Azure SQL, Azure Synapse Analytics, Power BI, and more.

The Microsoft Azure Data Clinic will be back again this year, providing you with one to one access to the engineers behind the products you love. This year we are taking full advantage of the virtual platform, to allow you to pre-book, scheduling your clinic appointment around those must-attend networking activities, and ensuring that you are connected to the right expert for your challenge.

Register for PASS Virtual Summit today for an incredible week of training, and 12 months on-demand access to world class learning.

The post Microsoft Azure at PASS Virtual Summit 2020 appeared first on Microsoft SQL Server Blog.

To help answer these questions and much more, we are hosting the Azure SQL digital event: Transform your applications with Azure SQL on October 14 at 10:00 AM Pacific Time. During this hour-long virtual event, I’ll share my advice and guidance on getting the most out of Azure SQL, whether you’re looking to migrate from an on-premises SQL Server deployment or exploring ways to utilize the newest Azure SQL service options. You will also hear firsthand experiences and best practices from customers who have become expert Azure SQL users.

Regardless of your familiarity with Azure SQL, we want to help you learn how to scale in response to rapidly changing needs, boost the resilience of your mission-critical workloads, and make deployments more cost-effective. We’ll present and offer demonstrations on topics including:

• Choosing the best deployment option for SQL Server in Azure, from Virtual Machines to fully managed databases.
• Selecting, setting up, operating, and scaling your database quickly by putting your existing SQL Server experience to work in the cloud.
• Best practices to ensure optimized database performance and scalability.
• Strategies for selecting the right service tier to maximize the resiliency of critical workloads—without breaking the bank.

We’ll also be taking a deeper dive into two of our newest and most exciting service tiers for Azure SQL Database: Serverless and Hyperscale. Through demos and interviews with current customers, we’ll show how to use these capabilities to create groundbreaking deployments that are both cost-effective and highly performant.

Finally, we’ll have a Q&A session where a panel of Azure SQL subject-matter experts and I will be eager to take your questions and point you on the right track in your Azure SQL journey. If you haven’t registered for the Azure SQL Digital Event, sign up now at the Azure SQL digital event page.

The post Azure SQL digital event: Transform your applications with Azure SQL appeared first on Microsoft SQL Server Blog.

Whether you’re rehosting your SQL Server workloads onto an Azure Virtual Machine, or modernizing them on evergreen, fully managed Azure SQL Database or Azure SQL Managed Instance, you’re shifting your capex to opex by avoiding the cost and complexity of buying and managing physical servers.  On top of that, Azure provides flexible management options that can boost productivity and provide more bandwidth for IT and database administrators alike to focus on more value-added initiatives. Backups, patching, and updates are performed on your behalf, and features like high availability, performance tuning, and more are part of Azure SQL’s managed services.

What more could you do if you could save up to 80 percent or more on your database costs? Read on to discover the top eight ways you can optimize your data spend on Azure.

The post More ways to save when you migrate SQL Server data to Azure SQL appeared first on Microsoft SQL Server Blog.