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
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I made a mistake :it’s not the CPU which is busy but the memory which is not released after use. I hope that the data above will help : DBCC TRACEON (3604) DBCC resource RESOURCE: ——— RESOURCE @0x00A12000 ——————– rdes = 603148096 *rdeshash = 0 rdescount = 8192 *prpssarray = 1119781016 rprocihash = 0x23C90030 rprocnhash = 0x23CB0030 rprocmemused = 2521 rflag1 = 16 rflag2 = 0 rprocnum = 96 rMSversion = 134218488 rpsytab = 603038008 m_pLangCache = 603038696 rservername = P-APPLIXR servicename = MSSQLSERVER servinstname = P-APPLIXR ropen_objmsgs = 0 pPerfStats = 11004808 pResLock = 600354712 LoginMode = 2 rsaspid = 0 DefaultLogin = guest AuditLevel = 0 DefaultDomain = P-APPLIXR MapChars[0] ‘_’ = MapChars[1] ‘$’ = MapChars[2] ‘#’ = MaxConnections = 32767 MaxCPUs = 32 article_cache = 1119429536 pResLockFree = 603043904 MaxSubProcesses = 255 CurSubProcesses = 0 replmem = 0x42B90030 PERFMON @0x00A12038 ——————- pcputicks = 7013 pioticks = 6210 pidlticks = 1876329 pbs_rpck = 60821 pbs_rbyt = 149509482 pbs_conn = 183 pbs_errors = 0 pblk_rd = 7054 pblk_wr = 19095 pblk_errors = 0 DS_CONFIG @0x00A14060 ——————— cconfsz = 8 cmajor = 8 cminor = 0 crevision = 15 cbootsource = 2 crecinterval = 0 ccatalogupdates = 0 cmbSrvMemMin = 0 cmbSrvMemMax = 752 cusrconnections = 0 cnetworkpcksize = 4096 ckbIndexMem = 0 cfillfactor = 0 ctapreten = 0 cwritestatus = 0 cfgpriorityboost = 0x0 cfgexposeadvparm = 0x1 cfglogintime = 20 cfgpss = 0 cfgpad = 4096 cfgxdes = 16 cfgaffinitymask = 0 cfgbuf = 4362 cfgdes = 0 cfglocks = 0 cfgquerytime = 600 cfgcursorthrsh = -1 cfgrmttimeout = 10 cfg_dtc_rpcs = 0 cclkrate = 31250 cfg_max_text_repl_size = 65536 cfgupddate = 38129 cfgupdtime = 18846641 fRemoteAccess = 1 cfgbufminfree = 331 cnestedtriggers = 0x1 cfgworkingset = 0x0 cdeflang = 2 cidCfg = 0x3400d008 cidCfgOld = 0x3400d008 cfgCutoffYear = 2049 cfgLangNeutralFT = 1033 maxworkthreads = 255 minworkthreads = 32 minnetworkthreads = 32 threadtimeout = 15 connectsperthread = 0 cusroptions = 0 exchcostthreshold = 5 maxdop = 0 cchecksum = 274 rWrkExtCache = Used Cache Entry = Iam (1:79), Extent (1:580<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DBCC execution completed. If DBCC printed error messages, contact your system administrator.

How about the memory settings? http://msdn.microsoft.com/library/d…n-us/dnsql2k/html/sqlxml_optimperformance.asp for information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
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

http://www.winnetmag.com/SQLServer/Article/ArticleID/8034/8034.html,
http://www.winnetmag.com/Web/Article/ArticleID/16467/Web_16467.html &
http://www.winnetmag.com/SQLServer/Article/ArticleID/5663/5663.html for information. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
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.

]]>