SQL Server Profiler

What is Profiler?

A graphical tool used to capture SQL Server Engine’s events.

Major functions

• Creating Trace
•  Watching Trace
•  Storing trace as Tables or Trace Files
•  Replaying Trace

SQL Profiler Uses

• Monitor the performance of an instance of SQL Server
• Debug Transact-SQL statements and stored procedures
• Identify slow-executing queries
• Test SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected
• Troubleshoot problems in SQL Server by capturing events on a production system and replaying them on a test system
• Audit and review activity that occurred on an instance of SQL Server to review any of the auditing events, including the success and failure of a login attempt and the success and failure of permissions in accessing statements and objects

Profiler Terminology

• Event:

          An action within an instance of SQL Server Database Engine         

          Examples:

1. Running any T-SQL or performing any operation in the SSMS that is related to database

2. Running stored procedures, and creating jobs

• Event Class:

           A type of event that can be traced and a column in a trace describes the event type.

           Examples:

                         1. SP:Starting and RPC:Completed.

• Event Category:

          Group of events are called an event category

           Examples:

                 1. Stored Procedure, Locks and errors and warnings

                          2. Store Procedures -> SP: Starting; SP: Completed; SP: StmtStarting; SP: StmtCompleted

• Data Column:

          Data column contains value of an event class.

          Examples:

                        1. StartTime, EndTime, Duration, SPID, DatabaseID, ObjectID

• Filter:

          Create seductiveness in data that are collected in trace.

          Filtration Reasons:

                            1. Profiler is too much expensive for the production server’s performance

                             2. Profiler capture too much data and making it extremely difficult to analyse later

• Template:

           Default configuration pattern for trace.Can be saved, imported and exported between instances

           Examples:

                         1. Standard template -> Captures all SP and T-SQL batches

DBA Checklist

To stay safe, a DBA of the SQL Server is required to be familiar with all the critical aspects. After knowing these key aspects he can create an environment in which nothing would be lost before the news or generating alerts.

Following is the basic SQL Server’s DBA Checklist and I found very detailed Checklist on Simple-Talk. 

• Connectivity:

1.    Check all SQL Server services are running

2.    Polls all servers ‘select @@servername’ every 10 minutes to check server availability

3.    Make sure each database is available and accessible both by logging in via user applications, as well as running test scripts.

• Backups:

1.    Check databases full backup(Get list of not recently backed up databases in last 7 days)

2.    Log backups

3.    Archiving

4.    Offsite storage

• Events:

1.    Check errors in your SQL Server Error Log and operating system event viewer and for errors or warnings.
2.  All database logs

3.    Application logs

4.    System logs

5.    Agent history

6.    Device logs

7.    NIC logs etc. Investigate any job failures

8.    Keep a log of any configuration changes you make to the servers.

9.    CRUD (Create, Read, Update, Delete) statements about database, table, view, UDF, triggers, stored procedures.

• Processes:

1.    Check that all required processes on the server are running

2.    Count of total SQL Agent Jobs and successfully run are equal.

3.    Make sure that SQL Mail, SQL Agent Mail, Database Mail or xp_SMTP_Sendmail is working correctly

4.    Export and then Delete all details of backup and restore processes created before a specific time

5.    Replication (If any )

• Integrity:

1.    Perform all database and server integrity checks; look for objects that break rules

(DBCC commands dbcc checkdb / dbreindex / checkcatalog / inputbuffer, sp_who, sp_kill)

• Indexes:

1.    Check on indexes to see if they are being used

2.    Need re-creation or modification in indexes are required

3.    If any are missing

• Volumetric:

1.    Check resources on the server such as files sizes (running out of space on any of disks)

2.    Disk space, CPU usage and memory (running low on server memory for SQL Server)

3.    Monitor growth

4.    Enough space available for all types of backup files

• Performance:

1.    Check application performance, long running or "hung" processes, especially those tied to a SQL Agent job

2.    Regular monitor Performance Counters

3.    Regular check on Blocking issues

4.     Count of Total requests  processing ( Total Batch Count)

5.    To avoid the AUTOGROWTH of the data files during the busiest time, manually increase their size.

6.    Performance statistics, using the Permanence tool; research and resolve any issues.

7.    For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate volumes on your server to isolate random writes from sequential ones

8.    “Auto create statistics” and “auto update statistics”  options should be on for all Databases in other case must manually update the statistics.

9.    If application supports snapshot isolation level, this will result in a Temp DB contention so it is recommended to divide the tempdb database into multiple files based on the number of CPU cores and to place it on a separate drive.

• Procedures:

1.    Check all Disaster Recovery Plans

• Security:

1.    Look for security policy violations(security plan according to needs)

2.    SQL Agent Job modifications, any schedule modification can have negative impact. Use a report that shows which jobs were modified and when, unfortunately it does not reveal "who",

3.    Count to Total connections to SQL Server instance

Configuration Manager

·         Configuration manager is GUI to manage/configure all

§  Sql Server Services,

§  Server Network Protocols,

§  Client Network Configurations

§  And also to change the Connectivity options.

·         Using SQL Server Configuration Manager you can set Service's (such as SQL Server Integration Services, SQL Server Full Text Search Service, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Database Engine Services, SQL Server Agent Service, SQL Server Browser Services etc)

                              §  State à   Start OR Stop

§  Start Mode à   Manual OR Automatic

·         Using SQL Server Configuration Manger, allows  you to Create or Remove Server Alias.

·         Using SQL Server Configuration Manger you can also force Protocol Encryption and Protocol’s

§  State à Enabled OR Disabled

(Shared Memory, Named Pipes, TCP\IP and VIA)

·         Using SQL Server Configuration Manager Tool you can change SQL Server Service Account or change the Password for any of the SQL Server Services.

·         Using SQL Server Configuration Manager, changes in Account settings also configure associated additional changes such as Windows Registory settings and account can read the SQL Server settings immediately once changed where as other Tools (SMO and WMI) can not change associated settings which may effect the proper start of service.

·         Using SQL Server Configuration Manager Tool  changes come into effect immediately by avoiding downtime

  

Reference Links

SQL Server Configuration Manger: http://technet.microsoft.com/en-us/library/ms174212.aspx

Protocol Encryption: http://technet.microsoft.com/en-us/library/ms191192.aspx

                               http://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

Account Permissions: http://msdn.microsoft.com/en-us/library/ms143504.aspx