Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Load Poll


The Load Poll

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36176 Visits: 18751
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
My Blog: www.voiceofthedba.com
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3692 Visits: 3118
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
Vila Restal
Vila Restal
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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.)
BenWard
BenWard
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 821
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
----------------------------------------
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
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. Cool
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36176 Visits: 18751
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
My Blog: www.voiceofthedba.com
Vila Restal
Vila Restal
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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?
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
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. Cool
This is not something I could have dreamed about doing 10 years ago. Now it is considered slow... :-P
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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.
SAinCA
SAinCA
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 683
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search