Koen Verbeeck (11/18/2013)

---

Maybe - and just maybe - you might benefit from a (clustered) columnstore index - if you're using SQL Server 2012 or 2014 - because those indexes compress...

A normal uncompressed backup/restore would likely have been faster and less prone to random failures.

I disagree with you completely.

Every table should have a clustered index, but certainly not on 'a random column'. The column has to be carefully chosen. That's irrelevant to this question...

None, there's no index useful for that query.

Restore then, run a checkDB with data_purity, update all stats with fullscan, run DBCC updateusage. Then consider whether you're going to change the compatibility level

Maybe. But it's a temporary mitigation. If you have stuff fragmenting MemToLeave, increasing the size will delay the problems, not prevent them.

Also, that means that, without AWE or 3GB, you...

Post the plan.

Not seeing why you're concerned though. Convert implicit isn't expensive, it just prevents index seeks, and cast does exactly the same.

Oracle_91 (11/17/2013)

---

Can anyone share such instances/examples where locks are being hold and what would you do to overcome the problem. Someone has already killed it and comes to dba for...

1) you can, but you should probably figure out what it's doing first and what will happen when it gets undone.

2) Until the rollback completes. Do not restart SQL unless...

The error is because two files were specificed for the backup. This is commonly by mistake, where there were two files listed in the destination box on the backup dialog...

No guarantee of any order. I think with the open table you can click a button at the top to get the query, add the order by and re-run, but...

A restore recreates the database exactly as it was at the time of backup, including all file sizes. You need the same space for the files as the source database...

Unless you're talking about many minutes of processing time and GB of TempDB space, just let the temp tables go out of scope and they'll be automatically dropped.

If you need to empty the table it's fine, faster than delete. If you don't have further use for the table, drop it.

ganeshc27 (11/15/2013)

---

The column on which the NCI is created, is being used in the WHERE conditions in the queries post this Index creation. How do I find whether this NCI...