May 12, 2009 at 2:53 pm
RBarryYoung (5/8/2009)
Wayne, questions:2) Have you used the time statistics to confirm if the QP estimates per statement are accurate (i.e., is the last statement really 75% of the run-time).
With the SET STATISTICS TIME ON, this does confirm that the last query is the biggest issue.
This was a "duh" thing for me, but I had forgotten it earlier... the execution time is only 600ms when I turn off the show actual execution plan. About 6500ms with it on.....
I did find the cause of the RID Lookup that was going on... I had someone omitted one column from the index.
I was able to tweak two other indexes to get slightly better performance (changing the order of the columns in the index).
Bottom line: I still have several index scans going on. I've completely eliminated the table scans and RID lookups. Most index scans that are still going on appear to be over a range of the index, not the entire index. The primary exceptions are those tables that Gail noted. I think that Gail's suggestions for procedure changes (dealing with the obligations table and the catch-all going on in the where clause) is the best bet for getting better performance out of this procedure. Of course, when I get into the procedure, I'll be looking at eliminating that cursor. I'd like to get rid of the index scans. However, I dug into this because of deadlock issues it was causing. With the indexes I've implemented, the deadlocks (currently) appear to be gone.
Gail: great article you wrote on your blog!
I want to thank all of you for looking into this with me, but mostly in helping me learn while doing so.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 12, 2009 at 6:33 pm
Not sure if this was mentioned in this thread, but are index stats up-to-date and is index fragmentation at low levels?
The optimizer can make the wrong choices if stats is not up-to-date.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 12, 2009 at 10:25 pm
WayneS (5/12/2009)
Most index scans that are still going on appear to be over a range of the index, not the entire index.
Index scans always read the whole index. They many return part of the index (based on the predicate), but they read the entire thing. It's only index seeks that can read part of the index and return it
Gail: great article you wrote on your blog!
Thanks. Which article in particular?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 13, 2009 at 7:59 am
GilaMonster (5/12/2009)
WayneS (5/12/2009)
Most index scans that are still going on appear to be over a range of the index, not the entire index.Index scans always read the whole index. They many return part of the index (based on the predicate), but they read the entire thing. It's only index seeks that can read part of the index and return it
Hmmm, I didn't know that. I guess I suspected it in the back of my mind though. I knew that it is bad to have them.
Gail: great article you wrote on your blog!
Thanks. Which article in particular?
A few posts ago you had a link to an ongoing discussion on the catch-all query. In that thread, you have a post to http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/, which is what I was referring to.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply