We have a service which inserts data into our database. When we run our service we noticed that it sometimes took allong time to insert our data in to the database (both insert and update queries). We investigated this and with the help of SP_WhoIsActive and the scripts from BrentOzar we found that we lose time because of PAGEIOLATCH_EX.
When this happens the complete sql engine seems to become unresponsive. If we run a querry in the ssms at that time it will take until the hold up is released before it is able to return info.
Now i'm a bit stuck on how to try and fix this. Can this be fixed?
What we already tried:
-Remove duplicate indexes
-Cleanup unneeded indexes.
-ran a query that to show the relation between scans, lookups, updates and seeks.
OS: Server 2012
SQL server: standard x64 Version 12.0.2000.8
Thanks in advance.