Befriending Dragons

Turn Scary Into Attainable


2 Comments

Azure Data Factory: Hub Not Found

You can use the new Azure portal to create or edit Azure Data Factory components. Once you are done you may automate the process of creating future Data Factory components from PowerShell. In that case you can use the JSON files you edited in the portal GUI as configuration files for the PowerShell cmdlets. For example, you may try to create a new linked service using settings from C:CoolerHDInsight.JSON as specified below:

New-AzureDataFactoryLinkedService -ResourceGroupName CoolerDemo -DataFactoryName $DataFactoryName -File C:CoolerHDInsight.JSON

In that case you may see something like this error:

New-AzureDataFactoryLinkedService : Hub: {SomeName_hub} not found.
CategoryInfo                : CloseError: (:) [New-AzureDataFactoryLinkedService]. Provisioning FailedException
FullyQualifiedErrorID   : Microsoft.Azure.Commands.DataFactories.NewAzureDataFactoryLinkedServiceCommand

image

If you check the JSON file that you exported from the portal and referenced in the PowerShell script, you will see it ends with something like this:

        “isPaused”: false,
“hubName”: “SomeName_hub”
}
}

The hubName is currently automatically generated based on the name of the Data Factory and should not be present in the JSON files used by PowerShell. Remove the comma on the line above the hubName and the entire line starting with hubName.

       ,
“hubName”: “SomeName_hub”

That will leave the end of the file looking something like this:

        “isPaused”: false
}
}

Check out all your other JSON files you are using for Data Factory components and do the same editing for any that have a hubName.

NOTE: This applies to Azure Data Factory as of April 2015. At some point the hubName should become a viable parameter usable by PowerShell.

I hope you enjoyed this small bite of big data!

Cindy Gross – Neal Analytics: Big Data and Cloud Technical Fellow  image
@SQLCindy | @NealAnalytics | CindyG@NealAnalytics.com | http://smallbitesofbigdata.com

//


Leave a comment

Data Gathering – SQL Server Setup Problems

Data Gathering – SQL Server Setup Problems

Cindy Gross, Dedicated Support Engineer

 

Gathering the right data during a problem can get you a long ways towards resolving the problem. When you ask the right questions and clearly define the problem it changes the way you approach the remaining steps. Sometimes the answer to a seemingly simple question leads you right to the solution. In this series of data gathering blogs I am NOT going to tell you how to solve the problem, but I am going to tell you how to get a good set of troubleshooting data you can use to do your own troubleshooting.

Any time you have a SQL Server setup issue you will need to collect this information:

 

  • Date/time of the failed setup
  • Type of install and parameters used – GUI, unattended/command line, slipstream, sysprep
  • Number of attempts, any actions you took to cleanup or remove prior installs
  • Version/Edition/Exact build #/32bit or x64 or IA64 for
    • Windows
    • SQL Server
    • Virtualization software (Hyper-V, VMWare for example)
  • How many cluster nodes and SQL instances or state that it’s standalone
  • Whether the problem is with RTM, SP, and/or CU
  • Whether the problem is with a new install or an upgrade
  • What components you are installing (Client tools, SSIS, SQL, RS, AS, BOL, samples) and whether any succeed
  • Exact error message
  • Repro steps and/or failure scenario
  • All setup logs (i.e. %ProgramFiles%Microsoft SQL Serverx0Setup BootstrapLOG)
  • Which step in the build doc you are at when you see the setup failure
  • What permissions have been enabled/granted for MSDTC
  • Any steps you take to attempt to resolve the problem and the result
  • If on a cluster, have you re-run the cluster verification log and addressed all errors and warnings?
  • Did you run setup using “run as administrator”?
  • What account did you log into Windows with when you ran setup and what groups does it belong to?

Armed with this information you are now ready to solve the problem!


Leave a comment

Windows storport enhancement to help troubleshoot IO issues

For Windows 2008 and Windows 2008 R2 you can download a Windows storport enhancement (packaged as a hotfix). This enhancement can lead to faster root cause analysis for slow IO issues. Once you apply this Windows hotfix you can use Event Tracing for Windows (ETW) via perfmon or xperf to capture more detailed IO information that you can share with your storage team. This could be very useful for your SQL Server installations or any system where you suspect IO issues.

 

Enhancement information and background topics:

·         Storport ETW Logging to Measure Requests Made to a Disk Unit http://blogs.msdn.com/b/ntdebugging/archive/2010/04/22/etw-storport.aspx

·         Diagnose Slow I/O on Windows With Xperf https://www.youtube.com/watch?v=0h-i_EfzadA (from the MSFT guys who wrote it – based on a pre-release version but the basics still apply)

·         xPerf: Once I Was Blind and Now I See http://blogs.msdn.com/b/jimmymay/archive/2009/09/08/xperf-once-i-was-blind-and-now-i-see.aspx

·         Storport Driver http://msdn.microsoft.com/en-us/library/ff567541(VS.85).aspx

The hotfix downloads are available from the top of each article and instructions are included in the body of each KB:

·         KB 979764 (Windows Server 2008 hotfix)

·         KB 978000 (Windows Server 2008 R2 hotfix)

·         http://msdn.microsoft.com/en-us/performance/cc825801.aspx (xperf downloads)


1 Comment

SQL Server Performance Tools – Boise Code Camp Presentation

Today I am presenting about SQL Server Performance Tools at the Boise Code Camp. You can download the slides and supporting files here on this blog (at the bottom it says Attachment(s): PerformanceTools.zip ). The basic agenda of items covered is:

 

¢  Methodology

¢  SQLDiag

¢  PSSDiag

¢  SQLNexus

¢  Profiler

¢  PerfMon

¢  References

The perfstats script I discussed can be found at:
And the perfstats analysis tools are at:

PerformanceTools.zip


2 Comments

What do those “IO requests taking longer than 15 seconds” messages on my SQL box mean?

You may be sometimes seeing stuck/stalled IO messages on one or more of your SQL Server boxes. This is something it is important to understand so I am providing some background information on it.

 

Here is the message you may see in the SQL error log:

SQL Server has encountered xxx occurrence(s) of IO requests taking longer than 15 seconds to complete on file [mdf_or_ldf_file_path_name] in database [dbname] (dbid). The OS file handle is 0x…. The offset of the latest long IO is: 0x….”.

 

The message indicates that SQL Server has been waiting on at least one I/O for 15 seconds or longer. The exact number of times you have exceeded this time for the specified file since the last message is included in the message. The messages will not be written more than once every five minutes. Keep in mind that read IOs on an average system should take no more than 10-20ms and writes should take no more than 3-5ms (the exact acceptable values vary depending on your business needs and technical configuration). So anything measured in seconds indicates a serious performance problem. The problem is NOT within SQL Server, this message indicates SQL has sent off an IO request and has waited more than 15 seconds for a response. The problem is somewhere in the disk IO subsystem. For example, the disk IO subsystem may have more load than it is designed to handle, there is a “bad” hardware or firmware somewhere along the path, filter drivers such as anti-virus software are interfering, your file layout is not optimal, or some IO subsystem setting such as HBA queue depth is not set optimally.

 

Though the root cause is IO, you can see other symptoms that are a side effect and may lead you down the wrong troubleshooting path. For example, if enough IO is backed up behind the stalled IO then you may see blocking in SQL Server (because locks that are usually taken for very short periods of time are now held for seconds), new connections may not be allowed, and the CPU usage can increase (because many threads are waiting), and a clustered SQL Server can fail over (because the IsAlive checks which are just SQL queries fail to complete like all the other queued queries). You may see other errors returned to the user or in the various logs, such as timeouts.

 

There are two ways to approach this problem. You can either reduce the IO on the system (change indexes or queries or archive data for example) or you can make the underlying system able to handle the IO load (fix hardware/firmware problems, change configurations, add disks or controllers, change the file layout, etc.).

 

Background:

·         897284  Diagnostics in SQL Server 2000 SP4 and in later versions help detect stalled and stuck I/O operations

http://support.microsoft.com/default.aspx?scid=kb;EN-US;897284

·         Detecting and Resolving Stalled and Stuck I/O Issues in SQL Server 2000 SP 4 http://msdn.microsoft.com/en-us/library/aa175396(SQL.80).aspx

 

Troubleshooting:

·         Every Windows 2003 SP1 or SP2 system should have this storport fix: 941276  A Windows Server 2003-based computer stops responding when the system is under a heavy load and when the Storport driver is being used http://support.microsoft.com/default.aspx?scid=kb;EN-US;941276

·         Use PerfMon to look at the disk counters for sec/read, sec/write, bytes/sec, current disk queue length, reads/sec, writes/sec

·         Collect data from sys.dm_io_virtual_file_stats and sys.dm_io_pending_io_requests.

·         Ask your storage admins to monitor the entire IO subsystem from the Windows system all the way through to the underlying disks.


Leave a comment

Professional SQL Server 2008 Internals and Troubleshooting

Our new book, Professional SQL Server 2008 Internals and Troubleshooting, will be shipping soon! Order now! 🙂 Christian Bolton, Justin Langford, Brent Ozar, and James Rowland-Jones have each written several chapters in this book. Steven Wort, Jonathan Kehayias and I each contributed a chapter as well. The 1st half of the book introduces you to how things work within SQL Server at a level that will make it easier to understand the rest of the book. The 2nd half of the book focuses on troubleshooting common SQL Server problems.

Download the first chapter and find out more about the book here: http://sqlservertroubleshooting.com/.

Chapters include:

  1. SQL Server Architecture
  2. Understanding Memory
  3. SQL Server Waits and Extended Events
  4. Working with Storage
  5. CPU and Query Processing
  6. Locking and Latches
  7. Knowing Tempdb
  8. Defining Your Approach to Troubleshooting
  9. Viewing Server Performance with PerfMon and the PAL Tool
  10. Tracing SQL Server with SQL Trace and Profiler
  11. Consolidating Data Collection with SQLDiag and the PerfStats Script
  12. Introducing RML Utilities for Stress Testing and Trace File Analysis
  13. Bringing It All Together with SQL Nexus
  14. Using Management Studio Reports and the Performance Dashboard
  15. Using SQL Server Management Data Warehouse
  16. Shortcuts to Efficient Data Collection and Quick Analysis

http://rcm.amazon.com/e/cm?lt1=_blank&bc1=000000&IS2=1&bg1=FFFFFF&fc1=000000&lc1=319D3A&t=cinisthou-20&o=1&p=8&l=as1&m=amazon&f=ifr&asins=0470484284