Performance Issue: Full Scans / Sec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance Issue: Full Scans / Sec

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-12-04 : 02:40:14

Hi Team,

I have a very odd value for Full Scans / Sec. It is Maximum of 3075 and Average of 2438. This value is obtained by conducting a load test using Visual Studio for 4 hours.

One observation is that at the end of the test when I run the following query, it returns no rows.
SELECT * FROM sys.dm_exec_query_stats

But at certain point of time it had returnded values.

Is the high value of Full Scans / Sec due to the unavailablity of statistics ? What tests will confirm this? How do I overcome it?

Please advise....

Note: I understand that Full Scans occurs due to unavailablity of index or unuse of index by engine.

Other counter values
% Processor Time = 7.5
Batch Requests / Sec = 132
Lock Waits / Sec = 5.3
Page Splits /Sec = 4.5
Compliations / Sec = 38.8
ReCompliations / Sec = 0.55
Work tables created / Sec = 34.3
Work Files Created / Sec = 132
Logout/ Sec = 0.11

Also, the following query returned 707 rows
SELECT name
FROM sysindexes
WHERE (name LIKE '%_WA_Sys%')

Thanks
Lijo

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-04 : 03:45:26

That counter by itself is not of much use. You should compare it with index searches/sec. If the ratio of full scans to index searches is less than 1 to 1000 then you are either under indexed, or you have a lot of small tables or queries where a scan is the most efficient way to access the data.

-Chad

Sachin.Nand

2937 Posts

Posted - 2010-12-04 : 04:10:09

Just by looking at the test results without understanding the underlying query and table schema would be like shooting in the dark.

PBUH

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-12-07 : 05:54:18

Thank you, Chad, for being kind to put some lights rather than giving discouraging comments.

When I re-ran the load test, following is the values. What is the implication of these values ?

((Full Scans / Sec) / ( Index Searches / Sec) ) = 0.021
Full Scans / Sec = 3009
Index Searches / Sec = 138486

That is 21 Full Scans in 1000 index searches, right?
( 1 Full Scan in 47 Index Searches)

The client demands that the Full Scans / Sec should be less than 1.

Can you please send me msdn or other article links that justifies that ((Full Scans / Sec) / ( Index Searches / Sec) ) = 0.021 is an acceptable value ?

Thanks
Lijo Cheeran Joseph

Kristen
Test

22859 Posts

Posted - 2010-12-07 : 07:31:10

"The client demands that the Full Scans / Sec should be less than 1"

If the application has "Find" forms that allow users to enter lots of criteria, including leaving any/many blank, then this request is totally unrealistic.

Its easily solved, I expect, by putting an index on every single column. Deliver the application in that state, so it matches the specification - but the database size will be bloated and the Insert/Update performance will be sub-optimal.

A blanket "Full Scans / Sec" requirement doesn't make much sense to me (but I don't know what your application is about, so maybe there are mitigating circumstances). "Response time for Task-A should be < 1 second" seems like a much more sensible metric to me. Up to you then how you achieve that.

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-08 : 01:32:27

I know because that is the guidance we give in SQLRAP (Risk Assessment Program), but you can confirm (As well as many other perfmon counters) here:

http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

-Chad

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-12-08 : 01:58:56

Thanks Kristern,
We have forms having lots of optional entries. We have lots of small lokkup tables also.

Thanks Chad,
What do you think about 21 Tables Scan out of 1000 Index Searches?

Anyway I will be targetting for 1 out of 1000.

chadmat
The Chadinator

1974 Posts

Posted - 2010-12-08 : 02:52:59

quote:
Thanks Chad,
What do you think about 21 Tables Scan out of 1000 Index Searches?

I think it doesn't sound very good, although a lot of small lookup tables may inflate your full scan number. Where did client come up with their requirement for <1 Fullscan/sec? Methinks they don't quite uderstand what they are asking for.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources