I just finished blog entry one hundred yesterday. It is quite an accomplishment since it takes one to two hours to research, design, and compose an article. I figured it was time to reset my goals for the upcoming year. In the next thirteen months, I will be focusing on the Microsoft SQL Server Stack: Database Engine, Integration Services, Reporting Services and Analysis Services. I will of course throw an article off topic from time to time when it suits me. During this time I also want to update my…
Month: November 2012
Deleting Large Data
Most of the time, the transaction log is your friend since it can be used for a point in time recovery. However, when deleting large amounts of data from a database, it can be your enemy. I will be reviewing THREE different TSQL patterns to delete data. Today, we will be using the MATH database that contains prime numbers from 2 to 6 million. I will post the actual files on this article so that they can be detached and reattached as needed for each test. We need to remove…
Shrinking the Transaction Log
The transaction log file records all transactions and the database modifications made by each transaction. Heavy application processing against the database can make the transaction log grow quickly. Therefore, the transaction log must be truncated on a ADHOC basis to keep it from filling up. Each physical log file is divided into smaller logical units called virtual log files (VLFs). Truncation provides free space by deleting inactive virtual log files. To avoid running out of log file space or physical disk space if growth is set to unlimited, one should…
Checksum vs Hashbytes
I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process. Today, I want to talk about how to generate a hash key by using two built in SQL Server functions. A hash function is any algorithm that maps…
Surrogate Keys
I recently worked on a project I which I redesigned sales data warehouse as a STAR schema, using daily file partitions, with a automatic sliding window, and applying data compression at the page level. I ended up reducing a 5 terabyte database to less than 750 GB. I will be writing several articles on the lessons that I learned during the process. Today, I want to talk about how surrogate keys can be used to tie the dimension tables to the fact tables. I need to start the talk off…
Seattle – Pass Summit 2012
I just returned to New England after 4 days in Seattle at Pass Summit 2012. I analogize the conference to going out to eat every hour, some presentations I truly enjoyed; Others did not suit my pallet. Overall, I would rate the event as a total success. Here are some presentations I viewed in chronological order that were definite highlights during my trip.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Matthew Roche & Peter Myers - 10 Extraordinary Things to Achieve with Integration Services 2012. Devin Knight - Adapting Your ETL Solutions to Use SSIS 2012. Kalen Delaney - Storing ColumnStore Indexes. Brent Ozar - Real-Life SQL 2012 Availability Group Lessons Learned. Kendra Little - Index Psychiatry: Diagnose and Treat the Top 5 Disorders. Paul White - Query Optimizer Deep Dive. Tim Ford - You Can Do That with T-SQL? |
In summary, if you can get the chance to go to the Summit 2013 Conference in October at Charlotte NC, please do. You will not…