SQL Server Performance

SQL Server VLDB?

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by kmedlin, May 25, 2010.

  1. kmedlin New Member

    Do I have a "VLDB"? I know the standard answer is "it depends". I currently have 2 Dell servers connected to a Dell SAN in a clustered active/passive hosting SQL Server 2008 databases. There is one table in one of our 17 databases that is causing problems. The table has 476,464,429 records and is 18,364,896 KB in size. The database is 27,959,632 KB in size. I have been experiencing deadlocks and real long queries when inserting, updating, or even joining on this table. Is it time to start patitioning the table? We will be upgrading hardware but I don't think that is the ultimate solution.

    the table consists of 6 columns. indexes are correct and stay under 30% fragementation (using idera defrag manager). i dont have a good column to partition the table on. could the calculated column cause the problem?

    columns:
    Column1 int,
    Column2 int,
    Column3 varchar(10),
    Column4 numeric(10,4), -- calculated column
    Column5 int,
    Column6 int
  2. ghemant Moderator

  3. moh_hassan20 New Member

    Set the computed field as Persisted computed column , enhhance performance , but get more space
    Set large tables on separete group file
  4. satya Moderator

    Rather than focus on a fixed size i would look at how the application is performing now and does it warrant the additional complexity. Then pick those techniques that fix the problems you experience.
  5. FrankKalis Moderator

    [quote user="satya"]
    Rather than focus on a fixed size i would look at how the application is performing now and does it warrant the additional complexity. Then pick those techniques that fix the problems you experience.

    [/quote]
    True.
    Don't preoptimize things when users are happy with the way it is. [:)]
  6. judepieries New Member

    Were you able to sort this out? Some thoughts

    - Try removing any unions and union all's (Insert data into temp tables and later construct the final select statement )
    - Avoid having more than 2-3 joins on this table
    This help for the table in concern and other table that's joining this table from being locked for a lengthy period of time
    - If this is a transaction table , have a replicated copy of this table where all the selects could be performed
    - Try and avoid having large lookups on this table. By having granular request on this table you allow the table to be available for many more request

    Some questions
    - Have you got a Clustered index on the table
    - Have you placed NNI's on the FK columns
    - Are there searches done on this table other than on the FK columns
    o If there are searches on the varchar(10) column have tried creating a index and place appropriate covering

  7. satya Moderator

    In my experience I have seen that 10GB database is also considered as VLDB for the clients, but the actual fact/meaning behind that term is how quickly you may get the database back in case of any issues & number of transactions that are demanding the scalability aspects for your application.
    ... its always Chicken & egg situation here [:)].

Share This Page