Downside is that snapshot isolation puts extra load on your TempDB

I generally prefer to keep two fulll backups (weekly), the diffs made against them (daily) and and of the log backups going back to the first of the fulls. That...

What do you mean by 'memory consumption issues'?

Since everyone's beaten me to the 'don't shrink', I'll just touch on other areas

You said the DBs are in full recovery. Do you have log backups running and, if so,...

Where was the table you were saving to?

Depends. Were you running it to file or to table....

Rename the table and see who complains? 😀

Seriously, there's no easy way. You'll have to trace the server for a while (a day, a week, a month) until you're happy...

Not exactly.

SQL will use the index that is most optimal for the particular query. If none of the NC indexes are useful, it will use the cluster. That's probably not...

Don't worry about self-blocking. It's usually short lived and just means that pieces of the query are waiting internally for other pieces.

Less activity on the test server. Is your query having to wait for locks? Wait for IO? Wait for time on the CPU? All are possible and more likely on...

Do you have anything else running on this server? Any antivirus or other IO filter drivers? The sec/write is way too high, but the writes/sec aren't. Doesn't make sense.

Could you...

Last time I gave...

Windows authentication is best. It's more secure than SQL authent. That said, their windows accounts only need access to SQL, not to the server itself. They don't need any rights...

To extend your example, if there's a frequent query for all people living in a certain city, then I would probably want an index on the Address table on City...

It's not really possible to say where that comes from. It doesn't look like a system query, but that doesn't mean it isn't.

I would suggest you set up a server-side...