Sample Header Ad - 728x90

SELECT xyz INTO #Temp from SSMS is 5x longer in one environment than another

6 votes
1 answer
96 views
First time posting, I hope I'm doing it right. I'm a "dba by accident" who is learning a lot but admittedly still has a lot more to learn. Here is my brain dump:
  1. Prod server has 5 million rows. I copied those rows to a Test server.
  2. Using SNAPSHOT or READ UNCOMMITTED produce different elapsed times, but the ratio between Prod and Test is always ~5:1 (14 minutes:3 minutes with SNAPSHOT, 4 minutes:sub-1 with READ UNCOMMITTED)
  3. Using STATISTICS IO ON reveals that most of the reads on Prod are logical (~90%) while none of the reads on Test are logical. I think that should mean Prod would be faster if anything.
  4. Most of the time appears to be going to waits of type "PAGEIOLATCH_SH."
  5. I don't think different statistics can matter, since no index should be used on an unfiltered, unjoined, unsorted query.
  6. SELECTing the top n rows on both servers reveals a very linear scale... the ratio is always ~5:1.
  7. The comparison is not apples-to-apples, but I think the only relevant differences are activity and storage.
  8. I have performed the comparison when activity was next to nothing, and saw the same results.
  9. I discovered that autogrowth was set to 1MB (ugh), which I have since changed but I wonder if this has led to fragmented storage at the file system level.
  10. The database is on a separate volume from the Windows installation, but it does share a volume with tempdb and with a FILESTREAM store, which I assume could also lead to fragmented storage.
  11. The "optimize drives" windows utility says the volume is "OK (98% space efficiency)" which seems to suggest that maybe file system fragmentation isn't a problem.
Questions:
  • Are my thoughts and assumptions above correct?
  • Is there anything I'm blatantly overlooking in my thought process?
  • What are good candidates for the next steps in this troubleshooting process?
  • What other information can/should I have provided?
  • Thank you for your attention and help! EDIT: Added rest of time information in 2nd statement.
Asked by Kyle Chandler (63 rep)
Dec 29, 2015, 10:45 PM
Last activity: Dec 30, 2015, 05:45 AM