I had this problem where I needed to gather Transaction Log information on multiple databases and check for valuable statistics on them. Running the command [DBCC Loginfo] brings back a number of rows for each Virtual Log File (VLF) in your Log File. It is really hard to do anything useful with that information on a larger scale. Each row returned gives you an estimate for the number of VLFs per Log File. Sure you could use the internal (and hidden) system stored procedure sp_msforeachdb to get the information for all database but it looks horrible. Here try it out for yourself before you read the rest of the post:
So why not make it better? That’s what I thought to myself, and I have recently been playing with storing DBCC command output to tables for analysis. I’ve put some together some code that allows you to capture the output of DBCC LogInfo into a Temp Table and then get some interesting information about the number of VLFs per database and other valuable information; see the comments for more information. Just by storing some of this data temporarily, I was able to write queries against it and discovered a major inconsistency in the size of my VLFs in a Log File that could potentially cause performance issues.
Feel free to create a permanent table for this data and run it on a regular basis to get an understanding of what your system is doing for troubleshooting. I also commented out the date field since I deemed it unnecessary, but if you’re looking for trending it maybe a good option to have that additional data.
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.
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.
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.
Less than a week left and I’m extremely excited about SQL Saturday in Philly on June 7th, 2014 and the Precon the day before (I signed up for Allan Hirt’s). I lived in Philadelphia for about 10 years during which I went to college, had my first two full-time jobs, and my first to kids were born in that area. This SQL Saturday is going to be a blast from the past for me. The actual event takes place in Malvern PA which is off of 202 in the Northwestern Region of the Philadelphia suburbs. It is part of the “mainline” and close to Valley Forge, King of Prussia and other historic/tourist attractions. I used to work in the Mainline area for Johnson Matthey in Wayne (and part time in Malvern) so I’m very excited about taking this trip back to visit friends and family.
For those of you that don’t know about SQL Saturday it is a fantastic event. Here are some of the reasons I’ve encouraged people to attend SQL Saturday events:
Instant File Initialization (IFI) is an interesting topic with regards to how SQL Server works with storage. It is an easy feature to turn on and can improve the performance of your server; specifically with creation and growth of data files including TempDB rebuilds with SQL Server restarts. There is a slight security risk where a professional data thief could potentially recover bits of data that have not been over written since IFI was turned on, but the chances of that happening are slim. Plus, if they have physical access to the hard drives on your server, you will have bigger problems to fix.
So without further ado here is the next delicious SQL Snack for Instant File Initialization:
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.
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.
**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.
I received an email from a connection on LinkedIn seeking some advice for architecture on a new reporting system. Although I don’t know the gentleman personally, he is a SQL Server professional and I felt obliged to help out a fellow colleague in the field. The email is included below and my suggested solutions following it.
An interesting post by Gethyn Ellis about an issue he encountered using Availability Groups. Follow the link above to read more.
I run into an interesting issue with an Availability Group database recently. I was running a data load against a database in an Always On Availability group database in SQL Server 2012. The load was SSIS package migrating data from staging database when the package fell over with the following error
“The transaction log for database DBName is full due to ‘AVAILABILITY_REPLICA’”
A part of every DBA’s job is to justify all those disk space requests. The phrases “I just need it” or “Trust me this database is going to grow one terabyte this year” might not convince your boss.
Many people are under the impression that disk space is cheap since they base that notion off of the shell shocker they saw on NewEgg or the weekly circular from Best Buy. If you are using consumer SATA drives from retail outlets for your production databases please do the following:
1. Update your resume
2. Apply for jobs at least 1000 miles from your current location
3. Hope the next DBA that replaces you has more common sense
We’re in the times of SANs, iSCSI, SAS drives, SSD drives and RAID architecture! You should be forking out almost the cost of your server in disk space. Unless of course losing data is an option where you work.
Now that I’ve bored you or intrigued you (or something in between) I can get to my point. There is a wonderful tool that comes with SQL Server 2008 and later called the Management Data Warehouse (MDW) also known as the Data Collector.
I’m going to call the series on System Center for the SQL Server DBA complete with this one last post today. First, here is the full series to date … my apologies for the lack of post naming consistency!!
What I wanted to point you all to is an update to my methodology of using SQL Server 2012 business intelligence leveraging in-memory analytical models to provide very important insights into your SQL Server environment with System Center Operations Manager. The link is here to my blog on this on SQL Server Pro Magazine.
This is somewhat of a quick or lazy way to truncate and shrink the transaction log because all you have to do is replace the database name and change your size requirements. It’s not the cleanest or best approach I admit, but sometimes I find myself on a development or staging server and I need some space quickly. There is no requirement on the server to preserve historic data, and since it is not highly transactional I don’t care what is on the log file to begin with. This happens often when running data aggregations in our DataMart environment where log files can grow up to 100GB+ with staging data that is not useful after the process is complete.
One problem I’ve always had is not having enough time to investigate a performance problem due to the fact that the system stored procedure Sp_Who2 only displays current activity. I had an issue with a problematic query that was running occasionally. Emails would be sent out about CPU usage spiking on a production transactional system but they would be short lived. I needed a way to save some of the activity so I could use it later.
![[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB](https://thesqlpro.files.wordpress.com/2014/01/sql-snacks.jpg?w=150 "[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB")
![[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools](https://thesqlpro.files.wordpress.com/2014/09/nissan_ga16de_engine.jpg?w=150 "[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools")
The SQL Pro 