Here is something you technical folks might find interesting – the rest of you may just may just get confused..:
A coworker and I ran into an odd problem when developing an application on a new server at one of the clients I work at. This new server is a new 64 bit HP with two quad core hyper-threaded processors giving us a total of 16 cores. This server is being used as a virtual server, and so for our SQL Server we provisioned a bunch of memory and 8 virtual cores. (Even though we could have purchased SQL Server 2012, the licensing by core made it unjustifyably expensive, so we are running SQL Server 2008.
The following query would sporadically return results, then no results, then results when run over and over:
select c.firstname, c.lastname, IH.invoicedate
from customer c
inner join invoiceheader IH on c.ID = IH.ID
inner join invoicedetail ID on IH.ID = ID.ID
where c.customercategory = 1
and c.activedate <= getdate() -- customer is active
and isnull(c.inactivedate, getdate()) >= getdate() - customer has a future inactive date or no inactive date
We tried this query on our old production servers, and didnt have the problem. After several minutes of head scratching, we finally came up with the solution and rewrote the query to:
select c.firstname, c.lastname, IH.invoicedate
from customer c
inner join invoiceheader IH on c.ID = IH.ID
inner join invoicedetail ID on IH.ID = ID.ID
where c.customercategory = 1
and c.activedate <= getdate() -- customer is active
and (c.inactivedate is null or c.inactivedate >= getdate()) - customer has a future inactive date or no inactive date
We theorize the root problem is that this query is exceeding our max cost of parallelism and splitting up the task of running this across the multiple CPU cores available. The assumption is, each core is taking responsibility for finding getdate() (SQL Server function to return the current system date) and the core that was calculating the date for the isnull(c.inactivedate, getdate()) clause was getting an earlier date than the core that did the final getdate() comparison at the end, sporadically determining that all customers without an inactive date were expired.
The other option is it is just a difference in the way SQL Server 2008 handles query processing, but I lean to the theory that it has to do with increased parallelism available with new servers. Arguably the original query was not the best way to do the date comparison, but we were surprised that SQL did handle this in a sporadic manner.
So this is another lesson for you developers out there that as servers start to increase the number of cores and spread out processing across multiple CPU’s, concurrency issues will increasingly become a factor, even if you are like me and stay away from multi threading applications.