[dba-SQLServer] SSMS priority

jwcolby jwcolby at colbyconsulting.com
Sat Sep 17 08:54:39 CDT 2011


I am running a query which is maxing out all 12 cores assigned to SQL Server.  Just as an aside i 
had to reduce the number of cores assigned in order to reduce the temperature of the cores below 
where it would blue screen.

In any event I now have 4 cores and 8 gigs assigned to the OS (Windows 2008R2), or more correctly 
not assigned to SQL Server.

My issue is that when all the resources are in use, SSMS responds very slowly.  For example I opened 
SSMS and clicked on the databases tree and it took several minutes to drop down.  After that things 
took 10 seconds to 30 seconds which normally take a second.

Is there a setting somewhere which will tell sql server to leave some resources for SSMS, or 
basically for any process other than the thing it is currently doing.  In this case it is running a 
simple append query, about 7 fields, from one database / table (index on those 7 fields) to another 
database / table.

In this specific case I am trying to copy these 7 fields for about 150 million records, rotating 
media for both databases, and for some reason it is incredibly slow.  The records do have to be 
inserted in sorted order, sorted on 3 fields.

-- 
John W. Colby
www.ColbyConsulting.com


More information about the dba-SQLServer mailing list