8.3 Advanced MS SQL Concepts

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:

This is useful for ad hoc views of current activity.

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.,


  version_store_reserved_page_count,


  version_store_reserved_page_count*8/1024.


FROM sys.dm_db_file_space_usage;

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

USE TempDB


GO

DBCC CHECKDB WITH ESTIMATEONLY



GO

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:

USE TempDB


GO

EXEC sp_helpfile

GO

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

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

GO

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

GO

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