Header

Friday, July 13, 2007

Help my log file is growing huge !

Scenario:
Database file size (.mdf): 250 MB
Log file size (.ldf): 10 GIGABYTES

Quick solution
Make a transaction log backup of your database. Shrink the log file. Make a new transaction log backup.

See BOL for the How to on your version of the SQL Server.


Reason
Many administrators mistakenly think that using the full recovery model (which is default) and then making a daily full backup is sufficient maintenance.

The problem is that the full backup does not flush the inactive transactions from the transaction log when the full backup finishes - this is the case both for SQL 2000 and SQL 2005.

There are different ways to solve the problem - the right one in my opinion being regularly to back up the log file - which flushes the log - and then solving the problem in the long run.

Long term solution
Schedule at least one daily transaction log backup - I would recommend in the middle of the work-day if only 1. Eg. Full backup at 23:00, T-Log at 12:00. If the database has any important activity during the day (and most does) then you properbly want to take transaction log backup every 2 or 3 or 4 hours during the day. Worst case - you can go back to the time of the last t-log backup. If you only take one full backup a day - worst case you need to go 24 hours back......

How many files in a filegroup

Because SQL Server opens one IO thread for each file pr. filegroup - it gives the server a higher read/write performance to have more than one file pr. filegroup.

This does not mean though, that you should have as many files as possible! The optimal amount of files pr. filegroup is between 0.25 and 1 pr. CPU. Meaning that if your Server has 4 CPUs you should have between 1 and 4 files pr. filegroup.

Please note that when I write CPUs I acutally mean physical CPU Cores !

My experience is that you normally would benefit the most by aligning the number of files with the number of CPUs. Eg. a server with 1 CPU with 2 physical cores should have 2 files pr. filegroup in general.

This is always true for TEMPDB where you should have 1 file pr. CPU.

Wednesday, July 11, 2007

Performance tuning counters for SQL Server

As an instructor I always tell my students to make a performance baseline on their SQL Server so that they have something to compare against, when (not if) the users complain about performance. But my students and customers often ask – which counters should I monitor then?

Well here is, to my experience, a good list to start with. Arguably you could narrow or widen the list – but for a start this list has in my experience proven to be sufficient.

Performance object

Counter 

Warning 

Alert  

Notes

Memory 

Pages/sec 

100

200

I know that many sites say 20 – but that was from the NT4 days with 128 MB RAM

Memory 

Available MBytes 

200

100

 

SQL Server: Buffer Manager

Buffer Cache Hit Ratio 

90

85

 

SQL Server: Buffer Manager 

Page Life Expectancy 

350

300

 
     

These should be per physical disk 

   

Physical Disk 

Disk Reads/sec 

No levels – just monitor these 

Physical Disk 

Disk Writes/sec 

No levels – just monitor these

Physical Disk 

% Disk time 

No levels – just monitor these 

Physical Disk 

% Disk time  

No levels – just monitor these 

Physical Disk 

Avg. Disk Queue Length 

1,5

2

This is x times the number of spindles in the raid set. E.g. Raid 1 with 2 disk = 4

     

Processor 

% Processor Time 

70

80

 

System 

Processor Queue Length 

 
     

Network Interface 

Bytes Received/sec 

No levels – just monitor these 

Network Interface 

Bytes Sent/sec 

No levels – just monitor these 

Network Interface 

Output Queue Length 

No levels – just monitor these

     

SQL Server: General 

User Connections 

No levels – just monitor these 

SQL Server: Memory Manager 

Connection Memory (KB) 

No levels – just monitor these 

SQL Server: Memory Manager 

Total Server Memory (KB) 

No levels – just monitor these


 

You don't have to monitor the counters all the time to create a good baseline, the warning and alert levels are included for those of you who want to implement the counters in your normal performance monitoring system like MOM.

Move a database between servers - WITH the logins

Quite a few times I have been asked if moving a database from one server to another would be any problem.

Generally this is not a problem - although you can save yourself some trouble by moving the logins for the database first.
The How-To is:
1) Script the logins for the database on the source server by using the scripts listed below (Originally posted by Microsoft)
Remember to filter the logins the script generates - it takes every login on the source server and you generally only want those with reference to the database being moved.
2) After filtering the logins from the source server apply the needed logins on the destination server before attaching the database.
3) Detach the database on the source server, move it, attach it on the destination server
The detach/attach procedure can most easily be done from Management Studio by right-clicking the database name in the object explorer -> Select Task -> Select Detach. On the destination server right click the folder Databases -> Select Attach.
The scripts that script the loginsThe scripts is originally posted by Microsoft. They come in two variants - one for SQL 2000 and one for SQL 2005.
Both scripts consist of the same. 2 stored procedures - sp_hexadecimal, and sp_help_revlogin. sp_help_revlogin genereates a new script in the result windows with one line for each login on the server. Copy the text result from the result window, and filter it before applying it on the destination server.