SQL Server – How to Determine if Queries Run in Memory or Disk

sql serversql-server-2008

I came across a set of stored procedures in an application today that are called repeatedly within a long running process. Within each procedure I found multiple different select statements, some within loops; not surprisingly, these routines as currently used take several minutes to run, when intuition would expect them to complete in a few seconds.

It seems fairly obvious that performance wasn't taken into consideration when these procedures were written, there are multiple instances of things that are just "not a good idea".

Processing each row when importing data is taking 300ms per row, so relatively small imports are taking several minutes to process.

However, the tables involved in the procedures are for the most part quite small. I'm thinking, if all of these tables are fully resident in memory, perhaps there's not all that much that can be gained by rewriting any of this.

I'm trying to determine….for this obviously inefficient code, how much of a real affect is it having? Is it worth fixing?

So the question is:
– is there a way to determine what tables are entirely pinned in memory?
– is there a way to turn on tracing in order to monitor nested stored procedures to find the particularly expensive portions?

Note: This is on SQL Server 2008 R2

Best Answer

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.

Related Question