MS SQL Database Performance & Servicedesk

I've been doing some investigations into the performance of the SD+ DB on MS SQL Server, with a view to improving query response speed, specifically when searching for information contained in a request.

I�ve noted a few interesting points.

1. The good news�. By using the SQL performance tuning wizard (I know very lazy of me) and a sample workload (activity) file I�ve been able to gain a 40% improvement in overall database performance! Essentially a result of series of new indexs and statistical flags on 99% of the SD+ tables.

2. The not so good news�. The exiting index�s and statistical flags around the tables involved in request search query (below) are already optimised. Even after a few hours of manual tweaking, the execution times did not fall.

I did however have success tracking down �the dreaded� SD+ request search returning a blank white screen rather to frequently, which is in all versions of SD+!

SD+ performs a very large query in order to find what you have asked it to search for (query below). As you can see I searched for the words �broken printer� and approximately 1 in 3 query�s returned a SQL deadlock victim message.

SQL Query analyser error output�.
�Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.�

The frequency of the deadlock message entirely depends on how busy SD+ is, or more specifically how much other database activity there is whilst you perform a request search. Because the typical the execution time of the request search query is 24 seconds (in our case), it�s is highly likely to be running at the same time as other SD+ query�s, therefore highly likely to be chosen as a deadlock victim.

I�ve had a good dig around the SD+ installation directory and was unable to find where this query is held. My intention was to add a series of �NO LOCK� statements around the select and inner joins, which would prevent the deadlock victim situation.

For those of you that are still reading this thread and are not aware of what a �NO LOCK� statement means�. NO LOCK is a method of reading from a SQL database without locks being granted/applied/used during its run time. The benefit of this is, the query will always return results and does not take out any locks on the DB, thus freeing up resources for other query�s. The down side of this is, because the query is not locking db resources, the query results can potentially be out of date. E.g. if data were to change whilst the request search query was running, the results that are retuned to you would not contain the changed data. In most cases that would be an unacceptable way of performing a query, however because the request search in SD+ isn�t critical to database operations, it�s a very strong candidate for a �NO LOCK� and would greatly improve the user experience!

I wonder if the Dev Team at Manage Engine would consider implementing a NO LOCK on the request search or at a very minimum, trap the deadlock victim error message and display a user friendly error in SD+ rather than just returning a blank white screen to the user?

Regards

Marc

SELECT wos.NOTIFICATIONSTATUS,wos.SHOWNOTESTOTECH,wos.OWNERID,aau.USER_ID,wo.WORKORDERID "RequestID",wo.TITLE "Title",aau.FIRST_NAME "Requester",cd.CATEGORYID,cd.CATEGORYNAME
"Category",ti.FIRST_NAME "Owner",wo.DUEBYTIME "DueBy",wo.COMPLETEDTIME "CompletedTime",qd.QUEUENAME "Group",std.STATUSNAME "Status",wo.CREATEDTIME
"CreatedDate",wos.ISREAD,wo.DESCRIPTION,wos.PRIORITYID,pd.PRIORITYID,pd.PRIORITYNAME "Priority",wos.LEVELID,lvd.LEVELID,lvd.LEVELNAME "Level",wo.MODEID,mdd.MODEID,mdd.MODENAME
"Mode",cri.FIRST_NAME "Created By",wo.WORKORDERID
"WOID",WorkOrder_Fields.WORKORDERID,WorkOrder_Fields.UDF_LONG1,WorkOrder_Fields.UDF_LONG2,WorkOrder_Fields.UDF_CHAR1,WorkOrder_Fields.UDF_CHAR2,WorkOrder_Fields.UDF_CHAR3,WorkOrder_Fields.UDF_CHAR4,WorkOrder_Fields.UDF_CHAR5,WorkOrder_Fields.UDF_CHAR6,WorkOrder_Fields.UDF_DATE1,WorkOrder_Fields.UDF_DATE2,WorkOrder_Fields.UDF_LONG3,WorkOrder_Fields.UDF_LONG4,WorkOrder_Fields.UDF_CHAR7,WorkOrder_Fields.UDF_CHAR8,WorkOrder_Fields.UDF_CHAR9,WorkOrder_Fields.UDF_CHAR10,WorkOrder_Fields.UDF_CHAR11,WorkOrder_Fields.UDF_CHAR12,WorkOrder_Fields.UDF_DATE3,WorkOrder_Fields.UDF_DATE4
FROM WorkOrder wo INNER JOIN WorkOrder_Threaded wot ON wo.WORKORDERID=wot.WORKORDERID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID INNER JOIN WorkOrderStates
wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN AaaUser aau ON wo.REQUESTERID=aau.USER_ID LEFT JOIN AaaUser ti ON
wos.OWNERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN LevelDefinition lvd ON
wos.LEVELID=lvd.LEVELID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN AaaUserContactInfo AUCI1 ON
aau.USER_ID=AUCI1.USER_ID LEFT JOIN AaaContactInfo ACI1 ON AUCI1.CONTACTINFO_ID=ACI1.CONTACTINFO_ID LEFT JOIN WorkOrder_Queue wo_queue ON wo.WORKORDERID=wo_queue.WORKORDERID LEFT
JOIN QueueDefinition qd ON wo_queue.QUEUEID=qd.QUEUEID LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN
WorkOrder_Fields ON wo.WORKORDERID=WorkOrder_Fields.WORKORDERID WHERE ((((((((((((((((((((((((UPPER(SUBSTRING(std.STATUSNAME, 1, DATALENGTH(std.STATUSNAME))) LIKE '%broken printer%')
OR (UPPER(SUBSTRING(wo.TITLE, 1, DATALENGTH(wo.TITLE))) LIKE '%broken printer%')) OR (wo.DESCRIPTION LIKE '%broken printer%')) OR (UPPER(SUBSTRING(cd.CATEGORYNAME, 1,
DATALENGTH(cd.CATEGORYNAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(ti.FIRST_NAME, 1, DATALENGTH(ti.FIRST_NAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(aau.FIRST_NAME, 1,
DATALENGTH(aau.FIRST_NAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(lvd.LEVELNAME, 1, DATALENGTH(lvd.LEVELNAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(pd.PRIORITYNAME, 1,
DATALENGTH(pd.PRIORITYNAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(qd.QUEUENAME, 1, DATALENGTH(qd.QUEUENAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR1, 1,
DATALENGTH(wof.UDF_CHAR1))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR2, 1, DATALENGTH(wof.UDF_CHAR2))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR3, 1,
DATALENGTH(wof.UDF_CHAR3))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR4, 1, DATALENGTH(wof.UDF_CHAR4))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR5, 1,
DATALENGTH(wof.UDF_CHAR5))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR6, 1, DATALENGTH(wof.UDF_CHAR6))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR7, 1,
DATALENGTH(wof.UDF_CHAR7))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR8, 1, DATALENGTH(wof.UDF_CHAR8))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR9, 1,
DATALENGTH(wof.UDF_CHAR9))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR10, 1, DATALENGTH(wof.UDF_CHAR10))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR11, 1,
DATALENGTH(wof.UDF_CHAR11))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(wof.UDF_CHAR12, 1, DATALENGTH(wof.UDF_CHAR12))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(dpt.DEPTNAME, 1,
DATALENGTH(dpt.DEPTNAME))) LIKE '%broken printer%')) OR (UPPER(SUBSTRING(ACI1.EMAILID, 1, DATALENGTH(ACI1.EMAILID))) LIKE '%broken printer%')) AND (wot.THD_WOID = wot.WORKORDERID)) ORDER BY
10 DESC