Grant Fritchey (5/7/2014)
Not sure about the encryption. I'm not sure why that would affect performance much at all (a little in the CPU I suppose).As to figuring out how the server is running, there are a ton of tools built into SQL Server. First, and most important, are the dynamic management views (DMV). The very first one of those you'd start with to understand why or how a system is running slow is sys.dm_os_wait_stats. That will show you what the server is waiting on. You can also use the DMVs to look at currently existing queries (sys.dm_exec_requests) an aggregation of query performance for queries in cache (sys.dm_exec_query_stats) and others. There's a book on this (free download e-book) by Tim Ford and Louis Davidson.
Another tool is to run perfmon to capture performance monitor metrics. This will let you capture general behavior and load indicators such as cpu percentage, memory use, and others.
After that, you start to get into more specialized tools such as extended events to capture certain types of behavior or events such as query execution time and other things.
There's a lot to this. In fact, the first several pages of my book (in the signature below) are all about capturing system behaviors in order to understand where your system is running slow.
Hello,
The query below returns the results as you see here:
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
Where do I start looking into the main delays which seem to be the first two here . i.e.:
DBMIRROR_EVENTS_QUEUE and DBMIRRORING_CMD
wait_typewaiting_tasks_countwait_time_msmax_wait_time_mssignal_wait_time_ms
DBMIRROR_EVENTS_QUEUE12338274409723209430091769603
DBMIRRORING_CMD2263712403291223986412339449678
BROKER_TASK_STOP148854801412496074100151517342
CLR_AUTO_EVENT55125603795613250590865
REQUEST_FOR_DEADLOCK_SEARCH1367856839358305170683935830
LAZYWRITER_SLEEP6962436839293871829126417
XE_TIMER_EVENT2279968391503830175683913037
XE_DISPATCHER_WAIT15236839144542468100020
LOGMGR_QUEUE139319268381452934100218580
SQLTRACE_INCREMENTAL_FLUSH_SLEEP17056668228060041811