October 14, 2019 at 10:55 pm
Whats difference between using execution plans vs statistics i/o to analyze and troubleshoot in sql server?
October 15, 2019 at 12:15 pm
Great question!! I can't stress enough the importance of you learning this. There are lots of materials you can find and they are worth your time. A good developer should understand and use both.
Here is a really nice tool I use to convert the stats data into easily readable information - http://statisticsparser.com/
October 15, 2019 at 2:21 pm
Really it's best to use them in combination.
High I/O numbers tell you to look for better ways to do related part(s) of a query. The execution plan shows you what SQL is currently doing so you can decide if there might indeed be a better way.
For a basic example, say you see very high I/O on table dbo.abc. You look at the query plan and see a clustered index scan (full table scan) on that table. Then you would consider an index on that table and/or, if enough factors point that way, reclustering the table to avoid reading the entire table.
Of course sometimes high I/O numbers are unavoidable, such as when the table is very large, and you've already limited the rows with an appropriate index. Thus, it's a lot of I/O simply because there are so many rows that you must read. Then you look at data compression and other ways to reduce I/O.
Most important, realize that query tuning is definitely an iterative process. Don't expect to jump straight to the most efficient version. Expect to work your there, refinement by refinement.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 15, 2019 at 3:09 pm
Suggest you get Grant's book on execution plans, statistics IO is not comparable when it comes to any kind of troubleshooting unless you want to troubleshoot yourself in the foot.
😎
October 15, 2019 at 5:37 pm
Scott has a good explanation, but I agree with Eirikur, get Grant's book: https://www.sqlservercentral.com/books/sql-server-execution-plans-second-edition-by-grant-fritchey
October 15, 2019 at 5:52 pm
I think we may have interpreted the q differently. I took "statistics i/o" to mean the results from:
SET STATISTICS IO ON;
The logical i/o results are directly comparable, aren't they?
I think maybe (?) Eirikur took it to mean the estimate % [of batch] from the query plan, which is indeed often misleading and should just be ignored.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2019 at 11:14 am
I think we may have interpreted the q differently. I took "statistics i/o" to mean the results from:
SET STATISTICS IO ON;
The logical i/o results are directly comparable, aren't they?
I think maybe (?) Eirikur took it to mean the estimate % [of batch] from the query plan, which is indeed often misleading and should just be ignored.
You are right Scott, that was my initial understanding.
😎
One thing to keep in mind is that setting STATISTICS IO ON can affect the actual execution as we have seen many times, especially in performance test harnesses.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply