Skip to content

August 2015

Statistics IO for Performance Tuning in SSMS

Statistics IO and Statistics Time is another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

Have you ever wanted to know exactly how long it took for a query to run? Have you ever wondered how many I/O reads or writes were caused by your query? With Statistics IO and Statistics Time you can understand both of these.

Statistics IO for Performance Analysis

This post is on using statistics IO to analyze query performance. There is another post on using Statistics Time for performance tuning.

I usually prefer to use Statistics IO over Statistics Time because the Statistics IO option gives better details on what exactly the query is doing.

Let’s take the following query as an example:

Read More »Statistics IO for Performance Tuning in SSMS

Using Statistics Time for Performance Tuning in SSMS

Statistics IO and Statistics Time are another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

Have you ever wanted to know exactly how long it took for a query to run? Have you ever wondered how many I/O reads or writes were caused by your query? With Statistics IO and Statistics TIME you can understand both of these.

Statistics Time for Performance Analysis

This post is on using statistics TIME to analyze query performance. There is another post on using Statistics IO for performance tuning.

Another SQL Server Performance Tuning Tip to help you better understand performance bottlenecks.

This is a simple step to get performance statistics on about any query you are writing in SSMS.

Let’s take the following query as an example:

Read More »Using Statistics Time for Performance Tuning in SSMS

Understanding your Wait Statistics

SQL Server Performance Tuning Tips – Wait Statistics

Wait statistics are commonly overlooked ways to quickly find out what is causing your SQL Server to be slow. One of the reasons is it’s difficult to see how they are trending over time.

>>> It is not difficult with a monitoring tool. <<<

When someone reports a slow database yesterday at 2:00pm, do you know how to determine what’s causing it? You can check the logs, you can look at the history of running jobs, and you might even ask around to see if anyone was doing anything unusual at that time. If you are tracking wait statistics, it is quick and easy to zoom in on a point in time and see exactly what queries were slow and why they were slow.

You might be thinking the following:

  • Did someone run a slow ad-hoc query?
  • Was there something wrong with the network?
  • Did someone run an unscheduled SSIS ETL package?
  • Was an index being rebuilt?
  • Did someone change application code?
  • Was there a hardware failure of some kind?
  • Was DBCC CheckDB running?

What are Wait Statistics

Whenever SQL Server is waiting for something to happen it logs that information as a WAIT.

What type of things get logged:

  • If your SQL Server is waiting on I/O.
  • If it’s waiting on network traffic to another SQL Server.
  • If your database is waiting on a transaction to complete.

Most everything that SQL Server waits on gets logged. However this information doesn’t stay around for long.

Read More »Understanding your Wait Statistics