2005, Database, Performance and Servers - Technology Performance Pulse

Additional T-SQL Improvements in SQL Server 2022

SQL Performance

AUGUST 1, 2022

Recently I covered some of the T-SQL improvements in SQL Server 2022 here , and Aaron Bertrand covered additional improvements here. Microsoft just announced the release of SQL Server 2022 CTP 2.1. I’ll be using the sample database TSQLV6 in the examples in this article. You can download this sample database here.

Servers

Servers Testing C++ Code

Get details of SQL Server Database Growth and Shrink Events

SQL Shack

JUNE 25, 2019

It is essential for the DBA to need to ensure the SQL Server database performance. Performance tuning is an open-ended task, and you need to ensure the monitoring of various database parameters.

Database

Database Servers Tuning Monitoring

Refreshing SQL Server Tables With Less Interruption Using Partition Switching

SQL Performance

SEPTEMBER 29, 2021

A common requirement in ETL and various reporting scenarios is to quietly load a SQL Server staging table in the background, so users querying the data aren’t impacted by the writes and vice-versa. Refreshing Tables in SQL Server. SQL Server Partition Switching Example. Compared to the Old Way of SQL Server Partition Switching.

Servers

Servers Games Database Processing

Performance Tuning Re-indexing and Update Statistics – A Case Study

SQL Server Performance

NOVEMBER 10, 2017

Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance.

Tuning

Tuning Performance Servers Database

Simple Parameterization and Trivial Plans — Part 2

SQL Performance

MARCH 29, 2022

When SQL Server applies simple parameterization to an ad-hoc statement, it makes a guess about the data type of the replacement parameter. For the time being, let’s look at some examples using the Stack Overflow 2010 database on SQL Server 2019 CU 14. The bigint type isn’t used for server-side parameterization.

Cache

Cache Servers Database Testing

Deprecated features to take out of your toolbox – Part 3

SQL Performance

JULY 1, 2021

There was the case where a colleague constantly promoted the deprecated backward compatibility view sys.sysprocesses instead of newer dynamic management views (DMVs), and another case where a different colleague took down a production server using SQL Server Profiler. The operation must be performed offline. USE BadIdeas ; GO.

C++

C++ Database Servers Performance

Deprecated features to take out of your toolbox – Part 1

SQL Performance

FEBRUARY 22, 2021

I would like to share a couple of recent examples that help illustrate how using deprecated SQL Server features continues to bite us. The system table sys.sysprocesses was replaced way back in SQL Server 2005 by a set of dynamic management views (DMVs), most notably sys.dm_exec_requests , sys.dm_exec_sessions , and sys.dm_exec_connections.

Latency

Latency Servers Monitoring Systems

The Amazing Evolution of In-Memory Computing

ScaleOut Software

JUNE 22, 2020

The pace of these changes has made it challenging for server-based infrastructures to manage fast-growing populations of users and data sources while maintaining fast response times. They transparently distribute stored objects across the cluster’s servers and ensure that data is not lost if a server or network component fails.

Ecommerce

Ecommerce Cache Scalability IoT

The Amazing Evolution of In-Memory Computing

ScaleOut Software

JUNE 22, 2020

The pace of these changes has made it challenging for server-based infrastructures to manage fast-growing populations of users and data sources while maintaining fast response times. They transparently distribute stored objects across the cluster’s servers and ensure that data is not lost if a server or network component fails.

Ecommerce

Ecommerce Cache Scalability IoT

Overlooked T-SQL Gems

SQL Performance

OCTOBER 9, 2019

In my examples I'll use a sample database called TSQLV5. You can find the script that creates and populates this database here , and its ER diagram here. The EOMONTH function was introduced in SQL Server 2012. SQL Server 2017 introduced support for the function TRIM. SQL Server 2012 introduced the function CONCAT.

C++

C++ Database Servers Code

SQL Server On Linux: Forced Unit Access (Fua) Internals

SQL Server According to Bob

DECEMBER 18, 2018

SQL Server relies on Forced-Unit-Access (Fua) I/O subsystem capabilities to provide data durability, detailed in the following documents: SQL Server 2000 I/O Basic and SQL Server I/O Basics, Chapter 2. Be sure to deploy SQL Server 2017 CU6 or newer for best data durability and performance results. “.

Servers

Servers Media Cache Storage

SQL Server I/O Basics Chapter #2

SQL Server According to Bob

JANUARY 11, 2020

Microsoft, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Servers

Servers Cache Database Media

Observability platform vs. observability tools

Dynatrace

DECEMBER 22, 2021

With observability, teams can understand what part of a system is performing poorly and how to correct the problem. Traces provide performance data about tasks that are performed by invoking a series of services. A database could start executing a storage management process that consumes database server resources.

Artificial Intelligence

Artificial Intelligence Metrics Architecture DevOps

Why Waits Alone Are Not Enough

SQL Performance

JANUARY 23, 2020

"Waitstats helps us identify performance-related counters. But waits information by itself isn't enough to accurately diagnose performance problems. The queues component of our methodology comes from Performance Monitor counters, which provide a view of system performance from a resource standpoint.".

Tuning

Tuning Servers Monitoring Hardware

Simple Parameterization and Trivial Plans — Part 1

SQL Performance

MARCH 22, 2022

Both target performance and efficiency for workloads frequently submitting simple statements. This series doesn’t dwell too long on the basics but concentrates on less well-known aspects likely to trip up even the most experienced database professionals. ALTER DATABASE SCOPED CONFIGURATION. Simple Parameterization. Shell Plans.

Cache

Cache Servers Database Code

Fundamentals of table expressions, Part 5 – CTEs, logical considerations

SQL Performance

AUGUST 12, 2020

In my examples I’ll use a sample database called TSQLV5. So it could be that the term originated in one of the database products and later adopted by some of the other database vendors. So it could be that the term originated in one of the database products and later adopted by some of the other database vendors.

C++

C++ Code Design Database

Top 8 Best Backend Frameworks

KeyCDN

MARCH 28, 2019

They are responsible for the implementation of database systems, ensuring proper communication between various web services, generating backend functionality, and more. Laravel also offers its own database migration system and has a robust ecosystem. Check out how to perform an Express CDN integration with KeyCDN.

Programming

Programming Website Scalability Design

How Parallel Plans Start Up – Part 1

SQL Performance

MARCH 30, 2021

This five-part series takes a deep dive into the way SQL Server row mode parallel plans start up. It includes initializing each operator, and adding hidden profilers to collect runtime performance data like actual row count and elapsed time. I used the public Stack Overflow 2013 database ( download details ).

Cache

Cache Servers Operating System Processing

AWS EC2 Virtualization 2017: Introducing Nitro

Brendan Gregg

NOVEMBER 29, 2017

Hardware virtualization for cloud computing has come a long way, improving performance using technologies such as VT-x, SR-IOV, VT-d, NVMe, and APICv. The latest AWS hypervisor, Nitro, uses everything to provide a new hardware-assisted hypervisor that is easy to use and has near bare-metal performance.

Virtualization

Virtualization AWS Hardware Storage

SQL 2016 – It Just Runs Faster Announcement

SQL Server According to Bob

JUNE 3, 2016

SQL Server 2016 ‘It Just Runs Faster’ A bold statement that any SQL Server professional can stand behind with confidence. Try SQL Server 2016 Today. The SQL Server Development team tasked several individuals with scalability improvements and real world testing patterns. – [link]. Follow-up.

Hardware

Hardware Software Engineering Transportation Scalability

AWS EC2 Virtualization 2017: Introducing Nitro

Brendan Gregg

NOVEMBER 28, 2017

Hardware virtualization for cloud computing has come a long way, improving performance using technologies such as VT-x, SR-IOV, VT-d, NVMe, and APICv. The latest AWS hypervisor, Nitro, uses everything to provide a new hardware-assisted hypervisor that is easy to use and has near bare-metal performance.

Virtualization

Virtualization AWS Hardware Storage

Technology Performance Pulse

Additional T-SQL Improvements in SQL Server 2022

Get details of SQL Server Database Growth and Shrink Events

Trending Sources

Refreshing SQL Server Tables With Less Interruption Using Partition Switching

Performance Tuning Re-indexing and Update Statistics – A Case Study

Simple Parameterization and Trivial Plans — Part 2

Deprecated features to take out of your toolbox – Part 3

Deprecated features to take out of your toolbox – Part 1

The Amazing Evolution of In-Memory Computing

The Amazing Evolution of In-Memory Computing

Overlooked T-SQL Gems

SQL Server On Linux: Forced Unit Access (Fua) Internals

SQL Server I/O Basics Chapter #2

Observability platform vs. observability tools

Why Waits Alone Are Not Enough

Simple Parameterization and Trivial Plans — Part 1

Fundamentals of table expressions, Part 5 – CTEs, logical considerations

Top 8 Best Backend Frameworks

How Parallel Plans Start Up – Part 1

AWS EC2 Virtualization 2017: Introducing Nitro

SQL 2016 – It Just Runs Faster Announcement

AWS EC2 Virtualization 2017: Introducing Nitro

Stay Connected