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 Redmondfor an MVP Summit—all top secret—“don’t tweet what you see!” During theMVP Summit, each product team holds a series of presentations where they explaintheir technologies, share their vision, and listen to some honest feedback.
Nội dung trích xuất từ tài liệu:
SQL Server MVP Deep Dives- P17 594 CHAPTER 47 How to use Dynamic Management Views queries that are relatively inexpensive for individual executions but are called very fre- quently (which makes them expensive in aggregate), or you may have individual que- ries 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 over- all 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 SPs 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 bot- tlenecks. 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 Server 2005/2008. One thing you can do to help reduce I/O pressure in general is to make sure you’re not under memory pressure, which will cause added I/O pressure. We’ll look at how to detect memory pressure a little later. For large SQL Server 2005/2008 deployments, you should make sure you’re run- ning a 64-bit edition of SQL Server (so you can better use the RAM that you have), and you should try to get as much RAM as you can afford or will fit into the database server. Having sufficient RAM installed will reduce I/O pressure for reads, and will allow SQL Server to issue checkpoints less frequently (which will tend to minimize write I/O pressure).Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Finding I/O pressure in SQL Server 595 The DMV queries in listings 7 through 11 are useful for measuring signs of I/O pressure. You’ll want to run the query in listing 7 multiple times, because the results will rap- idly change on a busy system. Don’t get too excited by a single high number. If you see consistently high numbers over time, then you have evidence of I/O pressure. Listing 7 Checking for I/O pressure -- Check for Pending I/O (lower is better) SELECT pending_disk_io_count FROM sys.dm_os_schedulers The query in listing 8 can help you identify which data and log files are causing the highest I/O waits. For example, perhaps you have a transaction log file on a slower RAID 5 array or LUN (which isn’t a good idea). This query will help prove that the log file is causing user waits. Listing 8 Identifying the highest I/O waits -- Avg I/O Stalls (Lower is better) SELECT database_id, file_id , io_st ...