August 4, 2008 at 12:56 pm
i want to know table scanning in sql2005
August 4, 2008 at 1:08 pm
A table scan in SQL Server means that the Query Processor has to read every row in a table in order to determine if the row should be returned by the query. These are typically resource intensive operations and should be avoided on all but small lookup type tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2008 at 1:51 pm
So I gotta ask, since performance tuning is not one of my strong suits, in fact, I don't even really have that suit at all, what are some ways to avoid table scans? From what I can gather in BOL on NONCLUSTERED indexes, that seems to be helpful. However, I have one view that joins a couple of tables, and takes around 18 seconds to return around 1 million rows. I added nonclustered indexes where I could, but speed of view did not change. I looked at the execution plan, and the big hog was an clustered index scan. At the bottom of the hover dialog box, under Argument, it lists OBJECT(MyDB.dbo.MyTable.pk_field). I'm confused. I thought PK's were nonclustered indexes. I also thought that when you created a PK, it automatically threw on a nonclustered index, but I was able to create one on the pk field, making me think it was not there to begin with. Could you possibly explain any of this in laymens terms?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 4, 2008 at 2:02 pm
Greg Snidow (8/4/2008)
I thought PK's were nonclustered indexes.
Ok, now I have found something in BOL that seems to imply PK's are clustered, at least that's what I think its telling me. Who the heck comes up with these names anyway? SO unique indexes are clustered?, and indexes on columns that are not unique are nonclustered? I'm almost to the point of giving up trying to better myself...since this isn't really my job anyway. Any insight would be greatly appreciated.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 4, 2008 at 2:13 pm
By default in SQL Server when you create a Primary Key it creates a CLUSTERED index. You can define the Primary Key to be non-clustered. Each table can have only 1 Clustered index and many non-clustered indexes.
Columns that are good candidates for clustered indexes are ones that are searched on by RANGE and/or are increasing, Identity columns, created date.
A clustered index scan is essentially a table scan as the clustered index is the data stored in order.
If you are getting a clustered index scan it is because either your search criteria are not indexed or you are returning a large subset of the rows so the QP has decided the scan is faster and/or it would take longer to determine the correct index than it does to do the scan.
Remove the non-clustered index you created on your PK as it is doing nothing for you.
If you post schema and query we may be able to help out with indexing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 4, 2008 at 2:23 pm
Thanks Jack. It'll be ugly, but I will work on table defs, sample data, and view tomorrow.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 5, 2008 at 12:25 pm
Hi Jack,
I think this is pretty brave advice to tell to replace clustered index on the table with nonclustered one. What about other queries, maybe it’s being used successfully somewhere else in the system? I'm not trying to be picky; I believe that with performance opinions it’s always good to be careful.
Greg: Another way of optimizing the response time for the view is creating indexed view, of course it has its drawback in performance penalty for maintaining index with the view's underlying tables’ data.
August 5, 2008 at 12:32 pm
Max Mulawa (8/5/2008)
Hi Jack,I think this is pretty brave advice to tell to replace clustered index on the table with nonclustered one. What about other queries, maybe it’s being used successfully somewhere else in the system? I'm not trying to be picky; I believe that with performance opinions it’s always good to be careful.
Greg: Another way of optimizing the response time for the view is creating indexed view, of course it has its drawback in performance penalty for maintaining index with the view's underlying tables’ data.
Max,
I don't see anywhere that I recommend replacing a clustered index with a non-clustered. The only specific advice I gave was to remove the NON-CLUSTERED index he had created on the CLUSTERED PRIMARY KEY.
Remove the non-clustered index you created on your PK as it is doing nothing for you.
Which is accurate as the non-clustered index he created includes the clustered index key.
Then I ask for more information in order to help with indexing suggestions.
If you post schema and query we may be able to help out with indexing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply