TOP 100 causing SQL Server 2008 hang? -


i have inherited poorly designed , maintained database , have been using knowledge of sql server , little luck keeping high availability server , not completing coming down in flames (the previous developer, quit kept system 4 years).

i have come across strange problem today. hope can explain me if happens again there way fix it.

anyway, there stored proc pretty simple. joins 2 tables between short date/time range (5 mins range) , passes results (this query runs every 5 mins via windows service). largest table has 100k rows, smallest table has 10k rows. stored proc simple , does:

note:the table , columns names have been changed protect innocent.

select top 100 m.* dbo.mytable1 m (nolock) inner join dbo.mytable2 s (nolock) on m.table2id = s.table2id m.rowactive = 1       , s.datestarted <= dateadd(minute, -5, getdate()) order m.datestarted

now, if keep "top 100" in query, query hangs until stop (running in sms or in stored proc). if remove top 100, query works planned , returns 50-ish rows, should (we don't want return more 100 rows if can it).

so, did investigating, using sp_who, sp_who2, , looked @ master..sysprocesses , used dbcc inputbuffer spids might locking or blocking. no blocks , no locking.

this started today no changes these these 2 tables designs , gather last time query/tables have been touched 3 years ago , has been running without error since.

now, side note, , don't know if have this. reindexed both these tables 24 hours before because 99% fragmented (remember, said poorly designed , poorly maintained server).

can explain why sql server 2008 this?

the absolute first thing would side side comparison of query plans of full , top 100 queries , see if top 100 not performant. might need update stats or have missing indexes.


Comments

Post a Comment

Popular posts from this blog

WPF: binding viewmodel property of type DateTime to Calendar inside ItemsControl -

Image
i have problem wpf binding. want bind list of months itemscontrol shows calendar control each month. each rendered calendar shows datetime.now,not bound datetimes. know why happening? this have far: the mainwindow.xaml <window x:class="calendarlisttest.mainwindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" title="mainwindow" height="350" width="525"> <grid> <itemscontrol x:name="calendarlist"> <itemscontrol.itemtemplate> <datatemplate> <calendar displaydate="{binding currentdate}" /> </datatemplate> </itemscontrol.itemtemplate> </itemscontrol> </grid> ** place collection assigned itemssource** private void window_loaded( object sender, routedeventargs e ) { cale...
Read more

java - Getting corefrences with Standard corenlp package -

i'm trying coreferences in text. i'm new corenlp package. tried code below, doesn't work, i'm open other methods well. /* * change template, choose tools | templates * , open template in editor. */ package corenlp; import edu.stanford.nlp.ling.coreannotations.collapsedccprocesseddependenciesannotation; import edu.stanford.nlp.ling.coreannotations.corefgraphannotation; import edu.stanford.nlp.ling.coreannotations.namedentitytagannotation; import edu.stanford.nlp.ling.coreannotations.partofspeechannotation; import edu.stanford.nlp.ling.coreannotations.sentencesannotation; import edu.stanford.nlp.ling.coreannotations.textannotation; import edu.stanford.nlp.ling.coreannotations.tokensannotation; import edu.stanford.nlp.ling.coreannotations.treeannotation; import edu.stanford.nlp.ling.corelabel; import edu.stanford.nlp.pipeline.*; import edu.stanford.nlp.trees.tree; import edu.stanford.nlp.trees.semgraph.semanticgraph; import edu.stanford.nlp.util.coremap; import e...
Read more

jQuery clickable div with working mailto link inside -

i have div want clickable need mailto link inside div still work too. when hover on mailto link mailto appears @ bottom of browser clicking activates link attached div. anyway around this? thanks. <div class="directory_search_results"> <img src="images/no_photo.png" /> <ul class="staff_details"> <li class="search_results_name"><a href="http://www.netflix.com">micheal staff</a></li> <li class="search_results_location">university of illinois</li> <li class="search_results_email"><a href="mailto:test@test.org">test@test.com</a></li> <li class="search_results_phone">(407) 555-1212</li> <li class="search_results_office">(407) 555-1212</li> </ul></div> and jquery $(document).ready(function(){ $(".directory_search_results").click(function(){ ...
Read more