Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

slow sql server machine Expand / Collapse
Author
Message
Posted Wednesday, May 7, 2014 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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
Post #1568612
Posted Wednesday, May 7, 2014 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 13,875, Visits: 28,272
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1568658
Posted Wednesday, May 7, 2014 1:07 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 4,400, Visits: 6,259
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 at GMail
Post #1568663
Posted Thursday, May 8, 2014 1:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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.
Post #1568781
Posted Thursday, May 8, 2014 2:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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_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
Post #1568792
Posted Thursday, May 8, 2014 2:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 40,180, Visits: 36,584
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1568796
Posted Thursday, May 8, 2014 6:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 13,875, Visits: 28,272
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1568847
Posted Thursday, May 8, 2014 6:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 13,875, Visits: 28,272
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
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1568848
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse