| Author |
Topic |
|
shinaco
Starting Member
6 Posts |
 Posted - 2009-04-28 : 19:56:13
|
| Hello everyone.I maintain an intranet webpage for a retailer that has about 350 stores which use the webpage.I recently found that for no apparent reason the CPU would go to 100% utilization and the site became really slow.The sqlserver process was the one eating the processor, so I restarted the service. It worked but only temporarily, the CPU went to 100% again after a couple of minutes.Then I decided to run the SQL Profiler and find out what was happening. I saw that a single, very simple query was being repeated hundreds (or maybe thousands) of times per second for no reason. After several tests, I found out that this happens only with a particular query in a particular page, and nowhere else.Here is the query: select emp_num from in_incidencia_emp_per_week where start_date = convert(varchar(10),'2009-4-20',20) and emp_num = 00027449Please note that the date and emp_num may vary as they come from ASP variables, but in a single case of this issue they remain the same.I revised my ASP code and saw that there was no possibility of infinite loops, and even so, if that was the case, several other querys should be repeated also because they are inside the loop, not only this one.Any idea on what can be causing this and how can I fix this?Thank you very much for your helpShinacoAfter further tests I found out that this can happen even if the asp page in the client has finished loading. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shinaco
Starting Member
6 Posts |
Posted - 2009-04-29 : 09:20:01
|
| Yes, I thought that it was a bug in my ASP code, but after revising it I saw that it was not possible to get an infinite loop, and also, the query repeated itself even if the ASP page finished loading in the client without a problem.Also, the loop that runs this particular query also runs other queries, and none of the other queries are being repeated this way.That's what is weird about this. Any idea what may be causing it?Could it be a bug in the IIS?I have Windows Server 2003 |
 |
|
|
shinaco
Starting Member
6 Posts |
Posted - 2009-04-29 : 09:28:10
|
| I revised my code again and found something that slipped that could actually cause an infinite loop and run only that query, you were right.However, I have still a question here. Is it right that an accidental infinite loop that calls the SQL server makes the CPU go to 100%? Or is that signaling there is something wrong with my database design? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shinaco
Starting Member
6 Posts |
Posted - 2009-04-29 : 12:32:04
|
| The query returns only one row and the table has about 89,000 rows which isn't a lot, it is indexed by the criteria used in the query, however, the execution plan actually does a table scan. When run alone in the sql query analyzer, without anything less running the query takes a very small fraction of a second.The duration of the query when seen from the profiler, when the infinite loop is happening, is between 10 and 20 miliseconds, which means this query is probably run somewhere between 50 and 100 times per second when the infinite loop happens.Having that info, is it normal for the CPU to go to 100% utilization when this happens? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shinaco
Starting Member
6 Posts |
Posted - 2009-04-29 : 22:51:18
|
| The other queries were avoided by an IF sentence that I overlooked because the code inside was very long, and inside that IF was the .movenext call for the recordset that was run through in the cycle (that recordset is from a call to an external oracle server). The only query outside that IF clause was the one that kept repeating. I fixed the issue by moving the .movenext call after the "end if".That was the issue.Should I worry that this simple mistake made the CPU be utilized to 100%?I also would like to thank you for your prompt answers and your kind attention.Shinaco |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-04-30 : 12:36:13
|
| When I asked about other queries, I meant from a SQL Profiler perspective. If a query is repeatedly running, it may impact the performance of other queries due to locking, blocking, resource utilization, etc. So this one query could have made other queries run slower and all of that could have caused this issue. I wouldn't be worried about it. You found the resolution.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
|
SQL Server 2000 repeats the same query indifinitel