SQL Server – Understanding Script Execution Speed Improvement When Split

You can use one of these two queries to see the total logical reads and the total physical reads.

SELECT  DB_NAME(st.dbid) Db,
        OBJECT_NAME(st.objectid, st.dbid) Prc,
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_physical_reads,
        qs.statement_start_offset,
        qs.statement_end_offset,
        st.text
FROM    sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st;

SELECT  DB_NAME(database_id) Db,
        OBJECT_NAME(object_id, database_id) Prc,
        execution_count,
        total_logical_reads,
        total_physical_reads
FROM    sys.dm_exec_procedure_stats ps;

The first one breaks this down by statement, the second one counts in the entire procedure.

Physical reads are reads against the disk, logical reads are against memory. You can use this to figure out which procedures or statements are the most expensive ones in your system and try to tune those.

Keep in mind, that while logical reads are significantly cheaper that physical reads, they are still expensive, so reducing the number of them (for example by adding an appropriate index) can make your queries run a lot faster.

There are a lot of additional columns in the DMVs above that you might find interesting too.

How does an index help to reduce logical reads?

In SQL Server all data is organized in blocks, 8KB in size. These blocks are called "pages".

Every table contains "meta" pages that contain information about the struktur of the table as well as pata pages. If no index exists and you run a query like SELECT * FROM tbl WHERE Id = 7 SQL Server has to go looking for this or these rows in the entire table. So it reads in one page at a time, loops through all rows in each page to determine the rows that fit the WHERE clause. So if the table requires 1,000,000 pages to be stored, this query will take 1,000,000 logical reads to execute.

If you have an index, SQL Server sorts the data logically within the pages and establishes a linked list between the pages. This allows for running queries with an ORDER BY to be executed without an expensive sort operation. But mor importantly that the sorting, SQL Server adds a B+Tree to the table. A B+Tree is a structure comparable to the index in a book, where looking for a specific keyword allows me to directly jump to the page that contains the keyword. The typical book has only one index level whereas a B+Tree can have multiple. Just think of a large book, where the index itself is multiple pages long. In a case like that it makes sense the add an additional index layer that tells us on wich page the index words starting with S are to be found.

B+Trees are optimized to have as few levels as possible while providing the property that any record in the index can be found by reading one page per index level. So assume the above WHERE Id = 7 query when you have an index sorted by Id. Let's say that index has 5 levels. Now, to find all records that match this query I have to read one page per index level (that is 5 pages).This is called an "Index Seek". If there are multiple records that fit the bill I might have to follow the sorted index for a while to retrieve all of them. But lets assume there is only one record.

So, without the index running that query required 1,000,000 reads, with indes it required 5 reads. Even though a logical read is an in-memory operation there is stil a substantial cost - in fact it is the most expensive operation in a trivial query like the one above. So reducing the amount of logical reads needed by a factor of 200,000 will speed up your query by a similar factor.

So, a logical read is not equivalent to a table scan, but a table scan causes a whole lot more logical reads than an index seek.

This is most likely caused by the MERGE operation in SQL Server being single threaded. No matter how many resources you throw at it, it wont run faster which is exactly what you are observing.

The solution is to manually parallelise the query by running multiple copies of the MERGE statement at the same time, each operating on their own subset of the data.

The problem is very common in data warehouses running SQL Server. There are a series of good design pattern in this document: http://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx (DISCLAIMER: This is written by yours truly, so I am shamelessly plugging this)