Saturday, January 7, 2012

SQL Power Architect

This is part of continuing set of reviews / opinion pieces about the state of data modeling tools that began over here.


Background

I must say that SQL Power Architect came out of no where for me. I was googling around when I stumbled upon it and it sounded too good to be true. A multi-platform, open source database modeling tool with an Enterprise version available (for pay naturally) if you need advanced features. Watching some of the videos it sounds like this was a tool developed in house that evolved into a product for the company. I couldn't find much more history than that, if anyone has some let me know and I'll add it in here.


Stability
I was back to using my small (< 50 table) model and had no stability issues. Rock Solid.


Price
SQL Power Architect: ~$00.00
SQL Power Architect Enterprise: $495.00


Features
Common Checklist

  • Logic Modeling: Yes 
  • Physical Modeling: No 
  • Reverse Engineering: Yes 
  • Forward Engineering: Yes
  • Sub Models: No 
This is an interesting contrast to MySQL Workbench. SQL Power Architect insists that you do all of your work in the Logical Model (called the PlayPen Database) and generate SQL to apply to your Physical Model. Of course this gets awkward if you need to update your Logical Model from the actual database.


Once again Auto-Arrange is a very poor choice. So many of the relationships overlap that it is difficult to understand the model. 


There is no documentation for the Community edition available. To understand how this application works your best bet is to visit the YouTube SQL Power Channel and play around for a while. In all fairness the Enterprise Edition does have documentation available.


The GUI, well it is in Java, so it does not feel native. The scrolling is wrong, either it scrolls too fast or begins scrolling before you would expect it to. In most apps this is isn't a problem but as you're trying to work on your diagram this can get really awkward.


Conclusion
This is easily the best open source tool I've seen for data modeling. It is easy to setup, works with JDBC connections and allows you to compare and forward engineer your changes.


The downside is that all DB schema changes are expected to originate from it, or be added to the Logical Model manually. And the Logical Model does not support sub-models. And the GUI makes working with the models challenging.


These issues can be worked around by making a lot of small projects as sub-models. This raises it's own challenges with overlapping entities and synchronizing projects. 


All in all if you are working on an open source project with limited funds this may be your best approach but watch out, you will spend more effort managing this model than you expect.

Saturday, December 31, 2011

The State of Data Modeling Tools (MySQL Workbench)

Background
I have only begun using MySQL and trying out the WorkBench tools from Oracle was the next logical step. This product was originally DBDesigner 4 from fabForce.net before it's developer was hired by MySQL AB to create a new set of MySQL GUI tools. This application has grown from a physical database designer for MySQL to include additional query and managment features. It is much closer to SQL Server Management Studio than it is to ERwin.


Stability
I was using a relatively small (< 50 table) model to work with and the system would crash after about on hour of use. The crashing was more frequent on the Mac side of things than it was when running on the PC but neither were very stable.


Price
MySQL Workbench: ~$00.00

Features
Common Checklist


  • Logic Modeling: No
  • Physical Modeling: Yes
  • Reverse Engineering: Yes
  • Forward Engineering: Yes *
    • * Note: Does NOT compare to existing schema. It will make fresh. new objects!
  • Sub Models: No
The auto-arrange does a terrible job distributing the tables, many were overlapping. The relationship lines are not adjustable, they go where the app assigns them to be.


The GUI makes me think of Eclipse, which is clearly not native on any platform and it generally works as expected.  The notation can be changed to fit your conventions and the automatic screen refreshing worked flawlessly (not something to take for granted after using ERwin).


Conclusion
This is a very good tool for visually showing developers how a few objects are related to each other and firmly belongs in the MySQL suite of tools.


Unfortunately, this application is just a bit too primitive to use for anything other than the most basic data modeling. The lack of a Logical Model is less than ideal, but not a deal breaker. I did find the crashing, lack of sub-models, minimal control of the relationship lines and the poor forward engineering very frustrating. I recommend using a piece of graph paper instead.

Friday, December 30, 2011

The State of Data Modeling Tools

Lately have had a chance to do some data modeling for a MySQL project and I thought I'd do a series of posts with a brief review the tools for making ERDs.

Data Modeling Tools
 Evaluation Criteria
  • Stability
  • Price
  • Features
ERwin Review
I have been using ERwin for a few years now so I am pretty familiar with this tool. Like it or not it has been around for ages (Logic Works, who originally made ERwin started in 1988) and is the benchmark all other system have to be measured against.

Stability
The most recent version has not been entirely stable, I cannot make it crash on demand but it will occasionally lock up.

Price
ERwin Data Modeler Std Edt + 1 yr Maint: ~$3,900

Features
Common Checklist
  • Logic Modeling: Yes
  • Physical Modeling: Yes
  • Reverse Engineering: Yes
  • Forward Engineering: Yes
  • Sub Models: Yes
Now having used it for a while I know a number of ERwin's quirks but stepping back and looking at it with a fresh eye, it is dated. The GUI looks like it is still built on an ancient toolkit which is likely why the work area screens require manual refreshes to clean up visual artifacts.

Conclusion
ERwin has the core features I'd like, but it's not entirely stable, the GUI is quirky and the product is expensive. For the price I would expect CA to be rolling updates more frequently to address these issues but ERwin has had minimal updates these last few years.

Sunday, January 30, 2011

NoSQL Summary

The best short description of when not to use NoSQL that I've ever seen is from a reddit comment by bucknuggets


Assumptions:

  • a key-value pair data store isn't optimal storage in the perfect world since data quality is very difficult to manage, reporting is a nightmare, and since most data doesn't map elegantly to key-values app complexity grows.
  • but it may be a very effective storage solution at certain scalability thresholds

Then for sites below those thresholds it is premature optimization - that adds unnecessary risk to the project.

Friday, January 1, 2010

The Fourth Paradigm brought to life?

I was reading "The Fourth Paradigm" and thinking about the whole data-brick concept that is needed to handle the information deluge. To their credit it is not a solution focused book, they mention Hadoop, MapReduce and Beowulf Clusters all over and hardly mention SQL Server at all. This is impressive because the man who inspired this book (Jim Gray) is the same database luminary that dragged SQL Server back from the dead with SQL Server 7.0. Now I'm only considering this in the context of business systems because that is where I have the most experience.

In the book, there are three main concerns.

  1. Capturing data
  2. Curating the data
  3. Analyzing the data

Capture
Capturing is not a problem, currently we capture a rather absurd amount of data but we don't know what to do with it. Between the Beowulf Clusters and MS Windows HPC (High Performance Clustering) Editions be know how to collect tons of information.

Curate

I think that the curating of the data is intended to be done by Master Data Services. This provides a way to setup data approval processes and de-duplication based on customized business rules. It's whole purpose in life taking care of information. Now, opening that data up so it is valuable to the world instead of a single company is a separate problem.

Data Analysis


Initially I was thinking that the data-brick concept for analyzing and retrieving the data was being brought to life as part of SQL Azure. Probably the best part of SQL Azure is that it could meet the need of centralized storage that can be rapidly accessed from anywhere in the world. Unfortunately SQL Azure has a lot of drawbacks.

  • Extremely limited DB size (10GB?)
  • Limited ability to do query tuning (Can't even see the query plan?)
  • No controlled backup strategy (the "I need data from a week ago" sceanario)
The limited database size is a real killer, this ain't it.

So what is the next option? I think Microsoft is addressing it through SQL 2008 R2 Parallel Data Warehouse Edition. Yup it's a mouth-full alright, but it is positioned in a very interesting spot. It's intended to be paired with Windows HPC (High Performance Cluster) Edition. It's based off the parallel data warehouse software from DATAllegro. It distributed the query across a bunch of nodes, collects the results and presents it. It sounds awesome, right? The price certainly is, (57k / Processor!).

I really like that MS has a coherent long-term strategy for all this stuff that I hadn't seen so clearly until reviewing Jim Gray's work.

Tuesday, November 10, 2009

PASS Summit 2009 Pre-Con

This was a very good day. Kimberly has an insane amount of energy and enthusiasm for indexes and database internals. Paul didn't say very much, but was responding to questions and his knowledge is astounding. This made me happy to be a DBA.

Indexing for Performance

So we started off with a lot of quality time discussing Heaps vs. Clustered tables. There was a clear bias for Clustered tables, but that's ok. One of the reasons I went to PASS was for Opinions not just raw facts.

Heaps
Heaps are nice because there are no page splits.
Heaps are not nice because they use a forwarding pointer when the row size changes.
This means that the RID may go to a leaf or require an extra jump to reach the leaf.

Why would they use this goofy forwaring pointer? Well the RID is not an incrementing value, but is tied to some internal structures. The indexes all point at the RID. If you update the RID to indicate the new page the data can be found on then any UPDATE statements have to wait for the index(es) to be updated as well. By using the pointer, the indexes can stay pointed at the RID. This was a case where the "The Answer is another layer of abstraction!". Unfortunately as you get more forwarding pointers it requires more reads to retrieve your data. Over time you will pay a price for this.

To check your forwarding pointer count look at dm_db_index_physical_stats


Clustered Indexes
Updates are faster but may cause page splits. This may not be an issue because the page split happens once and thenthere is room for a lot of additional data on the page.

The Page Splits can lead to fragmentation. The best way to reduce the page splits is to adjust your fillfactor down (~70%). Sure, your table will get a bit bigger at first but it is space you will likely need going forward regardless.

Ola Hallengren has some excellent maintenance scripts. Investigate this.

It's important for your clustered index to be minimal because the columns get added to each NonClustered index.

If your clustered index is not unique a "uniquifier" (I'm not kidding) is added to each set of values that exist more than once. This value is a 4 byte int. If you exclude 2.1 billion dupes things may break (but you likely have other problems by then).

Splits do not impact your NonClustered indexes and do not create separate entries in the transaction log because they are done as part of the UPDATE / INSERT transaction which created them.

Index lookups always reach down to the leaf level, this is required for consistency.

Trees!
SQL Server uses B+Trees for it's indexing method.

COUNT(*) works be doing a leaf-level scan of the smallest (minimal width) non-clustered index.

Trees live in the SQL Server cache and tend to be a large part of the logical reads.

Optimizer
The Optimizer's goal is not to find the best query plan, but to find an acceptable query plan swiftly.

The Optimizer expects the tree to be in the Cache and leafs to be on disk.

When does the optimizer decide to use a NonClustered Seek with bookmark lookup vs a Clustered index Scan?
It turns out this is tied to the page width.
When the # of rows returned <=  1/3 to 1/4 of the # of ALL pages (A table Scan) then the NonClustered index is used.
Otherwise the number of lookups of are cost prohibitive and it would be faster to use the Clustered index and filter.

Statistics
When performance drops off suddenly it is nearly always Statistic related.
When performance drops slowly over time, it is nearly always Fragmentation related.

Stats used to auto-update when 20% of the rows on the table had been touched. (SQL 2000). Can be found in sysindexes.rowmodctr.
Now Stats will change on a column in a table only when 20% of that COLUMN changes. This is irritating.

DBCC SHOW_STATISTICS has some good stuff, including:
*Last updated
*SampleSetSize
*Histogram
sp_autostats are interesting too.

They only record 200 entries in the histogram. They use Step Compression to show outliers. This means that similar values are lumped together so the outliers are more prominent.

_WA is used because MS is in WA.

sp_recompile works at the table level!

OPTION RECOMPILE is usefull to optimizing part of a query.

FULLSCAN will go parallel, SAMPLE will not. A sample of > 25% is slower than a full scan normally.

Filtered stats are pretty awesome.

TEMPDB
check out KB328551

Tips
AND is progressively limiting while OR is progressively inclusive.

To improve join performance
1. make sure the FKs are indexes
2. cover the join
3. cover the query

To improve aggregate performancehaving the group by in the index is important and you should INCLUDE any columns you want to aggregate.

DTA can useful for straight SQL but it is terrible with procs and views.

Notes
Kimberly makes heavy use of Solutions and SQLCMD. Should we consider these for the release foilders?

Friday, November 6, 2009

PASS Summit 2009 Day 3

Starting to wear down by the third day. The conference was a bit smaller today, I suspect the local folks only hit the first day or two. That makes sense because MS and the Vendors are only here the first two days as well.

Day 3:
Lab Time
I returned to the to the lab and did a few more sessions, unfortunately these all had problems. I got frustrated after a bit and checked in with work until the first session.

Analyzing File and Wait Stats (Andrew Kelly)
It's one thing to know that DMVs (Dynamic Management Views) exist, it's entirely something else to interpret them. That's what got covered in this session.

File Stats (sys.dm_io_virtual_file_stats)
These are PHYSICAL IO ONLY. Logical IO is excluded because it never hits the files, only the cache.
Backups will skew your results pretty heavily. If you are monitoring the Reads you will see a big jump during the backup time window. Take stats before and after backups so you can exclude these counts.

The Writelog waits will let you know if you need to move your Log file off to separate or faster disks.

TempDB will have more IO than you expect. Monitor it to determine if you need to add more files to the file group (wish they mentioned this during the TempDB session).

The two scripts he mentioned for monitoring the performance that he release are:

  • gather_file_stats_2008
  • report_file_stats_2008
The Stall that is reported can be > query time because stall is reported the sum of all threads and query time is overall.

When you have High Stall writes here are some things to try:
  • Add Writeback Cache to your RAID controller
  • Rebalance existing caches to 100% write and 0% read. SQL Server does not benefit much from read cache.
  • Add spindles
  • Separate Data and Log
  • Tune your SQL!
Wait Stats
There over 485 of these buggers! Internal and External ones are exposed in SQL 2008.

There is a new type that start with PreEmptive and are used to indicate you are waiting for something outside of the SQL Server's control (such as an OS or web service call).

Wait Time is the overall time being waited for while signal time is how long the task is waiting to get some CPU time. The actual Resource Wait Time = Wait Time - Signal Wait.

It is generally safe of ignore System and OLEDB. The only time you could impact these is by doing linked server calls. Some example waits:
  • CXPACKET: Check MAXDOP, lowering it may help.
  • LCK_M_%: You've got blocks!
  • ASYNC_NETWORKIO: Almost never the network, this is typically a slow client (I'm looking at you Data API!)
  • PAGE_IO_LATCH, IO_COMPLETION, WRITELOG: There is a storage issue somewhere.
  • PAGELATCH: Internal Resource Contention, check TempDB.
SQL Automation and some Powershell (Buck Woody)

This must be the funniest man at PASS. The session was great and he covered a lot of good stuff. I am going to watch all of his sessions when my DVDs arrive. I learned a lot and didn't realize it because he made it a lot of fun.

Powershell is now part of the "Common Criteria" at MS. That means that every product that is released must be able to managed using Powershell commands. What this really means: MS is not dropping Powershell any time soon and is probably worth learning.

There is a built in "MiniShell" in SQL 2008, you can open it right from object explorer.

Powershell resources to look into:

Advanced Physical Database Design (Boris Baryishnikov)
This was brutal, the guy was nice but his session completely mislabeled. It was about indexing, the database tuning advisor and sparse columns. There is a lot of Physical DB design choices to make when converting from a Logical Model and he didn't cover any of them. This was probably the most annoying session at PASS because the title was so completely inappropriate for the content.