I had some time to ponder this, and when you think about it, why wouldn't you expect it to be fragmented? If you really consider how snapshots work, they...

UPDATE:

I figured I would take a look at the current fragmentation before I started my nightly drop/re-create process. To my surprise, the snapshot file is already seriously fragmented. ...

So after nearly 3 months I am following up to say that in the end, the only resolution was to uninstall the 32-bit and install 64-bit (which by the way...

The service account does indeed already have the "lock pages in memory" right assigned to it.

UPDATE:

I have opened a support case with MS support and they have me running some...

AWE is enabled and I have the SQL Max Server Memory at 20GB. (I have 32GB available)

Another interesting observation is that I started tracking my available memory (using the sys.dm_os_virtual_address_dump...

1) The /PAE switch is not supported (or needed I guess) on a 64-bit OS.

2) Following the migration guide I was reading for moving the SQL service to new hardware,...

I'm back with good news!

I was finally able to get around to changing that code and implemented the permanent table solution. As a result, the default trace logs are...

Will do. I'm on vacation this week, and will of course be spending quite a bit of time catching up when I get in next week, but I'll get...

These are all great suggestions. Thanks for all the input guys. I did consider modifying my default trace, but that just didn't sit well with me. I...

I'm actually considering that approach (see my response to John Rowan earlier). The only downside is I have to perform several hundred if exists then update ... else insert...

Initially, I would agree that it doesn't seem to matter, since the application seems to be running well and does not appear to be putting undue stress on the server....

But there is no way to do a bulk insert (from in-memory data) with a stored procedure.

The permanent table needs to have several hundered records either updated (if they exist) or inserted. In situations like this I always try to stage the data in a...

Wow. It seems I learn something new everyday, then un-learn it the next day...

Thanks again for offering your insight.

Thanks for the detailed response Paul. While we're at it, let me drop another riddle out there.

Assuming you are on a 4 core server, what would you chose as...