Posted by Erland Sommarskog on 04/15/06 00:49

Greg D. Moore (Strider) (mooregr_deleteth1s@greenms.com) writes:
> Well the first thing I would say is make damn sure it's not a code issue.
>
> To relate a story, we had two boxes maxing out and were ready to buy a 3rd
> in order handle the load.
>
> After reading a white paper on performance tuning, I was able to work with
> our developers to rewrite a single stored procedure and get to the point
> where ONE box was handling the entire load and still had room to scale.
>
> Ok, that's an extreme case (the boxes were basically doing only the one
> thing) but it can show how much a difference simple tuning can make.
>
> Ok, assuming that you've done that, if you can break any of the stuff
> into read-only queries, one thing that might work is setup the current
> server as a "publishing" server and use replication to push the data to
> "read-only" servers.

To be blunt, I think Ian has a lot of potential here. Provided of course,
that he has control over the code. If he has a some sleazy third-party
app, tuning may not be that much of an option. Then again, SQL 2005
offers plan guides where you can give hints or complete plans to queries
without direct access to the source code. And he can still add indexes.

>> The "in the box" solution for SQL Server 2005 scaling out is the DMV.
>
> DMV, I'm not familiar with that acronym.

Dynamic Management Views, the new interface to engine-state information
in SQL 2005.

I guess that Ian was thinking of DPV, Distributed Partioned Views.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]