SQL Server Performance

Full Scans/Sec.

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Rlaubertsr, May 14, 2008.

  1. Rlaubertsr New Member

    I recently took a new position as the DBA for a web app company. Getting to understand the performance issues that are a major concern for the company I have started monitoring the servers. The DB server so far does seem to be a little bit overloaded and I am working on reducing the workload. One of the counter (Full Scan/Sec for SQL Server Access Methods) is running way above that it should be. I am recording averages of 150-200 Full Scans per second and Max has been over 650. At the same time the number of active temp tables which we use a lot of in the procedures begin used averages around 1200. Since none of the temp tables (create @tablename) use indexes I was wondering if the tablescans could be due to the heavy use of the temp tables. I have not been able to find this information any where in the books on line, Microsoft knowledge base or on 4-5 websites I have searched so far.
    I appreciate your inputs.
  2. MichaelB Member

    I would agree with your assumption. The tablescans could be related to the use of non indexed temp tables if they are used exnsively (which is not a good thing in itself). You can test this by checking the full scans/sec when running one of the queries that create the temp tables. Also, you can change some of that code to use derrived tables or table variables which should help out a lot or by adding correct indexes to the temp tables. Remember that table varaibles should not be used on large datasets. Indexed temp tables are better for that:)
    Mike
  3. satya Moderator

    See this blog post; http://sqlserver-qa.net/blogs/perftune/archive/2008/02/05/3182.aspx about how you can dig out the performance issues.
    One of the documentation refers:
    Defined as the number of unrestricted full scans. These can either be base table or full index scans.
    There is no good ratio that fits all situations. The more index you have, the less full scan you need, but your updates become slower. Finally, a high number here shows either poorly written stored procedures or bad indexing. Either way, you need to get to work identifying the source of the problem.

  4. Adriaan New Member

    And don't forget that you can add indexes on temp tables, but not on table variables (which can only have a PK).
  5. MichaelB Member

    But the key on a table variable can be clustered:) doesnt help much though...

Share This Page