Problem of CPU Occupation with SQL XML Support | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem of CPU Occupation with SQL XML Support

Hi !
I’ve configured a virtual directory with the SQL XML Support in IIS and i’m currently using template files to query a sql server DB on a remote server. Everything works well in the way that I call a template file in the url and
the server send me back results in an xml form. The thing is that when the query is done,
the processes sqlserver.exe and inetinfo.exe keep running
on the server and I can stop them only by restarting the server. I don’t anderstand why the CPU is still use when the query are done. Does anyone got an idea why these processes don’t free the CPU when the
request is ended and the results have been sent back ? Thanks

sqlserver is a service and continues to run regardless of the load it is servicing.
What sort of cpu usage are they exhibiting after the resultset is returned?
Could the sql server be servicing other requests at that time?
Is this causing a specific problem?

In fact, the server can be requested by many people in the same time and there is no problem if they query the DB directly with SQL Server. The problem is when i send a request through http with my template file :
– first, It use the CPU (which is normal)
– but then it doesn’t free the CPU
And this is the same for each request i send though http (request formatted as Select.. from for xml raw as an example). The charge of the CPU increase as long as it can. And when the CPU is busy the only thing to do is to restart the server. Thanks for your help.
How about memory settings on SQL Server?
Are there any other applications sharing server resources?
Collect PERFMON(SYSMON) data from physical disk, memory, process, processor for the involved servers.
Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
<br />I made a mistake :it’s not the CPU which is busy but the memory which is not released after use. <br /><br />I hope that the data above will help : <br /><br />DBCC TRACEON (3604)<br />DBCC resource <br /><br /><br />RESOURCE:<br />———<br /><br />RESOURCE @0x00A12000<br />——————–<br />rdes = 603148096 *rdeshash = 0 rdescount = 8192<br />*prpssarray = 1119781016 rprocihash = 0x23C90030 rprocnhash = 0x23CB0030<br />rprocmemused = 2521 rflag1 = 16 rflag2 = 0<br />rprocnum = 96 rMSversion = 134218488 rpsytab = 603038008<br />m_pLangCache = 603038696 rservername = P-APPLIXR servicename = MSSQLSERVER<br />servinstname = P-APPLIXR ropen_objmsgs = 0 pPerfStats = 11004808<br />pResLock = 600354712 LoginMode = 2 rsaspid = 0<br />DefaultLogin = guest AuditLevel = 0 DefaultDomain = P-APPLIXR<br />MapChars[0] ‘_’ = MapChars[1] ‘$’ = MapChars[2] ‘#’ = <br />MaxConnections = 32767 MaxCPUs = 32 article_cache = 1119429536<br />pResLockFree = 603043904 MaxSubProcesses = 255 CurSubProcesses = 0<br />replmem = 0x42B90030 <br /><br />PERFMON @0x00A12038<br />——————-<br />pcputicks = 7013 pioticks = 6210 pidlticks = 1876329<br />pbs_rpck = 60821 pbs_rbyt = 149509482 pbs_conn = 183<br />pbs_errors = 0 pblk_rd = 7054 pblk_wr = 19095<br />pblk_errors = 0 <br /><br />DS_CONFIG @0x00A14060<br />———————<br />cconfsz = 8 cmajor = 8 cminor = 0<br />crevision = 15 cbootsource = 2 crecinterval = 0<br />ccatalogupdates = 0 cmbSrvMemMin = 0 cmbSrvMemMax = 752<br />cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0<br />cfillfactor = 0 ctapreten = 0 cwritestatus = 0<br />cfgpriorityboost = 0x0 cfgexposeadvparm = 0x1 cfglogintime = 20<br />cfgpss = 0 cfgpad = 4096 cfgxdes = 16<br />cfgaffinitymask = 0 cfgbuf = 4362 cfgdes = 0<br />cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1<br />cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250<br />cfg_max_text_repl_size = 65536 cfgupddate = 38129<br />cfgupdtime = 18846641 fRemoteAccess = 1 cfgbufminfree = 331<br />cnestedtriggers = 0x1 cfgworkingset = 0x0 cdeflang = 2<br />cidCfg = 0x3400d008 cidCfgOld = 0x3400d008 cfgCutoffYear = 2049<br />cfgLangNeutralFT = 1033 maxworkthreads = 255 minworkthreads = 32<br />minnetworkthreads = 32 threadtimeout = 15 connectsperthread = 0<br />cusroptions = 0 exchcostthreshold = 5 maxdop = 0<br />cchecksum = 274 <br />rWrkExtCache = Used Cache Entry = Iam (1:79), Extent (1:580<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />DBCC execution completed. If DBCC printed error messages, contact your system administrator.<br /><br />
How about the memory settings?…n-us/dnsql2k/html/sqlxml_optimperformance.asp for information. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks for your help. In fact, i think the problem is solved or at least it won’t perturb the system in future. The thing is that sqlserver put data in memory the time for it to process my request but it keep in memory for the case of a similar request. Thus, since there were no bound specified as a memory size for sql server, it was increasing as far as he could. Now that i specified a bound for the memory size with sql entreprise manager it’s ok. Thanks again

Yes if you put IIS and SQL server on the same box you need to limit the max memory SQL Server can use or it will not leave much for IIS.
Hi ! I’ve a likewise problem still with my connection between SQL Server and IIS.
Sometimes, when I ask for a query with many rows in the results, the inetinfo process
takes a lot of memory and use like 99% of CPU without releasing it.
Is there a way to limit the size taken by inetinfo ? Thanks, & for information. HTH Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks a lot. I think I’ll find what i’m looking for. Thanks again.