Starting with SQL Server 2005, Microsoft introduced Dynamic Management Views to help DBAs see what’s going inside of SQL Server. One of my favorite is the sys.dm_db_index_usage_stats. It collects statistics for different types of index operations and the timestamp at which it was last performed. All the DMVs are great tools; however, they all have the same drawback. They are memory resident, which means the statistics are reset any time the SQL Server service is restarted, the database is attached/detached, or it’s restored. The best way to avoid losing this valuable data is to store it in a persistent table. ...
Continue reading...Patrick Keisler
Which SQL Instance is Killing Your CPU?
You have 5 instances running. How do you know which one is killing your CPU? As you can see from Windows Task Manager, you have several instances of SQL installed and one of them is using nearly half of the CPU. To figure this out, you’ll need to know the PID (Process ID) number. To view the PID number in Windows Task Manager, from the menu select View, then Select Columns, and then check the box for PID. For our example, it’s Process ID 1212 that’s using nearly 50% of our CPU. So how do you correlate that PID...
Continue reading...Grant Execute Permission on All Stored Procedures
Right out of the box, SQL Server makes it pretty easy to grant SELECT, INSERT, UPDATE, and DELETE to all user tables. That’s accomplished by using the built-in db_datareader (SELECT) and db_datawriter (INSERT, UPDATE, and DELETE) database roles in every user database. Any user you add to those database roles will be granted those permissions. But what if you want to grant EXECUTE permission to all of the user stored procedures. Where’s the built-in database role for that? Nowhere to be found. Oh you can create a database role, add users to that role, and grant EXECUTE permission to that...
Continue reading...How to Make SQL Server Listen on Multiple Ports
Have you ever needed an instance of SQL Server to listen on multiple TCP/IP port numbers but didn’t know how to implement it? Well here’s a quick step-by-step guide that will work for SQL Server 2005, 2008, and 2012. Open SQL Server Configuration Manager. On the left hand side, expand SQL Server Network Configuration. Select your instance name. On the right hand side, right click on TCP/IP and select Properties. Click the IP Addresses tab. Scroll down to the IPALL section. Make sure the TCP Dynamic Ports field is empty. In the TCP Port field, enter each port number separated...
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...
