Inside the Optimiser: Constructing a Plan – Part 4
Updated: 2010-07-31 08:41:57
: : THE SQL Server Blog Spot on the Web Welcome to SQLblog.com The SQL Server blog spot on the web Sign in Join Help in Page Free Space : Paul White Entire Site Search Home Blogs Forums Downloads Opml Page Free Space : Paul White A SQL Server blog from the Kāpiti Coast , New Zealand Inside the Optimiser : Constructing a Plan Part 4 Summary : More undocumented ways to explore how the query optimiser . works Documented Rule-Affecting Options It might surprise you to learn that most T-SQL users have written queries that disable one or more of the optimiser rules . already Join hints LOOP HASH MERGE REMOTE and the query hints shown below are all implemented by disabling one or more optimiser : rules For example , a logical GROUP BY operation including DISTINCT can be physically implemented as
This month was very interesting month for me. I visited 2 different countries – Malaysia and Sri Lanka. I had great time attending 3 community sessions – Chennai, Kuala Lumpur and Ahmedabad. Though, I was at home only 5 nights, I was fortunate enough to spend good amount of the time with family as well. [...]
Today is my birthday. For those who are curious, I have now completed 31 years in my life. I have learned lots of new things during the course of the past few years. Every year I feel that I am getting better as a human being. Since today is my birthday, I have decided NOT [...]
: THE SQL Server Blog Spot on the Web Welcome to SQLblog.com The SQL Server blog spot on the web Sign in Join Help in Page Free Space Paul White Entire Site Search Home Blogs Forums Downloads Opml Page Free Space Paul White A SQL Server blog from the Kāpiti Coast , New Zealand Inside the Optimiser : Constructing a Plan II Continuing the series of posts looking at how the optimiser matches and applies internal rules to refine a query . plan The last entry ended with this query : plan The optimiser has pushed the predicate ProductNumber LIKE T down from a Filter iterator to the Index Scan on the product table , but it remains as a residual predicate . 160 We need to enable a new transformation rule SelResToFilter to allow the optimiser to rewrite the LIKE as an index : seek Notice that the
: THE SQL Server Blog Spot on the Web Welcome to SQLblog.com The SQL Server blog spot on the web Sign in Join Help in Page Free Space Paul White Entire Site Search Home Blogs Forums Downloads Opml Page Free Space Paul White A SQL Server blog from the Kāpiti Coast , New Zealand Inside the Optimiser : Constructing a Plan For today’s entry , I thought we might take a look at how the optimiser builds an executable plan using rules . 160 To illustrate the process performed by the optimiser , we’ll configure it to produce incrementally better plans by progressively applying the necessary . rules Here’s a simple query using the AdventureWorks sample database that shows the total number of items in the warehouse for each of a small number of : products : 1 SELECT P.ProductNumber , : 2 P.ProductID 
THE SQL Server Blog Spot on the Web Welcome to SQLblog.com The SQL Server blog spot on the web Sign in Join Help in Page Free Space Paul White Entire Site Search Home Blogs Forums Downloads Opml Page Free Space Paul White A SQL Server blog from the Kāpiti Coast , New Zealand Cool Query Plan Transformation of the Day In my last post I showed how SQL Server 2005 and later can use a Segment Spool to efficiently implement aggregate window functions and the NTILE ranking . function The query optimiser is also smart enough to recognise that some queries are logically equivalent to a window function , even if they are written using different syntax . 160 As a reminder , here's the sample data defining what the AdventureWorks report output should look : like Here's a query written to meet the same.jpg)
Earlier this month, I got the opportunity to visit Malaysia for community sessions on June 29 – July 1, 2010 at Kuala Lumpur, Malaysia, which I would consider as valuable experience. I presented two different sessions at the event. The event was extremely popular in local community, and I had great time meeting people in [...]
THE SQL Server Blog Spot on the Web Welcome to SQLblog.com The SQL Server blog spot on the web Sign in Join Help in Page Free Space Paul White Entire Site Search Home Blogs Forums Downloads Opml Page Free Space Paul White A SQL Server blog from the Kāpiti Coast , New Zealand Partitioning and the Common Subexpression Spool SQL Server 2005 introduced the OVER clause to enable partitioning of rowsets before applying a window function . 160 This post looks at how this feature may require a query plan containing a common subexpression spool' . 160 This query plan construction is required whenever an aggregate window function or the NTILE ranking window function is . used Example To illustrate , here is a simple query based on the AdventureWorks sample database . 160 The AdventureWorks product
While trying out the new DACPAC feature in SQL Server 2008 R2 recently, I ran into a bug that makes no sense to me at all. In my environment we use an Active Directory Group for the SQL Database Administrators and that group is a member of the sysadmins fixed server role inside of SQL Server, which I’d suspect is pretty standard in most environments. The new Data-tier Application feature in SQL Server 2008 R2, has a bug that makes it impossible to Register a new Data-tier Application...(read more)
Every time I go for SQL Server Training or Consultation, I always learn something from my attendees. Sometime I even learn that a simple concept which we think is easy and whole world knows, many times very few knows about the same. In recent training I was surprised to know that many people think that [...]
Today is the last day to win MSDN subscription on this blog. SQL Server MVP Madhivanan is known name. As there are more than 150 comments, I had requested him to help me out with deciding the winner. After looking at the quality responses, he has for sure accepted to hep me out with the [...]
Today I will be speaking on the subject of Spatial Database at Community Tech Days at Ahmedabad. The event is absolutely FREE. We have so far received 500+ RSVP but there are only limited 250 seats are available. We are doing our best to inform everybody about their registration status. If you have received confirmation [...]
This is guest post by one of my very good friends and .NET MVP, Dhananjay Kumar. The very first impression one gets when they meet him is his politeness. He is an extremely nice person, but has superlative knowledge in .NET and is truly helpful to all of us. Objective In this article, I will [...]
SQL Server is one of the most popular products of Microsoft and a large amount of quality content is available online. Solid Quality Mentors have together built a superior quality journal, which contains the best of the best authentic articles from renowned experts of SQL Server. When I downloaded SolidQ Journal, the very first feeling [...]
Dive deep into the world of Microsoft technologies at the Community TechDays and get trained on the latest from Microsoft. Build real connections with Microsoft experts and community members, and gain the inspiration and skills needed to maximize your impact on your organization while enhancing your career. What more… you can watch some of these [...]
Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of TODATETIMEOFFSET as well. Let us go over the definition of the TODATETIMEOFFSET from BOL: Returns a datetimeoffset value that is translated from a datetime2 expression. What [...]
I was recently asked if I know how SWITCHOFFSET works. This feature only works in SQL Server 2008. Here is quick definition of the same from BOL: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. What essentially it does is that changes the [...]
If you are not keen on repetitive typing, you can still rapidly produce production-quality documented code by planning ahead and using Extended properties, and system views. Phil Factor explains, with some Scary SQL
I am very glad today that I have reached milestone of 1400th post. I was looking back to my journey which I started on Nov 1, 2006 and I feel that it has been long way. I have noticed a lot of changes in myself too. Earlier, I used to write a milestone post every [...]
I am very glad that I received this prestigious award for the third time in a row. I am very thankful to Microsoft for introducing this wonderful technology of SQL Server. I enjoy getting involved with the community, which also helps in my self-improvement as well. I would like to take this moment to thank [...]
Self Join has always been an note-worthy case. It is interesting to ask questions on self join in a room full of developers. I often ask – if there are three kind of joins, i.e.- Inner Join, Outer Join and Cross Join; what type of join is Self Join? The usual answer is that it [...]<img alt="" border="0" src="http://stats.wordpress.com/b.gif?host=blog.sqlauthority.com&blog=668536&post=9488&subd=sqlauthority&ref=&feed=1" /