Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

migrated from stackoverflow.com Feb 1 '13 at 15:35

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 10 down vote accepted

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.

share|improve this answer
    
> "...reducing the number of them (for example by adding an appropriate index) can make your queries run a lot faster." Could you explain how adding an index will reduce(?) logical reads? Is logical read synonymous with a table scan? –  tbone Jan 30 '13 at 5:00
1  
Added an explanation to my answer above. –  Sebastian Meine Jan 30 '13 at 13:39
    
Thanks. Even assuming proper indexes are on all the tables involved...I'd think there is still a vast performance difference between a table being pinned in memory vs being read from disk (assume same indexes in both scenarios)...or in other words, adding indexes will get you less of a % performance boost on a machine with lots of RAM than on a machine with less memory....correct? –  tbone Jan 30 '13 at 17:27
1  
physical disk access is clearly order of magnitudes more expensive than memory access. So taking measures to avoid it will get you very far. You still should look at number of logical reads first when query tuning. Keeping them low will in turn keep physical reads low. There is also a high chance that pages don't have to be evicted from cache reducing required physical reads even more. –  Sebastian Meine Jan 30 '13 at 21:59
2  
Minor nitpick - I think pages are 8kb :-). Good answer. –  onupdatecascade Feb 3 '13 at 4:52
show 2 more comments
  • is there a way to turn on tracing in order to monitor nested stored procedures to find the particularly expensive portions?

You can use SQL Profiler. When you start the trace, you should chose RPC Completed, SP Starting, SP StmtStarting and SP StmtCompleted (see image below)

enter image description here

This will let you see every query that runs inside of stored procedures. It will let you see how many times a nested stored procedure is called. When the trace is over, you should save it. Then, re-open it, and after that, you will be able to filter (with the "Column filters" button) to find the queries that causes you problem. (ex: the queries that took more than x reads or that lasted more than x seconds (duration)...)

The profiler options I showed you also show the execution plan, wich is also a lot of help.

share|improve this answer
add comment

It seems like a general query optimization question. From your description I would:

  1. Look at the code to see if it does row-by-row processing. If it does, then often orders of magnitude improvement can be made by implementing the same logic using sets (multiple rows processed at the same time). In other words, if it acts like "loop over each row" change it to "process all the rows." SQL excels at that because the optimizer can choose from more possible methods, potentially use parallelism, remove a lot of overhead that comes from one-row-at-a-time.
  2. Make sure, next, that there are indexes that support the work. Often, again, orders of magnitude improvement may be had with correct indexes vs. not. This is true in memory and with disk access. Processes can still take hours with everything in RAM if there aren't appropriate indexes on a large data set.
  3. Next, with set logic and indexes in place, I would look at whether the affected data pages fit in memory. At this point, if there's still a lot of disk access, looking at physical reads and disk activity makes sense, because all the big gains from optimization are done in the first two steps.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.