September 14, 2014 at 10:14 pm
Hello everyone,
I am using SQL 2014, but having problem :
When I use query in a table (having big data ..over one hundred million rows). I just query about 700 rows for select statement but I see : ram memory using for this increasing more and more, and after finished, It reached (about 97% memory), not release ram memory for my computer until I restart SQL service 😐
Please help me explain this case !!!!!
Thanks so much
September 14, 2014 at 10:52 pm
nam.lenhat (9/14/2014)
Hello everyone,I am using SQL 2014, but having problem :
When I use query in a table (having big data ..over one hundred million rows). I just query about 700 rows for select statement but I see : ram memory using for this increasing more and more, and after finished, It reached (about 97% memory), not release ram memory for my computer until I restart SQL service 😐
Please help me explain this case !!!!!
Thanks so much
Hi and welcome to the forum. First thought would be that although the query is only returning 700 rows, the SQL Server is having to scan large part or even all the big table to produce the result set. The SQL Server reads the data into memory as much as available or it hits the max memory settings for the SQL Server instance. The default settings for the max memory settings are "unlimited" so the SQL Server will use all available memory if it has to. To prevent this from happening, set the "Maximum server memory" (right click on the SQL Server in SSMS -> Properties -> Memory) to a number lower than the actual system memory, how much lower depends on what else is running on the system.
For more assistance, post here the DDL (create table script), the query and the actual execution plan, most likely this query has some room for optimization.
😎
September 15, 2014 at 1:35 am
Hi Eirikur Eiriksson,
"Set the "Maximum server memory" "... You means
"Set maximum server memory (in MB)" (1)
"Minimum memory per query" (See this attached picture plz ;-)) (2)
I had set maximum for (1): Can't not increase number
I had decrease number for (2): form 1024 --> 512
my table created below :
--------------------------------------------------------------------------------------------------------------------------
USE [SMSDB]
GO
/****** Object: Table [dbo].[DaySale_T] Script Date: 9/15/2014 2:02:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DaySale_T](
[CmpCd] [nchar](1) NOT NULL,
[CDate] [nchar](8) NOT NULL,
[Deptcd] [nchar](10) NOT NULL,
[EmpId] [nchar](7) NOT NULL,
[Dealer] [nchar](8) NOT NULL,
[CustCd] [nchar](8) NOT NULL,
[PrdCd] [nchar](7) NOT NULL,
[SaleGubun] [nchar](1) NOT NULL CONSTRAINT [DF_DaySale_T_SaleGubun] DEFAULT ('R'),
[OrderDt] [nchar](8) NOT NULL CONSTRAINT [DF_DaySale_T_OrderDt] DEFAULT (''),
[BoxPric] [decimal](15, 6) NOT NULL CONSTRAINT [DF_DaySale_T_BoxPric] DEFAULT (0),
[BoxQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_BoxQty] DEFAULT (0),
[CasePric] [decimal](15, 6) NOT NULL CONSTRAINT [DF_DaySale_T_CasePric] DEFAULT (0),
[CaseQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_CaseQty] DEFAULT (0),
[EaPric] [decimal](15, 6) NOT NULL CONSTRAINT [DF_DaySale_T_EaPric] DEFAULT (0),
[EaQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_EaQty] DEFAULT (0),
[ProBoxQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_ProBoxQty] DEFAULT (0),
[ProCaseQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_ProCaseQty] DEFAULT (0),
[ProEaQty] [int] NOT NULL CONSTRAINT [DF_DaySale_T_ProEaQty] DEFAULT (0),
[Display] [int] NOT NULL CONSTRAINT [DF_DaySale_T_Display] DEFAULT (0),
[DeviceId] [nvarchar](10) NOT NULL CONSTRAINT [DF_DaySale_T_DeviceId] DEFAULT (''),
[UserId] [nchar](7) NOT NULL CONSTRAINT [DF_DaySale_T_UserId] DEFAULT (''),
[LastDate] [datetime] NOT NULL CONSTRAINT [DF_DaySale_T_LastDate] DEFAULT (getdate()),
[CustWhcd] [nvarchar](5) NOT NULL DEFAULT (''),
[MngEmpId] [nvarchar](7) NULL,
CONSTRAINT [PK_DaySale_T] PRIMARY KEY CLUSTERED
(
[CmpCd] ASC,
[CDate] ASC,
[Deptcd] ASC,
[EmpId] ASC,
[Dealer] ASC,
[CustCd] ASC,
[PrdCd] ASC,
[SaleGubun] ASC,
[OrderDt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'R:Rs, D:DS, L:Dealer' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DaySale_T', @level2type=N'COLUMN',@level2name=N'SaleGubun'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'???' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DaySale_T', @level2type=N'COLUMN',@level2name=N'OrderDt'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DaySale_T', @level2type=N'COLUMN',@level2name=N'Display'
GO
-----------------------------------------------------------------------------------------------------------------------
I just query: Select * from DaySale_T where CDate >= '20140801' ==> About 700 rows
When I execute this query on to SQL 2008 Enterprise (the same DB, same table), It does't happen like this 🙁
Help me please !
September 15, 2014 at 2:40 am
Firstly, don 't change minimum memory per query unless you know what you're doing and why you're changing it and the impact thereof.
Do you mean the server's memory usage is increasing?
Do you mean that the client's (where you're running the query from) memory is increasing?
You need to set max server memory to a sensible value. Currently you're telling SQL that it can use 2048 TB (that's terabytes) or memory. Unless your server happens to have that much memory, that's a inappropriate setting. Set it to a sensible value based on how much memory the server actually has
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2014 at 2:58 am
As regards the query, are there any indexes on the table in the 2008 server that aren't on the 2014 one?
September 15, 2014 at 7:20 am
The way SQL Server works, it will take all the memory in a server that it needs unless you tell it not to. That's just the way it functions. Here's a quick introduction to setting max memory in SQL Server. This will work the same way in 2014.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2014 at 10:31 pm
The server's memory usage is increasing when querying BUT IT DOESN'T DECREASE WHEN FINISHED until I restart SQL services :|. (It reached over 70% ram memory)
Can you help me explain this case please !
September 16, 2014 at 1:00 am
nam.lenhat (9/15/2014)
The server's memory usage is increasing when querying BUT IT DOESN'T DECREASE WHEN FINISHED until I restart SQL services :|. (It reached over 70% ram memory)Can you help me explain this case please !
Normal, expected, documented behaviour.
By leaving max server memory at its default, you've told SQL that it can use 2048TB of memory. Hence it can and will allocated memory until it reaches the limit set by max server memory or until it's using all the memory available. It won't release that memory unless the OS tells it to (OS under memory pressure). This is by design and normal behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2014 at 1:28 am
I had setup again Maximum server memory about 20GB (total ram is 32GB), but It's also happening when finished task 🙁
September 16, 2014 at 1:35 am
If you've set max server memory to 20GB, then SQL will increase it's memory usage up to 20GB and it will stay there. It will not deallocate memory unless the OS is under memory pressure. Again, this is normal, documented, expected behaviour.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2014 at 2:15 am
HI Gail Shaw,
I see, but I means, when it's show data and finished task, why doesn't it release ram memory because I don't do anything more ....
September 16, 2014 at 2:43 am
Because it's not designed to.
SQL Server is a service. The assumption is that it's serving multiple users, lots and lots of connections. Hence spending time and resources repeatedly releasing and acquiring memory is a waste, it'll reduce the server's performance. SQL will acquire memory as it needs to, up to the limit set by max server memory and it will keep that memory so that it doesn't have to go through the time and cost of requesting and allocating the memory again for the next request.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2014 at 2:59 am
I see !, but I means, when the query finished, it doesn't release ram memory while I don't do anything after that 🙁
September 16, 2014 at 3:04 am
Yes, as I have said multiple times in this thread, that is how SQL server behaves.
If this is a server dedicated to SQL (nothing else installed), why are you even worrying about SQL using memory? If it's a server running other things, you need to set max server memory to a value that will ensure SQL doesn't interfere with those other applications.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 16, 2014 at 8:16 am
nam.lenhat (9/16/2014)
I see !, but I means, when the query finished, it doesn't release ram memory while I don't do anything after that 🙁
Yes. Exactly. It won't release that memory, ever. That's by design. It's completely expected. It's nothing to worry about as long as you set the max memory.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply