by Giselle N
The article I read this week dealt with how to investigate performance issues on the SQL server. The author goes on to say that at least half of the DBAs he meets are not familiar with the ways of the most basic and fundamental methods of inspecting the server performance. The SQL Server keeps two traces running all the time. The first being the default trace that tracks just a few events and the second is that similar to a black box recorder which is used in planes to investigate crashes. This black box does just the same. It records events within the server in order to be used as a reference for when a crash occurs. It is built into the SQL Server but is disabled by default. It acts as a trace, but can store a lot more information than the default trace. The author says that initially the black box only records 5MB from the time the server restarts and when it reaches 5 MB, it wraps and begins to write from the beginning again. However, you can change the settings so it can record 25 to 50 MB of data, in order to give you a better amount of information incase recovery and troubleshooting are needed.
I found this article very helpful. Because we are still learning how to work with the SQL server, chances are that there is going to be more troubleshooting than not. It’s good to know that these traces are always up and running and readily available. The author also posted in this article his blog, http://kevinekline.com/, where he has posted some sample code and queries to show how to increase black box trace file and how to look at the information in the trace.
Kline, Kevin. “Fundamental System Troubleshooting Information.” Database Trends And Applications 25.2 (2011): 32. OmniFile Full Text Mega (H.W. Wilson). Web. 27 Feb. 2012.