Browse by Tags

• Conversions
• DMVs
• Heaps
• Indexes
• Joins
• LOBs
• Memory Grant
• Parallelism
• Performance
• Query Optimizer
• Query Plans
• Seeks
• Sorting
• Spool
• TSQL
• Undocumented

• Can a SELECT query cause page splits?

  Books Online has this to say about page splits: When a new row is added to a full index page , the Database Engine moves approximately half the rows to a new page to make room for the new row.  This reorganization is known as a page split .  Read More... Posted Tuesday, August 30, 2011 7:46 AM by Paul White | 7 Comments Filed under: Internals, Spool, Query Plans, Undocumented, Memory Grant, Sorting, Indexes, Parallelism

• SQL Server, Seeks, and Binary Search

  The following table summarizes the results from my last two blog entries, showing the CPU time used when performing 5 million clustered index seeks: In test 1, making the clustered index unique improved performance by around 40%. In test 2, making the Read More... Posted Tuesday, August 09, 2011 10:10 AM by Paul White | 9 Comments Filed under: Internals, Undocumented, Indexes, Seeks, Performance

• Join Performance, Implicit Conversions, and Residuals

  You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance.  Some people have gone so far as to write scripts to search the plan cache Read More... Posted Tuesday, July 19, 2011 1:17 AM by Paul White | 14 Comments Filed under: Internals, Query Plans, Undocumented, Performance, Joins, Conversions

• Undocumented Query Plans: Equality Comparisons

  The diagram below shows two data sets, with differences highlighted: To find changed rows using TSQL, we might write a query like this: The logic is clear: join rows from the two sets together on the primary key column, and return rows where a change Read More... Posted Wednesday, June 22, 2011 5:58 AM by Paul White | 22 Comments Filed under: Internals, Query Plans, Undocumented, TSQL

• How Parallelism Works in SQL Server

  You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at: http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/ Read More... Posted Saturday, March 05, 2011 9:28 PM by Paul White | 7 Comments Filed under: Internals, Parallelism, TSQL

• Advanced TSQL Tuning: Why Internals Knowledge Matters

  There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes. Query tuning is not complete as soon as the query returns results quickly in the development or test environments. In production, your query will compete for memory, CPU, locks, I/O and other resources on the server. Today’s entry looks at some tuning considerations that are often overlooked, and shows how deep internals knowledge can help you write better TSQL. As always, we’ll need some example data. In fact, we are going to use three tables today, each of which is structured like this: Read More... Posted Wednesday, February 23, 2011 4:19 AM by Paul White | 12 Comments Filed under: Query Optimizer, Internals, Memory Grant, Sorting, LOBs

• I see no LOBs!

  Is it possible to see LOB (large object) logical reads from STATISTICS IO output on a table with no LOB columns? I was asked this question today by someone who had spent a good fraction of their afternoon trying to work out why this was occurring – even going so far as to re-run DBCC CHECKDB to see if any corruption had taken place. The table in question wasn’t particularly pretty – it had grown somewhat organically over time, with new columns being added every so often as the need arose. Nevertheless, it remained a simple structure with no LOB columns – no TEXT or IMAGE, no XML, no MAX types – nothing aside from ordinary INT, MONEY, VARCHAR, and DATETIME types. To add to the air of mystery, not every query that ran against the table would report LOB logical reads – just sometimes – but when it did, the query often took much longer to execute. Ok, enough of the pre-amble. I can’t reproduce the exact structure here, but the following script creates a table that will serve to demonstrate the effect: Read More... Posted Saturday, February 19, 2011 8:11 AM by Paul White | 7 Comments Filed under: Internals, LOBs

• Seeking Without Indexes

  A seek can contain one or more seek predicates – each of which can either identify at most one row in a unique index (a singleton lookup) or a range of values (a range scan). When looking at a query plan, we will often need to look at the details of the seek operator in the Properties window to see how many operations it is performing, and what type of operation each one is. As you saw in the first post in this mini-series, the number of hidden seeking operations can have an appreciable impact on performance. Measuring Seeks and Scans I mentioned in my last post that there is no way to tell from a graphical query plan whether you are seeing a singleton lookup or a range scan. You can work it out – if you happen to know that the index is defined as unique and the seek predicate is an equality comparison, but there’s no separate property that says ‘singleton lookup’ or ‘range scan’. This is a shame, and if I had my way, the query plan would show different icons for range scans and singleton lookups – perhaps also indicating whether the operation was one or more of those operations underneath the covers. In light of all that, you might be wondering if there is another way to measure how many seeks of either type are occurring in your system, or for a particular query. As is often the case, the answer is yes – we can use a couple of dynamic management views (DMVs): sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats. Read More... Posted Thursday, February 17, 2011 7:23 PM by Paul White | 11 Comments Filed under: Internals, Seeks, DMVs, Heaps Attachment(s): ScanSeek.txt