jwcolby
jwcolby at colbyconsulting.com
Wed May 19 15:03:28 CDT 2010
Jim, I am using SQL Server 2008 and I do some pretty hefty table processing. I can tell you that I often get 100% utilization of all the cores I give SQL Server, though I only have a single socket quad core machine with 16 gigs RAM, and I only give SQL Server 3 of the 4 cores and 12 gigs - precisely in an attempt to prevent the "system lockup" that you mention. To be honest this the first time I have seen a "system lock" mentioned, though I absolutely positively believe that is what is happening. I would be interested to know if there is a way to prevent that from ever being implemented. I had a lockup the other day. I learned a couple of things (like assigning a specific number of cores and specific amount of memory) which pretty much made the problem go away but very very sporadically I get one. I waited for a couple of hours for something that didn't seem like it should take that long, then ended up having to reboot my machine. Rebooting a SQL Server while it is "doing something" is pretty scary and usually damages the database that it is working on. John W. Colby www.ColbyConsulting.com Jim Lawrence wrote: > Hi All: > > A couple of nights ago, I went to a series of lectures given by a fellow > named Tiberiu Covaci, Swedish owner of a company called Multi-Core and he is > now doing a tour as a trainer for Microsoft. (His next lecture stop will be > in New Orleans.) > > His training lecture was on Parallelism. The first lecture covered the basic > concepts of computers and their new multi-core design, a design made > necessary as single core computers ran into the physical wall as of 2005. He > then went on to describe the attempts by developers to utilize these new > multi-core systems. It has been a slow process as you can imagine. > > The rudimentary attempts of threading and hyper-threading were far from > successful. First a single thread will consume up to 200 cycles just to get > substantiated and its deployment still requires the central CPU and to spawn > hundreds of threads, in a pool and then there was the issues of > synchronizing the process results. Even the best developed multi-threaded > applications, like MS SQL 2005 could over-load a computer when challenged by > a heavy duty task. MS even went so far as to apply a system lock when > performing resource heavy jobs... that means all other functionality on the > server would cease... no multi-tasking, until the job was completed... even > then sometimes a process would freeze up a server. (We have all heard of > that exact situation from a number of our DBA members.) > > Our lecturer had designed a couple of short programs to showed how > multi-threading was implemented and then demonstrated how these apps when > over-loaded would crash or lock out the system. He used a Quick-sort, demo > while splitting it into multiple pieces, multi-threading it and slowly > increasing the sort data showed a 30 percent gain per thread and a 70 > percent gain per core (maximum gain) but it also showed virtually not > performance loss as the data increased (10 to 100 million records) until the > process locked up the CPU. We watched the system progress via the Task > Manger's CPU display. > > Lecture two; enter Parallelism. This process has being developed on the > latest versions of Java and .Net frame work (4 or greater). He demonstrated > two new core features/objects Parallel and Task. Using Parallelism requires > a bit to decompose a problem into its components. He used an example of a > recipe where a number of steps was required from initial preparation, to the > cooking and to the table. The program which ended calculating the time was > initially designed in a standard C# routine. > > The app was then recoded using Parallel running tasks. Each task can run > independently, but wait for a process to be completed if required by > scheduling, monitoring a semaphore, event, state and/or wait loops. We also > covered how to handle synchronization, data sharing and how to avoid > deadlocks. By using pararllelism the meal was completed in half the time... > It showed how 6 people could do a job faster than one... now that's obvious > but maybe not so obvious with a computer application. > > The most telling observation was when viewing the Task Manger's CPU display. > No matter how heavy the requirements that were imposed on the system, the > CPU demand remained very low and flat and each core showed an even > distribution of utilization. > > I was totally impressed. Now I know how such super databases as Cassandra, > using Java Parallelism works. My suggestion to all those of you using MS SQL > 2005 or less; "Bail out quick and get you hands on the latest MS SQL... > there will be no comparison in performance." > > A couple of recommended books on the subject are: > "Patterns of Parallel Processing" and > "Concurrent Programming on Windows" > > The latest .Net has all these features built in...just ready to use. > > Jim > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >