PHP, MSSQL_CLOSE, and memory usage

I've been connecting to a MSSQL server lately and doing some work and I've noticed the Mem usage for MSSQL server instance has increased and not went back down. My scripts create 1 mssql_connect() resource and they all use mssql_close() at the end. My scripts also run from my own linux box. Per php.net mssql_connect says the connection should close after the script executes like php/mysql, but I've always felt php/mysql with Zend optimization has handled that better and I know from terminal services with MS that closing sessions and connections has always been difficult on Window's boxes.

Any pointers when working with MSSQL and PHP?

I know in MySQL your server will never really ever "free" memory. On my servers I usually run 80% of the system memory for the MySQL server. Keeping all the tables in memory is a lot faster on queries than reading from the disk every time.

After a few hours of my server running my database will be up to 80% and it will stay there till I restart it or if it crashes for some reason.

I believe I have noticed the same thing as SpooF. I typically setup my.cnf to never use more than 50% of the installed RAM, so I will never see it over 50% of the system memory.

I restarted the server and it freed up a lot of memory, but it is accurate the MSSQL SRV will close the connection automatically when the PHP script finishes executing??

It probably has some sort of timeout mechanism. So if the connection isn't used for x seconds (mysql defaults to 100 I believe) it will kill the connection.

If you dig through the configuration settings you'll probably find something like remote timeout or just regular timeout.

Ok, I'll check for a timeout setting for db connection. However, I think most of the mem usage is coming from cached queries or just normal SQL server operations as it seems most of the jump in usage is more analogous to uptime than to concurrent connections. I've capped the total amount of memory MSSQL can use even though it may cause some performace issues, but I can't let it keep just growing in its consumption or at least until memory on our server is not an issue which is soon hopefully.

Windows is always a problem. I have one question for you which you should ask to yourself. Are the databases/tables optimized. I mean the queries they have, are they optimized?

like i want to say some of your queries were unoptimized and analyzing far more rows than necessary?

I think they use all stored procedures. I'm, however, connecting remotely with a php script running on my own server and I'm using my own queries. I guess I can look more at figuring out how to call the stored procedures from PHP. I also do need to check the db optimization to see if the creators designated a column as a foreign key. I use a range to get the primary key from a main table which that ID should be setup as the foreign key in all the related tables is what it looks like. I really don't know how indexes and foreign keys play out in MSSQL. I've always pretty much called everything by primary key in MySQL and if I can't I index or foreign key the fields I may need. So, yeah. I believe I'm analyzing far more rows than necessary. I'm working on learning what I can in these areas of MSSQL. Also, I really don't care how much memory I'm using when the script is running, but once it's done I'd wish/hope that it would free the memory and not get cached. I'm also needing to do reading on cached queries in MSSQL and their control.

Any figures to back that up?

@Devilwood. Are you not suffering from a case of premature optimization here? Are you running into any issues? Is the performance of the machine being affected? Are you performing sub queries ie populating data based on a query then requerying per row returned on the initial query? Stored Procs will consume more or less the same memory, they just supposedly take less time to execute. I think the best place to start is check if connections on the server are being closed (not in the code but the physical sql server).

I noted on Windows 2008 the server doesn't "clear" memory as we understand it. It keeps it allocated till it needs it for something also (allocated meaning marked as used or in use)

Quick search revealed http://www.microsoft.com/sqlserver/2005 ... river.aspx

Interesting how MS now has it's own driver

make life easy install ubuntu

Yeah of course because MS SQL can run on ubuntu right?

well ubuntu is just a distro of linux MySQL runs on windows linux and would not be surprised with mac too. i just have a vmware windows xp running xxamp and it works easy and fine.

The problem being he is using Microsoft SQL Server not mysql and if he is going to put it in a VM he is still running the SQL server on a windows distribution. I think you might have miss read MSSQL as MySQL. I used to do it often (mistake the two that is)

And the way I understand the question he doesn't have much control over the database server, he is making use of someone elses DB hence the reason he went with queries over stored procs. I might be wrong though, it happens occasionally

Rabid Dog, you are correct sir. I have no control over the Windows Server and SQL Server. I was just making sure I was handling memory properly on the server. I know Zend has done optimization to handle mysql_close() commands better, for example, closing the connection without the command after the script has ran.