As a DBA, we’re often asked to troubleshoot performance issues for stored procedures. One of the most common tools at our disposal is the query execution plan cached in memory by SQL Server. Once we have the query plan, we can dissect what SQL Server is doing and hopefully find some places for improvement. Grabbing the actual XML query plan for a stored procedure from the cache is fairly easy using the following query. From this point, we can open the XML query plan in Management Studio or Plan Explorer to start our investigation. But what happens if SQL Server...
Continue reading...PowerShell
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 #39 – Use Powershell to Restore a Database on a Different Server
T-SQL Tuesday – This month’s party is hosted by Wayne Sheffield (blog|twitter), and the topic is about Powershell and how to use it for anything SQL Server. With that challenge, I’d like to share a script I’ve written that takes a backup file from one server, copies to another server, and and then restores it. That may sound pretty easy, but I’ve added in a few requirements to the restore. Here’s the scenario: We have two SQL Servers, one production (TRON2R2PROD) and one test (TRON3R2TEST), and we have one user database (AdventureWorks2008R2) on each of the production and test servers. The...
Continue reading...Use Powershell to Manage Idera SQL Diagnostic Manager
Idera SQL Diagnostic Manager (or as I call it “SQLdm”) is a great monitoring and performance tuning tool that I was introduced to a few years ago. There are no server-side agents, so it’s a very lightweight tool and good for monitoring all SQL Servers in your environment. The GUI is great for managing servers except when I need to put all monitored servers in maintenance mode. If I’m only monitoring a few servers, it’s not that big of a deal. However, if I’m monitoring a hundred servers, then using the GUI to place them all in maintenance mode could...
Continue reading...
