September 23, 2011 at 11:34 am
Hi,
I have two tables with the same structure in different databases on same server. SQL Server 2008 EE sp1.
Following query results with index seek on both tables as expected, because there is a two-column index on the predicate: (index key columns: c2,c3)
SELECT c1, c2, c3, c4,c5
FROM db1.dbo.t1 WITH (nolock)
where c2 = '62FF10B4-5BE6-4B49-AD6C-B7F434651AFE'
and c3 = '1000000933618588'
But the following results always with an index scan for db2 no matter the union order is:
SELECT * FROM(
SELECT c1, c2, c3, c4,c5
FROM db1.dbo.t1 WITH (nolock)
UNION ALL
SELECT c1, c2, c3, c4,c5
FROM db2.dbo.t1 WITH (nolock)
) T1
where c2 = '62FF10B4-5BE6-4B49-AD6C-B7F434651AFE'
and c3 = '1000000933618588'
But a query only on db2 table yields a index seek which is much much faster..
Indexes are not fragmented. What is the reason behind choosing index scan if the table is unioned?
Any comments appreciated,
September 23, 2011 at 11:40 am
Post the exec plan please
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 23, 2011 at 11:50 am
Plans attached
September 23, 2011 at 11:56 am
There's an implicit conversion that's forcing the scan. You'll probably find that the data types of attributevalue is not the same in both tables. SQL has to convert to do the union, and then the filter can't seek.
p.s. WITH(nolock,readuncommitted) ???
Nolock means readuncommitted, so you're just saying it twice. Really happy with uncommitted (dirty) data here?
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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 25, 2011 at 9:43 am
This solved my issue! There was collation conversion between tables, I have standardized the collations. Now its very fast, thanks Gila. How did you see the conversion in sqlplan?
Nolocks were to bypass blockings because there are always inserts in day time to the tables, I removed the no locks and will watch the performance. In fact the query was someting like a report, no inserts with the results.
Do you advice to enable snapshot isolation, which seems a superior lock mechanism in OLTP sense? I can see no benefit in not enabling snapshot isolation..
Thanks.
September 25, 2011 at 11:04 am
sporoy (9/25/2011)
This solved my issue! There was collation conversion between tables, I have standardized the collations. Now its very fast, thanks Gila. How did you see the conversion in sqlplan?
The CONVERT_IMPLICIT that was part of the predicate of the index scan
Do you advice to enable snapshot isolation, which seems a superior lock mechanism in OLTP sense? I can see no benefit in not enabling snapshot isolation
Snapshot is far superior to read uncommitted. Snapshot gives the same data anomaly behaviour as serializable, read committed snapshot gives the same data anomaly behaviour as read committed, but read operations take no locks. It's great for cases where there's severe blocking that can't be resolved by tuning the queries or the indexes.
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 25, 2011 at 11:28 am
Thanks.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply