SQL Server Performance

How many statistics is too many?

Discussion in 'Performance Tuning for DBAs' started by ms5150, Feb 17, 2008.

  1. ms5150 New Member

    Hi all, I'm maintaining applications that access a SQL 2000 database that badly needs some tuning. I ran the Database Engine Tuning Advisor to see what recommendations it could make. On the table that I suspected was causing a lot of performance issues, it had recommended that about 70 different sets of statistics be added to one table. It also recommended more indexes, but that's another topic. There are currently about 12 sets of statistics currently on that table. While I do think that this table does need more indexes and statistics, 70 seem to me like that's too many. I don't know if I am going to be given the time to experiment with analyzing the queries and seeing which statistics make the most sense, and applying them one at a time. If I'm not given the time, and forced to just go with whatever the Database Engine Tuning Advisor, is this going to have a negative impact in other areas?
  2. Flexdog New Member

    Statistics won't help much if there are way too many table-scans and cartesian join queries.
  3. ms5150 New Member

    There's no table scans or cartesian joins, but the queries do have a lot of joins and subqueries. They could be simplied with a little denormalization, but that's not an option at the moment. I would think statistics and more importantly some indexes would help in this case. I know the indexes will slow down the inserts and updates, but I don't know how significantly adding such a high number of statistics would affect overall performance.
  4. satya Moderator

    DO you see these statistics as WA_SYS... or so?
    If so they can be easily dropped without further thinking, also just simply try to test the execution of queries by adding thsoe relevant indexes to the tables on the development platform.
  5. ms5150 New Member

    No, these aren't statistics that are currently on the table. I exported the results of the Database Engine Tuning Advisor without adding anything yet to the table. The prefix assigned to these statistics are _dta_stat_. Nothing in the script for wa_sys.
    My next step is to, like you said satya, add the indexes and stats to the table on the dev server.
  6. satya Moderator

    They are created by DTA tool then and you can ignore as they will not be picked up unless the engine unable to find relevant index plan.

Share This Page