A Fine Slice Of SQL Server
I’m still learning PostgreSQL and one of the things I’ve been looking at a lot lately is instrumentation. What do I mean? Well, if you’re in SQL Server, think, Dynamic Management Views (DMV), Extended Events, Query Store, <hack, spit> Trace <spit>. How do we know how long a query took to run? PostgreSQL can tell you, but, and this is one of those wild, cool, but, honestly, slightly frustrating things about PostgreSQL, not natively.
Read on for what Grant’s seen.
Comments closedHere we have the options, to set the processor affinity mask for all process, and also the I/O affinity mask. The default is to let SQL handle this automatically as above, as it will provide better performance for most workloads.
On a basic level, these settings restrict which CPUs can be used when restricting SQL server processing and/or SQL IO operations.
There are very specific use cases in which setting CPU affinity makes sense. Rod does a really good job of showing just why mucking with CPU affinity is not for the faint of heart.
Comments closedI saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.
That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.
Read on for Steve’s solution. There are also other places where you might find these calls: SSISCatalog if you use Integration Services, specific company metadata tables if you use those to build T-SQL statements, etc. But this does give you a good start.
Comments closedUmair Shahid tracks some statements:
pg_stat_statementsis an extension for PostgreSQL that tracks execution statistics of SQL statements. It is designed to provide insight into the performance characteristics of database queries by collecting data on various metrics such as execution time, number of calls, and I/O operations. This extension is immensely useful for database administrators and developers looking to optimize their SQL queries and improve overall database performance.
Click through to learn more about pg_stat_statements, including how to install and configure it, as well as some of the things you can do with it.
Comments closedLaurenz Albe shares a warning:
Linux tries to conserve memory resources. When you request a chunk of memory from the kernel, Linux does not immediately reserve that memory for you. All you get is a pointer and the promise that you can use the memory at the destination. The kernel allocates the memory only when you actually use it. That way, if you request 1MB of memory, but use only half of it, the other half is never allocated and is available for other processes (or the kernel page cache).
Overbooking is a concept that airlines all over the world have been using for a long time. They sell more seats than are actually in the plane. From experience, airlines know that some passengers don’t show up for the flight, so overbooking allows them to make more profit. By default, Linux does the same: it deals out (“commits”) more memory than is actually available in the machine, in the hope that not all processes will use all the memory they allocate. This memory overcommit is great for using resources efficiently, but it has one problem: what if all the flight passengers show up, that is, what if the processes actually use more memory than is available? After all, you cannot offer a computer process a refund or a free overnight hotel room.
Read on to learn more about memory overcommit and what you can do about it.
Comments closedChad Callihan recovers from a missing database backup:
There may be no worse feeling than needing a database backup and not having one. It ranks right up there with running a DELETE statement and missing the WHERE clause. God help you if you if you suffer both of those together. If you come across that situation with a MySQL database, you might be able to recover what you need.
Read on to see how. Even so, I’d be concerned about what happens if there are foreign key constraints involved.
Comments closedAndy Brownsword lets things get out of hand:
Last week we looked at using Database Snapshots to help with rolling back upgrades. The snapshot maintained a point in time copy of the database which could be later restored.
We can go further – a database can have multiple snapshots.
Let’s suppose we want to take one before an upgrade, another once the upgrade is complete, and another before the start of business the following day. This would provide us multiple points to restore too.
This however makes restoring more complicated.
My recollection is that it’s not just restoration that gets more complicated, but also any database activity, to the point where too many database snapshots on a single database can have a considerable performance impact.
Comments closedJosephine Bush creates and uses a managed identity:
Benefits of Using Managed Identities and Entra Groups
- Enhanced Security: Using managed identities eliminates the need to manage credentials, reducing the risk of credential theft.
- Simplified Management: Entra Groups streamline the management of permissions for multiple users or managed identities, making it easier to apply consistent access policies.
- Scalability: As your organization grows, you can easily manage access by adding new users or managed identities to Entra Groups without needing to update database permissions individually.
Read on to see how you can create one and what you can do with it.
Comments closedJana Sattainathan checks the labels on these bottles:
A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.
This is, strictly speaking, a comparison of configurations rather than data differences, indexing, and the like. Nonetheless, it’s useful to make this sort of comparison just to ensure that your instances have your desired state configuration.
Comments closedDavid Wheeler talks pre-loading:
Recently I’ve been trying to figure out when a Postgres extension shared libraries should be preloaded. By “shared libraries” I mean libraries provided or used by Postgres extensions, whether
LOADable libraries orCREATE EXTENSIONlibraries written in C or pgrx. By “preloaded” I mean under what conditions should they be added to one of the Shared Library Preloading variables, especiallyshared_preload_libraries.The answer, it turns out, comes very much down to the extension type. Read on for details.
Read on for an interesting discussion of what pre-loading means and the circumstances you should consider along the way.
Comments closed