SQL Server News & Information tsql, performance tuning, industry trends, & bad jokes
tsql, performance tuning, industry trends, & bad jokes
This site is maintained by Jason Massie. He has 10 years experience as a DBA and has specialized in performance tuning for the last five. He was recognized by Microsoft as a SQL Server MVP. Jason has spoken at the Professional Association of SQL Server Conference, the North Texas SQL Server Users Group, SQL Connections and TechED. He has worked at Terremark (formerly Data Return) for nearly a decade.
You can contact him at jason@statisticsio.com or 469.569.5965
Abstracts addition Affinity Aggregation allocation Always Analysis Announced another API Appending article Authentication backup be Behavior between Bootstrapper Breaking Build Cache Caching Check checksums Codeplex collection Connecting contest Controller Creating CTEs CTP CUBE cursors Data Database DATALENGTH Debugging Design Diagnosing Diagnostic Differences Documentation DTS Emergency enhancement Entity ETW Exchange execution Express Extensions Fall February Filestream Filtered group GROUPING have Hosting Idle impact Improvement Increase Index Indexes Inserts Instances Interoperability Introduction IO large Late LOB local Localized Magazine Maintaining Maintenance Management maps March Microsoft minutes missing Mix Never November Offline OLE Online operations operators optimizations Optimized Overlapping Package Page Paging Panacea parallel part Partial Partition partitioned Partitioning PASS Performance PFS plan Plans Practices problem Problems Procedure Program programmatically Programming Protection Queries query read recent Recursive Related released Reports Restore return ROLLUP ROWCOUNT Runtime Security Select Sequence sequential Server Services set SETS Shooting shorts sizes Solutions Sortable SPARSE Spool SQL SQLIOSim SSIS Stalled Star Statement Statements stats Stored strategy Stuck Studio Submission Subreports Suggested Summarizing system Table Tables Tampa Task Than there through Timeouts Total Traces Transaction transfer Tricks Trouble TSQL turning understand Understanding undocumented Unique unused upgrade Upgrading Useful Value variables VDI Vista Will Windows Wireless
Note: Let me start off by stating my definition of physical database design might be broader than the classical definition.
A look into the past
The landscape changes a little with each release. In SQL Server 2005, partitioning, partial database availability and mirroring really stand out as things to consider when planning the physical design. Less databases are better for mirroring. You must think in filegroups when partitioning and when implementing partial database availability.
Learn and plan today
What about SQL Server 2008? Well, at first look, nothing really changes from the physical database design concepts except filestream and filtered indexes. Check out what's new in SQL Server 2008.
However when you pull out the feature palette and start painting, it is deeper. What if you want to encrypt and compress data\backups? They don't work well together. You could put the sensitive data in a separate database and use transparent data encryption(column level encryption could be argued for as well.) The idea being the sensitive data is a small percentage of the total data. With filestream, considerations go further than just the location of the files. It would probably make sense to separate file stream data in its own database as well. Filestream doesn't play well with others just yet. Some issues are mentioned here. Mirroring, TDE, snapshot isolation and other are not supported on a database with filestream enabled. How about partitioning and compression? You could compress the partitions that are less often queried and less likely to be in cache while leaving the hottest data uncompressed. Sprinkle on compressed ,covering, partitioned but aligned, filtered indexes with a cherry on top just for fun!
Design for the future
At any rate, I don't have any production SQL Server 2008 servers yet much less any databases designed from the ground up for SQL Server 2008+. We should still plan for new features even if we are still designing SQL 2000\2005. Some of these are hard to change later. Imagine squashing several databases together that have been in production for several years. How about moving all of the objects out of the primary filegroup to implement partial database availability on a database that needs to be ..err... available.
Any other angles I am missing?
posted @ Wednesday, August 13, 2008 9:13 PM by DotNetKicks.com
posted @ Thursday, August 14, 2008 5:33 AM by K. Brian Kelley
Name (required)
Email (required)
Website
Notify me of followup comments via e-mail