May 31, 2017 at 4:10 am
Hi,
try running the script on here to provide Gail with the information requested. Run it during your slowest period, if you have one. Also, read through the references, at least the beginner's ones. This will help you with understanding any advice you're givin on the results, and why Gail has (repeatedly) asked for just this, and help you understand how to do some basic troubleshooting in the future and grow your skills.
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
Also, if you're autogrowing, turn on instant file initialisation. This won't be your main problem, in all probability, but it's worth doing anyway.
Instant File Initialisation
Also, if you could run sp_blitz from this link and show us the output
sp_blitz
Finally, if you're not terribly experienced as a SQL Server DBA (or just want a refresher / some tips) this series is likely to help.
SQL Skills Accidental DBA series
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 31, 2017 at 7:19 am
NorthernSoul - Wednesday, May 31, 2017 1:26 AMpranabpal - Wednesday, May 31, 2017 1:16 AMMax memory is set to 4096 MB.Auto growth is set to 10%.Manually shrinking of the data base is being done.RAM is 6GB and in task manager it is showing 50% is usage but the windows admin is saying RAM usage is 25%.
10% of the CPU is being utilised
What wait type is causing the problem . What is solution for this? any kind of assistance is really appreciable.
Thanks all of you for responding and viewing the use
Two things immediately stand out. First don't set your autogrowth to be a percentage, set it at a fixed size e.g 512MB. Secondly why are you shrinking your database? Please don't do this as it is unnecessary and will fragment you indexes and cause performance problems.
Thanks
I am very sorry i have set 100MB not percentage. Because percentage growth is not recommended. Appology for that .
May 31, 2017 at 7:24 am
pranabpal - Wednesday, May 31, 2017 7:19 AMNorthernSoul - Wednesday, May 31, 2017 1:26 AMpranabpal - Wednesday, May 31, 2017 1:16 AMMax memory is set to 4096 MB.Auto growth is set to 10%.Manually shrinking of the data base is being done.RAM is 6GB and in task manager it is showing 50% is usage but the windows admin is saying RAM usage is 25%.
10% of the CPU is being utilised
What wait type is causing the problem . What is solution for this? any kind of assistance is really appreciable.
Thanks all of you for responding and viewing the use
Two things immediately stand out. First don't set your autogrowth to be a percentage, set it at a fixed size e.g 512MB. Secondly why are you shrinking your database? Please don't do this as it is unnecessary and will fragment you indexes and cause performance problems.
Thanks
I am very sorry i have set 100MB not percentage. Because percentage growth is not recommended. Appology for that .
You might want to consider setting it at a higher figure than 100MB so there will be less growth events.
And why do you shrink your database? As I said before please DO NOT do this, it is completely unnecessary and will cause performance problems.
Thanks
May 31, 2017 at 7:26 am
GilaMonster - Wednesday, May 31, 2017 3:40 AMpranabpal - Wednesday, May 31, 2017 1:16 AMWhat wait type is causing the problem . What is solution for this?I don't know, because you still haven't posted what I've asked for multiple times.
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)
Can you guide me with that kind of script - so I can provide you the relevant data.I have shared the wait which are taking from than 1hrs. Your assistance is highly appreciated for the script to the pull the required data.
I am using this query to extract the data:
select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms desc
Can you provide me your query for the data which you are looking for.
Thanks a lot
May 31, 2017 at 8:25 am
GilaMonster - Wednesday, May 31, 2017 3:40 AMpranabpal - Wednesday, May 31, 2017 1:16 AMWhat wait type is causing the problem . What is solution for this?I don't know, because you still haven't posted what I've asked for multiple times.
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)
GilaMonster - Wednesday, May 31, 2017 3:40 AMpranabpal - Wednesday, May 31, 2017 1:16 AMWhat wait type is causing the problem . What is solution for this?
I don't know, because you still haven't posted what I've asked for multiple times.
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)
Can you guide me with that kind of script - so I can provide you the relevant data.I have shared the wait which are taking from than 1hrs. Your assistance is highly appreciated for the script to the pull the required data.
I am using this query to extract the data:
select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms desc
Can you provide me your query for the data which you are looking for.
Thanks a lot
May 31, 2017 at 8:28 am
First, that is not much memory for a production SQL Server. I believe (but could be wrong) that it is recommended to have 4 GB dedicated to the OS, and presuming this is a dedicated SQL Instance running only SQL Server (ie no SSAS, SSRS or SSIS), allocate the rest to SQL. But 2 GB for SQL is going to perform poorly.
Second, did you read the link Gail provided?
Third, stop manually shrinking the database. Growing is a slow operation. I'm glad you don't have auto shrink turned on on a production box. That is a big mistake.
And lastly, I'll re-iterate the questions you didn't answer as they are relevant:
2 - when did the slowdown first become noticable? Has it always been slow or is it gradually getting worse? (with 6 GB of memory total, 4 GB dedicated to SQL, I expect it has always been slow)
3 - how frequently do you update statistics and rebuild/reorganize indexes? (index rebuild/reorganize is really only important if you have slow disk)
5 - how frequently does it autogrow and (heaven forbid) autoshrink?
7 - are the slow applications in house or 3rd party?
7b - if they are 3rd party, have you contacted support?
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.
May 31, 2017 at 8:33 am
pranabpal - Wednesday, May 31, 2017 7:26 AMCan you guide me with that kind of script - so I can provide you the relevant data.I have shared the wait which are taking from than 1hrs. Your assistance is highly appreciated for the script to the pull the required data.I am using this query to extract the data:
select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms descCan you provide me your query for the data which you are looking for.
Thanks a lot
I didn't ask for the waits taking longer than an hour. I asked for the wait times *in a one hour interval*, and I pointed you at Glenn Berry's scripts which filter out the useless waits.
and, from a couple of hours ago:
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)
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
June 1, 2017 at 5:32 am
GilaMonster - Wednesday, May 31, 2017 8:33 AMpranabpal - Wednesday, May 31, 2017 7:26 AMCan you guide me with that kind of script - so I can provide you the relevant data.I have shared the wait which are taking from than 1hrs. Your assistance is highly appreciated for the script to the pull the required data.I am using this query to extract the data:
select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms descCan you provide me your query for the data which you are looking for.
Thanks a lotI didn't ask for the waits taking longer than an hour. I asked for the wait times *in a one hour interval*, and I pointed you at Glenn Berry's scripts which filter out the useless waits.
and, from a couple of hours ago:
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)
Is this the query to get the useful waits in the interval of of 1 hr? Please confirm.
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
June 1, 2017 at 6:15 am
pranabpal - Thursday, June 1, 2017 5:32 AMGilaMonster - Wednesday, May 31, 2017 8:33 AMpranabpal - Wednesday, May 31, 2017 7:26 AMCan you guide me with that kind of script - so I can provide you the relevant data.I have shared the wait which are taking from than 1hrs. Your assistance is highly appreciated for the script to the pull the required data.I am using this query to extract the data:
select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms descCan you provide me your query for the data which you are looking for.
Thanks a lotI didn't ask for the waits taking longer than an hour. I asked for the wait times *in a one hour interval*, and I pointed you at Glenn Berry's scripts which filter out the useless waits.
and, from a couple of hours ago:
Get a script that filters out the useless waits
Save the wait information to a table every hour
Work out the differences between two sample periods (in busy times) and post that (the top 25 useful ones, not the whole list)Is this the query to get the useful waits in the interval of of 1 hr?
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
Please confirm.SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
Yes, except for the "Please confirm" in the middle of it, that will get your waits and eliminate the waits that are not important. However, it won't capture the information at a one hour interval. To do that you need to run this, yourself, once an hour. Now, you can automate that, and you should. Use SQL Agent to set up a job that runs this once an hour. Capture the data in a table so that you can compare different intervals to one another. That way, you can tell what waits are causing you issues. Once you have knowledge of the waits, you can figure out what's up with your system.
Most of the time, you'll be seeing that the core issue is poorly written queries, poorly structured databases, insufficient or incorrect indexes, bad statistics maintenance, or all of the above. However, in your case, it does sound like you're running it on a very tiny system. 6gb of RAM is 10gb less than my laptop. You might just need more hardware, in addition to addressing that list of issues I mentioned.
"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 1, 2017 at 7:14 am
Just use the first part of it, there's no need for the complex portion.
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)
Now, that will get the total waits with the useless ones ignored. Run that every hour and insert the results into a table, then calculate the difference in wait stats between two samples.
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
June 1, 2017 at 12:21 pm
GilaMonster - Thursday, June 1, 2017 7:14 AMJust use the first part of it, there's no need for the complex portion.
SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK')
)Now, that will get the total waits with the useless ones ignored. Run that every hour and insert the results into a table, then calculate the difference in wait stats between two samples.
These are 3 high value waits
| wait_type | WaitS in Secs |
| DIRTY_PAGE_POLL | 223287.847 |
| HADR_FILESTREAM_IOMGR_IOCOMPLETION | 223284.348 |
| QDS_SHUTDOWN_QUEUE | 223264.158 |
| SP_SERVER_DIAGNOSTICS_SLEEP | 223200.172 |
| DIRTY_PAGE_POLL | 202670.01 |
| HADR_FILESTREAM_IOMGR_IOCOMPLETION | 202666.43 |
| QDS_SHUTDOWN_QUEUE | 202623.78 |
| SP_SERVER_DIAGNOSTICS_SLEEP | 202623.78 |
So now what is the resolution for these waits?
June 1, 2017 at 1:27 pm
I 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.
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
June 1, 2017 at 8:57 pm
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.
June 2, 2017 at 4:54 am
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.
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.
June 2, 2017 at 5:01 am
32GB of RAM for your server would cost roughly 50-100 bucks and would most likely have a dramatic effect of performance.
How much time have you spent on this exercise?
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
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply