Post reply

Taking More Time to execute then the previous

  • September 23, 2014 at 1:27 am

    #397386

    HI,

    I'm working in DWH environment Every day many data gets extracted transformed & loaded But from last week i see that there are taking more time then previously executed.

    I don't have a option to run SQL profiler on production.

    Does Trace ON 1222 will impact on performance on production if enabled.

    so what should i check for them & what need to check for improving performance of these servers?

    i check fragmentation, Disk Space, Error Logs daily wat more need to check the improve the performance of server on DWH environment

    Thanks In Advance

  • September 23, 2014 at 2:01 am

    #1747321

    Is the load process incremental or do you reload all of the data every day?

    If the tables are getting bigger, it is normal that the load time increases a bit.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 23, 2014 at 2:51 am

    #1747338

    No, traceflag 1222 won't have noticable impact. But all that traceflag does is write deadlock graphs to the error log. Are you having deadlocks (batches failing with error 1205)?

    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

  • September 24, 2014 at 5:11 am

    #1747783

    Rebuild the index and also use dbcc command for same

  • September 24, 2014 at 11:25 pm

    #1748037

    Even without running a trace, you may still have a chance to see what is going on.

    First, you can find the execution time and query plans of all queries in the cache using: sys.dm_exec_query_stats. If the number of queries on the system is low, this will likely contain nearly everything that has run lately.

    Second, you can see what waits you have by reading data out of sys.dm_os_wait_stats. To use this view, you need to first take a snapshot before the ETL runs and then delta that with a new snapshot after the ETL has run. This gives you the aggregate of the waits which happened during the run.

    These two views together might give you a good idea of what is going on. Especially if you can compare the before/after picture.

    SSMS Expert

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply