how to free the memory occupied by "blob" in MS sql server

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E:\ folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:\Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

When you delete from SQL server the memory will not be removed until the database is truncated. If you want to store data temporarily create a temporary table with a prefix of #

CREATE TABLE #TEMPTABLE|||

i want to remove one record from the table, and i did it by using "DELETE" statement with WHERE clause,

now i want to free memory which was occupied by recently deleted record.

what i know about TRUNCATE statement is that it deletes the whole table and we cannot use WHERE clause with TRUNCATE.

and i don't want to create temporary table.

so how can i use truncate?.

thanks for your suggestion

|||

Hi,

This problem make me concentrate to it ! I have the same problem, please read this article :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=947545&SiteID=1

Althought I use temporary table, but, it does not free memory when I delete temp table, I do not know why ! For someone, they said that : "In AS, it free memory not well and Microsoft company does not anonounce this problem in a clear way !".

Can you tell me more,

Regards,

Tran Quang Phuong.

how to free the memory occupied by "blob" in MS sql server

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E:\ folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:\Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

When you delete from SQL server the memory will not be removed until the database is truncated. If you want to store data temporarily create a temporary table with a prefix of #

CREATE TABLE #TEMPTABLE
|||

i want to remove one record from the table, and i did it by using "DELETE" statement with WHERE clause,

now i want to free memory which was occupied by recently deleted record.

what i know about TRUNCATE statement is that it deletes the whole table and we cannot use WHERE clause with TRUNCATE.

and i don't want to create temporary table.

so how can i use truncate?.

thanks for your suggestion

|||

Hi,

This problem make me concentrate to it ! I have the same problem, please read this article :

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=947545&SiteID=1

Althought I use temporary table, but, it does not free memory when I delete temp table, I do not know why ! For someone, they said that : "In AS, it free memory not well and Microsoft company does not anonounce this problem in a clear way !".

Can you tell me more,

Regards,

Tran Quang Phuong.

how to free memory used by prior query statement within a batch by TSQL?

Just Like these:

-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)

/*
can I do something here to free res used by prior statement?
*/

select * from someOtherTable
--batch end

The Sqls above are written in a procedure to automating test for some select querys.What about this:

select ...
DBCC DROPCLEANBUFFERS
select ...|||Won't that force a recompile of everything?

Gotta look that up...

btw...SQL Server will grab as much memory is available, and will only release it if it's not using it and something else needs it...

It's not very sociable...|||thanks for help, but it seemed not work as I hoped.

Sqlserver used 20M memory before I run the select query;
Sqlserver used 123M memory after I run the select query;
Sqlserver still used 123M memory after I run 'DBCC DROPCLEANBUFFERS', but I want memory used by Sqlserver not larger than 20M;

I don't know exactly how memory useage affect the performance of next query's execution, so I write down my primal Intention:

select... -- query A

/* do something here to make query B to be executed just as query A was not executed before( or minish query A's affection). */

select... -- query B

could I make it?|||here is my test plan:

there are query ABC..., and insert all querys into a table called querytbl;
open a cursor for all records from querytbl;
fetch next query from cursor;
while @.@.fetchstatus = 0
begin
exec query for 3 times and calculate average time spending;
fetch next query from cursor;
end
...

Is there any better test plan?(just test time spending)

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
Thanks
There is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks
|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> On Sybase ASE, there is a command that will force the table and all its
>> contents into RAM, so that it can be queried faster. Is there such a
>> feature on SQL Server 2000?
>> Thanks
>

How to force a table into memory.

Hello,
On Sybase ASE, there is a command that will force the table and all its
contents into RAM, so that it can be queried faster. Is there such a
feature on SQL Server 2000?
ThanksThere is no command that will force table data into buffer cache. However,
you can mark a table as pinned so that SQL Server will keep table data in
buffer cache once read into memory. See DBCC PINTABLE in the Books Online
for details.
Note that it is very rare that this option will improve performance.
Microsoft SQL Server's buffer management algorithm will automatically keep
frequently used data in memory and makes the best use of available memory
resources.
Hope this helps.
Dan Guzman
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||You could run a query like this:
SELECT COUNT(*) FROM MyTable (index=0)
If your SQL Server has enough memory, then this will cause the entire
table to be loaded into SQL Server's data buffer. If there is no memory
pressure, it will stay in memory.
There is an option called DBCC PINTABLE, but I would definitely advise
against it. It is best to let SQL Server manage the available memory.
Gert-Jan
Frank Rizzo wrote:
> Hello,
> On Sybase ASE, there is a command that will force the table and all its
> contents into RAM, so that it can be queried faster. Is there such a
> feature on SQL Server 2000?
> Thanks|||Just want to add that the act of pinning a table will not actually place the
contents into memory. It just keeps them there after you access them. You
would have to do a select to get the data into cache. But I will also
emphasize what Dan stated in that this usually is not the right thing to do.
And this option is going away in SQL2005.
Andrew J. Kelly SQL MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23JhFDXF8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> There is no command that will force table data into buffer cache.
> However, you can mark a table as pinned so that SQL Server will keep table
> data in buffer cache once read into memory. See DBCC PINTABLE in the
> Books Online for details.
> Note that it is very rare that this option will improve performance.
> Microsoft SQL Server's buffer management algorithm will automatically keep
> frequently used data in memory and makes the best use of available memory
> resources.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Frank Rizzo" <none@.none.com> wrote in message
> news:O%23K12MF8FHA.3636@.TK2MSFTNGP09.phx.gbl...
>sql

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-Nags
SELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>
|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>
|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
>

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-NagsSELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
--
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
>

How to find which tables have been pinned in memory.

Is there a way to find which tables are pinned in memory ?
-NagsSELECT table_name
FROM information_schema.tables
WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
'TableIsPinned') = 1
Jacco Schalkwijk
SQL Server MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Hello,
Use the property "TableIsPinned" property with Objectproperty function.
1 = True (in memory)
0 = False
usage
--
select objectproperty('tableid','TableIsPinned)
How to get the table id:-
select object_id('table_name')
Thanks
Hari
MCDBA
"Nags" <nags@.DontSpamMe.com> wrote in message
news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Is there a way to find which tables are pinned in memory ?
> -Nags
>|||Got it. Thank you
-Nags
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uS71O8JeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> SELECT table_name
> FROM information_schema.tables
> WHERE OBJECTPROPERTY(OBJECT_ID(table_schema + '.' + table_name),
> 'TableIsPinned') = 1
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:u5GzR1JeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > Is there a way to find which tables are pinned in memory ?
> >
> > -Nags
> >
> >
>sql

How to find current memory usage using SQL?

Anybody knows of any DBCC command or sp_configure param
which would tell me how much memory is SQL Server
currently grabbing?
I don't want to use DBCC perfmon(lrustats) because (1) it
is being deprecated and (b) the output is voluminous.
Using performance monitors is not an option since I want
to be able to do this from within a SQL script.
Thx a bunch!Check out master..sysperfinfo. The values in this table are not completely
cooked. Check out msdb..sp_sqlagent_get_perf_counters to see how the counter
values can be cooked.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Asim" <aabubba@.yahoo.com> wrote in message
news:1fc7001c38a3b$4f2fc4a0$a601280a@.phx.gbl...
> Anybody knows of any DBCC command or sp_configure param
> which would tell me how much memory is SQL Server
> currently grabbing?
> I don't want to use DBCC perfmon(lrustats) because (1) it
> is being deprecated and (b) the output is voluminous.
> Using performance monitors is not an option since I want
> to be able to do this from within a SQL script.
> Thx a bunch!