SQL Server Performance

estimated execution plan - need help on output

Discussion in 'Performance Tuning for DBAs' started by v1rtu0s1ty, Nov 1, 2004.

  1. v1rtu0s1ty New Member

    Ok guys,

    I found a query that is has a value of 29 seconds in CPUTime, 350 seconds in duration in Profiler.

    I loaded that query in QA and clicked Display Estimated Execution Plan.

    Please see the links below:

    http://restricted.dyndns.org/target/executionplan1.gif
    http://restricted.dyndns.org/target/executionplan2.gif
    http://restricted.dyndns.org/target/executionplan3.gif
    http://restricted.dyndns.org/target/executionplan4.gif
    http://restricted.dyndns.org/target/executionplan5.gif
    http://restricted.dyndns.org/target/executionplan6.gif

    Can someone please tell me what the result is all about. Why I also got this query as the most CPU intensive too.

    Thanks guys,

    V1rt
  2. Chappy New Member

    Not really sure what it is youre askin. First thing to make sure is that the estimated execution plan matches the ACTUAL execution plan. If it doesnt, it may mean your statistics are not up to date. If it DOES, then at last you know youre looking at representative scenario

    Also you can paste an execution plan as text in a much easier way



    SET SHOWPLAN_ALL ON
    GO
    <your query or stored proc call>
    GO
    SET SHOWPLAN_ALL OFF
    GO
  3. v1rtu0s1ty New Member

    This is what I got using your suggestion.<br /><br /<a target="_blank" href=http://www.restricted.dyndns.org/target/executionplan.txt>http://www.restricted.dyndns.org/target/executionplan.txt</a><br /><br />Ok, I've been digging into why our CPU is always at 85%-95% everday. In task manager, it show's that sqlserver is the culprit. So I did a profiler and found that query to be eating up 29 seconds of CPU time. So from my understanding, a query that is good should not be longer that 100ms. So what I was asking here if can some help me understand that execution plan result such as "does it show where the problem is", what things I should do to get rid of that problem.<br /><br />Thanks.<br /><br />Any help will be greatly appreciated. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />
  4. satya Moderator

    Try to collect PERFORM stats for PROCESSOR, PROCESS, MEMORY counters etc. as well alongwith the PROFILE trace to findout the activity.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. Twan New Member

    Hi ya,

    are you able to post the actual query? and the size of each table involved (sp_spaceused)

    The query shows two clustered index scans, which are just table scans, so depending on the size of those tables that may be impacting performance.

    Cheers
    Twan
  6. v1rtu0s1ty New Member

    Hi Satya,<br /><br />The reason why I am posting the execution plan is because, I found that query to be running 29 seconds long. He is in the top most list of most cpu intensive queries. I have already gathered before about processor being used up all by sqlserver. It even floors to 100% for 5 mins.<br /><br />I also do see tons of exec('insert' + ..............) which hav 5+ seconds in CPU column in profiler.<br /><br />Like what I have mentioned, I would like to know more details about that culprit query. And have learned from this forum that by showing the execution plan, we can see how poor the indexes are. That's where I need help.<br /><br />Hi Twan,<br /><br />I will grab the sizes once I reach office. I will also post the exact query here later.<br /><br />Thanks guys! <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  7. v1rtu0s1ty New Member

    Here are the results of sp_spaceused

    +------------+----------+-----------+-----------+-----------+------------+
    | table name | rows | reserved | data | index_size| unused |
    | | | | | | |
    +------------+----------+-----------+-----------+-----------+------------+
    | | | | | | |
    | FOLDERS | 2696704 | 4110712KB| 1033968KB| 1547080KB| 1529664 KB |
    | | | | | | |
    | TUAUXILIARY| 2696714 | 1387560KB| 523104 KB | 242920 KB | 621536 KB |
    | | | | | | |
    | USERTABLE | 1471 | 456 KB | 416 KB | 16 KB | 24 KB |
    | | | | | | |
    | DEPARTMENT | 2 | 24 KB | 8 KB | 16 KB | 0 KB |
    | | | | | | |
    | USEROFFICE | 1582 | 232 KB | 64 KB | 120 KB | 48 KB |
    +------------+----------+-----------+-----------+-----------+------------+

    and below is the exact query:

    SELECT FOLDERS."BARCODE", FOLDERS."FOLDERNUMBER", FOLDERS."FIELD2", FOLDERS."FIELD3", TUAUXILIARY."PERMLOCATION", TUAUXILIARY."MOVE_DATE", USERTABLE."USERID" FROM { oj ((("FOLDERS" FOLDERS INNER JOIN "TUAUXILIARY" TUAUXILIARY ON FOLDERS."BARCODE" = TUAUXILIARY."BARCODE") INNER JOIN "DEPARTMENT" DEPARTMENT ON FOLDERS."OFFICEDEPARTMENT" = DEPARTMENT."OFFICEDEPARTMENT") INNER JOIN "USEROFFICE" USEROFFICE ON DEPARTMENT."OFFICEDEPARTMENT" = USEROFFICE."OFFICEDEPARTMENT") INNER JOIN "USERTABLE" USERTABLE ON USEROFFICE."USERID" = USERTABLE."USERID"} WHERE USERTABLE."USERID" = 'user78'

    Are there other statistics that you need? If so, what query should I do other than showplan_all on?

    Thanks guys!
  8. v1rtu0s1ty New Member

    Follow up, assuming I run that query in Index Tuning Wizard during production hours (8am-5pm), will it affect our users?

    Thanks
  9. Twan New Member

    Hi ya,

    yes I think that ITW would impact other users, and wouldn't recommend running it during prod hours...

    I've not come across the syntax { oj (((...) } before do you know what this is?

    You could possibly simplify the query to

    SELECT FOLDERS."BARCODE",
    FOLDERS."FOLDERNUMBER",
    FOLDERS."FIELD2",
    FOLDERS."FIELD3",
    TUAUXILIARY."PERMLOCATION",
    TUAUXILIARY."MOVE_DATE",
    USEROFFICE."USERID"
    FROM
    "FOLDERS" FOLDERS
    INNER JOIN "TUAUXILIARY" TUAUXILIARY
    ON FOLDERS."BARCODE" = TUAUXILIARY."BARCODE"
    INNER JOIN "USEROFFICE" USEROFFICE
    ON FOLDERS."OFFICEDEPARTMENT" = USEROFFICE."OFFICEDEPARTMENT"
    WHERE USEROFFICE."USERID" = 'user78'

    with indexes on
    useroffice( userid )
    folders(officedepartment, barcode)
    tuauxiliary( barcode )


    Cheers
    Twan
  10. v1rtu0s1ty New Member

    So what can you say about the result of sp_spaceused? I also would like to focus more on the result of the execution plan. What are my current problems there?
  11. v1rtu0s1ty New Member

    I think, it's the FOLDERS table that is not properly indexed. How do I verify that?
  12. joechang New Member

    1. i would like to know how long (CPU & Dur) the following takes, plan should show a merge join, also just try SELECT COUNT(*) so we exclude network transfer time, etc
    SELECT FOLDERS."BARCODE",
    FOLDERS."FOLDERNUMBER",
    FOLDERS."FIELD2",
    FOLDERS."FIELD3",
    TUAUXILIARY."PERMLOCATION",
    TUAUXILIARY."MOVE_DATE",
    FROM
    "FOLDERS" FOLDERS
    INNER JOIN "TUAUXILIARY" TUAUXILIARY
    ON FOLDERS."BARCODE" = TUAUXILIARY."BARCODE"

    2. try the following indexes, if the field size permits
    folders(officedepartment, barcode,FOLDERNUMBER,FIELD2,FIELD3)
    tuauxiliary( barcode,PERMLOCATION,MOVE_DATE )

    3. show us what the actual row counts are, not just the estimated
  13. v1rtu0s1ty New Member

    1A. select count(*) from folders took 1250(1.250 seconds) in Duration column and 720 in the CPU column. <br /><br />1B. The long query on a non production hour(7pm) took 76983(1.17 minutes) and CPU is 25750(25+ seconds)<br /><br />2. No answer for that question yet<br /><br />3. Total rows for the long query is 2,701,467<br /> Total rows for the count(*) is 2,701,470<br /><br />Thanks for helping <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  14. joechang New Member

    this suggests the merge join for 2.7M rows is very fast, but it takes a long time to send the data over the network<br />how many rows did your query (user7<img src='/community/emoticons/emotion-11.gif' alt='8)' /> return?<br />
  15. v1rtu0s1ty New Member

    But it was eating up 25 seconds of CPU time. I think, that's were the problem is. I actually did it again last night, and the 1.17 minutes for Duration became 4 minutes.

    Today, that same query I saw was eating up 42 seconds of CPU time. Other queries against FOLDERS were averaging 6 seconds for the CPU time column and they were tons of them.

    From what I have been reading, a good query should not consume more than 100ms of CPU time.

    Any more thoughts?

    Thanks guys!
  16. v1rtu0s1ty New Member

    Hey guys,

    Sorry to resurrect this thread. I did a profiler capture this morning and this query is eating up 600000 milliseconds of durations(10 mins). Will you be able to identify the issue by just looking at this showplan_all result?

    http://restricted.dyndns.org/target/appissue2.txt

    Thanks again guys!

Share This Page