3

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.

  • Sorry, I will try there next. We restarted the server about an hour ago with the tembdb data files set up as 24 data files, 512 mb each, 10% growth, got rid of the secondary data files and went back to just 1 log file. It seems to have helped. If it slows down again after a few hours I'll check serverfault. –  Apr 08 '11 at 19:24
  • Hmmm, Even on my laptop that query takes about a second looking at `select MAX(Date),MIN(Date) from #Temp`. What does `sys.dm_os_wait_stats` say? – Martin Smith Apr 08 '11 at 19:28
  • Is Tempdb on a volume by itself? I would also make the initial size of tempdb large enough the it doesn't need to grow under normal use, autogrow takes time, and causes fragmentation. – SqlACID Apr 08 '11 at 20:54
  • Are you testing on a loaded machine, or are the users running queries while you are doing your test? That test runs in less than a second on my two year old laptop. With the setup you have, that machine should't even notice that you are running that script. I'm surprised that it would take so long on your old server, unless you are testing while live. What kind of disk/storage setup are you running? Are you remoted onto the server while running that? If not, the network round-trip time for all of those "(1 row(s) affected)" is included in that time. – Darin Strait Apr 09 '11 at 15:07
  • @mage - Perhaps you could you add some additional info about disk config and performance stats relating to disk io times and queue length? – Chris Apr 09 '11 at 19:30

3 Answers3

4

Your Sr. DBA doesn't know what he is doing. Adding multiple log files does nothing to improve performance unfortunately. It's a shame that he doesn't know how log files work. Log files are used sequentially and if you add 5 more log files they won't be used anyway unless the first one is completely used. In normal day to day operations that won't happen.

As per adding multiple data files to tempdb, there is some conflict between MSFT and industry experts on the recommendation. MSFT plays nice and recommends 1:1 for core:files but in all cases that is NOT necessary. Industry experts say only 1:1/4 to 1:1/2 is enough but you need to watch for the 2:1:1 (Page Free Space i.e PFS bottleneck) and 2:1:3 (SGAM bottleneck) and tweak the number of files as necessary. In some extreme cases, you may have to add more files than the number of cores also but its a big "It Depends".

Coming to the memory issue, have you checked the % use of PageFile, Page Life Expectancy, buffer cache hit ratio. If these numbers look good then It may be the case this new server not stressed enough.

YOu need to look at the wait statistics information before changing the # of files in tempdb. If 24 files worked for you then its good, but look at the wait stats and find out if tempdb is the bottleneck. Note that there are 2 common types of bottleneck for tempdb (IO +allocation bottleneck). If it is allocation bottleneck then you may also want to use TF 1118.

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type, 
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE); -- percentage threshold
Jim B
  • 24,081
  • 4
  • 36
  • 60
Sankar Reddy
  • 1,374
  • 8
  • 8
  • Good answer but I disagree that the number of tempdb files is somehow in conflict. If you want to guarantee performance use 1:1 tembdb files/cores. If (for some bizzare reason) you are trying to minimize the nmber of files ou can ususally get away with 1-1/2 – Jim B Apr 11 '11 at 14:57
  • Jim, We have to disagree. These days with the # of cores around 48/64/128 becoming normal having that many files for tempdb as the default is NOT a good practice nor gives you performance. In fact it may reduce ur performance if u don't have allocation bottleneck. Note that TF 1118 and temp table cache help a lot in reducing this allocation bottleneck. – Sankar Reddy Apr 11 '11 at 15:51
  • Have you seen this : http://sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-TF-1118.aspx – Jim B Apr 11 '11 at 20:09
  • I agree that on a very underutilized server it will cause a minor performance decrease, the times you need it (when the sever is busy) ,you need it. You could spend all day looking for latch contention but, in my experience, when folks are buying 128 core machines, it's not becasue the money was burning a hole in their pocket. Maintaining a modern service oriented architecture means that standarization is prefered over optimization. Rather than tune every server individually I'll take the minor performance hit. – Jim B Apr 11 '11 at 20:16
2

Besides what @Sankar explained, there is a known issue after upgrade regarding a SQL Server running on Windows 2008 R2 with the server running power saving mode on(which is on by default) and it impacts query performance especially if your servers are not under huge pressure(CPU might be running as half slow to save power). check out this this and this blogs for details.

DaniSQL
  • 1,107
  • 7
  • 12
0

Hey guys, thanks for all the helpful advice and links. I've passed on a lot of this information to our sys admin, since I don't actually have admin rights to this server, only to SQL. After Friday when we restructured the tempdb files to 24 data files, and got rid of the secondary data files and extra log files, that seemed to help a lot. We didn't have a lot of load on Friday afternoon or over the weekend though, so it was hard to tell if that alone fixed the issue.

There was some more work done over the weekend that I wasn't made aware of until yesterday. They installed SQL Server 2005 on the server and several service packs. (I guess they wanted to have a backup instance available, I don't really know the reason) When the 2005 instance was active, RAM use shot up to normal levels. The SQL server 2005 instance was removed, RAM use remained high for the 2008 instance as well, which is good - we wanted 2008 to start using all the RAM available to it. So I don't know if it was the 2005 instance that kick-started something, or one of the service packs (though they were all old ones that should not have been necessary at this point) but now RAM is up where we want it too.

I'm sorry if I didn't get back to everyone about specific stats. I'm a mere mid-level DBA and really have no business mucking around in this sort of thing, and it was probably a miracle I happened to google and find the tempdb core:file ratio issue.

I'm assuming the tempdb primary file structure was the key. So, I hope at least this might help anyone else out there who has the same issue pop up.

mage
  • 1