Copy, paste and execute this query to list the top CPU consuming queries in a SQL DB
select top 20 left(p.cacheobjtype + '(' +p.objtype + ')', 35) as cacheobjtype, p.usecounts, p.size_in_bytes / 1024 as size_in_kb, stat.total_worker_time/1000 as tot_cpu_ms, stat.total_elapsed_time/1000 as tot_duration_ms, stat.total_physical_reads, stat.total_logical_writes, stat.total_logical_reads, left (case when pa.value=32767 then 'ResourceDb' else isnull (db_name (convert (sysname, pa.value)), convert (sysname, pa.value)) end, 40) as dbname, sql.objectid, convert (nvarchar(50), case when sql.objectid is null then null else replace (replace (sql.[text], char (13), ' '), char(10), '') end) as procname, replace (replace (substring (sql.[text], stat.statement_start_offset/2 + 1, case when stat.statement_end_offset = -1 then len (convert (nvarchar(max), sql.[text])) else stat.statement_end_offset /2 - stat.statement_start_offset / 2 + 1 end), char(13), ' '), char(10), ' ') as stmt_text, qplan.query_plan from sys.dm_exec_cached_plans p outer apply sys.dm_exec_plan_attributes (p.plan_handle) pa inner join sys.dm_exec_query_stats stat on p.plan_handle = stat.plan_handle outer apply sys.dm_exec_sql_text (p.plan_handle) as sql outer apply sys.dm_exec_query_plan(p.plan_handle) as qplan where pa.attribute = 'dbid' order by tot_cpu_ms desc
select sp.*,st.text from sys.sysprocesses sp cross apply sys.dm_exec_sql_text(sp.sql_handle) as st where sp.lastwaittype like 'RESOURCE_SEMAPHORE_QUERY_COMPILE%' ORDER BY sp.waittime desc;
select q.query_hash, q.number_of_entries, q.total_time, t.text as sample_query, p.query_plan as sample_plan from (select top 20 query_hash, count(*) as number_of_entries, sum(total_elapsed_time) as total_time, min(sql_handle) as sample_sql_handle, min(plan_handle) as sample_plan_handle from sys.dm_exec_query_stats group by query_hash having count(*) > 1 order by count(*) desc) as q cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p
select top 20 query_hash, count(*) as number_of_entries, sum(total_elapsed_time) as total_time, min(sql_handle) as sample_sql_handle, min(plan_handle) as sample_plan_handle from sys.dm_exec_query_stats group by query_hash having count(*) > 1 order by count(*) desc
This small guide will help you to enable Internet access to a Windows 2012 R2 Remote Desktop Services farm without using a TS Gateway.
configure the Active Directory domain (including TS broker and RDS hosts) in a
You need to allow the FQDN name rdshost1.domain.com to be resolved
internally with the local address and publicly with the WAN IP.
On your firewall enable a one-to-one NAT rule for port 3389 from the public address to the RDS host private IP (one dedicated rule each RDS
Be sure the DNS name
on public DNS and internal DNS match with the NAT rules.
At this point if you try to test your environment connecting to the firewall public IP, you should notice some strange behavior. In some conditions your client will try to connect to the farm (broker redirected host) using the LAN IP instead of the WAN IP address of the server.
In order to fix this strange way of working please follow the steps above.
Add a secondary vNIC
(if VMware) or secondary IP address on physical NIC to the every RDS host.
To avoid Windows detecting IP address
conflicts when starting the networking service, in VMware attach the vNIC
on a dedicated vSwitch.
Disable every additional protocol and leave only IPv4 configured with the same IP address as the corresponding WAN
Disable all IPv6 bindings and delete the teredo tunnel interfaces.
To remove the virtual adapters go to Device Manager, View
menu, select Show Hidden Devices.
Drill down to
network adapters and tight click the object to uninstall.
Create a new GPO with the following settings and link it to the OU containing the RDS Hosts.
Set up the collection as usual installing the RDS host role on each node and the Broker service on a dedicated server.
Now you can test the RDS farm connecting to one of the public addresses (WAN IP)
In order to verify the connection, open some concurrent Remote Desktop Connection session pointing to the hosts FQDN (or load balancer address) Check your local pc active RDP sessions with the following command:
netstat -an | find /i "3389"
Each connection should display the public IP address of the firewall as destination address.
Ho to solve Set-PublicFolderMigrationRequest -Identity \PublicFolderMigration -PreventCompletion:$false error NotSpecified: (:) [Set-PublicFolderMigrationRequest], ManagementObjectNotFoundException
Symptoms: Entering the following EMS command
Set-PublicFolderMigrationRequest -Identity \PublicFolderMigration -PreventCompltion:$false You get this error
Couldn't find a request that matches the information provided. Reason: No such request exists in the specified index. + CategoryInfo : NotSpecified: (:) [Set-PublicFolderMigrationRequest], ManagementObjectNotFoundException + FullyQualifiedErrorId : [Server=servername,RequestId=10c7cc99-77e4-4aa9-b6b1-3aa6f3c8f6dd,TimeStamp=20.02.2015 15: 00:58] [FailureCategory=Cmdlet-ManagementObjectNotFoundException] CA306C92,Microsoft.Exchange.Management.Recipient Tasks.SetPublicFolderMigrationRequest + PSComputerName : servername.domain.com Solution:
Fire the command get-PublicFolderMigrationRequest | fl