While figuring out a consolidation strategy for Sql
Server, I was concearned that the memory issues (
http://blog.mischel.com/2008/10/14/copying-large-files-on-windows/)
affecting large file copies
would also effect Sql server backups to a cifs location. The reason I wanted to
do this was to consolidate the existing backup server onto the new hardware. It
was ageing and was limited in its current throughput. As the backup disk
was SAN based it should be easy to unplug from the old server and plug into the
new server. However, knowing consolidation projects as intimately as I do, I
know it could take a while to consolidate the remaining Sql Servers
onto the new box. I didn’t want the backups from the old servers to starve the
new server of memory as it does this.
So I ran some tests on the new server
which was running:
-
Windows 2008 server
-
48Gb RAM
-
24 Cores
First I created a network share and
backup folder. I then backed up a 40Gb file from a Sql 2005 Windows 2003 Server.
The server had just rebooted and there was 40Gb of headroom. Monitoring the
RAM usage I only observed a 1.8Gb increase in RAM and 100Mb increase in page
file.
I then checked the impact
that the Netbackup tape backup software had in
copying the file to tape and it appears that this process only took up
200Mb.
Pushing a file from the 2008 server
consumed 1 Mb and pulling a file consumed 30 MB of RAM.
Job done, while I can see there is still
and impact of copying and backing files to a server, its not huge compared to
the file size its working with . As long as I leave an appropriate amount of
free memory and lock pages in memory I am not likely to see major implications
of this.
Emil Fridriksson also has a
couple of extra tweaks that can be made if the
problem becomes quite severe.
When I visit a client site to troubleshoot deadlocks, it is
always a cause of annoyance when I cannot go through historical deadlock events to determine
the frequency and causes of these deadlocks. I either have to talk them
through enabling trace flag 1222 or explain to them how to enable profiler to
capture specific deadlock graphs. Even then its not possible to retrospecivley go back
and examine deadlocks before this point.
I recently came accross this article by Jonathan Kehayias about how to retrieve
historical deadlock graphs with 2008 extended
events. These scripts have proved invaluable for me in going back
in time.
You may need to upgrade to Service Pack 2 to
get it to work correctly, but heres a copy of the
final script:
declare
@xml xml
select
@xml =
target_data
from
sys.dm_xe_session_targets
join
sys.dm_xe_sessions on
event_session_address = address
where
name = 'system_health'
select
CAST(
REPLACE
(
REPLACE
(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),
'<victim-list>'
, '<deadlock><victim-list>'),
'<process-list>'
,'</victim-list><process-list>')
as
xml) as
DeadlockGraph
FROM
(
select @xml as
TargetData) AS Data
CROSS
APPLY TargetData.nodes
('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData (XEvent)