SQL Server Performance

Hardware setup for a database server with full-text index

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by gregbalajewicz, Oct 15, 2007.

  1. gregbalajewicz New Member

    Hello Everyone,
    I am developing a asp.net application using sql server 2005 with full-text searching.
    Question
    I am looking for a recommnded hardware setup for the databases server. I know there is no one answer but I am wondeirng if there is a microsoft guide that I could use.
    If anyone has any experience, i would welcome your comments as well.
    I am estiamting that the full text index will be about 2-4 gb in size, most of the activity will be full-text searches.
    Also, any good benchmarks out there for full-text searching or sql server tps ?
    Thank you for your time!!
  2. thomas New Member

    All the usual recommendations apply

    -sufficient RAM
    -separate user data, user logs, tempdb, system data, backups
    -in your case, a separate partition for your FT data would be an idea
    -ensure you have sufficient disk capacity for the lif eof the server - planning is key
    -ensure your sofwtare edition (SQL Server Standard, Enterprise, Windows Advanced/etc) is sufficient for your hardware
    -ensure you have enough processing 'grunt'

    Mostly common sense...
  3. gregbalajewicz New Member

    Hi Thomas,
    Thank you for your thoughts!
    Some questions - when you say "separate partition for your FT data" - do you mean seperate physical disks? or just partitions? i would think that a sepeate partition alone yields little performance improvements no?
    As to the ram, what I am looking for, is some way of estimating amount of ram I will need. Assuming I have a 4gb full text search, would 4GB be sufficient? i am assuming no... the reason I ask, is in this once I need more then 4GB, i need more to move sql server enterprise which is greater cost...
  4. satya Moderator

  5. thomas New Member

    Yes, I meant separate physical partition. Different logical drives on the same physical array is fairly pointless. For RAM, i don't think 4GB FT data necessarily requires 4+ GB of RAM.

    Your database data requirements, and procedure cache, are equally, if not more, important. How large is your database? How much RAM does it use on current hardware, what is your performance like, what is your buffer cache hit ratio like?

    Fact is, hardware purchases aren't made often, its' worth making them big ones. A more powerful server will last longer and will impress your users with its performance more.
  6. gregbalajewicz New Member

    [quote user="thomas"]
    Yes, I meant separate physical partition. Different logical drives on the same physical array is fairly pointless. For RAM, i don't think 4GB FT data necessarily requires 4+ GB of RAM.

    Your database data requirements, and procedure cache, are equally, if not more, important. How large is your database? How much RAM does it use on current hardware, what is your performance like, what is your buffer cache hit ratio like?

    Fact is, hardware purchases aren't made often, its' worth making them big ones. A more powerful server will last longer and will impress your users with its performance more.
    [/quote]
    well i know 4gb is not neccessary but I want the entier index to fit in memory no? the database size is (exepected to be) around 4gb. most of this will be the text which is full-text indexed.
    it is still in development so I have no benchmarks. nor am I a DBA but a developer so i am not that familiar with database tunning. this is for an internet startup so unfrtunatelly i am as close to a dba as we are going to get! :)
  7. thomas New Member

    4GB of RAM is not a lot these days. Usually you don't need to fit all of your DB in RAM as it's not all in use all the time. But get the most RAM you can afford - it's relatively cheap, and the performance benefit of having data already in RAM is very significant.
  8. satya Moderator

    What is the edition of SQL Server you are using, I don't see any issues with Standard edition though.

Share This Page