SQL on Azure VM BPCheck

After a long hiatus, I am back on the blogosphere and have decided to dedicate a series to running Best Practices checks using PowerShell on Azure virtual machines running in the new deployment model: ARM (Azure Resource Manager). If you have worked on the classic deployment model, then you will need to unlearn a few things and re-learn a little more. However, the learning curve ain’t that great. Think of Azure Resource Manager as a container for all the cloud components that make up your solution. If you had a SQL Server instance running on an Azure virtual machine, then you would be using compute, networking, storage components in Azure which would together be encapsulated in a Resource Manager group.

I had previously done a series of posts around running best practices checks on Azure Virtual Machines running SQL Server. Some of those checks are still valid today as they only dealt with the SQL Server instance. You will see components of those scripts reused. Some of the checks are way easier due to the way ARM deployments are managed in Azure and the endpoints that the Azure PowerShell 1.0 exposes.

The PowerShell scripts available on the GitHub repository are mentioned below. I will run a post on each of these scripts to explain what each of these do and what to expect as the output of each of these scripts.

  • Get-AllocationUnitCheck.ps1 – Checks if the allocation unit size for the disks attached to the VM is 64K
  • Get-DBProperties.ps1 – Checks if any database has AUTO CLOSE or AUTO SHRINK enabled
  • Get-FilesOnTemp.ps1 – Checks to see if any database files are hosted on the temporary drive
  • Get-IFI.ps1 – Checks to see if the SQL Server service account has instant file initialization security privileges
  • Get-LPIM.ps1 – Checks to see if Lock Pages in Memory privilege is granted to the SQL Server service account
  • Get-OSFilesDB.ps1 – Checks to see if database files are hosted on the OS drive
  • Get-StorageAccountBP.ps1 – Checks to see if the storage account has replication enabled
  • Get-VMSize.ps1 – Checks if the right virtual machine tier is being used
  • Temporary Drive.ps1 – Finds out the temporary drive on the virtual machine
  • Get-Backups.ps1 – Finds out if any backups are being taken to local disk

The PowerShell scripts are available on GitHub repository SqlOnAzureVM. Since these scripts are now on GitHub, please feel free to pull them and enhance them as per your needs.

Exporting Data to a SQL Server on an Azure VM

If you have played around with a SQL Server installation on an Azure Virtual Machine, then you will invariably have the need to move a database from an on-premise environment to your Azure Virtual Machine for testing, deployment and a host of other activities which you are involved with on a regular basis at work!

Books Online has complete documentation on this wizard. In this post, we will attempt to understand what happens under the hood. Read on to find out more.

Continue reading

Azure SQL Database Export

In one of my previous posts I had talked about creating or rather restoring a deleted database backup. There is another option in preview called the automated export which allows you to create an automated export copy of your Azure SQL Database. The Azure SQL Database Import/Export Service is a REST-based web service that runs in every Microsoft Azure data center. The service provides a free request queuing service and a free Compute service to perform imports and exports from a Microsoft Azure SQL database to Microsoft Azure binary large object (BLOB) storage. The import and export operations are not a traditional physical database backup but a logical backup of the database that uses a special BACPAC format. This logical BACPAC format lets you avoid having to use a physical format that might vary between versions of SQL Server and SQL Database. Therefore, you can use it to safely restore the database to an SQL database and also to a SQL Server database.

As you can see from the screenshot, the Configure tab in the Azure Management portal for my Azure SQL Database, megatron, has an Export Status set to None.



As usual, this is a PREVIEW feature and the above is true as of today when I am writing this post. This functionality could change in the future.

Read on to find out what happens when I change the setting to Automatic… Is is as easy as 1-2-3?

Continue reading

SQL Server 2012: Trace Flags

This is something that I have been planning to release for some time but just have not been able to get to mainly due to procrastination! So this new year, I decided to get down to it! A list of all the publicly documented SQL Server 2012 Trace Flags.

The list below shows the trace flags available in SQL Server 2012 along with whether the trace flag can be enabled as a startup parameter or using DBCC TRACEON command or both.

SQL Server exposes multiple trace flags which are required to set specific server characteristics or to switch off a particular behavior. Some trace flags are required to enable the fix post the installation of the update.

This is currently a work-in-progress. The trace flags mentioned in the table below have the associated public article links mentioned which provide more information about the trace flag.

If you want a trace flag to be mentioned in the list below, then feel free to leave a comment. Note that only publicly documented trace flags will appear in this list.

The list of trace flags available for SQL Server 2005 are documented here.

The list of trace flags available for SQL Server 2008 are documented here.

1. Data mentioned below is as of 14th January, 2014.
2. Trace flags should be used under the guidance of Microsoft SQL Server support.  They are used in this post for discussion purposes only and may not be supported in future versions.
3. Trace flags for hotfixes should be only enabled if the fix is applicable to the SQL Server instance that you are enabling the trace flag on.

Startup: If Y,Trace flag can be enabled by adding the trace flag number as a startup parameter using –T<trace flag number>
DBCC: If Y, Trace flag can be enabled using DBCC TRACEON(<trace flag number>,-1)

Trace Flag Description KB Startup DBCC
205 Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog. 195565 Y Y
260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). BOL Link Y Y
610 Minimally logged inserts for indexed tables for fast load scenarios. Read article for limitations. Article Y Y
652 Disable pre-fetch scans 920093 Y Y
661 Disable ghost removal process 920093 Y Y
806 Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag 841776 Y Y
815 Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled Article Y Y
818 Detect stale reads. Very helpful in analyzing recurring data corruption issues. Article Y Y
830 Suppress stuck-stalled I/O warnings Article Y Y
834 Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances. 920093 Y N
836 Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server 920093 Y N
840 On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts 912322 Y Y
1106 Enable tempdb allocation trace. Not suggested for production use. 947204 Y Y
1117 Even growth of all files in a file group Whitepaper Y Y
1118 Prevents allocation of pages from mixed pages for all databases 328551 Y Y
1204 Returns the resources and types of locks participating in a deadlock and also the current command affected. Useful for SQL Server 2000. BOL Link Y Y
1205 Prints information about each deadlock search 832524 Y Y
1211 Disabled lock escalation BOL Link Y Y
1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. To be used for SQL Server 2005 and above. BOL Link Y Y
1224 Disables lock escalation based on the number of locks BOL Link Y Y
1260 Disabled mini-dump for non-yield conditions Whitepaper Y Y
1262 Dump everytime a non-yielding scheduler condition is detected. Article Y Y
1439 Trace database restart and failover messages to SQL Errorlog for mirrored databases 983500 Y Y
1448 Prevent replication latency with database mirroring enabled. Apply KB 983480 to prevent issues after enabling -T1448. 937041 Y Y
2301 Enable advanced decision support optimizations 920093 Y Y
2340 Disable batch sort to reduce CPU consumption Blog Y Y
2371 Use a modified threshold for auto update statistics 2754171 Y Y
2389 Automatically generated quick statistics for known ascending keys 922063 N Y
2390 Automatically generated quick statistics for all columns that include known ascending keys or unknown ascending keys 922063 N Y
2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. Article Y Y
2544 Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2546 Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y
2551 Generate a filtered dump of the SQL Server process. Reference Y Y
3004 Write extended information about backup/restore to Errorlog Blog N Y
3014 Write extended information about backup/restore to Errorlog Blog N Y
3023 Automatically enable CHECKSUM for BACKUP command 2656988 N Y
3042 Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size Article Y Y
3205 Disable hardware compression for tape drives. Article N Y
3213 Display backup/restore configuration parameters Blog N Y
3226 Suppress successful backup messages in the logs Article N Y
3502 Displays start and end of checkpoint. Can be very verbose and increase your transaction log size very quickly 815436 Y Y
3504 Print Checkpoint summary Blog Y Y
3505 Disable Checkpoint 815436 Y Y
3604 Send trace output to client Y Y
3605 Send trace output to SQL Errorlog Y Y
3608 Prevents SQL Server from automatically starting and recovering any database except the master database Article Y N
3625 Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages. Article Y N
3688 Needed to enable fix for issue mentioned in KB Article. 922578 Y N
4010 Allows only shared memory connections to the SQL Server Blog Y Y
4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0. Article Y Y
4029 Push more verbose errors to the errorlog when read+write errors occur in the network layer on the server side. Blog Y Y
4199 Controls multiple query optimizer changes previously made under multiple trace flags. Refer article for more details. 974006 Y Y
4606 Disables password policy check during server startup Blog Y Y
4610 Increases the size of the hash table that stores the cache entries by a factor of 8. 959823 Y Y
4616 Makes server-level metadata visible to application roles Article Y Y
4618 Limit the number of entries in the TokenAndPermUserStore store 959823 Y Y
4621 Control TokenAndPermUserStore store size using registry value (Applies only to SQL Server 2005. For SQL Server 2008 and above, please refer 959823) 959823 Y Y
6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration Article Y Y
7806 Enables a dedicated administrator connection (DAC) on SQL Server Express Article Y N
8002 Treat affinity mask like processor affinity 818765 Y Y
8004 Create memory dump for the first occurrence of out-of-memory condition Article Y Y
8011 Disable the ring buffer for Resource Monitor 920093 Y Y
8012  Disable the ring buffer for schedulers 920093 Y Y
8018 Disable the exception ring buffer 920093 Y N
8019 Disable stack collection for the exception ring buffer 920093 Y N
8020 Disable working set monitoring 920093 Y N
8024 Performs additional checks before generating non-yielding condition dump Whitepaper Y Y
8032 Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger Article Y N
8048 Forces CPU based partitioning Blog Y N
8602 Ignore all index hints Whitepaper Y Y
8721 Will dump information into the error log when AutoStat has been run. 195565 Y Y
8744 Disable pre-fetching for ranges 920093 Y Y

PowerView and System Health Session–Scheduler Health

This is actually turning out to be a series. I had demonstrated the use of System Health session to retrieve CPU usage details earlier. In this post, I will demonstrate how to use Power Pivot and Power View with System Health Session data to fetch SQL OS scheduler health information.

Using the Transact-SQL query below, I shall fetch the SQL OS scheduler statistics from the System Health Session data in a PowerPivot table name “QueryProcessingComponent”.


-- Fetch data for only SQL Server 2012 instances
IF (SUBSTRING(CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)),1,CHARINDEX('.',CAST(SERVERPROPERTY ('ProductVersion') AS varchar(50)))-1) >= 11)
-- Get UTC time difference for reporting event times local to server time

-- Store XML data retrieved in temp table
INTO #SystemHealthSessionData
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'   AND xet.target_name = 'ring_buffer';

;WITH CTE_HealthSession (EventXML) AS   (
SELECT C.query('.') as EventXML
FROM #SystemHealthSessionData a
CROSS APPLY a.XMLDATA.nodes('/RingBufferTarget/event') as T(C) )
DATEADD(mi,@UTCDateDiff,EventXML.value('(/event/@timestamp)[1]','datetime')) as [Event Time],
EventXML.value('(/event/data/value/queryProcessing/@maxWorkers)[1]','bigint') as [Max Workers],
EventXML.value('(/event/data/value/queryProcessing/@workersCreated)[1]','bigint') as [Workers Created],
EventXML.value('(/event/data/value/queryProcessing/@workersIdle)[1]','bigint') as [Idle Workers],
EventXML.value('(/event/data/value/queryProcessing/@pendingTasks)[1]','bigint') as [Pending Tasks],
EventXML.value('(/event/data/value/queryProcessing/@hasUnresolvableDeadlockOccurred)[1]','int') as [Unresolvable Deadlock],
EventXML.value('(/event/data/value/queryProcessing/@hasDeadlockedSchedulersOccurred)[1]','int') as [Deadlocked Schedulers]
FROM CTE_HealthSession
WHERE EventXML.value('(/event/@name)[1]', 'varchar(255)') = 'sp_server_diagnostics_component_result'
AND EventXML.value('(/event/data/text)[1]','varchar(255)') = 'QUERY_PROCESSING'
ORDER BY [Event Time];

DROP TABLE #SystemHealthSessionData


Now I shall create three new columns in the Power Pivot sheet as follows:

1. Day using Day() function on the Event Time column

2. Hour using Hour() function on the Event Time column

3. Minute using the Minute() function the Event Time column

After this is done, I created two Power View sheets with the following layouts:

1. One sheet named Threads Timeline containing scatter graph with the hour column on the X-axis and the day column on the Y-axis. The play axis uses the event time data.

2. Another sheet named Worker Thread Stats with a line graph for workers created, pending tasks and idle workers using event time as the x-axis. Also there is a tabular output showing the same data with two additional columns showing the unresolvable deadlocks and deadlocked schedulers respectively.

A screenshot of one of the Power View sheets is shown below. The screenshot of the scatter graph would not do justice which is why you need to download the Excel sheet and see for yourself! Smile

Excel file download location: http://sdrv.ms/10O0udO


Stay tuned to TroubleshootingSQL for new visualizations that I will be discussing soon.

Previous post in this series

PowerView and System Health Session–CPU health 

A year that was

Happy New Year to all of you out there!!

The year that was…. 2012… was an action packed year in all things SQL. Microsoft launched SQL Server 2012 which introduced a large number of enhancements and new features in the database engine, integration services, analysis services and reporting services. If I would have to summarize my learning time year-on-year, then I have spent the maximum time in 2012 studying and enhancing my knowledge in SQL Server due to all the new features introduced.

TroubleshootingSQL also had a good year… The Facebook page now has over 300 likes… The Twitter avatar of TroubleshootingSQL has over 200 followers and my blog showed a significant YoY growth in visitor count!! So thank you for all the patronage and I am glad the SQL folks find my posts useful! The TOP 5 posts of the year in my blog were:

The new Wiki series that I started also received significant amount of coverage as well and the posts #2 and #3 mentioned above are part of my Wiki series.

I did speak at various community events throughout the year, the most significant of them being TechEd India.

Another highlight was the book launch at SQL PASS 2012 which I co-author: Professional SQL Server 2012: Internals and Troubleshooting, which is now available both as e-books as well as paperback across many regions.

What can you look forward to in 2013. Since the world did not end, I will write about all the posts that I have been holding onto (partly due to procrastination) due to the fact that world was going to end in December!!

You will see new additions to the SQL Wiki series, more posts about Power View, useful features in SQL Server 2012 that are not commonly used and much more! So stay tuned….

SQL PASS and Book Launch

Day 1 done at the SQL PASS Summit 2012!! …. It was a packed day with some interesting events starting with the Keynote session. I spent most of my day at the SQL Server Clinic meeting customers and discussing their work with SQL Server and helping them with suggestions and answers to the questions they had regarding the product. Most of all, I enjoyed meeting all the friends that I made on Twitter in person. It is always good to connect a face to a virtual name that you interact with.

Day 2 will be much more interesting as I will be part of the official launch of the book, Professional SQL Server 2012 Internals and Troubleshooting, that I have co-authored with Christian Bolton, James Rowland Jones, Glenn Berry, Justin Langford and Gavin Payne.

You will still find me at the SQL Server Clinic or have me loitering about near Exhibition Hall 4. In case you still can’t locate me, send a tweet across my way (@banerjeeamit). In partnership with SQL Sentry at the exhibitors stand from 12:30 on Thursday 8th November the authors will be available for a book signing event which will give you the chance to meet and greet the authors and contributors. This book is truly a great example of collaboration and community power where the authors have interacted over email for months but have met each other only once. And for some, this summit is their first face-to-face meeting!

SQL Sentry also have lots and lots of copies to give away for free so be sure to drop by their stand and ask about it! The book is also available at the PASS bookstore for sale. So if you want one, head out there and grab a copy.

You can also order it from your favorite online retailer:

Amazon.com: http://amzn.to/U9IlPV

Barnesandnoble.com: http://bitly.com/Ux1gog