Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The Load Poll Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 10:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
Comments posted to this topic are about the item The Load Poll






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1399184
Posted Friday, December 21, 2012 1:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 2,865, Visits: 1,705
At one point one of our old 32bit SQL2000 clusters were getting through 8,000+ batch requests/sec regularly at peak.

Different lines of business have different busy periods both seasonally and weekly so an average doesn't mean much.

At 8:30 in the morning on December 23rd I can see people arriving at work and starting to use our website and the transaction/sec on the old box has jumped from 200/sec up to 787/sec.


LinkedIn Profile
Newbie on www.simple-talk.com
Post #1399261
Posted Friday, December 21, 2012 2:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 15, 2013 3:19 AM
Points: 22, Visits: 64
"I was taking to someone recently and this person had a large transaction load on their SQL Server."

Is that a euphemism and is that why you took to them?

(Sorry, just a joke. The typo made me think of that and it amused me so I had to share it.)
Post #1399291
Posted Friday, December 21, 2012 5:24 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:41 AM
Points: 363, Visits: 624
Right now at the quietest part of the day with probably half the company having taken the day off our busiest SQL Server database is fluctuating between 250 batch requests per second and 800 with regular (every few seconds) spikes up to 2500+. about 800 over a 30 second average.

I don't have any other metrics to hand but when the business are using the application in anger I'd expect to be averaging about 1500 to 2000/s

we also get peaks of activity during import phases which occur throughout the day - don't have any metrics on that unfortunately.

CPU is between 0 and 1%, DB I/O is less than 0.1MB/s

I'd think that 1000 transactions per second is actually quite low usage on a system with the number of users we have. I'd think our server could probably handle upward of 16000 tps maybe even 20k at a push.






Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1399352
Posted Friday, December 21, 2012 7:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
I has been at least 10 years since I have had a Production SQL server that averaged 1000tps or less during regualr business process cycles. Currently our main Database servers for our line of Business applications have process they support that average around this. Add to that Share Point Server support and JDE and you got a very large average tps.
This is all on our older SQL 2005 single node and Fail over clusters running on x86 systems.
Our newer x64 class systems can handle a lot more than that, but we have had some issues with vritualization and using iSCSI SAN interfaces for our shared storage.
Post #1399396
Posted Friday, December 21, 2012 8:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:31 PM
Points: 32,780, Visits: 14,941
Vila Restal (12/21/2012)
"I was taking to someone recently and this person had a large transaction load on their SQL Server."

Is that a euphemism and is that why you took to them?

(Sorry, just a joke. The typo made me think of that and it amused me so I had to share it.)


Doh! I read that a few times, and edited this without noticing the mistake.

It's corrected.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1399451
Posted Friday, December 21, 2012 8:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 15, 2013 3:19 AM
Points: 22, Visits: 64
So after having made fun of such a minor typo I feel obliged to give my figures:

I'm just looking after a small set of databases - just 100 users on a LAN.
Today isn't a good day to measure stats for it. Many people are on leave.
But anyway peak tps today was 12!! And averaging about 0.2.
On a busy day I think it would be double or triple that.

Perhaps not stats worth mentioning but it's important to have it known that not all databases are enterprisey multi-billion-row, multi-hundred-thousand-user monsters (and it's not a competition and even if it were I wouldn't want to win it: I don't envy those who after look after them). The little ones count too.

Lastly, this method of collecting a poll isn't very efficient for you or the contributors.
Does this blog not have a survey facility or at least a thread that can be voted on?
Post #1399459
Posted Friday, December 21, 2012 9:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Vila Restal (12/21/2012)
Perhaps not stats worth mentioning but it's important to have it known that not all databases are enterprisey multi-billion-row, multi-hundred-thousand-user monsters (and it's not a competition and even if it were I wouldn't want to win it: I don't envy those who after look after them). The little ones count too.

I totally agree. Our database that generates the most TPS actualy has one windows user or servcie account that connects for the services our 15 primary business users consume. These processes spawn multiple threads in multiple services running on 3+ servers. This ends up creating an average of 300-500 active dtabase connections executing batches that do 1500+ tps. So in a way our average tps per application user on this database is 150-200. Not too shaby for one of several systems supported by a team of 10 people including our CIO and First Level support.
This is not something I could have dreamed about doing 10 years ago. Now it is considered slow...
Post #1399503
Posted Friday, December 21, 2012 9:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
The environment is a rural hospital I ran stats over our last month for all DBs under my team's purview (which excludes the data warehouse and our Electronic Health Record) and excluded system databases and the DBA utility DB as well as low use times which also eliminated some DBs since they never averaged over one transaction per second (test or DR for example). I can't pick one time range because some servers are 24x7 and other are 8-5 depending on what they're used for. I also excluded one server that went through an application upgrade and migration since it's not a fair representation of load.

Our highest average is 331 tps with a peak on that DB of 772 and our highest peak was 2,106 with an average of 10 for that DB. The average average was 16 and the average peak was 92.

And thus concludes my TPS report.
Post #1399511
Posted Friday, December 21, 2012 10:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 6:51 PM
Points: 117, Visits: 547
SQL2012EE on Win2K8R2 active-passive 128GB RAM single 6-core E5-2640 @ 2.5 GHz on Dell R720 runs at:
- Transaction/sec: 13800 peak, 1600 average excluding peaks.
- 1170 Batches/sec peak, otherwise between 100 and 800 per monitored minute.
- 15 peak active sessions, 3/minute normal average.
- 12MB/sec peak Physical IO, 1MB/sec average otherwise.
- Page life expectancy 300,000.
- 500 Log Flushes/sec peak, 350 average.
- 58% CPU Utilization max for 1 minute in every 10, otherwise under 10%.

Specifications for the machine courtesy of Glenn Berry's excellent blog posts.
Post #1399531
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse