Francisco Tapia
fhtapia at gmail.com
Sat Sep 17 19:05:47 CDT 2011
If your sorting the order and the recordset is large, you may need to ensure your tempdb is also on fast disks, what would also help on the insert would be if there are no indexes on the target table, at least during the insert routine. Sent from my mobile On Sep 17, 2011, at 6:55 AM, jwcolby <jwcolby at colbyconsulting.com> wrote: > 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 > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >