January 7, 2016 at 1:39 pm
I have a 2-Node SQL 2014 cluster running on (2) Server 2012R2 nodes. These are virtual machines sitting on a 2-node ESX5.5 cluster (vSphere5.5). Each node has 16GB RAM and 8vcpus. Memory is configured to allow SQL to only use 13GB of the 16GB.
The SQL nodes are split across each ESXi host. Storage is presented to the SQL cluster nodes via in-guest iscsi LUNs to a backend SAN array.
Front end application is experiencing slowness when performing a task. I logged on to the SQL cluster nodes, and it is even slow expanding the databases. When I failover, the application seems to go back to acceptable performance.
I am no SQL DBA or expert, but know my way around the SSMS console.
What quick steps can I do to check where the bottleneck on the SQL cluster is?
How can I determine if it needs additional memory, etc.?
What are some high-level post installation tasks I should complete?
January 7, 2016 at 4:02 pm
bcatty13 (1/7/2016)
I have a 2-Node SQL 2014 cluster running on (2) Server 2012R2 nodes. These are virtual machines sitting on a 2-node ESX5.5 cluster (vSphere5.5). Each node has 16GB RAM and 8vcpus. Memory is configured to allow SQL to only use 13GB of the 16GB.The SQL nodes are split across each ESXi host. Storage is presented to the SQL cluster nodes via in-guest iscsi LUNs to a backend SAN array.
Front end application is experiencing slowness when performing a task. I logged on to the SQL cluster nodes, and it is even slow expanding the databases. When I failover, the application seems to go back to acceptable performance.
I am no SQL DBA or expert, but know my way around the SSMS console.
What quick steps can I do to check where the bottleneck on the SQL cluster is?
How can I determine if it needs additional memory, etc.?
What are some high-level post installation tasks I should complete?
There are UMPTEEN BAD DEFAULTS/configuration settings in your entire stack I bet.
iSCSI makes me wonder about the networking between disks and host. I have seen single 1Gb NICs (grossly misconfigured of course) more times than I can count.
You need to check File IO Stalls and Wait Stats to get a higher-level view of where it hurts. Search Glenn Berry SQL Server Diagnostics for scripts to help.
I STRONGLY recommend a quick performance review from a qualified consultant. Huge bang-for-the buck if you get the right person.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 8, 2016 at 6:45 am
I'm with Kevin, this is probably a very involved situation.
However, to at least get you started, I'd look at sys.dm_os_wait_stats to understand what is causing the system to run slow. Where are the slow points occurring. Even better if you can capture this before and after a particularly slow query.
The failover causing things to run faster for a period could be a large number of things, but the first that comes to mind is the possibility of bad parameter sniffing. This would require you to identify the slowest running queries. The best way to do this is capture query metrics using extended events. However, you can also get a quick idea of slow running queries by looking at sys.dm_exec_query_stats. This can show you which queries, currently in cache, are the longest running, most frequently called, etc.
However, everything I'm suggesting is the lightest possible start at what you really need, a thorough evaluation of the status of the server, its settings, its behaviors, performance metrics, wait stats, query metrics, and more. This all leads into making system changes, code changes, maintenance routines, indexes, statistics... It's just a very broad and involved process.
"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
January 10, 2016 at 1:54 pm
Thank you both for the feedback. I do agree it is a broad topic and cannot be resolved over a forum. I will start at the items you mentioned.
thnx.
January 10, 2016 at 2:16 pm
Quick thought, 3Gb for a W2K12 Server is pretty meagre especially if there is any other activity than the SQL Server Service active, RDP should be a no-no and SSIS shot on sight.
😎
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply