Microsoft® SQL Server™ 2005 Administrator's Companion

Creating a Performance Data Warehouse

As we’ve seen in this chapter, DMVs present a very powerful means for gaining insights into the operations of the server and analyzing performance problems. However, the dynamic nature of DMVs can limit their usefulness in certain situations. Consider a case where the users of an application complain of occasional poor transaction response times that occur at random and especially after midnight. You, the database administrator, know that SQL Server is possibly not performing optimally but cannot effectively investigate the problem because by the time the users encounter the problem and tell you about it the next morning, the problem has stopped occurring. Now, given that the problem has resolved itself by ...