0

I have a .NET 1.1 application running on Windows XP and with SQL Server 2000 Service Pack 3. The application uses Enterprise Library Data & Exception Handling dlls.

When I submit a form which calls a few stored procs & finally execute an INSERT into a table, the sqlsrvr.exe process keeps on increasing up to 300-500 MB. The form takes more than 10 mins to execute.

The same page in normal condition, takes not more than 1 min to execute.

What could be happening here, and how could it be fixed?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
Suraj
  • 89
  • 1
  • 12

2 Answers2

6

The Sql Server process consuming that amount of memory is normal. SQL Server will consume a large amount of memory, but will give it back under OS memory pressure (based upon the SQL server configured min and max memory settings)

If your query is slow, look to your SQL queries and/or missing indexes.

The symptom you describe sounds like it may be caused by parameter sniffing. Ensure your statistics and indexes are up to date, and perhaps post your query.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Thanks for your reply @Mitch, well it's difficult for me to post the query since i don't know exactly which one is causing the process to consume so much memory. since there are quite a number of stored procs being executed when the page submits. do you think this can be an issue with the Enterprise Library dlls also? – Suraj Aug 22 '11 at 14:07
  • Suraj, please see: http://stackoverflow.com/questions/257906/how-can-i-log-and-find-the-most-expensive-queries/257944#257944 – Mitch Wheat Aug 22 '11 at 14:10
  • Well it did not fix the problem yet but i found which stored proc is taking long to execute. thanks @Mitch – Suraj Aug 23 '11 at 12:32
0

the sqlsrvr.exe process keeps on increasing up to 300-500MB.

Ok, where is the hugh here? I have a 16gb sql server ;)

SQL Server, UNLESS TOLD OTHERWISE IN THE CNOFIGURATION, will cache as much as it can. Every page (8kb) it reads stays in memor. Discs are slow. ram is fast. It asumes it is a server and thus can use the memory.

And the form takes more than 10mins to execute.

Hm, lets see.

  • Crappy programming (missing indices`?)
  • Not enough Memory ;) Yes, SQL Server takes you say up to 500mb - is that a VM with 128Mb to start with or what?
  • Unsuitable disc subsystem. SQL Servers are VERY interesting in DISC IOPS - not mb/s but IOPS. I run my server with 10 discs now and it wants more.

I am quite strongly pointuing into either 1 (indices missing) or 2 (crappy hardware). Dont take it personal, but you whole setup soudns really outdated and you dont really yound like you are on top of things, so this is just a shot into the wild based no past experience.

The most brutal I had in this area was a person with zero indices and a web page that executed like 30 queries all resulting in table scans and overlaoding the poor normal cheap disc so much the computer got toally unresponsive for half an hour.

To start:

  • Check performance coutners, mostly disc based. This gives you a hint.
  • Make sure you proper indices. Valdiate that by checking the queries executing with the profiler. It is possible something just triggers significant inefficient operations thanks to a missing index.
  • If that is not the case, check waits statistics - locking may be the culprit, too.

It is VERY hard to say ANYTHING except - well - that you dont give nough information.

I have seen statements like this:

When I submit a form which calls a few stored procs & finally execute an INSERT into a table

explode into tons of inefficient operations in 12 layers of cascaded stored procedures.

That said, if it is hardware - upgrade to a SSD. A 120gb SSD is VERY cheap these days and EXTREMELY fast. I have Velociraptors 3 now for some oeprations and tehy do up to 60.000 IPS - while a higher end disc struggles with 400.

TomTom
  • 61,059
  • 10
  • 88
  • 148