Performance By Design

This is the 2nd post in a series on SQL Server performance monitoring, emphasizing the use of key Dynamic Management View. The series starts here : OS Waits  The consensus among SQL Server performance experts is that the best place to start looking for performance problems is the OS Wait stats from the sys.dm_os_wait_stats DMV. Whenever it is running, the SQL Server database Engine dispatches worker threads from a queue of ready tasks that it services in a round-robin fashion. (There is evidently some ordering of the queue based on priority –background tasks with lower priority that defer to foreground tasks with higher priority.) The engine records the specific wait reason for each task waiting for service in the queue and also accumulates the Wait Time (in milliseconds) for each Wait reason. These Waits and Wait Time statistics accumulate at the database level and reported via the sys.dm_os_wait_stats DMV. Issuing a Query like the following on one of my SQL Server test...

Let’s look at an example of what happens when Hyper-V machine memory is over-committed and the Dynamic Memory Balancer attempts to adjust to that condition. The scenario begins with five Windows guest machines running with the following dynamic memory settings: Minimum Maximum WIN81TEST1 2 GB 6 GB WIN81TEST2 2 GB 6 GB WIN81TEST3 512 MB 6 GB WIN81TEST4 512 MB 6 GB WIN81TEST5 512 MB 8 GB In the scenario, four of the five guest machines are doing actual work. Only guest machine 5 is active, as illustrated in the screen shot of the Hyper-V Manager console, shown in Figure 17. The Hyper-V Host used in the test contains 12 GB of RAM. The remaining guest machines are idle initially, which allows Hyper-V to reduce the amount of machine memory allocated to guests 3-4 to near their minimum values, using the ballooning technique we discussed.  Figure 17. The Hyper-V...