Full Scan per second

[SQLSister]

In fooling around with setting some performance alerts, I set the computer to warn me when the full cans per second reached a certain number. Not only did the alert fire right away, but is has fired every time I reenable it and the number is growing bigger. This seems to be a bad thing to me (it is up to 4.5 million full scans per second and only two users are on the system right now!)

I thought a full scan meant a full scan of a table rather than using an index, but I couldn't find the term actually defined in Book on line.

If anyone has any knowledge of what this term really means and whether I should be concerned about the fact that it is growing please let me know. And if it is a problem, where would be the best place to start looking for the solution.

Much thanks!

Judy

 

[mgriffith]

"While table scans are a fact of life, and sometimes faster than index seeks, generally it is better to have fewer table scans than more. To find out how many table scans your server is performing, use the SQL Server Access Methods Object: Full Scans/sec. Note that this counter is for an entire server, not just a single database. One thing you will notice with this counter is that there often appears to a pattern of scans occurring periodically. In many cases, these are table scans SQL Server is performing on a regular basis for internal use.

What you want to look for are the random table scans that represent your application. If you see what you consider to be an inordinate number of table scans, then break out the Profiler and Index Tuning Wizard to help you determine exactly what is causing them, and if adding any indexes can help reduce the table scans. Of course, SQL may just be doing its job well, and performing table scans instead of using indexes because it is just plain more efficient. [6.5, 7.0, 2000]"


--

http://www.sql-server-performance.com/performance_monitor_counters.asp

they have a good listing of performance monitor stuff for sql...check it out mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[fluteplr]

I agree with everything Mike said, just an additional note, any small table <2k in 6.5 or 7 and < 8k in 2000 is always going to be table scanned. This is due to the fact that the smallest chunk of data sql server will read is either 2k or 8k depending on the version. If it can read the entire table in one read why bother with indexes.

 

[SQLSister]

Thanks for the info. At least it iwhat I thought it was. But what ccould be causing it to keep getting higher and higher when no one is trying to access any tables right now?

 

[mgriffith]

Note that this counter is for an entire server, not just a single database mike griffith
----------------------------
mgriffith@lauren.com
mdg12@po.cwru.edu

 

[SQLSister]

No other databases are active on this server.