Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My sqlserver.exe cpu usage %100 !

Status
Not open for further replies.
Braderd

Braderd

Programmer
Mar 2, 2008
20
TR
Hi,

I have a dedicated server. I'm using SQL Server 2005 Express Edition. sqlserver.exe cpu usage reach %100 when my below page loads.




<%
Response.CodePage = 1254
Response.CharSet = "windows-1254"%>


<% Response.Buffer = True %>


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1254">

<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Dow</title>
<meta name="keywords" content="dop3">
<link rel="stylesheet" type="text/css" href="link2.css">
<link rel="stylesheet" type="text/css" href="link.css">
<style type="text/css">
<!--
.style1 {
color: #FF0000;
font-style: italic;
font-size: 16px;
}
.style2 {
border-collapse: collapse;
}
.style3 {
text-align: center;
}
-->
</style>
</head>


<body>

<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td width="100%" bgcolor="#387CA6"><b>
<font face="Arial" size="2" color="#FFFFFF">D</font></b></td>
</tr>
</table>
<div align="center">
<center>
<br>
<table border="0" width="100%" id="table3">
<tr>
<td>
<p align="center"></td>
</tr>
</table>
<table border="0" width="90%" id="table5" height="91" bgcolor="#FFFFFF">
<tr>
<td>
<p align="center">
</td>
</tr>
</table>
<table border="0" width="100%" id="table34">
<tr>
<td>
<p align="center"><b><i><font color="#FF0000" face="Arial" size="2">
KATEGOR?LER</font></i></b></td>
</tr>
</table>
</center>
</div>
<table width="169" border="0" align="right" height="414" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0" cellspacing="0">
<tr>
<td height="410" width="161" valign="top">
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="101%" id="AutoNumber11" height="197">
<tr>
<td width="100%" height="197" valign="top" style="border-bottom-style: none; border-bottom-width: medium">
<table cellSpacing="0" cellPadding="5" width="100%" bgColor="#ffffff" border="0" style="border-collapse: collapse" bordercolor="#111111" height="1">
<tr>
<td width="260" bgColor="#f5f5f5" height="35" style="border-style:solid; border-width:1; ">
<table cellSpacing="5" cellPadding="0" width="100%" border="0" height="358" bgcolor="#F5F5F5" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<td align="middle" width="220" bgColor="#387CA6" height="19" style="border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: none; border-bottom-width: medium">
<font color="#FFFFFF">
<a href=" <font color="#FFFFFF">Yeni Eklenenler</font></a></font></td>
</tr>
<tr>
<td width="100%" height="76">
<font face="Arial" color="#124A82" size="2"><font class="pn2">
<span style="color: #36515F">
<%
kategori = ""
altKategori = ""
altKategorib = ""
Set Conn = Server.CreateObject("Adodb.Connection")
Conn.Open strDb

Set Rs = Server.CreateObject("Adodb.REcordset")
if altKategori = "" and altKategorib = "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' order by active mydate desc"
elseif altKategori <> "" and altKategorib = "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' and altKategori = '"& altkategori &"' order by active mydate desc"
elseif altKategori <> "" and altKategorib <> "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' and altKategori = '"& altkategori &"' and altKategorib = '"& altKategorib &"' order by active mydate desc"
end if
rSQL = "Select * from all_pages Where active=1 order by mydate desc"
Rs.Open rSQL, Conn, 1, 3

i = 1
%> <font face="Arial" size="1"><%
Do while i < 11 and Not Rs.Eof
%> </font></span></font><b><font face="Arial" color="#003366" size="2"></font><font face="Arial" color="#387CA6" size="2"><span style="color: #36515F">&nbsp;</span></font></b><font face="Arial" color="#36515f" size="2"><span style="color: #36515F">
<font face="Arial" size="1">
<a href="../program.asp?id=<%=rs("id")%>">» <%=rs("title")%> </a>
<br>
<%
i = i + 1
Rs.MoveNExt
Loop
%> <%
conn.Close
Set conn = Nothing
Set rs = Nothing%> </font></span></font></font>
<font face="Times New Roman" color="#FF0000" size="2">
<p><b><a href=" <font color="#FF0000">Eram</font></a></b><font face="Arial" color="#36515f" size="2"><br>
&nbsp;</p>
<table border="0" width="100%" id="table8">
<tr>
<td>
<p align="center">
<br>
<br>
</td>
</tr>
</table>
</font></td>
</tr>
<tr>
<td align="middle" width="100%" bgColor="#387ca6" height="19">
<font face="Times New Roman" color="#FFFFFF">bcbc Menü</font></td>
</tr>
<tr>
<td width="100%" bgColor="#f5f5f5" height="134">
<font face="Arial" color="#124A82" size="2"><font class="pn2">
<br>
&nbsp;</font></font><br>
&nbsp;</td>
</tr>
</table>
</td>
</tr>
</table>
<p align="center"><br>
</td>
</tr>
</table>
</td>
</tr>
</table>
<table width="182" border="1" align="left" height="182" style="border-collapse: collapse" bordercolor="#111111" cellpadding="4" cellspacing="0">
<tr>
<td width="172" bgColor="#f5f5f5" height="140" style="border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: none; border-top-width: medium; border-bottom-style: none; border-bottom-width: medium">
<table cellSpacing="5" cellPadding="0" width="100%" border="0" height="40" bgcolor="#F5F5F5" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<td align="middle" width="320" bgColor="#387CA6" height="17" style="border-left-style: solid; border-left-width: 1; border-right-style: solid; border-right-width: 1; border-top-style: solid; border-top-width: 1; border-bottom-style: none; border-bottom-width: medium">
<font color="#FFFFFF">En</font></td>
</tr>
<tr>
<td width="100%" height="17"><span style="color: #36515F">
<font face="Arial" color="#36515f" size="2"><font class="pn2">&nbsp;</font></font><font class="pn2"><b><font face="Verdana" color="#36515f" size="1">
T
<a href=" face="Arial" color="#36515f" size="2"><br>
<font class="pn2"><br>
&nbsp;</font></font></span><font face="Verdana" color="#36515f" size="1"><b> ! bcbc
<a href=" eniz ?çin</a> bölmi ?<br>
<br>
</b></font><font face="Arial" color="#36515f" size="2"><br>
</font><font face="Arial" color="#36515f" size="1">
<table border="0" width="100%" id="table4" bgcolor="#000099">
<tr>
<td>
<p align="center"><font color="#FFFFFF" size="2"> <br>
</p></font></td>
</tr>
<tr>
<td align="middle" width="100%" bgColor="#387ca6" style="border-bottom-style: none; border-bottom-width: medium">
<font face="Arial" size="2" color="#FFFFFF">TOP 10</font></td>
</tr>
<tr>
<td width="100%" bgColor="#f5f5f5" style="border-style: none; border-width: medium">
<table cellSpacing="5" cellPadding="0" width="100%" border="1" height="1" bgcolor="#F5F5F5" style="border-collapse: collapse" bordercolor="#111111">
<tr>
<td width="100%" height="1">
<font face="Arial" color="#36515f" size="2"><font class="pn2">
<font face="Arial" color="#124A82" size="1">
<%
kategori = ""
altKategori = ""
altKategorib = ""
Set Conn = Server.CreateObject("Adodb.Connection")
Conn.Open strDb

Set Rs = Server.CreateObject("Adodb.REcordset")
if altKategori = "" and altKategorib = "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' order by hit desc"
elseif altKategori <> "" and altKategorib = "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' and altKategori = '"& altkategori &"' order by hit desc"
elseif altKategori <> "" and altKategorib <> "" then
rSQL = "Select * from all_pages where active<>0 and kategori = '"& kategori &"' and altKategori = '"& altkategori &"' and altKategorib = '"& altKategorib &"' order by hit desc"
end if
rSQL = "Select * from all_pages order by hit desc"
Rs.Open rSQL, Conn, 1, 3

i = 1
%> <%
Do while i < 11 and Not Rs.Eof
%> <%=i%>-&nbsp;<a href="../program.asp?id=<%=rs("id")%>"> <%=rs("title")%></a><br>
<%
i = i + 1
Rs.MoveNExt
Loop
%> <%
conn.Close
Set conn = Nothing
Set rs = Nothing
%> </font><br>
<font face="Arial" color="#36515f" size="2">
<a class="pn2" style="COLOR: #36515f" href=" <br>
</a></font></font></font></td>
</tr>
</table>
</td>
</tr>
<tr>
<td width="100%" height="17" style="border-top-style: none; border-top-width: medium">&nbsp;
</td>
</tr>
</table>
</td>
</tr>
</table>
<table width="595" border="0" align="center" height="327" style="border-width: 0">
<%
Set conn = CreateObject("ADODB.Connection")
conn.Open strDB
Dim rs
dim bir,iki
Set rs = CreateObject("ADODB.Recordset")
rSQL = "SELECT * FROM category WHERE m_id = 0 order by name asc"
Rs.Open rSQL, Conn, 1, 3
%>
<%
While (rs.EOF=false)
%>
<tr>
<td width="50%" align="center" valign="top">
<img src="images/index_html_smartbutton1.gif" namo_npi=":EmbededNPI3" border="0" align="center" width="19" height="16">
<b><a href=" color="#124A82"><%=rs("name")%></font></a></b>
<% bir=rs("id") %>
</td>
<% rs.movenext %>
<% if rs.EOF=false then %>
<td width="50%" align="center" valign="top">
<img src="images/index_html_smartbutton1.gif" namo_npi=":EmbededNPI3" border="0" align="center" width="16" height="16">
<b><a href=" color="#124A82"><%=rs("name")%></font></a></b>
<% iki=rs("id") %>
<% end if %>
</td>
</tr>
<%

%>
<tr>
<td width="50%" valign="top" >
<% if bir <> "" then %>
<% Dim rs1
Set rs1 = CreateObject("ADODB.Recordset")
rSQL = "SELECT * FROM category WHERE m_id = " & cstr(bir)
Rs1.Open rSQL, Conn, 1, 3
%> <% While (rs1.EOF=false)
rs1_sayac =rs1_sayac+1%>

<font face="arial, helvetica" color="#cc0000" size="2">
<%
id1=rs1("id")
if alt_kategori(id1)=0 then
%>
<a href="<% else %>
<a href="<%end if%>
<% if rs1_sayac<rs1.recordcount then%>
<font face="arial, helvetica" size="2">,</font>
<% end if %>
<%
rs1.movenext
wend %>
<%
rs1_sayac=0
bir = ""
%>
<% end if %>
</td>
<td width="50%" valign="top" >
<% if iki <> "" then %>
<% Dim rs2
Set rs2 = CreateObject("ADODB.Recordset")
rSQL = "SELECT * FROM category WHERE m_id = " & cstr(iki)
Rs2.Open rSQL, Conn, 1, 3
%>
<% While (rs2.EOF=false)
rs2_sayac =rs2_sayac+1%>
<font face="arial, helvetica" color="#cc0000" size="2">
<%
id2=rs2("id")
if alt_kategori(id2)=0 then
%>
<a href="<% else %>
<a href="<%end if%>

<% if rs2_sayac<rs2.recordcount then%>
</font><font face="arial, helvetica" size="2">,</font>
<% end if %>
<%
rs2.movenext
wend %>
<%
rs2_sayac=0
iki = ""
%>
<% end if %>
</td>
</tr>
<tr>
<td valign="top">&nbsp;</td>
<td valign="top">&nbsp;</td>
</tr>
<% rs.MoveNext
wend %>
</table>

<%
function alt_kategori(str)
Dim rsx
Dim tempx
tempx=0
Set rsx = CreateObject("ADODB.Recordset")
rSQL = "SELECT * FROM category WHERE m_id = " & cstr(str)
Rsx.Open rSQL, Conn, 1, 3
While (rsx.EOF=false)
tempx=1
rsx.movenext
wend
set rsx=nothing
alt_kategori=tempx
end function
%>

<table border="0" cellpadding="0" bordercolor="#111111" width="55%" id="AutoNumber12" height="398" align="center" class="style2">
<caption><br>
</caption>
<td width="52%" height="186"><b><span class="style1"><strong><br>
<br>
Editömi</strong></span></b><font size="2"><b><a href=" <br>
MoR</a></b></font><br>
<br>
<font size="2">Fir?r.</font><p>
<font face="Arial" size="2"><br>
&nbsp;</font></td>
<td width="48%" height="186">
<p align="center"><br>
<img border="0" src=" width="202" height="164"></td>
</tr>
<caption><br>
</caption>
<tr>
<td width="250%" valign="top" colspan="2" height="70">
<table border="0" width="100%" id="table2" height="21">
<tr>
<td>
<p align="center"></td>
</tr>
</table>
<p align="center"><br>
<font face="Arial" color="#36515f" size="2"><a style="color: #36515f">
<img src="images/index_html_smartbutton1.gif" namo_npi=":EmbededNPI12" border="0" align="top"></a></font><font face="Arial" color="#CC3399" size="2"><a style="color: #36515f">&nbsp;</a></font><font face="Arial" color="#0000FF" size="2"><u><a href=" color="#0000FF">Yeni
Eklr</font></a></u></font><br>
<br>
&nbsp;</p>
<table border="2" width="100%" id="table10">
<tr>
<td>
<p align="center"><b>
<a target="_blank" href=" PREMIUM
<br>
SANDI !</a></b></td>
</tr>
</table>
<p align="center"></td>
</tr>
</table>
<p align="center"></p>
<p></p>
<div align="center">
</div>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber9">
<tr>
<td width="100%">
<p align="center"><font face="Arial" size="-2">
2001 - 2?d?r.</font></td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber8">
<tr>
<td width="100%">
<p align="center"><b><font size="2">Webmaster</font></b></td>
</tr>
<tr>
<td width="100%" class="style3">

</td>
</tr>
</table>

</body>

</html>






What can i do? Which code is garble? Thank you.
 
Sort by date Sort by votes
I had something similar happen to me one time. In my case, I was trying to insert records in a table without creating duplicates, so I was checking to see if the data existed first. I was trying to insert about 50 records every 10 seconds, but it was taking more than a second to check for each record to see if it existed.

Why is this relevant?

Once I identified the problem, it was easy to resolve. You see, why query was not properly indexed. Once I added an index to the query, the CPU utilization went from 100% down to 2%.

Do you have an index on the column myDate in the all_pages table? Also, how many records are in that table? It appears as though you only want the first 10 records.

Code: 
Set Rs = Server.CreateObject("Adodb.REcordset")
if altKategori = "" and altKategorib = "" then
    rSQL = "...."
elseif altKategori <> "" and altKategorib = "" then
    rSQL = "...."
elseif altKategori <> "" and altKategorib <> "" then
    rSQL = "...."
end if
rSQL = "Select * from all_pages Where active=1 order by mydate desc"
    Rs.Open rSQL, Conn, 1, 3

i = 1
%> <font face="Arial" size="1"><%
[!]Do while i < 11 and Not Rs.Eof[/!]
  %>.....<%
i = i + 1
Rs.MoveNExt
Loop
%>

It appears as though you tried adding code that would reduce the number of records returned, but the rSQL is always overwritten by the last one.

If you only want the first 10 rows, then you should use the TOP predicate. Like this...

[tt][blue]Select [!]Top 10[/!] * from all_pages Where active=1 order by mydate desc[/blue][/tt]

Then, later you have....

Select * from all_pages order by hit desc

Do you have an index on the hit column of the all_pages table?

Last thing, I noticed that you are using Select [!]*[/!] all over the place. If the tables contain columns that you do not need for this particular page, then you should list the columns instead of selecting them all. By using *, you are returning all columns which is wasteful from a memory and a performance perspective.

Now, understand that I just picked a couple of example from your code. And it's entirely possible that what I have suggested will not make any difference at all. I encourage you to find the performance problem so that you have something reasonable to work with.

 
Upvote 0 Downvote
Thank you for your reply.

I ve 6000 records in the all_pages table.

What do you mean by index on the hit column or mydate ?
 
Upvote 0 Downvote
In query analyzer, run this (and post the output here).

[tt][blue]sp_helpindex 'all_pages'[/blue][/tt]
 
Upvote 0 Downvote
I said to run that in Query Analyzer, but since you are using SQL Express, you really need to run it in SQL Server Management Studio instead. Sorry for the confusion.
 
Upvote 0 Downvote
After testing, I realized that problem is below codes. I use these codes in 2 different part of the page. When I remove one of them everything returns to normal. I think that while and loop code has a problem to fix. What can i do? Thank you.

i = 1
%> <font face="Arial" size="1"><%
Do while i < 11 and Not Rs.Eof
%> </font></span></font><b><font face="Arial" color="#003366" size="2"></font><font face="Arial" color="#387CA6" size="2"><span style="color: #36515F">&nbsp;</span></font></b><font face="Arial" color="#36515f" size="2"><span style="color: #36515F">
<font face="Arial" size="1">
<a href="../program.asp?id=<%=rs("id")%>">» <%=rs("title")%> </a>
<br>
<%
i = i + 1
Rs.MoveNExt
Loop
%>
 
Upvote 0 Downvote
What can i use instead of "do while" code or suggestion? Thank you.
 
Upvote 0 Downvote
Did you even read and try Georges suggestion about the select top 10 ... .

I guess not.

Christiaan Baes
Belgium

My Blog
 
Upvote 0 Downvote
Which code is garble?
Click to expand...

Pretty much all of it.

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson

Finally, <. is a good thing!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Auguy
  • Locked
  • Question
Delete Trigger Usage and Options 1
Replies
5
Views
184
Auguy
Auguy
WomanPro
  • Locked
  • Question
How to display the results of my query delimited with pipes?
Replies
9
Views
179
WomanPro
WomanPro
Cpreston
  • Locked
  • Question
How to get rid of { fn } in my View code
Replies
5
Views
173
Andrzejek
Andrzejek
bmacbmac
  • Locked
  • Question
SQL 'For json' query for over 100,000 records - truncating results
Replies
2
Views
347
bmacbmac
bmacbmac
EM1107
  • Locked
  • Question
Help finding a way to show my information on one row 3
Replies
11
Views
85
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top