Sql Server 2005 provides Dynamic Management Views(DMV) that can help save you a lot of work. One excellent and helpful DMV is sys.dm_exec_query_stats. In previous version of SQL Server to find out the highest impact queries on CPU or IO in system, you had to walk through a long set of analyses steps including getting aggregated information out of the data you collected from profiler.
With sys.dm_exec_query_stats, you can figure out many combinations of query analyses by a single query. Here are some of the examples;
•
Find queries suffering most from blocking –
(total_elapsed_time – total_worker_time)
•
Find queries with most CPU cycles –
(total_worker_time)
•
Find queries with most IO cycles –
(total_physical_reads + total_logical_reads + total_logical_writes)
•
Find most frequently executed queries –
(execution_count)
•
You can find more information on how to use dynamic management views for performance troubleshooting in the “SQL Server 2005 Waits and Queues” whitepaper located at:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx
Performance of the SQL MERGE vs. INSERT/UPDATE
-
MERGE is designed to apply both UPDATE and INSERTs into a target table from
a source table. The statement can do both at once, or simply do INSERTs or
on...
Posts
No comments:
Post a Comment