Category Archives: SQL Server 2008 R2

SQL Profiler

Leave a reply

SQL Server Profiler is a graphical tool that helps in the monitoring of an instance of SQL Server Database Engine or Analysis Services. SQL Profiler is a tool which monitors the events and activity running on a SQL Server instance. The results can be saved to a file or inside a SQL Server table. We can replay this saved trace. Profiler is mostly used in stress testing a server, analyzing performance, debugging TSQ statements, and auditing SQL Server activity.

See below image to see how to open SQL Profiler

See below list for the Key terms associated with profiler.

Event is an action that is generated within an instance of a SQL Server Database Engine. These could be login failure, connection failure or any disconnection. It will include events such as T-SQL statements, remote procedure call batch status, the start or end of a stored procedure, the start or end of statements within a stored procedure and so on.. These are displayed in the trace in a single row intersected by data columns with descriptive details.

Event Class is an event that can be traced and contains all of the data that can be reported by the event. For example SQL: Batch completed for instance is an event class

Event Category defines the methodology used for grouping events within the SQL Server Profiler. For instance lock events will be categorized under Lock event category.

Data Column is an attribute of an event class that is captured in the trace.

Template is the default configuration for a trace. It includes the event classes that are required for monitoring.

Trace captures data based on selected event classes, data columns and filters.

Filter Data can be filtered by specifying criteria of selection during the execution of an event. This feature is used to reduce the size of the Trace.

Event Selection Tab

You can use fn_trace_gettable function to read the trace file.

SQL Server Services

Leave a reply

As per the options you choose during the SQL Server installation, it will install below services on server.

SQL Server Database Services – The service is used for SQL Server relational Database Engine.

SQL Server Agent – is used for scheduling. It executes jobs, monitors SQL Server, send alerts, and enables automation of some of the administrative tasks.

Analysis Services – Provides online analytical processing (OLAP) and data mining functionality for business intelligence applications.

Reporting Services – Manages, executes, creates, schedules, and delivers reports.

Integration Services –is used for SSIS package. It provides management support for Integration Services package storage and execution.

SQL Server Browser – The name resolution service that provides SQL Server connection information for client computers. It is used for named instance only.

Full-text search – Provided full text index and searching facility for BLOB columns.

SQL Server Active Directory Helper – Publishes and manages SQL Server services in Active Directory.

SQL Writer – Allows backup and restore applications to operate in the Volume Shadow Copy Service (VSS) framework.

Copying column headers with grid query results in SQL Server Management Studio

Leave a reply

Many times when we are copying result from the Result Set GRID, column headers are not copied with it. To copy the column header you have to enable it.

Go to Tools menu -> Option -> Query Results -> SQL Server -> Results to Grid -> Checked the “Include column headers when copying or saving the result” check box

Script to get the last update statistics date

Leave a reply

Below script will execute against the sys.Objects,sys.indexes and sys.stats. Script will return last statistics update date and meta data of  statistics. Script will return the result for table and view level statistics.

In this script I have used the function STATS_DATE which will return last updated statistics date.

Syntax
STATS_DATE ( table_id , index_id )

SELECT sysobj.name AS objectname,
sysindex.name AS indexname,
Stats_date(sysindex.[object_id], sysindex.index_id) AS
[Statistics Update Date],
CASE sysstats.auto_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyqueryprocessor,
CASE sysstats.user_created
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedbyuser,
CASE sysstats.no_recompute
WHEN 0 THEN ‘No’
WHEN 1 THEN ‘Yes’
END AS
isstatscreatedwithnorecomputeoption
FROM sys.objects AS sysobj WITH (nolock)
INNER JOIN sys.indexes AS sysindex WITH (nolock)
ON sysobj.[object_id] = sysindex.[object_id]
INNER JOIN sys.stats AS sysstats WITH (nolock)
ON sysindex.[object_id] = sysstats.[object_id]
AND sysindex.index_id = sysstats.stats_id
WHERE sysobj.[type] IN ( ‘U’, ‘V’ )
ORDER BY Stats_date(sysindex.[object_id], sysindex.index_id);