SQL Server Performance

Big database - big problem

Discussion in 'Performance Tuning for DBAs' started by Roberto, Feb 23, 2009.

  1. Roberto New Member

    Hello all!
    This is my first post here, so i hope this is the right place for it :)
    I designed a database, where about 1 million rows per month are added.
    The problem is, there are many inserts all the time(about 100 per minute), and i need to make some big selects on it too.
    Ex. SELECT COUNT(1), Col_1 FROM Table_1 GROUP BY ....
    This would return about 20 records with a count of 100.000
    To run inserts and selects at the same time gets horrible.
    I run this on a xeon + 4gb ram system.
    I think the indexes are ok (mostly 90% fillfactor cause inserts works with less problems then selects)
    Thanks very much if someone has an idea!

  2. MohammedU New Member

    If you don't worry about the data, you can use NOLOCK or ISOLATION level READ UNCOMMITTED to speed up the process to avoid blocking...
    If you move to SQL 2005 OR 2008 you can use table partinitioning to take the avantage....
    It is advisable to change the fillfactor from 90 to 75/80 and make sure you have the index on group by if you are using it...
  3. Roberto New Member

    Hi and thanks for the fast answer!
    I use nolock at the moment, this improves performance.
    Do you think moving to sql 2008 has "big" performance advantages?
    But the concept is right to use the same database for insert and selects on this big rowcounts?
    I mean, is it possible to do that performantly?

  4. FrankKalis Moderator

    How exactly do you define "horrible"?
    100 INSERTs per minute really isn't that much. How are you inserting into that table? What is your table look like?
    Having just one database for OLTP and reporting can work out quite well im some cases, while in others it is a mess.
  5. Roberto New Member

    well i mean that if i start a select statement that needs about 2 seconds ( Like SELECT COUNT( DISTINCT myColumn ) FROM TABLE WITH(NOLOCK)... )
    the inserts are slowed down.
    All the inserts are made trough stored procedures.
    I have one master(5 ID Columns) and 5 slave tables.
    First i check in the tables (About 1 million of rows) if the records which i want to insert already exists. (Field to check is a NVARCHAR 255)
    If the records is there, i get the ID and write it in the main table else i insert the new record and return the ID.
    That works fine (i testet it with mass insert).
    I don't care of the data on selects if there is one row more or not, so nolock is no problem.
    How i said before, if a select is going on, the inserts need up to 1 second the get completet.
    The inserts are more important(must always work) then the big selects.
    Thanks for help!

Share This Page