Featured Article(s)
Freespace – A DBA’s Favorite View
One of a DBA’s main worries is the amount of freespace in a tablespace. This article will present a view that I use on a daily basis to monitor tablespace freespace. This view is one of my most frequently used tools and has help keep me out of trouble.
Tuning, Troubleshooting – Now.
When you’re chasing a performance issue with your queries, you can quickly find that it’s challenging to find out exactly what’s happening on your system. Profiler, audits, debugging, adding in debugging-type output… it’s painful. It doesn’t need to be though. SQL Sentry’s Performance Advisor’s Quick Trace will jump in, tell you what’s running at a given instant in time. You’ll immediately know what to check on your system, and you can see not only the potentially troublesome query, but also other things that are running at the same time that it may be impacting. Amazing tool – you need to check it out.
Query Performance Feedback and Comments
James wrote in with an example of some of the subtle aspects of query optimization that you can be facing – I wanted to pass along his note here for your thoughts.
"The worst thing T-SQL isn’t a join — it is the subquery (or sometimes called a correlated subquery). The reason it is so terrible is that there is nothing that the database can do to optimize the statement. With a subquery, every comparison value in every row has to be interrogated.
Here is an example. Let’s say that you want to know the top ten customer transactions in your transaction database. You might code the SQL as follows:
SELECT TransID
,Amt
,CustID
,CustNM
FROM TransDB.dbo.TransTBL a
where 10 > (select count(*)
from TransDB.dbo.TransTBL b
where a.Amt < b.Amt
and b.Amt is not null) order by Amt desc
I know you can use the TOP parameter or design this database differently to avoid this type of thing. But for the sake of understanding a subquery, this is what you have sometimes. This SQL is making two passes on the same table to run a comparison in order to get the top ten values. If this table is large, even having an index-only look-up will run slow.
I would certainly ask tough questions about the necessity of this SQL. If it is necessary, then Service Level Agreements might have to be waived for this one."
What other things have you learned to avoid, or specifically do, to make your systems hum? Send me a note here.
…another question – do you set up service level agreements with your internal users? I’d seen this as a trend for a while, but then it seems to have become less common. Do you do this? Personally, I think it’s a great idea.
Featured White Paper(s)
Managed .NET Connectivity
Database connections are the lifeblood of enterprise applications, administrating the secure and steady flow of information b… (read more)