A Fine Slice Of SQL Server
Henrietta Dombrovskaya covers a couple of background processes:
Welcome to the second blog of the “magic of parameters” series. In the first entry, I covered memory parameters, and in this article. In this article will talk about PostgreSQL configuration parameters which manage the (auto)vacuum and (auto)analyze background processes.
Read on for a deeper dive into vacuuming and a bit on auto-analyze as well.
Comments closedGrant Fritchey does some explaining:
I’ve had people come up to me and say “PostgreSQL is open source and therefore license free. Why on earth would I put PostgreSQL in Azure?”
Honestly, I think that’s a very fair question. The shortest possible answer is, of course, you don’t have to. You can host your own PostgreSQL instances on local hardware, or build out VMs in Azure and put PostgreSQL out there, some other VM host, or maybe in Kubernetes containers, I mean, yeah, you have tons of options. So why PostgreSQL in Azure, and specifically, I mean the Platform as a Service offering? Let’s talk about it.
The biggest issue I’ve historically had with PostgreSQL or MySQL platform-as-a-service offerings in Azure is that Microsoft is always behind the release curve. With PostgreSQL, it’s not so bad—flexible server offers version 14.7, which is one major version behind Postgres itself (15) but at least the latest minor version. They’ve caught up on MySQL, but for a while, they were way behind.
Comments closedAndreas Scherbaum polls the audience:
For anyone who doesn’t know, I’m running a weekly interview series with people from the PostgreSQL community. It’s called “PostgreSQL Person of the Week“. One of the questions in the default set I give everyone is:
What is your favorite PostgreSQL extension?And guess what the answer is: by far everyone’s favorite is pg_stat_statements!
Read on to learn a bit more about what the extension does, why people like it, and what other extensions interviewees prefer.
Comments closedSteve Steadman reminds us of a SQL Server feature:
The Scan For Startup Procs feature in SQL Server allows you to specify a list of stored procedures that will be automatically executed whenever the database engine starts. This can be useful in certain scenarios, such as when you want to perform tasks such as restoring a database or performing maintenance tasks when the database engine starts.
“Scan for startup procs” is a configuration option in Microsoft SQL Server that determines whether the server should scan for and execute stored procedures that are marked as “startup procedures” when the server starts up.
I’ve used this to good effect in the past, but there is a fundamental problem with this approach: it’s easy to forget about these, potentially leading to a difficult search for why some action took place. If you only let sysadmins add or change startup stored procedures, then I’d consider this just as little a security risk as xp_cmdshell: if the attacker already has sysadmin, the attacker can simple enable the feature, so there’s no real value to denying yourself the capability if it makes sense in your environment.
Comments closedRyan Lambert shows off a Postgres extension:
This is my entry for PgSQL Phriday #008. It’s Saturday, so I guess this is a day late! This month’s topic, chosen by Michael from pgMustard, is on the excellent
pg_stat_statementsextension. When I saw Michael was the host this month I knew he’d pick a topic I would want to contribute on! Michael’s post for his own topic provides helpful queries and good reminders about changes to columns between Postgres version 12 and 13.In this post I show one way I like using
pg_stat_statements: tracking the impact of configuration changes to a specific workload. I used a contrived change to configuration to quickly make an obvious impact.
Read on for the example.
Comments closedYou have probably seen the recommendation to turn on the “optimize for ad-hoc workloads” setting. You might even have seen a more recent recommendation to set the database setting parameterization to forced (instead of the default which is simple). The aim of this post is to briefly describe each and then do some test with various settings.
Click through for that test. This is a good example of how we need to temper guidance with context. In Tibor’s scenario, forced parameterization is a no-brainer and optimize for ad hoc workloads gives a pretty nice reduction in plan cache utilization. But then, with optimize for ad hoc workloads on, you lose the ability to see the first run of a query in Query Store and lose the opportunity to tune the different variations of a query which only ran once. Pretty much every setting in SQL Server exists because there is a scenario in which that is the most appropriate setting. Except auto-shrink. Auto-shrink delenda est.
Comments closedThe Big Data in Real World team spams the delete button:
Shutting down a node abruptly is not the right way to decommission or remove a node from the Elasticsearch cluster. Doing so will cause your shards which are replicated to go down in replication and it could cause disruption to the clients who are currently consuming data from Elasticsearch.
Proper way to decommission or remove a node from Elasticsearch is to add the host to the exclusion list.
Click through to learn how to do this.
Comments closedAndrea Allred wants to know who’s doing what on this system:
I needed to collect sp_WhoIsActive into a table, but the twist was that it is on my Azure Managed Database, so I had to get creative with how I did it. We needed an Azure Pipeline to run it, but we wanted to record it every minute and firing a pipeline every minute adds up fast. So we decided that we would kick it off once an hour and have the process wait for a minute and then fire until the hour ended. Then it fire again at the top of the next hour and the same process would happen.
That’s an interesting way to do it. Another alternative might have been an Azure function app, which you could schedule to run every minute. I think that’d be a lot less expensive than running an Azure Pipeline, and this goes to show you that there are many ways to solve the same problem in Azure.
Comments closedPhil Booth categorizes various mistakes as learning experiences:
Previously on Extreme Learning, I discussed all the ways I’ve broken production using healthchecks. In this post I’ll do the same for PostgreSQL.
The common thread linking most of these gotchas is scalability. They’re things that won’t affect you while your database is small. But if one day you want your database not to be small, it pays to think about them in advance. Otherwise they’ll came back and bite you later, potentially when it’s least convenient. Plus in many cases it’s less work to do the right thing from the start, than it is to change a working system to do the right thing later on.
Click through for the nine lessons learned, eight of which are still relevant as of PostgreSQL version 12. Many of these also have analogues in the SQL Server world, e.g., don’t overuse triggers, use non-recursive methods for path traversal when possible, do add indexes on foreign keys.
Comments closedChad Callihan loses track of time:
SQL Server Evaluation editions are great to use for testing early releases. When the real deal comes out, it’s best to upgrade then and there (probably Evaluation to Developer if you’re testing and experimenting). But what happens if you let an evaluation edition expire by accident? The good news is you can still upgrade but it’s a bit tedious.
Click through to see how.
Comments closed