In the query which i mentioned in (i) is used to find the execution plan for each batch inside a specific proc. It...

Steve Jones - SSC Editor (6/12/2011)

---

Did you set an sa password? Did you add your account as an admin during setup?

Also, you aren't necessarily running as administrator on the local...

Other way to find the default trace,old fashioned way RDP to the server do a search with...

that is correct, i will test before making changes in production. I am trying to find most effecient and quick way if i can?

thanks..let me provide some more details..there is a table more than 100 gb in size with a column ntext which occupies 90% of total table space. Goal is to change...

I need to convert...

There are few more details i am working on. The same sproc works perfectly fine on other server, i am still leaning towards tempdb. I have a case opened with...

Thanks everyone.

Thanks everyone. I don't think it is parameter sniffing issue because it doesn't take any parameters at all. Mentioned below is the query where it occasionally hangs.

sELECT Row_number() OVER...

'

Hey , can you please post the actual link for the hotfix?

Craig Farrell (6/5/2011)

---

If the spid isn't blocked, it's in the tempdb spooling. Look for contention on the raid for the tempdb.

I realize that's kind of a leap, but it's...

Few things to check:

i) run sp_who2 every 10 secs when the last step occurs and capture the data to see if any blocking

ii)are your indexes and statistics upto date?

iii)if you...

GilaMonster (6/4/2011)

---

A covering index won't make that seekable. There's no predicate (other than join predicates) for the Cov table, it's probably faster to scan than to seek for each value...

Thanks Gail. Gail do you know of any good article which explain different types of cache in memory for sql server?

Thanks. After reading the replies sounds like i am not duplicating anything here, just will need to revisit Update stats strategy for big table. Thanks