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?


Wednesday, October 06, 2004

Is XML the Answer?

What an interesting idea, something I've been waiting to hear about for a long time. Xml has some definite uses, but it's not the solution for everything.

I have had some thoughts about using SQL Server to generate xml-formatted data, and then use IE's data island to display recordsets that are just too huge to manage in cfquery.

But I have subconsciously realized, that would not be a real solution, that if you have a page bringing too much fresh data, that you need alternatives.

This is all apart of my philosophy of using what works for me. Or in shorter form, if it works, use it.

But this also goes towards the idea that every programming paradigm, is the solution for all problems.

I like the fact that he's willing to dump a programming paradigm, if it doesn't get the job done...

I am not saying to drop oo, different methodologies, only to be replaced by another trend/fad of what to do...

Stick with what you know, but don't be afraid to realize that no solution solves every problems....

Common Sense, isn't always that common...

Have a good day everyone...

Tuesday, October 05, 2004

Balanced SQL

As I been going thru pages upon pages of cf code with sql statements, I realize how important it is to balance the needs of the end-user against scalability.

As much as we create these incredible applications, sometimes we go over the edge in demanding too much from sql, code to deliver what should not be delivered.

Have you had that experience, of reading someone else's code, and thinking, what the heck is this supposed to do?

Or why did they do it that way?

Or the, I want tons of fresh data, with no issues for performance?

There's a give and take, of what we can deliver...

I mean if it's an application, with only 1 end-user, that's quite a different scalability challenge, then discover tons of users using this archaic application.

It's both fascinating and frustating, how much we have to learn, to be really good coders.

We think that being a good coder, is knowing the syntax, using the latest methodology, reading the latest books.

They are but a tip of the iceberg....

So my message of today, is really plan for how many users will use your application, and plan for the far future.

It's a simple phrase.

Think Longterm.