Browse DevX
Sign up for e-mail newsletters from DevX


Diagnose Server Performance Problems with SQL Server's SQL Profiler : Page 4

SQL Profiler is a great SQL Server tool that can help you reach your database optimization goals. This 10-Minute Solution presents a few preparatory steps to start using Profiler for monitoring performance problems. It discusses some techniques, tools, and commands that reveal information about server behavior.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

As a last consideration, there are some automation alternatives for using Profiler. If you're a dyed-in-the-wool Books Online aficionado, you know you have a bunch of stored procedures you can invoke to run Profiler (sp_trace_setfilter to get them set up and sp_trace_setstatus to stop them). But if you are after the creature comforts, you can schedule your trace selections as jobs by having Profiler create the scripts for them. This is infinitely more convenient, not to mention less frustrating, because the system stored procedures that control traces are very particular in the way they accept parameters. Then again, if you weren't very particular, you wouldn't be use the stored procedures in the first place!

Using the scripting facility to create scheduled jobs is an enormously helpful feature. Once you know what you want, you can have the jobs engine start and stop jobs for you. The only change you have to make to the script file is to change the name of the target directory where the output will be saved. Note that you can pepper your trace with the panoply of Show Plan variants, so that you're sure to get the information you need to determine how things are working (or not working).

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects. You can reach Drew with your comments and observations at drewg1010101@aol.com.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date