Simple SQL Server

When I get alerts from SQL Server I want it to do three things for me. Tell me what’s wrong, show me the data, then tell me how to take action on that data. Unfortunately, SQL doesn’t have a great way of showing the data unless you want to parse it into text by yourself or click on an attachment to view a text document of query results. Start with the punchline Here is an example email I have…

New Post: Powershell, when were my servers rebooted?

One of my servers wasn’t rebooted when it should have been and I never realized it until after the outage was over.  The big deal wasn’t that it didn’t reboot, it was that I never realized it.  These are the things that bug me, so I used it as an excuse to learn a bit of PowerShell. A little backstory to give the details a better chance of making sense to everyone…  Instead of having a couple…

New Post: File Growths #entrylevel #sqlpass

I hinted at file growths in my previous posts about shrinking data and log files.  Then I talked about growing log files in my post Database Log VLFs.  However, there are still some unanswered questions about growing data files. Should I use autogrowth?  What should I set it to grow by? Why do manual growths, and how do I know how much to grow by or when a growth is needed? Can I and should I use…

Database Growth Trends - Idera Diagnostic Manager - @Idera_Software

How fast are your databases growing?  Should I panic about a 1 TB database that has 100 GB free on disk?  Am I safe with a 100 GB database that also has 100 GB free?  Based on those sizes…I have no idea. It’s possible that the 1 TB database is purging data as fast as it takes it in, so it’s been at 1 TB for a long time and may never use the 100 GB it has free.  Looking at the 100 GB database, it…

New Post: Multiserver Queries #entrylevel #sqlpass

You can run queries against multiple servers at once, and it’s quite useful for a number of reasons.  I use it to check settings, verify backups and DBCC ran recently on unmonitored servers, make sure all servers rebooted during the maintenance window, and many other reasons. This is all done through registering servers on SQL Server and opening a new query for the group.  I’ll walk you through…

New Post: Recompile Monitoring using XEvents

Recompiles can be a hidden bottleneck on your server, and it may not be too obvious.  I should know, it happened to me.  Unfortunately for me, that was before I was a proponent of tracing or using XEvents to watch for recompiles.  Fortunately for you, the experience changed me, and I enjoy sharing my experiences. What happened to me was a proc that was called WAY too often was taking too long…

I/O Baseline Comparison – Idera Diagnostic Manager

What servers need the most attention when it comes to I/O?  When I increase the memory on a server what effect does it have on I/O?  What was it before the change and what was it afterwards? These are tough questions, and I have a script that I hope will help you out. Most monitoring tools, including Idera, are great at looking at what a single server is doing right now, but not so much with…

Blog Post: Wait Stats Baseline Comparison - Idera DM - @Idera_Software

Database servers have to wait on different resources, and we make changes to try to reduce those waits.  This could be anything from increasing memory, tuning a query, moving to different hardware, changing indexes, etc..  All of these things should reduce waits, but how effective were they? The trick is to know what things looked like before so you have something to compare it to.  If you know…

New Post: Shrinking Database Log Files #entrylevel #sqlpass #iwanttoshare

Files in SQL Server need to grow as the database grows, and in very specific circumstances need to be shrunk as well with additional maintenance.  This is quite a bit different between log files and data files, so I created a separate post for Shrinking Database Data Files. When should you shrink log files First, if a large process that will never happen again caused your log file to grow to a…

Virtual Log Files (VLFs) split a physical database log file into smaller segments, which are required for how log files work in the background.  These are created automatically, but automatically doesn’t always mean perfect.  Here’s a practical view of what you need to know and how you can set them up properly. What VLFs Do My initial view of how a transaction log worked was that each change was…

TempDB Excessive Memory Usage Example

I see TempDB using more memory than I feel it should and found a way to resolve it.  Previously I dove into technical details and questioning what I saw in my post TempDB Memory Leak? which is great for proving and debugging an issue.  Now I want to step back and talk about it from a practical perspective. To keep it practical, I’m going to walk through a single server, step-by-step.  What I saw…

New Post: Tim Ford's Entry-Level Content Challenge #entrylevel #sqlpass

All bloggers started as beginners.  We picked things up, learned them, and got to the point where we wanted to start sharing our knowledge.  The problem is that most of us moved on to talking about advanced topics and left people to fend for themselves on the difficult entry-level learning curve.  My blog, Simple SQL Server, was actually created with me saying I was going to fill that gap, right…

Querying Deadlocks From System_Health XEvent

It’s easy to query XEvents to see some of the basic info in deadlocks, including the system_health session which is already capturing this information by default. This script will, by default, read from system_health.  However, change the name of one parameter at the start to have it read from any active session.  I pull the file name automatically, so the session has to be active to avoid many…

Shrinking Database Data Files

Normal DBAs will sum up shrinking a database data file in one word: NO! I’ll talk to you about this subject because I’m not normal.  Still, my goal is that you’ll go from “why can’t I shrink my files” to “I don’t want to shrink my files.” Truth be told, even when I HAVE to shrink files, I don’t want to. Basically, you needed the space at one point in time, so you’ll probably need it again. …

SQL Saturday Cleveland #SQLSat473

On February 6, 2016, Cleveland is hosting a free training event for SQL Server.  This has a lot of the great stuff from the big, paid events, and skips some of the negatives. There’s a great team from the North Ohio SQL Server Users Group that took on a very difficult task to put all of this together.  They hand selected every presentation, being forced to turn away over half of the abstracts and…

I found a bug where I’m seeing TempDB use more memory than it should.  While I’m almost certain that the issue is in SQL Server and not my query, especially since I saw results in my workaround, I wanted to put this out here and discuss the issue before opening a connect item. Querying the Buffer Pool I have a query to show me what’s in the buffer pool for the entire server, with my newest…

Query the Buffer Pool

DBAs are known for asking for more memory, but often can’t say what’s in memory. IF OBJECT_ID('TempDB..#BufferSummary') IS NOT NULL BEGIN DROP TABLE #BufferSummary END IF OBJECT_ID('TempDB..#BufferPool') IS NOT NULL BEGIN DROP TABLE #BufferPool END CREATE TABLE #BufferPool ( Cached_MB Int , Database_Name SysName , Schema_Name SysName NULL , Object_Name SysName NULL , Index_ID Int NULL ,…