SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Accessing data from the data pages
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/12/2013 :  08:49:51  Show Profile  Reply with Quote
quote:

And i dint get your statement sir. "Table scan would be for heap".


I originally didn't realize when the table scan would be used. So I discovered it is with a Heap so I was glad to learn that. For a table with a Clustered index, an Index scan is used instead. Although maybe they amount to nearly the same thing.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/12/2013 :  11:40:14  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:

And i dint get your statement sir. "Table scan would be for heap".


I originally didn't realize when the table scan would be used. So I discovered it is with a Heap so I was glad to learn that. For a table with a Clustered index, an Index scan is used instead. Although maybe they amount to nearly the same thing.



ok.
Thanks you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/12/2013 :  11:47:53  Show Profile  Reply with Quote
quote:

Good Point, I hadn't even noticed that.

I ran a test. It's not the be all and end all, so many factors could be at play.

I run the same query under 3 scenarios: Table Scan, Clustered Index Scan, Non-Clustered Index Scan. I ran each under both an empty cache and data-in-cache.

The table had a few million records. The table had 30 columns while the Non-Clustered Index had only 3 columns. Also, the index was covering so there was no bookmark lookup.

Here are my Results!:


Non-Clustered Index Scan Empty Cache		1,500ms
Clustered Index Scan Empty Cache	       16,500ms
Table Scan Empty Cache			       18,600ms

Non-Clustered Index Scan Data-In-Cache	          380ms
Clustered Index Scan Data-In-Cache		  570ms
Table Scan Data-In-Cache			  550ms


So the Index Scan was much faster than Table Scan when the cache was empty. It was also faster than the Table Scan when the data was in-cache, but not a huge amount.

I also found out that the Table Scan is only for the Heap.


quote:

Since it you are taking a covering non-clustered index, this non-clustered index is that efficient.

If you take a non-clustered(non-covering) index, then the decision of opting for a table or index scan depends on the number of rows that a query results right?



What do you say about this post sir?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/15/2013 :  10:11:27  Show Profile  Reply with Quote

quote:

If you take a non-clustered(non-covering) index, then the decision of opting for a table or index scan depends on the number of rows that a query results right?



Essentially. SQL Server will estimate the number of matches and based on that it will try to determine an efficient plan.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  05:58:14  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief


quote:

If you take a non-clustered(non-covering) index, then the decision of opting for a table or index scan depends on the number of rows that a query results right?



Essentially. SQL Server will estimate the number of matches and based on that it will try to determine an efficient plan.



Yes thanks for the post.



--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  05:58:56  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:

And i dint get your statement sir. "Table scan would be for heap".


I originally didn't realize when the table scan would be used. So I discovered it is with a Heap so I was glad to learn that. For a table with a Clustered index, an Index scan is used instead. Although maybe they amount to nearly the same thing.



even in case of having a non-clustered index, index scan can be opted right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Edited by - sgondesi on 12/16/2013 07:27:21
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/16/2013 :  06:01:09  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:
Originally posted by denis_the_thief

quote:

And i dint get your statement sir. "Table scan would be for heap".


I originally didn't realize when the table scan would be used. So I discovered it is with a Heap so I was glad to learn that. For a table with a Clustered index, an Index scan is used instead. Although maybe they amount to nearly the same thing.



even in case of having a non-clustered index, index scope can be opted right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


what do you mean by index scope? did you mean using explicit index hints?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  07:24:38  Show Profile  Reply with Quote
quote:


even in case of having a non-clustered index, index scope can be opted right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


what do you mean by index scope? did you mean using explicit index hints?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

No, sorry i miss typed it. it is index scan.



--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  07:28:29  Show Profile  Reply with Quote
And explicit index hints should not be trusted always right.
Because, those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.

If we create an index for every hint, we need to suffer with the index maintenance problems right. So i feel that, we should also consider the frequency at which a particular query is being executed per day and then if it looks beneficial then we should create indexes on the fields desired for that query.

Are my assumptions valid?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Edited by - sgondesi on 12/16/2013 07:32:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/16/2013 :  07:48:13  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

And explicit index hints should not be trusted always right.
Because, those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.

If we create an index for every hint, we need to suffer with the index maintenance problems right. So i feel that, we should also consider the frequency at which a particular query is being executed per day and then if it looks beneficial then we should create indexes on the fields desired for that query.

Are my assumptions valid?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


sorry i didnt get what you mean by below
those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.
FYI Index hints wont be displayed anywhere for you.
Index hints are what you specify in a query yourself to override the default plan created by optimizer and make it use an available index.

ex:

SELECT COlumn1, Column2 
FROM Table  WITH( INDEX (IndexName) )
WHERE conditions...





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  09:13:41  Show Profile  Reply with Quote
[/quote]
sorry i didnt get what you mean by below
those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.
FYI Index hints wont be displayed anywhere for you.
Index hints are what you specify in a query yourself to override the default plan created by optimizer and make it use an available index.

ex:

SELECT COlumn1, Column2 
FROM Table  WITH( INDEX (IndexName) )
WHERE conditions...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

[/quote]

Sorry not mentioned sir.

I took your post in a wrong sense. I thought about the hints which tells us about the missing indexes when a query is executed. I thought you are telling about this concept.



--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  09:19:24  Show Profile  Reply with Quote
quote:

sorry i didnt get what you mean by below
those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.
FYI Index hints wont be displayed anywhere for you.
Index hints are what you specify in a query yourself to override the default plan created by optimizer and make it use an available index.

ex:

SELECT COlumn1, Column2 
FROM Table  WITH( INDEX (IndexName) )
WHERE conditions...




Query Optimizer already has intelligence right, will the hints (which you are talking about) help optimizer in determining a less cost approach (to execute the query) faster?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/16/2013 :  10:29:15  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:

sorry i didnt get what you mean by below
those hints will be displayed for every table which does not have indexes on the desired fields of the query which is executed on a particular table.
FYI Index hints wont be displayed anywhere for you.
Index hints are what you specify in a query yourself to override the default plan created by optimizer and make it use an available index.

ex:

SELECT COlumn1, Column2 
FROM Table  WITH( INDEX (IndexName) )
WHERE conditions...




Query Optimizer already has intelligence right, will the hints (which you are talking about) help optimizer in determining a less cost approach (to execute the query) faster?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


Most cases it will have intelligence. Quite a very few occasions I've seen query with improved performance when we used index hints. But it takes a lot of trial and error approach and may not be taken for granted always IMO.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/16/2013 :  12:35:35  Show Profile  Reply with Quote
quote:

Most cases it will have intelligence. Quite a very few occasions I've seen query with improved performance when we used index hints. But it takes a lot of trial and error approach and may not be taken for granted always IMO.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




IMO means?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/17/2013 :  10:27:07  Show Profile  Reply with Quote
IMO - in my opinion

The hints don't help the optimizer, they override the optimizer.

I've seen very few cases where the hints in indexes improve performance. But at least it gives you that control, if you want it. The term "hint" in this case is misleading as it forces sql server to use the index.

SQL Server's inteligence is based on the stats tables, have you looked at this yet?
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/19/2013 :  06:58:26  Show Profile  Reply with Quote
yes sir.
I have learned few things about statistics.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/22/2013 :  04:53:06  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

IMO - in my opinion

The hints don't help the optimizer, they override the optimizer.

I've seen very few cases where the hints in indexes improve performance. But at least it gives you that control, if you want it. The term "hint" in this case is misleading as it forces sql server to use the index.

SQL Server's intelligence is based on the stats tables, have you looked at this yet?



You meant stat tables, which store the details like (The number of rows that will be returned upon executing a query on a particular table) right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
594 Posts

Posted - 12/23/2013 :  09:16:48  Show Profile  Reply with Quote
It stores details to help SQL Server estimate the number of rows or matches for an index.

Here is a good link:

https://www.simple-talk.com/sql/learn-sql-server/statistics-in-sql-server/
Go to Top of Page

sgondesi
Posting Yak Master

India
200 Posts

Posted - 12/26/2013 :  04:19:20  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

It stores details to help SQL Server estimate the number of rows or matches for an index.

Here is a good link:

https://www.simple-talk.com/sql/learn-sql-server/statistics-in-sql-server/




Ya that's i mean.
I failed to tell my idea exactly.
I have learned basics of statistics from this page only.
Thank you for the post.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000