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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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