Resource Governor basics – Questions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Resource Governor basics – Questions

There is a very useful article on this site, by Ashish Kumar Mehta regarding the Resource Governor;
http://www.sql-server-performance.com/articles/per/Resource_Governor_in_SQL_Server_2008_p1.aspx
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.?

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |