Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


slow sql server machine


slow sql server machine

Author
Message
arkiboys
arkiboys
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 263
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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17655 Visits: 32273
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6016 Visits: 8314
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
arkiboys
arkiboys
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 263
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.
arkiboys
arkiboys
SSC-Enthusiastic
SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)SSC-Enthusiastic (196 reputation)

Group: General Forum Members
Points: 196 Visits: 263
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
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

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


Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17655 Visits: 32273
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17655 Visits: 32273
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search