September 29, 2017 at 1:07 am
any reason for this? After restart it take 1 day for used all memory that fixed (128GB)
every query running very fast about 1 week and then becoming slow with the same routine of database working and more slower for each day (higher waiting time to wait)
September 29, 2017 at 1:42 am
What maintenance do regularly perform on your databases (Index maintenance etc.) and how often?
Thanks
September 29, 2017 at 1:47 am
Also how much memory does your server have? What is your max server memory set to? Is it solely used as a database server?
Thanks
September 29, 2017 at 2:06 am
NorthernSoul - Friday, September 29, 2017 1:42 AMWhat maintenance do regularly perform on your databases (Index maintenance etc.) and how often?Thanks
rebuild index and update stats everyday
September 29, 2017 at 2:10 am
chonlatis.je - Friday, September 29, 2017 2:06 AMNorthernSoul - Friday, September 29, 2017 1:42 AMWhat maintenance do regularly perform on your databases (Index maintenance etc.) and how often?Thanks
rebuild index and update stats everyday
And how much memory does your server have, max memory setting and it is just used as a database server?
Thanks
September 29, 2017 at 2:10 am
NorthernSoul - Friday, September 29, 2017 1:47 AMAlso how much memory does your server have? What is your max server memory set to? Is it solely used as a database server?Thanks
mem 128GB and limit for sql 120GB
September 29, 2017 at 2:30 am
Next time this happens, don't restart SQL Server, because you'll lose all the metadata in your DMVs, which is invaluable for troubleshooting. Make sure you establish a performance baseline and gather wait stats while the server is performing well so that you can compare it all with performance counters and wait stats during poor performance and see what has changed.
John
September 29, 2017 at 2:41 am
John Mitchell-245523 - Friday, September 29, 2017 2:30 AMNext time this happens, don't restart SQL Server, because you'll lose all the metadata in your DMVs, which is invaluable for troubleshooting. Make sure you establish a performance baseline and gather wait stats while the server is performing well so that you can compare it all with performance counters and wait stats during poor performance and see what has changed.John
any query to check DB that can compare now and after performance down and how to monitor it
Thanks
September 29, 2017 at 2:55 am
This is a bigger subject than it's possible to cover in a forum topic, I'm afraid. You need to read about which performance counters to capture, and how to capture them. You'll need to know about which wait stats to capture and which to ignore, and how to interpret them. You'll probably want to know how to read an execution plan, as well. Below are a couple of articles that will get you started. If you can persuade the beancounters, consider implementing monitoring software such as SolarWinds or SQLMonitor. If you feel really out of your depth on this, it may be best to get someone in to help you set it all up, teach you the basics and write some documentation.
http://www.sqlservercentral.com/articles/baselines/94656/
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
John
September 29, 2017 at 5:04 am
chonlatis.je - Friday, September 29, 2017 2:06 AMrebuild index and update stats everyday
You rebuild the indexes (which incidetally rebuilds the stats with a full scan)
Then you rebuild the stats which uses a default sample
Is this correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2017 at 6:05 am
Perry Whittle - Friday, September 29, 2017 5:04 AMchonlatis.je - Friday, September 29, 2017 2:06 AMrebuild index and update stats everydayYou rebuild the indexes (which incidetally rebuilds the stats with a full scan)
Then you rebuild the stats which uses a default sampleIs this correct?
i used maintenance plan
September 29, 2017 at 6:20 am
chonlatis.je - Friday, September 29, 2017 6:05 AMPerry Whittle - Friday, September 29, 2017 5:04 AMchonlatis.je - Friday, September 29, 2017 2:06 AMrebuild index and update stats everydayYou rebuild the indexes (which incidetally rebuilds the stats with a full scan)
Then you rebuild the stats which uses a default sampleIs this correct?
i used maintenance plan
then there lies your problem quite possibly
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 29, 2017 at 8:45 am
Perry Whittle - Friday, September 29, 2017 6:20 AMchonlatis.je - Friday, September 29, 2017 6:05 AMPerry Whittle - Friday, September 29, 2017 5:04 AMchonlatis.je - Friday, September 29, 2017 2:06 AMrebuild index and update stats everydayYou rebuild the indexes (which incidetally rebuilds the stats with a full scan)
Then you rebuild the stats which uses a default sampleIs this correct?
i used maintenance plan
then there lies your problem quite possibly
What Perry is saying is that rebuilding indexes also rebuilds the stats with a full scan. Your rebuilding statistics is not only a waste of resources because it isn't needed, but it also results in poorer statistics than you had from the index rebuild. So, don't rebuild statistics after rebuilding indexes.
BTW, I think maintenance plans are better than nothing, but not much better. They're a brute-force approach that rebuilds everything whether it needs it or not. I'd suggest you look into one of the alternatives. To start, look at Ola Hallengren's solution and Minion. They aren't so wasteful as a maintenance plan.
October 1, 2017 at 6:33 pm
Stop rebuilding/reorganizing indexes. It's a waste of time, clock cycles, and disk space.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply