2

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 :)

  • What does this mean, " extreme slow feeling in the VM"? – Shawn Melton Sep 9 '14 at 16:22
  • I don't believe Azure (or whatever they're calling it this week) gives any guarantee whatsoever on I/O performance - you're kind of at the whim at whatever subsystem is sitting under there (and this can change at their discretion). Have you filed a ticket with your findings? – Aaron Bertrand Sep 9 '14 at 16:22
  • @Shawn Yes I meant to comment on that too - lag in an RDP session (e.g. slow response to clicks) may have absolutely nothing at all to do with I/O performance. – Aaron Bertrand Sep 9 '14 at 16:22
  • i will file a ticket, tried to solved this without their support, yet i don't think this is an azure RDP lag, since a couple of months ago i had the same "feeling" and after consulting their support i applied some best practice to the tempdb tuning and it solved everything... – Matan L Sep 9 '14 at 16:28
  • 2
    Or maybe after consulting their support they also did things on their end to temporarily relieve the problem... – Aaron Bertrand Sep 9 '14 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.