3 Replies Latest reply on Aug 13, 2018 2:59 PM by lcsw2013

    Can someone help me with database waits? I have trouble tracking potential weakness in our db.




      Parallelism is a setting on SQL we've been messing with for a while.  No matter which option we pick we always end up with cxpacket wait times. And sometimes they hold up as long as 10 seconds or greater indicating potential for delays in execution. Also, Writelog wait time is one that is constantly high. Not sure if queries are suspended to long waiting for resources. But trying to determine how to better test the endurance and performance of my database so I can tweak it's weaks spots and bring it up to optimal performance.


      It's SQL 2014 with the latest updates.

      Windows 2012 R2 Datacenter edition.

      24 vCPU's

      100Gig of ram

      On a brand new low use UCS host. (Database is virtual)

      Connected to a VMAX60 EMC SAN appliance. VMDK's optimized for the fast policy. And the LUN is primarily all SSD's with a small portion on 10k SAS spindles. 30gig Fiber Channel to the SAN and LUN is not shared with any high resource application and storage guys tell me there is barley anything happening on these disks other than our database.


      When database maintenance runs, or if your running diagnostic reports we see the tasks waiting shoot up to 20 thousand tasks stays there for about 1 second and clears down to around 20 to 40 tasks holding which is normal for our databsae. I know the database is as fast as it's weakest link and I'm trying to determine how can I test it to find out it's weakest link. I get the feeling something isn't right but I don't know enough to accurately troubleshoot and test this and need help.


      Any help is appreciated.