SQL Server Virtualization Summary

Support

• SQL Server is is supported on Microsoft Virtual Server, including the support for the clustered environment.
• With a Premier Support SQL Server is also supported for Novel virtual software.
• In 2007: VMWare is tested and supported by VMware, but not by Microsoft which is not a surprise considering it is in direct competition with their Virtual Server. If the customer has the premier support, Microsoft say it "will use commercially reasonable efforts to investigate potential issues".
• March 2009 update: Microsoft started supporting SQL on some of the VM, including VMware. This excludes quest clustering and unsupported SQL/VM's OS combinations.
  

Licensing

Licensing for the SQL Server on virtual environment is done depending on the edition:

• For Enterprise Edition, if all processors in a machine have been licensed, then the customer may run unlimited instances of SQL server 2005 on an unlimited number of virtual operating environments on that same machine. Otherwise, the same as below
• For the other edition, it goes per each virtual environment and, within it, for each processor that the virtual machine accesses.

VMWare

Vmware has interesting features like: automatic failover to another VMware-enabled server, movingSQL Server instances from one host to another LIVE (zero downtime), quick disaster recovery.

Virtual Server

VS 2005 EE is a free download since April 2006. It supports up to 3.6G of RAM per each environment.

Virtual Server provides simple two-node failover from one virtual machine to another for testing and development

SQL 2005 EE has an add-on that can run Virtual Hard Disk for up to 10G. VHD is a disk for each virtual machine and it is stored in a .vhd file.

SQL Server Auditing - Example

Set up objects

select * from master.dbo.ddl_all_databases

create table master.dbo.ddl_all_databases
( DateTime^? datetime,
LoginName^? char(20),
DBName char(20),
UserName^? char(20),
EventType^? char(200),
CommandText^? char(1000))

CREATE TRIGGER Trig_DBEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS INSERT master.dbo.ddl_all_databases
(
DateTime^?,
LoginName^?,
DBName,
UserName^?,
EventType^?,
CommandText^?
)
SELECT GETDATE(),
EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(50)'),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(1024)')
GO

Monitored events

This will monitor:

• DDL_TRIGGER_EVENTS, DDL_FUNCTION_EVENTS, DDL_SYNONYM_EVENTS, DDL_SSB_EVENTS, DDL_DATABASE_SECURITY_EVENTS, DDL_EVENT_NOTIFICATION_EVENTS, DDL_PROCEDURE_EVENTS, DDL_TABLE_VIEW_EVENTS, DDL_TYPE_EVENTS, DDL_XML_SCHEMA_COLLECTION_EVENTS, DDL_PARTITION_EVENTS, DDL_ASSEMBLY_EVENTS

Preferably you can start monitoring with only:

• DDL_USER_EVENTS,DDL_INDEX_EVENTS,DDL_TABLE_EVENTS

Trigger on the SERVER level can be set up and monitor DDL_SERVER_LEVEL_EVENTS instead (ALTER DB, adding, dropping roles,etc...)

Tuning SQL Statements

Top 10 worst SQL statements (measured by the time)

select top 10
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS sql_text,
total_worker_time/execution_count "CPU per exec",
total_elapsed_time/execution_count/1000000 "Time per exec",
total_physical_reads/execution_count "Ph.Reads per exec",
total_logical_reads/execution_count "Log.Reads per exec",
total_logical_writes/execution_count "Log.Writes per exec"
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where qs.total_elapsed_time/execution_count/1000000>10
order by "Time per exec" desc

Top 10 worst SQL statements with the explain plan

select top 10
qs.sql_handle,
total_worker_time/execution_count "CPU per exec",
total_elapsed_time/execution_count "Time per exec",
total_physical_reads/execution_count "Ph.Reads per exec",
total_logical_reads/execution_count "Log.Reads per exec",
total_logical_writes/execution_count "Log.Writes per exec",
qp.query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp

Indexes that are possibly not used

select * from sys.dm_db_index_usage_stats
where user_seeks=0 and user_scans=0 and user_lookups=0 and user_updates=0