A Fine Slice Of SQL Server
Dave Stokes shares some queries:
I am (slowly) adding handy PostgreSQL queries to my GitHub, and Vacuum is the newest category. The end goal is to have a compilation of queries for those of us who need to keep an instance healthy.
I’ve always liked these compendia of helpful queries. The downside is that these are PDF images, making them harder to use. But even so, Dave is accepting pull requests.
Comments closedKellyn Gorman shares a few tips with us:
After the initial overview of memory monitoring in Oracle, particularly around the SGA and PGA, I wanted to take a deeper dive into the most common Oracle memory configurations. These include how memory settings are defined, how they interact, and the combinations you’re likely to encounter across different workloads. Oracle memory is a no-brainer for many of us who’ve been in the space for an extensive time, but I’ve come to realize, it can be a bit overwhelming and especially with recent changes from Oracle 12c on.
Click through for Kellyn’s guidance.
Comments closedStephen Planck offers some advice:
Database snapshots are one of those features that’s been around forever, but still solves real-world problems with very little setup. In a single statement you can capture a point-in-time, read-only copy of any user database, use it for reporting or off-load testing, and—if disaster strikes—revert the source back to that snapshot in minutes. This guide explains how snapshots work under the hood, walks through day-to-day tasks (including creating the original database), and highlights the pitfalls you should plan for before using them in production.
One additional tip that I’d offer: limit yourself to one database snapshot per database. There’s a performance cliff you can hit when you get fancy and try to have multiple database snapshots going at the same time off of the same database.
Comments closedJosephine Bush schedules a task:
pg_cron is a simple cron-based job scheduler for PostgreSQL that runs inside the database as an extension. It allows you to schedule PostgreSQL commands directly from your database, similar to using cron jobs at the operating system level. pg_cron on PG Flex is pretty easy to use, making it easy to schedule regular database maintenance and processing tasks directly from within PostgreSQL.
Read on to see how to install the extension, and then how to manage cron jobs. Josephine also lays out some limitations when using pg_cron on Azure and how to track failed jobs.
Comments closedAjay Dwivedi switches out log files:
In my organisation, we have started building PostgreSQL Clusters with Patroni + Consul. In PostgreSQL we enable a few extensions like pg_stat_statements to ensure we don’t miss any performance impacting query.
But this generates too much log in active servers, leading to PostgreSQL log bloating. Thus, it becomes important to ensure log files do not consume beyond an agreed amount of disk space. For this reason, I implemented the following log rotation steps for Postgresql –
- Ensure to set proper log file name for PostgresSQL logging_collector.
- Add a logrotate policy on linux system for postgres logs directory.
- Add a cron job for running logrotate policy more frequently.
Click through to see how.
Comments closedRob Sewell does a bit of testing:
A contained user can create a Windows login as its own account, although as it cannot grant connect permissions it is then is unable to connect at all.
So if your vendor application is running as a contained user and during an upgrade it tries to create a login for itself, it will succeed in the creation but then be unable to connect to the SQL Server instance and the upgrade will fail.
Click through for the context and the proof.
Comments closedStephen Planck turns the page:
Most of the time, corruption in SQL Server is either nonexistent or so widespread that you have no choice but to perform a file or full‑database restore. Yet an awkward middle ground exists: a handful of pages—perhaps only one—become unreadable while the rest of the database remains perfectly healthy. A full restore would repair the damage, but at the cost of rolling back hours of work and locking users out of an otherwise functional system.
That is precisely why Microsoft built RESTORE … PAGE. When you meet a short list of prerequisites (FULL or BULK_LOGGED recovery model, an unbroken backup chain, and a page that is not allocation metadata), you can surgically overwrite just the bad 8‑KB chunks, roll them forward with transaction‑log backups, and return the database to service in minutes rather than hours.
Read on to see how it all works, as well as situations in which this isn’t the right answer.
Comments closedReitse Eskens shows off Microsoft Fabric’s Monitor hub:
When we create a data solution for our company or clients, we create the best, right? Sure, we do. But still, monitoring the solution is something you should never forget. Your solution can be stable, but the data usually isn’t. And it just feels good to know all the data has been processed correctly.
So, let’s dig into monitoring Microsoft Fabric.
Read on to see how it works, as well as plenty of thoughts, advice, and critique from Reitse.
Comments closedWhen describing Oracle features to folks that are new to the platform or coming from other database platforms, I found it’s best to keep it simple, but take on the important aspects of the technology. I’m going to take on a few more posts on Oracle internals from the perspective of the individual new to Oracle.
To start, I’ve been having some long discussions, as well as documenting how Oracle memory works and how we monitor it when you don’t have the diagnostic and tuning pack at your disposal. With the exclusion of these management packs, you have to be intentional in your queries to provide the data, without violating the licensing by using the %_HIST_% views.
Click through for an overview of how Oracle makes use of memory and how you can monitor this usage.
Comments closedRyan Booz continues a series on pgNow:
In that first article, I shared how pgNow can be a lifesaver when you need immediate performance insights, highlighting features like query tuning and current activity monitoring. The tool’s ability to take periodic snapshots of query activity and spotlight active sessions has already been a significant help for early users.
Today, I wanted to look at another area of information that pgNow can help you explore during times of performance degradation or even as part of a regular database maintenance and hygiene: the Indexing tab.
Click through to see what’s in the feature and to get a free copy of the preview for pgNow.
Comments closed