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

[Wes Sisk]

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> 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
> 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/89bf0a24/attachment.html>