June 2, 2017 at 6:01 am
It sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.
"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
June 4, 2017 at 6:35 pm
pranabpal - Friday, June 2, 2017 4:54 AMGilaMonster - Thursday, June 1, 2017 1:27 PMI give up.Firstly, those are all also useless waits, so get Glenn Berry's *latest* script, not one that's probably from 2005 looking at the excluded waits.
Second. I DO NOT WANT the totals!
then calculate the *difference* in wait stats between two samples.
And then post the ones with the highest *difference* between the two time periods. Those totals are still useless.GilaMonster - Thursday, June 1, 2017 1:27 PMI give up.
Firstly, those are all also useless waits, so get Glenn Berry's *latest* script, not one that's probably from 2005 looking at the excluded waits.
Second. I DO NOT WANT the totals!
then calculate the *difference* in wait stats between two samples.
And then post the ones with the highest *difference* between the two time periods. Those totals are still useless.Difference of these wait total are :
wait Type Waits in sec
DIRTY_PAGE_POLL 50423.84 HADR_FILESTREAM_IOMGR_IOCOMPLETION 50424.11 QDS_SHUTDOWN_QUEUE 50460.93 As you have mentioned these useless waits - so there are only ASYNC_NETWORK_IO waits in the database which exceeds 6 mins and others are with 1.5 mins like BACKUP IO. Tell what to do to over come these wait issue,
Thank a lot.
ASYNC_NETWORK_IO waits in the database which exceeds 24 hrs ie difference of two days and BACKUP IO waits varies for 1~1.5min. So what needs to be done in the sql server?
June 4, 2017 at 6:47 pm
in waits: ASYNC_NETWORK_IO waits in the database which exceeds 24 hrs ie difference of two days and BACKUP IO waits varies for 1~1.5min(ie difference). So what needs to be done in the sql server ? is it causing slowness in sql database
Server has 6GB ram and showing 25% usage using ESX and 5% CPU usage
June 5, 2017 at 3:06 am
Grant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.
Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
June 5, 2017 at 9:02 am
pranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 6, 2017 at 8:21 am
bmg002 - Monday, June 5, 2017 9:02 AMpranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
I am not able to convince my infrastructure team that memory usage is high and ie why the disk usage is high. %idle time is 90%. Disk is not used always. So they do not like to add additional RAM.
Can anyone time tell me what could be other ways to resolve the performance issue?
June 6, 2017 at 8:28 am
pranabpal - Tuesday, June 6, 2017 8:21 AMbmg002 - Monday, June 5, 2017 9:02 AMpranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
I am not able to convince my infrastructure team that memory usage is high and ie why the disk usage is high. %idle time is 90%. Disk is not used always. So they do not like to add additional RAM.
Can anyone time tell me what could be other ways to resolve the performance issue?
Get rid of the infrastructure team, if they cannot help you in this instance then they are insufficiently well informed to hold the position. Invest the money saved in hardware and a holiday for yourself.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 6, 2017 at 8:29 am
pranabpal - Tuesday, June 6, 2017 8:21 AMbmg002 - Monday, June 5, 2017 9:02 AMpranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
I am not able to convince my infrastructure team that memory usage is high and ie why the disk usage is high. %idle time is 90%. Disk is not used always. So they do not like to add additional RAM.
Can anyone time tell me what could be other ways to resolve the performance issue?
Start by showing them this thread, I suppose. Then get your manager to persuade their manager to give you what you want. Frankly, if they're going to make it so difficult for you to get something as trifling as 16GB of RAM, what's going to happen when you need something significant? Could well be time to look for a new job.
John
June 6, 2017 at 8:37 am
Just send them the memory requirements for SQL Server.
From the MS website:
All other editions: At least 4 GB and should be increased as database size increases to ensure optimal performance.
(https://docs.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server)
Presuming this is a VM, getting them to give you more RAM for a quick demo should be quick and easy on their part. If it is a physical machine, then it is a little bit more work and money, but even then I'd try to fight for it. Get a long running query and execute it to show them how long it takes to complete. Get them to throw more memory at the machine (and change the max memory for the SQL instance to use the newly added memory, but make no other changes) and re-run the query.
Even if you can only get the extra memory short term for that demo, you have then proved that memory will help performance.
Otherwise, I'd recommend going above the infrastructure team. Tell them and their supervisor and your supervisor that "As the DBA, your recommendation and the recommendation from the SQL community is to get more memory in the machine. We are at the minimum recommended memory for SQL Server and performance is only going to degrade as the database grows."
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 6, 2017 at 9:15 am
pranabpal - Tuesday, June 6, 2017 8:21 AMbmg002 - Monday, June 5, 2017 9:02 AMpranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
I am not able to convince my infrastructure team that memory usage is high and ie why the disk usage is high. %idle time is 90%. Disk is not used always. So they do not like to add additional RAM.
Can anyone time tell me what could be other ways to resolve the performance issue?
It's unlikely that you will reveal a "go faster" switch by checking wait states on a server throttled by too little RAM, but there are perhaps some improvements available to you. Have you examined the slowest queries yet? Someone has already mentioned Glenn Berry's scripts. A bunch of them are designed to show you the slowest and most expensive queries. Have a look, judge for yourself if there's any room for improvement. Also, check out indexing. Refactoring your indexes is an opportunity to reduce the memory footprint of your data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 6, 2017 at 9:36 am
ChrisM@Work - Tuesday, June 6, 2017 9:15 AMpranabpal - Tuesday, June 6, 2017 8:21 AMbmg002 - Monday, June 5, 2017 9:02 AMpranabpal - Monday, June 5, 2017 3:06 AMGrant Fritchey - Friday, June 2, 2017 6:01 AMIt sounds like your machine is completely underpowered. You need memory. I/O implies disk, but it includes reads and writes to memory plus reads and writes to disk. If you don't have enough memory, it has to write excess to disk temporarily and then read from disk later, again, adding lots of overhead. You're on a machine that just barely crosses the line for the minimum viable size. Depending on the size of your database, the size and number of transactions, you're just simply exceeding what that minimum possible machine is capable of.Can you tell me based on what parameter you are recommending the solution. I will grateful if you can provide me the parameter name and their values for taking such decision.
6 GB of memory is not a lot for SQL Server, and you also indicated that the max memory for your server is 4 GB. From looking at your first screenschot, your SLQ memory is almost completely utilized. This means that pretty much any query that is pulling in more than 500 MB of data (data here is not just the rows you are asking for, but back end stuff like query plan) will need to be dumped to disk. This will create a lot of I/O which is what you are seeing. The backup I/O waits could be a lot of different things, but likley related to this too.
Give your box more memory and you will see a performance increase.
I am not able to convince my infrastructure team that memory usage is high and ie why the disk usage is high. %idle time is 90%. Disk is not used always. So they do not like to add additional RAM.
Can anyone time tell me what could be other ways to resolve the performance issue?
It's unlikely that you will reveal a "go faster" switch by checking wait states on a server throttled by too little RAM, but there are perhaps some improvements available to you. Have you examined the slowest queries yet? Someone has already mentioned Glenn Berry's scripts. A bunch of them are designed to show you the slowest and most expensive queries. Have a look, judge for yourself if there's any room for improvement. Also, check out indexing. Refactoring your indexes is an opportunity to reduce the memory footprint of your data.
Also, if you are more of a "GUI" guy, then have a look at Steve Stedman's database health monitor. Since I expect you are a DBA, it would be good to know and understand Glenn Berry's scripts (he's a very smart guy), but sometimes it is nice to have a GUI with graphs to show off to the management type people to help explain why things are going badly. It also has a fun little "technical debt" feature (which you can configure) so you can see how many objects might need modifying in the future.
In his tool, I would recommend connecting to the SQL instance that is slow, look at the database that is being slow, and look at the following under real time:
Statistics
Long Running
Needs Parameters
One Time Use Queries
Blocking
As a little aside, how many SQL instances do you have running on that poor little machine? Is there just the 1 (which I am hoping) or are there multiple?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 6, 2017 at 2:01 pm
You've never really put together the set of wait statistics that Gail asked for, let alone gathered them one day and then another day and compared between the two (using the scripts that everyone has been pointing you to over & over). However, if you did, you'd have a very good set of indicators as to what is running slow within the system. You need to be very methodical and careful and take this one step at a time.
Another option that you should look at (after doing what Gail explained) to check for low memory issues is to take a look at the os_memory_buffers as outlined in this article. If you're literally running out of memory, you can see messages there. The query in the article was written for SQL Server 2012. There's a comment that modifies it for 2008R2.
As has already been stated, there isn't a magic "run faster" switch. The vast majority of the time the problems are related directly to the queries, so it's always a good idea to understand how your queries are being written, what they do, how they're called, etc. Following this, it's usually stale or incorrect statistics. It's sometimes poor data structures. Seldom is there a server setting that you can flip and performance improves. However, as has been pointed out repeatedly, you're operating with the extreme bare minimum of resources. Of course that's going to be a problem if there's any kind of significant load.
"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
June 14, 2017 at 11:49 am
| RmNotification | IndicatorsProcess | IndicatorsSystem | RmDateTime | TargetMemory | ReserveMemory | CommitedMemory | SharedMemory | PagesMemory | MemoryUtilization | TotalPhysicalMemory | AvailablePhysicalMemory | TotalPageFile | AvailablePageFile | TotalVirtualAddressSpace | AvailableVirtualAddressSpace | AvailableExtendedVirtualAddressSpace |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 26:05.6 | 4194280 | NULL | 4149056 | 0 | 3547664 | 97 | 6290996 | 549448 | 8095156 | 1999228 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4149056 | NULL | 4149056 | 0 | 3546416 | 97 | 6290996 | 401032 | 8095156 | 1838236 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4179776 | NULL | 4179776 | 0 | 3546416 | 97 | 6290996 | 393356 | 8095156 | 1807420 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4181824 | NULL | 4181824 | 0 | 3546416 | 97 | 6290996 | 393396 | 8095156 | 1805388 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4182848 | NULL | 4182848 | 0 | 3546416 | 97 | 6290996 | 393712 | 8095156 | 1804368 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4183872 | NULL | 4183872 | 0 | 3546416 | 97 | 6290996 | 394180 | 8095156 | 1841788 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4184896 | NULL | 4184896 | 0 | 3546416 | 97 | 6290996 | 394136 | 8095156 | 1840772 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4185920 | NULL | 4185920 | 0 | 3546416 | 97 | 6290996 | 394116 | 8095156 | 1839756 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4186944 | NULL | 4186944 | 0 | 3546416 | 97 | 6290996 | 393800 | 8095156 | 1838744 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4187968 | NULL | 4187968 | 0 | 3546416 | 97 | 6290996 | 393428 | 8095156 | 1837728 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 25:40.6 | 4191040 | NULL | 4191040 | 0 | 3546416 | 97 | 6290996 | 394232 | 8095156 | 1833804 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4149056 | NULL | 4149056 | 0 | 3546416 | 97 | 6290996 | 405460 | 8095156 | 1838236 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4179776 | NULL | 4179776 | 0 | 3546416 | 97 | 6290996 | 393356 | 8095156 | 1807432 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4181824 | NULL | 4181824 | 0 | 3546416 | 97 | 6290996 | 393396 | 8095156 | 1805388 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4182848 | NULL | 4182848 | 0 | 3546416 | 97 | 6290996 | 393712 | 8095156 | 1804368 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4183872 | NULL | 4183872 | 0 | 3546416 | 97 | 6290996 | 394180 | 8095156 | 1841788 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4184896 | NULL | 4184896 | 0 | 3546416 | 97 | 6290996 | 394136 | 8095156 | 1840772 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4185920 | NULL | 4185920 | 0 | 3546416 | 97 | 6290996 | 394116 | 8095156 | 1839756 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4186944 | NULL | 4186944 | 0 | 3546416 | 97 | 6290996 | 393800 | 8095156 | 1838744 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4187968 | NULL | 4187968 | 0 | 3546416 | 97 | 6290996 | 393428 | 8095156 | 1837728 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 25:40.6 | 4191040 | NULL | 4191040 | 0 | 3546416 | 97 | 6290996 | 394232 | 8095156 | 1833804 | 137438953344 | 137421959544 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 04:07.6 | 4194280 | NULL | 4194280 | 0 | 3533096 | 96 | 6290996 | 590632 | 8095156 | 1988480 | 137438953344 | 137421957488 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 03:22.6 | 4194280 | NULL | 4194280 | 0 | 3775760 | 96 | 6290996 | 595168 | 8095156 | 1988472 | 137438953344 | 137421957488 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 2 | 0 | 03:20.6 | 4194280 | NULL | 4194280 | 0 | 3725768 | 96 | 6290996 | 594764 | 8095156 | 1988444 | 137438953344 | 137421957488 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 02:09.6 | 4194280 | NULL | 4033536 | 0 | 3880888 | 98 | 6290996 | 687992 | 8095156 | 2172448 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 01:38.6 | 4033536 | NULL | 4033536 | 0 | 3880888 | 98 | 6290996 | 520824 | 8095156 | 2140004 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 01:38.6 | 4033536 | NULL | 4033536 | 0 | 3880888 | 98 | 6290996 | 618868 | 8095156 | 2171924 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 42:23.6 | 4194280 | NULL | 4090664 | 0 | 3921520 | 98 | 6290996 | 612352 | 8095156 | 2112216 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 41:28.6 | 4090664 | NULL | 4090664 | 0 | 3921520 | 98 | 6290996 | 480108 | 8095156 | 1974684 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 41:26.6 | 4090664 | NULL | 4090664 | 0 | 3925768 | 98 | 6290996 | 391936 | 8095156 | 1971064 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:22.6 | 4103960 | NULL | 4103960 | 0 | 3939040 | 98 | 6290996 | 387152 | 8095156 | 1907636 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 1 | 11:22.6 | 4194280 | NULL | 4103960 | 0 | 3938856 | 98 | 6290996 | 535852 | 8095156 | 2061484 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 11:22.6 | 4194280 | NULL | 4103960 | 0 | 3939040 | 98 | 6290996 | 393172 | 8095156 | 1907636 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:14.6 | 4103960 | NULL | 4103960 | 0 | 3939040 | 98 | 6290996 | 414652 | 8095156 | 1908976 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:14.6 | 4112152 | NULL | 4112152 | 0 | 3939040 | 98 | 6290996 | 393320 | 8095156 | 1890444 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:14.6 | 4103960 | NULL | 4103960 | 0 | 3939040 | 98 | 6290996 | 418232 | 8095156 | 1908976 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:14.6 | 4112152 | NULL | 4112152 | 0 | 3939040 | 98 | 6290996 | 393320 | 8095156 | 1890444 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 46:11.6 | 4194280 | NULL | 4112944 | 0 | 3930408 | 98 | 6290996 | 537284 | 8095156 | 2058188 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 45:51.6 | 4112944 | NULL | 4112944 | 0 | 3930248 | 98 | 6290996 | 421112 | 8095156 | 1900760 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 45:51.6 | 4112944 | NULL | 4112944 | 0 | 3930248 | 98 | 6290996 | 423044 | 8095156 | 1938788 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 30:38.6 | 4194280 | NULL | 4121560 | 0 | 3958568 | 98 | 6290996 | 495260 | 8095156 | 1975172 | 137438953344 | 137421965712 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:02.6 | 4121584 | NULL | 4121560 | 0 | 3981808 | 98 | 6290996 | 381612 | 8095156 | 1857432 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:02.6 | 4144112 | NULL | 4144112 | 0 | 3981808 | 98 | 6290996 | 394156 | 8095156 | 1873132 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:02.6 | 4145136 | NULL | 4145136 | 0 | 3981808 | 98 | 6290996 | 394156 | 8095156 | 1872108 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:02.6 | 4146160 | NULL | 4146160 | 0 | 3981808 | 98 | 6290996 | 393900 | 8095156 | 1871084 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:02.6 | 4121560 | NULL | 4121560 | 0 | 3979920 | 98 | 6290996 | 416388 | 8095156 | 1895544 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:02.6 | 4144112 | NULL | 4144112 | 0 | 3981808 | 98 | 6290996 | 394156 | 8095156 | 1873132 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:02.6 | 4145136 | NULL | 4145136 | 0 | 3981808 | 98 | 6290996 | 394156 | 8095156 | 1872108 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:02.6 | 4146160 | NULL | 4146160 | 0 | 3981808 | 98 | 6290996 | 393900 | 8095156 | 1871084 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:01.6 | 4149240 | NULL | 4149240 | 0 | 3981808 | 98 | 6290996 | 468540 | 8095156 | 1946896 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 30:01.6 | 4157432 | NULL | 4157432 | 0 | 3981808 | 98 | 6290996 | 393696 | 8095156 | 1855472 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:01.6 | 4149240 | NULL | 4149240 | 0 | 3981808 | 98 | 6290996 | 468528 | 8095156 | 1946896 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 30:01.6 | 4157432 | NULL | 4157432 | 0 | 3981808 | 98 | 6290996 | 393696 | 8095156 | 1855472 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 11:49.6 | 4194280 | NULL | 4154904 | 0 | 3987552 | 96 | 6290996 | 533248 | 8095156 | 1941216 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:16.6 | 4154904 | NULL | 4154904 | 0 | 4014096 | 96 | 6290996 | 527716 | 8095156 | 1930628 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:15.6 | 4175392 | NULL | 4175392 | 0 | 4015424 | 96 | 6290996 | 394160 | 8095156 | 1797856 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:15.6 | 4176416 | NULL | 4176416 | 0 | 4015424 | 96 | 6290996 | 394156 | 8095156 | 1796840 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:15.6 | 4183584 | NULL | 4183584 | 0 | 4015424 | 96 | 6290996 | 394760 | 8095156 | 1783944 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:15.6 | 4154912 | NULL | 4154904 | 0 | 4015424 | 96 | 6290996 | 390424 | 8095156 | 1780372 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:15.6 | 4175392 | NULL | 4175392 | 0 | 4015424 | 96 | 6290996 | 394160 | 8095156 | 1797856 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:15.6 | 4176416 | NULL | 4176416 | 0 | 4015424 | 96 | 6290996 | 394156 | 8095156 | 1796840 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:15.6 | 4183584 | NULL | 4183584 | 0 | 4015424 | 96 | 6290996 | 394760 | 8095156 | 1783944 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 18:16.6 | 4194280 | NULL | 4184464 | 0 | 4008384 | 96 | 6290996 | 510852 | 8095156 | 1913964 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 17:20.6 | 4184464 | NULL | 4184464 | 0 | 4008408 | 96 | 6290996 | 393320 | 8095156 | 1787556 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 17:20.6 | 4185488 | NULL | 4185488 | 0 | 4008408 | 96 | 6290996 | 393320 | 8095156 | 1786532 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 17:20.6 | 4184464 | NULL | 4184464 | 0 | 4008408 | 96 | 6290996 | 393320 | 8095156 | 1787556 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 17:20.6 | 4185488 | NULL | 4185488 | 0 | 4008408 | 96 | 6290996 | 393320 | 8095156 | 1786532 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 01:59.6 | 4194280 | NULL | 4083672 | 0 | 3945104 | 98 | 6290996 | 667872 | 8095156 | 2162532 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 00:59.6 | 4083672 | NULL | 4083672 | 0 | 3945096 | 98 | 6290996 | 480524 | 8095156 | 2160252 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 00:58.6 | 4084816 | NULL | 4084768 | 0 | 3945336 | 98 | 6290996 | 380040 | 8095156 | 2018132 | 137438953344 | 137421967768 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:19.6 | 4159728 | NULL | 4159728 | 0 | 4000576 | 98 | 6290996 | 393156 | 8095156 | 1901716 | 137438953344 | 137421973936 | 0 |
| RESOURCE_MEM_STEADY | 0 | 1 | 11:19.6 | 4194280 | NULL | 4159728 | 0 | 4000576 | 98 | 6290996 | 523296 | 8095156 | 2044588 | 137438953344 | 137421973936 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 18:50.6 | 4194280 | NULL | 4159728 | 0 | 3990240 | 98 | 6290996 | 534156 | 8095156 | 2051380 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 18:20.6 | 4159728 | NULL | 4159728 | 0 | 3990096 | 98 | 6290996 | 514220 | 8095156 | 2021864 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 18:20.6 | 4167920 | NULL | 4167920 | 0 | 3990096 | 98 | 6290996 | 393680 | 8095156 | 1882680 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 18:20.6 | 4159728 | NULL | 4159728 | 0 | 3990096 | 98 | 6290996 | 514220 | 8095156 | 2021864 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 18:20.6 | 4167920 | NULL | 4167920 | 0 | 3990096 | 98 | 6290996 | 393680 | 8095156 | 1882680 | 137438953344 | 137421971880 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 25:04.6 | 4194280 | NULL | 4154512 | 0 | 3988104 | 98 | 6290996 | 550252 | 8095156 | 2062292 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 24:28.6 | 4154512 | NULL | 4154512 | 0 | 4003920 | 98 | 6290996 | 397564 | 8095156 | 1905400 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 24:28.6 | 4158608 | NULL | 4158608 | 0 | 4003920 | 98 | 6290996 | 393444 | 8095156 | 1901228 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 24:28.6 | 4189328 | NULL | 4189328 | 0 | 4003920 | 98 | 6290996 | 393568 | 8095156 | 1860592 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 24:28.6 | 4154512 | NULL | 4154512 | 0 | 4003920 | 98 | 6290996 | 400520 | 8095156 | 1905400 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 24:28.6 | 4158608 | NULL | 4158608 | 0 | 4003920 | 98 | 6290996 | 393448 | 8095156 | 1901228 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 24:28.6 | 4189328 | NULL | 4189328 | 0 | 4003920 | 98 | 6290996 | 393568 | 8095156 | 1860592 | 137438953344 | 137421969824 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 11:45.6 | 4194280 | NULL | 4181976 | 0 | 4002032 | 97 | 6290996 | 528344 | 8095156 | 1968744 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:20.6 | 4181976 | NULL | 4181976 | 0 | 4010128 | 96 | 6290996 | 484092 | 8095156 | 1919224 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:20.6 | 4190168 | NULL | 4190168 | 0 | 4010128 | 96 | 6290996 | 394044 | 8095156 | 1833696 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:20.6 | 4191192 | NULL | 4191192 | 0 | 4010128 | 96 | 6290996 | 393860 | 8095156 | 1832672 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 11:20.6 | 4193240 | NULL | 4193240 | 0 | 4010128 | 96 | 6290996 | 394268 | 8095156 | 1830624 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:20.6 | 4181976 | NULL | 4181976 | 0 | 4010128 | 96 | 6290996 | 484092 | 8095156 | 1919224 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:20.6 | 4190168 | NULL | 4190168 | 0 | 4010128 | 96 | 6290996 | 394044 | 8095156 | 1833696 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:20.6 | 4191192 | NULL | 4191192 | 0 | 4010128 | 96 | 6290996 | 393860 | 8095156 | 1832672 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 11:20.6 | 4193240 | NULL | 4193240 | 0 | 4010128 | 96 | 6290996 | 394044 | 8095156 | 1830624 | 137438953344 | 137421965776 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 00:14.6 | 4194280 | NULL | 4154248 | 0 | 3813360 | 94 | 6290996 | 1007212 | 8095156 | 2233484 | 137438953344 | 137421983928 | 0 |
| RESOURCE_MEM_STEADY | 0 | 0 | 00:14.6 | 4194280 | NULL | 4154248 | 0 | 3813296 | 94 | 6290996 | 1007212 | 8095156 | 2233484 | 137438953344 | 137421983928 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 2 | 0 | 00:13.6 | 4194280 | NULL | 4152112 | 0 | 3714424 | 94 | 6290996 | 992048 | 8095156 | 2158192 | 137438953344 | 137421983864 | 0 |
| RESOURCE_MEMPHYSICAL_HIGH | 0 | 1 | 36:10.6 | 3966104 | NULL | 3744624 | 0 | 2650640 | 98 | 6290996 | 442952 | 8095156 | 1826592 | 137438953344 | 137421990988 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 35:09.6 | 3745024 | NULL | 3745024 | 0 | 2396600 | 98 | 6290996 | 401008 | 8095156 | 1801356 | 137438953344 | 137421997156 | 0 |
| RESOURCE_MEMPHYSICAL_LOW | 0 | 2 | 35:09.6 | 3749120 | NULL | 3749120 | 0 | 2396600 | 98 | 6290996 | 393624 | 8095156 | 1789224 | 137438953344 | 137421997156 | 0 |
| RESOURCE_MEMPHYSICAL_LOW
| 0 | 2 | 35:09.6 | 3750144 | NULL | 3750144 | 0 | 2396600 | 98 | 6290996 | 393356 | 8095156 | 1796236 | 137438953344 | 137421997220 | 0 |
June 14, 2017 at 12:03 pm
Output of the query:
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC
I do n' t think there is issue with query. Your view is highly appreciable.
| (No column name) | execution_count | total_logical_reads | last_logical_reads | total_logical_writes | last_logical_writes | total_worker_time | last_worker_time | total_elapsed_time_in_S | last_elapsed_time_in_S | last_execution_time |
| DELETE FROM wen_benutzt_information WHERE umgebung = @P0 AND quelle = @P1 | 8 | 360036 | 45854 | 125 | 0 | 9845576 | 1449107 | 5 | 0 | 39:33.6 |
| SELECT [BEST_NR],[UMGE_NAME],[EING_VORGANGS_ID],[EING_FEHLER_ID],[BEST_SONDERABW],[BEST_ZUSTAND],[BEST_IST_MASTER],[BEST_NUTZE_HOSTVER],[BEST_USER],[BEST_ANGELEGT],[BEST_BASELINE] FROM [BEST_BESTUECKUNG] WHERE [BEST_NR]=@1 AND [BEST_IST_MASTER]=@2 | 72536 | 220685 | 3 | 0 | 0 | 4894225 | 0 | 4 | 0 | 48:33.5 |
| SELECT [BEST_NR],[UMGE_NAME],[SOEL_NAME],[SOEV_ID_BEST],[SOEV_ID_RUECK],[BSTE_ZUSTAND],[BSTE_TOOL] FROM [BSTE_BESTELEM] WHERE [UMGE_NAME]=@1 AND [BEST_NR]=@2 | 14274 | 57628 | 4 | 0 | 0 | 1101751 | 0 | 1 | 0 | 48:33.4 |
| SELECT [BEST_NR],[UMGE_NAME],[EING_VORGANGS_ID],[EING_FEHLER_ID],[BEST_SONDERABW],[BEST_ZUSTAND],[BEST_IST_MASTER],[BEST_NUTZE_HOSTVER],[BEST_USER],[BEST_ANGELEGT],[BEST_BASELINE] FROM [BEST_BESTUECKUNG] WHERE [BEST_NR]=@1 AND [BEST_IST_MASTER]=@2 | 15028 | 45728 | 3 | 0 | 0 | 998918 | 0 | 1 | 0 | 43:18.9 |
| SELECT * FROM wen_benutzt_information WHERE umgebung = @P0 | 2 | 44599 | 44599 | 0 | 0 | 2796539 | 2796539 | 8 | 8 | 44:34.4 |
| SELECT [AUSL_NR],[AUSL_DATUM],[BEST_NR],[AUSL_LINIE],[AUSL_AUSLUMG],[AUSL_ZUSTAND] FROM [AUSL_AUSLIEFERUNG] WHERE [AUSL_NR]=@1 | 19227 | 38454 | 2 | 0 | 0 | 1007425 | 0 | 1 | 0 | 50:11.9 |
| SELECT [AUSL_NR],[AUSA_ABH_VON] FROM [AUSA_AUSL_ABH] WHERE [AUSA_ABH_VON]=@1 | 72 | 35064 | 487 | 0 | 0 | 1731094 | 26005 | 1 | 0 | 50:11.2 |
June 14, 2017 at 12:18 pm
To me, I still say your memory is over utilized for that server. That was what my first post about it was and I stand by that claim still.
You are sitting with 94-98% memory utilization as you can see in what you posted. Your database is memory starved. You need more RAM dedicated to the SQL instance.
Get more RAM. Page 2 of the comments, 5th post says that 4GB for SQL isn't very much.
That and you should post what Gail asked for on page 1 of this thread... 5 pages of comments and still no useable wait stat information being posted...
Might not hurt to hire a consultant, but first thing they will likely suggest is getting more resources onto that box...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply