Grumpy Old DBA - Beware the darkside !

Index Rebuild Observations

As part of a series of migration tests carrying out an all index rebuild on a user database has been used to compare relative performance.

As the application is a true 7 x 24, there are no maintenance windows so index rebuilds are very intrusive and elicit user complaints.

For SQL 2000 dbcc dbreindex is used, for SQL 2008 ( and SQL 2005 ) ALTER INDEX ALL xxx REBUILD is used.

It wasn't the times which were particularly of interest but a difference in SQL 2008 between the index rebuild when the database was in Simple Recovery vs Full Recovery.

• For those technical little details all the servers use the same quad intel procs and have 4 sockets. Memory is either 32 or 64 GB, SQL 2000 is x32, SQL 2005/8 is x64 all Enterprise SQL.
• Tests on SQL 2005 were carried out as an afterthought, the hardware did not have the capacity to use the same database. ( The migration is 2000 - 2008 so 2005 was not part of the testing )
• Database files were sized such that there were no autogrow events. Index rebuilds were carried out on a fresh restore each time.
• Clearing cache(s) and/or restarting sql server and/or running tests without restoring the database did not appear to make any difference, although the number of tests like this were limited.
• Servers are all SAN attached although the vendors and configs are different. The same database was used for all sql 2000/8 tests, database converted to sql2008 mode for SQL 2008.
• The index rebuild code is in a sp_ stored procedure in master.

Anyway for SQL 2000 the index rebuild time was approx 14 minutes regardless of database recovery model.

SQL 2008 managed approx 4 mins in simple recovery and 9 mins in full recovery.

The tests were repeated up to 80 times each.

The calls to rebuild each index are built within a while loop; rewriting the proc to use a cursor reduced the 9 mins to 8 mins 30 sec on SQL 2008, this was not tried on SQL 2000.

A brief set of tests on SQL 2005 showed the same type of variance between recovery models..

Although I was not particularly looking for differences concerning parallelism I did run a few tests with it on and off.

These were not so scientific, however with SQL 2000 there didn't appear to be a significant difference, maybe 10%, but my tests with SQL 2008 showed that with parallelism off the index rebuilds took three times as long.

One last observation was that on average running the SQL 2008 index rebuild as a job took 1 minute longer than calling the procedure from a query window.

Setting Environment variables

Now you may well ask what on earth windows server environment variables have to do with SQL Server, and it's a fair question and only relevant in certain circumstances.

I'm currently running a series of replay profiler traces on a server, I need these to run in 24 hour periods, I don't want to run them across the network and I have a separate lun especially for such purposes.

The problem is that for one reason or another the C: drive partition is very small, this means that the temporary files created by profiler, not the actual trace files I'm writing to disk, become very large and by default they will get written to the C: drive because that's where all the environment variables are initially set. I personally find that the default behaviour of so many applications which write to your profile a real pain in the backside both on servers and workstations, but that's another matter.

There are two sets of variables which have to be changed to avoid the issue with temp files when running prolonged profiler traces and other processes which generate temporary files.
The first set which need to be changed are the user variables, these are local to the profile and have to be edited again if a profile is deleted.
(  I suspect changing this for the default profile will achieve this but I don't know how you achieve this )
There is also a second set which must be changed and these are the system variables for TEMP and TMP.

Now you could do this through the GUI but ad-hoc changes on a production server this way does not provide a repeatable process, what we need is a script!

Thankfully microsoft have provided us with SETX

To change a users variables the command line SETX command should be used
e.g. SETX TEMP T:\Temp  and SETX TMP T:\Temp
To change the system variables the following keys need to be changed
HKEY_LOCAL_MACINE/System/ControlSet001/SessionManager/Environment/
Keys TEMP and TMP set to the required location.
This affects all users and only needs to be done once
This can be achieved by appending a     /m   to the SETX command.  NB. Make sure the location folder for Temp has been created first.

NB. I don't believe SETX is available by default in workstation o/s

To return the Environment Variables and their values type SET in a cmd session, below is a typical output from windows server 2008

Microsoft Windows [Version 6.0.6001]

Copyright (c) 2006 Microsoft Corporation. All rights reserved.

C:\Users\GrumpyOldDBA>cd\

C:\>set

ALLUSERSPROFILE=C:\ProgramData

APPDATA=C:\Users\GrumpyOldDBA\AppData\Roaming

CLIENTNAME=MyWorkStation

CommonProgramFiles=C:\Program Files\Common Files

CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files

COMPUTERNAME=MITHIAN

ComSpec=C:\Windows\system32\cmd.exe

FP_NO_HOST_CHECK=NO

HOMEDRIVE=C:

HOMEPATH=\Users\GrumpyOldDBA

LOCALAPPDATA=C:\Users\GrumpyOldDBA\AppData\Local

LOGONSERVER=\\DC-10

NUMBER_OF_PROCESSORS=16

OS=Windows_NT

Path=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32

\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Dell\SysMgt\RAC5;C:\Program File

s (x86)\Dell\SysMgt\oma\bin;C:\Program Files (x86)\NetIQ\AppManager\bin;D:\Progr

am Files (x86)\Microsoft SQL Server\100\Tools\Binn\;D:\Program Files\Microsoft S

QL Server\100\Tools\Binn\;D:\Program Files (x86)\Microsoft SQL Server\100\Tools\

Binn\VSShell\Common7\IDE\;D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Bi

nn\;D:\Program Files\Microsoft SQL Server\100\DTS\Binn\

PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC

PROCESSOR_ARCHITECTURE=AMD64

PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 15 Stepping 11, GenuineIntel

PROCESSOR_LEVEL=6

PROCESSOR_REVISION=0f0b

ProgramData=C:\ProgramData

ProgramFiles=C:\Program Files

ProgramFiles(x86)=C:\Program Files (x86)

PROMPT=$P$G

PUBLIC=C:\Users\Public

SESSIONNAME=RDP-Tcp#0

SystemDrive=C:

SystemRoot=C:\Windows

TEMP=C:\Users\GrumpyOldDBA\AppData\Local\Temp\3

TMP=C:\Users\GrumpyOldDBA\AppData\Local\Temp\3

USERDNSDOMAIN=UK-Berkshire.GOD.LOCAL

USERDOMAIN=UK-Berkshire

USERNAME=GrumpyOldDBA

USERPROFILE=C:\Users\GrumpyOldDBA

windir=C:\Windows

C:\>