Very slow performance on SQL 2012 clustered on Windows Server 2012

I'm setting up a production environment with SQL Server 2012 SP1 (Clustered) and SharePoint 2013.

it turns out that the clustered SQL Server is much slower than the development machine, where is the production specification is way higher than the development, I've test the performance by running a simple query on both of servers and simple insert statement.

I check the performance monitor for the server, i found out that there is no load on the Memory or CPU.

Could it be a SAN storage or a Network issue, how can i check?

Is there any tool that can help? or if anyone faced the same issue.

Appreciating any help.

Best,

May 15th, 2014 5:06am
Make sure that  you set MAXDOP back to 0, as SP2013 installation requires it to be set to MAXDOP 1 
Free Windows Admin Tool Kit Click here and download it now
May 15th, 2014 9:24am

Thanks Dimant;

we've tried setting the MAXDOP back to 0, but we still having the same performance issue. Any other suggestions?

May 15th, 2014 9:33am

Thanks Dimant;

we've tried setting the MAXDOP back to 0, but we still having the same performance issue. Any other sugges

Free Windows Admin Tool Kit Click here and download it now
May 15th, 2014 9:40am

What does the below statement return?

;WITH Waits AS
(
  SELECT
    wait_type,
    wait_time_ms  /1000. AS wait_time_s, 
         100. * wait_time_ms / SUM(wait_time_ms)OVER()AS pct,
         ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
      FROM sys.dm_os_wait_stats
      WHERE wait_type NOT IN (N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'ONDEMAND_TASK_QUEUE',N'BROKER_RECEIVE_WAITFOR'
)
)
      --filter out additional irrelevant waits
SELECT
  W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12,2))AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12,2))AS pct,
  CAST(SUM(W2.pct)AS DECIMAL(12,2))AS running_pct
FROM Waits AS W1
  JOIN Waits AS W2
    ON W2.rn<= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct)-W1.pct < 90-- percentage threshold
ORDER BY W1.rn;

May 15th, 2014 11:16pm

Thanks Dimant,

Below are the results after running the query.

wait_type                                                 |   wait_time_s |   pct      | running_pct

PREEMPTIVE_OS_AUTHINTICATIONOPS   |   67905.90      | 68.38    | 68.38

WRITELOG                                                |    20405.82      | 20.55   | 88.93

CXPACKET                                                 |    3717.26        | 3.74    |  

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2014 2:00pm

Looks like this OS tries to authenticate something....Have you checked DNS?

BTW how fast the statement get executed?

SELECT SUSER_SNAME(sid),* FROM sys.database_principals

May 21st, 2014 2:15pm

Thanks Dimant;

I haven't checked the DNS yet, the statement got executed with no time.

Do you have any idea what to check on the DNS exactly?

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2014 2:21pm
http://www.brentozar.com/askbrent/
May 21st, 2014 2:30pm

Thanks again Dimant;

After fixing some network issues, below are the updated results

WRITELOG     198.84     49.33   49.33

LCK_M_S        41.89       10.39   59.72

PAGEIOLATCH_SH   28.70   7.12    66.84

Authentication_OPS 12.95   3.11   83.03

Appreciating your help!

Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2014 1:31pm

Thanks again Dimant;

After fixing some network issues, below are the updated results

WRITELOG     198.84     49.33   49.33

LCK_M_S        41.89       10.39   59.72

PAGEIOLATCH_SH   28.70   7.12    66.84


This statistics does not indicate any problem IMO.Or did you just ran the query after restarting SQL server I guess this could be reason for such low count. Please wait for couple of days may be a week  lets internal DMV cache to be updated then run the query.Restrting SQL server cleared buffer cache and hence all values were cleared.
May 22nd, 2014 1:36pm

Thanks Shanky;

Yes, we have ran the query after restarting the server. We still having issues with the performance, as mentioned above running queries on two different servers with different results (DEVELOPMENT is way faster than CLUSTER).

Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2014 1:54pm

Thanks Shanky;

Yes, we have ran the query after restarting the server. We still having issues with the performance, as mentioned above running queries on two different servers with different results (DEVELOPMENT is way faster than CLUSTER)


Please post query plan for both Dev and Cluster, Its difficult to comment without query plan.Are you facing slowness for just this query or whole server is slow.
May 22nd, 2014 2:04pm

It's not related to a specific query, all queries on the cluster are slow!. 

could it be a SAN Storage iss

Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2014 2:11pm

It's not related to a specific query, all queries on the cluster are slow!. 

could it be a SAN Storage issue? :/


Cannot say it. You need to find out root cause please refer to my first reply posted to drill down issue.
May 22nd, 2014 2:24pm

Hello Shanky;

below are the updated results, we have ran a job to insert more than 70000 files.

WRITELOG     38440     86.7   86.7



Free Windows Admin Tool Kit Click here and download it now
May 26th, 2014 4:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics