[cisco-voip] UCCX SQL queries spiking CPU on my SQL server

Wed Dec 2 12:33:20 EST 2009

Just as well, that list was all out-of-office replies anyways.

From: cisco-voip-bounces at puck.nether.net [mailto:cisco-voip-bounces at puck.nether.net] On Behalf Of Matthew Loraditch
Sent: Wednesday, December 02, 2009 11:23 AM
To: Wes Sisk
Cc: cisco-voip at puck.nether.net
Subject: Re: [cisco-voip] UCCX SQL queries spiking CPU on my SQL server

FWIW to others, that list is dead it seems:

Please note that all support is now provided over the forums. Partners can search the knowledge base and post questions to the Unified Contact Center Partner Community at https://www.myciscocommunity.com/community/partner/collaboration/contactcenter.

End-customers can post questions to the Contact Center User Group at https://www.myciscocommunity.com/community/technology/collaboration/usergroups. (You will need to request access to the Contact Center User Group.) Community forums' preferences can be set to provide email notifications of new posts.

This mailer list is going to be decommissioned on December 21st, 2009. If you need to contact the Product Management team directly, please use ipcc-express-pm at cisco.com<mailto:ipcc-express-pm at cisco.com>. Thank you for your cooperation in helping us serve you better.

Matthew Loraditch
1965 Greenspring Drive
Timonium, MD 21093
support at heliontechnologies.com<mailto:support at heliontechnologies.com>
(p) (410) 252-8830
(F) (443) 541-1593

Visit us at www.heliontechnologies.com<http://www.heliontechnologies.com>
Support Issue? Email support at heliontechnologies.com<mailto:support at heliontechnologies.com> for fast assistance!

From: Wes Sisk [mailto:wsisk at cisco.com]
Sent: Wednesday, December 02, 2009 10:45 AM
To: Matthew Loraditch
Cc: cisco-voip at puck.nether.net
Subject: Re: [cisco-voip] UCCX SQL queries spiking CPU on my SQL server

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?

Otherwise, I found a snippet about this.  Not my area of expertise so verbatim:
********
While creating a datasource from the Appadmin, the
subsystem will open the number of connections specified.

Even though all the connections are not required at the same time, it
will create all and keep it in a pool.

One connection will be used for polling the status of the database. If
the DB is down it will invalidate all the connections and create new
sets of connections once the external DB is available. When requesting
for a connection it will release the connection from the pool. After
using the connection, the connection needs to be released explicitly to
return it back to the pool.
*********

This leads to a few questions -
How many connections did you define? They appear to be opened if defined.  So, do you really need as many as you defined?
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?

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.

/Wes

On Wednesday, December 02, 2009 10:29:37 AM, Matthew Loraditch <MLoraditch at heliontechnologies.com><mailto:MLoraditch at heliontechnologies.com> wrote:
Wes,
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:
exec ..sp_tables N'',N'%',N'',NULL
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.

Any ideas? Since this isn't being caused by the Script as far as I can tell, would TAC be able to help?

[cid:image001.jpg at 01CA7343.3E85F550]
Matthew Loraditch
1965 Greenspring Drive
Timonium, MD 21093
support at heliontechnologies.com<mailto:support at heliontechnologies.com>
(p) (410) 252-8830
(F) (443) 541-1593

Visit us at www.heliontechnologies.com<http://www.heliontechnologies.com>
Support Issue? Email support at heliontechnologies.com<mailto:support at heliontechnologies.com> for fast assistance!

From: Wes Sisk [mailto:wsisk at cisco.com]
Sent: Wednesday, December 02, 2009 9:52 AM
To: Matthew Loraditch
Cc: cisco-voip at puck.nether.net<mailto:cisco-voip at puck.nether.net>
Subject: Re: [cisco-voip] UCCX SQL queries spiking CPU on my SQL server

interesting approach. SQL server has reporting and diagnostic abilities to report all of this.

a few pointers that may help:

sql profiler - basically a packet sniffer for sql.  sql profiler can track transactions, execution time, cpu cycles, and other useful diagnostic information.

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.

a few very handy SQL commands -
'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.

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.

'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.

HTH.
/Wes

On Wednesday, December 02, 2009 8:16:26 AM, Matthew Loraditch <MLoraditch at heliontechnologies.com><mailto:MLoraditch at heliontechnologies.com> wrote:

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?

Thanks!

Matthew Loraditch
1965 Greenspring Drive
Timonium, MD 21093
support at heliontechnologies.com<mailto:support at heliontechnologies.com>
(p) (410) 252-8830
(F) (443) 541-1593

Visit us at www.heliontechnologies.com<http://www.heliontechnologies.com>
Support Issue? Email support at heliontechnologies.com<mailto:support at heliontechnologies.com> for fast assistance!

________________________________

_______________________________________________

cisco-voip mailing list

cisco-voip at puck.nether.net<mailto:cisco-voip at puck.nether.net>

https://puck.nether.net/mailman/listinfo/cisco-voip

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20091202/a06c355d/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 33011 bytes
Desc: image001.jpg
URL: <https://puck.nether.net/pipermail/cisco-voip/attachments/20091202/a06c355d/attachment.jpg>