Inserts Taking Longer Time

May 23, 2007

Hello All,

I have SQL Server 2005 installed on my machine and I am firing following query to insert 1500 records into a simple table having just on column.

Declare @i int
Set @i=0
While (@i<1500)
Begin
Insert into test2 values (@i)
Set @i=@i+1
End

Here goes the table definition,

CREATE TABLE [dbo].[test2](
[int] NULL
) ON [PRIMARY]

Now the problem with this is that on one of my server this query is taking just 500ms to run while on my production and other test server this query is taking more than 25 seconds.
Same is the problem with updates. I have checked the configurations of both the servers and found them to be the same. Also there are no indexes defined on either of the tables. I was wondering what can be the possible reason for this to happen. If any of u people has any pointers regarding this, will be really useful

Thanks in advance,
Mitesh

View 6 Replies


ADVERTISEMENT

Setup And Upgrade :: Differential Backups Periodically Taking Longer Than Expected Time To Complete

Jun 15, 2015

We are running a SQL Server 2008R2 64-bit database system on a Windows 2012 R2 64-bit Standard system. I have noticed in recent weeks that our differential backups periodically are taking longer than expected to complete. The usual amount of time is about one hour, but on several occasions, it has taken upwards to five hours. The nights when the job takes longer to complete are on Friday.

I did some checking online, and one possible reason for this issue is my scheduling the reindexing of the database on the morning of the Differential backup. For example, this past Friday the reindexing occurred at 1:00 AM with the Differential running at 10:00 PM that night.The article that I read suggested the reindexing, which takes several minutes, if that, to complete, should be scheduled to run just before the Full backup job.

View 2 Replies View Related

Restore Taking Longer And Longer

Feb 4, 2008

The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.

declare @filename varchar(255)

set @filename =
(select top 1 physical_device_name
from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf
where bs.media_set_id=bf.media_set_id
and database_name = 'Live_PRD'
and backup_start_date>getdate()-1
and type = 'D'
order by backup_start_date desc)

restore database REPORTS_REP
from disk=@filename
with
move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf',
move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf',
move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf',
replace, stats=2, recovery

View 5 Replies View Related

Backup Taking Longer Than Usaual On Sundays

Mar 13, 2008



Hi All,

My Full backups are taking longer than the usual time on sundays.
I know this has nothing to do with the SQl Server storage engine or Database engine.
i hvae checked there are no jobs ruuning at this time..and this across all the servers sharing the SAN.


How can prove that some thing else is reponsible for this Behvaior and not SQL server.

are there any counters (perfmon) or tools or some sniffers which can tell me what is causing this.

please help.

Thanks in advance.

View 1 Replies View Related

Whats Does This Mean ??? --- SQL Server Has Encountered 1964 Occurrence(s) Of IO Requests Taking Longer Than 15 Seconds

Sep 13, 2007

Hello,

I see the following error on the sql server log of one of our sql servers running sql 2000 with sp4.
SQL Server has encountered 1964 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [h: empdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000534. The offset of the latest long IO is: 0x0000002b09e000


Any idea as to what might be causing this error. Appreciate any comments.

Thanks!

View 3 Replies View Related

Does It Take Longer To Restore The First Time?

May 16, 2007

We moved a 2000 database to another platform by restoring the database. It took a lot longer than I expected. Would it take less time to restore it a second time to the same target database since the allocations are already there?



Thanks

View 1 Replies View Related

Query Takes Longer To Execute The Second Time

Feb 13, 2001

Has anybody come across situations where queries take longer to execute the second time? The server is a dedicated sql server box with 1gb memory.

Thanks in advance.
Praveena

View 2 Replies View Related

URGENT !!!! Transaction Log Backup Running Longer Time

Dec 26, 2002

Hi Guys,

We have a database with 20 gig and with huge transactions. The transaction log backup is scheduled every one hour
from 3.00 AM to 9.00 PM.

We take a full backup in the disk at 9.00 PM and again a full backup in the tape at 2.00 AM

It works fine in the day from 6.00 AM and complete within seconds and the size is approx. 50 to 200 MB.

But the very first transaction log backup at 3.00 AM is running like 3 hrs and the size is approx. 11 gig whick is almost equivalent to the Full backup size. There are some dts packages that are running in the night and as usual reindex, intergrity checks are running and there no large user traffic during night. But I have no idea which the very first transaction log backup in the morning takes longer time and has this bug size. Is there any work around to fix this proble.

Please advise.

Thanks,
Anu

View 7 Replies View Related

Sql Server Is Taking More Time

Jun 11, 2002

I have a VB application which uses SQL server as the database and uses Crystal reports for reporting.We are using a stored procedure to create a report and we pass ID from the vb side to run the stored procedure.
In boston the report shows up in 4 sec.But in california it takes 7 min.
We have a very good network(T3).Why it is taking more time in california ?.
Any ideas ?

View 1 Replies View Related

Query Taking Time

Sep 13, 2001

Hi,
I am running this query and it is taking over 3 minutes.

"select * from table1 where CONVERT(varchar(10),dated,5) = '13-09-01' "

Table1 has a column called dated which is datetime datatype.

Any suggestions how can i optimize this query?I tried Non-clustered index on Dated column and time came down to less than 3 but still more than 2min.

TIA.

View 4 Replies View Related

Time Taking Table

Oct 12, 2007

Dear Experts,
i've one table named table11. in this perticular table, i've 30 columns and 40,000 rows of data.
this table is taking 35 sec for select * from table11.

defnetly it will take more time if i used this in some places like procedures and functions or views like that.

where is the problem? generally it takes that much of time or is there any problem?

guidence please.....

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 4 Replies View Related

Time Taking Query

Nov 2, 2007

Dear All,
here i'm posting my query which is taking 3 minutes

please suggest me the best query


SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val


thank you very much

View 7 Replies View Related

Scheduled Package Runs Longer Time Than Manually Start The Job

May 16, 2008

Hi,

I have a package designed as bring data tables over to SQL Server. There are 9 data flow tasks that runs parallel, to bring 9 datatables over. In BIDS, when I execute the package, it runs like 8 minutes. Or if I start the scheduled job manually, it runs around 8 minutes too. But it runs about 30 minutes at the scheduled time at midnight.

I wonder what I can do to speed up the scheduled job.

Thanks

View 13 Replies View Related

Scheduled Job Is Taking More Time. Any Suggestions ?

Jun 3, 2002

I have a stored procedure when I run it manually it takes 23 seconds.
I Scheduled a job to run the same stored procedure.It is taking 33 minutes.
Any ideas why it is taking so much time ?

View 3 Replies View Related

How To Reduce Time In Taking BACKUP

Dec 6, 1999

Hi guys.

I am having trouble in time issues while backuping my database.

My database size is around 50GB. It is taking around 5hrs.

Is there any way to reduce the 5 hr backup time to 3 or less.

Thanks in advance
MAK

View 1 Replies View Related

ExecuteQuery Taking A Long Time

Sep 4, 2007

Hi:

I have a query which returns approximately 50000 records, I am using a linked server to connect to two databases and retrieve data. For some reason it is taking a liitle more than hour to execute the query, but on MS Sql Server query window it comes after few minutes but the query runs for a long time.

How can expediate my query execution process.

Environment details

Database: MS Sql Server 64bit 2005
MS Sql jar file: sqljdbc_1.2.jar
OS: Windows both server and client.

Connect String in java code:

jdbcqlserver://sample_server:1433;databaseName=sample_db;user=admin_user;password=admin_pwd

and use PreparedStatement and ResultSet.

Regards
Arup

View 2 Replies View Related

Dm Query Taking Long Time

May 16, 2007

I'm running a query (see below) on my development server and its taking around 45 seconds. It hosts 18 user databases ranging from 3 MB to 400 MB. The production server, which is very similar but with only 1 25 MB user database, runs the query in less than 1 second. Both servers have been running on VMWare for almost 1 year with no problems. However last week I applied SP 2 to the development server, and yesterday I applied Critical Update KB934458. The production server is still running SQL Server 2005 Standard SP 1. Other than that, both servers are identical and running Windows 2003 Server Standard SP 1. I'm not seeing this discrepancy with other queries running against user databases.



use MyDatabase

GO

select db_name(database_id) as 'Database', o.name as 'Table',

s.index_id, index_type_desc, alloc_unit_type_desc, index_level, i.name as 'Index Name',

avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,

page_count, avg_page_space_used_in_percent, record_count,

ghost_record_count, min_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count,

schema_id, create_date, modify_date from sys.dm_db_index_physical_stats (null, null, null, null, 'DETAILED') s

join sys.objects o on s.object_id = o.object_id

join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

where db_name(database_id) = 'MyDatabase'

order by avg_fragmentation_in_percent desc

--order by avg_fragment_size_in_pages desc

--order by page_count desc

--order by record_count desc

--order by avg_record_size_in_bytes desc

View 4 Replies View Related

SSIS Package Taking Much Time Than DTS

Apr 14, 2008



Hi,

I have a DTS in SQL Server 2000, Where I am importing some data from a remote server (SQL 2000) to local server (SQL 2000).And this is working fine. it is taking max of 1 min to execute the package.

Now I have created the same DTS in sql server 2005 (SSIS) where the source server is sql server 2000 and the destination server is 2005.and I have created the ssis in that server. The same logic which i have created in sql 2000. But here it is taking almost 10 min to execute the package.

Where as the same in sql server 2000 taking max of 1 min. Why this happening.. Is there any configuration to execute the SSIS package.?

View 12 Replies View Related

Fuzzy Lookup Taking Too Much Time

Dec 14, 2006

I have a SSIS package where a small table of 270 rows are fuzzy looked up with a table in another sql server and inserts the records to a temporary table. This takes more than 3 hours in debug mode or so and never goes beyond this step.I have used a OLE DB destination to insert to temporary table and temporary table doesn't get a value.

View 2 Replies View Related

Restore Taking Very Long Time

May 31, 2007

i have sql 2000 db of about 120 GB. its taking about 10 -12 hours to restore on the same disk as new database.



server configuration is good.



when i try to restore another db of about 10 GB size, its restoring in about 5 minutes.

View 2 Replies View Related

SQL 2012 :: Importing Xer Format Files Through Wizard Takes Longer Time?

Aug 9, 2015

We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.

View 0 Replies View Related

Send Email If Packages Executes For Longer Than A Secified Amount Of Time

Nov 15, 2007



Is there a way ( using an included SSIS task rather than coding a script task) to detect whether a package has run longer than a specified period of time?

So I can send an email to operators notifying them that a job is taking longer than usual.

Thanks

View 12 Replies View Related

MSSQLSeverOLAPServieces Is Taking A Long Time To Start

Oct 13, 2003

Sometime is necessary to stop MSSQLSeverOLAPServieces to do a full backup in my OLAP Server disks. After backup had finished and I tried to star MSSQLSeverOLAPServieces but it takes almost 30 minutes to the services starts.
What can it be causing that?

Paulo

View 5 Replies View Related

Stored Proc Taking A Very Long Time

Aug 2, 1999

I have a stored procedure that normally takes about 5 hours to complete:
DELETE tblX WHERE PROC_DT < dateadd(day, -93 , getdate())

tblX has about 55 million records and has an index on PROC_DT.

I have this running as a scheduled task. Over the weekend, the task executed and it is still running 56+ hours later. Does anybody have any ideas as to where I should look for the problem? I am afraid to kill the process because of the rollback time.

View 4 Replies View Related

Update Taking Long Time In 2000 Then SQL 7.0

Mar 7, 2002

Hi,
I have a table with 48 million rows,when i executed following update query it is taking 10 HOURS in SQL SERVER 2000 with SP1.
Where as when i executed same query in SQL SERVER7.0 with same table then it is taking 13 MINUTES. Comming to Machine...SQL 2000 Server has more processors and greater memory than SQL 7.0 m/c.
It looks strange but this is true.Does any one faced such problem..is there any bug in SQL 2000?????

Here is Query::

update cus_pay_jan_dist set univ_regdate = b.dayid
from cus_pay_jan_dist a with (nolock), tm_dayids b with (nolock)
where a.univ_regdate = b.dayidnum and a.univ_regdate like '2001%'


Thanks
Ananth

View 6 Replies View Related

Stored Procedure Taking More Time Than Sql Query

Oct 6, 2005

Hi

I have one stored procedure and its taking 10 mins to execute. My stored procedure has 7 input parameters and one temp table( I am getting the data into temp table by using the input parameters) and also I used SET NOCOUNT ON. But if copy the whole code of the SP and execute that as regular sql statement in my query analyzer I am getting the result in 4 seconds. I am really puzzled with this.

What could be the reason why the SP is taking more than query,Unfortunately I can't post the code here.

Thanks.

View 1 Replies View Related

Taking Too Long Time For Insert Statement

Sep 19, 2007

Hi All,

Scenario:

There are two applications running on different server say ServerA and ServerB. Both applications are using same database server SQL Server 2005 say ServerB. Called the application as ApplicationA and ApplicationB with respect to Server names

It means for ServerA the database is remote and for ServerB, database is local.

Both the applications are Java application and using datasource to connect to the database. The driver used are SQL Server 2000 driver (which includes 3 jars). This can be a question that why 2000 driver is used for 2005. The reason is, application on ServerA is getting error while using SQL Server 2005 as Driver not proper.

Problem Area:

When ApplicationB (local to database) is doing some DB operations (which includes select and then batch insert), ApplicationA (remote) is trying to insert a record which is taking too long time (around 40 sec.). This is causing timed out in ApplicationA.

ApplicationA is inserting the data into the same table from where ApplicationB is selecting the data.

Any help????

Cheers
Nitin

View 2 Replies View Related

Dtsx Package Taking Too Much Time To Validate

Dec 11, 2007

Does the validation time of DTSX package depend on the amount of data?

I have a data transfer task which contains a oledb source data from a SQL Server 2005 view. Then the data go through 3 Lookup transfromations before going into another view, also on SQL Server 2005 but a different database.
The purpose of this package is to load fact data, so it has to deal with few million of rows. Before setting DelayValidation to true, it takes few minutes just to open the package in BI Studio. Now with DelayValidation set to false, I can open the package without any problem. But it takes more than 5 minutes during Validation, Prepare for Execution and Pre-execution Phase. During the time the memory usage and cpu time on SQL Server goes up significantly. The CPU doesn't hit 100% though. My client machine doesn't have any significant activity.

I have similar packages ( Oledb view -> 3 or 4 lookup Transformations -> Oledb Table) but dealing with dimension data. With DelayValidaion set to False, those packages can be opened in BI Studio within few seconds. They also take only few seconds during those 3 phases before starting actual execution phase.

So I have an impression that the validation time depends on the amount of data in the database. Shouldn't it just depends on the metadata?

View 5 Replies View Related

Update Statment Taking Long Time

Mar 27, 2008



I have an update statment in my SSIS that use to take 10 minutes in SQL 2000 dts and now its take 1 hour 15 minutes in SQL 2005.

this is my sql update statment -
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null

It is using ole db connection. About 36,000 records that it is updating.

I have read ole db can be slow and to use staging table. Does that mean on all updates like this I have to use a staging table and then insert. I didn't use to have to do this in SQL 2000. Has it changed. Are there any other options?


any input greatly appreciated.


View 7 Replies View Related

Transaction Log Backup Taking A Long Time

Mar 30, 2007

Hello,

I'm trying to figure out why my transaction log backup is taking up to an hour to complete. I started off with a full recovery model with a Full database back up every Sunday, differential backups every Tuesday/Thursday and log backups every 5 minutes. I would have thought that the log file backups would execute much quicker because I'm backing them up more often.

Here is my backup statement, I'm hoping I've got a wrong option that you can point out to me:

BACKUP LOG [xxxx] TO [LogFilexxxxBackups] WITH NOINIT , NOUNLOAD , NAME = N'xxxx log backup', SKIP , STATS = 10, NOFORMAT

View 1 Replies View Related

Table Update Process Is Taking Too Much Time

Mar 9, 2008



hi,


I'm using a OLE DB COMMAND component to perform an update (SQL statement) but the procces takes about 9 hours, so I changed it to a stored procedure but it was the same, I need to update about a a million of rows and the package is very simple.

How can I improve the time, Can I use another component or startegy?

thanks

View 3 Replies View Related

Bulk Insert Taking Long Time To Run

Apr 30, 2008



The process is as follow,


The destination table is truncated and indexes are dropped before loading and after data being inserted we re-create the indexes.

Before this, a view extracts data from more than 22 tables from a staging database and tries to insert this data in the destination table.

it used to take 12-15 mins, but since yesterday loading one particular table never completes. While loading, the database is set to Simple recovery. There are no blocking. It's part of a daily batch thats loads 6 GB of data everyday. But while loading on particular table it's just keep running for hours. I tried rebuilding the indexes and re-starting the SQL Server but of no use.


Any help is much appreciated as this production batch job.


Thanks in advance.

View 4 Replies View Related

Adding A New Record Takes Longer And Longer -- Archive? (was Table Help)

Mar 1, 2005

Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be great

View 4 Replies View Related







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