Appendix III. Monitoring SQL Server


One of the reasons SQL Server has become so popular is that it's so easy to monitor. From my very first Hitchhiker's Guide, I've been able to use the SQL Profiler to see what hoops the data access interfaces are asking SQL Server to jump through. In the past, Microsoft's data access strategy was built around a number of property/method-driven "black boxes" that drove the backend-specific data drivers or providers. Visual Basic, Visual Basic.NET, C#, and Visual Studio all have T-SQL code generators that were also hidden from the developer. SQL Server Management Studio and its toolsets are no exception. By monitoring the SQL Profiler, you can easily see each and every command given to SQL Server and get a real-time feel for the load it's under and what's generating the traffic. In addition, you can see what these black boxes are generating syntax-wise. This makes it easy to adapt your code to "help" the code generators create more efficient queries.

Of course, the SQL Profiler is not the only way to monitor SQL Server or even ADO.NET itself. The ADO.NET data provider, .NET Framework, and the SQL Server service all expose performance counters that can be monitored either by using the Windows Performance Monitor or by your code. Visual Studio exposes a Performance Monitor control that can make it less difficult (I can't say easy) to monitor these performance counters.

This appendix walks you through the process of setting up a basic SQL Profiler session, as well as shows you a simple application I wrote for one of my conference sessions that monitors the ADO.NET connection pool through use of the ADO.NET .NET data provider performance counters.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net