Management Studio is great tool with almost everything you need right at your fingertips. Notice I said “almost”. I like to customize the toolbars to meet my needs of doing work every day. Because being a DBA means you’re busy all day long, and you don’t have time to hunt through all the menus or memorize a keystroke for a special function.

The very first customization I always make is to add the “Hide Results Pane” button to the SQL Editor toolbar. This option is found under the Window menu and acts a toggle button to either show or hide the results pane for the query editor.

In SQL Server 2012 Management Studio, from the menu, select Tools, Customize, and then select the Commands tab. Select the Toolbar radio button, and just to the right of that select SQL Editor from the dropdown menu. In the Controls section, you will see each command that corresponds to the button on the SQL Editor toolbar.

To add a button to this toolbar, click the “Add Command” button. From the Categories section select Window, then select “Show Results Pane” from the Commands section, and then click OK. Remember this is a toggle button, so depending on the display state of the results pane, the button with either say “Show Results Pane” or “Hide Results Pane”. When we’re adding the button in the customization wizard, it always shows up as “Show Results Pane”.

Now you should see the “Show Results Pane” button on the Controls list.

Make sure that control is selected and click the “Move Down” or “Move Up” button to reposition it on your toolbar. Once you have the button where you want it, then just click Close. Your toolbar has now been customized.

This will save you a few keystrokes and/or mouse clicks when you’re trying to find that one thing that’s buried in the Management Studio menus. And don’t worry about messing anything up, if you don’t like the layout of a toolbar, you can just click the Reset All button in the Customize wizard to reset the layout back to the default.

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. I typically store data like this within the msdb database, but you can store in any database. Below we have created a table DBA.IndexUsageStats to hold each snapshot of the data.

USE msdb;

GO

CREATESCHEMA dba AUTHORIZATIONdbo;

GO

CREATETABLE dba.IndexUsageStats

(

SQLStartTime DATETIMENULL,

CaptureTime DATETIMENULL,

DatabaseName NVARCHAR(128)NULL,

ObjectName NVARCHAR(128)NULL,

index_name NVARCHAR(128)NULL,

index_type_desc NVARCHAR(60)NULL,

index_is_unique BITNULL,

database_id SMALLINTNULL,

object_idINTNULL,

index_id INTNULL,

user_seeks BIGINTNULL,

user_scans BIGINTNULL,

user_lookups BIGINTNULL,

user_updates BIGINTNULL,

last_user_seek DATETIMENULL,

last_user_scan DATETIMENULL,

last_user_lookup DATETIMENULL,

last_user_update DATETIMENULL,

system_seeks BIGINTNULL,

system_scans BIGINTNULL,

system_lookups BIGINTNULL,

system_updates BIGINTNULL,

last_system_seek DATETIMENULL,

last_system_scan DATETIMENULL,

last_system_lookup DATETIMENULL,

last_system_update DATETIMENULL

);

GO

Next we need to create the collector. This query will pull the current data from the DMV and store it in our table.

DECLARE@CurrentStartTime ASDATETIME,

@PreviousStartTime ASDATETIME;

SELECT@CurrentStartTime = sqlserver_start_time

FROMmaster.sys.dm_os_sys_info;

USETestDatabase;

INSERT msdb.DBA.IndexUsageStats

SELECT@CurrentStartTime AS SQLStartTime,

CURRENT_TIMESTAMPASCaptureTime,

Db_name()AS DatabaseName,

Object_name(ius.object_id)AS ObjectName,

i.name ASIndexName,

i.type_desc ASIndexTypeDesc,

i.is_unique ASIndexIsUnique,

ius.*

FROMsys.dm_db_index_usage_statsius

INNERJOINsys.indexes i

ONius.object_id= i.object_id

ANDius.index_id =i.index_id

WHERE ius.database_id =Db_id()

ORDERBY ObjectName,

ius.index_id;

GO

The script can be scheduled with SQL Agent to run on a regular basis. I prefer a weekly schedule, but any recurring schedule is fine. If you have more than one user database, then you’ll need to run this script for each one.

In the script we capture a bit more than just the DMV data. For example:

SQLStartTime – The time when the SQL Server service started.

CaptureTime – The time when the script captured a snapshot of the DMV data.

Once you have let this job execute over a few weeks, you can then revisit the data and start seeing how your indexes are being utilized over a longer period of time. This query will aggregate the index statistics using the captured data.

USE msdb;

SELECTMin(SQLStartTime)AS SQLStartTime,

Max(CaptureTime)ASCaptureTime,

ObjectName,

IndexName,

IndexTypeDesc,

index_id,

Sum(user_seeks)ASuser_seeks,

Sum(user_scans)ASuser_scans,

Sum(user_lookups)AS user_lookups,

Sum(user_updates)AS user_updates

FROM DBA.IndexUsageStats

WHERE DatabaseName =‘TestDatabase’

GROUPBY ObjectName,

index_name,

index_type_desc,

index_id

ORDERBY ObjectName,

index_id;

GO

These samples below were taken over a four month period and clearly show how the indexes have been utilized. It even points out some indexes (IX_ItemCodeIDand IX_PurchasedBy) that may be nothing more than overhead and could be candidates for removal. These two indexes have not been used for any seeks or scans, but SQL still needs to update them every time DML code is applied to the Purchases table.

As you can see, the more data you have to draw a conclusion about your index usage, the better you can make tuning recommendations for each of them.

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 to the SQL instance, so you can start the performance investigation? There’s a couple of places you can find this information.

The first place is to look at the most recent SQL Error Log for each of your instances. One of the very first entries will be the PID value.

Server process ID is 1212.

The other place is to open the SQL Server Configuration Manager, select SQL Server Services from the left hand side, then right click on an instance and click Properties. Click the Service tab and you’ll see the PID value for that instance.

Both of these methods give show you which instance is our culprit. Now you can drill into that instance to find out why it’s killing your CPU.

I prefer to look for this value in the SQL Error Log because I already have Management Studio open ready to investigate the issue. Keep in mind the PID value will change every time your SQL instance is started.

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 role for each stored procedure that’s created. But that’s just not as efficient as the built-in roles, mainly because you must always remember to grant the permission to the role every time you create a new stored procedure.

Here’s an easier way to create a database role that’s more like the built-in roles.

This example granted EXECUTE permission to the dbo schema. Any stored procedures that are created in the dbo schema can be executed by users who are members of the db_execproc database role. You can grant the same permission to other schemas if needed. This concept is not going to be for everyone and every database, but if you’re already using the built-in database roles to manage permissions within a database then this will be a good option for you.

For more details on database roles and grant schema permissions, check out Books Online.

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.

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 by commas.

Click OK.A pop up message will appear stating the changes will not take effect until the service is restarted. Just click OK to close the message.In the SQL Server Configuration Manager, on the left hand side, select SQL Server Services.On the right hand side, right click the SQL Server service and select Restart.

After the restart is complete, SQL Server will be listening on all the port numbers you specified.The easiest way to verify this is to look in the SQL Server Errorlog.You will see one entry for each port number.