I have a SQL 2014 with which I have some performance issues, mostly read/disk related. the system is VMWare/SSD SAN. Both OS and SQL have all available patches. The environment is mainly used for DSS characterized by large queries, an average of 80% read and 20% write. The data volume is 6 TB with 256 MB memory (90% allocated to SQL). I am just testing disk speeds (which proves most of the time the bottleneck) by running SELECT query with an index hint to force the queries to do a clustered index scan or table scan of 2 relatively large tables (the 2 selects will use all virtual CPUs). Testing the disk throughput shows 900 Mb/s right after VM restarts. After 1 day or so the disk throughput drops down to 300 Mb/s (I am testing only when there is no any other activity on the system for consistent results). I restarted a couple of times and same behavior.
The part that I do not understand is why there is such a drop?
Thank you