Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts

Wednesday, March 21, 2012

msmdsrv.exe taking substantial memory

Wondering if you have this problem or not, it seems SQL 2005 AS msmdsrv.exe taking lots of memory, I looked at 64 bit server, and 32 bit server, compare with other SQL server services, msmdsrv.exe runs about 500,000 - 600,000 K on 64 bit server, vs. 87,000 k for sqlserver.exe, and 67,000 k for reportserver.exe, and connect to AS through Management Studio also takes long time. I have default setting for all services.

I have one cube database which is about 240 MB in size and do full process, we have all SQL 2005 services in one 64 bit box with 8 way processor and at least 4 gz memory.

I am getting a little frustrated by this, any pointer?

Thanks

Not to become an emotional help, just to clarify the situation Smile

Analysis Server is completely different from SQL server. It is not relational but what is called OLAP and DM server. It works and implements completely different set of data structures. It is queried using different query language- MDX.
One example: Nature of relational databases is such that because of the frequent updates you often you cant caches of your data in memory. For Analysis Server the situation is completely different. The more things it can put in memory, the better- the faster it can answer user queries.

In many enterprise applicaiton it is recommended you place you Analysis Server on different machine from your relation database.

I understand that you have a small cube, but having all the servers running on your machine, fighting for resources can be cause you see for some of the slowdowns.


HTH

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

|||

Hi Edward,

Thank you so much for your comments. For right now, we have to put everything in one server becuase of licenses issue. So if I put all sql server services in one server, do you recommend set up memory limitation on Analysis Service can use instead of default setting?

I remember default setting is 75 or 80% of all avaliable memory, should I change that to lower percentage,so it will not affect other running servies?

Thanks

|||See whether this KBA http://support.microsoft.com/kb/922852 & http://support.microsoft.com/kb/900390 are any help here.|||

If SSAS is only using 500-600 Mb on a 4Gb box, it does not sound like there is too much of an issue. I would be more worried about why the sqlserver is so low.

There are a couple of perfmon counters that you can check under SQL Server: Memory Manager object called "Target Server Memory" and "Total Server Memory". The Target amount is what the server "wants" and the Total is what it is currently using. Ideally these should be the same value, but if your system is under memory pressure your Total will be lower than your target.

|||

Thanks all for your very helpfull advices. This is the test I did againist our test server, which of course not much usage. But I am a little concern about fighting resources among Database engine, RS, AS and SSIS. Have you all setup memory limit for your AS other than default setting?

Thanks

msmdsrv.exe taking substantial memory

Wondering if you have this problem or not, it seems SQL 2005 AS msmdsrv.exe taking lots of memory, I looked at 64 bit server, and 32 bit server, compare with other SQL server services, msmdsrv.exe runs about 500,000 - 600,000 K on 64 bit server, vs. 87,000 k for sqlserver.exe, and 67,000 k for reportserver.exe, and connect to AS through Management Studio also takes long time. I have default setting for all services.

I have one cube database which is about 240 MB in size and do full process, we have all SQL 2005 services in one 64 bit box with 8 way processor and at least 4 gz memory.

I am getting a little frustrated by this, any pointer?

Thanks

Not to become an emotional help, just to clarify the situation Smile

Analysis Server is completely different from SQL server. It is not relational but what is called OLAP and DM server. It works and implements completely different set of data structures. It is queried using different query language- MDX.
One example: Nature of relational databases is such that because of the frequent updates you often you cant caches of your data in memory. For Analysis Server the situation is completely different. The more things it can put in memory, the better- the faster it can answer user queries.

In many enterprise applicaiton it is recommended you place you Analysis Server on different machine from your relation database.

I understand that you have a small cube, but having all the servers running on your machine, fighting for resources can be cause you see for some of the slowdowns.


HTH

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

|||

Hi Edward,

Thank you so much for your comments. For right now, we have to put everything in one server becuase of licenses issue. So if I put all sql server services in one server, do you recommend set up memory limitation on Analysis Service can use instead of default setting?

I remember default setting is 75 or 80% of all avaliable memory, should I change that to lower percentage,so it will not affect other running servies?

Thanks

|||See whether this KBA http://support.microsoft.com/kb/922852 & http://support.microsoft.com/kb/900390 are any help here.|||

If SSAS is only using 500-600 Mb on a 4Gb box, it does not sound like there is too much of an issue. I would be more worried about why the sqlserver is so low.

There are a couple of perfmon counters that you can check under SQL Server: Memory Manager object called "Target Server Memory" and "Total Server Memory". The Target amount is what the server "wants" and the Total is what it is currently using. Ideally these should be the same value, but if your system is under memory pressure your Total will be lower than your target.

|||

Thanks all for your very helpfull advices. This is the test I did againist our test server, which of course not much usage. But I am a little concern about fighting resources among Database engine, RS, AS and SSIS. Have you all setup memory limit for your AS other than default setting?

Thanks

Monday, March 19, 2012

Msg 5133, Level 16, State 1, Line 1

I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
the error in subject when I try to create a database using this statement:
CREATE DATABASE [ESBMessageTracking] ON PRIMARY
( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE = 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF' ,
SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
I have confirmed the path is correct. Don't know what is going on. Help
please."SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:F5366135-EE7C-4C97-A782-C9A088E0FD96@.microsoft.com...
>I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
> the error in subject when I try to create a database using this statement:
> CREATE DATABASE [ESBMessageTracking] ON PRIMARY
> ( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE
> => 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> LOG ON
> ( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
> Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF'
> ,
> SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> COLLATE SQL_Latin1_General_CP1_CI_AS
> GO
> I have confirmed the path is correct. Don't know what is going on. Help
> please.
Did you confirm both PATH*S* are correct? Your MDF is on F: but your LDF is
on E: Next time, it would help if you included the entire error message
so - most people have not memorized the error numbers.|||Scott,
Thanks. I Did confirm both paths exist. Here's the error message:
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "F:\Microsoft SQL
Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF" failed
with the operating system error 1008(An attempt was made to reference a token
that does not exist.).
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check
related errors.
What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
server' it does not find the path even though it exists. It is like it
connot deal with the embedded blank.
"Scott Morris" wrote:
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:F5366135-EE7C-4C97-A782-C9A088E0FD96@.microsoft.com...
> >I am using SQL Server 2005 64 bit SP2 on a cluster with SAN. I am getting
> > the error in subject when I try to create a database using this statement:
> > CREATE DATABASE [ESBMessageTracking] ON PRIMARY
> > ( NAME = 'ESBMsgTrack_Data', FILENAME = 'F:\Microsoft SQL
> > Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF' , SIZE
> > => > 33536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> > LOG ON
> > ( NAME = 'ESBMsgTrack_Log', FILENAME = 'E:\Microsoft SQL
> > Server\MSSQL.1\MSSQL\TLogs\ESBMessageTracking\ESBMessageTracking_Log.LDF'
> > ,
> > SIZE = 218752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
> > COLLATE SQL_Latin1_General_CP1_CI_AS
> > GO
> >
> > I have confirmed the path is correct. Don't know what is going on. Help
> > please.
> Did you confirm both PATH*S* are correct? Your MDF is on F: but your LDF is
> on E: Next time, it would help if you included the entire error message
> so - most people have not memorized the error numbers.
>
>|||"SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
news:EC0C75CA-DF07-42D6-A414-565C1B1EBF79@.microsoft.com...
> Scott,
> Thanks. I Did confirm both paths exist. Here's the error message:
> Msg 5133, Level 16, State 1, Line 1
> Directory lookup for the file "F:\Microsoft SQL
> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF" failed
> with the operating system error 1008(An attempt was made to reference a
> token
> that does not exist.).
> Msg 1802, Level 16, State 1, Line 1
> CREATE DATABASE failed. Some file names listed could not be created. Check
> related errors.
> What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
> server' it does not find the path even though it exists. It is like it
> connot deal with the embedded blank.
The embedded blank shouldn't be a problem - the log file has one as well.
Apparently you have the exact same path on two different disks, using one
for data and one for logs. A bit strange that these paths appear to be sql
server installation paths - but what do I know.
First, things like this generally mean that there is a security or account
issue. Just because you personally have access to an E or F drive doesn't
mean that sql server and the accounts it uses internally do. Second,
clusters have their own complicated setup - something beyond my experience.
BTW - it appears that you posted this question last month in the cluster NG.
Is this a follow-on? Have you done the same things Geoff recommended?
http://groups.google.com/group/microsoft.public.sqlserver.clustering/browse_thread/thread/63ea092f04d74333/e447afb0ba5e592b?lnk=st&q=&rnum=1&hl=en#e447afb0ba5e592b|||I agree with Scott, in your SQL cluster group, both E:\ and F:\ disks must
have a cluster resource in the same group and SQL Server cluster resource
must depend on both drives in order to use them.
Ayad Shammout
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCN$M014HHA.600@.TK2MSFTNGP05.phx.gbl...
> "SQLGuru_not" <SQLGurunot@.discussions.microsoft.com> wrote in message
> news:EC0C75CA-DF07-42D6-A414-565C1B1EBF79@.microsoft.com...
>> Scott,
>> Thanks. I Did confirm both paths exist. Here's the error message:
>> Msg 5133, Level 16, State 1, Line 1
>> Directory lookup for the file "F:\Microsoft SQL
>> Server\MSSQL.1\Data\ESBMessageTracking\ESBMessageTracking_Data.MDF"
>> failed
>> with the operating system error 1008(An attempt was made to reference a
>> token
>> that does not exist.).
>> Msg 1802, Level 16, State 1, Line 1
>> CREATE DATABASE failed. Some file names listed could not be created.
>> Check
>> related errors.
>> What is interesting is that when I use xp_cmdshell 'dir f:\microsoft sql
>> server' it does not find the path even though it exists. It is like it
>> connot deal with the embedded blank.
> The embedded blank shouldn't be a problem - the log file has one as well.
> Apparently you have the exact same path on two different disks, using one
> for data and one for logs. A bit strange that these paths appear to be
> sql server installation paths - but what do I know.
> First, things like this generally mean that there is a security or account
> issue. Just because you personally have access to an E or F drive doesn't
> mean that sql server and the accounts it uses internally do. Second,
> clusters have their own complicated setup - something beyond my
> experience. BTW - it appears that you posted this question last month in
> the cluster NG. Is this a follow-on? Have you done the same things Geoff
> recommended?
> http://groups.google.com/group/microsoft.public.sqlserver.clustering/browse_thread/thread/63ea092f04d74333/e447afb0ba5e592b?lnk=st&q=&rnum=1&hl=en#e447afb0ba5e592b
>

Friday, March 9, 2012

MsDtsSrvr.exe memory leak?

In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.

The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.

Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?

More information about the possible memory leak.

The packages are run in in a CLR process with calls Package.Execute(), rather than being executed via dtexec or dtexecui or the designer. The reason for this approach is the ability to write a custom event handler with access to the package pointer, which other log handlers cannot do. The process runs Package.Execute() and exits, so there are 10,000 short-lived processes, each of which call Package.Execute() once.

To be slightly more specific about what the packages are doing, each of the 10,000 packages generally correlates to 1 source file, loading in dummy (aka inferred) dimension records, and then populating a fact table via a surrogate key pipeline.