SQL Server Performance

Significant Difference In Query Run Time - Help

Discussion in 'General Developer Questions' started by LeeBuckman, Jun 6, 2006.

  1. LeeBuckman New Member

    Please Help.

    I have a query that runs through T-SQL on a reasonably powerful machine in significantly varying times. It has run in as little as 4 seconds but can also take as long as 40 minutes. Whilst i fully accept that the query will run at different speeds subject to how many other users are running queries along with the type of queries they are running, i cannot accept that this would cause such a significant deterioration in performance. We only have 5 users who could be running inserts and updates at any time during the day. All the tables are sensibly indexed with primary keys/ clustered indexes etc where appropriate. The spec of the machine is as follows :

    Win Server 2003
    SQL Server 2000
    4 x 3.0 Ghz Hyper Threading CPU's With L2 8mb Cache
    1.09 Tb of disk space (RAID 5 array) with TempDB/Log Files On Seperate drives from Data
    16GB of RAM

    We have had our DBA's look into this and they keep saying there is nowt wrong with the Hardware or Network connections.

    Does anyone have any ideas if this is normal that a query would run so much slower just because 1 or 2 other users might be running inserts or updates. I cannot seem to accept this when i have seen it run almost instantly. I also feel we have a reasonable spec machine too!!

    Please Please Help if you have any ideas !!!

    Thanks In Advance

    Lee
  2. Adriaan New Member

    You're probably better off examining the details of the actual 'queries' with the different execution times.

    Are we talking about straight SELECT statements against tables, or about executing a stored procedure?

    What about varying filter criteria?

    And how are you measuring response time - what client app? what type of connection for different users, network-wise?
  3. LeeBuckman New Member

    We are talking about a simple select statement with a group by and count,query given below. As for users, all are located in same physical araea as myself,all use same log on procedure through their windows domain and the connection i think is through TCP/IP and named Pipes !!

    In terms of measuring speed i am simply looking at how long it takes to return the result set into the query analyser window through counter in bottom r h side of Analyser window.


    select idcref,count(distinct mdseln)
    from tdfid join mhfmd on idcref=mdcref
    join mhfmm on mdseln=mmseln
    join mhfqd on mmseln=qdseln
    join mhfpd on qdhprd=pdprodcode
    where mmsedd between 1050529 and 1060529
    and pdcompany=5
    and idcref<281300
    group by idcref

    Any help appreciated

    Lee
  4. Adriaan New Member

    Assuming that the values in your WHERE clause change between calls ...

    Make the object references complete ... you'll have to double-check the following query, as I have no way of telling which column belongs to which table!

    select t1.idcref, count(distinct mdseln)
    from dbo.tdfid t1
    inner join dbo.mhfmd t2 on t1.idcref = t2.mdcref
    inner join dbo.mhfmm t3 on t2.mdseln = t3.mmseln
    inner join dbo.mhfqd t4 on t3.mmseln = t4.qdseln
    inner join dbo.mhfpd t5 on t4.qdhprd = t5.pdprodcode
    where t3.mmsedd between 1050529 and 1060529
    and t5.pdcompany = 5
    and t1.idcref < 281300
    group by t1.idcref

    All columns mentioned in the ON clauses and in the WHERE clause must be covered by appropriate indexes. (Note that a PK constraint is also an index, but an FK constraint is not an index.) Describe the PKs and FKs and indexes on the underlying tables, as far as these columns are concerned.
  5. mmarovic Active Member

    On top of what Adriaan said, look at the execution plan differences.
  6. LeeBuckman New Member

    I have looked at the execution plans of both queries and they are absolutely identical with the same costs and row counts etc allocate dto them ??

    Is there any other way of interpreting these plans ??

    CheeRS

    Lee[^]
  7. Adriaan New Member

    Check the details of the execution plans - lots of table scans with large numbers of rows, which you want to avoid? Or lots of index seeks, which is what all queries in an ideal world would use?

    Try feeding the query to the Index Tuning Wizard, see if it has any suggestions about possibly missing indexes.
  8. mmarovic Active Member

    quote:Originally posted by LeeBuckman

    I have looked at the execution plans of both queries and they are absolutely identical with the same costs and row counts etc allocate dto them ??

    Is there any other way of interpreting these plans ??
    But you did have significantly different execution times? Have you compared estimated or actual execution plan? They should be the same, but it's good to make sure they are.
  9. LeeBuckman New Member

    Hi Again,
    The execution plans show index scans and clustered index scans along with parallelism. Surely this big difference in run time cannot be purely because of the type of queries other users are running ?

    Lee
  10. mmarovic Active Member

    It can be, you may check locks to see if it is the issue. On top of that, parallel execution plans sometimes occures when there is no good index to support serial execution plan.
  11. joechang New Member

    either disable parallel execution plans, or limit max degree of parallelism to 2
    or disable HT, the combination of HT and parallelism is seriously bad news

    also, does the execution plan show a table scan on t3 & t5?
    there should be the following index on these

    t3 mhfmm covered index: mmsedd, mmseln
    t5 mhfpd covered index: pdcompany, pdprodcode
  12. LeeBuckman New Member

    Hi Folks,
    Thanks for all the replies. We are still trying to get to the bottom of this.

    JoeChang, excuse my ignorance but what is HT ? We have another server which has the parallel execution disabled but we were under the impression from Dell that this is a good thing to have and use when you have multiple processors and so much RAM etc...

    Having looked at the table locks etc the only thing i can see which seems a bit weird is that for some queries (process ID's) it says that it is blocking itself. For example. On an insert somebody is doing there are multiple processes for the same query.Some of which say they are blocking themselves !!

    Any ideas ?

    With regard to T3 and T5 i'll double check the execution plan and make sure it's doing what i expected.

    Thanks Again Everyone.Hopefully i can be of help to you people some day !!

    Lee
  13. joechang New Member

    HT is Hyper-Threading
    Dell generally can not provide meaningful technical advice, especially with regards to the details on SQL Server
    Parallel plans are safer in SQL Server 2005, but not sure about the combination of HT and parallelism
  14. mmarovic Active Member

    Self-blocking parallel execution is the behaviour microsoft reports solved with each new release including service packs but it happens again and again under probably slightly different circumstances. As Joe said, I'd work on providing good indexes for the query and disable parallelism for that specific query at least.
  15. LeeBuckman New Member

    OK. Thanks. If we turn off the parallelism option though and specify only to use 1 processor for parallel execution, doesn't this defeat the object of having multiple processors, or am i better off just reducing the threshold for parallel execution consideration ??

    Your views please.....

  16. mmarovic Active Member

    You still get a benefit from multiple processors for running many queries in parallel. That way you just avoid to use more then one processor for a single query that you know it is having problem with parallel executions. Maybe if you review and improve indexes on tables involved you won't need to force anything, query optimizer might choose to use serial execution plan without focing.
  17. LeeBuckman New Member

    Ok. Here we have another dilemma. If i run exactly the same query on a lower spec server, which still has the ability for parallel execution, the execution plans are totally different for the same query. On the lower spec server, the query seems to run quicker too ??

    How can the plans be different, when the data is identical and the indexes are the same. As i cannot paste the execution plan in this window, here is breifly whats happening ??

    Lower spec server

    Table Seek Type Est. Row Count

    MD Clustered Index Seek 16
    PD Clustered Index Seek 40
    QD Clustered Index Seek 30953
    ID Index Seek 76212
    MM Clustered Index Seek 1190

    In a nutshell, the plan goes as follows :

    Nested Loop/Inner Join MD to ID
    Hash Match/Join Result To MM
    Hash MAtch/Join Result to QD
    Hash MAtch/Join Result to PD


    High Spec Server

    Table Seek Type Est. Row Count

    MD Index Seek 91648
    PD Clustered Index Seek 1
    QD Clustered Index Seek 5
    ID Clustered Index Seek 107327
    MM Clustered Index Seek 25

    In a nutshell, the following seems to happen :

    Nested Loop/Inner Join MM to QD
    Nested Loop/Inner Join Result of Above To PD
    Nested Loop/Inner Join Result Of Above To MD
    Hash Match/Inner Join Result Of BAove to ID

    Can anybody shed any light on this please ????


  18. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />How can the plans be different, when the data is identical and the indexes are the same. As i cannot paste the execution plan in this window, here is breifly whats happening ??<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />It depends on several factors how a query plan looks like. No of connections at the time of creation, available memory... It might very well be that two servers don't get the same plan.<br /><br />And you can actually post the plans. Do <br /><pre id="code"><font face="courier" size="2" id="code"><br />SET SHOWPLAN_TEXT ON<br />GO<br />&lt; your query goes here &gt;<br /></font id="code"></pre id="code"><br />Set the output of QA to Text mode via Ctrl-T and post the result here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  19. mmarovic Active Member

    Have you updated statistics on both servers?
  20. LeeBuckman New Member

    Hi. Thanks for all the ideas etc but i am still at a loose end on exactly why this wury should take such varying times. The statistics are updated weekly as part of the DB Maintenance Plan. Is that often enough ??<br /><br />I am currently running a query as we speak which is basically an insert and it has been running over an hour already. Too long in my opininion !!<br /><br />The TempDB is almost 100GB. Is this normal for a TempDB to get to this size when we are running queries. The largest table we have on our server contains 420 million records !!<br />The TempDB is on it's own drive with 4 fixed size files of 25GB each. Also, if we shrink using a truncate only on the TempDB i notice that all bar the original Primary TempDB file simply clear down ie committed transactions are cleared and file remains 25GB in size buit has much more free space. The Primary file actually Reduces in Size if we run a shrink TempDB with Truncate Only. Is this normal ??<br /><br />On another note. The other server that appears to run quicker has a setting of 127 for affinity mask when i run sp_configure whereas ours is 0. What is best ??<br /><br /><br />Thanks Again<br /><br />Lee<br /><br />[<img src='/community/emoticons/emotion-5.gif' alt=';)' />]

Share This Page