microsoft Archives - Networking HowTos

Measuring Microsoft SQL Query Performance

February 7, 2012 Database, Microsoft SQL

Minimizing the time it takes for an MS SQL query to run is important when creating an application or website. You can always add more resources on the SQL server, such as RAM and CPU, but that doesn’t really fix the source of the issues, especially if the there are large volumes of SQL transactions. Microsoft SQL Server has the ability to display statistics on queries, to determine the CPU time, and elapsed time of an SQL statement.
You can use these statistics as a guide to determine how long each query may take, and then try and optimize the query, and check the statistics again. Inefficient stored procedures, views with complex joins, recursive triggers, etc, can all potentially increase the query time drastically.
A sample of what the performance statistics information looks like:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 293 ms.

To turn statistics on, execute the following SQL statement in a SQL Management Studio query window.

SET STATISTICS TIME ON

Now after running each SQL query, you can check the ‘Messages’ window/tab (instead of the ‘Results’ window/tab), to see how long in milliseconds it took to run the query.
To turn statistics off, execute the following SQL statement in a SQL Management Studio query window.

SET STATISTICS TIME OFF

If you want to just enable the statistics for a single query, you can set the statistics to be on, and then off again straight after the query.
For example:

SET STATISTICS TIME ON
SELECT * FROM vProducts ORDER BY ProductCode DESC
SET STATISTICS TIME OFF

This will output something similar to the following in the ‘Messages’ window/tab.

(163 row(s) affected)
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 293 ms.

'TRUNCATE_ONLY' is not a recognized BACKUP option in SQL 2008

January 27, 2012 Database, Microsoft SQL

In Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.
The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.
The error message that occurs when you try the ‘TRUNCATE_ONLY’ option in Microsoft SQL 2008 is as follows:

Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

To truncate the transaction log file in Microsoft SQL Server 2008, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).
In the process of making the database recovery model “Simple”, it truncates the transaction log file, as it is not used with the simple recovery model.
This can be done manually via the Microsoft SQL Server Management Studio tool, or it can be done via SQL statements, and scripted if needed.
SQL statements to change the database model of a specific database:

ALTER DATABASE [<database_name>] SET RECOVERY SIMPLE
ALTER DATABASE [<database_name>] SET RECOVERY FULL

After doing the above, you can shrink the transaction log file using the following command:

DBCC SHRINKFILE(<log_name>)

For more information on shrinking the transaction log file, please check out the “How to shrink a Microsoft SQL transaction log file” article.

How to shrink a Microsoft SQL transaction log file

January 26, 2012 Database, Microsoft SQL

The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
Because of these abilities, the transaction log file can actually grow over time, and become excessivly big, and potentially filling up the hard disk space on the machine that Microsoft SQL is running on.
Shrinking the transaction log file required two parts. First of all you need to truncate the log file itself, and then secondly you need to shrink the file itself. The truncate part clears the contents of the file, but it doesn’t actually reduce the file size of the transaction log on the file system.
To truncate/clear out the transaction log, you need to perform a transaction log backup. If for some reason you are not able to do a transaction log backup (usually due to the size/disk space restrictions), you can perform a “TRUNCATE_ONLY” backup, which just clears/truncates the transaction log without creating a backup file.
To do a “truncate only” backup, you would run the following query:

BACKUP LOG [<database_name>] WITH TRUNCATE_ONLY

(Unfortunatly, since Microsoft SQL Server 2008, the TRUNCATE_ONLY options has been deprecated. Instead you need to change the database recovery model to “Simple”, and then set it back to “Full”. This has the same effect as a “TRUNCATE_ONLY” transaction log backup. For more details, check the “‘TRUNCATE_ONLY’ is not a recognized BACKUP option in SQL 2008” article).
Once that has been done, you can actually shrink the transaction log file, which will reclaim the free space from within the file itself, and let the filesystem use it again. This will typically shrink it back down to about 1MB in size.
Run the following script to shrink the transaction log file:

DBCC SHRINKFILE(<log_name>)

Note: you can run “SELECT * FROM SysFiles” to determine what the log name should be. This will be in the “name” column. It has typically got a _LOG tag appended to the name.
Or, for a more automated approach, this will automatically get the log file details, and shrink it (Make sure you have truncated the log file first).

DECLARE @LogFile varchar(256)
SELECT @LogFile=name from Sysfiles WHERE UPPER(RIGHT(filename, 4)) = '.LDF'
DBCC SHRINKFILE(@LogFile)

For more information on what the transaction log file is, please read the “What is the SQL transaction log file?” article.

What is the SQL transaction log file?

January 25, 2012 Database, Microsoft SQL

A Microsoft SQL database consists of at least two files, a data file (.mdf) and a transaction log file (.ldf). The data file contains all the data for the database, as you would expect. The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
It is generally good practice to store the data and log files on different physical drives, or different raid arrays, providing both speed advantages, and protection from hard drive crashes. Keep in mind not to use RAID 0. While RAID0 provides some speed advantages, it provides no redundancy in the event of a drive failure.
The transaction log file can be backed up independently of the main database backup if required.
If you want to be able to take full advantage of transaction logging, you will need set the recovery model option for the database to “Full”. You could also have the recovery model set to “Bulk-Logged”, however any bulk operations will not be logged. If the database is set to “Simple”, you can only recovery from a previous full backup.
For production environments, it is recommented to use the “Full” recovery model.

What is the Microsoft SQL Browser Service?

January 23, 2012 Database, Microsoft SQL

The Microsoft SQL Browser Service is a service that runs on a machine that has Microsoft SQL Server installed, and provides information to clients about the SQL server instances installed on the PC.
When you are connecting an application to a SQL server, generally the software has the capabilities to send a broadcast query out onto the network, and detect what SQL servers are available. This is generally presented to the end user with a drop down box listing all the SQL services, and the instance names. This broadcast query doesn’t communicate to the actual SQL Server directly, but it communicates with the SQL Browser service. This means that if the SQL Browser service is stopped, no client software will be able to automatically determine that there is a SQL server running.
With Microsoft SQL Server you can install a number of independent SQL Servers on a machine, and differentiate them with “instance names”. If you only want one SQL server installed, you can either set up an instance name, or use no instance name, which will be known as the “default instance”. With SQL 2008 R2, there is a limit of 50 instances per computer.
Each instance is completely separated, and databases set up on the individual instances. This can allow you to separate payroll database with tighter security, run multiple versions of SQL server, have a production and development SQL server, etc.
Since there can me many SQL instances running on the one computer, there needs to be a way to separate them at a network level. Each instance will run on its own unique TCP/IP port number. This is where the SQL Browser service comes into play. The SQL Browser service is queried before each initial SQL server connection, and can determine what instance name you are trying to connect to. It passes back to the client the port number that the specific SQL instance is listening on. If the SQL Browser service is stopped, you will no longer be able to connect to any SQL server instances (apart from perhaps the default instance if your lucky).
The SQL Browser service listens for incoming SQL queries on UDP port 1434. You must make sure this port, and also the TCP port that all your SQL instances are using, are allowed through any firewalls that might be in place on the machine running the SQL server.
Windows 2008 Server has a tendency to block these ports by default, so it can cause a few issues if your not aware of it.
Even if the SQL Browser service is stopped, it doesn’t prevent users from connecting to it, as long as you know the server name, instance name, and port number.
To connect directly to a SQL server instance running on a specific port, you would use the following format:
SERVERNAME\INSTANCENAME,PORTNUMBER
eg:
SQLSVR3\DEVELOPMENT,15032
This will bypass the SQL Browser service query, and connect directly to the SQL server called “SQLSVR3” with an instance name of “DEVELOPMENT” which is running on TCP port 15032.
For more information, check out the Microsoft article on the subject at http://msdn.microsoft.com/en-us/library/ms181087.aspx
If you need information on setting up SQL to listen on TCP instead of just Named Pipes, please check out my guide on Enabling TCP/IP in Microsoft SQL Server 2005.