Check this thread, you may get some ideas.

http://www.sqlservercentral.com/Forums/Topic624723-5-1.aspx#bm624760

I don't know storage. I just want to share my bad experience happened two years ago. it may not relate to your case.

At that time, we had a cluster SQL...

Our company has a project about Government Health. In this project, Client asked to audit any changes against more than 20 tables. So we created triggers (insert, update,delete) to every...

Ken McKelvey (4/1/2010)

---

If myTable is actually a complicated view, try avoiding subqueries and use a derived table instead:

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

LEFT JOIN

(

SELECT DISTINCT T1.ReferenceNumber

FROM myTable T1

WHERE T1.ReferenceNumber NOT...

Ken McKelvey (4/1/2010)

---

How does this do?

SELECT UID, ReferenceNumber, Type

FROM myTable

WHERE ReferenceNumber NOT LIKE 'TR%'

AND Type = 1

UNION ALL

SELECT T.UID, T.ReferenceNumber, T.Type

FROM myTable T

WHERE T.ReferenceNumber NOT LIKE 'TR%'

...

here is execution plan

|--Merge Join(Inner Join, MERGE:([Union1010])=([T].[UID]), RESIDUAL:([SQLAdmin].[dbo].[Mytable].[UID] as [T].[UID]=[Union1010]))

|--Stream Aggregate(GROUP BY:([Union1010]))

| ...

Execution plan please?[/quote]

Because this is production sensitive data and company policy does not allow me to disclose this. I will build test table with full column list and post execution...

lmu92 (4/1/2010)

---

Did you try the solution I posted earlier?

SELECT UID, ReferenceNumber, TYPE

FROM #tbl myTable

WHERE ReferenceNumber NOT LIKE 'TR%'

AND EXISTS (SELECT 1 FROM #tbl myTable2 WHERE TYPE = 1 AND...

Compared to my original...

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[myTable]

(

[UID] ASC

)

CREATE UNIQUE NONCLUSTERED INDEX [IX_ReferenceNumber]...

lmu92 (3/31/2010)

---

I'm not sure if I'm totally wrong, but isn't that WHERE condition identical to WHERE ReferenceNumber NOT LIKE 'TR%' ?

Reason:

With your first subquery you include all UIDs with Type...

Table structure

CREATE TABLE [dbo].[mytable](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[UID] [uniqueidentifier] NOT NULL,

[ReferenceNumber] [varchar](20) NULL,

[Type] [int] NOT NULL,

[Col4] [varchar](50) NOT NULL,

[Col5] [int] NOT NULL,

[Col6] [int] NULL,

[Col7] [uniqueidentifier] NULL,

[Col8] [int] NOT NULL,

[Col9] [varchar](max)...

Yes. The result is different.

I found the reason.

Our IT people cloned this server and built another brand new server. So all the server setting on new server is pointing to this server, including maintainence...

Hi,

Can anybody shed a light for me?

Yesterday, I deleted Maintainence plan for backup database including scheduled job.

And I disabled Integrity Check in scheduled job and removed Scheduled time on...