Which portion takes a long time to complete?

Sometimes the plan cost can tell a porkie and the longest running bit might be the cheaper costed plan.

I see a view, vw_Teva_Territory_ZipCodes,...

Im sure you can find the info in here by removing what is not needed.

Bit incomplete as I need to join to sysobjects and then to sys.schemas to get schema...

CREATE PROC usp_updateStatsAllDbs

AS

BEGIN

SET ANSI_NULLS ON

SET ARITHABORT ON

SET QUOTED_IDENTIFIER ON

--Set quoted_identifier off

DECLARE@dataname VARCHAR(MAX)

DECLARE@dataname_header VARCHAR(MAX)

DECLARE datanames_cursor CURSOR

FOR

SELECTname

FROMsys.databases

WHEREdatabase_id IN ( SELECTdbid

FROMsys.sysdatabases

EXCEPT

SELECTdatabase_id

FROMmsdb..suspect_pages ) AND

name NOT IN ( 'master',...

Cool. Just a note. Because you have used the dynamic-sql method, there is no need for with recompile. I heard Gail mention that she would use the with recompile only...

Good show.

Did you test after each change?

If so, can you let us know which specific change was the one which made the timeouts go away?

The index is (ID,Member_ID,Memeber_Name)

This means that it is like an excel spreadsheet sorted on ID, THEN by Member_id.

So you dont have an index on Member_id.

Conceptually, you have an index on...

Sean Lange (4/7/2015)

---

MadAdmin (4/7/2015)

---

Sean Lange (4/7/2015)

---

MadAdmin (4/7/2015)

---

Read committed also has it's issues.

It is possible to read the same row twice with read committed, once before a change, then after the...

Sean Lange (4/7/2015)

---

MadAdmin (4/7/2015)

---

Read committed also has it's issues.

It is possible to read the same row twice with read committed, once before a change, then after the change.

All isolation levels...

Read committed also has it's issues.

It is possible to read the same row twice with read committed, once before a change, then after the change.

All isolation levels have issues which...

If the splitter function returns a table, i.e. a table variable, you may lose parallelism, which should be OK if your query has high cardinality and supporting indexes.

And yes, as...

Your OR condition makes it impossible for one index to satisfy all the conditions.

You need an AND for composite indexes.

select from tableA where COL1 = @col and Col2= @col2.

Then you...

Well, the existing indexes wont help.

You need 2 indexes. One on ID1 and one on ID2.

Depending on cardinality, the original query should be instant if both the indexes exist

, i.e....

@State nvarchar(max) = '' --> can it be NVARCHAR(10)

ISNULL((Select TOP 1 TotalTaEnrollments From dbo.InstitutionMilitaryTuition pa Where pa.OpeidNumber = dbo.FeedEDData.OpeidNumber),'Not Reported') --> can this not work better with a outer apply?

That...

Eliminate OR condition?

Challenge accepted.

But don't think it will help the optimiser.

Select ID, ID1,Name, Cname from Customer

where not(not(ID =@IDSearch)AND not(ID1 =@Idsearch ))

DeMorgan's Laws (A OR B) = (A' AND...

Run this with your proc name on live vs your local.

I give you this because set options may be a reason why you can say 'works on my machine'.

Issue could...