SSAS Memory Problem In Querying
May 14, 2008
A question about memory management in querying an SSAS server.
It so happens that users make huge queries on unoptimized elements of the cube.
The result on our server is that memory usage grows very fast, and very far beyond the TotalMemoryLimit that we specified on the server.
Especially, we specified 2.6GB for TotalMemoryLimit, and the performance graphs I have show that memory usage for the service exceeded 4.5GB for some of these bad queries !
As a result, the paging explodes, and put the system on low-memory conditions... Since the service runs on a shared environment, it causes troubles to other services running there...
I understand that the momory limit is a kind of a soft limit that can be exceeded, but not that much !!!
I would prefer the bad queries are automatically killed by the service, replying that it does not have enough memory to complete...
Is there a way to configure the service that way ?
View 4 Replies
ADVERTISEMENT
Mar 27, 2008
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
View 1 Replies
View Related
Mar 29, 2008
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
View 6 Replies
View Related
May 21, 2007
This morning msmdsrv.exe looked like the following in Task Manager:
Memory Usage: 600,000 K
VM Size: 2,500,000 K
If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)
In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)
View 2 Replies
View Related
May 7, 2015
I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.
What might be the issue in such a small database? I would understand in a big database, but not on this one.
View 13 Replies
View Related
Jul 13, 2015
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
View 4 Replies
View Related
Sep 28, 2007
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
View 4 Replies
View Related
Oct 11, 2007
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
View 3 Replies
View Related
Aug 28, 2015
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies
View Related
Dec 6, 2006
I am running Visual Studio 2005. I have an SSIS Package which is consuming a huge amount of memory. During the execution of the package the memory keeps increasing. Until finally i get an Out of Memory exception. I have run this package using dtexec, and in the BIDS. No difference. I do have some script components and have added some code to get the assemblies in the current appdomain. I do see that one particular assembly is increasing on every loop. VBAssembly every time it hits the script component is increasing by 6, and along with it the memory is climbing. What is this VBAssembly being used for is there an update to SQL Server Integration Services that I need?
Thanks! Aaron B.
View 6 Replies
View Related
Aug 22, 2007
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
View 4 Replies
View Related
Oct 4, 2015
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
View 9 Replies
View Related
Jun 15, 2015
I've a database with a memory optimized filegroup on it. How can I remove it?I have removed the memory optimized table I had on it, but when I try to remove the filegroup I receive an error.
View 12 Replies
View Related
Jul 17, 2006
Hi,
I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.
I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).
Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?
How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)
Thank
View 5 Replies
View Related
Feb 10, 2015
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online:
• This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
• The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
View 3 Replies
View Related
Mar 20, 2008
Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies
View Related
Aug 2, 2006
Hi
I did a load testing and found the following observations:
1. The Memory:Pages/sec was crossing the limit beyond 20.
2. The Target Server Memory was always greater than Total Server Memory
Seeing the above data it seems to be memory pressure. But I found that AvailableMemory was always above 200 MB. Also Buffer Cache HitRatio was close to 99.99. What could be the reason for the above behavior?
View 1 Replies
View Related
Sep 9, 2007
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00'
AND EXPIRATION < '2007-02-28 00:00:00'
AND OPT_TYPE = 'CALL'
Related DB fields.
STOCK_SYMBOL
STRIKE
UND_LAST
OPT_TYPE
EXPIRATION
Any help would be appreciated.
View 2 Replies
View Related
Nov 8, 2007
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables
ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
View 3 Replies
View Related
Nov 9, 2006
Hello,
I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today?
How does time affect this?
View 5 Replies
View Related
Sep 28, 2007
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
View 7 Replies
View Related
Oct 31, 2007
Hey,
What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work.
Thanks!
View 4 Replies
View Related
Jan 27, 2008
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
View 5 Replies
View Related
Aug 10, 2005
I have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
View 4 Replies
View Related
Mar 20, 2006
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
View 4 Replies
View Related
Apr 20, 2006
Hi guys
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table
where type = 'Extra Mailbox'
and active = 1
and value not in
(select login from view)
order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
Any ideas?
Thanks in advance for any responses.
Ging
View 2 Replies
View Related
Oct 1, 2004
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
View 5 Replies
View Related
Apr 30, 2008
Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
View 2 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Jun 12, 2008
how to write a query to find the no of tables present in that database.
Any suggestions.
View 4 Replies
View Related
Jun 12, 2008
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
View 2 Replies
View Related
Jan 23, 2007
Hi there,
Is there a way where I can run a query or a store procedure where i can retrieve all the contraints attached to the tables in the database?
thanks
View 1 Replies
View Related
Apr 21, 2007
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
View 2 Replies
View Related
