November 29, 2015 at 5:38 pm
Hi
I have an issue with Memory in my database server last month all of sudden the memory became 96% and above . I can’t check the queries that was running at that time .
I check that memory has been capped . so Max memory setting for SQL is 41 GB out of 48 GB . Based on Performance monitor Total server memory is 41 GB
Until today memory is still very high
Anything that I need to do to troubleshoot this issue ?
I really appreciate for any feedback
cheers
November 29, 2015 at 8:02 pm
Are you saying that 41GB is high? If you set the max to 41GB, the SQL Server instance will use 41GB. All the RAM that isn't needed for queries is used for data that's loaded from disk.
November 29, 2015 at 8:22 pm
My concern is why the memory becomes very high even tho it has been capped since a long time ago .
Very curious
November 29, 2015 at 8:49 pm
You said it was capped at 41GB. It will always grow to 41GB as you use the server.
No concern, no curiosity. This is how SQL Server works. Every query reads data from disk into memory. This data in memory is not removed until new data needs to take the place of the existing data.
This will continue until SQL Server reaches the max server memory.
November 29, 2015 at 8:59 pm
It will still be my concern because memory is very high ( 96%)
Then how do I solve that issue and slowness as well ?
November 29, 2015 at 11:07 pm
WhiteLotus (11/29/2015)
It will still be my concern because memory is very high ( 96%)Then how do I solve that issue and slowness as well ?
That memory is not an issue. SQL Server will automatically use all the memory it has available to it. Always. Every time. So you don't have to worry about whether or not it's using the memory, it is. All of it.
Instead, you need to worry about HOW it is using that memory. Is it able to read from memory for most queries? Great. Win? Or, does it have to go to the disk for lots of queries, moving their information into memory, throwing out old information, only to do that again and again? That's a problem.
Instead of focusing on memory percentage, you need to be looking at your wait statistics through sys.dm_os_wait_stats and queuing through the various memory and disk queue indicators. That will tell you if you have problems and where those problems may be. You also have to look at the queries, which ones are long running, called frequently, or just use lots of resources. Tune the problematic queries.
That's how you go about monitoring. Don't get hung on a single number, percentage of memory, especially when that number is actually very unimportant.
"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
November 30, 2015 at 8:59 am
As Grant mentioned, the memory isn't the issue.
Slowness ~!= memory. They may be related, but may not be. Likely if your server is slow, it's not because SQL is using the memory. It's because SQL needs more memory, or you have poor code/indexing/IO.
What's target memory?
What does slow mean? Do you have specific queries that are slow? Have you looked at plans or examined waits as Grant mentioned?
November 30, 2015 at 3:47 pm
It was my understanding that SQL only acquired RAM when it needed it. That is, it doesn't automatically grab the max ram as soon as it starts. Of course SQL can find a lot of reasons to grab ram, so it seems as if it always gets the max, but I think you can give SQL possible access to extra ram and have it not use it.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 30, 2015 at 4:27 pm
Hi Guys ,
Thank you for taking time to reply to this thread . Much appreciate it.
I run this script :
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO
The result is as below ( I only grab 24 wait_type as the top wait time ) :
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
DBMIRROR_EVENTS_QUEUE25548498835836139413435336176
DBMIRRORING_CMD61144698333158679424463941833532
BROKER_TASK_STOP352246253447796204100862956138
LAZYWRITER_SLEEP378231633429339163892012793274
XE_TIMER_EVENT557361671830066302161671828702
REQUEST_FOR_DEADLOCK_SEARCH333739167175200053781671752000
ONDEMAND_TASK_QUEUE458931671695846157014010633
LOGMGR_QUEUE74242731671299253549711162587
CHECKPOINT_QUEUE5829166533125429394053447
SQLTRACE_INCREMENTAL_FLUSH_SLEEP414931166312381742123112
BROKER_EVENTHANDLER6916601363128456971738
FT_IFTS_SCHEDULER_IDLE_WAIT276271657878746602764765
XE_DISPATCHER_WAIT1191616212780859028640
SLEEP_TASK210511038366645311284866838
BROKER_TO_FLUSH8118218359054951266277186
CXPACKET4425325662146084835727766366417
BACKUPIO217901651368984671990171156857
LATCH_EX31159155145303779475663346941
BACKUPTHREAD53300286282059719586746501
WAITFOR234066235704657114097461
SOS_SCHEDULER_YIELD3958606438687290560386417797
WRITELOG101095976400625711342079531
BACKUPBUFFER993871610333362109161576
LCK_M_X728873299913714074107381
So this problematic server is the primary server mirror to secondary server . Honestly I don’t really understand about DBMirror wait type ? It could be the cause of slowness ?
I have been tuning some queries so far but no significant impact ?
November 30, 2015 at 4:44 pm
Scott,
AFAIK, you are correct. SQL Server doesn't grab the max memory on startup. However it does grow, primarily through caching data, and will often grow quickly to the max under a workload.
WhiteLotus,
There could be a number of issues. Certainly you should monitor your wait types regularly to understand what is normal for you. In terms of the mirror, are you synchronous or async? You might have issues with slowness.
A few things to note: version/build of SQL Server. Are you up to date? Mirroring has gotten a lot of fixes over time.
What is target server memory? Not total, but target.
What do your redo and send queues look like?
A few links that might help:
- https://msdn.microsoft.com/en-us/library/ms190030.aspx
- http://www.sqlskills.com/blogs/paul/importance-of-monitoring-a-database-mirroring-session/
November 30, 2015 at 4:52 pm
Actually there is 1 query which confuses me , so I added cover index (non clustered index ( key column is ABC.number with included columns – all columns in SELECT area) and the performance improves a lot ( duration drop to 0 sec from 10 sec ) for particular parameter ( the parameter is ABC.Number )
But when it uses another parameter. the duration is still 10 secs ( it uses index scan instead of index seek)
I wonder why this happen as I expect it will behave the same for all parameters / value
This is the query :
SELECT ABC.XY_Create_Date XY_Create_Date,
ABC.ABC_Id ABC_Id,
dbo.rfn_IdToUserString(ABC.ABC_Id) Ticket_Number,
ABC.Name Name,
ABC.Status Status,
ABC.XY_Create_User XY_Create_User,
ABC.XY_Edit_User XY_Edit_User,
ABC.XY_Edit_Date XY_Edit_Date,
(cast(ABC.XY_Edit_Date as float) + 2) XY_Float_Edit_Date
FROM ABC ABC
WITH
(
READCOMMITTED
)
WHERE 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND (ABC.Number LIKE @P1
AND
/*CN*/
Memory increased a lot along with Slowness was reported so they are suppose to be correlated to each other
November 30, 2015 at 5:44 pm
I just checked database mirroring , in Principal server the status for each database is “Principal , Syncronized “ and in mirror server , each database is “ Mirror, Syncronized”
Does it mean it is Synchronous type ?
The sql server is SP2 with CU 12 and patched last year .
Target server memory is the same with Total server memory = 41.906 MB (41 GB)
I don’t know how to check redo and send queues . Can you please point out how to check them ? what is the benefit knowing them?
Thanks so much for the links
November 30, 2015 at 10:33 pm
WhiteLotus (11/30/2015)
Actually there is 1 query which confuses me , so I added cover index (non clustered index ( key column is ABC.number with included columns – all columns in SELECT area) and the performance improves a lot ( duration drop to 0 sec from 10 sec ) for particular parameter ( the parameter is ABC.Number )But when it uses another parameter. the duration is still 10 secs ( it uses index scan instead of index seek)
I wonder why this happen as I expect it will behave the same for all parameters / value
This is the query :
SELECT ABC.XY_Create_Date XY_Create_Date,
ABC.ABC_Id ABC_Id,
dbo.rfn_IdToUserString(ABC.ABC_Id) Ticket_Number,
ABC.Name Name,
ABC.Status Status,
ABC.XY_Create_User XY_Create_User,
ABC.XY_Edit_User XY_Edit_User,
ABC.XY_Edit_Date XY_Edit_Date,
(cast(ABC.XY_Edit_Date as float) + 2) XY_Float_Edit_Date
FROM ABC ABC
WITH
(
READCOMMITTED
)
WHERE 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND 1 = 1
AND (ABC.Number LIKE @P1
AND
/*CN*/
Memory increased a lot along with Slowness was reported so they are suppose to be correlated to each other
Different parameter values will return different sets of rows, so potentially have different execution plans and different performance. That's expected behavior. You just won't get exactly the same performance when returning 1 row as you will when returning 1000. Without seeing the entire query and the different execution plans, it's hard to say for sure what's happening, but it sounds like it might be a case of bad parameter sniffing.
Yes, a query that results in a scan is going to consume more resources, I/O and memory, than one that does a seek (in general, there are exceptions to this, absolutely), so that's not surprising. You'll need to look to the execution plans and the statistics on the tables involved to understand why one parameter value results in a seek and the other results in a scan. It's entirely possible that for the value in question, a scan is actually more efficient.
"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
December 2, 2015 at 8:58 pm
I have just tested this query with some parameters
All of them cause long execution time ( around 8 until 12 secs per execution )
For example :
Before adding index
Duration : 9 Secs
(1 row(s) affected)
Table 'ABC'. Scan count 1, logical reads 1071012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
?Index Scan 92 %
After adding index
Duration : 9 secs
(1 row(s) affected)
Table 'ABC'. Scan count 1, logical reads 75421, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
?Cluster Index Scan 99%
All of parameters will only return 1 row
Unfortunately I can’t find parameter that uses Index Seek ( I did have it but I don’t remember that parameter anymore now ? )
I just run SQL profiler ( Server side Trace ) for 1 hour and I couldn’t find that parameter there
Questions :
1. Index that I added is :
CREATE NONCLUSTERED INDEX [IndexABC] ON [dbo].[ABC]
(
[Ticket_Number] ASC
)
INCLUDE ( [ABC_Id],
[Status],
[XY_Create_Date],
[Name],
[XY_Edit_User],
[XY_Create_User],
[XY_Edit_Date]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Is it the correct index ?
My Database Performance Analyzer suggested me to add this index
2. Any idea how will I find the parameter that will uses INDEX SEEK?
I really appreciate your feedback !
December 2, 2015 at 10:50 pm
Create statistics on ABC.number, or create a test index on ABC.number, and examine that statistic's histogram. The partially-omitted "(ABC.Number LIKE @P1 " predicate may not be sufficiently selective for the distribution of values that are shown in the histogram. Consider filtering the index if there is a preponderance of one value (such as NULL), by filtering out that value. If you created test index on ABC.number, force an index seek upon that index, and examine the execution plan. If ABC.number is not selective, the number of logical reads for the seeks will be greater than the number of logical reads for the above scan (in addition, statistics time should be considered). If the index on ABC.number was not suggested when inspecting the estimated plan, it likely is not going to help (but still worth testing).
I do have a concern about the repeated "AND 1 = 1" clauses. I do not think the "AND 1 = 1" clause is bad, in and of itself. But that clause's presence suggests there being a variety of ad-hoc plans with a variety of parametrized predicates. In which case I wish you luck :).
.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply