[dba-SQLServer] Multi-core madness

jwcolby jwcolby at colbyconsulting.com
Wed Oct 6 11:23:28 CDT 2010


I have never had more than 4 cores at my disposal.  On the old (SQL) server I had two cores 
dedicated to SQL Server.  On the new system I currently have 8 cores total and 6 of those dedicated 
to SQL Server.

In the past I would do things like build a multi-field index on a 50 million record table and it 
would max out the two cores.  I pretty much couldn't do anything else.

Today I am building multi-field indexes on an "off-line" copy of my database from hell.  Task 
manager tells me it is using about 40% of the total processor power, however the two cores dedicated 
to the system are not doing much.  The other 6 cores are chugging away somewhere (visually) just 
under 50%.

I needed to BCP (using the internal export wizard) about 30 million PKs and email addresses to a csv 
file.  When I started that running, Task manager informed me that I was using just under 60% of the 
available processor power, but the first two cores (dedicated to Windows) started chugging away, 
presumably doing file IO and the like.  My 6 SQL Server cores jumped up to around 65%.

BTW, the export process ripped it out pretty darned fast.  I didn't time it but the total took a 
minute or so.

So I was able to get two tasks going, and still had plenty of horsepower left over.  I then 
installed the 64 bit WinRar, which can use multiple threads, and had it compress the resulting text 
file as SQL Server continued building indexes.  All very smooth.

If I get no "bandwidth complaints", I will continue to post occasional emails regarding how long it 
takes to do stuff vs the old server.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list