Jive System Container

I wrote an article a while ago that discussed how to interpret CPU utilization in a virtualized environment and the caveats that must be considered when diagnosing SQL Server performance on a VM.  Here is a link back to that post.

Similar complexities arise when discussing memory utilization on a guest VM.  On a physical server, if we look at the system properties and see 8GB of physical memory, we can be certain that we have 8GB of memory to play around with.  Only when the OS requires more than 8GB of memory, does swaping memory to disk (paging) need to occur.  In a virtualized environment, we can no longer be sure that the physical memory that we are supposed to have, is actually available to my operating system.  To create a simple example, if we have one VMWare ESX host with 4GB of physical memory, and two VM guests, each allocated to use 4GB of memory, something must give.  ESX must step in and decide how physical memory is going to be delivered to each of its gusts.  The two main processes that may occur that can impact the VM guest's memory allocation are Memory Ballooning and Hypervisor Swapping.  I will explain each below:

Memory Balloning

On each VM that is being managed by VMWare ESX, there is driver installed on each guest OS that is referred to as the balloon driver.  This driver communicates with the ESX host through a private communication channel.  If the ESX host determines that it is being starved of memory, it will tell the balloon driver to artificially expand its memory utilization on the guest OS, thereby forcing the OS to use less memory. This may result in memory paging to disk, due to less physical memory being available.  Once the balloon driver claims its memory, the ESX host can then go and reclaim it since the guest OS no longer has access to it.

Hypervisor Swapping

If the Ballooning process is insufficient, ESX will step in as a last resort and swap (at random) some of the guest OS's memory pages to a disk device on the ESX server.  A guest specific swap file is created upon startup, and is used during this process.  Significant performance implications could exist in these scenarios depending on the speed of the disk that these swap files are being placed on.

For a thorough discussion of both of these process along with a wealth of additional information, the following white paper provided by VMWare is an excellent read.

Bottom line, on a VMWare guest, do not always assume that just because you see 8GB of physical memory as displayed in the system properties, that you actually have access to all of that memory.  Making this assumption could have you chasing your tail trying to diagnose a performance issue that is occurring simply because of an over provisioned ESX server.

We’re really lucky at Quest to have some very smart R+D talent. Our head of R+D in Melbourne, Australia, came to the Bay Area last week for the Strata Conference and brought a couple of his top people. I got the chance to talk with them about what they’re up to around emerging roles and technologies.

by Vickie Farrell

I just read yet another blog post telling us that implementing an end user  self-service BI tool like WebFocus or Spotfire automatically increases data quality, enables predictive insights and faster decisions, and implies that people are doing much more insightful analysis than most of them really are.

What the “BI as a predictive tool” section describes is not predictive analysis at all, but standard reporting of what has already happened. Useful, but not predictive.

I have found that the majority of people who say they do ad hoc query are actually using parameterized reports. True, end users are serving themselves, but what they are doing is hardly ad hoc. In most cases, what end users can do for themselves is chart and format data that has been cleansed and ETL’d for them.

Let’s not get carried away. Web-based interfaces, drop-down lists and wizards may make query and reporting easier, and they’re a valuable piece of the solution because of that, but they do not magically enable predictive analysis, ad hoc query and the ability to perform complex functions like demand forecasting and yield management.

Make no mistake, the ability to do predictive analysis, run ad hoc queries, detect and prevent fraud in process via real time analytics, integrating data from multiple incompatible sources, some external to your organization, with semantic reconciliation via an abstraction layer *does* exist.  But to do it requires more than turning end users loose with an easy-to-use web-based query tool.

As Jill Dyché points out in her January 4 “Anti-Predictions for 2011” blog post:

5. No one will be willing to shine a bright light on the fact that the data on their enterprise data warehouse isn’t integrated. Just because data is co-located on a single platform doesn’t mean that data is integrated. This is the dirty little secret of enterprise data warehouses that no one wants you to know.

Bingo. Data coming from multiple sources has to be integrated in a meaningful way before end users can use it for analysis. Of course, this can be done manually, as it has been for years. A second issue is the data quality. The data has to be trusted as a, what one of my former customers likes to call, “certified version of the truth.”

I have worked with organizations who are working to provision data for end users, ensuring needed level of quality, access, latency and integration. Once that has been accomplished, end users can be turned loose. I have seen these best practices really pay off in providing insightful analysis by business users themselves. And new technologies like Hadoop and MapReduce, in-memory processing, mobile BI and others can be applied to the established infrastructure.

by Christian Hasker

We love technology innovations. Technology innovations provide new market opportunities by exposing new pains that require new salves. Thank you Microsoft Azure!

We are big fans of Azure here at Quest, and we are fortunate, as a two-time ISV Partner of the Year, to be in a position where we work very closely with the Microsoft product teams. When we got an early look at Azure, and then started to port some of our existing products to the platform, and then started to build new products we suddenly found that we had a lot of pain internally around understanding our Microsoft Azure utilization and billing.

1.) Calculating how much my next bill is going to be is like sticking my finger in the breeze. How much do I budget?
2.) All our developers are spinning up new Azure accounts! How can I get a single view across multiple accounts?
3.) Just getting my bills out of the Microsoft portal is burning up a lot of time. There has to be an easier way!
4.) What am I actually utilizing? Am I using everything I am paying for?

What do we do at Quest? Why we build software of course! So, although it’s a bit of a departure for us, we decided to build a product to help solve our internal pains. We are calling it Project Cloudscope, and we’re making it available for public download as well. If you are using Azure this is likely to become a must-have tool for you so we invite you to check it out and provide us with your feedback. We’ll be continually updating the utility so if it’s missing features you need let us know.

The T-SQL Tuning feature of Toad for SQL Server can rewrite your T-SQL statements into potentially faster variations, and this video walks you through the steps.

How to output the additional information

• bcp "EXEC sp_server_info" queryout C:\output.csv -Sserver -T -c -t, -r\r\n

• EXEC master..xp_cmdshell 'bcp "EXEC sp_server_info" queryout C:\output.csv -Sserver -T -c -t, -r\r\n'

Additional information that you might want to capture

Configuration Info

• "EXEC master..xp_msver"
• "SELECT * FROM master.sys.configurations"

Security Info

• "SELECT loginname FROM master.dbo.syslogins" or "EXEC master..xp_loginconfig"
• "EXEC master.dbo.sp_helpsrvrolemember"

SQL Server Agent Info

• "EXEC msdb..sp_help_operator"

• "EXEC msdb..sp_help_job"
• "EXEC msdb..sp_help_jobstep @job_id='YourJobID"
• "EXEC msdb.dbo.sp_help_jobschedule @job_name='YourJobName'"
• "EXEC msdb.dbo.sp_help_schedule"
• "EXEC msdb..sp_help_alert"

Database Schema information and Scripts

• SELECT * FROM AdventureWorks.INFORMATION_SCHEMA.TABLES

To get the DLL scripts for stored procedures, views or functions, you can use:

• http://msdn.microsoft.com/en-us/library/ms187961.aspx
• http://msdn.microsoft.com/en-us/library/ms174365.aspx

Other methods of scripting metadata

• osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_server_info" -o "E:\output.txt"

• EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_who2" -o "E:\output.txt"'

• EXEC master..xp_cmdshell 'osql.exe -S YourServerName -E -Q "EXEC sp_who2" -o "E:\output.txt"'

We recently published a new brief outlining SharePlex for Oracle's support for the Oracle Exadata platform.  The brief also discusses best practices to successfully setup and utilize SharePlex for the latest Exadata environment.

Get your copy of the latest brief here.

Thanks - we look forward to any feedback you might have.

Load testing is an interesting topic.  Application load testing is what most people are familiar with.  They either use something like LoadRunner, or they ask half the company to ‘jump on the website and start clicking buttons.’

But what about the database?  In this case, what about SQL Server?  Will it be able to sustain the workload as it increases?  Will the performance SLAs hold?  Does the average response time for a transaction increase at a predictable rate as the user load goes up?

Quest sells a solution called Benchmark Factory for Databases.  This tool allows you to generate load tests and deploy them to Oracle, SQL Server, DB2, Sybase, and MySQL.  You can either run a synthetic test like the TPC-H or TPC-E, or you can actually replay activity that you have recorded in a trace.

Here is a quick example of replaying load for SQL Server 2008 R2.

Step 1: Profiler

Step 2: Run Your Application

As the application is running, all of the SQL and T-SQL calls will be recorded in the Profiler and stored in the table you created in step1.  Even in a small amount of time, you will have a lot of transactions.  For my example I ran in 5 minutes and captured over 90,000 calls.

Step 3: Stop Your Trace/Profiler Session

Step 4: Open Benchmark Factory for Databases

Step 5: Launch the Load Scenario Wizard

The Load Scenario Wizard will step you through the process of defining your replay test for SQL Server.

You may see this error message, just click through it and proceed.

Now enter your connection information.

Benchmark Factory will allow you to browse the databases, and will recognize the profiler table you created.

Benchmark Factory will now create a project based on the transactions recorded in your table.  If you want to filter out the activity to be replayed you can either:

Step 6 – Set Your Project Options

Now you have your Benchmark Factory project and you are ready to replay the recorded load on your database.  You have a few options to explore first.

How many users do you want running the transactions?

Let’s assume you recorded a single user running the application.  Now you want to see what will happen if 100 users are running the same load concurrently.  Just set the ‘Users’ value for your sceneario to ’100′.  You can also optionally have the number of executions go up from the default of ’1′.  This would allow you to run the test several times in one job.

You can also manipulate the latency for the transactions.  It will be default run the queries as captured and honor the timings.  However, if you want to enforce an absolute interarrival time between calls, you may do so.  You can artificially add keying and think times to simulate the users experience while they are running your application.  Removing all of the latencies will cause the project to run as fast as your SQL Server can respond to the queries.  It is critical that you define your test plan BEFORE you start jumping into the software to run your test.

Step 7 – Run Your Test

Step 8 – View the Results

Benchmark Factory stores the results in a local repository.  You can view key metrics such as maximum response time, bytes per second, transactions per second, and any optional counters you added in step 7.

The graphs can be exported as image documents for your own custom reports.  You can also export all of the data to an Excel spreadsheet.

Tips