Brent Ozar had a redgate whitepaper on 6 dba problems and what to do. The problems are not trivial, but the more important concept is how to approach determine the problems.
First, a simple database with two indexes slows way do. fine, run the appropriate query with check execution plan on. Despite the indexes, the plan say table scan, not index seek. Apparently, for bulk loading or an ETL job, someone remove or here disabled the index. Fine, but they didn’t turn it back on afterwards. there are a couple of approaches. check for ANY changes in the schema. Count the number of disabled indexes and alert if that changes
The server is configured to use a certain amount of memory for SQL, but it is using less. This might happen if OS stuff is overflowing the 4GB or 10% RM rule. The obvious culprit is remote desktop to use SSMS or to copy files/download a service pack. the file will be cached in RAM. eventually it will be purges, we can use an undocumented utility from SysInternals, or we can restart the server. Best to copy from from another machine via a share.
Initially, we have great performance from our SSD disk (fusion IO) but overtime, performance degrades. Best performance is to a clean partition. and as it becomes partially full. we might have to over-write deleted data – which is SLOW. And the utilities also use up a lot of drive space OOTB (under provisioning) . Monitor the speed and when it starts getting slower, copy from one area to another freshly initialized disk so that all the writable space is clean.
Tempdb should be in multiple files. For a single file, all activity is coordinated by the Shared Global Allocation Map (SGAM), and we can have major contention on IT when we have to actively write to tempdb. More files, more SGAM pages and less contention. We need the right number and they need to be the same size.
Page splits occur when a page, for a CI or for the index itself, is full. Page split are IO intensive and lead to fragmentation. More free space, less data in memory per page. and more pages read to get the same data and If we are going to update a table or an index is going to be changing a lot, we might need mroe free space when the page is created (or the page is split). The fill factor does not tell you anything about how full a page will become, only what happens when it is created, rebuilt or reorganized, and can be done on CI or individual NCIs.
As other users or programs modify daatbases (think application upgrades) the number of alerts and warning that are logged might change. You can monitor the number of alerts and warnings over time.