September 29, 2021 at 7:18 am
We are having an issue of SQL server slowness and I’m unable to find out why.
After the dbcc checkdb job completes the other processes like the ETL jobs, other maintenance jobs just run for a longer time.
we are having performance issues that we are forced to restart the server whenever this happens. This happens every once a week.
why do you think the dbcc checkdb is slowing down the system?
September 29, 2021 at 1:00 pm
Without a lot more information, I'm just guessing here. DBCC uses memory. Because of this, data that lives in memory could be flushed out to disk and then it takes a while for it to get back into memory again. Or, the memory use causes plans to be pushed out of cache and you're seeing slow downs as plans are compiled and stored again. Or, some combination of both. I'd suggest capturing more metrics, especially wait statistics. What is slowing things down before the DBCC check and what is slowing things down after. That can give you some indications as to what's going on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2021 at 3:07 pm
After the dbcc checkdb the queries that are mostly run are waiting on pageiolatch.
Grant, why do you think it takes a while to release back the memory?
September 29, 2021 at 3:17 pm
PAGEIOLATCH is reading data from disk to the buffer cache.
So what is flushing all the cache that everything needs to read back from disk.
How much RAM is in the SQL server and how much is allocated to SQL's max server memory setting.
If you are constantly faced with lots of PAGEIOLATCH you don't have enough RAM to support the workload and need to increase that and potentially look at if you have an IO bottleneck and look at things like SSDs etc.
September 29, 2021 at 3:20 pm
We have 450 GB of RAM. We’ve set the SQL max memory to 400 GB.
September 29, 2021 at 3:38 pm
And how big is the biggest database, and how big is the biggest table?
Going off Jonathan's calculations @450 SQL should be @392 so not far off.
Do you have lock pages in memory enabled?
Look through sys.dm_io_pending_io_requests when you are noticing the large amount of PAGEIOLATCH waits to see where the IO is pending if its at the OS or at the SQL layer.
Check all your disk metrics also as you could have slow disks if your constantly waiting on IO.
Trend counters like PLE so you can see how long things stay in the buffer cache for and when the values tank see what was running so have a XE trace set to pull the completed events for statement and procedure so you can see what was run when PLE tanked as a tanked PLE will then go to it needing to read back from disk.
Other than that offload the CHECKDB to another machine.
September 29, 2021 at 4:01 pm
You can do a lot of DBCC checks offline through a backup. That's one way to reduce the overhead. All the logical checks will work on a backup. You only have to do the physical checks on the server directly. That will absolutely help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply