Can resource governor apply based on what DB is being accessed?

• jasona.work

  SSC Guru

  Points: 50083

  More actions

  May 1, 2013 at 11:16 am

  #277066

  Sadly, I don't have a "play" environment at work anymore, and forgot to putz with this myself at home, so I'm asking.

  There is a user DB at my job, which was recently moved to a SQL2008R2 system from SQL2005. The end-users report that since the move, the response time from the DB has gotten longer. The contact person stated that on the previous server, MAXDOP was set to 1, and requested we do that on the new server.

  To which, I explained that being a server-wide setting, this would impact everyone else with DBs on the server, likely adversely affecting everyones performance.

  That was the last I heard from them.

  Since then, I was poking around in the Resource Governor, which is currently not set up on any of the SQL servers, and saw that it can apply a MXDOP setting that overrides the server setting...

  So, if they come back pushing for MAXDOP, is it possible to create a Classification Function for the RG so that any queries against one particular DB will then be processed by the Workload Group which would force a MAXDOP=1?

  I've tried Googling for this, and not found a clear answer, so I'm turning to the forums...

  Thanks!

  Jason