SQL Server According to Bob

SEPTEMBER 26, 2016

When we released Always On Availability Groups in SQL Server 2012 as a new and powerful way to achieve high availability, hardware environments included NUMA machines with low-end multi-core processors and SATA and SAN drives for storage (some SSDs). As we moved towards SQL Server 2014, the pace of hardware accelerated.

Availability 40

Availability Servers Hardware Benchmarking 40

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].

Hardware 40

Hardware Software Engineering Transportation Scalability 40

SQL Server According to Bob

JUNE 15, 2016

There is a MSVC, runtime library patch needed by SQL Server 2016 and without the patch the SQL Server service can simply terminate (crash.) This may not produce stack dumps and the SQL Server error log often looks like it simply terminates (no logging of shutdown.).

Software Engineering 40

Software Engineering Servers Database Engineering 40

SQL Performance

JUNE 21, 2019

I've been teaching and writing about common SQL Server mistakes for many years. This article will expand on my previous article and point out how these apply to SQL Server , Azure SQL Database , and Azure SQL Managed Instance. SQL Server Agent alerts. This situation applies to on-premises SQL Server and IaaS.

Servers 49

Servers Azure Hardware Database 49

SQL Server According to Bob

JUNE 3, 2016

SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale far better than previous releases. The following chart shows the same 1TB database testing. SQL Server. SQL Server 2014.

Hardware 40

Hardware Servers Cache Software Engineering 40

Percona

JANUARY 24, 2023

Some of the built-in features ( wal_compression ) have been there since 2016, and almost all backup tools do the WAL compression before taking it to the backup repository. Such “torn pages” are corruptions from the database point of view. This is generally referred to as “ partial page writes ” or “ torn pages.”

Database 121

Database Benchmarking Open Source Latency 121

Percona

FEBRUARY 2, 2023

Recall that logical replication works by using the PUB/SUB mode, where individual tables are published and are then subscribed by remotely connected databases/tables. The only real limitation is that one can only publish tables from a read-write server such as the PRIMARY. DML operations, such as INSERT, UPDATE, DELETE, TRUNCATE, etc.,

Games 95

Games Open Source Tuning Servers 95

Percona

AUGUST 18, 2023

Why do you need multiple instances on the same server? Why not just create another database on the same instance? You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs. ln -s /usr/local/Percona-Server-8.0.19-10-Linux.x86_64.ssl101/

Programming 89

Programming Servers Database Systems 89

Percona

SEPTEMBER 19, 2023

This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur. Physical backups are the backups that consist of raw copies of the directories and files that store database contents. Copyright (c) 2016, 2023, Oracle and/or its affiliates. Type 'help' or '?'

C++ 112

C++ Servers Storage Speed 112

SQL Performance

AUGUST 14, 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. SQL Server 2019 introduces batch mode on rowstore support, so you can get batch-mode processing even if there's no columnstore indexes present on the data.

Best Practices 108

Best Practices Servers Database Azure 108

SQL Performance

FEBRUARY 1, 2022

This is done to make managing operations in the transaction log easier for the log manager in SQL Server. You can’t specify how many VLFs are created by SQL Server when the database is first created or the log file automatically grows, but you can influence it.

Architecture 53

Architecture Servers Virtualization Database 53

SQL Performance

JUNE 9, 2021

That is, ones that are created as an object in the database, and stay there permanently unless dropped. In my examples I’ll use a sample database called TSQLV5. A table could be a base table defined as an object in the database, or it could be a table returned by an expression—more specifically, a table expression.

Database 140

Database Code Design Development 140

Alex Podelko

OCTOBER 20, 2016

I am looking forward to share my thoughts on ‘Reinventing Performance Testing’ at the imPACt performance and capacity conference by CMG held on November 7-10, 2016 in La Jolla, CA. I decided to publish a few parts here to see if anything triggers a discussion. It would be published as separate posts: – Introduction (a short teaser).

Performance Testing 100

Performance Testing Architecture Testing Performance 100

SQL Performance

MAY 1, 2020

In a recent tip , I described a scenario where a SQL Server 2016 instance seemed to be struggling with checkpoint times. Still, if you stack a bunch of high-transaction databases on there, checkpoint processing can get pretty sluggish. A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions.

C++ 58

C++ Database Servers Storage 58

SQL Performance

JUNE 5, 2019

One of the more perplexing problems to troubleshoot in SQL Server can be those related to memory grants. SQL Server’s optimizer estimates how much memory is needed, and the query must obtain the memory grant in order to start executing. ALTER DATABASE [ WideWorldImporters ] SET QUERY_STORE = ON ; GO. sort, hash).

Servers 41

Servers Cache Database Metrics 41

SQL Performance

JULY 16, 2019

A close monitoring of the hardware enthusiast community, including many of the most respected hardware analysts and reviewers paints an even more dire picture about Intel in the server processor space. Despite all of this, Intel is not going to lose their entire server processor business any time soon. So, what has changed my mind?

Servers 46

Servers Hardware Architecture Retail 46

Alex Russell

MARCH 6, 2021

Back in 2016, I gave a talk outlining the causes and effects of the terrible performance of web apps built using popular tools on the fastest-growing device segment: low-end to mid-range Android phones. In 2016, Jio swept over the subcontinent like a monsoon dropping a torrent of 4G infrastructure and free data rather than rain.

Performance 127

Performance Network Metrics Cache 127

SQL Performance

DECEMBER 9, 2020

You can create supporting tables in the user database if needed. I tested my solutions in SQL Server 2019 Enterprise edition. SQL Server chose row mode processing for all previously shown solutions by default. There are a couple of ways to get SQL Server to use batch processing here. You can add hints as needed.

C++ 114

C++ Servers Testing Processing 114

SQL Server According to Bob

JULY 13, 2016

20+ years ago when I joined Microsoft I was handed a diskette (maybe it was two), and was told “Here is SQL Server. So I proceeded to install SQL Server 4.20 There was a GUI as part of setup but within just a few clicks, SQL Server was installed and ready for use. So now there are more choices to make when installing SQL Server.

Servers 40

Servers Media Engineering Virtualization 40

Percona

APRIL 12, 2023

Whereas many open source software offerings — like the community version of MySQL — use the GNU General Public License ( GPL ), MongoDB has been under the GNU Affero General Public License ( AGPL ) and more recently under the Server Side Public License ( SSPL ), introduced by MongoDB itself. Is MongoDB free to use?

Open Source 86

Open Source Programming Database Servers 86

SQL Server According to Bob

NOVEMBER 8, 2016

SQL Server 2016 Service Pack 1 (all SKUs) , in combination with Windows Server 2016 (All SKUs) or Windows 10 Client introduces non-volatile memory support for the tail of the log file (LDF) which can significantly increase transaction throughput. Create / Alter Database … LOG ON. NVDIMM is measured in nanoseconds.

Cache 40

Cache Servers Media Storage 40

SQL Server According to Bob

JANUARY 26, 2017

My goal is to resolve mysteries about SQL Server I encounter but do this without going straight the source code first. The first case that helped me start this journey was asked by a MVP within the SQL Server community (his first name starts with Joey<g>). That is to ensure your database is encrypted at the file level.

Servers 40

Servers Database Azure Cache 40

SQL Performance

NOVEMBER 1, 2019

As I embark on my first PASS Summit in a few years, it is hard to contain my excitement about the newest version of SQL Server. As you may recall, back in SQL Server 2016 SP1, they opened up all of the programming surface features to all editions, and many (but certainly not all) of the performance and availability features.

Servers 40

Servers Hardware Database Availability 40

SQL Performance

JULY 27, 2021

Continuing my series of articles on latches, this time I’m going to discuss the DBCC_OBJECT_METADATA latch and show how it can be a major bottleneck for consistency checks prior to SQL Server 2016 under certain circumstances. But remember, this bottleneck only happens for versions of SQL Server earlier than SQL Server 2016.

C++ 54

C++ Servers Database Code 54

SQL Performance

AUGUST 11, 2021

Another is using a custom calculation that emulates the DATE_BUCKET function, which you can use in any version, edition, and flavor of SQL Server and Azure SQL Database. In my examples, I’ll use the sample database TSQLV5. You can find the script that creates and populates TSQLV5 here and its ER diagram here. DATE_BUCKET.

Azure 139

Azure Code Database Servers 139

SQL Server According to Bob

APRIL 21, 2017

I recently visited a customer onsite and presented to them topics on SQL Server 2016. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. We now have a new way of instrumenting the backup/restore process for any database or transaction log.

Servers 40

Servers Database Processing Virtualization 40

SQL Server According to Bob

APRIL 25, 2017

I recently visited a customer onsite and presented to them topics on SQL Server 2016. One question I got went like this “I’ve tried to restore a database on SQL Server using the WITH STATS option. We now have a new way of instrumenting the backup/restore process for any database or transaction log.

Servers 40

Servers Database Processing Virtualization 40

SQL Server According to Bob

JULY 12, 2016

Trace flags 2562 and 2549 are documented in knowledgebase article: [link] and the blog post [link] highlights the SQL 2016 DBCC performance improvements. For SQL Server 2008, 2012 and 2014 the trace flag behavior remains the same. For SQL Server 2016 the trace flags are available but the behavior may be altered by the design changes.

Servers 40

Servers Design Engineering Database 40

SQL Performance

OCTOBER 14, 2020

In my examples I’ll continue using the sample databases TSQLV5 and PerformanceV5. The issue is that even though SQL Server does support a short circuit concept at the physical processing level, there’s no assurance that it will evaluate the filter predicates in written order from left to right. Substitution/unnesting.

C++ 52

C++ Servers Code Efficiency 52

SQL Performance

OCTOBER 29, 2019

There are multiple methods to look at poorly performing queries in SQL Server, notably Query Store, Extended Events, and dynamic management views (DMVs). In order to use Query Store and Extended Events, you have to configure them in advance – either enabling Query Store for your database(s), or setting up an XE session and starting it.

C++ 74

C++ Database Servers Tuning 74

SQL Performance

JULY 1, 2019

The results are technically non-deterministic since SQL Server could validly return any one of the rows in each group. Nevertheless, if you run this query yourself, you are quite likely to see the same result I do: The execution plan depends on the version of SQL Server used, and does not depend on database compatibility level.

C++ 58

C++ Servers Database Performance 58

SQL Performance

JUNE 22, 2021

The FGCB_ADD_REMOVE latch protects a data structure called an FGCB, or File Group Control Block, and there will be one of these latches for each online filegroup of each online database in a SQL Server instance. DROP EVENT SESSION [ FGCB_ADDREMOVE ] ON SERVER ; END. Showing the Bottleneck. ( SELECT * FROM sys.

Servers 54

Servers Database Performance Code 54

SQL Performance

JULY 9, 2019

The ISO/IEC 9075:2016 standard (SQL:2016) defines a feature called nested window functions. I posted a suggestion to improve SQL Server by adding support for nested window functions. This feature allows you to nest two kinds of window functions as an argument of a window aggregate function.

Servers 83

Servers Availability Database Code 83

SQL Server According to Bob

FEBRUARY 10, 2019

. · Failures occur from remote clients or sqlcmd executed directory on the server (/opt/mssql-tools/bin). SQL Server 2017 CU10 does not show behavior. · SQL Server 2017 CU12 exhibits the behavior. Server is a Superdome with lots of CPUs and RAM. Here is an example of a valid SQL Server login. – sqlclr.

Servers 40

Servers Network Database Systems 40

Brendan Gregg

NOVEMBER 29, 2017

But not all workloads: some are network bound (proxies) and storage bound (databases). ## 5. This was extended to instance storage devices for the x1.32xlarge in 2016. AWS Bare Metal 2017 Also announced at AWS re:Invent: the Amazon EC2 Bare Metal instances, which are just that – bare metal servers. The first was c3.

Virtualization 110

Virtualization AWS Hardware Storage 110

SQL Performance

MAY 29, 2019

It can be activated from SQL Server 2008 to 2014 inclusive using documented trace flag 610. From SQL Server 2016 onward, FastLoadContext is enabled by default ; the trace flag is not required. For more background, see the Data Performance Loading Guide and the Tiger Team notes on the behaviour changes for SQL Server 2016.

Cache 64

Cache Testing Servers Code 64

SQL Performance

MAY 1, 2019

The summary rows for heap tables without indexes are the same in both documents (no changes for SQL Server 2016): An explicit TABLOCK hint is not the only way to meet the requirement for table-level locking. The following demo script should be run on a development instance in a new test database set to use the SIMPLE recovery model.

Servers 47

Servers Database Testing Performance 47