Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 31. Dynamic Management Views > Creating a Performance Data Warehouse

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 morning and the information presented by DMVs is transient in nature, the data does not accurately represent the state of the server when the problem occurred, rendering it of limited use in investigating the problem the following morning.

To address this issue, you can consider creating a performance data warehouse. A performance data warehouse is essentially a historical archive of periodic snapshots of the DMV data of interest. You can archive the data from as many DMVs as you want, at the required frequency, and in a level of detail you believe will be most useful for analyzing the performance of your workload. For example, you can choose to capture the data of all DMVs every 60 minutes, or you can capture the information for just a few columns of a handful of DMVs every 60 seconds.


  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint