If it is blocking users until all inserts have finished you probably have a transaction running that spans all inserts. How is the script started?

To find the most expensive queries you could for example use code like this:

SELECT top 20

max_elapsed_time/1000000.0 as max_duration,

total_elapsed_time/1000000.0 as total_duration,

execution_count,

char(13)+char(10)+

(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) *...

I find it unlikely that the cast to nvarchar is causing your performance problems. If you are updating a large number of rows, most of the time is probably spent...

hahnorlaska (10/12/2010)

---

Yes, I had misunderstood.

The issue with the Except is that it only exists to handle the rare (relatively) occasion where something went wrong as the process is...

I have SQL2008 development edition SP1 CU6

select @@version

Microsoft SQL Server 2008 (SP1) - 10.0.2757.0 (X64) Jan 8 2010 19:55:08 Copyright (c) 1988-2008 Microsoft Corporation ...

hahnorlaska (10/12/2010)

---

I could use an indexed temp table, but that temp table will hold millions of rows of data in the very near future. The data volumes involved has...

It works for me with a test script.

You can see how the following works for you:

use tempdb

GO

CREATE TABLE [dbo].[geo1](

[id] [int] IDENTITY(1,1) NOT NULL,

[latlong] [geography] NULL,

CONSTRAINT [PK_geo1] PRIMARY KEY CLUSTERED...

Your problem is caused by how SQL server estimates the number of rows for a statement like

select ... from PART where PARTNAME LIKE @p

In the posted execution plan you...

I totally agree with bitbucket.

Please read the first link in his signature.

Your need to make your question much clearer.

At a minimum you need to post sample data in the form...

The fill factor only applies when an index is created or rebuilt.

SQL server does not try to maintain the fill factor when updating or inserting values.

Quote from BOL:

The fill factor...

I suggest you break up the query in two parts using a temporary table.

First select the duplicates into a temporary table.

Then insert all data not included in the duplicate table.

The...

160 is a non-breaking space.

You very often get such characters if you copy and paste text from the web.

YSLGuru (8/20/2010)

---

UPDATE

Well this test of creating a duplicate index by a differnet name has appeared to of worked. The purge tool ran to completion without errors and the Index...

The main problem with INSERT INTO is that it creates a lot of log when inserting large amounts of data. SELECT INTO is minmally logged which means that it is...

BULK INSERT is only used to insert data from a file into a table.

When copying data between two sql server tables you could either use SSIS or you could use...