Tag Archives: Monitoring

SQL Server monitoring and optimization

To optimize the performance of your database, you need to monitor and tune. You determine the performance baseline, how SQL Server is currently performing, through monitoring. Your optimization process includes making changes and then monitoring their effect. This means adjusting two overlapping performance areas, that is, database server performance and query performance.

Click here to read full article on SSWUG.org…

Advertisement

Monitoring Blocked Processes with Event Notifications

SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. In SQL Server, a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn’t release it. Blocking typically occurs more frequently with increasing transaction volumes.

Fortunately, SQL Server provides variety of different tools, which helps database administrators (DBA) and developers to identifying blocked and blocking processes on SQL Server instance that are listed as follow:

Out of all these options Extended Events and Event Notifications are the most efficient and modern way to capture the blocking information.

Event Notifications were introduced in SQL Server 2005 and offer the ability to collect a very specific subset of SQL Trace and DDL Events through a Service Broker service and queue. SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. The key difference between the two features is that Event Notifications allow automated processing of the events asynchronously through service Broker. There is no similar mechanism even in SQL Server 2012 for Extended Events, which is why Event Notifications SQL Trace events still exists for event generation.

As being a fan of Event Notification and service broker, I usually use these two features to proactively monitoring blocked processes information. Check out my article here in which I demonstrated the steps, which you can follow to set-up Event Notification to proactively capture blocked process information.

This article is published on SSWUG.org.

Essential Trace Flags for Recovery & Debugging

The following trace flags are essential for a variety of recovery scenarios. The use of trace flags allow the DBA to gain a finer granularity of control on the DBMS not normally given. Following are the list of important trace flags:

  • T260 – Show version information on extended stored procedures
  • T1200 – Prints lock information (process ID and lock requested)
  • T1204 – Lock types participating in deadlocking
  • T1205 – Detailed information on commands being run at time of deadlock
  • T1206 – Complements 1204.
  • T1704 – Show information about the creation/deletion of temporary tables
  • T3502 – Prints information about start/end of a checkpoint
  • T3607 – Skip auto-recovery for all instance databases
  • T3608 – As above, except master database
  • T3609 – Skip creation of the tempdb database
  • T4022 – Bypass master database stored procedure that run on instance start up
  • T7300 – Get extended error information on running a distributed query
  • T7502 – Disable cursor plan caching for extended stored procedures

Remember that each flag has its own –T<trace#>. Do not try and use spaces or commas for multiple flags and a single –T command.

Always review trace output in the SQL Server error log to ensure start up flags has taken effect.

There are several ways to enable or disable a trace flag in SQL Server. Maybe the easiest way is to do it in the Sql Server Configuration Manager. In the Configuration Manager you then go to the properties of the SQL Server service (default is MSSQLSERVER). In the Advanced tab, select the StartUp parameters and add e.g ;-T4616 at the end and then click on OK button:

Now we have added the trace flag. It will be enabled as soon as we restart the SQL Server service.