Files are at  http://cid-614f86be9e3d997e.skydrive.live.com/browse.aspx/Public/SQL%202008%20Performance%20Scripts

Mete Dönmez  |  Technical Account Manager |  Microsoft Services

È +90 533 769 32 24   |   www.microsoft.com/services

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
Select 'Total Entries'
select COUNT(*) As NumberOfPlans, SUM(size_in_bytes) / 1024.0/1024.0 AS SizeInMB
from sys.dm_exec_cached_plans
 
Select 'Total Entries Broken Out by Type'
select COUNT(*) As NumberOfPlans, SUM(size_in_bytes) / 1024.0/1024.0 AS SizeInMB,
  objtype
from sys.dm_exec_cached_plans
group by objtype
 
Select 'Those only used once'
select COUNT(*) As NumberOfPlans, SUM(size_in_bytes) / 1024.0/1024.0 AS SizeInMB
from sys.dm_exec_cached_plans
where usecounts = 1
 
Select 'Those only used once Broken Out by Type'
select COUNT(*) As NumberOfPlans, SUM(size_in_bytes) / 1024.0/1024.0 AS SizeInMB,
  objtype
from sys.dm_exec_cached_plans
where usecounts = 1
group by objtype

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
-- Average Stalls per Read, Write and Total IO
---- adding 1.0 to avoid division by zero errors
select database_id, file_id
  ,io_stall_read_ms
  ,num_of_reads
  ,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
  ,io_stall_write_ms
  ,num_of_writes
  ,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms'
  ,io_stall_read_ms + io_stall_write_ms as io_stalls
  ,num_of_reads + num_of_writes as total_io
  ,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by avg_io_stall_ms desc

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
-- see runnable tasks
select scheduler_id, current_tasks_count, runnable_tasks_count from sys.dm_os_schedulers where scheduler_id  
 
-- see what current query are using most cpu
select top 50 sum(qs.total_worker_time) as total_cpu_time, sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements, qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc
-- select * from sys.dm_exec_query_plan(plan_handle)
 
-- query using highest cpu
 
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text] from (select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc
 
 
-- look for hash match or sorts etc.
 
sys.dm_exec_cached_plans

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
--Currently executing statements.sql
---- lists users in sys.dm_exec_requests
select r.session_id
    ,status
    ,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2) as query_text --- this is the statement executing right now
    ,qt.dbid
    ,qt.objectid
    ,r.cpu_time
    ,r.total_elapsed_time
    ,r.reads
    ,r.writes
    ,r.logical_reads
    ,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
-- query that generate the most physical IO
-- only need to gauge logical writes, checkpoint lazywrite actually flush to physical
 
select top 5 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc
 
--select * from sys.dm_exec_sql_text(sql_handle)
--select * from sys.dm_exec_query_plan(plan_handle)

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
--Indexes & Row Lock Waits
-

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
-- Cost Benefit of indexes
---
--- Compare the benefit (measured by reads) vs. the cost (measured by updates) for indexes
 
use Northwind
go
 
--- sys.dm_db_index_operational_stats
/*select 'object'=object_name(o.object_id), o.index_id
    , reads=range_scan_count + singleton_lookup_count
    , 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count , 'leaf_page_splits' = leaf_allocation_count
    , 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
    , 'nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (10,NULL,NULL,NULL) o
where objectproperty(o.object_id,'IsUserTable') = 1
order by reads desc, leaf_writes, nonleaf_writes
go
*/
declare @dbid int
select @dbid = db_id('Northwind')
--- sys.dm_db_index_usage_stats
select 'object' = object_name(object_id),index_id
    ,'user reads' = user_seeks + user_scans + user_lookups
    ,'system reads' = system_seeks + system_scans + system_lookups
    ,'user writes' = user_updates
    ,'system writes' = system_updates
from sys.dm_db_index_usage_stats
where objectproperty(object_id,'IsUserTable') = 1
and database_id = @dbid
order by 'user reads' desc
 
select 'object'=object_name(o.object_id), o.index_id
    , 'usage_reads'=user_seeks + user_scans + user_lookups
    , 'operational_reads'=range_scan_count + singleton_lookup_count
    , range_scan_count
    , singleton_lookup_count
    , 'usage writes' = user_updates
    , 'operational_leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count , leaf_insert_count,leaf_update_count,leaf_delete_count , 'operational_leaf_page_splits' = leaf_allocation_count
    , 'operational_nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
    , 'operational_nonleaf_page_splits' = nonleaf_allocation_count
from sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) o
  ,sys.dm_db_index_usage_stats u
where objectproperty(o.object_id,'IsUserTable') = 1
and u.object_id = o.object_id
and u.index_id = o.index_id
order by operational_reads desc, operational_leaf_writes, operational_nonleaf_writes
go

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which
the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product
in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and
Our suppliers from and against any claims or lawsuits, including attorneys' fees, that arise or result from the use or distribution of the Sample Code.
*/
select
  m.database_id,
  db_name(m.database_id) as database_name,
  m.file_id,
  m.name as file_name, m.physical_name, m.type_desc,
  fs.num_of_reads, fs.num_of_bytes_read, fs.io_stall_read_ms, fs.num_of_writes, fs.num_of_bytes_written, fs.io_stall_write_ms
from sys.dm_io_virtual_file_stats(NULL, NULL) fs
  join sys.master_files m on fs.database_id = m.database_id and fs.file_id = m.file_id

/* This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT
NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code
and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market

Introduction to Hyper-V for Exchange

Contents

Hyper-V architectural overview

Hyper-V Virtual Machine creation

Hyper-V network creation

Hyper-V for Exchange server

Monitoring and troubleshooting of Virtual Machines      

                Hypervisor Performance Monitor Objects

Working with Traces

Resources

Credits

Tech Bulletin Archive and Subscription Information

Hyper-V Architectural Overview

Hyper-V tends to be compared with Microsoft’s solutions like Virtual PC and Virtual server, but Hyper-V comes with a totally different architecture. It leverages the virtualization extensions provided by the Intel and AMD processors to implement its new architecture.

Hyper-V uses the concept of a parent partition and child partitions. A parent partition is the OS installed on the physical hardware and child partitions are the virtual machines created on the OS. The hypervisor (as seen in the diagram 1.1 below) loads before the Windows Kernel and runs in a special execution mode called Ring -1, which is made possible by the Intel  and AMD processor virtualization extensions. Data Execution Prevention (DEP) is also a requirement for Hyper-V.  Hyper-V takes care of processor and memory management, allowing efficient management of all the virtual machines.

The code for Hyper-V is written in assembly language. Since it is a very light code, it is very fast and agile in performance. The Windows hypervisor has its own application programming interface (API) and all the Operating Systems running on top of Windows hypervisor call into this API via “Hypercalls”. The Microsoft hypervisor must have at least one parent or root partition, running Windows Server 2008. The virtualization stack in the parent partition has direct access to the hardware devices. The parent partition then uses the hypercall API to create the child partitions which house the guest operating systems.

Hyper-V supports installation of Windows 2000 and higher operating systems, for both x86 and x64 architecture. For smooth management of the virtual machines, Hyper-V provides Integration Services referred to as Kernel Enlightenments. These include time synchronization, heartbeat, shutdown, Key/value pair Exchange and VSS. Integration services are only available for supported operating systems.

1.1   Hyper-V architecture

Hyper-V virtual machine creation

Hyper-V comes as an installable server role in Windows server 2008.  It can be installed and managed using the Server Manager console. Virtual Machines can be created by following the steps below:

1.       Open Server Manager and expand Roles

2.       In the right pane click Add roles

3.       In the Add roles wizard, select the Hyper-V role and click next

4.       You can create a dynamically expanding VHD and the wizard suggests the maximum size the VHD can grow based on the space available in the physical hard drive

5.       You can either choose to create a blank VHD or choose to boot from a ISO image, drive or floppy

Hyper-V Virtual Network Creation

Hyper-V allows creation of virtual networks, to manage connectivity in the following areas:

a)      Between the virtual machines hosted in a server

b)      Between servers with Hyper-V role installed in them and hosting VMs

c)       To provide VMs with external network access

The networking can be illustrated using the below diagram (3.1)

3.1 Hyper-V networking

The diagram on the left illustrates the state of the parent partition without any child partitions created. The physical NIC is bound with all the networking protocols and hence all the related communications.

The diagram in the right illustrates the networking model with parent and child partitions. When a virtual network is created, a virtual switch is created in the parent partition. A virtual NIC created in the child partition connects to virtual switch in the parent partition using a virtual Ethernet port. Now with this configuration, all the protocols are unbound from the physical NIC and the only protocol bound is the Microsoft Virtual Network Switch protocol. All the child partition’s networking is handled by the virtual network switch shown above which then binds with the physical NIC, completing the picture.

There are three types of virtual network switches that can be created:

1.       External network switch: Binds with the physical network adapter and provides external network access to the virtual machines.

2.       Internal network switch: Allows connectivity between the parent partition and the child partitions on the same Hyper-V server.

3.       Private network switch: Allows connectivity between the child partitions in the same Hyper-V server, there is no connectivity to the parent partition.

A virtual network can be created using the Virtual network manager window.

Depending on the requirement, you can create any one of the three types of virtual networks.

Hyper-V for Exchange Server

Below are the key take away points for Exchange deployment in Hyper-V:

Support:

·         Exchange 2007 SP1 is fully supported on Hyper-V

·         Exchange 2003 is supported, provided it meets the requirements outlined in the support policies and recommendations here

·         Exchange 5.5 and 2000 are not supported

·         Unified Messaging is not supported in Hyper-V

·         CAS, HUB, EDGE will be recommended and supported

·         The Mailbox server role will be supported on Hyper-V, but emphasis is placed on performance:

o   A Physical server should be used to scale up a Mailbox server

o   The Exchange VSS Writer does not work inside of a VM.

Performance:

·         The same performance requirements need to be met when scaling virtual machines

·         Customers should consider performance first and virtualization second

·         Exchange server performance is currently a major call generator and with Hyper-V as an additional layer is introduced that needs to be evaluated when investigating performance issues

·         Pass-Through disk I/O performance is better than VHDs

·         It is easy to over subscribe to a physical resource

·         iSCSI does not perform well with virtual network switches or NICs

·         Bottlenecks in the parent partition can affect performance in child partitions

·         Perfmon counters are different in the child and parent partitions and may need to be captured on both depending on the issue

Monitoring and Troubleshooting Virtual Machines

Hyper-V provides various diagnostic and monitoring tools. In Windows server 2008, event logging for Virtual machines are made easy to access and diagnose by providing individual classification under the event viewer. The various classifications available under Event viewer are:

Hyper-V-Config

Events are logged here that relate to problems accessing a virtual machines configuration file.

Hyper-V-High-Availability

Events are logged here that relate to quick migration or failover clustering between two virtual machines.

Hyper-V-Hypervisor

Events are logged here regarding hypervisor load failures or problems with the hypervisor itself.  If the hypervisor does not load because hardware virtualization is not enabled in the BIOS, for example, that event will be logged here.

Hyper-V-Integration

Events are logged here that relate to problems with the integrated components.

Hyper-V-Network

Events are logged here that relate to problems with the virtual switch.

Hyper-V-SynthNic

Events are logged here if there is a problem with network communication using the synthetic NIC.

Hyper-V-SynthStor

Events are logged here that relate to any storage issues when using the synthetic storage driver.

Hyper-V-vhdsvc

Events are logged here that relate to virtual hard disks. Failures to add, delete, merge, expand, convert and mount virtual hard disks will write events to this log.

Hyper-V-VMMS

Events are logged here that relate to the virtual machine management service functionality.

Hyper-V-Worker

Events are logged here that relate to the worker processes functionality.

Hypervisor Performance Monitor Objects

The following section gives a deeper explanation of counters and how to use them to monitor the performance of virtual machines in Hyper-V .  This information can also be found here.

Microsoft Hyper-V ships with 24 performance monitor objects that can be used to monitor the performance of both the parent partition and child partitions. Using these objects and the associated performance monitor counters, an administrator should be able to get a much better overall picture of how their system is performing, as well as the ability to narrow down performance problems to a specific partition.

Performance monitor objects for Hyper-V are divided into the following groups:

·         Hypervisor                                                          

·         Hyper-V Legacy Network Adapter

·         Hyper-V Task Manager Detail

·         Hyper-V Virtual objects

·         Hyper-V VM objects

Monitoring CPU Utilization

The counters to monitor CPU Utilization and their description are given below:

Using the % Processor Time Counter

The % Processor Time counter provides key information about Hyper-V performance. This performance counter details the total amount of CPU time being used by processes, which is the time the CPU is not idle waiting for work. Other % Processor Time counters detail CPU process time on a process-by-process basi

:)