New Metadata-Only Column Changes in SQL Server 2016

SQL Performance

Depending on the type of change and the configuration of the database, an ALTER COLUMN command will need to perform one of the following actions: Change metadata in system tables only. Improvements in SQL Server 2016. The ALTER TABLE. ALTER COLUMN command is very powerful.

SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better

SQL Server According to Bob

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.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

SQL 2016 – It Just Runs Faster Announcement

SQL Server According to Bob

SQL Server 2016 ‘It Just Runs Faster’ A bold statement that any SQL Server professional can stand behind with confidence. My development collogues and I are starting a regular blog series, outlining the vast range of scalability improvements, allowing SQL Server 2016 to run across a wide array of hardware configurations, faster and better than previous releases of SQL Server. Try SQL Server 2016 Today. SQL 2016 – It Just Runs Faster: DBCC Scales 7x Better.

SQL 2016–Install MSVC Patch Required

SQL Server According to Bob

There is a MSVC, runtime library patch needed by SQL Server 2016 and without the patch the SQL Server service can simply terminate (crash.) For complete instructions on applying this patch, read the Release Notes for SQL Server 2016 (look at section called “Install Patch Requirement (GA)”) If you have already installed SQL Server 2016 you have the option to just install the MSVC patch vs the complete SQL Server Critical Update. Uncategorized Setup SQL 2016

SQL Server 2016 – It Just Runs Faster: Always On Availability Groups Turbocharged

SQL Server According to Bob

The result is a design for SQL Server 2016 that provides high availability for the most demanding workloads on the latest hardware with minimal impact and scalable for the future. The Y axis represents throughput as measured by the Performance Monitor counter Databases:Log Bytes Flushed/Sec.

Reinventing Performance Testing: New Architectures

Alex Podelko

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. Due to virtualization system-level monitoring doesn’t help much anymore and may be misleading – so getting information from application and database servers becomes very important.

Why Uber Engineering Switched from Postgres to MySQL

Uber Engineering

Architecture Open Source Data Database Infra MySQL Postgres Uber DataBy Evan Klitzke. Introduction. The early architecture of Uber consisted of a monolithic backend application written in Python that used Postgres for data persistence.

The SQL Server Basic Installer: Just Install It!

SQL Server According to Bob

Today we are introducing the new Basic Installer experience for SQL Server 2016 Express , SQL Server 2016 Developer , and SQL Server 2016 Evaluation Editions. In addition, the Download Media option includes a link to create a virtual machine in Azure for SQL Server 2016.

Media 52

Spice up your Analytics: Amazon QuickSight Now Generally Available in N. Virginia, Oregon, and Ireland.

All Things Distributed

In addition to AWS data sources, QuickSight also lets customers connect to third-party databases running on Amazon EC2 or on-premises and popular business applications like Excel and Salesforce.

DBCC Trace Flags 2562 and 2549

SQL Server According to Bob

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 2016 the trace flags are available but the behavior may be altered by the design changes. Trace flag 2549 is used to change how DBCC sees the volume layout of the database. The SQL Server 2016 design change uses the CheckScanner instead of the MultiObjectScanner improving I/O behavior.

Expanding the Cloud: Introducing the AWS Asia Pacific (Seoul) Region

All Things Distributed

Mirae Asset Global Investments improved its web service environment and reduced annual management costs by 50% by consolidating the management of all web services, including servers, network, database, and security.

Games 117

Expanding the AWS Cloud: Introducing the AWS US East (Ohio) Region

All Things Distributed

In the remainder of 2016 and inq 2017, we will launch another four AWS regions in Canada, China, the United Kingdom, and France, adding another nine AZs to our global infrastructure footprint. Today I am very happy to announce the opening of the new US East (Ohio) Region.

AWS 88

Accelerating Data: Faster and More Scalable ElastiCache for Redis

All Things Distributed

Fast Data is an emerging industry term for information that is arriving at high volume and incredible rates, faster than traditional databases can manage. While caching continues to be a dominant use of ElastiCache for Redis, we see customers increasingly use it as an in-memory NoSQL database.

Designing Schemaless, Uber Engineering’s Scalable Datastore Using MySQL

Uber Engineering

Architecture Data Database Infra Mezzanine MySQLBy Jakob Holdgaard Thomsen. The making of Schemaless, Uber Engineering’s custom designed datastore using MySQL, which has allowed us to scale from 2014 to beyond. This is part one of a three-part series on Schemaless. In Project Mezzanine we described … The post Designing Schemaless, Uber Engineering’s Scalable Datastore Using MySQL appeared first on Uber Engineering Blog.

How It Works: Session/SPID (–2) for DTC Transactions

SQL Server According to Bob

The client could have enlisted another resource manager (file system, another database server, etc.) I have written on this subject before but it seems to come up from time to time, as it did again this week.

The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL

Uber Engineering

Architecture Data Database Infra Mezzanine MySQLHow Uber’s infrastructure works with Schemaless, the datastore using MySQL that’s kept Uber Engineering scaling since October 2014. This is part two of a three- part series on Schemaless; part one is on designing Schemaless. In Project Mezzanine: … The post The Architecture of Schemaless, Uber Engineering’s Trip Datastore Using MySQL appeared first on Uber Engineering Blog.

Using Triggers On Schemaless, Uber Engineering’s Datastore Using MySQL

Uber Engineering

Architecture Data Database Infra Mezzanine MySQLThe details and examples of Schemaless triggers, a key feature of the datastore that’s kept Uber Engineering scaling since October 2014. This is the third installment of a three-part series on Schemaless; the first part is a design overview … The post Using Triggers On Schemaless, Uber Engineering’s Datastore Using MySQL appeared first on Uber Engineering Blog.

COLD, WARM, HOT … CLEAN, DIRTY, FREE … What Does All This Mean?

SQL Server According to Bob

If you talk about database pages HOT is a good thing. If the database page is held in the SQL Server Buffer Pool, main ram it is called HOT. CLEAN, DIRTY, FREE SQL Server tracks database pages in RAM or BPE (Buffer Pool Extension) with a BUF structure. The BUF structure contains a pointer to the database page in RAM or BPE, a latch used to protect physical changes, … and a status. When the database page is. I was asked about these terms from a blog reader.

How It Works: How is SQL Server Error 833, 15 Sec I/O Detected

SQL Server According to Bob

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d). I was approached to revisit the details of how Error Message 833 is detected and reported. The OS file handle is 0x%p. The offset of the latest long I/O is: %#016I64x. Older versions of SQL Server leveraged the Lazy Writer and I/O completion callback routines to record vs report the error condition.

Introducing LoadView: Real Browser Based Load Testing Software

Dotcom-Montior

This then has the capability of creating additional resource calls and database interactions, thus creating a more realistic simulation of actual user load on the web application. LoadView is a new load and stress testing product capable of performing load tests that scale to thousands of simultaneous users. The complete details of the new LoadView product are currently featured on www.loadview-testing.com.

Unresolved Deadlock vs Scheduler Deadlock

SQL Server According to Bob

Any rollback that fails causes SQL Server to take the database offline, killing all active sessions to the database. Once all connections are terminated the database is reopened in order to execute crash recovery. If crash recovery succeeds the database is available for use but if crash recovery fails the database is marked suspect. These are distinct errors but I have found that many people blend them together.

Upcoming Webinar Tuesday, 7/31: Using MySQL for Distributed Database Architectures

Percona

Please join Percona’s CEO, Peter Zaitsev as he presents Using MySQL for Distributed Database Architectures on Tuesday, July 31st, 2018 at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4). 5000 named Percona to their list in 2013, 2014, 2015 and 2016.

Weekend Reading: Amazon Aurora: Design Considerations for High Throughput Cloud-Native Relational Databases.

All Things Distributed

In many, high-throughput, OLTP style applications the database plays a crucial role to achieve scale, reliability, high-performance and cost efficiency. For a long time, these requirements were almost exclusively served by commercial, proprietary databases.

SQL Mysteries: SQL Server Login Timeouts – A Debugging Story

SQL Server According to Bob

This means we are reading database pages from the master, acquiring locks, and making calls to the domain controller (DC/KDC.). This means that we are unlikely encountering issues with database reads, locking or a preemptive, external call.

How It Works: SQL Server DTC (MSDTC and XA Transactions)

SQL Server According to Bob

Resource Manager – Database, File System, with ACID capabilities. SQL 2016 SQL Server 2017 DTC How It Works TransactionsI have found the term DTC is used in many ways so for the purposes of the discussion here are a few terms.

"0 to 60" : Switching to indirect checkpoints

SQL Performance

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. Change the Target Recovery Time of a Database.

Which is the Best MongoDB GUI? — 2019 Update

Scalegrid

In 2014, we discussed 4 of the top MongoDB GUIs: MongoVue, MongoHub, RockMongo, and Robo 3T (formerly Robomongo), and again in 2016: MongoDB Compass, Robo 3T, Studio 3T, and MongoBooster. SQL Import/Export that supports major databases: Oracle, SQL Server, MySQL, and PostgreSQL.

Should You Use ClickHouse as a Main Operational Database?

Percona

What if we use ClickHouse (which is a columnar analytical database) as our main datastore? Well, typically, an analytical database is not a replacement for a transactional or key/value datastore. how many messages was send for some time period and how much it cost) and a typical key/value queries like: “return 1 message by the message id” Using a columnar analytical database can be a big challenge here. 2016 ? 2016 ?

Please help with STRING_SPLIT improvements

SQL Performance

In SQL Server 2016, STRING_SPLIT solved a long-missing gap in a language that, admittedly, was not intended for complicated string processing. People have more complex strings than 1,2,3 or A|B|C , and they're often fed to their databases from systems outside of their control.

Common SQL Server Mishaps

SQL Performance

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. When looking at backups, I check for recovery model and the current history of backups for each database.

T-SQL bugs, pitfalls, and best practices – window functions

SQL Performance

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. This article is the fourth installment in a series about T-SQL bugs, pitfalls and best practices.

Webinar Tuesday, 8/28: Forking or Branching – Lessons from the MySQL Community

Percona

5000 named Percona to their list in 2013, 2014, 2015 and 2016. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Database Performance Blog.

Analyzing "death by a thousand cuts" workloads

SQL Performance

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. [ dbid ] ) AS [ Database Name ] , REPLACE ( REPLACE ( LEFT ( t. [

C++ 82

Thoughts on SQL Server 2019 Editions

SQL Performance

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. I wrote about this in a post entitled, " A Big Deal : SQL Server 2016 Service Pack 1."

Is Intel Doomed in the Server CPU Space?

SQL Performance

From 2007 until 2016, Intel was able to successfully execute their Tick-Tock release strategy, where they would introduce a new processor microarchitecture roughly every two years (a Tock release). Intel has been stuck at 14nm in the server space since the Broadwell release in Q4 of 2016.

The ANY Aggregate is Broken

SQL Performance

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. The more complex SQL Server 2016+ plan does not use the ANY aggregate at all.

SQL Server Mysteries: The Case of TDE and Permanent Tempdb Encryption

SQL Server According to Bob

Transparent Data Encryption (TDE) is a feature that was introduced in SQL Server 2008 (and is also available for Azure SQL Database, Azure SQL Data Warehouse, and Parallel Data Warehouse) with the purpose of encrypting your data at rest. encrypted only shows 1 starting in SQL Server 2016).

Nested window functions in SQL

SQL Performance

The ISO/IEC 9075:2016 standard (SQL:2016) defines a feature called nested window functions. This feature allows you to nest two kinds of window functions as an argument of a window aggregate function.

Row Pattern Recognition in SQL

SQL Performance

The ISO/IEC 9075:2016 standard, or SQL:2016 in short, introduces support for a profound concept called Row Pattern Recognition (RPR) in SQL. The SQL:2016 standard provides two main RPR-related features: Feature R010, “Row pattern recognition: FROM clause”.

C++ 89

Minimal Logging with INSERT…SELECT into Heap Tables

SQL Performance

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. For example, with row versioning turned off for the database: SELECT.

Minimal Logging with INSERT…SELECT and Fast Load Context

SQL Performance

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. FastLoadContext can be disabled on SQL Server 2016 using documented trace flag 692. The test table schema is such that 130 rows can fit on a single 8KB page when row versioning is off for the database.

AMD EPYC Processors in Azure Virtual Machines

SQL Performance

Both of these Intel processors are special bespoke models that are not in the Intel ARK database. The slightly newer Intel Xeon E5-26xx v4 (Broadwell) series which was introduced in Q1 of 2016, increased that to 2400MHz.

5 tips for architecting fast data applications

O'Reilly Software

Google was among the pioneers that created “web scale” architectures to analyze the massive data sets that resulted from “crawling” the web that gave birth to Apache Hadoop, MapReduce, and NoSQL databases. We usually relate SQL to querying tables in relational databases.