Đang chuẩn bị liên kết để tải về tài liệu:
SQL Server MVP Deep Dives- P17

SQL Server MVP Deep Dives- P17: Each year Microsoft invites all the MVPs from every technology and country to Redmond for an MVP Summit—all top secret—“don’t tweet what you see!” During the MVP Summit, each product team holds a series of presentations where they explain their technologies, share their vision, and listen to some honest feedback. | 594 Chapter 47 How to use Dynamic Management Views queries that are relatively inexpensive for individual executions but are called very frequently which makes them expensive in aggregate or you may have individual queries that are more expensive CPU-wise but are not called as often. Looking at total worker time is a reliable method for finding the most expensive queries from an overall CPU perspective. Another similar DMV query shown in listing 6 sorts by average worker time. This will let you find expensive CPU queries that may be easier to improve at the database level with standard database tuning techniques. Listing 6 Finding expensive stored procedures sorted by average worker time -- Get Top 20 executed SP s ordered by Avg worker time CPU pressure SELECT TOP 20 qt.text AS SP Name qs.total_worker_time qs.execution_count AS AvgWorkerTime qs.total_worker_time AS TotalWorkerTime qs.execution_count AS Execution Count ISNULL qs.execution_count DATEDIFF Second qs.creation_time GetDate 0 AS Calls Second ISNULL qs.total_elapsed_time qs.execution_count 0 AS AvgElapsedTime qs.max_logical_reads qs.max_logical_writes DATEDIFF Minute qs.creation_time GetDate AS Age in Cache FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text qs.sql_handle AS qt WHERE qt.dbid db_id -- Filter by current database ORDER BY qs.total_worker_time qs.execution_count DESC Finding I O pressure in SQL Server Most large-scale SQL Server 2005 2008 deployments sooner or later run into I O bottlenecks. This happens for several reasons. First systems engineers often just think about CPU and RAM when sizing big database servers and neglect the I O subsystem. Second many DBAs are unable to completely tune the SQL Server workload to minimize excessive I O requirements. Finally there are often budgetary issues that prevent the acquisition of enough I O capacity to support a large workload. Whatever your situation it helps if you know how recognize and measure signs of I O pressure on SQL .