Chapter 11. Useful scripts

 

This chapter covers

  • Viewing everyone’s last-run SQL query
  • A generic performance test harness
  • Estimating the finishing time of jobs
  • Finding where your query really spends its time
  • A simple lightweight tracing utility

Gallimaufry refers to “odds and ends,” and that nicely sums up the content of this chapter. It contains a hodgepodge of scripts that can reveal some interesting and useful information. The scripts given in this chapter wouldn’t sit comfortably within the other chapters of the book.

The content of this chapter is wide ranging, covering such diverse areas as finding which SQL queries everyone last ran, getting Windows system information within SQL Server, determining where your queries really spend their time (as opposed to the cached plan estimate), and a lightweight SQL tracing utility. We’ll begin by looking at how you can view everyone’s last-run query.

11.1. Viewing everyone’s last-run SQL query

Viewing details of everyone’s last-run SQL queries can be useful in understanding how your users use the database. It can also be useful in checking users when they say, “the only SQL I ran was….” Often there’s a difference between what users say they’ve done and what they’ve really done. The script provided here should help clear up this discrepancy.

11.1.1. Find the last-run queries

11.2. A generic performance test harness

11.3. Determining the impact of a system upgrade

11.4. Estimating the finishing time of system jobs

11.5. Get system information from within SQL Server

11.6. Viewing enabled Enterprise features (2008 only)

11.7. Who’s doing what and when?

11.8. Finding where your query really spends its time

11.9. Memory usage per database

11.10. Memory usage by table or index

11.11. Finding I/O waits

11.12. A simple lightweight trace utility

11.13. Some best practices

11.14. Where to start with performance problems

11.15. Summary