Analysis Server Memory Upgrade

Dear Anyone,

We recently upgraded our analysis server from a 4GB to 8GB. For a couple of weeks, we noticed that the server memory isnt going up to close to 8GB and only goes to around 4. We use the /3Gb and /PAE on our server.

Can anyone recommend some memory tweaks that we can use on the Analysis Services?

Thanks,

Joseph

Unfortunately in this case having more memory is not going to help.

32 bit version of Analysis Server will not use more than 3Gb of memory.

You should move to 64 bit hardware to be able to take advantage of more memory of your system.
In AS 2005 you should be able to install multiple instances and distribute your databases across instances.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Just want to clarify that this 3GB limitation is on Analysis Services 2005? I thought the memory limitation was for Analysis Services 2005 got a lot bigger from that of the 2000 version. I'm I wrong in thinking this?

Furthermore, can anyone please share the maximum memory configuration we can give Analysis Services 2005 to maximize the available memory? I.e. Analysis 2005 is on a box with 8GB of memory.

|||

Memory limitations for AS2K5 are the same as AS2K -- they are based on the OS (see earlier post).

Therefore to use 8GB of memory you must be running on a 64-bit platform, as a native 64-bit application.

That being said however, what might be confusing you, is that memory usage in AS2K5 has gotten a while lot more efficient. We now have a dimension memory cache, replicas used for dimension security are smaller and more efficient, etc. Thus you can do more with what you have.

But our general advise is still the same -- running on 32-bit hardware, there is no reason to have more than 4GB on your server. See the AS Operations Guide for more guidelines like this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/anservog.mspx

Hope that helps.

_-_-_ Dave

sql

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.
One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.
|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or record
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe to
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:
> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from your
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> > We have been using the textcopy.exe tool since SQL7 (we were able to still
> > use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> > can
> > use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> > record
> > in our database. Basically, the VB6 code calls a stored proc and passes
> > it
> > the name and location of the file. The stored proc will then use the
> > textcopy.exe to upload this file into an image column in a database table:
> >
> > SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> > password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> >
> > EXEC Master..xp_cmdShell @.cmd
> >
> > where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> > test.pdf).
> >
> > When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> > this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> > to
> > work in 2005.
> >
> > What is the best way to insert a file into an image column in the database
> > in 2005? I'd like to not have to re-architect this piece of our
> > application,
> > but I may have to...is there an alternative similar to textcopy.exe for
> > SQL
> > 2005? Indexing the file is not important - we just need to save the file
> > in
> > the database so that it can be viewed from a web application.
> >
> > Thanks!
> > Jeff M.
>
>

Alternative to textcopy.exe in SQL2005

We have been using the textcopy.exe tool since SQL7 (we were able to still
use this tool when we upgraded SQL2000) - we have a VB6 app that a user can
use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or recor
d
in our database. Basically, the VB6 code calls a stored proc and passes it
the name and location of the file. The stored proc will then use the
textcopy.exe to upload this file into an image column in a database table:
SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
EXEC Master..xp_cmdShell @.cmd
where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
test.pdf).
When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe t
o
work in 2005.
What is the best way to insert a file into an image column in the database
in 2005? I'd like to not have to re-architect this piece of our application
,
but I may have to...is there an alternative similar to textcopy.exe for SQL
2005? Indexing the file is not important - we just need to save the file in
the database so that it can be viewed from a web application.
Thanks!
Jeff M.One method is to insert the blob data using OPENROWSET...BULK and then
update your main table:
CREATE PROC dbo.UpdateMyTableImageData
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(max))
--insert blob into temp table
SET @.SqlStatement =
N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
Personality, I think the file content should be inserted directly from your
client application rather than on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
> We have been using the textcopy.exe tool since SQL7 (we were able to still
> use this tool when we upgraded SQL2000) - we have a VB6 app that a user
> can
> use to attach a file (a .pdf file or a .doc file, etc.) to a "job", or
> record
> in our database. Basically, the VB6 code calls a stored proc and passes
> it
> the name and location of the file. The stored proc will then use the
> textcopy.exe to upload this file into an image column in a database table:
> SET @.cmd = 'c:\MSSQL7\Binn\textcopy.exe /S SQL01/D DB01 /U username /P
> password /T tblImages /C picture /W ' + @.whr + ' /F ' + @.fil + ' /' + @.mod
> EXEC Master..xp_cmdShell @.cmd
> where @.whr is the path (e.g. c:\folder\) and @.fil is the filename (e.g.
> test.pdf).
> When we upgraded from 7 to 2000, we could still use the textcopy.exe to do
> this. I'm testing a 2000 to 2005 upgrade and I can't get the textcopy.exe
> to
> work in 2005.
> What is the best way to insert a file into an image column in the database
> in 2005? I'd like to not have to re-architect this piece of our
> application,
> but I may have to...is there an alternative similar to textcopy.exe for
> SQL
> 2005? Indexing the file is not important - we just need to save the file
> in
> the database so that it can be viewed from a web application.
> Thanks!
> Jeff M.|||Thanks Dan - that most definitely points me in the right direction! And I
agree, at some point here in the near future we will certainly re-architect
the app to do the insert...but for now, we need to upgrade the DB first.
Thanks again!
Jeff M.
"Dan Guzman" wrote:

> One method is to insert the blob data using OPENROWSET...BULK and then
> update your main table:
> CREATE PROC dbo.UpdateMyTableImageData
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(max))
> --insert blob into temp table
> SET @.SqlStatement =
> N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> Personality, I think the file content should be inserted directly from you
r
> client application rather than on the server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "jeffromiller" <jeffromiller@.discussions.microsoft.com> wrote in message
> news:727F2AB3-AA04-44E2-9D44-4990C8B96097@.microsoft.com...
>
>