<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Just because those queries are executing does not mean they are the
problem. Check the execution time associated with each execution. Are
those what are really concerning the sysadmin?<br>
<br>
Otherwise, I found a snippet about this. Not my area of expertise so
verbatim:<br>
********<br>
While creating a datasource from the Appadmin, the<br>
subsystem will open the number of connections specified.<br>
<br>
Even though all the connections are not required at the same time, it<br>
will create all and keep it in a pool. <br>
<br>
One connection will be used for polling the status of the database. If<br>
the DB is down it will invalidate all the connections and create new<br>
sets of connections once the external DB is available. When requesting<br>
for a connection it will release the connection from the pool. After<br>
using the connection, the connection needs to be released explicitly to<br>
return it back to the pool.<br>
*********<br>
<br>
This leads to a few questions -<br>
How many connections did you define? They appear to be opened if
defined. So, do you really need as many as you defined?<br>
Looks like one connection may be used for monitoring. In the pool
definition or data source definition are there any options to modify
monitoring rate?<br>
<br>
You might try TAC, but script development and db interaction is not
among their primary responsibilities. I would lean more toward
ask-icd-ivr-support[at]external.cisco.com.<br>
<br>
/Wes<br>
<br>
<br>
<br>
On Wednesday, December 02, 2009 10:29:37 AM, Matthew Loraditch
<a class="moz-txt-link-rfc2396E" href="mailto:MLoraditch@heliontechnologies.com"><MLoraditch@heliontechnologies.com></a> wrote:<br>
<blockquote
cite="mid:530C67FE62559C42857C78B962454E6203BDC25A3C@hermes.helion.local"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]-->
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
color:black;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;
font-weight:normal;
font-style:normal;
text-decoration:none none;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);">Wes,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);">I had
done some googling and found some of what you mention but that profiler
is a gold mine. What I am seeing is UCCX constantly executing this
query: <o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);">exec
..sp_tables N'',N'%',N'',NULL<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);">this
seems to be a system thing as that is a query to list tables in the DB
and it’s not in my script anywhere and I have no idea why UCCX would
need a list of tables.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);">Any
ideas? Since this isn’t being caused by the Script as far as I can
tell, would TAC be able to help?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span style="color: rgb(0, 51, 102);"><img
id="Picture_x0020_1" src="cid:part1.05040100.01080607@cisco.com"
alt="cid:image001.jpg@01C95158.4441E1B0" height="105" width="225"></span><span
style="font-size: 10pt; font-family: "Arial","sans-serif"; color: rgb(31, 73, 125);"><o:p></o:p></span></p>
<p class="MsoNormal"><b>Matthew Loraditch</b><br>
1965 Greenspring Drive<o:p></o:p></p>
<p class="MsoNormal">Timonium, MD 21093 <br>
<a moz-do-not-send="true" href="mailto:support@heliontechnologies.com"><span
style="color: black;">support@heliontechnologies.com</span></a><br>
(p) (410) 252-8830<br>
(F) (443) 541-1593<br>
<br>
Visit us at <a moz-do-not-send="true"
href="http://www.heliontechnologies.com"><span style="color: black;">www.heliontechnologies.com</span></a>
<br>
Support Issue? Email <a moz-do-not-send="true"
href="mailto:support@heliontechnologies.com"><span
style="color: black;">support@heliontechnologies.com</span></a> for
fast assistance!<span style="color: rgb(31, 73, 125);"><o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span style="color: rgb(31, 73, 125);"><o:p> </o:p></span></p>
<div>
<div
style="border-style: solid none none; border-color: rgb(181, 196, 223) -moz-use-text-color -moz-use-text-color; border-width: 1pt medium medium; padding: 3pt 0in 0in;">
<p class="MsoNormal"><b><span
style="font-size: 10pt; font-family: "Tahoma","sans-serif"; color: windowtext;">From:</span></b><span
style="font-size: 10pt; font-family: "Tahoma","sans-serif"; color: windowtext;">
Wes Sisk [<a class="moz-txt-link-freetext" href="mailto:wsisk@cisco.com">mailto:wsisk@cisco.com</a>] <br>
<b>Sent:</b> Wednesday, December 02, 2009 9:52 AM<br>
<b>To:</b> Matthew Loraditch<br>
<b>Cc:</b> <a class="moz-txt-link-abbreviated" href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</a><br>
<b>Subject:</b> Re: [cisco-voip] UCCX SQL queries spiking CPU on my
SQL server<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">interesting approach. SQL server has reporting
and diagnostic abilities to report all of this.<br>
<br>
a few pointers that may help:<br>
<br>
sql profiler - basically a packet sniffer for sql. sql profiler can
track transactions, execution time, cpu cycles, and other useful
diagnostic information.<br>
<br>
sql query analyzer - offers an option to show 'execution plan'. SQL
statements ideally use indexes to perform selects, updates, etc. If
you don't use an index you end up performing a 'table scan'. A 'table
scan' loads every row one at a time to compare criteria. This is very
suboptimal. Use of an index is heavily dependent on your the clauses in
your SQL statements. Even a reordering of the clauses can can
significant performance impact. take the queries that you execute from
UCCX (capture using profiler above) and run them through sql query
analyzer with the option to show execution plan. If you're accessing a
large table and not using an index then you will need to reorder your
statement or create new index.<br>
<br>
a few very handy SQL commands -<br>
'sp_who2' - show active connections to SQL server, spid, active
transactions, and transaction execution time. This is the easiest way
to identify a resource hog from the server side. <br>
<br>
Keep in mind that UCCX potential to "keep some sort of constant open"
is heavily dependent on the script that you authored to perform the
database access.<br>
<br>
'sp_lock' - show active locks in the database. With multiple
applications accessing the same database you increase the potential for
concurrent locks and the ever feared deadlock scenario. If you are
making SQL statements that cause grabbing and holding locks then you
are going to get into serious contention with other applications
attempting to access the same information.<br>
<br>
HTH.<br>
/Wes<br>
<br>
On Wednesday, December 02, 2009 8:16:26 AM, Matthew Loraditch <a
moz-do-not-send="true" href="mailto:MLoraditch@heliontechnologies.com"><MLoraditch@heliontechnologies.com></a>
wrote:<br>
<br>
<o:p></o:p></p>
<p class="MsoNormal">I have one script that does a pretty simple sql
query if a customer enters a ticket number to find the assigned tech,
yet somehow my SQL Admin is reporting the account I have doing that is
chewing up the CPU on the SQL server. We have other Apps that pull from
the same DB every 5 seconds yet they aren’t causing the same Issue.
Does the DB Subsystem in UCCX keep some sort of constant open on a SQL
db?<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal">Thanks!<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<p class="MsoNormal"><span
style="font-size: 10pt; font-family: "Arial","sans-serif";"> </span><o:p></o:p></p>
<p class="MsoNormal"><b>Matthew Loraditch</b><br>
1965 Greenspring Drive<o:p></o:p></p>
<p class="MsoNormal">Timonium, MD 21093 <br>
<a moz-do-not-send="true" href="mailto:support@heliontechnologies.com"><span
style="color: black;">support@heliontechnologies.com</span></a><br>
(p) (410) 252-8830<br>
(F) (443) 541-1593<br>
<br>
Visit us at <a moz-do-not-send="true"
href="http://www.heliontechnologies.com"><span style="color: black;">www.heliontechnologies.com</span></a>
<br>
Support Issue? Email <a moz-do-not-send="true"
href="mailto:support@heliontechnologies.com"><span
style="color: black;">support@heliontechnologies.com</span></a> for
fast assistance!<o:p></o:p></p>
<p class="MsoNormal"> <o:p></o:p></p>
<pre><o:p> </o:p></pre>
<pre style="text-align: center;"><hr align="center" size="4"
width="90%"></pre>
<pre><o:p> </o:p></pre>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>cisco-voip mailing list<o:p></o:p></pre>
<pre><a moz-do-not-send="true"
href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</a><o:p></o:p></pre>
<pre><a moz-do-not-send="true"
href="https://puck.nether.net/mailman/listinfo/cisco-voip">https://puck.nether.net/mailman/listinfo/cisco-voip</a><o:p></o:p></pre>
<pre> <o:p></o:p></pre>
<p class="MsoNormal"><span
style="font-size: 12pt; font-family: "Times New Roman","serif";"><o:p> </o:p></span></p>
</div>
</blockquote>
<br>
</body>
</html>