SQL Server tracing is essential for troubleshooting performance issues, yet it can put loads on your server that would cause noticeable slowness, capture so much information that it fills the drive, and I’ve even seen it cause a cluster failover more than once. With a tool this powerful you just… Read more
According to Jason Strate (SQL Server MVP) there are 5 white papers ever SQL Server DBA should read. Considering my blogging is focused on how to monitor and tune previously installed servers, we’ll focus on the first two of these white papers: Troubleshooting Performance Problems in SQL Server 2008 Read more
If a query is taking longer to run than normal, there’s a good chance it’s being blocked by something else. This is especially true when you’re doing something rediculously simple and SQL Server just sits there thinking. Symptoms of blocking problems include a trace of SQL:BatchCompleted and RPC:Completed with durations… Read more
sys.dm_os_wait_stats is one of the most important DMVs out there, and one that you should know the historical values of for every server you care about. This DMV will tell you the cumulative amount of time you waited for each wait type since SQL Server services started, which is nice… Read more
The biggest problem developers and newer DBAs have with understanding indexes is that you don’t realize when you’re using the exact same thing away from your computer. Pick up any reference style book and you have one clustered index and one nonclustered index. The clustered index is also split into… Read more
Indexes aren’t free, and many databases end up with unused indexes. Every time you make any update to a table you will be updating the clustered index (I assume no heaps, because I hate heaps), and every index that has uses one of the columns that were updated. Inserts and… Read more
This info should be easier to get than it is. Keep in mind that for something to run the subscription and schedule both have to be enabled.
To be fair, the majority of this code was grabbed from the internet, and this is not something I modified to the extent… Read more
Rolling log files for a day, especially with 15 or even 5 minute log backups is a pain at best. Here’s a slightly better way to do it. Set the two variables at the top to the directory where the log backups are and the database you’re looking to restore.… Read more
There’s no simple way in SQL Server to see the sizes of all the tables and their indexes. Even seeing a single table’s size through SSMS can take a while. The solution below is grabbed from statistics, so there’s no guarantee you’re getting exact numbers. However, exact numbers don’t tend… Read more
Don’t trust the name of an index to tell you what it is. The name is wrong, or at least that’s what you have to assume.
I’m a huge fan of index naming conventions, with my favorite being starting out by saying it’s an index (IDX) followed by the table… Read more
Sp_who2 is not used by this DBA. That info is amazingly good to have, but I want more. Don’t get me wrong, sp_who2 is kinda hidden in this one as it’s really just a proc that does little more than “SELECT * FROM SysProcesses“, and I use SysProcesses… Read more
Can you tell me the drive we were backing up server #58 to exactly 8 months ago, and, if possible, it would really help if you could give me the exact file name for me to pull the backup from tape for you. Or, last night’s backup didn’t finish until… Read more
This script is so simple you’ll start off by asking why you’d ever use it. Then you’ll use it and wonder why no one gave this to you as part of your new-hire orientation at your first DBA job. The truth is that in larger environments there will always be… Read more
There are two scripts I like to use to look at the file sizes on servers. The first one is everything I could hope for on servers where there is only a single file per filegroup. All of the vital information is all in one place. I know, DBAs tend… Read more
Copy / Paste formatted text is a nightmare in WordPress. I’m singling out WordPress because I use it, and, from what I can see, every blog site has this issue. If you have a SQL Script (again, singled out because that’s what I use), and copy / paste from SSMS… Read more
This script looks much more intimidating than the results it produces, so I recommend running it before reading through it. I’ll run this when I first get on a unfamiliar server as a basic assessment of what’s there and find some hard to find issues with no effort.
Like many SQL Server DBAs I run into a problem at work or get an idea in my head then scour the internet to see if someone else had the same thing so I don’t have to reinvent the wheel. Some DBAs stop at that point, I don’t. I grab… Read more