Michael J. Swart

May 28, 2008

An uncommon query performance metric.

Filed under: Technical Articles — Michael J. Swart @ 5:34 am

I often get asked to look at queries and tell if they perform well (or well enough). That’s a tough question and – as always – the answer is “it depends”.

Well the common metrics that can be read from any sql trace are cpu, reads, writes and duration.
Duration being a sort of bottom-line sort of number.

I’ll introduce now the metric I like to use. It’s something I gleaned from presenters at Dev Connections.

So I often like to look at the IO, the reads and writes. In the system I work on, IO is often the bottleneck. 10,000 reads would normally be poor unless you’re returning 1,000,000 rows. 200 reads would normally be acceptable unless the query is used often and typically returns one row.

So the metric I have started looking at is this:

reads / row

For a query just take reads and divide by the rowcount. In Profiler, rowcount is a trace column that isn’t included with the standard trace.

I’m still trying to get a sense of what an acceptable range means for this metric, but it’s been useful so far. Terrible queries look terrible 1000+ reads/row. And good queries look good 0.001 reads/row.

We’ll see how useful it gets.

May 14, 2008

What's taking you so long?

Filed under: Technical Articles — Michael J. Swart @ 6:09 am

Sometimes I wonder what SQL Server is doing. For example maybe I want to fail-over SQL Server to another node and I want to ask SQL Server the question “Are you in the middle of something?” If the answer is “yes” than it can mean a long rollback. I haven’t found one system view that can answer the question, but there is a combination of three of them that I think covers everything:

— longest waiting queries

select top 10 * from sys.dm_os_waiting_tasks

where session_id > 50

order by wait_duration_ms desc

— longest transaction (possibly not waiting but keeping resources)

select top 10 *

from sys.dm_tran_active_transactions at

left join sys.dm_tran_session_transactions s_t

on s_t.transaction_id = at.transaction_id

where transaction_type in (1,4)

order by transaction_begin_time asc

— longest running queries (possibly not waiting but working hard)

select top 10 st.text, er.*

from sys.dm_exec_requests er

cross apply sys.dm_exec_sql_text(er.sql_handle) st

order by er.start_time asc

May 13, 2008

How useful is connect.microsoft.com?

Filed under: Miscelleaneous SQL — Michael J. Swart @ 6:06 am

So I’ve been using Microsoft connect for SQL Suggestions and the response so far for my three issues is:

  • Call MS support for that problem
  • Defer your request.
  • No comment

I wanted to see what other peoples’ experiences were and so I took a very quick survey for bugs and suggestions submitted on some random days older than a week (May 1 and May 2) to be exact. Here are the results of my survey

  • no comment yet (6 issues)
  • active dialogue waiting on MS (4 issues)
  • active dialogue waiting on submitter (4 issues)
  • already fixed in existing release (2 issues)
  • by design or won’t fix (8 issues)
  • to be considered for a later release(4 issues)
  • fixed for next release (4 issues)

So how does MS come out at the end of this? Quite well I think, they’re getting a lot of valuable feedback. How do customers come out at the end of this? So-so. I think MS could do better. The first two categories of issues describe issues that seem orphaned. That’s 10 issues out of 32. If MS had a better track record than that, connect.microsoft.com would be seen as a real place to be heard. That would encourage even more feedback.

Another thing that I hope MS is doing is to place a larger weight on feedback that would help improve scalability/performance. This is because if there is feedback that could help MS become a better Enterprise-level platform, then this feedback would be fairly sparse compared to the other feedback requests that deal with UI improvements say. My guess is that if the SQL Server team spends time on enterprise-level improvements, the ROI would be at least as great as UI improvements.

Powered by WordPress