I am working on a system, there is a cisco load balancer that can handle 40K requests.

There are two app servers. There are 12 or so web services.

and there is a SQL server with 2TB of data.

Bottle neck here is SQL server, it is on a single machine with Quad Core CPU, and 80 GB ram.

There are about 2.5 million items stored in database.

The kind of queries are , select product with some properties which are not indexed.

There is a huge index and we don't want to index everything, because index fragmentation is really hurting performance.

I thought about caching, distributed caching, but some of the queries are not very convenient for caching, such as give me companies that starts with foo. There is a huge combination there.

How would you go working on this issues?

There is more select than insert. mostly select. There are several small select. and there are some joins with 3 tables.

  • 1
    This not on-topic for StackOverflow. Voting to move to dba.se
    – Kermit
    Commented Mar 15, 2013 at 13:12
  • 1
    Without much data about app and with such a RAM, try to increase SQL buffer pool manually.
    – Xaqron
    Commented Mar 15, 2013 at 13:15
  • 4
    @DarthVader You have conclude that the SQL-Server and indexing is the bottleneck. How isn't it database realted? Commented Mar 15, 2013 at 13:16
  • 3
    Step one hire a dba with experience in large systems. This isnot some easy questions that someone on the InNternet can answer. Likely there are many things including redesigning your database or rewriting your queries or redoing your indexes or updating your statistics that need to be done. You may need to look at partioning the data and hardware changes.
    – HLGEM
    Commented Mar 15, 2013 at 14:50
  • 1
    I'm inclined to agree with @HLGEM. 2TB and 40k requests/sec is not Q&A territory. Suggestions made here will be shots in the dark and you're likely to make matters worse by following them blindly. That said, stuffing the server full of RAM won't hurt. Commented Mar 15, 2013 at 15:30

3 Answers 3


With 2.5 million items I would expect your indexes to be rather large. Particularly if you are indexing on large fields. Also just as an FYI if your primary key is large that doesn't help either. Any non clustered index has the primary key (assuming that is your clustered index) in it regardless of if you include it or not. It has to as a reference back to the table.

So if you have a large primary key (and only if you have a large primary key) you might consider creating an identity column and changing that to your clustered primary key. You can keep your existing primary key as a non-clustered unique index. Also if you are worried about index fragmentation REBUILD/REORG it. It's part of the ALTER INDEX command in SQL 2008 and up. You can even do it on line.

My suggestion to you is to add the indexes you need (within reason). Generally 3 or 4 non clustered indexes isn't going to hurt performance. Honestly you can probably get away with 10 or so without a problem if you really need it. Test on your system as mileage may vary. Personally I would make sure that you have a clustered index as well. Also add a maintenance job of some type to do your index REBUILD/REORG. There are a number of them out there that will even check your current fragmentation and REBUILD or REORG intelligently as needed.


Time to break out the DMV queries:


It will probably suggest you remove a few indexes and add a few.

Just make sure you script out any indexes you might drop. Because if you drop it, and the next day someone is like "This certain functionality doesn't work well anymore"...be ready.

That's the quick way.

The longer way is too:

  1. Identify your worse performing stored procedures.
  2. Tweak the indexes.
  3. Test and Retest.
  4. GOTO #1.

That's how the best DBA's with whom I've worked..operate. They don't wait for "reports", they just keep rinse and repeating until there is nothing to rinse and repeat.

Full disclosure. I am not a dba. But I had to play one once on TV.


I have a few ideas, hope they are useful

It seems your indexes need a bit of work. Firstly fragmentation, it will take ages to defrag a 25+ million row table, therefore use a script that only rebuilds highly fragmented indexes (there's a few on the internet) Next if the index column is too wide, consider using multiple indexes and using index joins. (I'd prob test this in a non production environment before deploying to live)

Next check your stored procedures, try and get as many of them to index seek, furthermore ensure the where and join clauses have been written in a manner that uses the index and prevent index or table scanning. Also avoid select * or using functions in the where column, and try to bring back min amount of data

Lastly 80gb will prob be beyond the means of standard or bi edition of SQL Server (in believe they are 64 gb. Therefore set both max and min memory to 64gb unless you have enterprise edition).

Hope this helps as a starter

  • Are you sure you answered the right question? They mentioned 2TB vs your 80 GB and so on. Commented Aug 1, 2016 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.