913
Chapter 37: Performance Monitor and PAL
37
BEGIN
SET @Counter = @Counter + 1
EXEC sp_user_counter1 @Counter
WAITFOR Delay '00:00:02'
ENDBest Practice
Use System Monitor to get an overall picture of the health of the server and to get an idea of the types
of issues that might be occurring within SQL Server. Then, armed with this information, move to SQL
Server Profi ler or Extended Events to target the specifi c problem.The confi guration of System Monitor, including every counter, can be saved to a confi gura-
tion fi le using File ➪ Save As, and later restored using File ➪ Open. Using this technique,
you can export a System Monitor confi guration to other servers.There is one catch: The counter must be monitoring the local server to move from server to
server. However, if the counters monitor a remote server, the confi guration monitors that
remote server regardless of where the System Monitor confi guration fi le is opened. Because
DBAs are seldom physically at the SQL Server being monitored, this is a problem. (If this
bothers you, e-mail the author, Aaron Nelson, at [email protected]; one of these
days I’m going to write a custom system monitor, probably using PowerShell, to fi x this and
other problems.)Data Collector Sets
In Windows 2008, Performance introduces Data Collector Sets, which encompasses the func-
tionality formerly known as counter logs. Data Collector Sets use the same server counters
as System Monitor, but in addition to graphically displaying the data in real time, the Data
Collector Sets also enable you to write the counter data to a log fi le. This means the data
can be analyzed after the fact or even replayed within Performance Monitor.Data Collector Sets are accessed via Performance Monitor and include User Defi ned, System,
Event Trace Sessions, and Startup Event Trace Sessions subfolders. To see and confi gure
where the log fi les will be stored, right-click a Data Collector Set, and choose Properties.
Navigate to the Directory tab, and examine the Root Directory, Subdirectory, and
Subdirectory name format areas. An example of the fi le format and location is shown in the
Example directory area.If you are ready to create a new Data Collector Set, follow these steps:- Right-click the User Defi ned subfolder, and mouse-over New.
- From the context menu, select Data Collector Set.
c37.indd 913c37.indd 913 7/31/2012 10:03:53 AM7/31/2012 10:03:53 AM
http://www.it-ebooks.info