May 20, 2015 at 2:15 pm
audiocool (5/20/2015)
if SQL do not use page file, can you explain to me why it eat a lot of the C driver hardisk space. more than 30GB is gone.
Gonna venture a guess that you have the system databases installed to the C drive. And since tempdb is on C, and you probably have some horrible queries gone awry, tempdb has bloated and chewed up 30GB.
Another alternative is you have your databases installed on C and no log backups occurring for the databases in Full recovery - thus causing the transaction log to grow and chew up disk space.
All in all, it really sounds like you have several things that are mis-configured on this server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2015 at 2:18 pm
GilaMonster (5/20/2015)
Oh, and the page file is high probably because it's the default settings in Windows. The page file is used by the OS, not by SQL Server.
If the page file is really what is high. I'm finding myself questioning that right now.
But definitely agree that the page file is used by the OS.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2015 at 2:20 pm
I am going to throw something out there that I see time and again from people migrating from really old hardware to new hardware. It always causes a cpu problem and is seldom checked.
Is the server set to run in "balanced" mode?
Do you have energy saving features disabled in the bios of the host server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2015 at 7:16 pm
Hi Grant,
Please refer to the query result you asked for at following url:
1st Results:
https://drive.google.com/file/d/0B7H-a08logDcOXczcl9hOXJlMjg/view?usp=sharing
2nd Results:
SELECT *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
order by total_worker_time desc, execution_count desc
https://drive.google.com/file/d/0B7H-a08logDcUENZODZUdHQwM0U/view?usp=sharing
At the moment, no change is been made for:
the cost threshold for parallelism - Still default setting = 5
May 20, 2015 at 9:46 pm
Probably a couple of stupid questions, but have to ask.
Was SQL 2000 on a VM?
And if so, is the same host?
Is this an in house designed database, or some vendor supplied application using SQL server on the backend?
If vendor supplied, that might be a good resource to question.
Differences between 2012 and 2000 are substantial, I assume there was some testing / use of upgrade advisor to check for issues.
And if going from a physical server to a VM, lots of other things can come into play.
Sometimes simple things - like a couple physical disks - can be better to keep things like tempdb and OS page file separated can help a lot.
As well as moving data base files onto their own disk.
Compare 2000 to what you have on the 2012 machine, and also consider physical vs. logical.
Before changing MDOP, read this[/url] and check out the Microsoft recommendations, then compare to how your VM is allocated.
If SQL and the OS start to fight each other for resources, things can spiral out of control rather quickly.
So you may find allocating less of the memory - causing SQL to more aggressively flush older cache - actually helps.
28 GB of 32 GB might be a bit too close to this, and you may want to test 24 or 26 GB if you can.
Sometimes as mentioned earlier, getting a consultant in is a good learning experience.
Learning by trial and error can be very expensive in production.
And although we all want to help, chances are with something like this, we might not even be asking the right questions.
Make sure you carefully note any changes you may make, and try to test individually if you can.
Nothing worse than losing your starting point, things got worse, and you don't know how to get back to square one.
May 21, 2015 at 12:25 am
Hi Greg,
Answer:
- SQL 2000 is from VM the same physical server as SQL 2012.
- this is in-house designed database for local application used.
- Before I restart the server yesterday (9:45am) I actually reduce the Maximum Memory to 20GB only and 5GB for minimum Memory in SQL Server.
Memory Usage in Task Manager seem like invalid as you compare side by side photo in following attachment:
Under the performance tab, it mentioned that memory is consumed up to 30.3GB
But under the processes tab, memory only consume 21GB.
May 21, 2015 at 12:30 am
Don't use Task Manager to check memory usage, it's often wrong because it doesn't include some memory areas. Process Explorer is great, otherwise use perfmon counters.
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
May 21, 2015 at 12:56 am
Quick question, can you post the output of these two queries?
😎
SELECT
OSM.total_physical_memory_kb
,OSM.available_physical_memory_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_high_memory_signal_state
,OSM.system_low_memory_signal_state
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;
SELECT
OPM.physical_memory_in_use_kb
,OPM.large_page_allocations_kb
,OPM.locked_page_allocations_kb
,OPM.total_virtual_address_space_kb
,OPM.virtual_address_space_reserved_kb
,OPM.virtual_address_space_committed_kb
,OPM.virtual_address_space_available_kb
,OPM.page_fault_count
,OPM.memory_utilization_percentage
,OPM.available_commit_limit_kb
,OPM.process_physical_memory_low
,OPM.process_virtual_memory_low
FROM sys.dm_os_process_memory OPM;
May 21, 2015 at 1:05 am
Hi Gail,
CPU and Memory Usage:
May 21, 2015 at 1:23 am
Eirikur Eiriksson (5/21/2015)
Quick question, can you post the output of these two queries?😎
SELECTOSM.total_physical_memory_kb
,OSM.available_physical_memory_kb
,OSM.total_page_file_kb
,OSM.available_page_file_kb
,OSM.system_cache_kb http://qa.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_spacer.gif
,OSM.kernel_paged_pool_kb
,OSM.kernel_nonpaged_pool_kb
,OSM.system_high_memory_signal_state
,OSM.system_low_memory_signal_state
,OSM.system_memory_state_desc
FROM sys.dm_os_sys_memory OSM;
SELECT
OPM.physical_memory_in_use_kb
,OPM.large_page_allocations_kb
,OPM.locked_page_allocations_kb
,OPM.total_virtual_address_space_kb
,OPM.virtual_address_space_reserved_kb
,OPM.virtual_address_space_committed_kb
,OPM.virtual_address_space_available_kb
,OPM.page_fault_count
,OPM.memory_utilization_percentage
,OPM.available_commit_limit_kb
,OPM.process_physical_memory_low
,OPM.process_virtual_memory_low
FROM sys.dm_os_process_memory OPM;
Results:
May 21, 2015 at 3:42 am
audiocool (5/21/2015)
CPU and Memory Usage:
USe Process Explorer (not resource monitor), it's a download from MS, or perfmon (Performance Monitor) counters. Task manager and resource monitor are for quick looks and don't have history information and don't always show what you think they're showing. You need to track information over a period of time, not look at single data points.
Have you checked the power setting that Jason mentioned?
Top wait is CXPacket, so increasing cost threshold should help a little. IT's not a fix though, you will need to identify the heaviest CPU-using queries and tune them. That may be adding indexes to support them, that may be changing the code it will probably involve both
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
May 21, 2015 at 4:51 am
Jeff Moden (5/20/2015)
audiocool (5/17/2015)
Hi Shawn,- OS and SQL Server 2012 are 64 bits
- The server equipped with SQL Server 2012 and McAfee Virus Scan Enterprise v8.8
- I monitor through the task manager in Windows Server. When that time, it occupied more than 90%
I haven't read through all the pages of this thread but you MUST absolutely make sure that virus scan isn't scanning MDF's, LDFs, or anything for SQL Server. The symptoms that you quickly stated in your original post seem to indicate that might be the actual problem.
It must also be set to not examine any I/O for SQL Server.
I totally agree with Jeff here. As much as I agree with everyone about reading the book, check McAfee first! We had a similar problem where McAfee was scanning the LDF file and real time virus checking was turned on. This degraded both our memory and CPU to the point were we found it impossible to do business.
Exclude all MDFs, LDFs, NDFs (if you have them), and turn off the "real time" scanning (EDIT: this is On Demand Scanning, which scans on each read or write of the disk). Have scheduled scans on your SQL servers instead. That should help. Assuming your issue is McAfee, that is, and not SQL Server.
May 21, 2015 at 4:56 am
One of your top queries is from Activity Monitor. You should probably not have it open all the time. You're actually adding load to the server by doing that.
Let me start this by putting a couple of caveats. I'm not there. I can't see your servers or their configuration. That means that anything I'm about to say is wrong.
From what I can see, you're hitting CPU issues WAY more than anything that suggests memory problems. So, please, walk away from the memory. Don't mess with it any more. Let's focus on what the problem might be.
I still think it could be something on the VM, but I'm not going to attempt to advise you there.
On SQL Server, first, change the Cost Threshold.
Next, is the 2000 instance still running? I'm curious what the cost threshold there is. Also, I'm curious if the max degree of parallelism on the 2000 server and the new server are at the default, 0. If they're different, what are they?
Assuming it's not the VM and the values above are the same, then I think we're down to tuning queries.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 21, 2015 at 7:58 am
Brandie Tarvin (5/21/2015)
Jeff Moden (5/20/2015)
audiocool (5/17/2015)
Hi Shawn,- OS and SQL Server 2012 are 64 bits
- The server equipped with SQL Server 2012 and McAfee Virus Scan Enterprise v8.8
- I monitor through the task manager in Windows Server. When that time, it occupied more than 90%
I haven't read through all the pages of this thread but you MUST absolutely make sure that virus scan isn't scanning MDF's, LDFs, or anything for SQL Server. The symptoms that you quickly stated in your original post seem to indicate that might be the actual problem.
It must also be set to not examine any I/O for SQL Server.
I totally agree with Jeff here. As much as I agree with everyone about reading the book, check McAfee first! We had a similar problem where McAfee was scanning the LDF file and real time virus checking was turned on. This degraded both our memory and CPU to the point were we found it impossible to do business.
Exclude all MDFs, LDFs, NDFs (if you have them), and turn off the "real time" scanning (EDIT: this is On Demand Scanning, which scans on each read or write of the disk). Have scheduled scans on your SQL servers instead. That should help. Assuming your issue is McAfee, that is, and not SQL Server.
Well, if you must have AV on your SQL Servers, then exclude the volume containing data and log files entirely. Then exclude the SQL binaries. Don't even let AV touch your data and log volumes - it causes problems too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 21, 2015 at 8:00 am
Grant Fritchey (5/21/2015)
One of your top queries is from Activity Monitor. You should probably not have it open all the time. You're actually adding load to the server by doing that.
Hahaha - Activity Monitor is one of the biggest causes of performance issues. So many clients open that and leave it open and wonder why performance sucks. Not quite as bad as running profiler against the server having performance issues - but it is up there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 46 through 60 (of 107 total)
You must be logged in to reply to this topic. Login to reply
