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

IanIpp (ian.ippolito@xxxxxxxxx) writes:
3) Regarding tuning queries,etc.

Yes, we have control over the code but we already run
extensive/constant query tuning and add/adjust indexes and regularly
use the Database Tuning Advisor (see my post here for some of the
existing bugs I've found in SQL 2005's DTA:
http://rentacoder.com/CS/blogs/real_life_it/archive/2006/03/17/447.aspx
). We also update statistics and defrag the indices (and rebuild the
ones that can't be defragged). There are 2 bugs I have open tickets on
with indices not being defragged even after rebuilding...and not on
small tables, but large ones with thousands of pages of data. I'll
update my blog once MSFT gives more information on what is going on.

I don't want to belittle, but I have a strong feeling that you still
have a lot to gain by tuning the application. Maybe you've past all
the simple ones: adding indexes, finding bad queries etc, and you
will now have to look for more structural issues. That is, how much
iterative processing (cursors and the like) do you have?

After all, the numbers Stu gave for his system were appalling better
than yours.

Of course, TPC-C benchmark was even further afield, but that is a
value that more demonstrates the outer edge of what is at all possible.

I can't give any numbers for our system, but none of our customers are
close to the load that yours and Stu's system see.

--
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: 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: Advice on middleware products for TRUE Scaling out of SQL Server
    ... we had two boxes maxing out and were ready to buy a 3rd ... thing) but it can show how much a difference simple tuning can make. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • RE: Round 2:Database Engine Tuning Advisor suggestion to replace synta
    ... I think that I found the reason. ... "I am a developer in the Database Tuning Advisor team. ... As we can see that the reason is that for sp_executesql, SQL Server could not return an execution plan for tuning, so it ...
    (microsoft.public.sqlserver.tools)
  • Re: Tools similar to Quest SQL Tuning module.
    ... btw.) Symantec and Embarcadero have some performance tuning products, ... "I support PASS - the Professional Association for SQL Server ... and suggestion alternative methods of writing it similar to the Quest ...
    (microsoft.public.sqlserver.tools)