January 10, 2017 at 9:02 am
Hello experts,
Does anyone know what would cause DBCC CHECKDB to take in excess of 20 minutes to run on a 500 MB database?
I have been seeing the DBCC CHECKDB command running long in the scheduled jobs, so I tried the command manually, e.g.:
DBCC CHECKDB ('mydb');
That is what has been running 20+ minutes.
I ran dbo.sp_WhoIsActive and see that the SPID is:
wait_info = (1271857ms)RESOURCE_SEMAPHORE
status = suspended
percent_complete = NULL
cpu = 252
reads = 6,977
writes = 13
used_memory = 3
open_tran_count = 1
I don't know why the command should be taking so long on a database that doesn't seem that large. But I must be missing something.
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 10, 2017 at 9:11 am
January 10, 2017 at 9:22 am
Thank you, John,
On the page you referenced, I found the sys.dm_exec_query_memory_grants DMV description and queried it. These are the results for the DBCC CHECKDB statement that I am running.
request_id0
scheduler_id2
dop1
grant_timeNULL
requested_memory_kb790816
granted_memory_kbNULL
required_memory_kb512
used_memory_kbNULL
max_used_memory_kbNULL
query_cost785999.2792
timeout_sec86400
resource_semaphore_id0
queue_id9
wait_order0
is_next_candidate0
wait_time_ms2326109
group_id1
pool_id1
is_small0
ideal_memory_kb582776856
The requested_memory_kb and ideal_memory_kb values seem really high and are way above the values for the other SPIDs in the result set. For example, the next highest requested_memory_kb is 9360 and the next highest ideal_memory_kb value is 413.895664959691.
Do you know where I can find further info?
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 10, 2017 at 9:40 am
If your database is 500MB, then the ideal memory grant should be about 582776856 bytes, not KB. I don't know what's going on there. How much memory do you have in your server, and what is the max server memory setting? You probably need to investigate whether you are under memory pressure (it sounds as if you are). This[/url] is a good place to start.
John
January 10, 2017 at 9:53 am
Thanks again for the helpful info.
The server currently has 8 GB of RAM, SQL set to 2 GB min and 4 GB max. Given that we have one database on the server that has grown to over 100 GB, I am going to review the resources as you recommend. The DBCC CHECKDB had been running OK until a few weeks ago, so I think the server has crossed a threshold where the available RAM and/or CPU is under too much pressure as you said.
I really appreciate your feedback.
Thanks -
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 10, 2017 at 9:58 am
OK, good luck. If your server isn't running any other applications or components such as Reporting Services, I'd consider increasing max server memory to 5 or 6 GB. That said, it's still not much when you have a 100GB database.
John
January 10, 2017 at 11:42 am
1) Unless you have very little active data in that large database, 8GB on the server and 4GB for SQL Server is WAY too low.
2) checkdb can cause a HUGE amount of tempdb activity too. I would check for that.
3) Did you do a file IO stall analysis? track for 5 minutes or so while the checkdb is running.
4) Computed columns and other things can cause SEVERE performance issues with checkdb. Search web and also note that significant improvements were released in the not too distant past to help rectify those things. I don't know if they went back to 2012 but I believe they did.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 10, 2017 at 1:06 pm
TheSQLGuru (1/10/2017)
1) Unless you have very little active data in that large database, 8GB on the server and 4GB for SQL Server is WAY too low.2) checkdb can cause a HUGE amount of tempdb activity too. I would check for that.
3) Did you do a file IO stall analysis? track for 5 minutes or so while the checkdb is running.
4) Computed columns and other things can cause SEVERE performance issues with checkdb. Search web and also note that significant improvements were released in the not too distant past to help rectify those things. I don't know if they went back to 2012 but I believe they did.
Thanks for these tips! Yes, the 8 GB RAM is way low. This is a VM set up by our systems team with input from me, and I underestimated the growth of the space and processing usage. I am still investigating but wouldn't be surprised if I end up requesting a memory increase as part of the solution.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 12, 2017 at 2:49 pm
FYI as a follow-up:
I ran sp_Blitz and found that this seems to be what's going on:
Whenever SQL Server needs to wait while it’s executing queries, it tracks that time as a wait statistic. We can query the waits tables to get overall bottleneck data over time.This part of our SQL Server sp_Blitz® scriptour SQL Server sp_Blitz® script checks sys.dm_os_wait_stats looking for more than 5 seconds cumulative wait times for the RESOURCE_SEMAPHORE, RESOURCE_SEMAPHORE_QUERY_COMPILE, or THREADPOOL wait.
https://www.brentozar.com/blitz/poison-wait-detected/
Most likely this is memory pressure from the small amount of RAM. I know that merely corroborates what you guys helpfully suggested anyway, but it was nice to see a kind of "smoking gun" to report back to my team.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply