I am running SQL Server 2012 on windows azure VM Windows server 2012.
This is a live Database, about 250GB, with high usage.
I applied some best practice articles for the temp-db long ago, and also split my main database to its own physical drive for the data file and for the log file.
In the past couple of days i encountered extreme slow feeling in the VM (though the databases response seems fast and good).
The VM is running on an A7 instance (8 cores 56GB Ram) and from performance monitor it seems that the bottle neck is on the Hard drive, especially the one that is running the mdf file of the main database.
I researched the web a bit, and decided to create another file group (had only a primary and only the mdf), I attached another 7 hard drives to the machine, so that i now have 4 files (hard drive for each..) for the primary group and 4 files (hard drive for each..) for a secondary group that i defined as default. Afterwards i rebuild the indexes on most of the important tables (which caused me a downtime of about 15 minutes for one big important table…).
I expected that these actions would dramaticly effect the machine's slowness yet no dramatic change is felt, also the drives that are running the ndf's and the mdf's are still having pretty high Avg. disc sec/read write (0.4 avarage for read and 0.01-0.02 write)
Am i missing something ?
EDIT:
Ok,
So after about 24 hours from the changes i implemented it seems to be working well, the disk read/write seems better and the machine is running fast.
I honestly can't explain why it took about 24 hours yet i'm fine with the fact that it worked 🙂
Best Answer
You miss basic steps. Obviously that isa new problem. Do not throw hardware at the issue until you ahve found out why. May be right, may more likely be crappy sql/missing indices. Use the standard dba approach and find out what is using so much IO. It is not like ther are not tools in Server 2012 that make that "Look at one activity monitor as first step".