Remember Neo? The guy who disturbed the order of the matrix? He had dreams of being given instructions to follow the white rabbit to figure out the answer to the question, "What is the Matrix?" Later in the film, he finds a woman with a tattoo of a white rabbit on her shoulder. And, then, he does just as he was instructed.
In my previous blog post, I talked about doing a MVP Birds of a Feather Lunch session at the upcoming PASS Community Summit in Seattle, WA and how you, if you're attending the conference, should watch out for blog posts that contain secret messages so you can get a special gift when you drop by my session. In this particular blog post, you will find a link to a graphic that you can download and print out. I'll tell you what you need to do about the graphic on my next blog post. And while finding out where the secret message is ain't about disturbing the order of The Matrix, it will certainly help you distinguish yourself from the rest
Wednesday, November 3, 2010
Monday, November 1, 2010
PASS Summit MVP Birds of a Feather Lunch 2010
After a few months of inactivity, I'm back to blogging - this time with a purpose. By now, there have been a lot of blog posts about the upcoming PASS Community Summit 2010 in Seattle, WA on the 9th-11th November 2010. This, in particular, has been announced earlier this month but I need to confirm my attendance even before I can blog about it. So, now that my plane tickets and hotel reservations are set, I guess I can say that I am indeed going to PASS this year. And while I am not doing a regular session this time around, I will be at the MVP Birds of a Feather Lunch on the 10th. Keeping up with my commitment to do professional development sessions at PASS (although I will be at the Ask-the-Experts booth on High Availability and Disaster Recovery as well), my topic for the lunch session will be about why you need to distinguish yourself from the rest.
If you're attending the PASS Summit this year, drop by my area and I'll have something for you. For the next couple of days, I will be posting some secret messages on Twitter that give instructions on what you need to bring with you when you drop by. And I'm definitely sure that you'll enjoy having lunch with me because it's all about YOU.
Below is the list of topics for the Birds of a Feather Lunch for the PASS Summit 2010. I got it off Mike Walsh's post on SQLServerPedia so be sure to check out that site as well if there are any changes, although, I'm pretty sure there won't be any at this time.
Allan Hirt SQL Server Manageability Tips for the DBA
Ami Levin Performance Management Tools
Andy Leonard SSIS Frameworks (or design patterns or getting started)
Andy Warren Statistics
Arnie Rowland Community – TechNet Wiki
Brad Schulz Interpretting Query Plans
Bruce Loehle-Conger SSRS Lessons Learned
Chris Webb Performance Tuning SSAS
Christian Bolton Hyper-V with SQL Server
Christopher Shaw Professional Development for the Data Professional
Darren Gosbell SSAS Triple A – Administration, Automation and APIs
Davide Mauri SQL Server & NoSQL Alternatives
Dean Vitner Write-Ahead Logging & The Transaction Log
Denny Cherry SQL Service Broker
Eduardo Castro HA In Virtualized Environments
Edwin Sarmiento Why you need to distinguish yourself from the rest
Erland Sommarskog Practical T-SQL Programming
Geoff Hiten Advanced SQL Clustering
Gianluca Hotz Oracle to SQL Server DBA
Glenn Berry Database Mirroring
Grant Fritchey T-SQL Tuning & Optimization
Greg Galloway What’s the Coolest Business Problem You’ve Solved with a Cube?
Jason Strate Querying The Procedure Cache
Jeff Moden Black Arts T-SQL (Like the Tally Table)
Jessica Moss Fixing Bad Data Using SSIS
Jesus Gil Geo-Spatial Data on SSRS (en espanol)
Jonathan Kehayias VMWare With SQL Server
Justing Langford SQL Server Consolidation
Kevin Boles Performance Tuning
Kevin Kline Help for the involuntary DBA
Louis Davidson Database Design
Michael Coles SQL Encryption (pending ATE schedule question)
Michael Steineke High Performance DBs on Shared Storage
Patrick LeBlanc CDC
Paul Turley BI Tool & Visualization Choices
Peter Ward SQL Server Migration and Upgrade Planning
Plamen Ratchev Implementing Hierarchies in SQL
Rafael Salas PowerPivot: Where/How Are You Using It?
Rob Farley Tables don’t exist, so join me at my index
Scott Klein Data Development in SQL Azure
Sean McCown PowerShell For the SQL Server DBA
Ted Krueger Cheapest DR/HA Options on SQL Server
Tim Chapman SQL Security Best Practices
Tim Mitchell Care and Feeding of your SSIS Infrastructure
Tim Ford DMVs
Todd McDermid Data Warehouse ETL with SSIS
Tomislav Piasevoli The Past, Present and Future of Microsoft BI
If you're attending the PASS Summit this year, drop by my area and I'll have something for you. For the next couple of days, I will be posting some secret messages on Twitter that give instructions on what you need to bring with you when you drop by. And I'm definitely sure that you'll enjoy having lunch with me because it's all about YOU.
Below is the list of topics for the Birds of a Feather Lunch for the PASS Summit 2010. I got it off Mike Walsh's post on SQLServerPedia so be sure to check out that site as well if there are any changes, although, I'm pretty sure there won't be any at this time.
Allan Hirt SQL Server Manageability Tips for the DBA
Ami Levin Performance Management Tools
Andy Leonard SSIS Frameworks (or design patterns or getting started)
Andy Warren Statistics
Arnie Rowland Community – TechNet Wiki
Brad Schulz Interpretting Query Plans
Bruce Loehle-Conger SSRS Lessons Learned
Chris Webb Performance Tuning SSAS
Christian Bolton Hyper-V with SQL Server
Christopher Shaw Professional Development for the Data Professional
Darren Gosbell SSAS Triple A – Administration, Automation and APIs
Davide Mauri SQL Server & NoSQL Alternatives
Dean Vitner Write-Ahead Logging & The Transaction Log
Denny Cherry SQL Service Broker
Eduardo Castro HA In Virtualized Environments
Edwin Sarmiento Why you need to distinguish yourself from the rest
Erland Sommarskog Practical T-SQL Programming
Geoff Hiten Advanced SQL Clustering
Gianluca Hotz Oracle to SQL Server DBA
Glenn Berry Database Mirroring
Grant Fritchey T-SQL Tuning & Optimization
Greg Galloway What’s the Coolest Business Problem You’ve Solved with a Cube?
Jason Strate Querying The Procedure Cache
Jeff Moden Black Arts T-SQL (Like the Tally Table)
Jessica Moss Fixing Bad Data Using SSIS
Jesus Gil Geo-Spatial Data on SSRS (en espanol)
Jonathan Kehayias VMWare With SQL Server
Justing Langford SQL Server Consolidation
Kevin Boles Performance Tuning
Kevin Kline Help for the involuntary DBA
Louis Davidson Database Design
Michael Coles SQL Encryption (pending ATE schedule question)
Michael Steineke High Performance DBs on Shared Storage
Patrick LeBlanc CDC
Paul Turley BI Tool & Visualization Choices
Peter Ward SQL Server Migration and Upgrade Planning
Plamen Ratchev Implementing Hierarchies in SQL
Rafael Salas PowerPivot: Where/How Are You Using It?
Rob Farley Tables don’t exist, so join me at my index
Scott Klein Data Development in SQL Azure
Sean McCown PowerShell For the SQL Server DBA
Ted Krueger Cheapest DR/HA Options on SQL Server
Tim Chapman SQL Security Best Practices
Tim Mitchell Care and Feeding of your SSIS Infrastructure
Tim Ford DMVs
Todd McDermid Data Warehouse ETL with SSIS
Tomislav Piasevoli The Past, Present and Future of Microsoft BI
Sunday, August 1, 2010
Communicating Shrinking Databases to the Accidental DBAs
I'm a self-proclaimed communicator (although most people say that I was born a teacher) which is why I always think of ways to simplify concepts, though they seem to be complex.
I was on IM with my former student and asking him how he was doing. During the course of the conversation, I asked if they have a full time DBA. Unfortunately, they don't and that they rely on their ERP system's tuning tool to do the performance tuning for their databases (of course, I recommended hiring my services to do it for them). One question that popped up during the conversation was shrinking the database. Most of the time, I get tempted to refer them to SQL Server MVP Tibor Karaszi's blog about why you shouldn't be shrinking your databases which I have done a lot of times when answering questions in the technical forums. But this time, I approached it differently because I was this guy's former trainer and he definitely knows that I will try to keep things as simple as possible. I started out with the concept of internal and external fragmentation, explaining what happens to the database files when you shrink them and, as auto growth kicks in due to the additional amount of data being loaded, grow back to a large file size. Imagine a room full of boxes where each box contains a bunch of items - shoes, books, other personal items, etc. Of course, a box has a fixed volume that can only contain a certain volume of items, much so occupying a certain amount of space in the room. If you need to add more items in a box full of books, what would you do? Well, you would have to get a bigger box to replace the smaller one before you can hold the additional books. So, the process of adding more books in a full box definitely requires more effort even before you can do the only task you thought about doing - storing books in a box. I bet it would take more time and effort storing books in a full box as compared to storing them in an empty one. Think of this box as your database. While we cannot auto grow boxes due to the underlying principles of nature, we can do so with databases and any files stored in the file system. However, the process of storing books in a full box is similar to adding more data in a full database. The database engine will have to grow the database even before it can add the data in it. But isn't the goal just to add records? This means that we are making the database engine do more unnecessary work than we need it to. And you wonder why database professionals complain about transactons getting blocked when the database is already full. Which is why the best practice is to allocate more than enough space on your databases so that we minimize those auto growth events during regular business hours. And this is where Tibor explained about internal fragmentation and how shrinking your databases will fragment your indexes, further hurting your database performance. Now, what about external fragmentation? Let's get back to that box analogy. If you were to replace the small box with a bigger one, wouldn't you move the other boxes around just to accomodate the new, larger box? Imagine that the smaller box is on the third column, fourth stack of the fifth row and that you have organized the boxes in the room in such a way that you have maximized the space (by the way, I'm a bit like that when arranging my personal stuff). You would then have to take the old, smaller box out of it's original location, find a place where you can place the new, larger box and, finally, store the additional books. If the new, larger box fits in the same place as your old, smaller box, then, you would have unused space in the room and, therefore, not maximizing the room space. This, from a file system and disk perspective, is called fragmentation and from the database's perspective, external fragmentation. And, because the boxes (or files for disk subsystems) are not arranged in contiguous, close-together kind of fashion, you (or the disk) will have to work harder just to get access to different boxes stored inside the room.
So, the next time somebody asks about why database shrinking is definitely not a good idea, you can tell him this story
I was on IM with my former student and asking him how he was doing. During the course of the conversation, I asked if they have a full time DBA. Unfortunately, they don't and that they rely on their ERP system's tuning tool to do the performance tuning for their databases (of course, I recommended hiring my services to do it for them). One question that popped up during the conversation was shrinking the database. Most of the time, I get tempted to refer them to SQL Server MVP Tibor Karaszi's blog about why you shouldn't be shrinking your databases which I have done a lot of times when answering questions in the technical forums. But this time, I approached it differently because I was this guy's former trainer and he definitely knows that I will try to keep things as simple as possible. I started out with the concept of internal and external fragmentation, explaining what happens to the database files when you shrink them and, as auto growth kicks in due to the additional amount of data being loaded, grow back to a large file size. Imagine a room full of boxes where each box contains a bunch of items - shoes, books, other personal items, etc. Of course, a box has a fixed volume that can only contain a certain volume of items, much so occupying a certain amount of space in the room. If you need to add more items in a box full of books, what would you do? Well, you would have to get a bigger box to replace the smaller one before you can hold the additional books. So, the process of adding more books in a full box definitely requires more effort even before you can do the only task you thought about doing - storing books in a box. I bet it would take more time and effort storing books in a full box as compared to storing them in an empty one. Think of this box as your database. While we cannot auto grow boxes due to the underlying principles of nature, we can do so with databases and any files stored in the file system. However, the process of storing books in a full box is similar to adding more data in a full database. The database engine will have to grow the database even before it can add the data in it. But isn't the goal just to add records? This means that we are making the database engine do more unnecessary work than we need it to. And you wonder why database professionals complain about transactons getting blocked when the database is already full. Which is why the best practice is to allocate more than enough space on your databases so that we minimize those auto growth events during regular business hours. And this is where Tibor explained about internal fragmentation and how shrinking your databases will fragment your indexes, further hurting your database performance. Now, what about external fragmentation? Let's get back to that box analogy. If you were to replace the small box with a bigger one, wouldn't you move the other boxes around just to accomodate the new, larger box? Imagine that the smaller box is on the third column, fourth stack of the fifth row and that you have organized the boxes in the room in such a way that you have maximized the space (by the way, I'm a bit like that when arranging my personal stuff). You would then have to take the old, smaller box out of it's original location, find a place where you can place the new, larger box and, finally, store the additional books. If the new, larger box fits in the same place as your old, smaller box, then, you would have unused space in the room and, therefore, not maximizing the room space. This, from a file system and disk perspective, is called fragmentation and from the database's perspective, external fragmentation. And, because the boxes (or files for disk subsystems) are not arranged in contiguous, close-together kind of fashion, you (or the disk) will have to work harder just to get access to different boxes stored inside the room.
So, the next time somebody asks about why database shrinking is definitely not a good idea, you can tell him this story
Continuing the SharePoint Foundation 2010 Installation
I've been out of the blogosphere for quite a while due to travel and work but I realized that part 2 of the series on installing SharePoint Foundation 2010 is already published. Check out the last part of the article at MSSharePointTips.com
Subscribe to:
Posts (Atom)