Tag Archives: performance

Find Object Execution

I found a useful script to find executions of stored procedure. This will give you a good idea of what is utilization. Run it multiple times to compare how often a procedure of group of procedures executes within certain time periods.

Stored Procedure Executions

Stored Procedure Executions

For example, you have a under performing billing process. Your system fires multiple procedures multiple times. You could use something like this to get an idea of execution counts, worker time, average elapsed time and max logical reads. It will point you in the right direction procedure that can be optimized. Tweak this to fit your needs.
Read more »

Disk Space – Finding I/O Bottlenecks(SQLIO)

As a production DBA , I get to start diving into the internals of the SQL Server environment. Over the last year, we had an opportunity to ‘let loose’ on a new clustered environment with a dedicated SAN.  Zoom… Zoom…Zoom! It was like performing sea trials or a shakedown tour in a new ship. Much of the testing centered around the execution of existing SQL code and processes. We were able to set-up a series of tests using the  Microsoft ‘s  SQLIO Disk Subsystem Benchmark Tool to saturate the disk subsystems.

 The tool does not actually test your SQL Server configuration , but provides the means to script out and execute a controlled load against the disk subsystem. Scripts can be executed to create a variety of read and write operations. Parameters allow the load to use a variety of file sizes, number of threads, latency, outstanding requests in random or sequential reads/writes for specific durations.   I am not going to get into the specifics of the scripts we used. The links below provide a much better examples than I could tap out in this post.

Read more »