SQL Server Performance issue – high value for Full Scans/sec? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server Performance issue – high value for Full Scans/sec?

One of the intersting post in forums and replies to them:
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.
MichaelB replied that: 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.
Further I have added to Michael’s response as;
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. 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. More to come….

Thanks for thinking of me! LOL

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |