March 6, 2008 at 9:27 am
What are the differences between a seek and scan index? why and when do we use them? When exactly to use the UPDATE_STATISTIC command and when do you think that is the best to run an index tuning?
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 6, 2008 at 10:38 am
A seek is when a particular value (or values) is pulled out of an index. Think of it like looking up a particular index card (OK, people may not know what that means any more, uh, how about using the index in a book to find a particular mention of a name). A scan is when the data is perused to find the value (or values) needed. Think of that like reading through a chapter, or maybe even the whole book, to find a value. Obviously, in most cases, but not all, the seek will be preferable to the scan. Usually, a seek is an indication of a properly written query against a properly configured index, but again, there are exceptions in every direction.
Updating statistics really depends on the system in question and the volatility of the data. I've seen systems where they were never updated and it was never noticed, say a reporting system that was reloaded each night, but had no inserts or deletes during the day, to systems that are updated weekly, daily, every two days, because the data changed enough that the automatic statistics just weren't cutting it any more, say an average OLTP system. I've also seen instances where we were updating statistics every hour, on the hour, say, a system that was completely hosed and had inappropriate indexes (my case) or a system with very high volatility. Determining exactly how often and when statistics are updated is a question for your system. Do you see query performance degrading over time? One possible cause of this is out of date statistics.
When to run index tuning? Do you mean index defragmentation? We run it nightly, but put parameters around it so that only indexes that are fragmented get rebuilt. There are scripts available in the link on the left.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2008 at 11:03 am
This is a great chunk of answer. You answered almost all of my questions neatly. Thanks! (Yeah, I know, nobody uses an index card nowadays, do they?)
Well, then, how do you run the defragmentation of index then? and how would you go about update certain ones only. I am very interested in knowing this.........Upgrading myself to a DBA.
Also, can you tell me what is a DBA's day to day job? Like what are the things DBA is suppose to know and do?(Other than open a browser and surf......:D)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 6, 2008 at 11:24 am
Right now I'm trying to uninstall 2008 from a virtual machine.
For a good overview, read the book "Database Administration" by Craig Mullins. Best summary of the job I've ever seen.
Do a search for the index defrag scripts. The better ones will check the fragmentation of the index first and then, depending on that fragementation level, do nothing, defrag or rebuild the index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2008 at 3:21 pm
Hi,
In Nutshell Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.
Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.
Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.
Thanks -- Vj
March 11, 2008 at 4:23 am
It's best explained in this article:
http://www.sqlservercentral.com/articles/Administering/executionplans/1345/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply