|
Hello,
My Question is related to SQL Server Memory Usage,
I have a VB.net application with MSDE.
When i run my application, the sqlserver.exe in Task Manager uses memory of about 20 MB,
Then when I View any Crystal Report created report, the memory goes on increasing as the report is loaded and if reaches about 150 to 200 MB, but even after closing the report and also closing the application, SQL Server does not release memory.
So when we again start the application, again memory goes on accumulating but is never released.
Memory is released only when we Stop and start the SQL Server or restart the PC.
Can anyone tell me how to make SQL Server Release Memory from my VB.net application.
Thanks in Advance.
|
|
|
|
|
Hello,
sqlservr.exe gives back never the memory except when there is a memory pressure, that's to say when the operating system is needing memory for itself or for another application. For example, SQL Server is releasing memory when too much paging is occuring.
When the operating system is short of free memory, sqlserv.exe frees RAM memory ( K. Delaney neede about 20 pages in her book (Inside Microsoft SQL Server 2005 : the storage engine ) to explain the
management of the memory by SQL Server.
You may limit the amount of memory used by a sqlservr.exe ( there is 1 sqlservr.exe for each started instance of SQL Server ) thru the options Min server memory and Max server memory ( right-click on an instance , properties, tab memory ). For my 3 instances, i have limited Max server memory to 500 MB, so i am sure that never a sqlservr.exe will use more than 500 MB ( in fact, it is rare that the 3 instances are running, so i am sure that, with 2 GB of RAM , i am sure that the OS and applications other than sqlservr will have at least 500 MB of free memory.
You can't manage and free memory from your VB.net application.
But a connection to a server or database is one of the heaviest operation and i prefer to limit the number of connections by opening the connection once ( at the beginning of the application ) and closing the connection when exiting the application ( don't forget to close the connection in the exceptions treatement )
|
|
|
|
|
Hi
While installing SQL Server 2008, it asks for Service account and account name, pls click: [^]
Here, what are the meanings of options: "NT AUTHORITY\SYSTEM", "NT AUTHORITY\NETWORK SERVICE" etc..?
Are these registry keys or something like that. Where can i find information about this stuff?
I don't even know which subject they are related and where i must start to search about these.
Ihanks...
|
|
|
|
|
A simple table:
create table temptable(userid int,username varchar(100),score int)
Values inserted:
insert into temptable values(1,'BOB',17)
insert into temptable values(1,'BOB',21)
Test:
select * from temptable where userid =1
Result:
1,BOB,17
1,BOB,21
Returns 2 rows. Fine.
The requirement: Find number of times BOB has scored above 20.
Expected result
1,BOB,1
My attempt:
select userid,username,count(*) from temptable where userid=1 group by userid,username
Here how do I specify a condition for the "Count()"? It just need to count score>20 for BOB.
I'm a rock bottom sql noob. Any help?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Try:
select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
|
|
|
|
|
ed5756 wrote: select userid,username,count(*) from temptable where userid=1 and score>20 group by userid
 imagine if BOB doesn't have any record that has score over 20. It won't return any result at all. May be I should have stressed that point. In this case, the result should be like :
1,BOB,0
So I'm clear with my question now?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
OK, you want to return a 0 if there are no scores over 20. That is trickier.
This will return the count of Bob's scores over 20:
Select userid,username,count(*) as [count] from temptable where userid=1 and score>20 group by userid
This union query will return that same result and also a dummy record with a 0 count:
Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1
One would think then that using the MAX function in a sub-select would get your desired result:
select userid,username,max(ct) as
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username
union
Select top 1 userid,username,0 as ct from temptable as b where userid=1)
group by userid,username
But I find that in T-SQL for some reason it throws an error. However, if I first define a view based on that union, then this works:
select userid,username,max(ct) from view_c as Over20 group by userid, username
|
|
|
|
|
|
Sorry, there was a typo in my last post. That query should have read
select userid,username,max(ct) as Over20
from
(Select userid,username,count(*) as ct from temptable as a where userid=1 and score>20 group by userid, username union Select top 1 userid,username,0 as ct from temptable as b where userid=1) group by userid,username
I have run the 2-step solution using a view on SQL Server and it works.
|
|
|
|
|
Yeah thanks. Also the one provided by Jorgen is also cool.
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Something like ?
select * from
(select userid,username from temptable where userid=1 group by userid,username)as a
inner join
(select userid, count as cnt from temptable where score>21
group by userid)as b on a.userid=b.userid
But this doesn't work too 
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
Got it almost!
select * from
(select userid,username from temptable where userid=1
group by userid,username)as a
left join (select userid, count(*)as cnt from temptable
where score>21 group by userid)as b on a.userid=b.userid
But this returns "NULL" instead of 0. How can I make it 0 ?
Can I use "CASE" on the count? Like:
CASE cnt when NULL then 0 END
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
How about:
SELECT DISTINCT userid
,username
,(SELECT Count(*) FROM temptable t2 WHERE t1.userid=t2.userid AND score > 20) as topscorecount
FROM temptable t1
WHERE userid = 1
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from template
where username ='Bob'and score > 20
group by userid,username
|
|
|
|
|
Check here[^]
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
select count(*),userid,username from temptable
where username ='Bob' and score > 20
group by userid,username
union
select 0,userid,username from temptable
where username ='Bob'and score < 20
group by userid,username
|
|
|
|
|
set nocount on
create table tempuser (userid int, username varchar(10))
create table tempscore(userid int, score int)
insert into tempuser (userid, username) values (1, 'Bob')
insert into tempscore (userid, score) values(1, 17)
insert into tempscore (userid, score) values(1, 21)
select u.userid, u.username,
coalesce(s.total, 0) as total
from tempuser u
left outer join
(
select userid, count(*) as total
from tempscore
where score > 20
group by userid
) s on s.userid = u.userid
drop table tempscore
drop table tempuser
|
|
|
|
|
Dear friend
I want to write a query that produced single row find the max form two table and also
query that delete all redundant data from table in sql 2005
Thanks and regards
Azad Yadav
|
|
|
|
|
azad yadav wrote: write a query that produced single row find the max form two table
you can get max for each table and then find max from two result . same as this code:
select max(twotable.fieldmatch) from
(
select max(field1) as fieldmatch from table1
UNION
select max(field1) as fieldmatch from table2
)twotable
azad yadav wrote: query that delete all redundant data from table in sql 2005
This is a general question , you must specify what you mean exactly.
|
|
|
|
|
one thing between two table should have delete cascading on master table .
then u can use
delete from mastertable where id = select top 1 id from (select max(id) as id from master a join detail b on a.id=b.id )as a
|
|
|
|
|
I created a table in scott schema and stored some unicode charaters through iSQL*PLus in internet explorer , I can check the data in this invironment correctly while I am checking that through Oracle SQL*Plus it is displaying as ?????? when I installed the oracle I select the required language package as well (Arabic), I can see my data only Interent explore (iSQL*Plus), I can not see the data in Forms , or in Reports
|
|
|
|
|
If you use unicode characters I recommend using nvarchar2 instead of varchar2, then you won't have conversion problems.
It still won't work in SQL plus though as it does not support unicode characters as far as I know.
"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon
|
|
|
|
|
Is there anyway that we can extract or backup data from a sql server table to anything, and later importing them?
Is there any popular tool or technique to do so?
|
|
|
|
|
you can generate sql script for you table structure and also data which exist in your table.
if you have sql server 2008 follow this instructions:
Open Sql Server Management Studio
Right Click on your database
go to : Tasks
then: generate scripts...
wizard will shown then
try this wizard you can find it very simple
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
It will take a day for at least to understand. So you can be free till I come back 