July 23, 2009 at 7:11 pm
I recently found a SQL2005 server with "Full Scan/sec" performance counter 10 times than before.
I was trying to find out specific sql statements which may potentially cause this, but got nothing meaningful.
Any suggestions?
July 24, 2009 at 5:10 am
If it really are a problem to be solved in your environment, you should try to trace it using the Performance and SQL Profiler to find out the queries that are causing it. Also, attention to the indexes of the table, if they are being used and placed in the correct place(columns).
But remember that full scan doesn't always means that you have problems.
You will find a lot of good articles in this site about indexes.
Regards.
July 24, 2009 at 6:44 am
Hi,
I'm currently doing a similare excerise to reduce the fullscans/sec on some legacy objects.
One thing that helped me was to profile the 'Scan Started' event and set a filter where the index id equals 0 this will give the object id's for all the objects which are 'heap' tables (you can then look these up in sys.objects), you can then apply the appropriate indexing onto these objects and reduce your fullscans.
Hope this helps.
July 24, 2009 at 4:48 pm
That's really a good way to track, but the events I caught were mostly from tempdb. I tried it on another server (server B), which had normal "Full scans/sec" number and the same structure as suspected server (server A), and got the same result (most events were from tempdb). Server B actually had more traffic than server A, but much less "Full scans/sec" number.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply