SQL Server Execution Plans

Question

SQL Server Execution Plans

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Laurence Neville SSC Eights! Points: 889 More actions · Answer 1

Hi Grant,

One question about the book:

You refer a couple of times to indexed views that "only refresh once per day". My understanding was that indexed views are updated instantly when base table data is updated, and I can only find references to Oracle materialised views having this "refresh" function. Is there something I am missing?

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Laurence Neville (11/11/2011)
Hi Grant,
One question about the book:
You refer a couple of times to indexed views that "only refresh once per day". My understanding was that indexed views are updated instantly when base table data is updated, and I can only find references to Oracle materialised views having this "refresh" function. Is there something I am missing?
Thanks

It's probably something like a temp reporting table loaded from a view.

Your understanding of the indexed views is correct.

s.pig.snow SSC Rookie Points: 27 More actions · Answer 3

Grant, I confused for a question when I'm reading <Dissecting SQL Server

Execution Plans>:

Following two T-SQL generate two different plans, but I don't know why:

--Clustered index scan

SELECT ContactID,LastName,EmailAddress FROM person.Contact WHERE EmailAddress LIKE 'sa%'

--NonClustered index seek and Key Lookup

SELECT ContactID,LastName,EmailAddress FROM person.Contact WHERE EmailAddress LIKE 'sab%'

Would you give me some suggestion please?

Thanks a lot.

s.pig.snow@gmail.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Sab must return less rows.

An index seek + bookmark lookup is a very costly operation. It takes very little data returned to make a table scan more efficient than a seek + lookup.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

This: http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

GilaMonster (11/14/2011)
This: http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/

And make sure to read this excellent comment at the end!

http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Tipping-Point-Query-Answers.aspx

:hehe:

Grant Fritchey SSC Guru Points: 398979 More actions · Answer 7

Sounds like you guys covered it all better than I would have. Thanks!

"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

SQL Server Execution Plans

Cookies on SQLServerCentral