[SQL Snacks Video] Performance Tuning 103 – T-Logs and VLFs

Finally the arrival of Part 3 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. Also, if you missed part two from yesterday you can review it here.

If you are still interested in learning more about Performance tuning with SQL Server, I will be giving an hour long presentation with the PASS DBA Fundamentals Virtual Chapter on January 6, 2015 (11 am Central Time/Noon Eastern Time).  For more information please visit http://dbafundamentals.sqlpass.org/ and join PASS for a great way to learn more about SQL Server.

Code from Demo below:

 

[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

Finally the arrival of Part 2 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. This second video discusses the importance of properly sizing Data files, placement, and how the Proportional Fill-Algorithm works for data insertion. I’m hoping you get some last minute Performance tuning in before 2015 and so I will be posting the third video within the next 24 hours.

Code from Demo below:

 

[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools

Welcome to Part 1 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. This first video describes how to setup a baseline for your system using the PAL tools. It is essential to get a baseline before you start performance tuning so that you can determine how effective the efforts done in trying to tune your SQL Server have been.  The PAL tools at first look a little intimidating but they are really very easy to use and extremely helpful for performance analysis. Enjoy and happy baselining!

A special thanks to Edgardo Valdez for showing me the how to use this tool.

Link to download PAL Tools and Prerequisites: https://pal.codeplex.com/

 

[SQL Snacks Video] SQL Server Table Partitioning 103

Welcome back for part 3 of my SQL Snack Pack on Table Partitioning! If you have not watched the first two videos, I would highly encourage you to do so.

 

[SQL Snacks Video] SQL Server Table Partitioning 102

Welcome back for part 2 of my SQL Snack Pack on Table Partitioning! If you have not watched the first video, I would highly encourage you to do so.

 

[SQL Snacks Video] SQL Server Table Partitioning 101

I hope you’re hungry for another SQL Snack! In fact, this will be one of a series of snacks (dare I call it a SQL Snack pack?). Table partitioning is a fantastic feature that is easy to learn and can significantly improve your OLTP and Data-warehouse environments. It can be a little intimidating because it is tricky to get started with, but once you get the basics down you’ll realize it’s pretty straight forward and a very useful feature to have.  I will be providing the code and outline for each of the SQL Snacks related to table partitioning so that you have a chance to practice on your own.  Happy partitioning!

 

Indexing Fundamentals Presentation – Pending Questions Answered

On July 10th 2013, I gave a presentation with the Professional Association for SQL Server (PASS) DBA Fundamentals Virtual Chapter. The topic was Indexing Fundamentals and there were some questions that we did not have time to answer. I have combined similar questions and given one answer to cover the specific topic.

If you missed the presentation, you can click HERE to see the recording.

Abstract:
Indexing is an integral part of SQL Performance and Architecture. In this presentation we will cover the basics of Clustered and Non-Clustered Index structure and function. Additionally, we will discuss the B-Tree structure, index density (fill factor), page splits, and performance considerations. Finally, there will be a brief discussion of index maintenance.

 

Indexing Fundamentals Presentation

**This Session has been rescheduled for July 10th @ Noon EST (16:00 UTC)**

On July 10th 2013 @ Noon EST (16:00 UTC) I will be giving a presentation with the Professional Association for SQL Server (PASS) DBA Fundamentals Virtual Chapter. Entrance is free as always and details can be found at http://fundamentals.sqlpass.org.

Code, Slides, and other information about the presentation will be under the Presentations section of this site afterward. Hope to see you there!

Abstract: Indexing is an integral part of SQL Performance and Architecture. In this presentation we will cover the basics of Clustered and Non-Clustered Index structure and function. Additionally, we will discuss the B-Tree structure, index density (fill factor), page splits, and performance considerations. Finally, there will be a brief discussion of index maintenance.

 

Estimate Table Size based on the Metadata from INFORMATION_SCHEMA.COLUMNS

A problem I face often is not knowing the size of a new table that I’m creating. I put some basic code together that pulls the column information from INFORMATION_SCHEMA.COLUMNS and with some basic math gets the approximate size of the table based on the estimated number of rows.

 

SQL Saturday in Washington D.C. December 8, 2012

This is my first time attending a SQL Saturday event and I’m quite excited. If you live in or near the D.C. Metro area I hope to see you there. If you have not registered, you still have a chance:

http://sqlsaturday.com/173/eventhome.aspx

I’ve put together a short list of the sessions that I am planning to attend with some brief reasons why. Hope to see you all there!

 

SQL Server Parallel Data Warehouse (PDW) and SQL Server Fast Track Data Warehouse

The SQL Server Parallel Data Warehouse and SQL Server Fast Track Data Warehouse are very high end builds of Microsoft’s SQL Server 2008R2 product; and SQL 2012 in the future. I’ve been intrigued by these products so I’ve started to learn about it. They have both been on the market for a while, but I’ve just recently been exposed to the technology and it has been exciting to learn more and more about it.

James Serra was kind enough to take the time to point me in the right direction with a great post that he wrote up last year:
http://www.jamesserra.com/archive/2011/08/microsoft-sql-server-parallel-data-warehouse-pdw-explained/

Here are some additional resources about this product:
https://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx

http://dwjunkie.wordpress.com/2012/02/22/parallel-data-warehouse-pdw-poc-lessons-learned/

Finally, a very nice set of videos provided by the Microsoft Virtual Academy:

https://www.microsoftvirtualacademy.com/tracks/breakthrough-insights-using-microsoft-sql-server-2012-scalable-data-warehouse

Module 2  talks about the PDW architecture. Module 4 talks about the Fast Track Datawarehouse; which is a product that sits between your custom build of SQL Server and the PDW. Of course all the other modules are useful, but just in case you wanted to take a quick dive into material related to the PDW you should go for Modules 2 and 4.