May 19, 2015 at 8:07 pm
No choice. Fixed in one thing is to let the shop floor and warehouse to continue running. While I need more time to get my self understand the book since there is a lot of technical term which I hardly to understand also.
Just got a few called from shop floor and warehouse, they are not able to connect. I personally connect to Management Studio and found out the message is insufficient memory. But I checked the usage compare to my previous post which is 31.9/32GB and now is 30.2/32GB. I'm puzzled why it stated memory insufficient. If I can't connect so how to troubleshoot further.
Found out one thing which is even the memory just now is 30.2/32GB, it actually taken a lot of C drive hardisk space. I think is page file.... Check system page file setting is set to System Managed and it is auto set to 67GB which in C drive there is not that much of free space...
May 19, 2015 at 8:33 pm
It sounds like there are a lot of different problems going on here. I recognize that you're focused on the memory, but you need to step back from it. It sounds like you may have a lot of connections coming in to the server. It's possible that your connection pooling has been disabled for some reason. That's also going to lead to more memory use since each connection has to have some memory. It also sounds like you're hitting long running or very large queries, suggesting traditional tuning is needed. Didn't you mention a deadlock earlier? That's another performance problem, and one that's unrelated to memory in any way.
What we're trying to tell you is that you need to approach this systematically. To understand what's going wrong you can't just keep comparing memory counters over and over. That's not telling you anything at all except that the system is using the amount of memory that you gave it.
I still think your best bet is to read through the chapters in the book to understand how to set up monitoring. But, if you want some really fast suggestions on things to look at, run a query against sys.dm_os_wait_stats. That's going to show you what the server is waiting on. Order by the number of waits and put the top 15 up here. Order by the length of waits and put the top 15 up here. We can then see why the server is running slow rather than guessing.
Then, run a query against sys.dm_exec_query_stats. You'll have to combine that with sys.dm_exec_sql_text to get the query string like this:
SELECT *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
Order this one by the longest run time and the frequency of runs and put the two lists up here.
With all this information, we can make a few guesses for you, but no guarantees.
Please, instead of just poking at the server, read through that book.
----------------------------------------------------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 19, 2015 at 10:14 pm
Hi Grant,
First of all, I would like to thanks for your effort for trying to help me on this.
Below is the output that you require:
Wait_Stat:
Query Stat
https://drive.google.com/file/d/0B7H-a08logDcdEY1WmY2bEo2Rlk/view?usp=sharing
May 20, 2015 at 12:02 am
Grant Fritchey (5/19/2015)
It sounds like there are a lot of different problems going on here. I recognize that you're focused on the memory, but you need to step back from it. It sounds like you may have a lot of connections coming in to the server. It's possible that your connection pooling has been disabled for some reason. That's also going to lead to more memory use since each connection has to have some memory. It also sounds like you're hitting long running or very large queries, suggesting traditional tuning is needed. Didn't you mention a deadlock earlier? That's another performance problem, and one that's unrelated to memory in any way.What we're trying to tell you is that you need to approach this systematically. To understand what's going wrong you can't just keep comparing memory counters over and over. That's not telling you anything at all except that the system is using the amount of memory that you gave it.
I still think your best bet is to read through the chapters in the book to understand how to set up monitoring. But, if you want some really fast suggestions on things to look at, run a query against sys.dm_os_wait_stats. That's going to show you what the server is waiting on. Order by the number of waits and put the top 15 up here. Order by the length of waits and put the top 15 up here. We can then see why the server is running slow rather than guessing.
Then, run a query against sys.dm_exec_query_stats. You'll have to combine that with sys.dm_exec_sql_text to get the query string like this:
SELECT *FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
Order this one by the longest run time and the frequency of runs and put the two lists up here.
With all this information, we can make a few guesses for you, but no guarantees.
Please, instead of just poking at the server, read through that book.
Grant Hats Off Man ...:Whistling:
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
May 20, 2015 at 12:05 am
Please, please, please get and read that book.
Most of the waits in the picture are ones we don't care about (sleep is supposed to be high). There's a script in chapter 1 that excludes all the useless waits.
You have high CPU usage, queries using lots of CPU. You need to identify those queries and fix them so that they don't use so much CPU
No choice. Fixed in one thing is to let the shop floor and warehouse to continue running.
Nope. SQL is NOT a system where problems are fixed by looking at one counter and fiddling with associated things, fixing one thing, unless you happen to hit on the root cause, is not going to let the warehouse continue running.
I've done this work for clients enough times to see that come true again and again and again.
What did you order the query stats by? Longest Duration? If so, in light of the high CPU stats, run it again ordered descending by total worker time. That'll give you the highest CPU-consuming queries and a place to start.
Also, SQL doesn't use the page file, so no need to look at that.
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 20, 2015 at 12:35 am
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.
May 20, 2015 at 12:37 am
OP: you have been going at this for several days now, with the loss of your SQL Server functionality clearly affecting your business operations. Right from the very start on May 17th multiple people have told you to get professional help. Why hasn't management seen their way to hiring a consultant for a short term "fix me now" session?? This type of corporate behavior just makes no sense at all to me. :crying:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2015 at 12:48 am
We have vendor recommendation to consult Microsoft straight away because usually this kind of problem they do not know how to handle. In our country, we have partner of Microsoft but they are not really expert in handling this issue.
I can consult Microsoft for this but if the answer from them is apply CU6 then full stop.
The reason why I look for you guys help because you guys have certain of expertise and been handle some critical cases and maybe some information given to you guys, you can guide me through the correct path.
Reading is not really a bad idea but if I'm given u a book to read to dismantle engine part rather than if I showed you how to dismantle the engine part, u may know a lot of technical thing in book. When come into practical, will you able to handle that and that's the question.
May 20, 2015 at 3:42 am
audiocool (5/20/2015)
We have vendor recommendation to consult Microsoft straight away because usually this kind of problem they do not know how to handle. In our country, we have partner of Microsoft but they are not really expert in handling this issue.I can consult Microsoft for this but if the answer from them is apply CU6 then full stop.
The reason why I look for you guys help because you guys have certain of expertise and been handle some critical cases and maybe some information given to you guys, you can guide me through the correct path.
Reading is not really a bad idea but if I'm given u a book to read to dismantle engine part rather than if I showed you how to dismantle the engine part, u may know a lot of technical thing in book. When come into practical, will you able to handle that and that's the question.
The real issue is, let's use the engine analogy, you're asking us to repair your engine when we can't see it, can't hear it, don't know what state it's in, where the parts are, what parts you have, etc. You're focused on the gas mileage and we keep telling you that the problem has to be somewhere other than the gas mileage. We're trying to help, but since we have an utterly incomplete picture of what's going on, we're just making pokes at the issue. That's why we keep coming back around to the book and professional help. The book is more likely to help you help yourself. A pro can more quickly diagnose what's up.
One small thing, utterly on the side, completely unrelated to your problem, but, go and change the cost threshold for parallelism. It's currently the default value of 5. Make it 40. This won't fix your issues, but it's going to come up eventually, might as well jump on it. I'm going to go look at your results now.
----------------------------------------------------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 20, 2015 at 4:03 am
audiocool (5/19/2015)
Hi Grant,First of all, I would like to thanks for your effort for trying to help me on this.
Below is the output that you require:
Wait_Stat:
Query Stat
https://drive.google.com/file/d/0B7H-a08logDcdEY1WmY2bEo2Rlk/view?usp=sharing
Sorry. My fault. I should have been more clear. Let's try again. Please post the results, right here in one of the windows, but run the query here[/url]. I just want you to modify the second place where it says SELECT to say SELECT TOP 20.
But, the sos_scheduler_yield is at least suggesting you have high CPU queries. Gail has already suggested what you need to do there.
Async_network_io suggests that you might have issues with data waits on the client side of things. Now that could be because you're returning large data sets, or it could be something on the client side like they're starting transactions but not committing them. Hard to know from here.
You have a bunch of latch waits too. That's indicative of possible disk problems.
Looking at the results from the queries, those durations are pretty crazy considering the size and complexity of the queries. It's possible, maybe even probable, that there are indexing issues. We'd have to look at your query plans to be sure. But, let's start with the basics, first, ensure that your automatic update of statistics is enabled. Same thing with automatic create of statistics. Then, let's execute sp_updatestats. That will ensure that your statistics are up to date.
Post the wait statistics results so that we can see what's going on there. As far as the queries go, I'd like to see the results from what Gail suggested. I asked for sorts by number of executions and by total run time. We'd need to see all three to start to see which queries are common and therefore more likely to be the culprits.
Another concern I have, one that's been questioned, but never answered, since this is running on a VM, we do have to worry about how the VM is configured. That's not something I'm an expert at, but it should be addressed. You could be seeing issues because of other VMs, a noisy neighbor, as much as you're seeing issues because of SQL Server. I don't have immediate advice for what to look for here. Maybe someone else on the thread can pop in.
----------------------------------------------------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 20, 2015 at 6:48 am
Hi Grant,
I will compiled the results you are looking for tomorrow.
Thanks.
May 20, 2015 at 7:41 am
audiocool (5/20/2015)
Hi Grant,I will compiled the results you are looking for tomorrow.
Thanks.
Sure thing. Trying hard to help out.
----------------------------------------------------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 20, 2015 at 12:01 pm
audiocool (5/20/2015)
We have vendor recommendation to consult Microsoft straight away because usually this kind of problem they do not know how to handle. In our country, we have partner of Microsoft but they are not really expert in handling this issue.
Many of us will happily work across country boundaries. My company is a Microsoft partner. I do this kind of work a lot.
The reason why I look for you guys help because you guys have certain of expertise and been handle some critical cases and maybe some information given to you guys, you can guide me through the correct path.
And we've been trying, and you've been ignoring many of the things we're asking for and suggesting. I've fixed a number of problems similar to yours, I spent today doing so, but I can't do so without detailed diagnostic data and fixing such problems is absolutely not a case of 'change one setting at random'
Reading is not really a bad idea but if I'm given u a book to read to dismantle engine part rather than if I showed you how to dismantle the engine part, u may know a lot of technical thing in book. When come into practical, will you able to handle that and that's the question.
Which tells me you haven't looked at the book in question, because it's structured as a 'What do do when I have this problem?' book. It's a 'how to fix a noisy engine' book, not a 'here's the pieces of the engine' book. That's why Grant and I recommend it. It's why I helped write it in the first place.
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.
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 20, 2015 at 1:11 pm
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.
--Jeff Moden
May 20, 2015 at 2:08 pm
GilaMonster (5/18/2015)
audiocool (5/18/2015)
Sorry we don't have DBA here and everything learn from internet. 🙂Which is why I'm very strongly suggesting you get a 3rd party in. There are lots of good people in all areas of the world that could probably help you.
For self-study, the book "Troubleshooting: A guide for the accidental DBA" is a good place to start. Work through chapter 1, get as much data as you can, then go through the other chapters as you need depending on the problem you are trying to solve.
+10 on both suggestions.
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 - 31 through 45 (of 107 total)
You must be logged in to reply to this topic. Login to reply