Thursday, October 07, 2004

Managing Sql Server Database

Have been learning from Ibrahim Hafidh of Imagetrend, all about profiling, writing better sql. The goal being to both better monitor sql server, and to help identify areas of improvements.

I have been testing out this sweet trial version of Embarcadero Performance Analyst, which for $5000+ is a very sweet, web-based sql performance analyzer.

However, since I wanted to come up with a more practical aka cheaper solution.

Ibrahim and my fellow workers, were learning all about working with SQL Profiler, especially in watching the duration profile, in actually being able to see sql statements that were being run thru sql server. Which is a very powerful tool.

It makes me feel stupid sometimes, seeing all these built-in tools, and not realizing how easy it is to use.

Well starting with this morning, I am sql profiling, reviewing queries to be worked on, if only i could figure out how to identify what coldfusion page, that query came from. That would really help.

Also I have been playing with Information Schema today, to help identify what tables do/don't have primary key's and of those, which are clustered indexes.

Face it, if you have a lot of tables to review, it helps to know and prioritize which ones need the most work.

I modified the above script, actually combining it with another one. Perhaps this already existed, but it's still a nice tool, at least for my own sake.

select i.table_name, cu.column_name, tc.constraint_name, case
objectproperty(object_id(i.table_name), 'tablehasclustindex') when 0 then
'no' when 1 then 'yes' end as [has clustered index]
from information_schema.table_constraints tc
inner join information_schema.constraint_column_usage as cu on
tc.constraint_name = cu.constraint_name
full outer join information_schema.tables i on tc.table_name =
where objectproperty(object_id(i.table_name), 'isusertable') = 1
order by i.table_name asc, [has clustered index]

Run this in SQL Query Analyzer, and see how surprised you are/aren't on how well you created/modeled/optimized your tables.

I have been working with SQL Server 2000 for about 2 years, and the more I learn, the more I realize I have yet to learn.

Also I would like to strongly recommend Ken England's book on Microsoft SQL Server 2000 Performance, Optimization and Tuning Handbook. He's the really first guy, to present high level information, from a first person perspective, that is easy to read and understand. He's a great teacher...

For most coders, you could care less what the issue is with the table design/structure. What matters for you, is delivering the right data, for the best performance.

Here's where Ken England's book comes in handy. He explains, that when you look at Explanation Plans, what makes the explanation plan pick the correct indexes, for joins, scans, depends on the selectivity of the index.

Part of this, as far as I can figure requires that:

1. You have a primary key that is clustered
2. Any fields that you use to do joins, where's need to be indexed.
3. It helps to define relationships between tables, that you join against frequentally.

Here is his formula for determining the selectivity of an index, to be used for the Query Optimizer in your sql statements.
selectivity = (the nmber of rows returned / the count of rows in the table) * 100

if 5,000 of the rows in our 100,000-row table were returned, the selectivity of our query would be:

selectivity = (5000 / 100000) * 100 = 5%

if 90,000 of the rows in our 100,000-row table were returned, the selectivity of our query would be:

selectivity = (90000 / 100000) * 100 = 90%

the more selective a query the fewer rows returened and the more likely that an index will be chosen by the query optimizer.


So basically our goal as coders, is to help make it easy for sql query optimizer to deliver our data with the best performance.

I myself, still have to play around with this more to get my head, which is going to take time...

What kind of things have you learned to optimize your queries performance?


1 comment: