ExERCISE 13.3
R
eview u
Seful DMVS
Take a look at the following DMVs to become familiar with the contents:
sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_sql_text
sys.dm_exec_query_stats
sys.dm_os_wait_stats
sys.dm_db_index_usage_stats
sys.dm_db_index_operational_stats
sys.dm_db_missing_index_details
Partitioning
Organizations collect more data and retain data for longer than ever before. The phenomenal growth of the storage manufacturing industry over the past 10 years is a testament to continually increasing data collection. Given adequate capacity, storing large quantities of data within SQL Server is no big problem, until we need to retrieve some data or perform any maintenance. New challenges arise from retrieving single rows or range searches of multiterabyte databases, while maintaining good response times.Partitioning was first available in SQL Server 7.0, although in different versions this application logic was required to determine the partition holding a specific row. In SQL Server 2000 it was possible to define a view that unified the data and in SQL Server 2005 table partitions were completely transparent to applications. SQL Server 2008 Enterprise edition provides the next generation of table and index partitioning, which introduces a round-robin thread model to satisfy queries accessing multiple partitions. Additionally, SQL Server 2008 includes new level of lock escalation, which means locks can escalate from row or page locks to partition locks. This differs from SQL Server 2005, where row or page locks could be escalated directly to table locks.
Horizontal Partitioning
Horizontal Partitioning involves dividing a large table into a number of smaller tables, each containing all columns for a subset of rows. Dividing rows into separate tables means each table is much smaller and access times are typically more efficient,
