A Fine Slice Of SQL Server
Chad Callihan doesn’t have time for the UI:
If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.
Read on to see how the procedure works and what you can do.
Comments closedThe Big Data in Real World team doesn’t have time to mess around:
If you work with Apache Hadoop, you may find yourself needing to kill multiple YARN applications at once. While you can kill them one by one using the yarn application -kill command, this can be a tedious and time-consuming process. Fortunately, there is a faster way to kill multiple YARN applications at once using the yarn application command in combination with awk.
Click through to see how. I will say, though, remembering some of these sed+grep+awk solutions I’ve written in the past makes me happy that Powershell is object-based…
Comments closedSalman Ahmed wants to be highly available:
We are thrilled to announce the release of pg_cirrus! First of all, you might be wondering what “cirrus” means. The term refers to the thin and wispy clouds that are often seen at high altitudes.
pg_cirrus is a simple and automated solution to deploy highly available 3-node PostgreSQL clusters with auto failover. It is built using Ansible and to perform auto failover and load balancing we are using pgpool.
Read on to see how it works. It’s also licensed under GPLv3, so it’s not only highly available but also freely available.
Comments closedAthar Ishteyaque enables an extension:
The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.
The goal of a PostgreSQL audit is to provide the tools needed to produce audit logs. These logs are often required to pass certain government, financial, or ISO certification audits.
I am kind of curious what the performance impact of this extension is.
Comments closedUmair Shahid gives us an overview:
While PostgreSQL is known for its robustness, scalability, and reliability, data inconsistency can occur in PostgreSQL clusters, which can cause issues and impact the overall performance of the system. In this blog, we’ll define data inconsistency in PostgreSQL clusters, discuss the challenges it poses, its causes, and provide some tips on how to prevent and resolve it if it occurs.
Click through for the article.
Comments closedRyan Booz explains one area where Postgres’s implementation differs from most other vendors:
All relational databases handle transaction isolation in some way, typically with an implementation of Multi-version Concurrency Control (MVCC). Plain ‘ol, mainline SQL Server uses a form of MVCC, but all older rows (currently retained for ongoing transactions) are stored in TempDB. Oracle and MySQL also do something similar, storing (essentially) diffs of the modified data outside of the table that is merged at runtime for ongoing transactions that still need to see the older data.
Among these databases, PostgreSQL stands alone in the specific way MVCC is implemented. Rather than storing some form of the older data outside of the current table for transactions to query/merge/etc. at runtime, PostgreSQL always creates the newly modified row in-table alongside the existing, older versions that are still needed for running transactions. Yes, every UPDATE creates a new row of data in the table, even if you just change one column.
Read on to understand some of the implications of this and how it affects the way we manage databases.
Comments closedErik Darling explains why he is not a fan in this video on the topic.
Because there’s no text, I’ll hit on the points:
Click through for the video. Erik also talks a bit about cost threshold for parallelism.
Comments closedEitan Blumin has a script for us:
In this ultimate guide, learn how to fix high VLF counts and shrink transaction logs in SQL Server to improve performance. Discover best practices for managing transaction log sizes and VLFs, and get your hands on a robust script to help you out. Boost your SQL Server’s performance and optimize your database administration with these tips.
Click through for the guide, and for a script Eitan has put together.
Comments closedAndrea Allred has a script for us:
Last month I talked about how I started collecting data from sp_whoisactive. The long term goal was to analyze and tune those long running processes that are sometimes hard to quantify in Query Store. We had started collecting the data in a table (whoisactiveLog), but wanted to make a simple table that our Engineers could refer to and find the long running processes. We also wanted to archive off the whoisactiveLog into another table and save that for 70 days in case it was needed for further research on the tuning of processes. Each night, we have a break in our processes where we can do this maintenance so we decided we would empty the whoisactiveLog table completely.
There’s also a check for session length in there.
Comments closedGuy Glantser does some explaining:
In the world of database management systems, ensuring data durability is of paramount importance. It involves guaranteeing that committed transactions are not lost in the event of unexpected failures or system crashes. SQL Server offers a feature known as Delayed Durability to address this challenge. Delayed Durability provides a mechanism for optimizing transaction logging and improving performance without compromising data integrity. This article aims to delve into the concept of Delayed Durability in SQL Server, specifically focusing on its behavior during expected shutdown scenarios.
Guy’s post surprised me, and I recommend reading it if you’ve thought about enabling the feature.
Comments closed