<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><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]--></head><body bgcolor=white lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Wes,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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:#1F497D'>exec ..sp_tables N'',N'%',N'',NULL<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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:#1F497D'><o:p> </o:p></span></p><div><p class=MsoNormal><span style='color:#003366'><img width=225 height=105 id="Picture_x0020_1" src="cid:image001.jpg@01CA7339.E210C380" alt="cid:image001.jpg@01C95158.4441E1B0"></span><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'><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 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 href="http://www.heliontechnologies.com"><span style='color:black'>www.heliontechnologies.com</span></a> <br>Support Issue? Email <a href="mailto:support@heliontechnologies.com"><span style='color:black'>support@heliontechnologies.com</span></a> for fast assistance!<span style='color:#1F497D'><o:p></o:p></span></p></div><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext'> Wes Sisk [mailto:wsisk@cisco.com] <br><b>Sent:</b> Wednesday, December 02, 2009 9:52 AM<br><b>To:</b> Matthew Loraditch<br><b>Cc:</b> cisco-voip@puck.nether.net<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 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:10.0pt;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 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 href="http://www.heliontechnologies.com"><span style='color:black'>www.heliontechnologies.com</span></a> <br>Support Issue? Email <a 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 size=4 width="90%" align=center></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 href="mailto:cisco-voip@puck.nether.net">cisco-voip@puck.nether.net</a><o:p></o:p></pre><pre><a 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:12.0pt;font-family:"Times New Roman","serif"'><o:p> </o:p></span></p></div></body></html>