| |
 |
|
SQL Server Tips by Burleson |
Access Path Effectiveness
The second determinant of access efficiency is the route through
which the information must travel to be accessed. Index lookups
versus table scans must be taken into account to determine how well
code has been written or how well the DBMS optimizer chooses the
best path. Design considerations also come into play.
Small table scans should be overlooked, as it is often more
efficient for a database to cache and scan a small table than to use
any available indexing mechanisms. Another important factor that
contributes to access path effectiveness is the consistent
application of object demographics to the data dictionary.
Most DBAs have witnessed an access path, which worked well for
objects with a certain volume of data, begin to fail when the
demographics of those objects changed and the database’s optimizer
was not informed of the change.
The final huge factor is I/O consumption, which is comprised of
logical and physical consumption. While physical I/O may take longer
to accomplish a database request, a heavy load of logical I/O is not
conducive to quick response time either. Mitigating both should be a
goal of the performance model. Even if the database is servicing
requests well in memory, it might not matter if the code is not
accessing the data properly or utilizing the right techniques to get
only the data that is necessary.
This is the point at which some DBAs who rely only on singular
measures, such as the cache hit ratio, fail to properly measure
performance. Yes, a user thread may sport a 98% cache hit ratio, but
if that same thread used 500,000 logical reads to produce a result
that SQL could have achieved in 5,000 logical reads, wouldn’t
overall speed be increased with the use of SQL?
Measuring code efficiency is not simple since individual SQL
statements can be difficult to track and trace; however, there are a
few components that will indicate positive or negative trends in
this territory.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets
ISBN:
0-9761573-6-5
Robin Schumacher
http://www.rampant-books.com/book_2005_2_sql_server_dba.htm |
