Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gborasaniya
Starting Member
3 Posts |
 Posted - 2008-11-17 : 01:18:44
|
| 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 boxFollowing 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. ? Best Regards,Ghanshyam BorasaniyaSr. DBAwww.asite.comCell +91-98790-93240 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 01:26:04
|
| Could you spot any differences in execution plans between cases where query execution speeds vary? |
 |
|
|
gborasaniya
Starting Member
3 Posts |
Posted - 2008-11-17 : 02:11:19
|
| Hi visakh16, Thanks for your point.I have already studied execution plan, on native database all SQL execution steps has 0% cost, but I can see in execution plan that 100% cost is spent on each 'remote scan' step where update statement is there in all four remote queries which are updating 5 tables across 4 SQL Server Instance (hence each step is taking 20% cost). ( there are two databases on one SQL server instance.)On target table where UPDATE operation is performed, proper index is also there. On execution plan says in next step about Bookmark of the row and table spool (eager spool) step but they have 0% cost in 20% overall cost for the step. It is common step in across database update what I believe.Ghanshyam BorasaniyaSr. DBAwww.asite.com Cell: +91-98790-93240. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 02:15:46
|
| So you're trying to get data from four remote servers? cant you just implement replication among servers and bring reqd data tables to your servers and avoid remote access then? |
|
|
|
gborasaniya
Starting Member
3 Posts |
Posted - 2008-11-17 : 04:24:08
|
| Thanks for your response.Yes, you are right, replication is one of the solution. But this problem is with our staging environment where we 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. Server configuration is scalable alike production environment but still load is comparatively less on staging environment.One more thing I want to highlight is 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 = OLEDBwait resource = DBBOX\SQL Instance (SPID=104) For the same SPID =104 on remote server, following is the session status.waittype = 0x0000waittime = 0 lastwaittype = MISCELLANEOUS but consecutive execution of dbcc inputbuffer show following event info as output in total.1) FETCH API_CURSOR000000000001AE872) (@Param000004 varchar(100))UPDATE [dbname].[dbo].[tablename] SET [email]=@Param0000043) sys.sp_reset_connection;1During 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.Ghanshyam BorasaniyaSr. DBAwww.asite.com Cell: +91-98790-93240. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-17 : 06:35:03
|
| 1)If production database is restored on QA on periodic basis,then why there is a need to remote queries2)You are runnig 64-bit SQl 2005 standard edition,Which has issued with properly allocating memories 3)You are running 3 instances in one physical box with limited memory leading to memory pressure4)There is issue with locking/blocking which might also cause this. |
|
|
|
|
|
|
|
|
Unique performance problem with across database qu