AnsweredAssumed Answered

SQLserver sizing - best practices?

Question asked by Roger Palmen on Aug 13, 2019

When it comes to sizing (AND the related monitoring of growth!) an SQLserver database used for PI AF, there are is just a single guideline: the sizing sheet: 

Now there is more to the sizing of SQL than that. E.g. Sizing AF database including Audit Trail  is an example where the SQL sizing is just a starting point.

Now i also had quite some issues with SQLserver sizing, where e.g. the indication was made by techsupport that SQLserver RAM should be at least approx. 60% of the PIFD database size (note that the sizing sheet does not comply with that).


So my first question: what do you guys out there use to size your SQL database?


This question becomes even more important as it turned out that if the SQLserver is slowed down due to incorrect sizing, the stability of the system is impacted, loosing data. I expected the system to slow down, but not to start loosing data from e.g. Analysis due to timeouts on SQLserver. So that brings the second part into view: if system stability is dependant on SQLserver performance, then monitoring the performance of SQLserver is also very important.


So my second question: what are the metrics to use to monitor SQLserver properly? After all we want to know if the system is running stable or not..




PS: With regards to sizing, the trend is that it's getting worse. E.g. there used to be some pointers for PI Vision in the sizing sheet, but those have also been removed in later versions. So