Update Query Running Very Slow

Apr 17, 2008

Hi all,

I'm having what you might call an optimisation issue - but I'm also not sure if my approach to this problem is right. I've spent the whole day trying various methods but none seem to be performing as admirably as I'd hoped.

Basically, here's the scenario:

1. Log files are being inserted into a SQL table via Log Parser 2.2.
2. I have a lookup table of ip address ranges to countries and other geographic data.
3. Once the log row has been inserted from Log Parser, I want to update the same log table with data from the lookup table.

The main problem seems to be the updating (the initial insert from Log Parser is lightning quick).

I initially wrote a trigger on AFTER INSERT on the log table that converted the actual user's IP address into IPNumber format using a simple algorithm, then pumped the IPNumber into a quick select query which retrieved the geolocation data. Then, in the same trigger, there was an update statement which basically said:

update dbo.Logs
set [Country] = @Country
where [IpNumber] = @IpNumber and [Country] = Null

Therein lies the problem. The update statement slows everything down to almost a standstill and also seems to obtain a lock on the table.

Critical factors:

1. The log table must remain readable.
2. The query must execute in seconds -- not over half hour :)

I've experimented with various combinations of indexing, so far to no avail.

Any suggestions would be very much appreciated.

Regards

View 10 Replies


ADVERTISEMENT

Very Slow Running Update Query Query

Nov 19, 2004

I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on

the query thats causing teh problem is


--//////////////////////////////////// this is the one thats running


Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions


any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one

Dave

View 6 Replies View Related

Slow UPDATE - Running For 16 Hours And Counting...

Jun 7, 2004

The following basic UPDATE SQL statement has been running for 16 hours and counting. I need to get this done ASAP.


UPDATE Recipients SET UndeliverableTime = getdate()
FROM Recipients
INNER JOIN Domains ON (Recipients.DomainID = Domains.ID)
INNER JOIN Undeliverables ON (
Recipients.UserName + '@' + Domains.Domain =
Undeliverables.EmailAddress)


Is there any way I can see how far this has gone and how long it will take to finish? Will this take another hour to finish or another week?

Both tables (Recipients and Undeliverables) have approximately 80 million records

I did a nearly identical operation with another table that had only 7 million records and it took 10.5 hours. I hope this doesn't scale linearly to 115 hours.

I am tempted to cancel, retune, and rerun but that may be trigger a really expensive rollback operation that could take days. Any ideas?

thanks!

View 14 Replies View Related

Triggers Running Slow (Update Trigger)

Jul 20, 2005

am using FOR UPDATE triggers to audit a table that has 67 fields. Myproblem is that this slows down the system significantly. I havenarrowed down the problem to the size (Lines of code) that need to becompiled after the trigger has been fired. There is about 67 IFUpdate(fieldName) inside the trigger and a not very complex selectstatement inside the if followed by an insert to the audit table. WhenI leave only a few IF-s in the trigger and comment the rest of thecode performance increased dramatically. It seems like it is checkingevery single UPdate() statement. Assuming that this was slowing downdue to doing a select for every update i tried to do to seperateselects in the beginning from Deleted and Inserted and assigningcolumns name to specific variables and instead of doingif Update(fieldName) i didif @DelFieldName <> @InsFieldNamebeginINSERT INTO AUDITSELECT WHAT I NEEDENDThis did not improve performance. If you have any ideas on how to getaround this issue please let me know.Below is an example of what my triggers look like.------------------------------------Trigger 1 -- this was my original designCREATE trigger1 on TableFOR UPDATEASif update(field1)begininsert into AuditSELECT What I needENDif update(field2)begininsert into AuditSELECT What I needEND...... Repeated about 65 more timesif update(field67)insert into AuditSELECT What I needEND---------------------------------------------------------------------------Trigger 2 -- this is what i tried but did not improve performanceCREATE trigger2 on TableFOR UPDATEASDeclare @DelField1 varcharDeclare @DelField2 varchar....Declare @DelField67 varcharSelect@DelField1 = Field1,@DelField2 = Field2,....@DelField67 = Field67From DeletedDeclare @InsField1 varcharDeclare @InsField2 varchar....Declare @InsField67 varcharSelect@insField1 = Field1,@insField2 = Field2,....@InsField67 = Field67From Inserted-- I do not do if Update() but instead compare variablesif @DelField1 <> InsField1beginInsert into AUDITSELECT what I needendif @DelField2 <> InsField2beginInsert into AUDITSELECT what I needend............if @DelField67 <> InsField67beginInsert into AUDITSELECT what I needend----------------------------------------------IF you have any idea how to optimize this please let me know. Anyinput is greatly appreciated. I do not have a problem with triggersdoing what they are supposed to, they are very slow this is myconcern. The reason I gave you two examples is because i suspect ithas something to do with the enormouse amount of code inside thetrigger. both examples perform about the same whether i use the twohuge selects from the Inserted and Deleted or not.Thanks,Gent

View 1 Replies View Related

Query Running Slow

Aug 24, 2007

hi

if suppose one query is running slow than what steps we can follow to optimize it...

this question is asked me in interview.

thanx

View 3 Replies View Related

Slow Running SELECT TOP Query

Aug 27, 2007

I have a SELECT TOP query in order to return x number of top records from a table which has the indexing service enabled on it, such as this :


SELECT TOP(15) * FROM [TableName] ORDER BY [ColumnName]


and also there are not that many records(MAX 100 rows) kept in the table at the moment however, it will grow later.
The issue stems out from the ORDER BY [ColumnName] part of the syntax that changes the TOP selection order which makes the query to run very slow as I have also analyzed in the SQL SERVER QUERY ANALYZER.
Anyhow, I need to have the ORDER BY statement to show the data based on different columns either ascending or descending.
There might we workarounds to achieve the same goal that I am not aware of.
Any thoughts is appreciated.

View 3 Replies View Related

Slow Running Query With A High Lob Logical Reads, Lob Data

Jun 1, 2007

we had some slow down complaints lately and this query seems to be the culprit almost every single time. The estimated execution plan is a clustered index seek as there is a clustered index on the uidcustomerid column. setting profile statistics on shows that every time it executes it does an index seek.

profiler session showed a huge number of reads for these queries depending on the value being looked up. 1500 through 50000. i set up profile io on and the culprit is lob logical reads. everything else is 0 or very low. in this case lob logical reads is over 1700.

3 of the columns in the select statement are text columns. when i take them out of the query the lob logical reads drops to 0 and goes up incrementally as i add each column back in.

is there anyway to improve the performance without changing data types to varchar(max)?


select SID,Last_name,Name_2,First_name,Middle_initial,Descriptives,Telephone_number,mainline,Residence,ADL,
DID_number,Svce_street,Svce_town,Svce_state,Svce_appt,Mailing_street,Mailing_town,Mailing_state,Mailing_appt,
Mailing_zip,Listing,Addl_listing,Published,Listed,Gold_number,PIN,status,SSnumber,tax_jurisdiction,
Bill_date,Past_balance,Service_start_date,Service_end_date,LOA,FCC_type,Line_type,I_W,Jacks,Voice_messaging,
vms_ring_cycles,CCS,phonesmarts,ringmate,voice_dialing,Bill_detail,Contact_Number,Contact_extension,
Best_Time,suspend,suspend_start,suspend_end,credits_allowed,credits_granted,home_region,Calling_Plan,Local_Plan,
Local_Plan_Rate,Flat_Rate,Sales_agent,Community,Building_Mgmt,How_Heard,Incentive_1,Incentive_1a,Incentive_1b,
Incentive_1c,Incentive_2,Incentive_2a,Incentive_2b,Incentive_2c,Incentive_3,Incentive_3a,Incentive_3b,
Incentive_3c,block_operator,block_collect,block_group,block_adult,block_call_return,block_repeat_dialing,
block_call_trace,block_caller_id,block_anonymous,block_all_high_toll,block_regional_and_ld,block_DA_Call_Completion,
block_DA,block_3rd_party,bank,prepayment,dial_around_number,custid,waive_interest,Financial_Treatment,
Other_Feature_1_code,Other_Feature_1_rate,Other_Feature_2_code,Other_Feature_2_rate,Other_Feature_3_code,
Other_Feature_3_rate,Other_Feature_4_code,Other_Feature_4_rate,Partial_Account,mail_date,snp_1_date,snp_2_date,
terminate_date,snp1notified,snp1peak,snp1offpeak,snp2notified,snp2peak,snp2offpeak,avg_days_paid,Pulled_Ld,SNP1,
SNP2,Treatment,Collections,Installment,Nynex_BTN,LD_rate,local_discount,to_month,rounds_up,full_package_made,
local_made,PIC,LPIC,tax_exempt_local,tax_exempt_federal,CommissionedAgent,LDRateID,UidCustomerId,
accVchLineClassUSOC,block_Inter_Reg_LD,block_international,block_DA_3rd_Collect,block_DH2,block_ISP_2,block_ISP_3,
block_ISP4_3_GBAS,block_ISP3_3_GBAS,block_collect_only,block_LD_Reg_DA,block_usage_based,block_ISP5_3_GBAS,
block_ISP5_2_GBAS,block_group_adult,csr_PIC,csr_LPIC,csr_SA,csr_exception,cutover_status,cutover_datetime,
OutsideAgent,prfVchAttributes,uidResellerID,Category,uidDealID
from profiles where UidCustomerID in (352199267)

View 3 Replies View Related

Report Running Very Slow Compared To Query Analyzer - High TimeDataRetrieval

Jun 26, 2007

Hi,



I have a report in SQL Reporting Services 2005 which calls a stored proc and the report takes a very long time to run and sometimes returns zero records. But when i run the stored proc in query analyzer it takes about 4 seconds!!



I have checked the execution log on the RS using the below sql:






Code Snippet

use ReportServer

Select * from ExecutionLog with (nolock) order by TimeStart DESC



It shows that i have a large amount of time for the dataretrieval (601309ms, about 10mins) and does not return any records most likely because of a query timeout:



TimeDataRetrieval TimeProcessing TimeRendering Source Status ByteCount RowCount
601309 2227 3 1 rsSuccess 4916 0



The weird thing is that when i run it in query analyzer, i get about 400 records in 4 seconds !!



I dont understand what RS is doing to take up so much time like this to retrieve data.



The report is very simple - it basically returns the records straight out into a table.



The only thing I somewhat suspected was a parameter data type conflict between RS and SQL, specifically dates. I have a start and end date parameter in the report - i tried specifying this as date and string to see if it made any difference but it didn't.



Any help would be greatly appreciated.

View 19 Replies View Related

Update Query Is Slow With Merge Replication?

Dec 4, 2014

I have a database with enabling merge replication.

Then the problem is update query is taking more time.

But when I disable the merge triggers then it'll update quickly.

View 3 Replies View Related

SQL 2012 :: Update Query Is Slow With Merge Replication?

Dec 3, 2014

I have a database with enabling merge replication.

Then the problem is update query is taking more time.But when I disable the merge triggers then it'll update quickly.

View 1 Replies View Related

DataAdapter.Update Is Running This Query... What Is Wrong With It?

Jan 18, 2008

My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!

exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.

View 1 Replies View Related

DTS Running Too Slow

Aug 8, 2000

I have a dts job set up to transfer 550,000 records from a dbf file into a sql server. If I just let it run, there is a 9-10 minute delay, then it starts. If I try to schedule a job, it fails completely. I looked up ways to get it to execute quicker, mainly going to the advanced tab of the transform arrow and making the inserts 1000 at a time, the table locked, turning constraints off. Any advice on how to speed it up or why the job is failing?

View 7 Replies View Related

Slow Running SP

Apr 27, 2002

Hi All

Last week, we upgraded to sql2000 from 6.5. Everything went on fine. we re-compiled all the procedures. When i try to run a procedure, its running for long time - more than 10 hours (in sql 6.5 it runs for 50 mins). do i need to set any procedure cache?.

Also, the server CPU usuage is constantly high - more than 80%. It was fine till last week.

Any suggestions?

Thanks in advance

Jaya

View 2 Replies View Related

SP Running Slow

Aug 30, 2006

i have written a SP, but it eventually runs slower. i have to run this SP 500 times.

do you know what is causing that?

View 13 Replies View Related

Slow Running

Jul 1, 2007

when i turn on my pc, it takes about 10 minutes from turning on to actually be able to use.

View 4 Replies View Related

Slow Running Server

May 2, 2001

Our server is running. There are no locks, and server has been rebooted but the problem is still there. This has been going on for some time now. I intend to restart the server. Does anybody have a quick solution, please help. Thanks for your assistance!!

View 4 Replies View Related

Slow Running Server

May 17, 2001

Please what do I look out for 6.5 if I want to troubleshoot for slow running.
Thanks.

View 2 Replies View Related

SQL Server Running Slow Down

Jun 6, 2001

Hi,

When first time I start my sql server is running faster. After 10 to 15 days later, sql sever performance is very slow. After I restart SQL service, to become normal.

Thanks
Mohan

View 1 Replies View Related

ADO Inserts Running Slow

Oct 13, 1999

Hi list,
I'm a long time lurker on this list and really enjoy the discussions, although I rarely get a chance to participate.

Here is my situation: We are importing chunks of data (500 records at a time) from a C++ interface. The records have to be transformed before inserting into the target table which I am doing using a stored proc which is working fine. The records are in memory in C++ and the programmer is looping through the records building inserts into a temp table through ADO (which my proc picks up). The server business object is using the connection.execute method which is inserting one record at a time. That part of the process is taking over 15 seconds for 500 records which is the bulk of the total time.

My question is: Using ADO is there a better way to insert these records into the temp table? I see mention of a recordset interface but my programmers are new to ADO and since I am the DBA and have never used ADO, I am not sure what to tell them.

Any insight would be greatly appreciated.

shawn

View 2 Replies View Related

Slow Running Cursor...

Apr 14, 2004

I have linked three SQL Servers together, and have written a stored proc that has a cursor that joined three tables from one of the linked servers. When I pull the SQL out of the cursor definition and run it in a query window it runs fine, but when I run the stored proc that simply steps through the same select result set it is too slow for words. It also throws a warning about serial isolation levels. Is there any way I can fix this.

David

View 1 Replies View Related

Sql Server Running Slow

Mar 2, 2004

Hi,

Our main production server has started running slow, it is a dual zeon thingy with plenty of ram so hardware is not an issue.

Basically a service connects to the database and executes a few stored procs, the only way I can get the system up to speed again is to recompile one of the SPs but that is only a temporary fix.

Anyone had a similar thing?

Can anyone give me help on performance tuning in SQL server 2000.

Thanks

View 2 Replies View Related

Need Help, DTS Is Running Extremely Slow!!!

May 13, 2004

Hi guys.

I have a DTS package ON SQL 2000 which transfer data from AS400 to SQL 2000 using an ODBC Client Access 5.1 (The DSN was configured by a sysdmin on the AS400 so it is configured properly).
When i execute the package manualy (by right click and "execute package") the package runns fine and ruterns data in no time (Eproximatly 30000 rows in 15 sec).

The problem starts when a job executes the same packagee!!!
When i start the job, the DTS package is running Very Very Slow!!!!
sometime it takes Hours to return a few rows! and it seems that it is stuck.

The SQLAgent is running as a NT Account with Administrator rights, and has full access to the AS400!! so the problem is not the Agent.

by monitoring the AS400, i have noticed that the job/DTS is retreaving the first fetch quickly , and then it is in a waiting status

i have tried everything and cant seem to get this problem fixed

Does anyone know what could be the problem?
I Need Help Quick!!!
Thank You

Gil

View 5 Replies View Related

Slow Running Proc's

Jan 30, 2008

Hello All,
I have two procedures being run one after the other.
when I run proc1 it runs for about 15 min.
Now the proc2 is dependent on proc1, when I run proc2 it runs for 45 min.
If I run both the proc's simultaneously through .net code it takes more than 1 hour. Can anyone of you tell me where would be the problem.

Thanks in Advance.

View 6 Replies View Related

Slow Running Function

Oct 23, 2007

Hi, when the following function is used within the where statement of a select statement it runs very slowly, anyone know why?



ALTER FUNCTION [dbo].[fn_GetLastDayOfLVPeriod] ( @pInputDate DATETIME )

RETURNS DATETIME

BEGIN



DECLARE @vLVDPeriod varchar(4)

DECLARE @vLVDYear varchar(4)

DECLARE @vOutputDate DATETIME

SELECT @vLVDPeriod = LVDPeriod

FROM dbo.tblMIDates

WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)

SELECT @vLVDyear = LVDYear

FROM dbo.tblMIDates

WHERE CONVERT(varchar(8), Date, 112) = CONVERT(varchar(8), @pInputDate, 112)

SELECT @vOutputDate = MIN(Date)

FROM dbo.tblMIDates

WHERE LVDPeriod = @vLVDPeriod

AND LVDYear = @vLVDyear

RETURN @vOutputDate

END

View 5 Replies View Related

Urgent SQL Jobs Running Very Very Slow

Dec 29, 2001

Hi,

I have SQLServer 6.5 SP5a update running on Windows NT 4.0 SP6
with 4 gig RAM and 4 processor.

Suddenly the SQL 6.5 jobs running on the production server started running very very slow. A job that suppose to run in 30 minutes are running like 2 hours and completing successfully.

(I suspect the after the Norton Anti virus automatic live update may be the reason but not the Second Vulnerability as mentioned by Microsoft Bulletin last week)

I check the SQLServer, ran the performance monitor, checked pagefiles, disk space, databases,memory, tempdb. Everything seems to be normal.

I rebooted the server, checked any other process making that slow. But no use.

Please help me out with this issue as this is a production and the CRM applications from the clients uses the database server.

Thanks in advance,
Anu

View 1 Replies View Related

Crystal Reports Running Slow ??

Nov 9, 2004

All,

I have been tapped to help with fixing a reporting tool. We have a Sql Server database/crystal reports(10) setup. I havent had the chance to look at the tables in the DB yet, but I was told that aggregate tables were used. In my past experience with crystal reports, we used database views to feed crystal reports (in Oracle). I was thinking that I could somehow use views instead of tables and then try to re-index the base tables and compile satitics (if theres such a thing with Sql Server). I was also going to look into bottlenecking and locking (table locking as opposed to row or page locking for the lookup tables...to reduce overhead on the main tables) but, I'm not sure if it'll make a difference since this is just a demo server with no major traffic hitting it yet.

The question is, does anyone have any experience with crystal reports running slow with Sql Server, what should I look out for??

'Wale

View 9 Replies View Related

SQL Server 2005 Running Slow

May 3, 2007

Hi all,

I am having a problem ,SQL server is running very slow.This is happening some days only.For example my stored procedure ususally runs less than 2 minutes, some days will take 13minutes.I dont understand the problem.All the stored procedure having the same problem.Sorry, I am not a DBA,basically a devloper.Daily morning we are taking the DB backup and indexes already applied.DB size 10461.06 MB,RAM 4GB,CPU usage is less than 50%,This is a Cinema Database,so lot of users are accessing at same time(Web,IVR,cinema ticket counters etc).We are using SQL reports.Because of the stored procedure running slow,can not view the reports.pls advice..

please help me..If you need some more information please ask ..

Thanks in advance.

View 16 Replies View Related

Visual Studio Running Too Slow

Jan 27, 2006

Hi friends
after working visual studio (on my report model project) few minutes it runs too slow. i mean clicking on entities ,attributes takes ages to finish. I opened task manager i see "devenv.exe" is taking more than 800,000 k !!

am using sql server 2005 standard edition.
have you seen similar problem.
Thanks for your help.

View 19 Replies View Related

Stored Procedure Running Slow In ADO.NET

Jan 9, 2008

We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks

View 22 Replies View Related

Slow Running Insert Statement

Apr 6, 2007

I support a website (ASP.NET 2.0) where recently users have been unable to insert data due to timeout issues. The functionality executes a query that inserts a single row into a SQL Server 2005 db table. I tried running this query from the backend, and it took 4:48 to insert a single row! Interestingly, after that initial agony any similar inserts I tried took no time whatsoever. I have checked the execution plan, but unfortunately don't really know what I'm looking for with inserts, as most of my experience with execution plans is with select statements. Any resources anyone could point me to for troubleshooting this would be much appreciated.



Thanks,



-Dave

View 3 Replies View Related

ASYNC_NETWORK_IO Qyery Running Slow

Oct 5, 2006

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

Environment

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.


Thanks
-Ashis

View 9 Replies View Related

Application Slow - Running Double Take 4.4 On SQL 2000

Jun 6, 2005

Hi,

We are having SQL2000 Advance Server.
4 processor with hypherthreading, Memory 4 GB and it is a high transactional OLTP server. We are also running Transaction Replication on that server.

We recently bought Double Take and implemented on the server with File Difference with block check sum option for Disaster Recovery Purpose.
The Queue and the Log file folder is on local drive where the system doesn't use that folder except double take.

We are replicating from Source to Taget thru the WAN (DS3).We are replicating approx. 200 Gig of Data but just the difference.
Now the CPU usage is normal,Memory utilization is normal, but the network is a major problem as the Applications connecting to the Server timesout and the applications running very slow.

We have set just like the Tech support recommended.

I would appreciate, if someone give us some recommendations to run the double take without any problem.

Thanks in advance.
Anu

View 3 Replies View Related

SQL Server Running Slow On A High End System

Mar 7, 2004

I have about a 447 MB SQL server 2000 database on a desktop PC acting as a QA server. The hardware specs of the QA box are as follows:

CPU: P4 2.4 GHz
Memory: 1GB
Drives: 80 GB IDE

I recently purchased a Dell PowerEdge 2650 server to act as the staging box. The staging box has

CPU: P4 2.4 GHz
Memory: 2GB
Drives: 40GB SCSI, mirrored

I made a backup of the database on the QA box, and restored it on the staging box. Yet when I run something as simple as a select query (select * from <table>), the less powerful QA box is faster.

I figured maybe the statistics are different on the staging box. I ran dbcc showcontig to make sure the statistics were identical. Also ran RedGate's SQL compare and data compare to make sure everything was identical.

I figured maybe the query optimizer needs to be tweaked. I recreated the indexes and updated statistics on the staging box. The queries actually got slower as a result.

I thought maybe SCSI drives are slower. Tried breaking the mirror on the staging box. No luck. Put the mirror back in place, ran a test where I copied a large folder from one directory to another on the staging box. Repeated the same test with the same data on the QA box. The staging box was more than twice as fast than the QA box.

It doesnt appear to be a problem with the query, adjusting memory in SQL server has not effect, both boxes are using SQL server 2000 SP3, why is the bigger machine running queries hundreds of milliseconds slower than the smaller machine? Any help will be appreciated!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved