September 20, 2007 at 1:06 pm
My PC is running sql server at 20% cpu and i use a vpn to connect to other pc's in a network
the request data from excel to my pc, simple stuff really, i updated the pc to a much faster pc
this time i notice that my sqlserver.exe memory usage is going up to 500000k by the end of the day, 8 hours by the way i am tracking stocks in this data base
for some reason on this installation the queries from the rome PC's are getting really slow, the higher the memory usage the slower the request.
did not happen before
any help as to why this happens?
September 20, 2007 at 1:10 pm
i know its not the vpn because in my home network where i have the server a pc running excel is also running slow requesting data so i assume its sql
September 20, 2007 at 1:18 pm
this time on setup i set all users to dbo, i think previously i clicked a different button on setup and had logins with different perms on the data base, so there were just a database reader database writer
September 20, 2007 at 11:37 pm
How much memory do you have on your server?
What service pack of SQL 2005 do you have?
Have you set max and min server memory?
What else is running on the server?
Run perfmon over the period you're concerned about and monitor SQL:Memory Manager:Total Server Memory and SQL:Memory Manager:Target Server Memory. How do they behave?
How big is the db? How much activity?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2007 at 7:11 am
p.s. Use perfmon to see if it really is SQL Server. Task manager's memory reading is hard to understand.
Process(sqlsrvr.exe)/private bytes (What it has comitted)
Process(sqlsrvr.exe)/virtual bytes (virtual memory pool)
Process(sqlsrvr.exe)/working set (memory actually in RAM)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2007 at 8:48 am
How much memory do you have on your server? 2GB , by the end of the day is normally 1gb still free
What service pack of SQL 2005 do you have? studio express 9.00.3042
Have you set max and min server memory? not sure how to do this but i set the database to 500mb
What else is running on the server? excel 2003 and realtick stock data tracker
Run perfmon over the period you're concerned about and monitor SQL:Memory Manager:Total Server Memory and SQL:Memory Manager:Target Server Memory. How do they behave?
I dont think studio has perfmon on it
How big is the db? How much activity? the database each day is around 140mb
I send 75 blocks of data every 1.5 seconds
then i make excel query it from local and vpn pc's
September 21, 2007 at 8:49 am
is there a way of looking at memory usage with out perfmon
September 21, 2007 at 9:31 am
how do you make this excel query? are you sure that the excel process is closed after being used?
...and your only reply is slàinte mhath
September 21, 2007 at 10:11 am
rsRecordset.Open "select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)", gcnConnect
Sheet1.Range("A15") = rsRecordset.Fields(0).Value
Sheet1.Range("B15") = rsRecordset.Fields(1).Value
Sheet1.Range("C7") = rsRecordset.Fields(2).Value
Sheet1.Range("D7") = rsRecordset.Fields(3).Value
Sheet1.Range("E7") = rsRecordset.Fields(4).Value
Sheet1.Range("F7") = rsRecordset.Fields(5).Value
Sheet1.Range("K7") = rsRecordset.Fields(6).Value
Sheet1.Range("L7") = rsRecordset.Fields(7).Value
Sheet1.Range("M7") = rsRecordset.Fields(8).Value
Sheet1.Range("N7") = rsRecordset.Fields(9).Value
Sheet1.Range("A18") = rsRecordset.Fields(10).Value
Sheet1.Range("B18") = rsRecordset.Fields(11).Value
rsRecordset.Close
' Retrieve the data using ADO.
Set gcnConnect = New ADODB.Connection
Set rsRecordset = New ADODB.Recordset
Set rsRecordset2 = New ADODB.Recordset
Set rsRecordset3 = New ADODB.Recordset
Set rsRecordset4 = New ADODB.Recordset
Set rsRecordset5 = New ADODB.Recordset
Set rsRecordset6 = New ADODB.Recordset
gcnConnect.ConnectionString = sConnect
' Keep the connection open for as long as we're running
gcnConnect.Open
I have more than one query running
so i use a timer function to run several by opening and closing each record set
September 21, 2007 at 4:31 pm
I don't think studio has perfmon on it
Performance monitor is a windows app, not a sql tool. All the windows OSs since windows 2000 (at least) has it.
There are other ways, but perfmon is the easiest and the most accurate.
To set max server memory, which you should probably do, since it doesn't appear that you're talking about a dedicated server, right click on the server in object browser, select properties and go to the memory tab. I would suggest no more than 1 GB from the sound of the machine you're running on.
Which OS?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 24, 2007 at 4:22 am
why do you bother? SQL Server will take the memory it requires to operate and if you still have 1gb of free memory at the end of the day that's fine - free memory means it's not being used e.g. you're paying for something you're not using.
I doubt if sql server has anything to do with your problems and you've merely picked upon it as it's the easiest visible thing which changes. This, and other forums, are full of postings about memory changes when using sql server - unless you're using awe that's the way it's supposed to be. I've not used express but if you view the table sysperfinfo ( or sys.dm_os_performance_counters ) you'll find target and total memory which sql server uses. I think you're looking in the wrong place.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply