Great video. My first job as a junior dba, we had around 2000 instances around the globe with no central monitoring. And would see corruption about once a week from someone. (mostly because they would just power the server off and on) So I felt I got good at handling it. But yes, call MS if you run into it and arne't familiar. And yes, the DBCC challenge blog is hugely beneficial for practice.
This is about the section 02:53. You indicate that dbcc can cause data pages to be Flushed from memory. Can you confirm this? I'm curious to know because Sql server backups don't utilize the memory from sql memory buffer (max memory setting). Instead the backup uses the memory outside of the sql buffer memory (it creates 4 to 8 buffers, each of 4MB). Can you confirm this please?
DBCC CHECKDB needs a memory grant (query workspace memory) in order to do its work. That memory comes from the buffer pool. In cases of large memory grants, that causes data pages to be flushed from memory. Cheers!
@@TheBrentOzar is your reply assuming that MAX sql memory isn't set, so it will flush data pages to allocate more memory (outside of mem buffer) for backups? Or is your answer the same irrespective of whether or not the sql server max memory option (example: 90% memory) and LPIM (lock pages in memory) is set?
No, because you need to proactively check pages that haven't been read yet. An Agent alert would only tell you when a user has run across the corruption. By that point, it could already be widespread on disk.
Great video.
My first job as a junior dba, we had around 2000 instances around the globe with no central monitoring. And would see corruption about once a week from someone. (mostly because they would just power the server off and on) So I felt I got good at handling it. But yes, call MS if you run into it and arne't familiar. And yes, the DBCC challenge blog is hugely beneficial for practice.
This is about the section 02:53. You indicate that dbcc can cause data pages to be Flushed from memory. Can you confirm this? I'm curious to know because Sql server backups don't utilize the memory from sql memory buffer (max memory setting). Instead the backup uses the memory outside of the sql buffer memory (it creates 4 to 8 buffers, each of 4MB). Can you confirm this please?
DBCC CHECKDB needs a memory grant (query workspace memory) in order to do its work. That memory comes from the buffer pool. In cases of large memory grants, that causes data pages to be flushed from memory. Cheers!
@@TheBrentOzar is your reply assuming that MAX sql memory isn't set, so it will flush data pages to allocate more memory (outside of mem buffer) for backups? Or is your answer the same irrespective of whether or not the sql server max memory option (example: 90% memory) and LPIM (lock pages in memory) is set?
@@NK-xw4uu the answer has nothing to do with max memory. For more info, check out our Mastering Server tuning class.
Can we use sql agent alert to notify of there are any corruption instead ? In this way , no need to run RBC checkdb ? Thx
No, because you need to proactively check pages that haven't been read yet. An Agent alert would only tell you when a user has run across the corruption. By that point, it could already be widespread on disk.
Понравилась шутка про русских хакеров.