June 19, 2009 at 3:10 am
Hello all.
Can someone please help me with the problem that is driving me crazy.
I have lots of connections (~100) from a VB6 application to ms sql 2005 standard edition. Sometimes (when lots of the connections are active) the sql server just stops processing other queries. There is also a website using the same database and it stops getting data from the database. The sql server is showing 0 - 10% cpu usage when this happens so it must be a configuration problem?
I had the same problem before when the application was using ms sql 2005 desktop engine and I saw in the error log the error about exceeding 8 concurrent connections so I decided to upgrade to standard edition but the problem hasn't gone away, now I just don't get the error.
Thanks for any replies!
June 19, 2009 at 3:28 am
This can be due to any reason. Why don't you check for blocking to begin with.
select * from sysprocesses where blocked0
June 19, 2009 at 3:42 am
Hi. Thanks for your reply.
The query came back with 110 rows.
Here is the results file:
June 19, 2009 at 4:29 am
Is there any way I could see some data of each blocked processes like query string or at least which table its supposed to be updating?
Thanks.
June 19, 2009 at 6:28 am
you will get the spid of the blocking connection in sysprocesses.
use
DBCC INPUTBUFFER(spid)
to get the query
June 19, 2009 at 6:30 am
Once you get to know which query is blocking, you may want to see if it's optimized, whether it's using indexes properly.
June 19, 2009 at 7:48 am
Try to run this query:
select b.text, a.* from sysprocesses a cross apply sys.dm_exec_sql_text(sql_handle) b
where blocked0
You seem to have a lot of blocking, perhaps there is an uncommited transaction in your VB app?
...and your only reply is slàinte mhath
June 19, 2009 at 8:37 am
Once you've established blocking, you need to understand why there is blocking. It could be poor performance requiring index or query tuning or it might be transaction management or contention on resources. The best thing to do is start isolating why the processes are waiting. Read this white paper from Microsoft on using wait states and queues as a way to identify what's causing processes to wait and subsequently block other processes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 19, 2009 at 8:59 am
Thank you all very much for your replies, they have been very helpful.
I have figured the source of problems to be this huge command that is executed 4 times after every 15 seconds with different parameters.
Please state any ideas on how to make this query more efficient and fast because it takes a lot of time to execute and while it's executing no other process can access the table but it is necessary.
Update tournamententrants set Rank='95' where tournmentID ='AJd1fqk8qt' and username ='tarzan' Update tournamententrants set Rank='94' where tournmentID ='AJd1fqk8qt' and username ='carpacho' Update tournamententrants set Rank='229' where tournmentID ='AJd1fqk8qt' and username ='trucha' Update tournamententrants set Rank='93' where tournmentID ='AJd1fqk8qt' and username ='capote' Update tournamententrants set Rank='92' where tournmentID ='AJd1fqk8qt' and username ='jefe' Update tournamententrants set Rank='228' where tournmentID ='AJd1fqk8qt' and username ='coronel' Update tournamententrants set Rank='91' where tournmentID ='AJd1fqk8qt' and username ='tramposo' Update tournamententrants set Rank='90' where tournmentID ='AJd1fqk8qt' and username ='pecoso' Update tournamententrants set Rank='227' where tournmentID ='AJd1fqk8qt' and username ='cabaretera' Update tournamententrants set Rank='226' where tournmentID ='AJd1fqk8qt' and username ='compa' Update tournamententrants set Rank='225' where tourn
mentID ='AJd1fqk8qt' and username ='chito' Update tournamententrants set Rank='89' where tournmentID ='AJd1fqk8qt' and username ='chiquis' Update tournamententrants set Rank='224' where tournmentID ='AJd1fqk8qt' and username ='payaso' Update tournamententrants set Rank='223' where tournmentID ='AJd1fqk8qt' and username ='pepito' Update tournamententrants set Rank='222' where tournmentID ='AJd1fqk8qt' and username ='grapa' Update tournamententrants set Rank='235' where tournmentID ='AJd1fqk8qt' and username ='polico' Update tournamententrants set Rank='88' where tournmentID ='AJd1fqk8qt' and username ='tico' Update tournamententrants set Rank='87' where tournmentID ='AJd1fqk8qt' and username ='galan' Update tournamententrants set Rank='221' where tournmentID ='AJd1fqk8qt' and username ='dado' Update tournamententrants set Rank='220' where tournmentID ='AJd1fqk8qt' and username ='frijol' Update tournamententrants set Rank='86' where tournmentID ='AJd1fqk8qt' and username ='lampon' Update tournamente
ntrants set Rank='85' where tournmentID ='AJd1fqk8qt' and username ='reteto' Update tournamententrants set Rank='84' where tournmentID ='AJd1fqk8qt' and username ='pancho' Update tournamententrants set Rank='83' where tournmentID ='AJd1fqk8qt' and username ='huevon' Update tournamententrants set Rank='82' where tournmentID ='AJd1fqk8qt' and username ='campeon' Update tournamententrants set Rank='219' where tournmentID ='AJd1fqk8qt' and username ='azteca' Update tournamententrants set Rank='81' where tournmentID ='AJd1fqk8qt' and username ='defrino' Update tournamententrants set Rank='108' where tournmentID ='AJd1fqk8qt' and username ='ruperto' Update tournamententrants set Rank='107' where tournmentID ='AJd1fqk8qt' and username ='jipon' Update tournamententrants set Rank='106' where tournmentID ='AJd1fqk8qt' and username ='cubana' Update tournamententrants set Rank='218' where tournmentID ='AJd1fqk8qt' and username ='salmon' Update tournamententrants set Rank='105' where tournmentID ='AJd1fqk8qt' a
nd username ='ventos' Update tournamententrants set Rank='217' where tournmentID ='AJd1fqk8qt' and username ='Jim' Update tournamententrants set Rank='216' where tournmentID ='AJd1fqk8qt' and username ='Jack' Update tournamententrants set Rank='104' where tournmentID ='AJd1fqk8qt' and username ='Joe' Update tournamententrants set Rank='215' where tournmentID ='AJd1fqk8qt' and username ='mmmmm' Update tournamententrants set Rank='214' where tournmentID ='AJd1fqk8qt' and username ='mmmm1' Update tournamententrants set Rank='103' where tournmentID ='AJd1fqk8qt' and username ='mmmm2' Update tournamententrants set Rank='213' where tournmentID ='AJd1fqk8qt' and username ='mmmm3' Update tournamententrants set Rank='212' where tournmentID ='AJd1fqk8qt' and username ='mmmm4' Update tournamententrants set Rank='211' where tournmentID ='AJd1fqk8qt' and username ='mmmm5' Update tournamententrants set Rank='210' where tournmentID ='AJd1fqk8qt' and username ='mmmm6' Update tournamententrants set Rank='234' wher
e tournmentID ='AJd1fqk8qt' and username ='mmmm7' Update tournamententrants set Rank='102' where tournmentID ='AJd1fqk8qt' and username ='mmmm8' Update tournamententrants set Rank='209' where tournmentID ='AJd1fqk8qt' and username ='mmmm9' Update tournamententrants set Rank='101' where tournmentID ='AJd1fqk8qt' and username ='mmmm10' Update tournamententrants set Rank='208' where tournmentID ='AJd1fqk8qt' and username ='mmmm11' Update tournamententrants set Rank='100' where tournmentID ='AJd1fqk8qt' and username ='mmmm12' Update tournamententrants set Rank='99' where tournmentID ='AJd1fqk8qt' and username ='mmmm13' Update tournamententrants set Rank='207' where tournmentID ='AJd1fqk8qt' and username ='mmmm14' Update tournamententrants set Rank='98' where tournmentID ='AJd1fqk8qt' and username ='mmmm15' Update tournamententrants set Rank='97' where tournmentID ='AJd1fqk8qt' and username ='mmmm16' Update tournamententrants set Rank='236' where tournmentID ='AJd1fqk8qt' and username ='mmmm17' Update
tournamententrants set Rank='96' where tournmentID ='AJd1fqk8qt' and username ='mmmm18' Update tournamententrants set Rank='206' where tournmentID ='AJd1fqk8qt' and username ='mmmm19' Update tournamententrants set Rank='205' where tournmentID ='AJd1fqk8qt' and username ='mmmm20' Update tournamententrants set Rank='204' where tournmentID ='AJd1fqk8qt' and username ='mmmm21' Update tournamententrants set Rank='203' where tournmentID ='AJd1fqk8qt' and username ='mmmm22' Update tournamententrants set Rank='202' where tournmentID ='AJd1fqk8qt' and username ='mmmm23' Update tournamententrants set Rank='201' where tournmentID ='AJd1fqk8qt' and username ='mmmm24' Update tournamententrants set Rank='200' where tournmentID ='AJd1fqk8qt' and username ='mmmm25' Update tournamententrants set Rank='199' where tournmentID ='AJd1fqk8qt' and username ='mmmm26' Update tournamententrants set Rank='198' where tournmentID ='AJd1fqk8qt' and username ='mmmm27' Update tournamententrants set Rank='197' where tournmentID ='
AJd1fqk8qt' and username ='mmmm28' Update tournamententrants set Rank='196' where tournmentID ='AJd1fqk8qt' and username ='mmmm29' Update tournamententrants set Rank='195' where tournmentID ='AJd1fqk8qt' and username ='mmmm30' Update tournamententrants set Rank='194' where tournmentID ='AJd1fqk8qt' and username ='mmmm31' Update tournamententrants set Rank='193' where tournmentID ='AJd1fqk8qt' and username ='mmmm32' Update tournamententrants set Rank='192' where tournmentID ='AJd1fqk8qt' and username ='mmmm70' Update tournamententrants set Rank='191' where tournmentID ='AJd1fqk8qt' and username ='mmmm71' Update tournamententrants set Rank='190' where tournmentID ='AJd1fqk8qt' and username ='mmmm72' Update tournamententrants set Rank='189' where tournmentID ='AJd1fqk8qt' and username ='mmmm73' Update tournamententrants set Rank='188' where tournmentID ='AJd1fqk8qt' and username ='mmmm74' Update tournamententrants set Rank='187' where tournmentID ='AJd1fqk8qt' and username ='mmmm75' Update tournament
entrants set Rank='186' where tournmentID ='AJd1fqk8qt' and username ='mmmm76' Update tournamententrants set Rank='185' where tournmentID ='AJd1fqk8qt' and username ='mmmm77' Update tournamententrants set Rank='184' where tournmentID ='AJd1fqk8qt' and username ='mmmm78' Update tournamententrants set Rank='183' where tournmentID ='AJd1fqk8qt' and username ='mmmm79' Update tournamententrants set Rank='182' where tournmentID ='AJd1fqk8qt' and username ='mmmm80' Update tournamententrants set Rank='181' where tournmentID ='AJd1fqk8qt' and username ='mmmm81' Update tournamententrants set Rank='180' where tournmentID ='AJd1fqk8qt' and username ='mmmm82' Update tournamententrants set Rank='179' where tournmentID ='AJd1fqk8qt' and username ='mmmm33' Update tournamententrants set Rank='178' where tournmentID ='AJd1fqk8qt' and username ='mmmm34' Update tournamententrants set Rank='177' where tournmentID ='AJd1fqk8qt' and username ='mmmm35' Update tournamententrants set Rank='80' where tournmentID ='AJd1fqk8qt
' and username ='mmmm36' Update tournamententrants set Rank='79' where tournmentID ='AJd1fqk8qt' and username ='mmmm37' Update tournamententrants set Rank='78' where tournmentID ='AJd1fqk8qt' and username ='mmmm38' Update tournamententrants set Rank='77' where tournmentID ='AJd1fqk8qt' and username ='mmmm39' Update tournamententrants set Rank='76' where tournmentID ='AJd1fqk8qt' and username ='mmmm40' Update tournamententrants set Rank='75' where tournmentID ='AJd1fqk8qt' and username ='mmmm41' Update tournamententrants set Rank='74' where tournmentID ='AJd1fqk8qt' and username ='mmmm42' Update tournamententrants set Rank='176' where tournmentID ='AJd1fqk8qt' and username ='mmmm43' Update tournamententrants set Rank='175' where tournmentID ='AJd1fqk8qt' and username ='mmmm44' Update tournamententrants set Rank='73' where tournmentID ='AJd1fqk8qt' and username ='mmmm45' Update tournamententrants set Rank='72' where tournmentID ='AJd1fqk8qt' and username ='mmmm46' Update tournamententrants set Rank=
'71' where tournmentID ='AJd1fqk8qt' and username ='mmmm47' Update tournamententrants set Rank='70' where tournmentID ='AJd1fqk8qt' and username ='mmmm48' Update tournamententrants set Rank='174' where tournmentID ='AJd1fqk8qt' and username ='mmmm49' Update tournamententrants set Rank='173' where tournmentID ='AJd1fqk8qt' and username ='mmmm50' Update tournamententrants set Rank='69' where tournmentID ='AJd1fqk8qt' and username ='mmmm51' Update tournamententrants set Rank='172' where tournmentID ='AJd1fqk8qt' and username ='mmmm52' Update tournamententrants set Rank='68' where tournmentID ='AJd1fqk8qt' and username ='mmmm53' Update tournamententrants set Rank='67' where tournmentID ='AJd1fqk8qt' and username ='mmmm54' Update tournamententrants set Rank='66' where tournmentID ='AJd1fqk8qt' and username ='mmmm55' Update tournamententrants set Rank='171' where tournmentID ='AJd1fqk8qt' and username ='mmmm56' Update tournamententrants set Rank='65' where tournmentID ='AJd1fqk8qt' and username ='mmmm57'
Update tournamententrants set Rank='170' where tournmentID ='AJd1fqk8qt' and username ='mmmm58' Update tournamententrants set Rank='64' where tournmentID ='AJd1fqk8qt' and username ='mmmm59' Update tournamententrants set Rank='169' where tournmentID ='AJd1fqk8qt' and username ='mmmm60' Update tournamententrants set Rank='168' where tournmentID ='AJd1fqk8qt' and username ='mmmm61' Update tournamententrants set Rank='63' where tournmentID ='AJd1fqk8qt' and username ='mmmm62' Update tournamententrants set Rank='62' where tournmentID ='AJd1fqk8qt' and username ='mmmm63' Update tournamententrants set Rank='167' where tournmentID ='AJd1fqk8qt' and username ='mmmm64' Update tournamententrants set Rank='61' where tournmentID ='AJd1fqk8qt' and username ='mmmm65' Update tournamententrants set Rank='60' where tournmentID ='AJd1fqk8qt' and username ='mmmm66' Update tournamententrants set Rank='166' where tournmentID ='AJd1fqk8qt' and username ='mmmm67' Update tournamententrants set Rank='59' where tournmentI
D ='AJd1fqk8qt' and username ='mmmm68' Update tournamententrants set Rank='165' where tournmentID ='AJd1fqk8qt' and username ='mmmm69' Update tournamententrants set Rank='164' where tournmentID ='AJd1fqk8qt' and username ='mmmm83' Update tournamententrants set Rank='163' where tournmentID ='AJd1fqk8qt' and username ='mmmm84' Update tournamententrants set Rank='58' where tournmentID ='AJd1fqk8qt' and username ='mmmm85' Update tournamententrants set Rank='57' where tournmentID ='AJd1fqk8qt' and username ='mmmm86' Update tournamententrants set Rank='56' where tournmentID ='AJd1fqk8qt' and username ='mmmm87' Update tournamententrants set Rank='55' where tournmentID ='AJd1fqk8qt' and username ='mmmm88' Update tournamententrants set Rank='54' where tournmentID ='AJd1fqk8qt' and username ='mmmm89' Update tournamententrants set Rank='162' where tournmentID ='AJd1fqk8qt' and username ='mmmm90' Update tournamententrants set Rank='161' where tournmentID ='AJd1fqk8qt' and username ='mmmm91' Update tournamente
ntrants set Rank='160' where tournmentID ='AJd1fqk8qt' and username ='mmmm92' Update tournamententrants set Rank='159' where tournmentID ='AJd1fqk8qt' and username ='mmmm93' Update tournamententrants set Rank='158' where tournmentID ='AJd1fqk8qt' and username ='mmmm94' Update tournamententrants set Rank='157' where tournmentID ='AJd1fqk8qt' and username ='mmmm95' Update tournamententrants set Rank='53' where tournmentID ='AJd1fqk8qt' and username ='mmmm96' Update tournamententrants set Rank='52' where tournmentID ='AJd1fqk8qt' and username ='mmmm97' Update tournamententrants set Rank='156' where tournmentID ='AJd1fqk8qt' and username ='mmmm98' Update tournamententrants set Rank='155' where tournmentID ='AJd1fqk8qt' and username ='mmmm99' Update tournamententrants set Rank='51' where tournmentID ='AJd1fqk8qt' and username ='mmmm100' Update tournamententrants set Rank='50' where tournmentID ='AJd1fqk8qt' and username ='mmmm101' Update tournamententrants set Rank='49' where tournmentID ='AJd1fqk8qt' a
nd username ='mmmm102' Update tournamententrants set Rank='48' where tournmentID ='AJd1fqk8qt' and username ='mmmm103' Update tournamententrants set Rank='47' where tournmentID ='AJd1fqk8qt' and username ='mmmm104' Update tournamententrants set Rank='154' where tournmentID ='AJd1fqk8qt' and username ='mmmm105' Update tournamententrants set Rank='153' where tournmentID ='AJd1fqk8qt' and username ='mmmm106' Update tournamententrants set Rank='152' where tournmentID ='AJd1fqk8qt' and username ='mmmm107' Update tournamententrants set Rank='151' where tournmentID ='AJd1fqk8qt' and username ='mmmm108' Update tournamententrants set Rank='150' where tournmentID ='AJd1fqk8qt' and username ='mmmm109' Update tournamententrants set Rank='46' where tournmentID ='AJd1fqk8qt' and username ='mmmm110' Update tournamententrants set Rank='149' where tournmentID ='AJd1fqk8qt' and username ='mmmm111' Update tournamententrants set Rank='45' where tournmentID ='AJd1fqk8qt' and username ='mmmm112' Update tournamententran
ts set Rank='148' where tournmentID ='AJd1fqk8qt' and username ='mmmm113' Update tournamententrants set Rank='44' where tournmentID ='AJd1fqk8qt' and username ='mmmm114' Update tournamententrants set Rank='43' where tournmentID ='AJd1fqk8qt' and username ='mmmm115' Update tournamententrants set Rank='42' where tournmentID ='AJd1fqk8qt' and username ='mmmm116' Update tournamententrants set Rank='41' where tournmentID ='AJd1fqk8qt' and username ='mmmm117' Update tournamententrants set Rank='147' where tournmentID ='AJd1fqk8qt' and username ='mmmm118' Update tournamententrants set Rank='40' where tournmentID ='AJd1fqk8qt' and username ='mmmm119' Update tournamententrants set Rank='39' where tournmentID ='AJd1fqk8qt' and username ='mmmm120' Update tournamententrants set Rank='146' where tournmentID ='AJd1fqk8qt' and username ='mmmm121' Update tournamententrants set Rank='38' where tournmentID ='AJd1fqk8qt' and username ='mmmm122' Update tournamententrants set Rank='37' where tournmentID ='AJd1fqk8qt' a
nd username ='mmmm123' Update tournamententrants set Rank='145' where tournmentID ='AJd1fqk8qt' and username ='mmmm124' Update tournamententrants set Rank='144' where tournmentID ='AJd1fqk8qt' and username ='mmmm125' Update tournamententrants set Rank='36' where tournmentID ='AJd1fqk8qt' and username ='mmmm126' Update tournamententrants set Rank='35' where tournmentID ='AJd1fqk8qt' and username ='mmmm127' Update tournamententrants set Rank='34' where tournmentID ='AJd1fqk8qt' and username ='mmmm128' Update tournamententrants set Rank='143' where tournmentID ='AJd1fqk8qt' and username ='mmmm129' Update tournamententrants set Rank='33' where tournmentID ='AJd1fqk8qt' and username ='mmmm130' Update tournamententrants set Rank='142' where tournmentID ='AJd1fqk8qt' and username ='mmmm131' Update tournamententrants set Rank='141' where tournmentID ='AJd1fqk8qt' and username ='mmmm132' Update tournamententrants set Rank='140' where tournmentID ='AJd1fqk8qt' and username ='mmmm133' Update tournamententran
ts set Rank='32' where tournmentID ='AJd1fqk8qt' and username ='mmmm134' Update tournamententrants set Rank='31' where tournmentID ='AJd1fqk8qt' and username ='mmmm135' Update tournamententrants set Rank='30' where tournmentID ='AJd1fqk8qt' and username ='mmmm136' Update tournamententrants set Rank='139' where tournmentID ='AJd1fqk8qt' and username ='mmmm137' Update tournamententrants set Rank='29' where tournmentID ='AJd1fqk8qt' and username ='mmmm138' Update tournamententrants set Rank='138' where tournmentID ='AJd1fqk8qt' and username ='mmmm139' Update tournamententrants set Rank='137' where tournmentID ='AJd1fqk8qt' and username ='mmmm140' Update tournamententrants set Rank='136' where tournmentID ='AJd1fqk8qt' and username ='mmmm141' Update tournamententrants set Rank='233' where tournmentID ='AJd1fqk8qt' and username ='mmmm142' Update tournamententrants set Rank='135' where tournmentID ='AJd1fqk8qt' and username ='mmmm143' Update tournamententrants set Rank='134' where tournmentID ='AJd1fqk8q
t' and username ='mmmm144' Update tournamententrants set Rank='133' where tournmentID ='AJd1fqk8qt' and username ='mmmm145' Update tournamententrants set Rank='28' where tournmentID ='AJd1fqk8qt' and username ='mmmm146' Update tournamententrants set Rank='132' where tournmentID ='AJd1fqk8qt' and username ='mmmm147' Update tournamententrants set Rank='131' where tournmentID ='AJd1fqk8qt' and username ='mmmm148' Update tournamententrants set Rank='27' where tournmentID ='AJd1fqk8qt' and username ='mmmm149' Update tournamententrants set Rank='130' where tournmentID ='AJd1fqk8qt' and username ='mmmm150' Update tournamententrants set Rank='129' where tournmentID ='AJd1fqk8qt' and username ='mmmm151' Update tournamententrants set Rank='26' where tournmentID ='AJd1fqk8qt' and username ='mmmm152' Update tournamententrants set Rank='232' where tournmentID ='AJd1fqk8qt' and username ='mmmm153' Update tournamententrants set Rank='128' where tournmentID ='AJd1fqk8qt' and username ='mmmm154' Update tournamente
ntrants set Rank='25' where tournmentID ='AJd1fqk8qt' and username ='mmmm155' Update tournamententrants set Rank='24' where tournmentID ='AJd1fqk8qt' and username ='mmmm156' Update tournamententrants set Rank='127' where tournmentID ='AJd1fqk8qt' and username ='mmmm157' Update tournamententrants set Rank='126' where tournmentID ='AJd1fqk8qt' and username ='mmmm158' Update tournamententrants set Rank='23' where tournmentID ='AJd1fqk8qt' and username ='mmmm159' Update tournamententrants set Rank='22' where tournmentID ='AJd1fqk8qt' and username ='mmmm160' Update tournamententrants set Rank='125' where tournmentID ='AJd1fqk8qt' and username ='mmmm161' Update tournamententrants set Rank='21' where tournmentID ='AJd1fqk8qt' and username ='mmmm162' Update tournamententrants set Rank='20' where tournmentID ='AJd1fqk8qt' and username ='mmmm163' Update tournamententrants set Rank='19' where tournmentID ='AJd1fqk8qt' and username ='mmmmm164' Update tournamententrants set Rank='18' where tournmentID ='AJd1fqk
8qt' and username ='mmmmm165' Update tournamententrants set Rank='124' where tournmentID ='AJd1fqk8qt' and username ='mmmmm166' Update tournamententrants set Rank='123' where tournmentID ='AJd1fqk8qt' and username ='mmmmm167' Update tournamententrants set Rank='17' where tournmentID ='AJd1fqk8qt' and username ='mmmmm168' Update tournamententrants set Rank='231' where tournmentID ='AJd1fqk8qt' and username ='mmmmm169' Update tournamententrants set Rank='122' where tournmentID ='AJd1fqk8qt' and username ='mmmmm170' Update tournamententrants set Rank='121' where tournmentID ='AJd1fqk8qt' and username ='mmmmm171' Update tournamententrants set Rank='16' where tournmentID ='AJd1fqk8qt' and username ='mmmmm172' Update tournamententrants set Rank='15' where tournmentID ='AJd1fqk8qt' and username ='mmmmm173' Update tournamententrants set Rank='120' where tournmentID ='AJd1fqk8qt' and username ='mmmmm174' Update tournamententrants set Rank='14' where tournmentID ='AJd1fqk8qt' and username ='mmmmm175' Update
tournamententrants set Rank='119' where tournmentID ='AJd1fqk8qt' and username ='mmmmm176' Update tournamententrants set Rank='13' where tournmentID ='AJd1fqk8qt' and username ='mmmmm177' Update tournamententrants set Rank='12' where tournmentID ='AJd1fqk8qt' and username ='mmmmm178' Update tournamententrants set Rank='11' where tournmentID ='AJd1fqk8qt' and username ='mmmmm179' Update tournamententrants set Rank='10' where tournmentID ='AJd1fqk8qt' and username ='mmmmm180' Update tournamententrants set Rank='9' where tournmentID ='AJd1fqk8qt' and username ='mmmmm181' Update tournamententrants set Rank='8' where tournmentID ='AJd1fqk8qt' and username ='mmmmm182' Update tournamententrants set Rank='7' where tournmentID ='AJd1fqk8qt' and username ='mmmmm183' Update tournamententrants set Rank='118' where tournmentID ='AJd1fqk8qt' and username ='mmmmm184' Update tournamententrants set Rank='117' where tournmentID ='AJd1fqk8qt' and username ='mmmmm185' Update tournamententrants set Rank='116' where to
urnmentID ='AJd1fqk8qt' and username ='mmmmm186' Update tournamententrants set Rank='115' where tournmentID ='AJd1fqk8qt' and username ='mmmmm187' Update tournamententrants set Rank='6' where tournmentID ='AJd1fqk8qt' and username ='mmmmm188' Update tournamententrants set Rank='114' where tournmentID ='AJd1fqk8qt' and username ='mmmmm189' Update tournamententrants set Rank='113' where tournmentID ='AJd1fqk8qt' and username ='mmmmm190' Update tournamententrants set Rank='112' where tournmentID ='AJd1fqk8qt' and username ='mmmmm191' Update tournamententrants set Rank='111' where tournmentID ='AJd1fqk8qt' and username ='mmmmm192' Update tournamententrants set Rank='110' where tournmentID ='AJd1fqk8qt' and username ='mmmmm193' Update tournamententrants set Rank='109' where tournmentID ='AJd1fqk8qt' and username ='mmmmm194' Update tournamententrants set Rank='5' where tournmentID ='AJd1fqk8qt' and username ='mmmmm195' Update tournamententrants set Rank='230' where tournmentID ='AJd1fqk8qt' and username
='mmmmm196' Update tournamententrants set Rank='4' where tournmentID ='AJd1fqk8qt' and username ='mmmmm197' Update tournamententrants set Rank='3' where tournmentID ='AJd1fqk8qt' and username ='mmmmm198' Update tournamententrants set Rank='2' where tournmentID ='AJd1fqk8qt' and username ='mmmmm199' Update tournamententrants set Rank='1' where tournmentID ='AJd1fqk8qt' and username ='mmmmm200'
update registration set lastloggedin = getdate() where username = 'tarzan' update registration set lastloggedin = getdate() where username = 'ruperto' update registration set lastloggedin = getdate() where username = 'mmmm19' update registration set lastloggedin = getdate() where username = 'mmmm33' update registration set lastloggedin = getdate() where username = 'mmmm60' update registration set lastloggedin = getdate() where username = 'mmmm100' update registration set lastloggedin = getdate() where username = 'mmmm127' update registration set lastloggedin = getdate() where username = 'mmmm154' update registration set lastloggedin = getdate() where username = 'mmmmm181' update registration set lastloggedin = getdate() where username = 'carpacho' update registration set lastloggedin = getdate() where username = 'jipon' update registration set lastloggedin = getdate() where username = 'mmmm20' update registration set lastloggedin = getdate() where username = 'mmmm34' update registration set lastloggedin = g
etdate() where username = 'mmmm61' update registration set lastloggedin = getdate() where username = 'mmmm101' update registration set lastloggedin = getdate() where username = 'mmmm128' update registration set lastloggedin = getdate() where username = 'lampon' update registration set lastloggedin = getdate() where username = 'mmmmm182' update registration set lastloggedin = getdate() where username = 'trucha' update registration set lastloggedin = getdate() where username = 'cubana' update registration set lastloggedin = getdate() where username = 'mmmm21' update registration set lastloggedin = getdate() where username = 'mmmm35' update registration set lastloggedin = getdate() where username = 'mmmm62' update registration set lastloggedin = getdate() where username = 'mmmm102' update registration set lastloggedin = getdate() where username = 'mmmm129' update registration set lastloggedin = getdate() where username = 'mmmm156' update registration set lastloggedin = getdate() where username = 'mmmmm183' upda
te registration set lastloggedin = getdate() where username = 'capote' update registration set lastloggedin = getdate() where username = 'salmon' update registration set lastloggedin = getdate() where username = 'mmmm22' update registration set lastloggedin = getdate() where username = 'mmmm36' update registration set lastloggedin = getdate() where username = 'mmmm63' update registration set lastloggedin = getdate() where username = 'mmmm103' update registration set lastloggedin = getdate() where username = 'mmmm130' update registration set lastloggedin = getdate() where username = 'mmmm157' update registration set lastloggedin = getdate() where username = 'mmmmm184' update registration set lastloggedin = getdate() where username = 'jefe' update registration set lastloggedin = getdate() where username = 'ventos' update registration set lastloggedin = getdate() where username = 'mmmm23' update registration set lastloggedin = getdate() where username = 'mmmm37' update registration set lastloggedin = getdate()
where username = 'mmmm64' update registration set lastloggedin = getdate() where username = 'mmmm104' update registration set lastloggedin = getdate() where username = 'mmmm131' update registration set lastloggedin = getdate() where username = 'mmmm158' update registration set lastloggedin = getdate() where username = 'mmmmm185' update registration set lastloggedin = getdate() where username = 'coronel' update registration set lastloggedin = getdate() where username = 'Jim' update registration set lastloggedin = getdate() where username = 'mmmm24' update registration set lastloggedin = getdate() where username = 'mmmm38' update registration set lastloggedin = getdate() where username = 'mmmm65' update registration set lastloggedin = getdate() where username = 'mmmm105' update registration set lastloggedin = getdate() where username = 'mmmm132' update registration set lastloggedin = getdate() where username = 'mmmm159' update registration set lastloggedin = getdate() where username = 'mmmmm186' update registr
ation set lastloggedin = getdate() where username = 'tramposo' update registration set lastloggedin = getdate() where username = 'Jack' update registration set lastloggedin = getdate() where username = 'mmmm25' update registration set lastloggedin = getdate() where username = 'mmmm39' update registration set lastloggedin = getdate() where username = 'mmmm66' update registration set lastloggedin = getdate() where username = 'mmmm106' update registration set lastloggedin = getdate() where username = 'mmmm133' update registration set lastloggedin = getdate() where username = 'mmmm160' update registration set lastloggedin = getdate() where username = 'mmmmm187' update registration set lastloggedin = getdate() where username = 'pecoso' update registration set lastloggedin = getdate() where username = 'dado' update registration set lastloggedin = getdate() where username = 'pepito' update registration set lastloggedin = getdate() where username = 'mmmm40' update registration set lastloggedin = getdate() where user
name = 'mmmm5' update registration set lastloggedin = getdate() where username = 'mmmm107' update registration set lastloggedin = getdate() where username = 'mmmm12' update registration set lastloggedin = getdate() where username = 'mmmm161' update registration set lastloggedin = getdate() where username = 'mmmmm188' update registration set lastloggedin = getdate() where username = 'cabaretera' update registration set lastloggedin = getdate() where username = 'mmmmm' update registration set lastloggedin = getdate() where username = 'mmmm27' update registration set lastloggedin = getdate() where username = 'mmmm41' update registration set lastloggedin = getdate() where username = 'mmmm68' update registration set lastloggedin = getdate() where username = 'mmmm108' update registration set lastloggedin = getdate() where username = 'mmmm135' update registration set lastloggedin = getdate() where username = 'mmmm162' update registration set lastloggedin = getdate() where username = 'mmmmm189' update registration s
et lastloggedin = getdate() where username = 'compa' update registration set lastloggedin = getdate() where username = 'mmmm1' update registration set lastloggedin = getdate() where username = 'mmmm28' update registration set lastloggedin = getdate() where username = 'mmmm42' update registration set lastloggedin = getdate() where username = 'mmmm69' update registration set lastloggedin = getdate() where username = 'mmmm109' update registration set lastloggedin = getdate() where username = 'mmmm136' update registration set lastloggedin = getdate() where username = 'mmmm163' update registration set lastloggedin = getdate() where username = 'mmmmm190' update registration set lastloggedin = getdate() where username = 'chito' update registration set lastloggedin = getdate() where username = 'mmmm2' update registration set lastloggedin = getdate() where username = 'mmmm29' update registration set lastloggedin = getdate() where username = 'mmmm43' update registration set lastloggedin = getdate() where username = 'm
mmm83' update registration set lastloggedin = getdate() where username = 'mmmm110' update registration set lastloggedin = getdate() where username = 'mmmm137' update registration set lastloggedin = getdate() where username = 'mmmmm164' update registration set lastloggedin = getdate() where username = 'mmmmm191' update registration set lastloggedin = getdate() where username = 'chiquis' update registration set lastloggedin = getdate() where username = 'mmmm3' update registration set lastloggedin = getdate() where username = 'mmmm30' update registration set lastloggedin = getdate() where username = 'mmmm44' update registration set lastloggedin = getdate() where username = 'mmmm84' update registration set lastloggedin = getdate() where username = 'mmmm111' update registration set lastloggedin = getdate() where username = 'mmmm138' update registration set lastloggedin = getdate() where username = 'mmmmm165' update registration set lastloggedin = getdate() where username = 'mmmmm192' update registration set lastl
oggedin = getdate() where username = 'payaso' update registration set lastloggedin = getdate() where username = 'mmmm4' update registration set lastloggedin = getdate() where username = 'mmmm31' update registration set lastloggedin = getdate() where username = 'mmmm45' update registration set lastloggedin = getdate() where username = 'mmmm85' update registration set lastloggedin = getdate() where username = 'mmmm112' update registration set lastloggedin = getdate() where username = 'mmmm139' update registration set lastloggedin = getdate() where username = 'mmmmm166' update registration set lastloggedin = getdate() where username = 'mmmmm193' update registration set lastloggedin = getdate() where username = 'grapa' update registration set lastloggedin = getdate() where username = 'mmmm6' update registration set lastloggedin = getdate() where username = 'mmmm70' update registration set lastloggedin = getdate() where username = 'mmmm47' update registration set lastloggedin = getdate() where username = 'mmmm87'
update registration set lastloggedin = getdate() where username = 'mmmm114' update registration set lastloggedin = getdate() where username = 'mmmm141' update registration set lastloggedin = getdate() where username = 'mmmmm168' update registration set lastloggedin = getdate() where username = 'mmmmm195' update registration set lastloggedin = getdate() where username = 'polico' update registration set lastloggedin = getdate() where username = 'mmmm7' update registration set lastloggedin = getdate() where username = 'mmmm71' update registration set lastloggedin = getdate() where username = 'mmmm48' update registration set lastloggedin = getdate() where username = 'mmmm88' update registration set lastloggedin = getdate() where username = 'mmmm115' update registration set lastloggedin = getdate() where username = 'mmmm142' update registration set lastloggedin = getdate() where username = 'mmmmm169' update registration set lastloggedin = getdate() where username = 'mmmmm196' update registration set lastloggedin
= getdate() where username = 'tico' update registration set lastloggedin = getdate() where username = 'mmmm8' update registration set lastloggedin = getdate() where username = 'mmmm72' update registration set lastloggedin = getdate() where username = 'mmmm49' update registration set lastloggedin = getdate() where username = 'mmmm89' update registration set lastloggedin = getdate() where username = 'mmmm116' update registration set lastloggedin = getdate() where username = 'mmmm143' update registration set lastloggedin = getdate() where username = 'mmmmm170' update registration set lastloggedin = getdate() where username = 'mmmmm197' update registration set lastloggedin = getdate() where username = 'galan' update registration set lastloggedin = getdate() where username = 'mmmm9' update registration set lastloggedin = getdate() where username = 'mmmm73' update registration set lastloggedin = getdate() where username = 'mmmm50' update registration set lastloggedin = getdate() where username = 'mmmm90' update r
egistration set lastloggedin = getdate() where username = 'mmmm144' update registration set lastloggedin = getdate() where username = 'mmmmm171' update registration set lastloggedin = getdate() where username = 'mmmmm198' update registration set lastloggedin = getdate() where username = 'mmmm53' update registration set lastloggedin = getdate() where username = 'mmmm10' update registration set lastloggedin = getdate() where username = 'mmmm74' update registration set lastloggedin = getdate() where username = 'mmmm51' update registration set lastloggedin = getdate() where username = 'mmmm91' update registration set lastloggedin = getdate() where username = 'mmmm118' update registration set lastloggedin = getdate() where username = 'mmmm145' update registration set lastloggedin = getdate() where username = 'mmmmm172' update registration set lastloggedin = getdate() where username = 'mmmmm199' update registration set lastloggedin = getdate() where username = 'frijol' update registration set lastloggedin = getdat
e() where username = 'mmmm11' update registration set lastloggedin = getdate() where username = 'mmmm75' update registration set lastloggedin = getdate() where username = 'mmmm52' update registration set lastloggedin = getdate() where username = 'mmmm93' update registration set lastloggedin = getdate() where username = 'mmmm119' update registration set lastloggedin = getdate() where username = 'mmmm146' update registration set lastloggedin = getdate() where username = 'mmmmm173' update registration set lastloggedin = getdate() where username = 'mmmmm200' update registration set lastloggedin = getdate() where username = 'reteto' update registration set lastloggedin = getdate() where username = 'mmmm13' update registration set lastloggedin = getdate() where username = 'mmmm77' update registration set lastloggedin = getdate() where username = 'mmmm54' update registration set lastloggedin = getdate() where username = 'mmmm94' update registration set lastloggedin = getdate() where username = 'mmmm121' update regi
stration set lastloggedin = getdate() where username = 'mmmm148' update registration set lastloggedin = getdate() where username = 'mmmmm175' update registration set lastloggedin = getdate() where username = 'mmmm46' update registration set lastloggedin = getdate() where username = 'pancho' update registration set lastloggedin = getdate() where username = 'mmmm14' update registration set lastloggedin = getdate() where username = 'mmmm78' update registration set lastloggedin = getdate() where username = 'mmmm55' update registration set lastloggedin = getdate() where username = 'mmmm95' update registration set lastloggedin = getdate() where username = 'mmmm122' update registration set lastloggedin = getdate() where username = 'mmmm149' update registration set lastloggedin = getdate() where username = 'mmmm86' update registration set lastloggedin = getdate() where username = 'huevon' update registration set lastloggedin = getdate() where username = 'mmmm15' update registration set lastloggedin = getdate() where
username = 'mmmm79' update registration set lastloggedin = getdate() where username = 'mmmm56' update registration set lastloggedin = getdate() where username = 'mmmm96' update registration set lastloggedin = getdate() where username = 'mmmm123' update registration set lastloggedin = getdate() where username = 'mmmm150' update registration set lastloggedin = getdate() where username = 'mmmmm177' update registration set lastloggedin = getdate() where username = 'mmmm147' update registration set lastloggedin = getdate() where username = 'campeon' update registration set lastloggedin = getdate() where username = 'mmmmm174' update registration set lastloggedin = getdate() where username = 'mmmm80' update registration set lastloggedin = getdate() where username = 'mmmm57' update registration set lastloggedin = getdate() where username = 'mmmm97' update registration set lastloggedin = getdate() where username = 'mmmm124' update registration set lastloggedin = getdate() where username = 'mmmm151' update registrati
on set lastloggedin = getdate() where username = 'mmmmm178' update registration set lastloggedin = getdate() where username = 'mmmm140' update registration set lastloggedin = getdate() where username = 'azteca' update registration set lastloggedin = getdate() where username = 'mmmm17' update registration set lastloggedin = getdate() where username = 'mmmm81' update registration set lastloggedin = getdate() where username = 'mmmm58' update registration set lastloggedin = getdate() where username = 'mmmm98' update registration set lastloggedin = getdate() where username = 'mmmm125' update registration set lastloggedin = getdate() where username = 'mmmm152' update registration set lastloggedin = getdate() where username = 'mmmmm179' update registration set lastloggedin = getdate() where username = 'mmmmm167' update registration set lastloggedin = getdate() where username = 'defrino' update registration set lastloggedin = getdate() where username = 'mmmm18' update registration set lastloggedin = getdate() where
username = 'mmmm82' update registration set lastloggedin = getdate() where username = 'mmmm59' update registration set lastloggedin = getdate() where username = 'mmmm99' update registration set lastloggedin = getdate() where username = 'mmmm126' update registration set lastloggedin = getdate() where username = 'mmmm153' update registration set lastloggedin = getdate() where username = 'mmmmm180'
Thanks again!!
June 19, 2009 at 9:57 am
If it is executed 4 times a minute, maybe you can build XML from the parameters and run the update in one go in a stored procedure?
...and your only reply is slàinte mhath
June 19, 2009 at 2:50 pm
Thanks for your replies again.
I resolved the blocking problem by making a primary key field for both tables used and using only that field's value in the update string.
Now there is no blocking but another problem has come up.
After my program has ran for a while, the sql server's cpu usage jumps up to 100% and connections get timed out. There is no blocking and no errors and the application is not making more connections then usually. Before it jumps up it is stable at 1%-5%.
Thanks for any ideas!
June 19, 2009 at 4:56 pm
Did you try using XML as Piotr Rodak recommended?
If you look to Acitivty Monitor, which process generates the 100% CPU and what's the current action done by this process?
June 21, 2009 at 3:30 am
The problem was my application is massively running a stored procedure that was quite large and then I optimized it a lot, now everything is working fine.
Thank you all for your help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply