Environment:
OS: 64-bit Windows 2016 server, Windows 10
Database: 64-bit MS SQL 2017 server (standard edition), SQL Azure 2017
Applications (all 64 bit) : (1) Highly multi-threaded C++ windows service application writing to database (2) Web Application used for administration and monitoring (3) 3 other C++ applications also use database for reading and writing.
Problem faced:
1. Deadlock: Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Using SQL profiler (xml_deadlock_report), we have been able to track the problem to some indexes on views but removing the indexes is not the solution as it slows the performance. We need better solution to fix our SQLs.
2. Blocking: There must be quite a few Select calls that are blocking. We have observed that the performance of our C++ program slows down when web application is used.
3. SELECT SQLs: Need to understand if select sqls must always open database in read only mode for better performance.
4. FLAGS: Need to use appropriate isolation levels, and other flags / properties. Complete sanitation needs to be done.
5. Poor performance: In general, database seems to slowing down the performance of the entire application.
Need MS SQL Server database expert who is proficient with SQL profiler and other tracing options to find out the problems and suggest changes.
The expert will be provided access to a VM instance on Azure, which will have all the applications running, SQL server standard edition, SQL Management studio.
The expert will be suitably assisted with necessary support staff to run the tests, collect necessary traces, setup breakpoints, and do whatever groundwork necessary so that the expert does not have to learn the application but rather focus firmly on finding problems at the database end.
Please describe the approach you will take to solve the problem. I am looking for an expert on long-term basis whose expertise we will keep using intermittently.
If you have any questions, please let me know. I am very much available over weekends also.
Thank you for taking time reading this and responding.
With warm regards!
About the recuiterMember since Aug 24, 2017 Rupinder S.
from Singapore, Singapore