September 19, 2010 at 11:28 pm
Hi,
I have a stored procedure where the performance is bad. The Execution plan shows 5 table scans each of cost 12%. The data is being pulled from the table using the following where clause.
where (datestamp>getdate() - 'sep28 1902 12:00 AM')
I already have an index on Datestamp column but still Table scan is happening. There is no restriction on avoiding index is specified in the query.
If the index is place inappropriately there would be index scan but here I have table scan.
Could anyone help me in finding out why this is happening.
-Thank you,
Jaya
September 19, 2010 at 11:48 pm
The table scan (rather than index seek) is probably because the index is not covering and the query returns too large a percentage of the table for the combination of seek and key lookups to be efficient. http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 12:07 am
Hi Gail,
Thanks for your response. It is true that my select statement has more columns, may be that is the reason why table scan is going on. Do you suggest Covered index on all the following columns?
Here is my select query
select (ID,Datestamp, par1,par2,par3,par4,ind1,ind2,ind3,block1)
where Datestamp >getdate() - 'sep28 1902 12:00 AM'
The table has following indexes defined
ID --- Non clustered
Datestamp -- Non clustered
block1 --- Non clustered
Datestamp,Ind1 -- Non clustered
Ind2,Ind3 --- Non clustered
RecordID -- Primary key
Unfortunately I cannot provide Execution plan as I am using 2000 and there is no save option.
Thank you,
Jaya
September 20, 2010 at 2:04 am
Without seeing the table def, I can't say whether that's a good idea or not.
Please read the article I referenced, it describes how to save the execution plan on SQL 2000 as well as on 2005+. I would really like to see it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 4:53 am
lallu_jaya (9/20/2010)
Do you suggest Covered index on all the following columns?
Do we have covering index in sql 2000 ? :unsure:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 20, 2010 at 8:54 am
Bhuvnesh (9/20/2010)
Do we have covering index in sql 2000 ? :unsure:
Covering index is not a feature that's available in some versions and not in others. It just means that the index has all the columns that the query requires.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2010 at 9:03 am
GilaMonster (9/20/2010)
Bhuvnesh (9/20/2010)
Do we have covering index in sql 2000 ? :unsure:Covering index is not a feature that's available in some versions and not in others. It just means that the index has all the columns that the query requires.
Congrats on the 20K points Gail... We won't see many people to reach that level, ever :hehe:.
September 20, 2010 at 9:33 am
Bhuvnesh (9/20/2010)
lallu_jaya (9/20/2010)
Do you suggest Covered index on all the following columns?Do we have covering index in sql 2000 ? :unsure:
Yes, by adding all the columns in the key definition of the index you can make it a covering index in 2000. In this case, that would be all the columns in the table, so this might not be the right spot for it.
If the index is on the date column, I would have expected a key lookup (bookmark in 2000) operation, not a table scan, if the index is selective enough. I'd look there to see if the data contained in that date column is reasonably unique (say, 99% or better, unique values). That would explain why the index wasn't referenced at all.
"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
September 20, 2010 at 9:35 am
where Datestamp >getdate() - 'sep28 1902 12:00 AM'
I highly doubt that this will ever do anything but a scan untill that value is changed to something much more recent.
September 20, 2010 at 11:56 am
Virtually everyone is STUNNED when I tell them about how few rows it takes (1-2% is common) before the SQL Server relational engine will switch from index seek/bookmark lookup plan to a table scan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 20, 2010 at 12:00 pm
TheSQLGuru (9/20/2010)
Virtually everyone is STUNNED when I tell them about how few rows it takes (1-2% is common) before the SQL Server relational engine will switch from index seek/bookmark lookup plan to a table scan.
I was under the impression that even 0.3% could be the cutoff, so 1-2% is really generous!!
September 20, 2010 at 1:11 pm
Ninja's_RGR'us (9/20/2010)
TheSQLGuru (9/20/2010)
Virtually everyone is STUNNED when I tell them about how few rows it takes (1-2% is common) before the SQL Server relational engine will switch from index seek/bookmark lookup plan to a table scan.I was under the impression that even 0.3% could be the cutoff, so 1-2% is really generous!!
I think afte 1% you're working on luck. Below 1% is what I shoot for and it usually works. But you're right, for a guaranteed result, you need to go below .3%
"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
September 21, 2010 at 12:57 am
I could convert all my table scans into Index seek by including the columns the query returns.
I still have one question:
Though the recent data in my table is from 2005, For some reason In my execution plan under argument section it shows up as Index seek where datestamp>getdate()-'sep 28 1902 12:00 AM Ordered forward.
Is there any reason?
September 21, 2010 at 1:05 am
lallu_jaya (9/21/2010)
Though the recent data in my table is from 2005, For some reason In my execution plan under argument section it shows up as Index seek where datestamp>getdate()-'sep 28 1902 12:00 AM Ordered forward.Is there any reason?
it depicts that optimizer has chosen that index because it is hitting the required data directly whenever optimizer go for seek it means thats the best use of related index.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 21, 2010 at 1:10 am
I am not sure why this particular date is showing up in Aruguments getdate()-'sep 28 1902 12:00 AM.
I havent included this date in any of my where clauses.
My question is where did this date come from?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply