slow server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

slow server

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-05-07 : 13:05:06

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-07 : 13:21:20

1. I don't know. If it's synchronous mirroring and there's network latency between the two databases, then that could significantly impact DML commands.
2. Take a look at wait stats. You need to determine what they are waiting on. Is it I/O? Blocking? CPU? Etc.

Check PerfMon and determine if you have any hardware bottlenecks. I suspect you have missing indexes that are causing selects to run slowly, which in turn causes CPU to rise. Or maybe memory pressure.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-08 : 01:48:16

Is regular maintence in place?
Which queries are being used the most ? have you checked if they are running efficeintly?
Has anything else changed on the server?
For SQL Server Tuning method for a drop in throughput - http://www.sqlserver-dba.com/2013/11/sql-server-tuning-method-for-a-drop-in-throughput.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-05-08 : 04:23:46

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_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
DBMIRROR_EVENTS_QUEUE 12338274 4097232094 3009 1769603
DBMIRRORING_CMD 2263712 4032912239 86412339 449678
BROKER_TASK_STOP 14885480 1412496074 10015 1517342
CLR_AUTO_EVENT 55 1256037956 132505908 65
REQUEST_FOR_DEADLOCK_SEARCH 136785 683935830 5170 683935830
LAZYWRITER_SLEEP 696243 683929387 1829 126417
XE_TIMER_EVENT 22799 683915038 30175 683913037
XE_DISPATCHER_WAIT 1523 683914454 246810002 0
LOGMGR_QUEUE 1393192 683814529 34100 218580
SQLTRACE_INCREMENTAL_FLUSH_SLEEP 170566 682280600 4181 1

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-08 : 11:47:51

I presume the mirroring is synchronous? Is the IO on the mirrored server slow (check disk queues and response times in perfmon). How about the network latency and bandwidth capacity between the two servers? If you pause mirroring does the performance improve significantly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-08 : 11:48:30

Is it sync or async mirroring?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2014-05-09 : 03:13:33

Note that sometimes the performance is good but sometimes bad.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-05-09 : 12:30:08

quote:
Originally posted by arkiboys

Note that sometimes the performance is good but sometimes bad.

Is it sync or async mirroring?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-05-10 : 12:11:32

Hi, One suggestion is to check for general bottlenecks including 32\64 bit differences,activity on non-mirrored databases, amount of mirroring sessions, analyse difference between asysnchronous and synchronous, IO contention check principle and mirror

Depending on whether you are using synch or asynch , check disk IO contention. If asynch check the principle, if synch check the mirror.

these are just suggestions - to give more detailed information , some extra information is required about the system resources.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources