Last weekend we moved our production database to a new server. It's a Windows Server 2008 R2 Datacenter. On it is a brand new installation of SQL Server 2008 Enterprise Edition 64-bit. On Sunday, after the move was finished, everything looked normal. But once users started using the application Monday morning, things slowed down to a crawl and have been slow ever since.
I think I've isolated the problem to the tempdb, since nearly all the active processes running when I check are inserts into temp tables. This query:
SELECT '1' AS Number,GETDATE() AS Date INTO #Temp Go INSERT INTO #Temp VALUES ('1', GETDATE()) GO 1000
Takes 20 seconds on my new 2008 server, whereas on the old server with SQL 2005, it takes only 2-3 seconds.
The new server has 128 GB ram and at any given time it's only using 35 GB total for all processes. On the old production server the ram usage is at least 50% at any given point, even with hardly anyone using that, and our dev environment it's about 80% which is good and normal. I have no idea why our SQL Server 2008 on the new server is only using a tiny fraction of the available ram.
We reconfigured our tempdb to use 10 data files of equal size, it was 1 before on our old server with a 8:1 core/file ratio. We have 48 cores on this new server so that was a 48:10 core/file ratio. One of the more sr. DBAs here made 10 more secondary data files for the tempdb and 5 more log files, but this didn't seem to help at all.
I've checked perfmon for total memory and it looks like it's flatlining. I don't have any restrictions on memory configured so it should be using everything available, right?
I've tried googling answers to my questions about the tempdb and memory usage, and all advice seems geared towards earlier 2003 servers, or 34 bit systems. I can't find any relevant information that would help with a Windows Server 2008 R2 datacenter and a SQL Server 2008 instance.
The network guy did also try calling Microsoft and so far they've been unable to help.
Please help me out. I'm really convinced that it's a memory/tempdb issue but I can't seem to get SQL to use all the memory it has available to it.