In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled. Let us learn about how to Enable Automatic Statistic Update on Database.
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3
Earlier I have written two different articles on the subject Remove Bookmark Lookup. This article is as part 3 of the original article. Please read the first two articles here before continuing reading this article.
SQL SERVER – Download Logical Query Processing Poster
You can download the poster from Itzik Ben-Gan’s T-SQL Querying page over here.
Earlier this year, I had written article on SQL SERVER – Logical Query Processing Phases – Order of Statement Execution and I had asked one question to readers. I got very good response for this question. Today, I am going to discuss about one of the errata I have made there. I had displayed the Logical Query Processing order, where I had incorrectly listed the last two operations. I have listed the operations as ORDER BY first and TOP afterwards. The fact is that TOP is always executed first and ORDER BY after that.
SQL SERVER – Queries Waiting for Memory Allocation to Execute
In one of the recent projects, I was asked to create a report of queries that are waiting for memory allocation. The reason was that we were doubtful regarding whether the memory was sufficient for the application. The following query can be useful in similar case. Queries that do not…
Read MoreSQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2
This article is follow up of my previous article SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup. Please do read my previous article before continuing further. I have described there two different methods to reduce query execution cost. Let us compare…
Read MoreSQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
Today, I would like to share one very quick tip about how to remove bookmark lookup or RID lookup. Let us first understand Bookmark lookup or RID lookup. Please note that from SQL Server 2005 SP1 onwards, Bookmark look up is known as Key look up. When a small number…
Read MoreSQL SERVER – Outer Join in Indexed View – Question to Readers
Today I have question for you. Just a day ago I was reading whitepaper Improving Performance with SQL Server 2008 Indexed Views. Following is question and answer I read in the white paper. Q. Why can’t I use OUTER JOIN in an indexed view? A. Rows can logically disappear from…
Read More