May 26, 2008 at 10:20 pm
Created non clustered index on this column (key) of 2 table a1, a2.. made a join with index column only .. and select non index column date .. but execution plan shows table scan ....
same query i choose index column only no other changes in join .. however it shows index scan .. how it is possible
---->>>>>TABLE SCAN
SELECT STG2.DT , STG1.DT
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([STG1].)=([STG2].),
RESIDUAL:([STG2].=[STG1].))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([STG1].))
| |--Table Scan(OBJECT:([QIW].[dbo].[A1] AS [STG1]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([STG2].))
|--Table Scan(OBJECT:([QIW].[dbo].[A2] AS [STG2]))
---->>>>> INDEX SCAN
SELECT STG1.KEY , STG2.KEY
FROM A1 STG1 (NOLOCK)
JOIN A2 STG2 (NOLOCK)
ON STG1.KEY = STG2.KEY
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join, HASH:([STG2].)=([STG1].),
RESIDUAL:([STG2].=[STG1].))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([STG2].))
| |--Index Scan(OBJECT:([QIW].[dbo].[A2].[NC_INDEX1_A2] AS
[STG2]))
|--Parallelism(Repartition Streams, PARTITION
COLUMNS:([STG1].))
|--Index Scan(OBJECT:([QIW].[dbo].[A1].[NC_Index1_A1] AS
[STG1]))
=================
some note :
=================
1 : server free space is 1 TB
2 : There is no concurrency this is only job running in DB.
3 : this table does not have any cluster index and constarint
4 : this both table has 1030 + 1030 columns .
5 : both record count are same .
6 : total record count is 4.5 lak and filtered to 4 lak
7 : most of place uses this small qry .
8 : only one place with the same where clause but select list conatains 350 columns this alone takes 3 hrs .
May 26, 2008 at 10:26 pm
Well, it does not appear that the "DT" column is in the index, and since it has to get both it and the join Key values, it's probably faster to table scan once than to index scan and then Bookmark Lookup to get all of the DT values.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2008 at 10:47 pm
DT is not a index column . can i force index ??? if so what is the syntax ??
May 26, 2008 at 11:04 pm
CREATE [UNIQUE] INDEX idxTablenameKeyDT
ON TableName ( Key, DT )
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 26, 2008 at 11:12 pm
i cannot use index on DT column .. if i try to create index , have to create in lot places ... in one place with this same join select list contains 350 columns .. also unique index also not advisable .. BCP done on this table and more deletion and updation happens ..
May 27, 2008 at 12:06 am
I take it you didn't like the replies to your previous, identical, post
http://www.sqlservercentral.com/Forums/Topic506239-65-1.aspx
My guess is that the index is not covering (which with 300+ columns in SQL 2000 it can't be) and it's not selective enough for SQL to use.
6 : total record count is 4.5 lak and filtered to 4 lak
Um, how many is a lak?
Since you're querying most of the table (89% of it), the scan will probably be faster than a seek on a non-covering index with the associated key lookups. Key lookups are expensive and the optimiser will only pick a plan that involves lookups if the number of rows affected is les than 1% of the table
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
May 27, 2008 at 12:28 am
if i use the option ( merge join ) .. one of the table goes index scan .. another one is table but cost is reduced from 24 to 8 % . but sort order is included newly as 20 % .
May 27, 2008 at 12:35 am
And if you run the hinted query along with the otiginal, how are the relative costs, how are the IOs (SET STATISTICS IO ON), how is the duration (SET STATISTICS TIME ON)?
You should stay away from any form of query hint unless you are 100% certain that you know better than the optimiser, you've carefully tested to ensure that there is a performance improvement and you know completely what the hint is doing.
My guess - the relative cost for the scan has dropped because the sort is not taking a significant portion of the query's cost. Remember the percentages you see in the exec plan are relative costs for the various operators in the plan.
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
May 27, 2008 at 1:45 am
ss the sort cost newly add to 25 %..
to run statistics on .. without giving the qry output can we take the statistics output ??.. like execution plan can be see without getting the result ..
May 27, 2008 at 1:59 am
No. Those are execution statistics.
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
May 27, 2008 at 2:51 am
Table 'A1'. Scan count 8, logical reads 160572, physical reads 0, read-ahead reads 128208.
Table 'A2'. Scan count 8, logical reads 305858, physical reads 3, read-ahead reads 320673.
i am not aware of what this result says .. if u explain that will be helpful for me .. to be the best how should the result look ??
May 27, 2008 at 3:18 am
It says that running the query require SQL to read almost half a million pages. That's an awful lot. The fewer IOs done by a query, the better
Is that with the hint, or without?
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
May 27, 2008 at 3:32 am
this result is without hint .. hint shown 25 % sort cost .. so i dropped ...
wud like to solve without hint ..
May 27, 2008 at 4:35 am
If you could give us the table definitions, the index definitions and the query, it would really help.
Also, copy the entire execution plan to excel (not just the 1st column that you posted), zip that and attach it to your post, it would be useful.
Without that, we're guessing.
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
May 27, 2008 at 5:07 am
INDEX :
=============
NC_INDEX1_A1 > nonclustered located on PRIMARY > KEY, QNBR, TIMESTAMP, BUS_CNTR
Table definition is difficult .. that has 1030 columns ... also i am sure there is no constraint , index .. only index is above mentioned only ...
also i have used the posted qry in more place .. that has only single column .. another place i have 350 column and in another it has 1000 .. so nothing to see in DDL...
posted showplan text is for one qry only .. if that is not showing the index scan .. definitely 350 and 1000 columns will not show .. first i need to solve the single and simple qry posted above ..
giving your requirement is not an issue .. but this simple one should be solve .. i am sure thr would be some solution .. but i am not aware ...
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply