SQL Server Performance

Performance issues under load testing

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Swapnam, Mar 28, 2011.

  1. Swapnam New Member

    Hi All,
    Our web application uses SQL Server 2005 and above versions. Currently we are facing performance issues under load.
    We have a table with 4milliion records. The table has more than 100 columns. The end user can apply filter on any of those 100 columns. Hence we created non-clustered indexs for all the columns (Each column has 1 non-clustered index). And a clustered index (Primary Column) for ID column whihch will have alpha-numeric data.
    The query that will be generated at run-time will be as below:
    BEGIN
    With TEST as (
    select ROW_NUMBER() over( order by x, y asc) as RN, ROUND(AVG(a),5), ROUND(AVG(b),5)........ FROM Table1 Where z >= 3260 Group by x , y)
    select * from TEST where RN BETWEEN 1 AND 200 order by RN;
    END
    I cehcked the execution plan and found that Clustered index scan has 99% whihch uses only Primary key for search. Meaning the non-clustered index created on the column is not being utilized.
    When did a load test, out of 10 users, 3 got blank page and the rest of them had a result in less than 20secs.
    Can somebody let me know what changes can be done so that the above query gets executed successfully under load.
    Note: There could be more than 4m records in that table. The target is 34m. But this is failing even for 4m.
  2. mmarovic Active Member

    Hi Swapna.
    Please don't be offended, but the whole post shows the intention to play with database without any knowledge or experience in database design. Besides performance problem you described, you will have huge problem populating that table. So, I would recommend you to hire a database specialist who would help you with db design, index design and sql.
    I think there is no point in solving the specific query, when you have much more serious problem to solve.
    However, if you insist, for this query to be efficient enough, you need an index on columun z, x and y in that order. Also you need to add columns a, b, ... in the leaf level of that index (use include). I hope "..." that you are not listing avarages of all remaining 97 columns. Even in that case the query would be too slow just not as slow as now. I am not sure about execution plan, maybe index on z, with included x, y, a, b, etc... would give you the same performance.
    I also do not understand the query design. You want to display results of averages for columns x and y, but you do not actually display these column values, just row number that is calculated based on order of these values. How would one who looks at the query results understand what are averages displayed for?
    Kind regards,
    Mirko.
    [Edit] Question about query design added.
  3. satya Moderator

    Welcome to the forums.
    I believe you have an oversized indexes on the platform, having NC index on all the columns will have a downturn too.
    Is it not possible to create NC index on most active columns or rather you can take help of INCLUDED columns feature - http://msdn.microsoft.com/en-us/library/ms190806(v=sql.90).aspx that will reduce the size of index and offer performance too.

Share This Page