I started as a SQL Server DBA in November of 2007 and attended my first PASS Summit in 2008. While there, I saw Jerome Halmans present on Advanced Troubleshooting with SQL Server Extended Events. It was one of my favorite presentations of the summit and I wanted to start using Extended Events in my environment. However, every time I tried to use them, the overhead of getting them set up was greater than their usefulness. I found myself using other troubleshooting tools that I was more familiar with.
Despite these difficulties, I remained interested in using Extended Events. Several months ago, I downloaded Jonathan Kehayias’ ( blog | twitter) SQL Server 2008 Extended Events SSMS Addin but couldn’t get any momentum going.
That brings me to my most useful feature in SQL Server 2012. This week’s Brent Ozar PLF newsletter contained a link to Bob Dorr’s article on SQL Server 2012: RML, XEvent Viewer and Distributed Replay. I just started playing with Extended Events in 2012 today and I am really excited about it.
They are now integrated into Management Studio, removing the manual scripting that was necessary to use them previously. The interface looks and feels a lot like SQL Profiler, but Extended Events has much less overhead than a Profiler trace. And they are one of Thomas LaRock’s ( blog | twitter ) top 3 things you should learn in SQL Server 2012.
I will be at the SQL Skills immersion event on internals and performance next week, but Extended Events is on the top of my list when I get back.
Central Management Server (CMS) allows a query to be executed against multiple instances of SQL Server at one time. It is a very useful tool for returning information about your environment and creating the same object on several instances. I will use one of the queries from my previous post in this example.
To set up CMS, open SQL Server Management Studio 2008 or later and click View and select Registered Servers. On the Registered Servers pane, right-click Central Management Servers and select Register Central Management Servers.
Enter a SQL Server name into the Server name field. You can test the connection using the Test button. Once you are satisfied, click Save. Your server will appear in the tree on the left.
Right-click the server name and select New Server Registration.
In the New Server Registration dialog, enter a server you want to add. After testing the connection, click Save. The registered server will appear under the Central Management Server. Repeat this for all of the servers you want to add. Also, note that you can create multiple levels in the CMS tree structure. In my environment, we have the clustered servers grouped together by both data center and environment. This allows queries to be executed against the entire environment as well as any subset of the instances.
To execute a query against multiple databases, right-click on the level in the tree you want to run against and select New Query.
A query window will open. You will notice that the status bar is pink and the lower left-hand corner will show Connected (n/n), displaying the number of instances you are connected to.
For this example, I am going to use the xp_fixeddrives query from my last post, but you can use CMS to execute any T-SQL query.
IMPORTANT NOTE: USE EXTREME CAUTION WHEN EXECUTING ANY INSERTS, UPDATES, DELETES, DROPS OR ANY OTHER POTENTIALLY DESTRUCTIVE QUERIES.
Once you click Execute, there is no going back. If you execute
DROP DATABASE master
against all of the instances in your environment, you could potentially drop every copy of master. Additionally, there is no way to control the order in which the servers return their results, so canceling a query may have unexpected results.
Once you’ve absorbed that, click execute. Your query results will return with one result set per instance. The first column in each row will always contain the instance the returning that row.
There are a few things I recommend when running CMS queries.
First, always declare a USE clause. This will prevent problems when running various system stored procedures against 2005 and 2008 systems at the same time.
Second, when running a SELECT statement, always use a column list. This will prevent problems if the number of columns returned has changed between versions of SQL Server.
Third, keep in mind CMS connects to each instance and executes your query, returning each result set individually. This means you have no control over the order the result sets return and cannot order the entire result set. If you code an ORDER BY in your query, each result set will be sorted. If I need to sort the entire result set, I will copy it into Excel and do the sorting there.
I use CMS on a daily basis to return information from my environment. It also makes creating and modifying code used in MSX/TSX jobs very simple. With a single execution I can push a new stored procedure out to every instance in my environment. Have fun playing with CMS to see what uses you can find for it.
I attended the SQL Server 2012 launch this past Thursday. Thanks to Dave Paulson, Ross LoForte, Ron Martin and the rest of the folks at Microsoft for putting on such a great event. Some of the highlights from the launch:
Ross’s demo of AlwaysOn
It was the first time Id seen it in action and it is impressive. I received answers to some questions about the listener, and I hope to get it up and running in a lab environment this week.
A better understanding of the ColumnStore index
Data warehousing is one of things on my learning list for this year, so the information Dave Paulson provided should come in handy.
Tempdb on a local drive
We are testing a lot of new products and platforms in our environment this year: VMWare, Microsoft’s Data Consolidation Appliance, and SQL Server 2012. This is another item to put on the list. I am looking forward to running performance comparisons of tempdb on our existing SAN versus a local drive.
Reminders of SQL Server 2008 features
Dave Paulson’s presentation on Mission Critical features contained a section on SQL Server 2008 features, including Policy-Based Management and Resource Governor. I have done some initial research into these features, but this will work to implement them this year.
The highlight of the event was the keynote given by Doug Laney of Gartner. The topic was infonomics, or methods of treating information as an asset. Having worked in information technology for 13 years with the last five in database administration, I understand the value of the data I work with. But I’d never given thought to that data as an asset on a balance sheet.
Laney talked about four ways to determine the value of information:
- Replacement value – How much would you have to pay to replace your data?
- Revenue generation – How does your data contribute to the bottom line?
- Market value – How much can you sell your data for on the open market?
- Ransom – How much would you pay if your data was held hostage?
It was an extremely thought-provoking presentation, and I’ve started conversations with my leadership about bringing these ideas into our organization. Gartner is releasing two white papers in the coming weeks, and I am looking forward to reading them. I will post more on the topic after reading.