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


The Need For Speed - Upgrading Your Servers


The Need For Speed - Upgrading Your Servers

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

Group: Administrators
Points: 50485 Visits: 19003
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnis

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
chisholmd
chisholmd
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 86
Thanks for the article Steve. It left me wondering though.

As you say your metrics looked good except at certain peak times for certain timezones/geography. So what can one do to alviete the burden of those peak times without upgrading the whole box in all deminsions (or the ones peaking)?

Perhaps the developers could identify the jobs that are sucking the most resources at those peak times and scheme up some sort of pre-processing that could be done in times leading up to the peak.

I guess I approach this from a developer point of view. The first thing I'd do is an over the shoulder analysis of the work performed by users at this peak time, it could turn out that roundtrips to the db could be reduced simply by small changes to application design. I have seen apps where the user ends up having to load up a data heavy screen simply to access a link or a button. So the db is doing a bunch of work to deliver data tghat the users doesn't actual care about. Multiple that by 5,000 seats at tax time and I guess it would save cycles simply to offer them a direct link to the info they want.

I guess there are dozens of schemes that developers can employ; pre-processing, distributed processing, or other design changes but in the final analysis weighing developer time versus a shiny new box, the box might be allot cheaper

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
David.Poole
David.Poole
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5801 Visits: 3226

I would second the motion to investigate the peaks.

I work on a large web CMS that has a complicated caching mechanism. If I get the CMS caching right the database load drops as most stuff gets retrieved from the cache.

User perception is also a problem. I remember thinking an IBM AT was fast!!

Today the performance wows them, tomorrow exactly the same performance is taken for granted.



LinkedIn Profile

Newbie on www.simple-talk.com
Henrik Staun Poulsen-105872
Henrik Staun Poulsen-105872
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 1

Yesterday, I went to a Microsoft seminar where Mogens Nørgaard were speaking about obtaining the best performance for a DB system. Key points were:

1) Always investigate on a job/session level. Do not use overall counters.

2) Find out where precisely the time is spent.

Have a look at http://www.baarf.com/ on why not to use Raid 5.

He was hoping that there were better "system metrics" build into SQL 2005, so that you were able to better pin-point where time was spent.

Regards,

Henrik Staun Poulsen

Denmark





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

Group: Administrators
Points: 50485 Visits: 19003

As far as this system is concerned, it's a DW backend for a Microstrategy backend, so many of the peaks are not tunable. End suers pick a few things and it generates 50 lines of SQL (or more). So we've built it for the 80-90% of the load. Just can't get the last without a huge upgrade, and even then who knows.

I agree that most of your tuning is in the application and possible indexes. I've had a couple seminars where we really run profiler over a few days, capturing SQL and then grouping and sorting to find the top 10 worst performers and focusing there. We also look at the frquency of these queries and use that to gauge whether to tune them. I might have a 2 hour query, but if it's run once a year I might not tune it. But a query that takes 2 minutes and it run 100 times a day might be something to focus on.

The reality is that the most frequently complained about queries are the ones we focus on, trying to tune or rework them (and the app) to get the perception of performance down.



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
stefdem
stefdem
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

If it's for reports try SQL Reporting Services where you can schedule reports delivered to users.

So you can run stuff overnight, overweekend or in offpeak periods and deliver it to users by mail


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