none
SQL Server 2016 - TempDB - High I/O Latency after upgrade

    Question

  • Hello All,

    We are experiencing higher I/O latencies more than 900 ms after we upgraded to SQL 2016. We have observed such latency values for the tempdb, during the performance tests.

    However when we compare the latencies with SQL Server 2014, we see values around 5 ms after the completion of the performance test for our OTLP application.

    One thing observed is that tempdb is having a target_recovery_time_in_seconds set to 60 (default of SQL 2016), meaning it is using indirect checkpointing mechanism. Whereas for our application database the target_recovery_time_in_seconds is set to 0, meaning it is using automatic checkpoint mechanism.

    Also checked the Perfmon values for the Checkpoints/sec, and the average is ~500, and the highest being 23K.

    Ours is a very tempdb intensive application, and any pointers on how to reduce the I/O latencies would help.

    Since the 1118 trace flag is enabled by default in SQL 2016, we are hoping that should help in performance and not a reason for the current degradation.

    Another observation is that the I/O latency values for the application database is around 100 ms and is not so bad as of tempdb.


    Thanks, Kumar

    Thursday, January 12, 2017 10:42 AM

All replies

  • Kumar,

    You need to compare apple to apple. I think these two servers you comparing are having their own disks on which tempdb is placed. Can you check that? You need to check the disk or underlying storage characteristics out of which tempdb drive is created on both the servers you are comparing.

    Thanks,

     


    Kindly mark the reply as answer if they help

    Thursday, January 12, 2017 11:37 AM
  • Hi Sunil, thanks for the response. It is on the same SAN and tested under same conditions. There should not be any physical H/W changes attributing to this huge increase in I/O write latencies.

    Thanks, Kumar

    Thursday, January 12, 2017 12:16 PM
  • What does the below statement return?

    select   session_id, wait_duration_ms,   resource_description 
          from    sys.dm_os_waiting_tasks
          where   wait_type like 'PAGE%LATCH_%' and
                  resource_description like '2:%'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, January 12, 2017 12:24 PM
    Answerer
  • You need to look at your waitstats. Are you experiencing high values for LATCH_EX's?

    You may be running into problems with the cardinality estimator.

    Thursday, January 12, 2017 12:29 PM
  • Thanks Uri for your response. The machine was restarted and hence don't have the data (as I mentioned these are perf. test systems).

    I will verify the same and get back to you. Any guidance on what I should be looking at the result set of this query?


    Thanks, Kumar

    Thursday, January 12, 2017 1:22 PM
  • We have suffered enough with the cardinality estimator engine changes during the upgrade from SQL 2008 to SQL 2014. Are you saying that we should be turning off the flags like 4199 etc., so as to make the cardinality engine work similar to SQL 2014?

    After struggling for more than 6 months, the application is kind of stabilized for the SQL 2014 version.

    Hence asking in advance as we don't want to get into such problems again for SQL 2016 upgrade.


    Thanks, Kumar

    Thursday, January 12, 2017 1:24 PM
  • wait_type wait_time_ms signal_wait_time_ms resource_wait_time_ms percent_total_waits percent_total_signal_waits percent_total_resource_waits
    SP_SERVER_DIAGNOSTICS_SLEEP 300001 62400776 0 15.4405 59.0919 0.0000
    SQLTRACE_WAIT_ENTRIES 300000 0 300000 0.0742 0.0000 0.0742
    QDS_PERSIST_TASK_MAIN_LOOP_SLEEP 60475 510 62591816 15.4879 0.0005 15.4877
    LCK_M_U 16560 981 547238 0.1357 0.0009 0.1354
    LCK_M_X 6891 912 150412 0.0374 0.0009 0.0372
    PAGEIOLATCH_EX 6709 18944 4620586 1.1480 0.0179 1.1433
    PAGEIOLATCH_UP 6100 2346 413282 0.1028 0.0022 0.1023
    LATCH_EX 6083 10937 11487561 2.8452 0.0104 2.8425
    PAGELATCH_SH 6073 313497 3749385 1.0053 0.2969 0.9277
    PREEMPTIVE_OS_WRITEFILEGATHER 5767 0 90187 0.0223 0.0000 0.0223
    LCK_M_SCH_S 5742 613 79099 0.0197 0.0006 0.0196
    PAGELATCH_EX 5736 4677560 1541221 1.5388 4.4295 0.3814
    PAGEIOLATCH_SH 4654 104862 5548111 1.3988 0.0993 1.3728
    LCK_M_SCH_M 4529 461 32923 0.0083 0.0004 0.0081
    PREEMPTIVE_OS_QUERYREGISTRY 4513 0 5335 0.0013 0.0000 0.0013
    PREEMPTIVE_OS_DOMAINSERVICESOPS 4513 0 4513 0.0011 0.0000 0.0011
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 4155 82 62598437 15.4894 0.0001 15.4894
    PWAIT_ALL_COMPONENTS_INITIALIZED 2710 0 8053 0.0020 0.0000 0.0020
    LATCH_SH 2622 42964 438195 0.1191 0.0407 0.1084
    WAIT_XTP_HOST_WAIT 2234 0 4468 0.0011 0.0000 0.0011
    LCK_M_S 1981 541 14243 0.0037 0.0005 0.0035
    SEQUENCE_GENERATION 1544 0 39593 0.0098 0.0000 0.0098
    WRITELOG 1542 5999988 17750022 5.8767 5.6818 4.3921
    MSQL_XP 1273 0 3570 0.0009 0.0000 0.0009
    PREEMPTIVE_OS_WRITEFILE 1182 0 30620 0.0076 0.0000 0.0076
    LCK_M_IU 1040 196 3691 0.0010 0.0002 0.0009
    SQLTRACE_FILE_BUFFER 879 701 54537 0.0137 0.0007 0.0135
    IO_COMPLETION 853 195 7842 0.0020 0.0002 0.0019
    HADR_FILESTREAM_IOMGR_IOCOMPLETION 848 3722 62626746 15.4973 0.0035 15.4964
    CHKPT 846 0 846 0.0002 0.0000 0.0002
    SLEEP_MASTERDBREADY 840 0 840 0.0002 0.0000 0.0002
    SOS_SCHEDULER_YIELD 636 31941848 6533 7.9053 30.2481 0.0016
    PREEMPTIVE_XE_GETTARGETSTATE 603 0 109361 0.0271 0.0000 0.0271
    THREADPOOL 537 0 15057 0.0037 0.0000 0.0037
    PAGELATCH_UP 494 17407 83236 0.0249 0.0165 0.0206
    PREEMPTIVE_OS_LIBRARYOPS 441 0 441 0.0001 0.0000 0.0001
    LCK_M_IX 437 193 4711 0.0012 0.0002 0.0012
    LOGBUFFER 436 16514 174263 0.0472 0.0156 0.0431
    DIRTY_PAGE_POLL 375 2771 62618079 15.4949 0.0026 15.4942
    CMEMTHREAD 295 14808 2915 0.0044 0.0140 0.0007
    ASYNC_IO_COMPLETION 263 0 343 0.0001 0.0000 0.0001
    ASYNC_NETWORK_IO 251 17463 42775 0.0149 0.0165 0.0106
    PREEMPTIVE_OS_GENERICOPS 227 0 408471 0.1011 0.0000 0.1011
    PREEMPTIVE_OS_DISCONNECTNAMEDPIPE 223 0 4808 0.0012 0.0000 0.0012
    PREEMPTIVE_OS_DELETESECURITYCONTEXT 199 0 8625 0.0021 0.0000 0.0021
    PREEMPTIVE_OS_COMOPS 191 0 191 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_CRYPTOPS 173 0 9324 0.0023 0.0000 0.0023
    SLEEP_DCOMSTARTUP 156 0 156 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_FILEOPS 152 0 16995 0.0042 0.0000 0.0042
    PREEMPTIVE_OS_PIPEOPS 142 0 142 0.0000 0.0000 0.0000
    SLEEP_DBSTARTUP 135 1 9023 0.0022 0.0000 0.0022
    PREEMPTIVE_OS_GETDISKFREESPACE 75 0 190 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_CRYPTIMPORTKEY 72 0 4161 0.0010 0.0000 0.0010
    XE_BUFFERMGR_ALLPROCESSED_EVENT 59 50 603 0.0002 0.0000 0.0001
    PREEMPTIVE_XE_CALLBACKEXECUTE 59 0 407 0.0001 0.0000 0.0001
    FT_IFTS_RWLOCK 59 1261 2556 0.0009 0.0012 0.0006
    PREEMPTIVE_OS_VERIFYTRUST 59 0 153 0.0000 0.0000 0.0000
    PREEMPTIVE_XE_TARGETINIT 58 0 507 0.0001 0.0000 0.0001
    PREEMPTIVE_XE_SESSIONCOMMIT 58 0 1872 0.0005 0.0000 0.0005
    PREEMPTIVE_OS_CRYPTACQUIRECONTEXT 53 0 3945 0.0010 0.0000 0.0010
    PREEMPTIVE_OS_FLUSHFILEBUFFERS 52 0 200 0.0000 0.0000 0.0000
    MEMORY_ALLOCATION_EXT 43 0 204066 0.0505 0.0000 0.0505
    PREEMPTIVE_OS_LOADLIBRARY 41 0 81 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_SETFILEVALIDDATA 37 0 37 0.0000 0.0000 0.0000
    RESOURCE_SEMAPHORE_MUTEX 33 3774 780 0.0011 0.0036 0.0002
    LCK_M_IS 31 0 41 0.0000 0.0000 0.0000
    SOS_PHYS_PAGE_CACHE 30 2615 778 0.0008 0.0025 0.0002
    RESERVED_MEMORY_ALLOCATION_EXT 30 0 52706 0.0130 0.0000 0.0130
    LOGMGR_FLUSH 22 0 637 0.0002 0.0000 0.0002
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE 22 0 292 0.0001 0.0000 0.0001
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 21 0 312 0.0001 0.0000 0.0001
    PARALLEL_REDO_WORKER_WAIT_WORK 19 2 1436 0.0004 0.0000 0.0004
    PREEMPTIVE_OS_AUTHORIZATIONOPS 17 0 3026 0.0007 0.0000 0.0007
    WRITE_COMPLETION 17 8 327 0.0001 0.0000 0.0001
    PREEMPTIVE_XE_TARGETFINALIZE 16 0 76 0.0000 0.0000 0.0000
    LATCH_UP 10 4 7 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_GETFILEATTRIBUTES 9 0 36 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_CREATEFILE 6 0 35 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_REVERTTOSELF 5 0 134 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES 4 0 107 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_DELETEFILE 4 0 5 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_REPORTEVENT 4 0 16 0.0000 0.0000 0.0000
    DAC_INIT 4 0 4 0.0000 0.0000 0.0000
    PREEMPTIVE_OS_CLOSEHANDLE 2 0 2 0.0000 0.0000 0.0000
    RECOVERY_MGR_LOCK 2 0 2 0.0000 0.0000 0.0000
    SQLTRACE_FILE_WRITE_IO_COMPLETION 1 0 1 0.0000 0.0000 0.0000
    PREEMPTIVE_FILESIZEGET 0 0 1 0.0000 0.0000 0.0000

    Thanks, Kumar

    Thursday, January 12, 2017 1:55 PM
  • wait_type wait_time_s pct running_pct
    HADR_FILESTREAM_IOMGR_IOCOMPLETION 62630.98 15.5 15.5
    DIRTY_PAGE_POLL 62621.51 15.5 30.99
    SQLTRACE_INCREMENTAL_FLUSH_SLEEP 62598.52 15.49 46.48
    QDS_PERSIST_TASK_MAIN_LOOP_SLEEP 62592.33 15.49 61.97
    SP_SERVER_DIAGNOSTICS_SLEEP 62400.78 15.44 77.41
    SOS_SCHEDULER_YIELD 31948.38 7.91 85.32
    WRITELOG 23750.01 5.88 91.19
    LATCH_EX 11498.5 2.85 94.04
    PAGELATCH_EX 6218.78 1.54 95.58
    PAGEIOLATCH_SH 5652.97 1.4 96.97
    PAGEIOLATCH_EX 4639.53 1.15 98.12
    PAGELATCH_SH 4062.88 1.01 99.13
    LCK_M_U 548.22 0.14 99.26
    LATCH_SH 481.16 0.12 99.38
    PAGEIOLATCH_UP 415.63 0.1 99.49
    IO_COMPLETION 8.04 0 0
    PREEMPTIVE_OS_QUERYREGISTRY 5.34 0 0
    LCK_M_IX 4.9 0 0
    PREEMPTIVE_OS_DISCONNECTNAMEDPIPE 4.81 0 0.01
    RESOURCE_SEMAPHORE_MUTEX 4.55 0 0.01
    PREEMPTIVE_OS_DOMAINSERVICESOPS 4.51 0 0.01
    WAIT_XTP_HOST_WAIT 4.47 0 0.01
    PREEMPTIVE_OS_CRYPTIMPORTKEY 4.16 0 0.01
    PREEMPTIVE_OS_CRYPTACQUIRECONTEXT 3.95 0 0.01
    LCK_M_IU 3.89 0 0.01
    FT_IFTS_RWLOCK 3.82 0 0.01
    MSQL_XP 3.57 0 0.01
    SOS_PHYS_PAGE_CACHE 3.39 0 0.01
    PREEMPTIVE_OS_AUTHORIZATIONOPS 3.03 0 0.02
    PREEMPTIVE_XE_SESSIONCOMMIT 1.87 0 0.02
    PARALLEL_REDO_WORKER_WAIT_WORK 1.44 0 0.02
    CHKPT 0.85 0 0.02
    SLEEP_MASTERDBREADY 0.84 0 0.02
    XE_BUFFERMGR_ALLPROCESSED_EVENT 0.65 0 0.02
    LOGMGR_FLUSH 0.64 0 0.02
    PREEMPTIVE_XE_TARGETINIT 0.51 0 0.02
    PREEMPTIVE_OS_LIBRARYOPS 0.44 0 0
    PREEMPTIVE_OS_LIBRARYOPS 0.44 0 0.02
    PREEMPTIVE_XE_CALLBACKEXECUTE 0.41 0 0
    PREEMPTIVE_XE_CALLBACKEXECUTE 0.41 0 0.02
    ASYNC_IO_COMPLETION 0.34 0 0
    ASYNC_IO_COMPLETION 0.34 0 0.02
    WRITE_COMPLETION 0.34 0 0
    WRITE_COMPLETION 0.34 0 0.02
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 0.31 0 0
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICY 0.31 0 0.02
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE 0.29 0 0
    PREEMPTIVE_OS_NETVALIDATEPASSWORDPOLICYFREE 0.29 0 0.02
    PREEMPTIVE_OS_FLUSHFILEBUFFERS 0.2 0 0
    PREEMPTIVE_OS_FLUSHFILEBUFFERS 0.2 0 0.02
    PREEMPTIVE_OS_COMOPS 0.19 0 0
    PREEMPTIVE_OS_COMOPS 0.19 0 0.02
    PREEMPTIVE_OS_GETDISKFREESPACE 0.19 0 0
    PREEMPTIVE_OS_GETDISKFREESPACE 0.19 0 0.02
    SLEEP_DCOMSTARTUP 0.16 0 0
    SLEEP_DCOMSTARTUP 0.16 0 0.02
    PREEMPTIVE_OS_VERIFYTRUST 0.15 0 0
    PREEMPTIVE_OS_VERIFYTRUST 0.15 0 0.02
    PREEMPTIVE_OS_PIPEOPS 0.14 0 0
    PREEMPTIVE_OS_PIPEOPS 0.14 0 0.02
    PREEMPTIVE_OS_REVERTTOSELF 0.13 0 0
    PREEMPTIVE_OS_REVERTTOSELF 0.13 0 0.02
    PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES 0.11 0 0
    PREEMPTIVE_OS_QUERYCONTEXTATTRIBUTES 0.11 0 0.02
    PREEMPTIVE_OS_LOADLIBRARY 0.08 0 0
    PREEMPTIVE_OS_LOADLIBRARY 0.08 0 0.02
    PREEMPTIVE_XE_TARGETFINALIZE 0.08 0 0
    PREEMPTIVE_XE_TARGETFINALIZE 0.08 0 0.02
    LCK_M_IS 0.04 0 0
    LCK_M_IS 0.04 0 0.02
    PREEMPTIVE_OS_SETFILEVALIDDATA 0.04 0 0
    PREEMPTIVE_OS_SETFILEVALIDDATA 0.04 0 0.02
    PREEMPTIVE_OS_GETFILEATTRIBUTES 0.04 0 0
    PREEMPTIVE_OS_GETFILEATTRIBUTES 0.04 0 0.02
    PREEMPTIVE_OS_CREATEFILE 0.04 0 0
    PREEMPTIVE_OS_CREATEFILE 0.04 0 0.02
    PREEMPTIVE_OS_REPORTEVENT 0.02 0 0
    PREEMPTIVE_OS_REPORTEVENT 0.02 0 0.02
    LATCH_UP 0.01 0 0
    LATCH_UP 0.01 0 0.02
    PREEMPTIVE_OS_DELETEFILE 0.01 0 0
    PREEMPTIVE_OS_DELETEFILE 0.01 0 0.02
    DAC_INIT 0 0 0
    DAC_INIT 0 0 0.02
    PREEMPTIVE_OS_CLOSEHANDLE 0 0 0
    PREEMPTIVE_OS_CLOSEHANDLE 0 0 0.02
    RECOVERY_MGR_LOCK 0 0 0
    RECOVERY_MGR_LOCK 0 0 0.02
    PREEMPTIVE_FILESIZEGET 0 0 0
    PREEMPTIVE_FILESIZEGET 0 0 0.02
    SQLTRACE_FILE_WRITE_IO_COMPLETION 0 0 0
    SQLTRACE_FILE_WRITE_IO_COMPLETION 0 0 0.02

    Thanks, Kumar

    Thursday, January 12, 2017 1:55 PM
  • Sorry Uri for sharing in a raw format of the wait stats captured. Please let me know if you need any other information.

    Thanks in advance.


    Thanks, Kumar

    Thursday, January 12, 2017 1:57 PM
  • You don't mention how tempdb is configured, even though you're concerned about tempdb I/O performance. If you haven't already done so, consider implementing tempdb configuration performance best practice recommendations:

    Recommendations to reduce allocation contention in SQL Server tempdb database

    Optimizing tempdb Performance

    Do you have data/log/tempdb drives formatted with 64k block size, instead of 4k default?

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    Thursday, January 12, 2017 10:55 PM
  • We have suffered enough with the cardinality estimator engine changes during the upgrade from SQL 2008 to SQL 2014. Are you saying that we should be turning off the flags like 4199 etc., so as to make the cardinality engine work similar to SQL 2014?

    After struggling for more than 6 months, the application is kind of stabilized for the SQL 2014 version.

    Hence asking in advance as we don't want to get into such problems again for SQL 2016 upgrade.

    If you are using SQL Server 2016 and compatibility mode of the database is 130 you dont need to enable trace flag 4199 this will automatically take affect if compatibility level is 130.

    For tempdb how many data files have you created what is autogrowth value and initial size or did you made sure all were same. Have you made sure you configured tempdb while you were installing SQl Server 2016.

    What is output of select @@version

    Can you fire perfmon counters as shown in below screenshot and can you show me the data over period of time when you are doing the testing

    Can you then check with SQL Server 2014 that you see difference ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, January 13, 2017 10:03 AM
    Moderator
  • Thanks Shashank for your inputs.

    Here is the output for SELECT @@VERSION;

    Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)   Oct 28 2016 18:17:30   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

    --

    For the tempdb, we have configured it to have 8 data files corresponding to the 8 core server, and the initial size is set to 3 GB per file (unlimited growth). We haven't observed any growth beyond this at the end of the test (both in SQL 2014 and 2016). This configuration is NOT DONE during the installation of the SQL 2016 (but post the installation and before running the perf. test).

    I will get the perfmon counters setup for the next tests, but the bottom line is there is no difference in the work-load or the test scripts, as the same exactly run in the SQL 2014 environment works absolutely fine without any issues.

    Thanks in advance.


    Thanks, Kumar

    Monday, January 16, 2017 10:30 AM
  • ON a side note, we did create a sample script to see if this issue is disk specific or tempdb as such, and the results indicate that inherently in SQL 2016, we are having the high latency issues.

    DECLARE @LoopCounter INT = 1

    DECLARE @Attcount  INT

    WHILE ( @LoopCounter <= 10)

    BEGIN

    begin transaction

    select * into  ##tmpatt from tbltable1 -- containing 700K records

    select * into  ##tmpact from tbl_tmx_activity -- containing 200K records

        PRINT @LoopCounter 

           update ##tmpatt set score = @LoopCounter

           update ##tmpact set InstrNotes = cast (@LoopCounter as varchar(100))

        SET @LoopCounter  = @LoopCounter  + 1

           set @Attcount = (select count(attempt_pk) from ##tmpatt a inner join ##tmpact b on a.activityfk = b.activity_pk)

           drop table ##tmpatt

           drop table ##tmpact

           WAITFOR DELAY '00:00:01' ---- 1 Second Delay

    commit transaction

    END

    ------------

    Results indicate that the latencies for the same query when run in 2016 is ~200 ms, and in 2014 it is 5 ms.

    SQL 2016

    SQL 2014:


    Thanks, Kumar

    Monday, January 16, 2017 1:39 PM
  • The pictures are not clear at all can you please add it again

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, January 16, 2017 1:56 PM
    Moderator
  • Sorry for that.

    SQL 2016 -

    SQL 2014 -


    Thanks, Kumar

    Monday, January 16, 2017 5:27 PM
  • Hi All -

    the same problem can be replicated on any machine with SQL 2016 with the script shared. This seems mostly a problem with the SQL 2016 engine based on the extensive testing I did. I couldn't find any articles stating this as a known issue with the sql 2016 anywhere in the net. Though I could find this, that is talking about anti-virus and all.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d9a4cb9-ea10-4b8d-b9c9-6054676f993d/tempdb-latency?forum=sqldatabaseengine

    Should I log a connect bug? Our next steps on migrating towards SQL 2016 depends on the success of these performance tests and we are stuck badly with the I/O latency issues.

    Anyone aware of any SQL 2016, I/O related known issues? esp. with tempdb? Please guide me.


    Thanks, Kumar

    Tuesday, January 17, 2017 12:13 PM
  • Hi Kumar,

    did you notice difference between Exec Plan, from Sql 2014 and 2016?

    Could you please execute "set showplan_all on" in both and post the results?

    Thanks.

    Miller


    Se sua dúvida foi solucionada marque-a, e, aproveite para divulgar o novo fórum do msdn.

    Tuesday, January 17, 2017 2:36 PM
  • Hi Miller, it turned out to be a problem with the Indirect Checkpoint not being enabled. We are doing some testing around the same and will let this thread know if there is a progress.

    Thanks, Kumar

    Tuesday, January 17, 2017 9:18 PM
  • 1. check the performance counter where the files are there. check for disk/read and disk/write and queue length. in case of disk issue

    2. set the growth to MB rather %., also some initial size.

    3. separate the tempdb files in different disk.

    4. check for RAM and other configuration. change maxdop to 1 in case of parallel process and too much block and deadlocks

    Wednesday, January 18, 2017 9:38 AM
  • Hi Miller, it turned out to be a problem with the Indirect Checkpoint not being enabled. We are doing some testing around the same and will let this thread know if there is a progress.

    Thanks, Kumar

    That could be the reason, please let us know how things are moving further

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, January 18, 2017 10:17 AM
    Moderator
  • Hi Kumar,

    its a great news.

    I don't know if you still need a help, but... try to enable the following flags:

    DBCC TRACEOn (3502, -1);
    DBCC TRACEOn (3505, -1);

    This will make checkpoint process writes to the error log what it’s doing, as the dbid.

    Maybe the dbid can help you to figure out where the Indirect Checkpoint is working.

    Don't forget to turn off the trace with TRACEOff.

    Cheers,

    Miller


    Se sua dúvida foi solucionada marque-a, e, aproveite para divulgar o novo fórum do msdn.


    • Edited by Dobereiner Wednesday, January 18, 2017 8:35 PM
    Wednesday, January 18, 2017 8:33 PM