Apr 27, 2006
sms: reporting is single-threaded
a fellow mvp, brian rogers, started drilling me about sms reporting the other day (probably because all the smart people are hanging out in san diego drinking margaritas and eating fish tacos in Old Town pretending like they're at a conference). the questions started around how to best setup sms reporting for load. hashing back and forth the basics, it was assumed that sms reporting probably taxed sql server the most ... since the only bottleneck was page faults. brian alluded to a possible problem where the sms reporting function executes one request at a time. we started looking into this and discovered this to be the case. reviewing logs, he discovered that this was indeed the case. i did the same test myself and found that to be happening. the reporting point is, in fact, single-threaded. even though the database can handle multiple queries, the reporting point only sends one at a time. if the browser is closed after a query is sent, the query request does not end. in fact, it'll keep running on the server until it has completed or timed out. this means anyone running a massive report can stall anyone else's request. i can only speculate that the single-threaded operation is to ensure that reporting doesn't overwhelm the sms server/database from fulfilling other functions. the problem here is, what if you have a design where reporting is handled from a database with no clients? that implies that regardless of it functioning solely for the purpose of reporting, it is still single-threaded. it seems the only solution is to add additional reporting points that users can connect to and request reports with.