I'm not sure this is the place to get your code rewritten for you.

You would at least need to tell us where some of the bottlenecks are. The problem could...

As has been said, the other way around. The "HEAD" is the problem, so SPID 71 is blocking 67.

Also each extra indent indicates another layer of blocking. In a busy...

Someone has a server restart scheduled for 7:05 pm every night?

There are relatively few things that would force a connection to close, but a restart or Availability Group fail over...

There are a number of good scripts on the internet that will list the blocking chain in order and also tell you the blocking header query. Here's one I use...

The actual backup may not be taking 7+ hours. If they have Verify backup enabled, then SQL does a "logical" restore, reading through the whole backup and checking each page....

As with a lot of things,  "Hash Matches are often better than Loop Joins" has caveats. For small tables a Loop Join may be better, particularly if the inner loop...

The fact the restores take twice as long makes me think the drives may be incorrectly formatted. Assuming NTFS disks, check they are formatted with 64K allocation units and not...

Not sure if people actually read the question.

Best practice for TEMPDB is still to have multiple files. The general rule is one file per physical CPU up to 8 (not...

• This reply was modified 6 years, 11 months ago by Leo.Miller.

To optimize the reporting database we removed all indexes except the required Primary Key (for Transactional Replication). We then reviewed the report queries and designed a few select indexes specific...

I've found the default trace useful, and have had clients who want to keep more than 5 files. I've built a simple Agent job that runs on a scheduled basis...

I've used this and it certainly wasn't overkill. The client had a lot of resources on the SQL Server, but the main database was highly transactional, and performed very poorly...

Thanks, we've looked at both these and found nothing. I have found the NICs are set to AutoDetect, and an FTP on the problem server is only doing 33Kb per...

"When I open SSRS config on Server 1, the server name value that I connect to is SomeServer, as expected. I go ahead and connect and look at the database...

• This reply was modified 7 years, 2 months ago by Leo.Miller. Reason: Quotes didn't come out correctly

To answer your main questions:
If we stop the log shipping during the drill, will we be able to bring up the secondary in a read/write mode ? 

• You...

These may seem like random questions, but:
1) Is the database set for Read Committed Snapshot Isolation?
2) Are there any deletes on the table?
3) Are there any old...