Last night I got back from our third Quest Database Management Customer Advisory Board. I can’t stress strongly enough how valuable these advisory boards can be. Our previous CABs had a heavy focus on Microsoft SQL Server, whereas this time around we aimed for the DBA Manager level with cross-platform responsibilities. This blog you are reading, as well as our community initiatives in general, are a direct result of our previous Customer Advisory Board meetings. In those we heard loud and clear the refrain of ‘help us get more value out of your tools; educate us on them; unlock functionality we don’t know about already; help us learn new platforms; help us interact with our people in the community.’ We’re pretty proud of what we’ve achieved there, but we know we have a long way to go. With this board we showed some of what we have planned over the next year with our educational and community initiatives, and the response was good, as well as giving us some ideas of how to make them better. There’s a real need for a one-stop shop portal for all our initiatives; right now we are a little dispersed, and this causes you some pain. We’ll be addressing that soon.
Also, we were very excited to hear that our customers want a way to get intelligence out of the data that our tools generate and store in repositories. We have some technology at Quest, which should allow us to deliver a proof of concept to our board within a week or so. We’ll have them test it, develop use cases, and hopefully validate the approach. If that works out, then from the CAB to delivering a solution to a considerable pain point will be extremely fast. And, if the feedback comes back negatively, that they wouldn’t use the solution, then we’ll have saved ourselves a whole host of time and effort.
That’s the power of a CAB right there.
This feature has been around for awhile in the Toad product family (SQL Server, DB2, MySQL, and Data Analysts), however it was upgraded for Toad for SQL Server, version 5.0.
Here’s a quick run-down:
Open the Editor
Load up your T-SQL and get ready to have fun. You will need to activate the ‘Group Execute’ panel by mouse-right-clicking in the editor and toggling ‘on’ the ‘Show Group Execute’ item at the bottom of the popup list. Then you can click ‘Enable’ in the Group Execute panel and select which servers you want your script executed against.
Select Your Servers (and Optionally Databases)
Database Level Granularity
You can now optionally define at the database level for each server where you want your scripts executed.
Run the script.
You get a ‘nanny’ warning. I advise you do NOT click the ‘hide’ disclaimer on this popup. At some point you will get distracted and try to truncate logs or drop a table somewhere and accidentally do it against multiple sources.
Insurance Worth Keeping
Review the Results
The results panel will show one line per server/database. Selecting a line brings up the data grid hosting the results for that selected server/database.
Merge the Results Into a Single Grid or Report
See All the Results Merged Together in a Single Set
Voila!
The data grids are extremely configurable. Once you have all the data in a single set, you can choose to group by server or database, hide fields, add aggregate functions like Sum or Average, and of course export to the format you need (like MS Access MDB for @BrentO!)
We are very pleased to announce the availability of Toad for SQL Server 5.0. This release has some new features and several enhancements to current features, including:
SQL Azure Support – Connect to SQL Azure and utilize the same Toad features as if it were an on-premise SQL Server database
Debug Tracing – generate a trace on a piece of code and replay it back later to determine what actually happenedduring execution
Database Administration Object Explorer – enhanced to provide the ability to filter indexes in the Create/Alter Index window and allows you to postpone any Create/Create or Llike/Alter action
Collaboration – provides a direct connection to RSS and Twitter feeds, including any Toad community postings
Query Development – includes productivity-enhancing features for code completion, including object multi-select, the ability to resize the code completion box and parameter hints
Group Execute – execute a SQL script against each specific database within each instance and merge the results of a group executon into a single result set
Automation – Import/Export templates allow for the use of a default connection or the connection saved to the Import/Export template
Project Manager – open a new project or save or save a project using buttons on the Project Manager toolbar
To evaluate this new release of Toad for SQL Server, please visit our dedicated product page.
In this post I’ll finish the conversation with an overview of Toad’s Compare and Synch feature set.
For your reading enjoyment and my time constraints and sanity, I will show the Server Compare in a step-by-step manner, and then show the Schema and Data compares as an ‘end result.’ The process for each of the three compares is similar, and once you have grasped the wizard conceptually for one, you should be good to go for the others.
Compare and Synch in a Nutshell
Select Source and Target
Do the compare
Review the results
Select each difference for desired synchronization
Synchronize SetA to SetB or vice versa
Review Script
Execute immediately via Toad or save script externally
Server Compares
To get started you will obviously need to be able to connect to the two servers in question. You will need to use an Administrator-level account for obvious reasons. For example a lower level account would lack the privileges necessary to compare logins between two different servers.
Provide Connection Information for the Compare
Take notice to the ‘Offline Snapshot’ option for the comparison source or target. Once you have performed a server compare, you are able to save the characteristics of each server to a local file. That file can then be used as a ‘point-in-time’ reference point to the server. This could be useful for administrators that would like to keep track of their servers as they upgrade them from one release or patch set to the next.
After the report is generated, the results are then available for review. Missing, different, and equal properties are displayed by default, although you can toggle each category on or off using the Server Compare toolbar. For this example I have selected only the ‘Different’ category.
Select a property, see the difference, view the Synch script for either direction
The Synchronization is Optional!
If you are just looking for a reporting tool, then this will work fill that need as well. However, if you do need to generate change scripts, then continue reading.
Synch SetA to SetB or go the opposite direction
Once the script is generated, it is up to you to validate it for your task.
Read it, review it, run it, or save it for later!
Schema Compare
This feature unfortunately does not get off to a very strong start. The name is misleading as the feature actually does a DATABASE compare. You can compare databases from different servers or from the same server. The offline snapshot technology is also available here.
The Compare Report is available in multiple formats.
View Differences Side By Side or View the Synch Script for each object
Toad does validate the synch scripts and will warn you if the possibility of data loss occurs. For example this difference report shows a column of length 50 in one database and of 25 in another. For Toad to synch A to B, you would lose upwards of 25 characters on that column.
Script warnings alert you to possible data loss. Remember to validate all scripts!
Compare Data
This may be the most interesting of the compare features. This technology allows a SQL user to compare data between one set of tables to another set. The tables can reside in any database on any server. The tables do not need to share common table or column names. Of course it is easier to setup the compare if the names are the same and if they share common primary keys.
The tool allows you to view table differences side by side at the row and/or column level. You can choose to compare all the data or use a WHERE clause to limit the data being processed. The technology is fairly robust
but will consume quite a bit of PC resources. I have successfully ran a two million to two million row table compare in several minutes. As with all software programs, be careful what you ask for!
In a previous post we explained some of the challenges associated with monitoring CPU utilization on a VM. One of the most significant new features in Spotlight on SQL Server Enterprise version 7.0 is the ability to provide further insight into VMWare’s impact on your SQL Server’s. I’d like to explain the feature in greater detail below:
SoSSE is not designed to be a full blown VMWare monitoring package. Quest has one of those (it’s called vFoglight) if the you’re interested. SoSSE is hoping to provide you with enough information about your virtual environment so that you can rule in, or out, VMWare as the cause of your performance issue. By showing a you the amount of resource utilization that ESX is “stealing” from your database server, you can immediately know whether or not your performance issue is caused by your SQL Server instance, or is caused by VMWare taking your resources. Spotlight on SQL Server Enterprise can now provide full visibility into the entire OS stack that the database instance is running on. SQL Server -> Windows OS -> VMWare ESX.
This is exactly what DBA’s have been needing since VM’s became prevalent, the ability to know whether a problem is theirs to troubleshoot, or whether they are wasting their time looking into an issue that is caused by some other machine. Obviously with any metric showing overhead, you want the value to be as low as possible. SoSSE can raise alarms, if you’d like, whenever this metric exceeds a user definable percentage.
In addition to this single homepage metric, SoSSE also now has two additional drilldowns to provide more detailed information. The Virtual CPU drilldown shows a historical graph showing CPU % (idle and used) as well as the CPU that has been stolen by ESX% (in red). In addition to CPU %, this drill down also shows the MHz of CPU that a VM has consumed. We discussed in the last post how critical it is to analyze these two metrics together!
If you determine that VMWare is taking resources from your VM, you can also view a list of VM’s managed by ESX so that you can view which VM is taking your resources. You can sort this list by host to view all VM’s you are sharing an ESX server with, and then see how much (in GHZ) each VM is consuming.
We hope you find this feature useful and, as always, would love to hear your feedback!
Whether we like it or not (I’m not going to approach that political battle), virtualization is becoming a mainstay in not only our development environments, but also production. Whether or not you agree with virtualizing production servers, you are eventually going to have to manage and tune them, either at your current job or your next. I want to take this opportunity to explain and show the best way to analyze CPU utilization in a virtual environment. The examples we show here are using VMWare vSphere 4 but the concepts apply regardless of your virtual platform.
Historically, when we look at a physical machine, the simple metric of CPU % Used is a pretty good measure of how busy a SQL Server is. A server that is at one time showing 20% CPU utilization and then at another time is showing 80% CPU utilization is generating 4X as much work during the later time period. In an operating system that is being hosted by a VM, we lose the luxury of knowing exactly how much CPU horsepower we have at any given time. The are a few factors that attribute to this “grey area”:
A VMWare admin has the ability to set an upper limit on the amount of CPU that is available to your VM. They can also set a reservation to guarantee an amount of CPU to your VM.
I will not see this limit in My Computer -> Properties nor will I see any representation of this limit in task manager or perfmon. Let’s assume that throughout the morning I have 2GHz available to my VM and am showing 20% CPU utilization. Later in the afternoon, my VMWare admin needs to free up resources so they put a 1 GHz cap on my available CPU. Now the exact same workload will show 40% CPU utilization. Nothing has changed on the OS or in my SQL Server workload, yet I am showing twice the CPU %. See where this gets confusing?
Even if the VMWare admin hasn’t set any resource cap on your VM’s available CPU, the ESX host could simply become overloaded. Let’s say an ESX host has 8GHz of total processing power, and that host has 5 VM’s running on it. Normally each VM uses about 1GHz of processing power, but all of a sudden, each VM needs 2GHz. Like fitting 10 pounds of feathers into a 5 pound bag, something has to give. What gives, is that ESX has to dynamically scale down each VM’s available CPU to account for the increased workload. As a result, you may see 80% CPU utilization when looking at perfmon or task manager, but you have no idea what that 100% is of.
A virtual machine may not be tied to a single ESX host. For DR or performance reasons, a VMWare administrator can move your VM from ESX host to ESX host without you knowing. These ESX hosts also need not offer the same performance as one another. You could be chugging along just fine during the morning with 4 GHz of processing power, and then in the afternoon be switched to an ESX host with 3 GHz of processing power. Not only did you not know that this occured, but your VM’s CPU % will go up, even though the workload is unchanged.
Because of this, it is absolutely critical that you not simply look at CPU % as a measure of how busy SQL Server is or how much CPU it is using. Percentages are always relative to a ceiling, and when that ceiling can move up or down at will (or whenever a VMWare admin decides that your ceiling is too high), the percentage itself loses meaning. CPU% analyzed in conjunction with GHz used will allow you to paint the full picture of a VM’s CPU requirements. Unfortunately, this data is not available by looking purely at the OS. You will need metrics from the virtual layer as well. That data is readily available in the built in VMWare client tools (vSphere), but you’ll either need to have access to the ESX or vCenter instance to view them, or a tight relationship with the VMWare admin who can send them to you.
For a better way to have this information at your fingertips, stay tuned…
That’s right folks! Toad for SQL Server has been recognized AGAIN as the best development tool for SQL Server. In a previous post I talked about Toad’s IntelliSense and Group Execute features.
Let’s continue the conversation now with a quick introduction on our SQL Tuning feature.
SQL Optimization
SQL tuning, SQL optimization, SQL plan evaluation, SQL-go-faster: pick your poison, we just want to make your T-SQL more efficient and improve it’s performance profile.
Toad makes this task much more enjoyable. You can concentrate on getting your query ‘right’ in terms of it returning the correct data. Toad then destructs the query and re-builds it using different syntax methods to find the optimal plan.
Toad then gives you the option of testing one or more of these alternatives so you can choose the one best fit for your application.
While in Toad, you can just hit the ‘Optimize the Current Statement’ button, and this will launch you into the Optimizer window.
Help is Just a Click Away!
Tuning Step 1: Evaluate the current Plan and Stats
Tuning Step 2: Generate Alternatives
Tuning Step 3: Execute Selected Alternatives Tuning Step 4: Evaluate Runtime Stats Tuning Step 5 (Optional): Suggest and Test Indexes
Scale Test Your Alternatives
With another click, you can also put your queries under load to see which one passes muster. For example, your query might run fine for a single user, but start to break down once you have 10 concurrent sessions running it continuously over a period of time.
Remember the last day-long virtual training event I did with Kevin Kline (Blog – @KEKline)? The one where I dressed up like Dr. Horrible and played doctor with your DMVs?
We’re doing another one! This time it’s Wednesday, July 21st, and we’re adding another great presenter – Buck Woody (Blog – @BuckWoody)! He’s Microsoft’s real world DBA, and he has the awesome distinction of being voted 3 of the top 10 sessions at last year’s PASS Summit. This promises to be a killer event.
For those of you who have been living under a rock for the past decade, Toad is one of the most popular database administration and development tools for the Oracle RDBMS. It just so happens that Toad is also available for Microsoft’s SQL Server platform.
This blog serves as a quick introduction to Toad for SQL Server for those who may have just heard about it. It seems that many folks find Toad extremely powerful for SQL Server development and administration, as it was just named for the second consecutive year, ‘Best of TechEd‘ in the ‘Database Development’ category by Windows IT Pro® and SQL Server Magazine®.
In the summary for the award announcement, Toad was described as the ‘Swiss Army knife’ for database tools, and had it’s features for Intellisense, Group Execute, SQL Tuning, and Compares singled out as reasons for the award.
I want to spend a few minutes going over these features in detail. If you like what you see, then you can try out the tool for yourself by downloading a 30 day trial from Quest.com.
The Swiss Army Knife of Database Tools
It’s hard to detail everything Toad does, so we often use this comparison. With Toad you get an extremely powerful editor and object browser. These main ‘blades’ are supplemented with a dozen or more utilities to help the SQL Server professional get the job done. Let’s look at a few in detail.
IntelliSense
The easiest way to describe this feature is probably to show it. But to put it into words, it can be described as technology in the editor that attempts to automatically complete your keystrokes. Here’s the most basic use of the tool. Start typing and let Toad pop-up a list of objects or calls that might satisfy your query/script.
In this case I had AdventureWorks set as my current database and asked for a list of tables that belonged to the ‘Person’ schema. I can now click into that object. I could also start typing and use the CTRL+Period keystroke to have Toad auto-complete the object if possible.
Here’s another example: Let’s say I want to make a call to a SQL command, i can mouse-right-click and ‘Snippet – Insert a Snippet.’ Once you get to your call, you can then use SHIFT+F1 on the command to bring up the documentation for that snippet.
Once you’ve finished your masterpiece query or script, you can now execute that in Toad with F5. Or, you could execute that set of code against multiple databases.
Note: SSMS also has IntelliSense features, but only works while connected to SS2008. Toad’s IntelliSense works with SQL 2000, 2005, & 2008
Group Execute
This feature does exactly what it sounds like – allows you to take one or more commands and issue them concurrently against multiple servers.
I think it goes without saying that this feature is both powerful and dangerous – as are most of our favorite technologies. You do get a ‘Are you sure’ prompt before any of your scripts are executed against multiple servers.
More to come
I’d rather keep this in blog format than do an entire tech brief on Toad, so we’ll stop here and pick this discussion back up later in Part 2.
We’re getting together on Wednesday night at 9pm at the Bourbon Street Blues Company smack in the middle of the action. (If you can call a gathering of thousands of geeks “action.”)
