May 7, 2014 at 11:07 am
Hello,
We have a sql server machine with many databases.
These databases are big and a few of the tables have millions of records.
We also have mirroring setup so that the data gets mirrored into another location on another sql server machine too...
Everyday thousands of records are inserted, updated, etc...
I have started seeing extreme slow performance issue on these inserts or updates, etc...
After several checks, we believe the whole sql server machine is being hammered by users heavily using databases.
The machine has the max. memory available.
Note that sometimes the sql server machine hangs too, i.e. (can not click on the enterprise manager icon to open it)...
Questions:
1-
It seems the mirroring has encryption enabled by default. If this is disabled on the mirroring server which is on another location, then could it improve the performance on the local server?
2-
What can I use to investigate the workload on the server
Thank you
May 7, 2014 at 12:47 pm
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2014 at 1:07 pm
OP: Please don't take this the wrong way, but given some of the statements and questions in your post I fear that you would not be able to either a) understand some of the output/recommendations monitoring would do, b) could do monitoring improperly which could make performance suffer even more, c) would not know how to fix findings or d) possibly (and worst of all by far) make some changes that could be disastrously bad. A non-functional production system is NOT the place to learn by trial and error!
You mentioned Enterprise Manager. Was that just a slip or are you really on a VERY old edition of SQL Server? 🙂
I agree with Grant that encryption of mirroring would be an unlikely cause of what you are seeing.
Note that having mirroring in play can introduce a number of performance issues depending on many factors.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 8, 2014 at 1:08 am
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.
hi grant.
as I send this text I have your book with me.
infact I carry your book in my bag every day as it is a useful book.
ok. I will read about what you have suggested.
but I still do not see why applications which hit tge database is sometimes
fast but most of tge time slow
surely indexes are good if the queries are sometimes fast.
any further suggestions please?
Thankyou.
May 8, 2014 at 2:00 am
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
May 8, 2014 at 2:30 am
The majority of those are harmless waits, to be ignored. You need to filter out the meaningless waits. Also, taking a single view doesn't help much, you need to take the difference over a period of time, or multiple views at regular intervals so you can see what waits accrue when.
Have a read through Grant's book, especially the chapter on system performance analysis and query performance analysis (chapters 2 and 3 in the 2012 version), also take a look at chapter 1 of http://www.red-gate.com/community/books/accidental-dba
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 8, 2014 at 6:06 am
arkiboys (5/8/2014)
hi grant.as I send this text I have your book with me.
infact I carry your book in my bag every day as it is a useful book.
ok. I will read about what you have suggested.
but I still do not see why applications which hit tge database is sometimes
fast but most of tge time slow
surely indexes are good if the queries are sometimes fast.
any further suggestions please?
Thankyou.
Thanks for the kind words.
Without seeing a lot more detail on what's running on your systems, wait statistics, structures, queries, blocking, resource use & contention, I couldn't even really hazard an educated guess. Two areas that intermittent performance problems can come from are either bad parameter sniffing, or, out of date statistics (which can frequently lead to bad parameter sniffing). Examine the execution plans when the query is slow and fast to see if it's different. If it is, understand why. Is it due to the statistics, or is it due to the values passed to the query, parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2014 at 6:07 am
Complete agreement with what Gail is suggesting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply