Re: Advice on middleware products for TRUE Scaling out of SQL Server

Greg D. Moore (Strider) (mooregr_deleteth1s@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Query training -- Complex queries
    ... > Update PositionsEOM ... Here you don't have a derived table, but a correlated subquery. ... UPDATE in Books Online. ... is not written for SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Do UDFs slow down Stored Proc execution?
    ... Tuning the queries of course would be the correct thing to do. ... works beautifully from a business logic perspective. ... I have been plowing through the Microsoft SQL Server 2000 Performance ... I don't know if you actually want to see that proc. ...
    (microsoft.public.sqlserver.programming)
  • Re: Linked Server: How to check if server exists?
    ... The root problem is described in the Books Online topic Batches. ... Rick Byham, SQL Server Books Online ... linked server don't run on those development machines not having that linked ...
    (microsoft.public.sqlserver.connect)
  • Re: STORED PROCEDURE - passing table name as a parameter
    ... T-SQL, is that you get problems if the view definition does not fit into ... will need to query other system tables, for instance syscolumns. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Problem when changing Views
    ... There aree several reasons. ... I use neither of the tools, but I guess that Design View resubmits the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)