Resource Governor basics – Questions | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Resource Governor basics – QuestionsThere is a very useful article on this site, by Ashish Kumar Mehta regarding the Resource Governor;
The Resource Governor allows for specific pools and limits on CPU and memory usage, which can alleviate "..sudden spike in CPU and memory utilization thereby resulting in slow responses to query requests…" as per the article.
My server hardware is such that CPU and memory usage is never a problem, I never get ‘spikes’ that occupy 100%. I’m running a dedicated server with 4 quadcore processors (therefore 16 cores) and 36gb RAM, 24gb is set memory allocated to SQL Server. I have 2 x 4GB fibrechannel cards to an external SAN running SAS drives in a RAID 10 configuration.
My question is as follows: My performance bottlenecks, when they occur, are always on disk issues; WriteLog and I/O Waits, etc. Would Resource Governor therefore be of no use to me ? (since memory and CPU is never a contention issue on my server).
If implemented, can the governing of CPU and memory resources indirectly translate to a governing of disk I/O resources ? My guess would be "no" because statements and queries have huge variances on exactly what ratio of disk, memory and CPU resources they use per execution.
Furthermore, if a user is performing a DML statement (insert, update), then this statement takes place in a transaction and can possibly lock pages until completion. If Resource Governor were in effect to restrict resources to such a DML query, then logic tells me that this would actually worsen server performance for all users afftected by the DML lock, since their queries would remain suspended until the DML statement completes.
Am I correct ?
I don’t know if RG can help in your case.
If your issue is I/O Disk then, may be, you have lack of indexes or poor code.
Did you run Profiler, find long queries, check execution plan and try to optimize?.
Also, do you have Maintenance plans to defrag, update statistics, etc.?