SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What's Happening Here?


What's Happening Here?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63182 Visits: 19115
Comments posted to this topic are about the item What's Happening Here?

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
stephen.sarre
stephen.sarre
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 297
Hi Steve
Are you missing the data for the buffer cache hit ratio graph?
Steve
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2272 Visits: 2145
stephen.sarre (12/9/2010)
Hi Steve
Are you missing the data for the buffer cache hit ratio graph?
Steve


The Buffer cache hits are pegged at 100% (all that is required is found from cache).

I agree that the next things to look at are disk I/O and network contentions. I myself have seen hardware (disk) falling behind a couple of times, and predict I/O being the prime reason for performance bottlenecks in the future.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Christoph D
Christoph D
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 354
Hi,
I am wondering how you send you emails. Do you send them via an exchange-server inside your network? Or do you have some external provider sending your mails?
In the last company I worked we had over the time three different approaches to send our mails so that is why I am asking. Each approach had its benefits and back draws depending where you want the workload to be.
And is this just one newsletter with one list of recipients? Or are these different newsletters (which can’t be easily separated via a switch) so the Server has to read over the same table quite a few times. With 700.000 newsletters it should not be that much workload, depending what the normal workload and performance of the server. I guess that maybe there is tuning-potential in the application sending the mails or the workflow because you know what you are doing on the SQL Server side. And in the old company as well as in the new one most of the time I help the developers pinpoint down potential misdoings in the applications or bad design because the server is on the other end, bad behavior of an application can be seen pretty clear as soon as it hits the database.
BR
Christoph
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63182 Visits: 19115
We send mail through a .NET application, which sends them individually. There is a limited customization for each one, and so the database needs to be read to send them. We actually read a batch, process on the client, send, update the database back with sending records.

700,000 emails in a few hours is no joke. We examined lots of services and processes over the years. Managing that workloads requires lots of tracking and processing to comply with SPAM laws. There potentially could be a more efficient way, and it's probably worth examining. Currently there are about 3 tables used to store the emails, the recipients, and something else (escapes me this minute). We keep about 4 days worth of tracking there, though there are logs written to local disk on the clients as well.

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
Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 1121
Steve Jones - SSC Editor (12/9/2010)
What does this tell you?

From a pure hardware point of view, NOT taking into consideration optimising the software like mu_rauer said, investigate RAIDing some SSDs.

Compare the ROI of optimising the emailing software vs. the ROI of a (few?) SSD array(s). You might want to look at OCZ's HSDL

About OCZ's HSDL:
better suited to workstations and the HPC market. Backing these projections are OCZ’s initial performance numbers. The company is claiming read and write speeds of up to 740 MB/s, and up to 120,000 IOPS. Those figures are insane. Clearly, they’re also in excess of what a single SAS or SATA interface could accommodate. And according to OCZ, this is only the first generation of the technology—it’s supposed to get faster from here.


You might also want to have a gander at this article entitled "How Do SSDs Redefine Storage Performance?". This is a comparison of two Samsung 470 SSDs vs. eight Fujitsu 15K RPM HDDs.
It is important to note that there are faster and somewhat more efficient hard drives available, which would have shifted the results a bit in favor of the hard drive arrays. Yet, the differences we see are significant enough to be sure that whatever you tweak on the hard drive side still cannot beat a fast and efficient SSD array. Also, we had to use the 15K RPM Fujitsu drives because no hard drive maker wanted to provide drives once we told them what we were planning. This alone should speak volumes.

Andrew Jackson
Andrew Jackson
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 1121
Steve Jones - SSC Editor (12/9/2010)
700,000 emails in a few hours is no joke. We examined lots of services and processes over the years. Managing that workloads requires lots of tracking and processing to comply with SPAM laws.
Our company also had to be careful not to be marked as spammers, although we only have to send out +-4600 emails.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63182 Visits: 19115
AndrewJacksonZA (12/9/2010)
Steve Jones - SSC Editor (12/9/2010)
What does this tell you?

From a pure hardware point of view, NOT taking into consideration optimising the software like mu_rauer said, investigate RAIDing some SSDs.

Compare the ROI of optimising the emailing software vs. the ROI of a (few?) SSD array(s). You might want to look at OCZ's HSDL

About OCZ's HSDL:
better suited to workstations and the HPC market. Backing these projections are OCZ’s initial performance numbers. The company is claiming read and write speeds of up to 740 MB/s, and up to 120,000 IOPS. Those figures are insane. Clearly, they’re also in excess of what a single SAS or SATA interface could accommodate. And according to OCZ, this is only the first generation of the technology—it’s supposed to get faster from here.


You might also want to have a gander at this article entitled "How Do SSDs Redefine Storage Performance?". This is a comparison of two Samsung 470 SSDs vs. eight Fujitsu 15K RPM HDDs.
It is important to note that there are faster and somewhat more efficient hard drives available, which would have shifted the results a bit in favor of the hard drive arrays. Yet, the differences we see are significant enough to be sure that whatever you tweak on the hard drive side still cannot beat a fast and efficient SSD array. Also, we had to use the 15K RPM Fujitsu drives because no hard drive maker wanted to provide drives once we told them what we were planning. This alone should speak volumes.


I'm not sure how much of a budget I have to change hardware, especially as this works. There is a few minute slowdown as things ramp up, but overall the site performs well during sending. However I would definitely consider SSDs for something like this if performance were an issue.

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
rdameron
rdameron
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 346
Steve,

While exploring SQL Monitor's public demo this morning I DM'd @sqlmonitor the following...

Noticed on sqlmonitor public site, Autogrowth for Files set to defaults on 252314-sql1 sqlservercentral instance. PBM would flag this.


Autogrowth increment set to 1 MB for the sqlservercentral data file and 10% for the log file.
Policy Based Management would catch the use of a percentage for growth. The 1 MB growth increment is too small and a custom policy could be setup to catch that.

@sqlmonitor said this was done on purpose for some of your future blog posts and that I had caught it prior to the posts beginning.

I just love the idea that I can monitor machines with just a browser.

Regards,

@RonDBA



Steve Jones
Steve Jones
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63182 Visits: 19115
I'm looking to write something up on that. Once I get some screenshots and we schedule the maintenance, we'll change that. Part of what we are showing is that we make mistakes like everyone else. The website is a portion, and not the main portion, of the business.

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
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