Post reply

unique performance problem with across database queries and DML operation using trigger

  • November 16, 2008 at 11:10 pm

    #202936

    Hi All,

    We have unique performance problem with across database queries and DML operation on our database platform.

    We have table called companyaddress, among them contact and address information in synced via trigger mechanism across 4 database physical boxes having three SQL server instances installed on each physical box

    Following is the configuration.

    - Dell PowerEdge 2950 physical box has 4 GB RAM and 2 Physical Quad Enabled Xeon Processor (8 CPU)

    - OS is Windows 2003 64 bit Standard Edition with SP2 on all boxes

    - SQL server 2005 64 bit with SP2.

    Proper indexes and updated statistics (update statistics and index rebuild job runs every Sunday midnight), memory and CPU allocation with DTC configuration is also looks perfectly configured.

    Specific information like fname, lname, email, street, city , zip code etc is only synched across 4 SQL server instance lying separately on 4 physical box. Query statements are very simple and underlying tables are also small in size. Database size is also small compared to hardware configuration.

    Enough trial of performance using SQL profiler is taken but query behavior looks wondering. Some time it takes 4 second to execute some time it takes 60 to 120 second varying time. As per our ideal bench mark time it has to take 4 to 8 second in ideal load condition. There is no any I/O operation expect transaction log backup which are also very small in size in KB/MB at interval of an hour.

    Can anybody has any idea is such situation why performance is impacted and what could be the proper step to tune or to identify the root cause. ?

    Regards

    Ghanshyam Borasaniya

    Sr. DBA

    http://www.asite.com

    Cell +91-98790-93240

  • November 17, 2008 at 12:05 am

    #899182

    What wait type are you seeing on the queries when they run long?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 17, 2008 at 12:56 am

    #899192

    Thanks for your response.

    During execution of stored procedure and trigger called within it on one of the table underlying table, following is situation of the wait type and resources.

    On native database call (lets say for SPID = 76), following are reading using sys.sysprocesses system table.

    waittype = 0x006D

    waittime = 0

    lastwaittype = OLEDB

    wait resource = DBBOX\SQL Instance (SPID=104)

    For the same SPID =104 on remote server, following is the session status.

    waittype = 0x0000

    waittime = 0

    lastwaittype = MISCELLANEOUS

    but consecutive execution of dbcc inputbuffer show following event info as output in total.

    1) FETCH API_CURSOR000000000001AE87

    2) (@Param000004 varchar(100))UPDATE [dbname].[dbo].[tablename] SET =@Param000004

    3) sys.sp_reset_connection;1

    During slowness of session where it takes more time we see 'FETCH API_CURSOR000000000001A162' event info constantly sitting as output and takes time to execute. During fast execution we can see only sys.sp_reset_connection as output of dbcc inputbuffer as the remote session is completed and no any waittype or waitresource is used.

    This problem is with our staging environment where are deploying pre-release code, where actually production database is restored for load test and QA purpose. The same mechanism is running on production environment is micro/millisecond .

    Let me know in case any more input needed for the problem.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply