August 5, 2014 at 7:35 am
Hello,
I have a VM set up for offloading DBCC checks. Specs are below. I've read through this, but I'm not seeing the performance gains by enabling the trace flags and using the physical only switch.
Is the whole drawback that I'm on SATA storage? Is there a VM configuration with the CPU I can/should change? I've been playing with MAXDOP trying to see if I can get any benefits but I'm not seeing a much. I've configured the server to be as best-practice as possible otherwise, but feel free to ask!
Thanks
wait_type wait_time_spctrunning_pct
CXPACKET 561191.4228.7128.71
OLEDB 387136.7619.8148.52
PAGEIOLATCH_SH 340674.5817.4365.95
TRACEWRITE 321598.8416.4682.41
BACKUPIO 170933.988.7591.16
BACKUPTHREAD 70299.673.6094.76
BACKUPBUFFER 53898.122.7697.52
ASYNC_IO_COMPLETION 28354.431.4598.97
PREEMPTIVE_OS_WRITEFILEGATHER7281.960.3799.34
SQL Server and OS Version Info
Microsoft SQL Server 2012 - 11.0.5532.0 (X64)
TraceFlagStatus
2549 1
2562 1
Value Data
ProcessorNameStringIntel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz
Logical CPU Count
8
Physical CPU Count
1
Physical Memory (MB)
163839
Committed Memory (MB)
153598
volume_mount_pointfile_system_typelogical_volume_nameTotal Size (GB)Available Size (GB)Space Free %
E:\ NTFS SQL_Install 399.87 389.65 97.00
L:\ NTFS Logs 3071.87 2810.97 92.00
M:\ NTFS Database 10239.87 3032.68 30.00
T:\ NTFS TempDB 499.87 102.06 20.00
August 5, 2014 at 7:56 am
What are your baseline runtimes performance analysis of checkdb vs the runtimes after these config changes?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 8:04 am
Why are you using physical only?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 8:15 am
SQLRNNR (8/5/2014)
What are your baseline runtimes performance analysis of checkdb vs the runtimes after these config changes?
Here's one I've been concentrating on since most of our databases are around this size. We have some 1-5Tb databases as well but I would like to get this running as quickly as possible first.
DBCC CHECKDB ([EDDS]) WITH NO_INFOMSGS, ALL_ERRORMSGS
494Gb 04:45:43 8 vCPU 150GB RAM MAXDOP 4
494Gb 05:25:25 8 vCPU 150GB RAM MAXDOP 2
DBCC CHECKDB ([EDDS]) WITH NO_INFOMSGS, ALL_ERRORMSGS, PHYSICAL_ONLY
494Gb 04:02:13 8 vCPU 150GB RAM MAXDOP 4
494Gb 04:38:17 8 vCPU 150GB RAM MAXDOP 2
August 5, 2014 at 8:15 am
GilaMonster (8/5/2014)
Why are you using physical only?
What are the drawbacks?
Thanks
August 5, 2014 at 8:25 am
You miss some corruptions. Physical only is designed as a limited set of checks for frequent checks of busy servers. Since you're offloading the checks to another server, there's no good reason to skip the expensive checks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2014 at 8:52 am
GilaMonster (8/5/2014)
You miss some corruptions. Physical only is designed as a limited set of checks for frequent checks of busy servers. Since you're offloading the checks to another server, there's no good reason to skip the expensive checks.
Good to know.
So then assuming I will be doing full DBCC checks, is my VM setup optimal? Do you have any suggestions? I have as much memory as possible, but I can add CPUs, and possibly get some higher class storage and tier active data when checks are running.
Thanks
August 5, 2014 at 9:04 am
Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.
"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
August 5, 2014 at 9:14 am
What are your resource governor configurations?
What is the Max memory configured for SQL Server?
What is the memory reservation assigned to the VM?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 9:24 am
Grant Fritchey (8/5/2014)
Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.
I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.
This was the most win I could get out of the discussion. I'm trying to make the best of it.
Thanks
August 5, 2014 at 9:26 am
SQLRNNR (8/5/2014)
What are your resource governor configurations?What is the Max memory configured for SQL Server?
What is the memory reservation assigned to the VM?
Resource governor is not configured.
Max SQL memory is 150Gb.
Memory on server is 160Gb.
This barely touches memory though, when I'm monitoring it. It tops out hitting about 96Gb.
Thanks
August 5, 2014 at 9:41 am
sqldriver (8/5/2014)
SQLRNNR (8/5/2014)
What are your resource governor configurations?What is the Max memory configured for SQL Server?
What is the memory reservation assigned to the VM?
Resource governor is not configured.
Max SQL memory is 150Gb.
Memory on server is 160Gb.
This barely touches memory though, when I'm monitoring it. It tops out hitting about 96Gb.
Thanks
For BP, you should configure Resource Governor to limit checkdb. It actually runs much faster that way.
Your max memory setting is too high for this server. You should leave more for the OS than 10GB on a system with 160GB. I usually top out at 140GB for max memory.
The VM reservation from VMWare or Hyper-V should also be set to some number between 140GB and 160GB. The reservation is not how much memory is configured to the vm, but how much the vm cannot drop below.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 10:19 am
SQLRNNR (8/5/2014)
sqldriver (8/5/2014)
SQLRNNR (8/5/2014)
What are your resource governor configurations?What is the Max memory configured for SQL Server?
What is the memory reservation assigned to the VM?
Resource governor is not configured.
Max SQL memory is 150Gb.
Memory on server is 160Gb.
This barely touches memory though, when I'm monitoring it. It tops out hitting about 96Gb.
Thanks
For BP, you should configure Resource Governor to limit checkdb. It actually runs much faster that way.
Your max memory setting is too high for this server. You should leave more for the OS than 10GB on a system with 160GB. I usually top out at 140GB for max memory.
The VM reservation from VMWare or Hyper-V should also be set to some number between 140GB and 160GB. The reservation is not how much memory is configured to the vm, but how much the vm cannot drop below.
Okay, max server memory has been dropped to 140Gb, and a memory reservation has been set on the VM at 140Gb.
Is this what you had in mind for configuring resource governor[/url]? If not, can you point me to more applicable information?
Thanks
August 5, 2014 at 10:30 am
Yes, that article would be it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2014 at 10:35 am
sqldriver (8/5/2014)
Grant Fritchey (8/5/2014)
Wait a sec, if you're offloading DBCC checks, the one check you can't offload is PHYSICAL_ONLY. That one check must still be run on the original. Then, it's all the other checks, which you need to run, that can be run in a secondary machine to reduce the overhead on the original machine.I'm restoring from full backups and running the DBCC checks offloaded because running them on the production servers is not a palatable solution to management; other maintenance tasks take priority (index, statistics, full text catalog) because they impact user experience.
This was the most win I could get out of the discussion. I'm trying to make the best of it.
Thanks
Right, but you still have to run the physical checks on the production server.
"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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply