Great Ideas. Always Flowing.

We are not happy until you are happy. Client satisfaction guaranteed. Whatever your needs and requirements, we have the skills and resources for the job!

Recently Viewed...

To help you navigate to pages you most recently visited, select from the links below.    
SharePoint Search Results Customizations
Joomla VS DotNetNuke

SnowCovered Top Sellers

Frustrated over the lack of customization for your user's registration fields? Dynamically setup your DNN Portal with custom registration fields, layout, questions, and other core integration options......

Ultra Video Gallery is a brother product of Ultra Media Gallery, UVG allows you to upload videos in various format and automatically encode them to flv or H264 format, you also can add videos from internet or record live videos from your webcam.

Build high performance, completely customizable data-entry forms and views driven by your DNN and external databases. New built-in tools make it a snap to quickly create data entry forms, data views, and even database tables. Plus, add your own HTML, CSS, Javascript, SQL commands, stored procedures,

The most advanced DotNetNuke shopping cart on the planet. Easy to use e-Commerce, Secure Shopping Cart Software and SEO friendly. B2C / B2B Ecommerce Sites.

One stop solution for events calendar and events registration! FREE DOWNLOAD is available now!

Popular Tags

Tags

SQL Server Full Table Scans - Key SQL Performance Situations to Watch Out For

Key SQL Performance Situations to Watch Out For - Part Three of Three

 

View Part One - SQL Server Blocking

 View Part Two - SQL Server Expressions

 

Have you ever opened up a really big book lately?  Let’s say someone handed you a copy of War and Peace by Tolstoy and told you that there was an interesting phrase found on page 435.  How would you go about looking up that page?  Would you start at the beginning of the book, turning page after page, one at a time looking for page 435?  Would you start at the back of the book and do the same?  The most common answer to that is “Who’s Tolstoy?” but following at a close second place is “No way?  What are you… stupid or something?  Why would anyone do that?”  The most common way people would go about finding that page number would be to start in the middle.

 

Sure.  You’d start in the middle, and then you’d divide the search of the book in half estimating the approximate location of the page.  Then comparing the page number you’re looking at to the desired one, you’d know whether to look in the right half of the book or the left half.  You’d repeat this process with tighter and tighter division and with smaller and smaller number of pages until you find the page you are looking for.

 

Why do we do it this way?   We search books this way, simply because our human experience has taught us that this is the most efficient way to find pages we are looking for.  So what does this have to do with query performance? 

 

When figuring out how best to access data within SQL Server, the query optimizer takes the query that you submit to it and performs some analysis of the objects involved.  It tries to determine what indexes (just like the numbering of pages in a book) are available and if they are useful or not to your query, it tries to determine if the criteria in the where clause of the query allows it to use any of the indexes in an efficient manner.  When there is no other efficient way to access data within the tables involved in the query, the query processor will process all of the rows in the table one at a time to give you the results.

 

This is referred to as a Full Table Scan.  This is the equivalent of having a book that doesn’t have any page numbers, but being told that somewhere in the book is some piece of information that you need to know.  So, the only way to access this information is to look at every page one at a time searching for the information you need.  Sound like fun?  In a 10 page Children’s book, this might be fine, but in War and Peace by Tolstoy, not so much.

 

Having an index on a table tied to columns that are searchable (Names of things, IDs, Foreign Keys, etc.) allows the query optimizer more options when a query is being processed.  It can look at the Where clause and joins of the query to determine which indexes would best access the data and then perform searches on the data very similarly to how we would access a particular page within a book, which can dramatically improve the performance of accessing the data.

 

To show how this looks within a query plan, the following 3 illustrations of query plans show 3 different types of scans: Index Scans, Clustered Index Scans, and Table Scans.

 

SQL Server Full Table Scans

 

Avoid SQL server Full Table Scans

 

 

Full Table Scans also are not scalable as the data grows.  It’s very simple.  As more data is added to a table, full table scans must process more data to complete and therefore they will take longer.  Also, they will produce more Disk and Memory requests, further putting strain on your equipment. 

Consider a 1,000,000 row table that a full table scan is performed on.  SQL Server reads data in the form of an 8K data page.  Although the amount of data stored within each page can vary, let’s assume that on average 50 rows of data fit in each of these 8K pages for our example.  In order to perform a full scan of the data to read every row, 20,000 disk reads (1,000,000 rows / 50 rows per page).  That would equate to 156MB of data that has to be processed, just for this one query.  Unless you have a really super fast disk subsystem, it might take it a while to retrieve all of that data and process it.  Now then, let’s say assume that this table doubles in size each year.  Next year, the same query must read 312MB of data just to complete. 

 

If a unique index is used instead to find the row needed, a rule of thumb is to assume that about 1 log2(N) disk reads at most need to be performed to find the data.  In the formula, N = Number of rows to process, and log2 means logarithm for base 2 or binary logarithm.  So, let’s assume that we still have our 1,000,000 rows in our table.  Using our rule of thumb formula (which isn’t necessarily accurate but will give you an idea of how much more efficient these are) we find that 1 log2(1,000,000 rows) = 7  disk reads.  So, to find the one row you need in this scenario, only 7 disk reads need to be performed, which is much different than 20,000.

 

Assuming our 8K pages and assuming it has to read all 7 different pages, we find that running this query yields only 56K of data that needs to be processed.  Looking at our projected growth in a year, where the table now has 2,000,000 rows of data in it, we can apply our same rule of thumb algorithm and we find that 1 log2(2,000,000 rows) = 7 disk reads.  So, doubling the rows in this case doesn’t impact how many reads we have to do to find the data!  Let’s look at 10,000,000 rows.  Using our same algorithm, we find that 1 log2(10,000,000 rows) = 8 disk reads.  In our example, processing this much data by using a scan would yield 200,000 disk reads instead of 8.  So, we see that modifying our queries to use indexes for large, ever growing datasets, is far more scalable and efficient as the data grows than allowing full table scans.

 

There have been cases where applications perform fine under small load, but will absolutely crash a system when even mild increase in load is put upon them.  If a SQL Server doesn’t appear to scale well when the load increases, chances are you need to analyze the running queries to determine if they are scanning rather than performing seeks.

 

View previous article on Performance Situations to Watch Out for  - SQL Server Blocking.

View previous article on Performance Situations to Watch Out for  - SQL  Server Expressions.

 

 

 

Feedback Comments

Feedback

SharePoint Web Parts


All Data Springs Web Parts Support WSS 3.0, SharePoint 2007, and SharePoint 2010 Frameworks

Please select license option for each web part you wish to purchase. We highly recommend the SharePoint Bundle to get all Data Springs Web Parts for maximum value!

 

 

      
Cart


Data Springs Sharepoint Bundle

Best Value! The Bundle gives you all 5 web parts in one package at a very attractive price! Best Value! We think you will be very happy with the SharePoint bundle and great price discounts you will receive. With your purchase all of the web parts below will be included.
 
 
 
 

Random Image Web Part

With Random Image for Sharepoint 2007, you can select multiple images to display randomly when the web part loads...
 
 
 
 

Stock Quote Web Part

Giving your site visitors relevant information is critical. With the Data Springs Stock Web Part you can provide your users with up to date financial information
 
 
 
 

Dynamic Image Rotator Web Part

Who would have thought? Adobe Flash® with Sharepoint! The FIRST and ONLY image rotation web part for Sharepoint using Flash Technology from Adobe! The Dynamic Image Rotator displays selected images and then rotates between the images. Several extended and optional features allow you to select the time to rotate each image, fade between
 
 
 
 

SharePoint Charts Web Part

The MOSS Chart Web Part is a web part built by Data Springs for the purpose of rendering several chart types based on data from a SharePoint list on a MOSS 2007 or WSS 3.0 Site
 
 
 
 

Dynamic News Ticker Web Part

Provide current news items with a user-friendly news ticker for your Sharepoint Portal. With millions of web sites offering information you need a fun way to display information and the solution is Flash News Ticker....
 
 
 
 

Tailored Text Web Part

 Tailored Text Web Part allows you to add text/html to your web site that can be different for anonymous users, registered users,  and even individual users specifically.

 
 
 
 

Dynamic Views Web Part

Dynamic Views is an excellent tool to:
Personalization allows you to go the extra mile in communicating or connecting one to one with your clients. When it comes to technology and web site content, you now have the power to leverage this personalization directly with your users on your DotNetNuke® site

 
 
 
 

Dynamic Login Web Part

Your site content isn't vanilla, so why is your portal's login?

Add pizazz and functionality with Dynamic Login! Use custom templates, localization, redirection rules for various roles and many more features!
 
 
 
 


 
 

Join our mailing list...

Get current news and events the easy way
 
 
   
Subscribe Me

Recent Blogs...

 
Copyright 2005 - 2011 by Data Springs, Inc.