Constantly querying system views [Oracle, MSSQL, PostgreSQL]



  • 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!



  • 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 https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2