1

I'm a kind-of-experienced database developer that just got into the DBAdministration world.

I've been told that in both Oracle and MSSQL is definitely a terrible idea to run queries such as the below example, constantly every, let's say, 5 seconds. Since it might cause memory fragmentation.

SELECT  *
FROM    V_$SQL_MONITOR; --oracle

I've done some research, but I couldn't find any official (or unofficial, for what is worth) documentation discouraging to do such a thing.

Could someone experienced in the DBA world illustrate for me if this is indeed a bad idea, what memory fragmentation is, and if this practice also applies to PostgreSQL?

Thank you in advance!

Improve this question
3
  • 1
    Welcome to the DBA.SE community. In your question you write that "I've been told that in both Oracle and MSSQL is definitely a terrible idea...." and "...since it might cause memory fragmentation.". Do these people telling you such stories have any reputable source they can quote from? Can they provide you with a compelling explanation? If not, then I would probably put if off as "spinning a yarn". ;-) Commented Feb 18, 2022 at 15:23
  • The one that told me about Oracle has worked as a DBA for Oracle Incorporation for 20+ years. Although I certainly did not hear of "memory fragmentation" term before, I have complete respect and trust for her. Commented Feb 18, 2022 at 16:01
  • @glezo I will say this, all developers are equal and definitely make mistakes equally. As much as I love Microsoft, I've found bugs in their source code in some of their products before due to assumptions about their own product that their own developers made lol. But usually they're pretty rock solid, just a point that even the developers of a product themselves sometimes make accidental misconceptions. Commented Feb 18, 2022 at 17:38

1 Answer 1

Reset to default
2

I've primarily been working with Microsoft SQL Server for about a decade and I've never heard of the term "memory fragmentation". First result in Google quotes it as "Fragmentation of memory is a memory disorder where an individual is unable to associate the context of their memories...".

Kidding aside, I'm not aware of any specific memory issues with querying the system entities in SQL Server, but I would say the following are potential issues that can occur:

  1. Querying them frequently requires server resources like any other entity, and theoretically could add up, especially depending on the system entity and how efficiently it was designed / coded. CPU cycles, memory, and disk I/O typically being the resources consumed.

  2. Querying certain system entities too frequently could theoretically result in them being blocked, and / or your query on them being blocked and waiting a while to complete. The latter isn't necessarily a problem as much as it could be an annoyance unless you encompass querying those system entities as part of a larger workflow.

Personally, I'm querying certain system DMVs and logging them to a table every 10 seconds, 1 minute, and 10 minutes depending on the system entity and information I want to log, so that I can retroactively troubleshoot performance issues. 10 seconds is probably excessive for most people, and even every 1 minute might be unneeded in a lot of cases, but I like having that level of granularity and it works well in my current system that I support. I have yet to encounter either of the aforementioned issues.

Your mileage may vary on other database systems, depending on the system entities you query, your frequency, and the busyness of your server.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.