8.3 Advanced MS SQL Concepts

This section provides information on advanced concepts that are important when using MS SQL as your ZENworks database, including:

8.3.1 Useful MS SQL Tools

The following are some of the key tools built into MS SQL to help you troubleshoot, monitor, and diagnose the database:

Monitoring the Error Logs

The Windows application event log provides an overall picture of events occurring on the Windows Server and Windows operating systems as a whole, as well as events in SQL Server, SQL Server Agent, and full-text search. It contains information about events in SQL Server that is not available elsewhere. You can use the information in the error log to troubleshoot SQL Server-related problems.

DBCC (Transact-SQL)

DBCC (Database Console Command) statements enable you to check performance statistics and the logical and physical consistency of a database.

System-Stored Procedures (Transact-SQL)

The following SQL Server system-stored procedures provide a powerful alternative for many monitoring tasks:

sp_who (Transact-SQL) - Reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.

sp_lock (Transact-SQL) - Reports snapshot information about locks, including the object ID, index ID, type of lock, and type of resource to which the lock applies.

sp_spaceused (Transact-SQL) - Displays an estimate of the current amount of disk space used by a table (or a whole database).

sp_monitor (Transact-SQL) - Displays statistics, including CPU usage, I/O usage, and the amount of idle time since sp_monitor was last executed.

Trace Flags

Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues (for example, deadlock chains).

sp_trace_setfilter (Transact-SQL)

SQL Server Profiler tracks engine process events, such as the start of a batch or a transaction, enabling you to monitor server and database activity (for example, deadlocks, fatal errors, or login activity). You can capture SQL Server Profiler data to a SQL Server table or a file for later analysis, and you can also replay the events captured on SQL Server, step by step, to see exactly what happened.

Monitoring Resource Usage (System Monitor)

System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use, enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds for specific counters to generate alerts that notify operators.

System Monitor works on Microsoft Windows Server and Windows operating systems. It can monitor (remotely or locally) an instance of SQL Server on Windows NT 4.0 or later.

The key difference between SQL Server Profiler and System Monitor is that SQL Server Profiler monitors Database Engine events, whereas System Monitor monitors the resource usage associated with server processes.

Tuning the Physical Database Design

Database Engine Tuning Advisor analyzes the performance effects of Transact-SQL statements executed against those databases that you want to tune. Database Engine Tuning Advisor provides recommendations to add, remove, or modify indexes, indexed views, and partitioning.

Activity Monitor (SQL Server Management Studio)

The Activity Monitor in SQL Server Management Studio graphically displays information about:

  • Processes running on an instance of SQL Server

  • Blocked processes

  • Locks

  • User activity

This is useful for ad hoc views of current activity.

8.3.2 High CPU Utilization

A large number of open transactions or repeated SQL calls of the same query can cause high CPU utilization.

The following SQL statement can be used to find out the queries which are causing the high CPU utilization. This query will give all the information about the servers, physical_io, CPU, wait events and status.


Or, you can right-click the SQL Server instance and click on the Activity Monitor.

For more information, see http://technet.microsoft.com/en-us/library/hh212951.aspx.

You can use the KILL command to clean the unwanted, long-running session:

KILL <<spid>>

8.3.3 TempDB Impact on Performance

TempDB system database is used to hold temporary user objects, internal objects, and row versions. When the TempDB is heavily used, the SQL Server might experience contention during page allocation. This might cause queries and requests that involve TempDB to be unresponsive sporadically. Hence, the size and physical placement of TempDB can affect the performance.

To reduce the contention, adjust the data file in TempDB using the following formula:

If (logical processors <= 8) the TempDB data files should be number of logical processors. Otherwise TempDB data files should be 8 (increment it by 4 if contention continues).


  • Set the recovery model of TempDB to SIMPLE. This model automatically reclaims log space to keep space requirements small.

  • Set auto grow to ON for TempDB

  • Each data file must be the same size; this allows for optimal proportional-fill performance.

  • Put the TempDB database on a fast I/O subsystem and preferably on disks that differ from those that are used by user databases. Pre-allocate space for all TempDB files.

  • Set the file growth increment to a reasonable size to avoid the TempDB files from growing too small when compared to what is being written into them by offsetting performance.

TempDB Size Estimation

Use the following query to show the current TempDB size:

SELECT (unallocated_extent_page_count +version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,

  unallocated_extent_page_count       *8/1024.,



FROM sys.dm_db_file_space_usage;

To check how much space it requires without actually executing the command:





It returns the result set for the estimated TempDB space needed for CHECKALLOC(KB) and estimated TempDB space needed for CHECKTABLES(KB).

For more information, see http://technet.microsoft.com/en-us/library/ms345368(v=sql.105).aspx.

Resizing or Moving TempDB

Run the following code to get the file names of TempDB:



EXEC sp_helpfile


Run the following code to move the mdf and ldf files:

ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf')


ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'e:datatemplog.ldf')


For more information, see http://technet.microsoft.com/en-us/library/ms345408.aspx.

8.3.4 Custom MSSQL Defragmentation Script

This SQL Script can be used to defragment indexes. This script contains multiple options that can be configured:

  • Defrag completely (Y/N) – The default value is N. If it is Y, defragment all the indexes, else it will defragment the remaining indexes that were left in the last execution.

  • Number of Hours to Execute – Default is 4.

  • Change the database recovery mode to SIMPLE/FULL (Y/N). Default to N. If it is N, it means that the script will not change the recovery mode. This can be used when the Primary Servers are running. If it is Y, the script assumes that the Primary Servers are stopped and recovery mode will be set to SIMPLE. After the execution is complete, it will convert to FULL.

  • Index File Group Name – The default value is PRIMARY. If the customer is using a different file group for indexes, this can be modified.

8.3.5 Custom MSSQL Trace Blocked Session Script

This SQL script can be used to trace the blocked sessions.

Multiple options can be configured:

  • @TraceLoc is the name of your trace file name and location. For example, @TraceLoc = N'C:\Trace1'

  • @TimeToRun is the duration of the trace, in minutes. For example, @TimeToRun = 15

  • @maxfilesize is maximum trace file size in MB. For example, @maxfilesize = 5,0000