July 17, 2018 at 5:52 pm
database performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.
July 17, 2018 at 6:44 pm
saptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.
If it's a restart of the server and not just the instance, check for memory issues on the server itself. You can setup data collector sets in Performance Monitor and schedule those. You also need to check your memory configurations the SQL Server instance(s).
It sounds like you have an overall poor performing SQL Server and will need a good understanding how all of this works. Posting for how to fix a slow system usually won't end up fixing everything as you would like. Books are written on the subject. There is no one quick fix or button to push or tool to run that will address everything. Usually you will need to spend some time really understanding the configuration of the instance, the database configuration and how it's used, the queries and query plans, how you can improve the indexes, which tables need the most work, what other maintenance should be run (statistics), etc. It's a lot. If you have no idea where to start then considering hiring someone to come in and help out for a bit.
Sue
July 17, 2018 at 9:23 pm
saptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.
Get yourself a copy of sp_Blitz (Brent Ozar) and a copy of sp_WhoIsActive (Adam Machanic) and start troubleshooting your queries. You might also want to start looking for memory leaks in front-end code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2018 at 1:33 am
Sue_H - Tuesday, July 17, 2018 6:44 PMsaptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.If it's a restart of the server and not just the instance, check for memory issues on the server itself. You can setup data collector sets in Performance Monitor and schedule those. You also need to check your memory configurations the SQL Server instance(s).
It sounds like you have an overall poor performing SQL Server and will need a good understanding how all of this works. Posting for how to fix a slow system usually won't end up fixing everything as you would like. Books are written on the subject. There is no one quick fix or button to push or tool to run that will address everything. Usually you will need to spend some time really understanding the configuration of the instance, the database configuration and how it's used, the queries and query plans, how you can improve the indexes, which tables need the most work, what other maintenance should be run (statistics), etc. It's a lot. If you have no idea where to start then considering hiring someone to come in and help out for a bit.Sue
Sorry not server. we restarted just the instance only. Please advise the steps to check the memory configurations in the SQL Server instance
July 18, 2018 at 1:41 am
saptek9 - Wednesday, July 18, 2018 1:33 AMSue_H - Tuesday, July 17, 2018 6:44 PMsaptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.If it's a restart of the server and not just the instance, check for memory issues on the server itself. You can setup data collector sets in Performance Monitor and schedule those. You also need to check your memory configurations the SQL Server instance(s).
It sounds like you have an overall poor performing SQL Server and will need a good understanding how all of this works. Posting for how to fix a slow system usually won't end up fixing everything as you would like. Books are written on the subject. There is no one quick fix or button to push or tool to run that will address everything. Usually you will need to spend some time really understanding the configuration of the instance, the database configuration and how it's used, the queries and query plans, how you can improve the indexes, which tables need the most work, what other maintenance should be run (statistics), etc. It's a lot. If you have no idea where to start then considering hiring someone to come in and help out for a bit.Sue
Sorry not server. we restarted just the instance only. Please advise the steps to check the memory configurations in the SQL Server instance
What is you Max Server Memory set to (right click on your instance and look at Properties --> Memory)? How much memory does the server have? How many instances are on the server? Is it solely used as a database server or do you have anything else running on there?
Thanks
July 18, 2018 at 6:32 am
Jeff Moden - Tuesday, July 17, 2018 9:23 PMsaptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.Get yourself a copy of sp_Blitz (Brent Ozar) and a copy of sp_WhoIsActive (Adam Machanic) and start troubleshooting your queries. You might also want to start looking for memory leaks in front-end code.
Related to Jeff's suggestion, here's a link to a video describing how to effectively use sp_WhoIsActive and the sp_Blitz family of stored procedures:
https://www.brentozar.com/archive/2018/06/video-what-to-do-when-sql-server-is-slow/
If you want some guidelines on how to set the SQL Server Max Memory here is a good reference to start with:
https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
July 18, 2018 at 11:55 am
saptek9 - Wednesday, July 18, 2018 1:33 AMSue_H - Tuesday, July 17, 2018 6:44 PMsaptek9 - Tuesday, July 17, 2018 5:52 PMdatabase performance is getting better after restart the server and slowing down after a while even though i have index rebuild maintenance plan scheduled weekly once. Please suggest me the solution to fix this issue.If it's a restart of the server and not just the instance, check for memory issues on the server itself. You can setup data collector sets in Performance Monitor and schedule those. You also need to check your memory configurations the SQL Server instance(s).
It sounds like you have an overall poor performing SQL Server and will need a good understanding how all of this works. Posting for how to fix a slow system usually won't end up fixing everything as you would like. Books are written on the subject. There is no one quick fix or button to push or tool to run that will address everything. Usually you will need to spend some time really understanding the configuration of the instance, the database configuration and how it's used, the queries and query plans, how you can improve the indexes, which tables need the most work, what other maintenance should be run (statistics), etc. It's a lot. If you have no idea where to start then considering hiring someone to come in and help out for a bit.Sue
Sorry not server. we restarted just the instance only. Please advise the steps to check the memory configurations in the SQL Server instance
Not snipping here... it would appear that you good folks may be a bit over your head on this problem. You might want to get some professional help to get you out of the woods. Brent Ozar's (https://www.brentozar.com/sql-critical-care/) is very good at fixing such things and then teaching you how to fix them in the future. You might want to give them a shout. Yes, it'll cost some money but will definitely be worth it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply