SQL Server Performance Forum – Threads Archive

Significant Difference In Query Run Time – Help

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

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?

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

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.

On top of what Adriaan said, look at the execution plan differences.

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[^]

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.

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.

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

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.

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

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

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

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.

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…..

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.

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 ????

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> 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 ?? <hr height="1" noshade id="quote"></blockquote id="quote"> 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. And you can actually post the plans. Do <pre id="code"> SET SHOWPLAN_TEXT ON GO < your query goes here > </pre id="code"> Set the output of QA to Text mode via Ctrl-T and post the result here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Have you updated statistics on both servers?

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 ?? 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 !! 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 !! 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 ?? 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 ?? Thanks Again Lee [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

]]>