Notes

->Got connected to DB Server through vnc.eclinicalworks.com rdp.
->Found that the server was utilizing the Ram upto 92 % that means out of 256 GB only 22GB was free.
->So checked that by logging in to app server but was not able to judge if it was actully slowness or the connection was slow on eclinicalworks.
->tried checking was not able to understand what could utilize the memory
->Checked sp_whoisactive found that there were many process which were in locked
->Went on Call with on call take Navneet Navneet
->Informed him that we found that memory utilization and the sp_whoisactive found that there were many process which were in locked.
->Acording to him he tried and verified few thing but was not able to actully troubleshoot on performance point.
->So we got a respond from Jalaj and we started the working with him.
->So he checked that the queries were getting locked whenever there was Delete or Insert or Select query was running on vitals and labdata.
->So Jalaj asked me to kill the process which were locking the process so did that and then we ran query as
update statistics vital
which worked for Temperory purpose and then the query started going out and later we saw that again the quer on vitals started locking the prcoess for long duration.
->So after few things Jalaj asked to run the following Query as
update statistics vitaltypes
->Aslo Jalaj suggested that practice is on synchronise mode of replication should not be there and aslo need index maintenance on fragmentation level.
1)enc
2)vitals
3)itemdetails
4)items. For following tables
->So practice DBA changed it to async.
->We Found that there was query specifically which was locking it following is the query

select * from ( select distinct v . value as vitalvalue , id . value as uom , date , Row_Number ( ) over ( order by DATE desc ) as RowCounter from VITALS V inner join VITALTYPES VT on V . VITALID = VT . ITEMID inner join ENC on ENC . ENCOUNTERID = V . ENCOUNTERID inner join ITEMDETAIL ID on V . VITALID = ID . ITEMID where ENC . PATIENTID = 84917 and VT . TYPE = 'WT' and LEN ( LTRIM ( RTRIM ( V . VALUE ) ) ) > 0 and ID . PROPID = 62 ) Temp where Temp . RowCounter between 1 and 1

->So there were two queries which Jalaj emailed me to execute so that he could check and further troubleshooting.


SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
Plan_handle, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC;
GO

->We Emailed Jalaj the query_plan for those queries also two queries are taken into .xml files and
attached to the case

SELECT TOP 10
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%select * from ( select distinct v . value as vitalvalue , id . value as uom , date , Row_Number ( ) over ( order by DATE desc ) as RowCounter from VITALS V inner join VITALTYPES VT on V . VITALID = VT . ITEMID inner join ENC on ENC . ENCOUNTERID = V . ENCOUNTERID inner join ITEMDETAIL ID on V . VITALID = ID . ITEMID where ENC . PATIENTID = 84917 and VT . TYPE = 'WT' and LEN ( LTRIM ( RTRIM ( V . VALUE ) ) ) > 0 and ID . PROPID = 62 ) Temp where Temp . RowCounter between 1 and 1%';




select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
cross apply sys.dm_exec_query_plan(cp.plan_handle) qp
go


->After that we were still not able to Fix it So Finally there was conference call between practice DBA and they Figured that there was change in settings which is call Parallelism it should be at 8 and not 1