March 13, 2008 at 4:11 pm
I'm going to toss out a question that I already know it "depends" but I was hoping to get some opinions, even if they are in general terms.
I've been telling developers to be careful when they write stored procedures to return only the record set they need. Too much causes I/O and not enough will cause lots of calls to the database for individual records. Too many calls and it can become an issue. For example if you need to load a popular web page with 1000 records, you shouldn't make 1000 calls to get each record, go for the range you need. If that page is called by 100 users that is 100,000 calls for that page (roughly)
So here is the question. I'm tracking data and was curious to know on an "average" (whatever that means) when would you consider SQL Logins per second to be high. 1… 100.. 1000… 10,000... 1,000,000… And what size of a load is on your server? Is it OLAP or OLTP?
If you have a simple stored procedure that is called tons of time a second like the example above you would think that too many logins/logouts and calls to that object would generate a wait state at some point? For the sake of simplicity assume that there are only shared locks, so we can rule out blocking for Exclusive.
March 13, 2008 at 8:36 pm
Trevor, first, check out the perfmon counter for sql server:general statistics:logins/sec counter. btw, this can also be found in the sys.perfinfo table. Then check out the following article: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64044.
Then checkout articles on connection pooling.
DAB
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply