Dan Linstedt has some interesting tidbits from the field in using SQL Server 2005 and SSIS posted. He has a wish-list for SSIS and SQL Server 2005. Couldn’t agree more for his request for SS, but I wouldn’t vote for Visio functionality as the data modeling interface. I’ve used Visio for modeling more than a couple of times and it can be painful and a little rudimentary. It’s better than what’s built into the server tools, but I would like to see something easier to use and better features.
He talks a bit earlier in the post about the pains of table scans and poor query performance. I can’t really comment on what he’s seeing because I’m not sure what he is doing or what the schema/configuration looks like, but what I can say are that if you are seeing a table scan in your execution plan then there is no optimization going on. He’s definitely filtering the data down a bit, so there are a couple of things that he probably could do:
- Clustered indexes – lay down the data on disk on your most common filtering criteria. Limits the zigzagging on disk that can eat read performance away when pulling lots of data off disk.
- Eliminate Row/Index Free Space – Use the fill factor and pad index features to make sure your rows and indexes are packed tight. This will fill more pages and reduce the number of pages accessed for queries.
- Minimize Size of Columns Used for Indexes – Have a reference table with only 100 values? If the PK is an INT then you’re using three times the space. Small tweaks make a big difference when dealing with a lot of data.
Of course since he’s using a single disk that’s going to set him back some. At the very least you should have two channels so you can setup the physical storage of the server and database in order to read and write without the bottlenecks.
UPDATE: Dan posted a great follow-up in response to my post on his blog that goes into more detail about what he’s working with. Tons of good advice and perspective. After reading his post and thoughtful comments I realized that I exemplified what can go wrong when you try and oversimplify a solution to a complex problem when you don’t have all the information. Would like to say I planned it that way to prove a point, but I’m not that smart.
April 3, 2007 at 10:42 am |
Out of curiosity, what would you like to see for modeling? (I would not suggest Visio either).
April 3, 2007 at 1:35 pm |
Good question, Matt. Here are a few things that come to mind:
- Fast rendering. As the schema grows in size (tables and relationships) the tool should be just as responsive.
- Support for conceptual and logical modeling. Layer the model so the physical layer can be organized by several different views and drilled into for the details and modeling.
- Save without commit. Most databases of reasonable size are not architected in one sitting. It would be great to save and commit changes at a later date.
- Versioning. Schemas adapt and change over time. Plus it’s very possible to have different schema versions in different environments (Development, Integration/QA, Staging, Production, etc.) and you should be able to have a history with versions and tagging.
- Integrating multiple data sources. At the very least to show and map logical relationships. It would be awesome to make a change in a staging area and have a recommendation made for changes in the target database.
- Raise warnings long before commit. As you create relationship and constraints it should flag problems right away instead of waiting until commit time (which could be after hours of work).
- Recommend changes. If you decide to change the datatype or field size of a PK in a lookup table (for instance), in addition to the warnings (listed above), it should ask if you want to replicate that change through the relationships.
April 8, 2007 at 9:34 am |
Hi Ben,
Thank-you for your post and your reference to my blog. I’ve posted a new blog entry that explains some of my thinking. I’d be curious to see what you’re new thoughts are.
http://www.b-eye-network.com/blogs/linstedt/archives/2007/04/sqlserver2005_a_1.php
Thank-you,
Dan Linstedt
April 8, 2007 at 8:02 pm |
Hey Dan,
Thanks for the comments. I knew when I wrote this that I really didn’t know what you were trying to do with your work, assumed you had good reason, and thought it was a good springboard for some performance thoughts from my experience. But I knew I was skimming the surface and should have tabled it till I had more time.
My next post will be about the dangers of superficial blogging
Followed-up on your site as well saying pretty much the same thing. I really enjoyed your feedback and you’ve got a great blog.
Thanks for keeping me honest.
April 15, 2007 at 3:40 pm |
See my article on SSIS and Visual Studio 2005 performance issues. I’m quite impressed by SSIS as an integration platform, but it’s VERY slow as a power language.
http://chriswoodill.blogspot.com/2007/04/ssis-and-visual-studio-2005-are-very.html