Skip navigation.

Pythian Group

Syndicate content
Official Pythian Blog - Love Your Data
Updated: 10 hours 27 min ago

Join Us For a Networking Event at UKOUG

Fri, 2014-12-05 09:25
UKOUG event photo

Ask not what you can do for your data. Ask what your data can do for you!

Join us for an informal networking event alongside Rittman Mead on Monday December 8th during UKOUG. We will be discussing how to leverage data to drive your organization’s success. Come meet with peers and industry experts, Mark Rittman and Jon Mead of Rittman Mead, and Marc Fielding and Christo Kutrovsky of Pythian. The networking event will take place at PanAm Bar and Restaurant in Liverpool from 6-8 PM, and will include drinks and light refreshments.

Please be sure to RSVP to the event here—we hope to see you there! Find more information about Pythian’s speaking sessions here.

Questions? Please contact Elliot Zissman, Director of Sales at zissman@pythian.com.

Categories: DBA Blogs

What Does “Backup Restore Throttle Speed” Wait Mean?

Thu, 2014-12-04 11:29

After migrating a 10g database to 11g, I asked the Application team to start their tests in order to validate that everything was working as expected. I decided to keep an eye on OEM’s Top Activity page while they were running the most mportant job. I already knew what kind of “colors” I would  find because I had checked its behavior in the former version. Suddenly, a strange kind of wait appeared on my screen: it was my first encounter with Backup Restore Throttle Speed.

 

OEM graph 2

I had never seen this wait before. It was listed in a user’s session so its name really confused me. No RMAN operations were running at that time. FRA was almost empty. I checked Oracle’s documentation and My Oracle Support. I found nothing but one Community post from 24-SEP-2013 with no conclusions. In the meantime, the job ended and I got the confirmation that everything was well, even faster than in the old version. Weird, very weird. It was time to review the PL/SQL code.

After reading lots of lines, a function inside the package caught my attention:

Sleep (l_master_rec.QY_FIRST_WAIT_MIN * 60);

Since the job was using a log table to keep track of its execution, I was able to match the wait time with this function pretty quickly. This code was inside the function’s DDL:

for i in 1 .. trunc( seconds_to_sleep/600 )
loop
sys.DBMS_BACKUP_RESTORE.SLEEP( 600 );
end loop;
sys.DBMS_BACKUP_RESTORE.SLEEP( seconds_to_sleep-trunc(seconds_to_sleep/
600)*600 );

Finally I found the reason for this wait (and the explanation for its backup/restore related name): DBMS_BACKUP_RESTORE.SLEEP. As described in MOS note “How to Suspend Code Execution In a PL/SQL Application (Doc ID 1296382.1)”, the package was used to pause job’s execution while waiting for another task to be finished.

Lastly, it’s worth noting that OEM did not graph this wait on the 10g database but it was always there.

Categories: DBA Blogs

Watch: Hadoop vs. Cassandra

Mon, 2014-12-01 10:53

Every data platform has its value, and deciding which one will work best for your big data objectives can be tricky—Alex Gorbachev, Oracle ACE Director, Cloudera Champion of Big Data, and Chief Technology Officer at Pythian, has recorded a series of videos comparing the various big data platforms and presents use cases to help you identify which ones will best suit your needs.

“Hadoop is generally deployed in a single data center, multi-RAC deployment, but they’re all reasonably geographically co-located with each other,” Alex explains. Cassandra on the other hand, “…is frequently deployed in a very distributed fashion… Somewhere in Asia, Europe, North America… So you end up with a very fault-tolerant environment.” Learn how the two platforms compare by watching Alex’s video Hadoop vs. Cassandra.

Note: You may recognize this series, which was originally filmed back in 2013. After receiving feedback from our viewers that the content was great, but the video and sound quality were poor, we listened and re-shot the series.

Find the rest of the series here

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Big Data expertise.

Categories: DBA Blogs

Log Buffer #399, A Carnival of the Vanities for DBAs

Fri, 2014-11-28 09:52

This Log Buffer Edition brings some freshly picked blog posts right out of the gardens of Oracle, SQL Server and MySQL.

Oracle:

Suppose you have a process A that you want to run faster. This process doesn’t commit (much), so it doesn’t wait on log file sync. However, there is another multi-threaded process, B, that commits very frequently, and spends a lot of time on “log file sync”.

New Solaris 11 CPU package to install and track CVE security fixes.

Upgrade/Migrate/Consolidate to Oracle 12c and Parallel Multitenant Upgrade Internals.

CVE metadata in Solaris IPS packages for improved Compliance reporting.

Why Your Supply Chain Needs Science?

SQL Server:

SQL Server expert David Poole discusses how teams can work together and share templates in Management Studio.

Explaining The SQL Server 2014 Analysis Services Data Mining Model Lift Chart.

By this stage, you should be familiar with the basics of SQL Server indexes. We’ve discussed what an Index actually is, as well as some of the most common types you’re likely to encounter. Now that we’ve seen some simple examples of how Indexes can be useful, we’re going to delve deeper into nonclustered indexes, as we’ll see how they can improve the performance of more complex queries.

The Project Deployment Model introduced in SSIS 2012 speeds up the deployment of database projects in which there may be hundreds of SSIS packages per project. Not only that, but deployments can be configured differently for each environments such as test and staging, and there are now ways of monitoring the status and performance of packages and of versioning the SSIS Catalog.

A technique to deal with lack of metadata for stored procedures when used with SSIS.

MySQL:

How to mess up your data using ONE command in MySQL/Galera.

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

Nasty MySQL Replication Bugs that Affect Upgrade to 5.6.

(More) Secure local passwords in MySQL 5.6 and up.

Alternatives for chunking bulk deletes in common_schema.

Categories: DBA Blogs

Pythian at UKOUG 14

Tue, 2014-11-25 13:39

Will you be joining us at the UKOUG Conference and Exhibition in Liverpool, UK? Over 200 world-class speakers and industry experts will be in attendance, including some of our very own.

Michael Abbey, Oracle ACE and Team Lead at Pythian notes that Pythian’s attendance is important, not only for the company and its employees, but for the Oracle community as a whole. “Pythian’s presence at UKOUG this year is the next chapter in an ongoing participation in database and EIS technical events around the world,” Michael explains. “We pride ourselves on presence on all seven continents and our appearances in Liverpool are strategic to the worldwide user community, as are all the locations we frequent every year.The user group community is one of the fundamental building blocks for technical resources as they hone their skills to better serve their clients and the masses in general. Since its founding, Pythian has been a strategic and financial support organization feeding talent to many of the world’s largest technical shows.”

“You will see a number of presenters from the Pythian suite of experts including Oracle ACEs, Oracle ACE Directors, and members of the OakTable Network. We look forward to catching up with new acquaintances and rekindling existing relationships.” In the meantime, you can find their speaking sessions down below.

UPDATE: Join us for an informal networking event alongside Rittman Mead on Monday December 8th during UKOUG. We will be discussing how to leverage data to drive your organization’s success. Come meet with peers and industry experts, Mark Rittman and Jon Mead of Rittman Mead, and Marc Fielding and Christo Kutrovsky of Pythian. The networking event will take place at PanAm Bar and Restaurant in Liverpool from 6-8 PM, and will include drinks and light refreshments.

Please be sure to RSVP to the event—we hope to see you there!

 

RMAN: The Necessary Basics

Presented by Michael Abbey | Monday December 8, 2014 — 9:00-9:50 AM

This session, best suited for attendees just getting started with RMAN, the basic skills to write consistent backups and perform recovery activities from day one. Highlighted in this masterclass will be the following: RMAN architecture, using a catalog, backups to disk, backups to tape, recovery (complete and incomplete), database duplication, and tips and tricks.

About Michael: Michael Abbey is a seasoned and experienced presenter on the Oracle Database CORE technology. He first cut his teeth on V3 in 1986 and it has been a whirlwind of Oracle since then. Michael co-authored the first work in the Oracle Press series in 1994.

 

Why Use OVM for Oracle Database?
Presented by Francisco Munoz Alvarez | Monday December 8, 2014 — 5:00-5:50 PM

Vibrant session about OVM, that will explain how, when and why use this product for Virtualization. It will also give an overview of how Revera is currently using this product in NZ (Biggest OVM Farm in the ANZ region) and show benchmark results between Bare Metal, OVM and ESX concluding with some tips and showing the scalability and break point of load of the Virtualization solutions.

Come and discover the answers for the following questions:

  • Does an Oracle Database perform well on a virtualized environment?
  • What virtualization technology is more stable and allows an Oracle database to perform faster?
  • What is the performance difference between using a bare metal and a virtualized guest?
  • Is it safe to run a production database in a virtualized environment?

About Francisco:  Working out of Pythian’s Australian office in Macquarie Park, Francisco Munoz Alvarez is Vice President and Managing Director of Service Delivery in Asia Pacific, overseeing its regional expansion effort. Francisco previously served as Chief Technology Officer at Database Integrated Solutions Ltd and has more than two decades’ experience in Oracle databases. As President of the Chilean Oracle Users Group and founder of the Oracle Technology Network (OTN) tours in Latin America and Asia Pacific, he is best known for his evangelist work with the Oracle community.

 

Big Data with Exadata
Presented by Christo Kutrovsky | Tuesday December 9, 2014 — 5:30-6:20 PM

In this presentation, Oracle ACE Christo Kutrovsky will discuss common big data use cases and how they can be implemented efficiently with Exadata. Attendees will learn how Exadata actually delivers most of the benefits touted by newer big data technologies, and can often be the right platform for data scalability.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Measuring Performance in Oracle Solaris and Oracle Linux
Presented by Christo Kutrovsky | Wednesday December 10, 2014 —9:00-9:50 AM

You can’t improve what you can’t measure. If you want to get the most value from your database, you need to start with the basics: are you using your hardware and operating systems efficiently? Attend this session to learn how to measure system utilization in the Linux and Oracle Solaris operating systems and how to use this information for tuning and capacity planning.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Lessons Learned in Implementing Oracle Access Manager 11g with Forms, Reports, and Discoverer
Presented by Sudeep Raj and Maris Elsins | Wednesday December 10, 2014 — 12:30-1:20 PM

Support for SSO has ended in December 2011. To take advantage of the latest security enhancements, it’s always recommended for customers to upgrade their system to the latest and the greatest version of the product i.e OAM/OID 11g, very important to stay on the supported configurations. This session will give you an opportunity to understand how OAM 11g can be configured with Forms/Reports/Discoverer 11g, integration with external directory service like Microsoft AD and discuss about the upgrade considerations for customers planning to upgrade from 10g SSO/OID to OAM 11g and OID 11g.

About Sudeep: Sudeep Raj is a Team Lead/Oracle Applications Database Consultant at Pythian, managing a group of expert DBAs spread across the globe. With nearly a decade of experience as an Apps DBA, he has been involved in and led multiple Oracle E-Business Suite 11i/R12 implementations, maintenance, migration and upgrade projects. Sudeep Raj is an OCP certified professorial and holds a Bachelor of Engineering degree in Computer Science.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 

Optimizing and Simplifying Complex SQL with Advanced Grouping
Presented by Jared Still | Wednesday December 10, 2014 — 3:30-4:20 PM

This presentation will show how these features can be used to simplify SQL that was previously quite complex by reducing the amount of code needed and improving readability, and perhaps most importantly, greatly optimizing the performance of SQL statements.

About Jared: Jared Still is a Senior Database Consultant at Pythian. His experience includes working with Oracle databases beginning with version 7.0. While Oracle has expanded to encompass many aspects of the application environment, Jared’s focus has been on the database itself and related infrastructure.

 

Oracle RAC — Designing Applications for Scalability
Presented by Christo Kutrovsky | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Real Application Clusters (RAC) promises 100% transparent active-active clustering technology – true horizontal scaling, but does it work in all cases? This presentation explores the challenges with Oracle’s active-active solution and how to solve them from both database side and application side. Both conceptual design and highly practical solutions are explored.

About Christo: Christo Kutrovsky is an Oracle ACE in Pythian’s Advanced Technology Consulting Group. With a deep understanding of databases, application memory, and input/output interactions, he is an expert at optimizing the performance of the most complex infrastructures.  A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

 

Database as a Service on the Oracle Database Appliance Platform
Presented by Marc Fielding and Maris Elsins | Wednesday December 10, 2014 — 3:30-4:20 PM

Oracle Database Appliance provides a robust, highly-available, cost-effective, and surprisingly scalable platform for database as a service environment. By leveraging Oracle Enterprise Manager’s self-service features, databases can be provisioned on a self-service basis to a cluster of Oracle Database Appliance machines. Discover how multiple ODA devices can be managed together to provide both high availability and incremental, cost-effective scalability. Hear real-world lessons learned from successful database consolidation implementations.

About Marc: Marc Fielding is a passionate and creative problem solver, drawing on deep understanding of the full enterprise application stack to identify the root cause of problems, and to implement effective and sustainable solutions. He has extensive experience implementing Oracle’s engineered system portfolio, including leading one of the first enterprise Oracle Exadata implementations. Marc has a strong background in performance tuning and high availability.

About Maris: Recently awarded the Oracle ACE designation, Maris Elsins is a Lead Database Consultant at Pythian. He is a blogger and frequent speaker at many Oracle related conferences like Collaborate, UKOUG, and LVOUG where he is a board member. Maris is an exceptional trouble shooter and enjoys learning why things behave the way they do.

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Oracle expertise or read some of our Oracle-related blog posts.

Categories: DBA Blogs

Log Buffer #398, A Carnival of the Vanities for DBAs

Mon, 2014-11-24 09:10

This Log Buffer Edition covers some informative and interesting posts from Oracle, SQL Server and the MySQL.

Oracle:

If you’re not using Toad DBA Suite, it’s sometimes hard to find solutions. Somebody wanted to know how to find indexes that aren’t that aren’t indirect. Indirect indexes are those created for a primary key because a primary key column or set of columns are both not null and uniquely constrained. You can’t drop a unique index for a primary key without dropping the primary key constraint that indirectly created it.

At the NoCOUG fall conference at the eBay town hall in San Jose, we got a first-hand look at the workings of the most complex database environments in the world.

Is there a feature you would like added to Inventory or Inventory Items? Tired of logging Service Requests with Support to request product enhancements? Well those days are over. You can now submit Enhancement Requests (ER’s) for Logistics (Inventory) and/or Inventory Items (APC/PLM/PIM) directly in their respective Communities.

Oracle Database 12c : EXPAND_SQL_TEXT, APPROX_COUNT_DISTINCT, Session Sequences and Temporary Undo.

Integrating Cordova Plugin with Oracle MAF – iOS Calendar Plugin by Chris Muir.

SQL Server:

Learn how to invoke SSRS reports from an SSIS package after the data load is completed.

Questions About Using TSQL to Import Excel Data You Were Too Shy to Ask.

This article shows a step-by-step guide to move the distribution database to a new SQL Server instance.

Monitoring Azure SQL Database.

Stairway to SQL Server Agent – Level 2: Job Steps and Subsystems.

Where in the Application Should Data Validation be Done?

MySQL:

Creating JSON documents with MariaDB.

Geographic replication with MySQL and Galera.

Sys Schema for MySQL 5.6 and MySQL 5.7.

Logging with MySQL: Error-Logging to Syslog & EventLog.

Multi-source Replication with Galera Cluster for MySQL.

Categories: DBA Blogs

Watch: Hadoop vs. Oracle Exadata

Mon, 2014-11-24 09:05

Every data platform has its value, and deciding which one will work best for your big data objectives can be tricky—Alex Gorbachev, Oracle ACE Director, Cloudera Champion of Big Data, and Chief Technology Officer at Pythian, has recorded a series of videos comparing the various big data platforms and presents use cases to help you identify which ones will best suit your needs.

“Obviously there’s a big difference in cost,” Alex explains. “However, you would require significantly more engineering effort invested in Hadoop—so it’s a matter of scale when a solution like Hadoop becomes cost efficient.” Learn more key differentiators by watching Alex’s video Hadoop vs. Oracle Exadata.

Note: You may recognize this series, which was originally filmed back in 2013. After receiving feedback from our viewers that the content was great, but the video and sound quality were poor, we listened and re-shot the series.

Find the rest of the series here

 

Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Big Data expertise.

Categories: DBA Blogs

New leafmisscount Option in Flex Cluster 12c

Fri, 2014-11-21 09:40

In the Oracle RAC previous version we have worked with the option “misscount” of crsctl, which applies for all the servers because there was only one kind of servers, but in the new version of Oracle (12c) we have a new kind of RAC configuration, the “Flex Cluster”. With Flex Cluster we have two kinds of servers “Hub nodes” and “Leaf Nodes”, the Hub Nodes have the same concept of the normal nodes in the previous versions, however the Leaf Nodes are different. I will let Oracle Documentation define these servers:

Hub Nodes are similar to Oracle Grid Infrastructure nodes in an Oracle Clusterware standard Cluster configuration: they are tightly connected, and have direct access to shared storage. In an Oracle Flex Cluster configuration, shared storage can be provisioned to Leaf Nodes independent of the Oracle Grid Infrastructure.

Leaf Nodes are different from standard Oracle Grid Infrastructure nodes, in that they do not require direct access to shared storage, but instead request data through Hub Nodes. Hub Nodes can run in an Oracle Flex Cluster configuration without having any Leaf Nodes as cluster member nodes, but Leaf Nodes must be members of a cluster that includes at least one Hub Node.

Reference: https://docs.oracle.com/database/121/CWADD/bigcluster.htm#CWADD92560

With new kind of servers, we also have a new option to configure the maximum time that a Leaf node can be tolerated with heartbeat issues. This crsctl option is “leafmisscount” and it’s exactly the same than the “misscount” for Hub Nodes, but this is for Leaf Nodes.

Let’s see how does it work:

 My Flex Cluster

Configuration

[root@dg1 ~]# crsctl check cluster -all

**************************************************************

dg1:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg2:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

dg3:

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

**************************************************************

[root@dg1 ~]#

 

[root@dg1 ~]# crsctl get node role status -all

Node ‘dg1′ active role is ‘hub’

Node ‘dg2′ active role is ‘hub’

Node ‘dg3′ active role is ‘leaf’

[root@dg1 ~]#

Checking the current value:

[grid@dg1 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 30 for Cluster Synchronization Services.

[grid@dg1 ~]$

How does it work with 30 secs:

[grid@dg2 ~]$ date

Fri Nov 14 16:03:54 EST 2014

–Stop connectivity between Leaf Node
and Hub Nodes

[root@dg3 ~]# date

Fri Nov 14 16:04:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:04:29 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Now let’s increase the value to 600 sec:

[grid@dg1 ~]$ crsctl set css leafmisscount 600

CRS-4684: Successful set of parameter leafmisscount to 600 for Cluster Synchronization Services.

[grid@dg1 ~]$

Let’s confirm the value:

[grid@dg2 ~]$ crsctl get css leafmisscount

CRS-4678: Successful get leafmisscount 600 for Cluster Synchronization Services.

[grid@dg2 ~]$

The maximum value we can specify is 600:

[grid@dg1 ~]$ crsctl set css leafmisscount 3600

CRS-1671: The value for parameter leafmisscount is outside the allowed range of 1 to 600

[grid@dg1 ~]$

Let’s see how does it work with 600:

[root@dg3 ~]# date

Fri Nov 14 16:19:54 EST 2014

–Stop connectivity between leaf
node and hub nodes

[root@dg3 ~]# date

Fri Nov 14 16:24:01 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:25:18 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:27:30 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:28:08 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:04 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4537: Cluster Ready Services is online

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:29:50 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4529: Cluster Synchronization Services is online

CRS-4533: Event Manager is online

[root@dg3 ~]# date

Fri Nov 14 16:30:10 EST 2014

[root@dg3 ~]# crsctl check crs

CRS-4638: Oracle High Availability Services is online

CRS-4535: Cannot communicate with Cluster Ready Services

CRS-4530: Communications failure contacting Cluster Synchronization Services daemon

CRS-4534: Cannot communicate with Event Manager

[root@dg3 ~]#

Categories: DBA Blogs

Challenge Me! Capturing SQL Server Data with PowerShell

Fri, 2014-11-21 09:05

LaerteHello PowerShell lovers! If you are reading this blog post, then you are likely interested in attending my session at SQLBits (well, so far it is an abstract. Hopefully I will be selected!)

Any data professional who is responsible for performance tuning, has their way of gathering data to troubleshoot and solve issues. But what if I told you there was a different way, a better way, to collect the data you need without consuming additional SQL Server resources?

Attend my session and learn how to leverage PowerShell to collect data from Perfmon, DMVs, and more. This session will feature demos where attendees will learn about some .Net classes to control timer events and the PowerShell Register-Objectevent cmdlet.

Attendees are encouraged to send their challenges to me by email at laertesqldba@outlook.com. I will review their scripts and select some of them to demonstrate in the session by offering an alternative using PowerShell and the event  subsystem.

I will split your challenges into three categories:

  1. Perfmon Counters
  2. DMVs, DMFs, etc.
  3. Other

Using one challenge from each category, here are examples of the kinds challenges I will demonstrate:

 

Example 1: Perfmon Counters

Hello Laerte,

My name is James Tiberius Kirk, I am Captain of the starship USS Enterprise and we are testing a new warp drive and our SQL Server´s servers are very, very busy with that. Our DBAs need a way to collect some performance counters, in an interval of time of 15 minutes and the data stored in a repository for further analysis. This process need to be done remotely and we cannot use any kind of SQL Server resources for that. We don’t want to use any kind of GUI for that as well. The performance counters are:SQLServer: Buffer Manager: Buffer cache hit ratio

SQLServer: Buffer Manager: Page life expectancy
SQLServer: SQL Statistics: Batch Requests/Sec
SQLServer: SQL Statistics: SQL Compilations/Sec
SQLServer: SQL Statistics: SQL Re-Compilations/SecCan you help us?  Thanks!


Example 2: DMVs, DMFs, etc.

Hello Laerte,

My name is Anakyn Skywalker, (a.k.a. Darth Vader, the most powerful Sith Master ever.) We are building a new Death Star and our SQL Server´s servers are extremely busy with inserts and updates. We need to capture and store the fragmentation data from one table called BlowUpThePlanet in a server called DeathStarProd3 Database VaderIsTheBest. As I told you before and I will not say again, it is a very busy server and because of that, the interval of gathering needs to be hourly. I am awaiting your help today. *FORCE CHOKING*


Example 3: Other

Hello Laerte,

My Name is Neo. Everything you know, or you think know, actually does not exist—but we can talk about that later.  We are trying to inject a new code in the Matrix (don’t worry about that right now either) and I need to capture some data. I will send to you the TSQL and all information that I need. Can you help me?  Thanks.

TSQL
Insert into XXXX Select XXXX
Interval – 10 seconds
Server Name – XXX
Database Name – YYY

 

Please send your challenges to laertesqldba@outlook.com. I will select three of them, one from each category and I will demonstrate how to do them using PowerShell without using any kind of SQL Server resources, after  I explain the Timer class and the register-objectevent cmdlet.

That is it! And remember: Always… if it is PowerCool, it is PowerShell.

Categories: DBA Blogs

Capturing SQL Server IO Latencies for a Period of Time with PowerShell

Tue, 2014-11-18 14:54

Today’s blog post is a demonstration of the PowerShell approach to Paul Randal’s recent blog post, Capturing IO Latencies for a Period of Time. Since wait statistics were properly implemented in SQL Server, it turned into a powerful resource to diagnose and troubleshoot SQL Server issues.

Paul Randal is, IMHO, one of  the best names in this technology.  I requested his approval to use and modify his code to demonstrate another approach that can be used not only in this situation, but in others where you need to capture data in an interval and  don’t want to use SQL Server resources. Paul, as always, was very kind to allow me to use his implementation to demonstrate mine—thanks a lot, Paul.

In his excellent blog post Capturing IO Latencies for a Period of Time, Paul demonstrates how to capture the wait stats in an interval of 30  minutes using only T-SQL.

If you have a very busy system perhaps leaving this job to trigger the gathering to another resource than SQL Server may be a good idea. Those who know me, know that I am PowerShell lover, so let’s take a look how to do that using PowerShell.

Before we proceed, I strongly encourage readers to check out my good friend Ed Wilson’s fantastic blog post Use Asynchronous Event Handling in PowerShell to understand timer event handlers, asynchronous .NET events, or the cmdlets to work with events.

A quick note about .NET System.Timers.Timer class. 

It is simple—the namespace System.Timers fires an event in a specific interval and the class Timer generates recurring events. In other words, I can set up an event to be fired on each x unit of time (milliseconds.)

The first thing to do it it is to create the object System.Timers.Timer and define the interval that we want. For now, let’s use 500 milliseconds  by setting the property interval.

$Timer= New-Object System.Timers.Timer -Property @{ Interval = 100;autoreset=$true }

We instanced the timer and set up the interval. Now we need to register the object (in the case object $timer) that will fire the event and execute the action. For that we will use the Register-Objectevent cmdlet The parameter Eventname we will use Elapsed that is detailed in Use Asynchronous Event Handling in PowerShell  and also in Manage Event Subscriptions with PowerShell.

First, let’s just print something on the screen:

Register-ObjectEvent -InputObject$timer  -Action { Write-Host‘Holy PowerShell. It Worked!!!! ‘} -EventName Elapsed -SourceIdentifier stateful

Id              Name            State      HasMoreData     Location             Command

—              —-            —–      ———–     ——–             ——-

1               93ce50c3-6f5… NotStarted False                                 Write-Host ‘Holy Powe…

If you simply run this code, you will realize that after 30 seconds nothing happens.

Wait, PowerShell is broken? No my dear friend, the .net class is so beautiful that it allows you start and stop the suppression of the event. Yes, you can start the event and will be recurring every 30 seconds, and also stop it.

So let’s start:

$Timer.Start()

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

Holy PowerShell. It Worked!!!!

And to stop it :

$Timer.Stop()

 

And to Unregister the event use :

Unregister-Event -SourceIdentifier stateful

How PowerCool is that?

Let’s get back to our example. Using Paul’s code, let’s first create the table to store the data. I did some changes to store the date and time so you also can have the historical data if you want and the table is physical.

SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],

[num_of_writes], [io_stall_write_ms], [io_stall],

[num_of_bytes_read], [num_of_bytes_written], [file_handle]

INTO IOLatency

FROM sys.dm_io_virtual_file_stats(NULL, NULL);

GO

alter table IOLatency add [TimeCapture] datetime default getdate()

alter table IOLatency add [Identifier] bigint

 

I added this column identifier because my TSQL it is not so great and I want (like Paul’s example) to get the difference between the last top samples between 30 minutes, so this column will have the same value for the last two gatherings and I can easily create the TSQL condition for that.

Now let’s play with PowerShell: We need to import the module SQLPS to use the invoke-sqlcmd cmdlet . Also in the Action parameter of the Register-Objectevent cmdlet I am using a scriptblock variable to be more friendly readable. Also I am using the interval as Paul, 30 minutes, so the difference always will be in the 2 last same date times with 30 minutes of difference.

 

$Timer=New-Object System.Timers.Timer -Property @{ Interval = 1800000 ;autoreset=$true } #1800000 30 minutes in milliseconds

#Create the ScriptBlock variable to run in the action parameter in the register-objectevent cmdlet

$Action= {

#Import the SQLPS Module

Import-Module SQLPS -ErrorAction SilentlyContinue -DisableNameChecking;

#variable to counter the numb er of the events

$Script:counter+= 1;

#if the events is more than 2 , increase the the identity

if ($counter-eq 3) {

$script:Identity+= 1;

$counter= 1

}

#Define the TSQL

$Tsql =@”

insert into IOLatency ([database_id], [file_id], [num_of_reads], [io_stall_read_ms],

[num_of_writes], [io_stall_write_ms], [io_stall],

[num_of_bytes_read], [num_of_bytes_written], [file_handle],[Identifier])

SELECT [database_id],[file_id], [num_of_reads], [io_stall_read_ms],

[num_of_writes], [io_stall_write_ms], [io_stall],

[num_of_bytes_read], [num_of_bytes_written], [file_handle],$($Identity)

FROM sys.dm_io_virtual_file_stats (NULL, NULL);

“@;

#Invoke the TSQL

invoke-sqlcmd -ServerInstance vader -Database Test -Query$Tsql

}

Register-ObjectEvent-InputObject$timer  -Action  $Action  -EventNameelapsed  -SourceIdentifierstateful

Not it is juts start ….

$Timer.Start()

And every 30 minutes the table IOLatency will be populated. If you want to stop the gathering but not unregistered the event :

$Timer.Stop()

This way if you want to start again, just timer.start().

Now it is time to get the data. Using The Paul´s query  with a change to get the same identifier :

 

WITH [DiffLatencies] AS

(SELECT

— Files that weren’t in the first snapshot

[ts2].[database_id],

[ts2].[file_id],

[ts2].[Identifier],

[ts2].[num_of_reads],

[ts2].[io_stall_read_ms],

[ts2].[num_of_writes],

[ts2].[io_stall_write_ms],

[ts2].[io_stall],

[ts2].[num_of_bytes_read],

[ts2].[num_of_bytes_written]

FROM IOLatency AS [ts2]

LEFT OUTER JOIN IOLatency AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

and ts2.Identifier = ts1.Identifier

WHERE [ts1].[file_handle] IS NULL

UNION

SELECT

— Diff of latencies in both snapshots

[ts2].[database_id],

[ts2].[file_id],

[ts2].[Identifier],

[ts2].[num_of_reads] – [ts1].[num_of_reads] AS [num_of_reads],

[ts2].[io_stall_read_ms] – [ts1].[io_stall_read_ms] AS [io_stall_read_ms],

[ts2].[num_of_writes] – [ts1].[num_of_writes] AS [num_of_writes],

[ts2].[io_stall_write_ms] – [ts1].[io_stall_write_ms] AS [io_stall_write_ms],

[ts2].[io_stall] – [ts1].[io_stall] AS [io_stall],

[ts2].[num_of_bytes_read] – [ts1].[num_of_bytes_read] AS [num_of_bytes_read],

[ts2].[num_of_bytes_written] – [ts1].[num_of_bytes_written] AS [num_of_bytes_written]

FROM IOLatency AS [ts2]

LEFT OUTER JOIN IOLatency AS [ts1]

ON [ts2].[file_handle] = [ts1].[file_handle]

and ts2.Identifier = ts1.Identifier

WHERE [ts1].[file_handle] IS NOT NULL)

SELECT

DB_NAME([vfs].[database_id]) AS [DB],

LEFT([mf].[physical_name], 2) AS [Drive],

[mf].[type_desc],

[num_of_reads] AS [Reads],

[num_of_writes] AS [Writes],

[ReadLatency(ms)] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,

[WriteLatency(ms)] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,

/*[Latency] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,*/

[AvgBPerRead] =

CASE WHEN [num_of_reads] = 0

THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,

[AvgBPerWrite] =

CASE WHEN [num_of_writes] = 0

THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,

/*[AvgBPerTransfer] =

CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)

THEN 0 ELSE

(([num_of_bytes_read] + [num_of_bytes_written]) /

([num_of_reads] + [num_of_writes])) END,*/

[mf].[physical_name],

[vfs].identifier

FROM [DiffLatencies] AS [vfs]

JOIN sys.master_files AS [mf]

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

— ORDER BY [ReadLatency(ms)] DESC

ORDER BY [Identifier],[WriteLatency(ms)] DESC;

GO

 

If you want to to get only one sample , just add a

ON [vfs].[database_id] = [mf].[database_id]

AND [vfs].[file_id] = [mf].[file_id]

AND [vfs].identifier = <IdentifierNumber>

 

This way you also can know what time was made this gathering, just checking the timecapture column in the identifier number. In other words, you also have a historical data that you can query and get the differences or make any calculations that you need.

The data is stored—you can play with whatever query you want and need.

If you are thinking of gathering perfmon counters, it is possible too. Take a look at my blog post on Simple-Talk, The PoSh DBA – Specifying and Gathering Performance Counters and just play with the timer class.

I guess that you realize you can use this approach in any scenario that you need to capture data in a busy system without using any kind (or a minimum)  of SQL Server resources. IMHO, that can be a good approach in those kind environments

Again, I would like to thank Paul Randal for kindly allowing me to use and modify to use his code and blog post to demonstrate my approach, and of course the Hey, Scripting Guy! blog.

 

Remember kids… If it is PowerCool, it is PowerShell!

Categories: DBA Blogs

Log Buffer #397, A Carnival of the Vanities for DBAs

Fri, 2014-11-14 09:58

In the spirit of Movember, the database bloggers are also chipping in with their fair share of contribution, not only with a mo but also with the blog posts. This Log Buffer Edition encompasses that all.

Oracle:

Will the REAL Snap Clone functionality please stand up?

Oracle Linux images for Docker released.

In-depth look into Oracle API Catalog (OAC) 12c.

Patch Set Update: Hyperion Strategic Finance 11.1.2.3.504.

Rollback to Savepoint Does Not Release Locks.

SQL Server:

Overcoming the OPENQUERY Record Limit for AD.

Configuring Critical SQL Server Alerts.

What is Biml? – Level 1?

Database Configuration Management for SQL Server

Free eBook: SQL Server Backup and Restore.

Set up a Memory Quota for SQL Server Memory Optimized Databases.

MySQL:

In C (and C++) you can specify that a variable should take a specific number of bits of storage by doing “uint32_t foo:4;” rather than just “uint32_t foo”. In this example, the former uses 4 bits while the latter uses 32bits. This can be useful to pack many bit fields together.

Oracle AVDF post-installation configuration.

Everything about MySQL Users and Logins You Didn’t Know and Were Afraid to Ask.

Setting up a MySQL Enterprise Monitor 3 Test Environment.

While playing with MySQL 5.7.5 on POWER8, I came across a rather interesting bug (74775 – and this is not the only one… I think I have a decent amount of auditing and patching to do now) which made me want to write a bit on memory barriers and the volatile keyword.

Categories: DBA Blogs