02 November 2015

08 September 2015

SQL Top CPU Usage Queries Listing

Copy, paste and execute this query to list the top CPU consuming queries in a SQL DB


use [Master]

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

26 February 2015

How to enable Internet (WAN) access to a Microsoft Windows 2012 R2 Remote Desktop Services farm without using a TS Gateway



This small guide will help you to enable Internet access to a Windows 2012 R2 Remote Desktop Services farm without using a TS Gateway.

Install and configure the Active Directory domain (including TS broker and RDS hosts) in a split-DNS layout. 
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 host).

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.





20 February 2015

How to fix Exchange 2013 " Set-PublicFolderMigrationRequest -Identity \PublicFolderMigration -PreventCompletion:$false " error

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
   
[PS] C:\Users\user.domain\Desktop>get-PublicFolderMigrationRequest | fl


RunspaceId     : 85a6da6d-62fb-45c8-b86f-7cfd2dc9b313
SourceDatabase : Public Folder Database Server
Name           : true
RequestGuid    : 9fd478ac-9657-4c4d-a9bd-f6ca94a9f46f
RequestQueue   : DB09PUB01
Flags          : IntraOrg, Pull, Suspend
BatchName      :
Status         : AutoSuspended
Protect        : False
Suspend        : True
Direction      : Pull
RequestStyle   : IntraOrg
OrganizationId :
WhenChanged    : 20.02.2015 14:48:35
WhenCreated    : 20.02.2015 14:47:04
WhenChangedUTC : 20.02.2015 13:48:35
WhenCreatedUTC : 20.02.2015 13:47:04
Identity       : \true
IsValid        : True
ObjectState    : New



Use the RequestGiud value to complete the migration request command:


Resume-PublicFolderMigrationRequest -Identity "000000-00000-0000-YOURREQUESTID"

[PS] C:\Users\
user.domain\Desktop>Resume-PublicFolderMigrationRequest -Identity "9fd478ac-9657-4c4d-a9bd-f6ca94a9f46f"




12 February 2015

Microsoft Exchange 2013 - Outlook connection problem after mailbox migration


Symptoms:

After an Exchange 2010 to Exchange 2013 successful mailbox migration the Outlook client hangs or display the disconnected status.

The Outlook connection status (Ctrl + Right click on Outlook icon on the task-bar) display a pending connection to the old Exchange server.

Creating a new Outlook profile to test the mailbox access returns the following error message:

The name cannot be resolved. The connection to Microsoft Exchange is unavailable. Outlook must be online or connected to complete this action.



Solution:

Temporarily disable the Internet Explorer proxy settings or add a proxy exclusion for the exchange server name.