With opening day of Major League Baseball season finally here, I thought I’d take the time to cover two of my favorite topics…SQL Server and softball. Have you ever thought about how you can use SQL Server in conjunction with softball? Ok, so maybe you haven’t, but I have. I have been managing a slow-pitch softball team, the Sons of Pitches, for the past 5 years. Yes, I did say “slow-pitch”. My friends and I have already passed the peak of our physical ability, so we use it as an excuse to get together and have a little fun. As a DBA, I’m...
Continue reading...Use Powershell to Pick Up what Database Mirroring Leaves Behind
Database mirroring has been around since SQL Server 2005, and it’s turned out to be an excellent step up from log shipping. However, like log shipping, it is still only a database-level disaster recovery solution. Meaning that any logins, server role memberships or server-level permissions will not be mirrored over to the mirror server. This is where the DBA needs to plan ahead and create their own custom jobs to script and/or document these types of shortcomings. My solution is to use Powershell. In this example, I have setup database mirroring for the AdventureWorks2012 database. For this demo, both instances, TEST1 and TEST2, are on...
Continue reading...T-SQL Tuesday #40 – Proportional Fill within a Filegroup
T-SQL Tuesday #40 is underway, and this month’s host is Jennifer McCown (blog|twitter). The topic is about File and Filegroup Wisdom. Jennifer says she’s a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup. This should be pretty common knowledge, but I still talk to a lot of DBAs that don’t know anything about it, or if they have heard of it, they still don’t know how it works. The proportional fill algorithm is used to keep the amount of free space within a filegroup evenly distributed across all...
Continue reading...Dealing with a Fragmented Heap
Just for the record, this happens to be one of my favorite interview questions to ask candidates. At some point in time, there will be a database containing tables without clustered indexes (a heap) that you will be responsible for maintaining. I personally believe that every table should have a clustered index, but sometimes my advice is not always followed. Additionally there can be databases from a 3rd party vendor that have this same design. Depending on the what those heap tables are used for, over time it’s possible they can become highly fragmented and degrade query performance. A fragmented heap is...
Continue reading...The Performance Impact to Prefix Stored Procedures with sp_
Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries. In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,… This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that’s prefixed with sp_. All DBAs know, or should know, you should not prefix stored procedures with sp_. Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online. I’m not going to discuss the do’s and don’ts of naming conventions. What I want...
Continue reading...
