Category: Database Engine

Details about EXECSYNC Wait Type in SQL Server

Leave a comment
SQLServerF1

When a query is submitted to SQL Server for execution, SQL Server will generate a optimal query plan based on the statistics and indexes. Any query will need to read the data from memory or write to memory and in between it has to read from disk or write to disk. In this whole process, there are CPU, Memory, Disk and Network resources utilized and depending up on the amount of data involved and the resource speed, SQL Server will have to wait for the task to be completed by the respective resource and until then SQL Server may end up in waiting for it to complete. SQL Server provides with the wait type it is waiting on, to help us identify where it is waiting on, so that we can look into improving the speed of the processing with by improving the hardware or by tuning the queries. There are many wait types defined in SQL Server and can be found by querying sys.dm_os_wait_stats DMV.

Recently we encountered an issue, where we observed EXECSYNC wait type while running a reindex job. Interestingly, this wait type was encountered when we tried to stop the job, as it was running long and was causing blocking to user queries, but the job did not stop immediately and was waiting on this wait type EXECSYNC. This is not a popular wait type, so checking the description about this wait type, found this Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state. If we check from sys.sysprocessess, we can notice that the state of the request was in KILL/ROLLBACK state. We monitored and few minutes, the rollback of the reindex job completed and the wait type and the session ended. Up on further research, found that this wait type can be ignored, instead we need to focus on the reindex job on why it is running long and need to run it during off hours when there are least number of users using the database.

For more information regarding wait types in SQL Server, they are mainly categorized into below types.
Resource waits – Resource waits occur when a worker requests access to a resource that is not available because the resource is being used by some other worker or is not yet available. Examples of resource waits are locks, latches, network and disk I/O waits. Lock and latch waits are waits on synchronization objects
Queue waits – Queue waits occur when a worker is idle, waiting for work to be assigned. Queue waits are most typically seen with system background tasks such as the deadlock monitor and deleted record cleanup tasks. These tasks will wait for work requests to be placed into a work queue. Queue waits may also periodically become active even if no new packets have been put on the queue.
External waits – External waits occur when a SQL Server worker is waiting for an external event, such as an extended stored procedure call or a linked server query, to finish. When you diagnose blocking issues, remember that external waits do not always imply that the worker is idle, because the worker may actively be running some external code.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

.NET Framework execution was aborted Error in SQL Errorlog

Leave a comment
SQLServerF1

There are many error messages defined in SQL Server which are returned when certain invalid operation is performed and based on the task performed, appropriate error number, severity and brief error message is sent to the application which run the query like SSMS or SQLCMD. Unfortunately, not all the error messages are understood easily and can become difficult to identify the cause and to find a solution. Especially errors related to performance or memory can be even difficult to diagnose or troubleshoot. One of the error, which I worked on recently was related to .NET Framework execution was aborted by escalation policy because of out of memory. Initial look at the error message, it given an idea that the error message was result of memory problem, as it is “out of memory” in the error message.

Error: 6532, Severity: 16, State: 49.
–> .NET Framework execution was aborted by escalation policy because of out of memory.
[WARNING] System.Threading.ThreadAbortException: Thread was being aborted.
[WARNING] System.Threading.ThreadAbortException:
[WARNING] at System.Data.SqlTypes.SqlChars.get_Value()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetCharArray()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetString()
[WARNING] at System.Data.SqlServer.Internal.XvarWlobStream.GetSqlString()
Other than the above error, you may also end up seeing some error messages like below.
– AppDomain 2 (xxx) is marked for unload due to memory pressure.
– AppDomain Trio.dbo[runtime].xx was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
– Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure.
– Additionally, you may see the above error messages with SQL Server 2008 features which use SQL CLR internally, like DMF (Declarative Management Framework) and spatial data types.

These errors can occur when SQL CLR stored procedures, user defined functions, user defined data types, or user defined aggregates, are used against SQL Server especially on 32 bit SQL instance.
– AppDomain 2 (xxx) is marked for unload due to memory pressure – This error message can be mostly informational message indicating that SQL CLR is responding to the memory pressure on the system. If this is an intermittent error message and is not affecting the execution of SQL CLR objects, the message can be ignored.
– For other errors, ensure that .NET Framework 2.0 is up to date with the latest available patch. Ensure SQL CLR object uses any assemblies other than those documented are not used. SQL Server 32 bit instance virtual memory is limited to 4GB. If we use SQL CLR extensively with a SQL Server 32 bit instance and experience one or more of above errors, then the best thing would be to migrate to a 64 bit SQL Server. This will allow SQL CLR to access more virtual memory and may prevent the above errors from happening in your environment. Other than that when we develop custom SQL CLR applications, we need to use SQL CLR memory carefully, like avoiding caching large amount of data using objects such as DataTables, If required use TSQL to retrieve the data, try avoid using static variables in your code to store large objects, and try create objects as late, and release them as early, as possible. If issue still persists, then check if there are any known issues and patch SQL Server to latest version.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

MDW Data Collector Set Could Not be Uploaded Due to Cache File Corruption

Leave a comment
SQLServerF1

Management Data Warehouse (MDW) was introduced starting with SQL Server 2008 and this helps for performance troubleshooting by capturing some data. MDW is a set of components that enable a database developer or administrator to quickly track down problems that could be causing performance degradation. MDW feature consists of The Data Collector, The MDW database and The MDW reports components. Many of the reports included in MDW will provide suggestions for improving performance, based on information gathered by the Data Collector in areas of
Query performance statistics and use of indexes, Resource locking and blocking and Disk usage. Although MDW is a great feature and useful in many cases, but sometimes just link any other feature, it will create some issues, like low disk space issues on C:\ drive when cached option is chosen for data collection, then it will create .cache files under C:\ drive service account temp folder. There are other issues as well like .cache files are deleted or missing or corrupt leads to the MDW failures.

Some of the common errors or known issues with Management Data Warehouse (MDW) are mentioned below.
You may receive errors like below.
– The file had bad version and flags information. The file is damaged or not a SSIS-produced raw data file. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC0202061
– Encountered bad metadata in file header. The file is damaged or not a SSIS-produced raw data file.
component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020205E
– Unexpected end-of-file encountered while reading X bytes from file “Y”. The file ended prematurely because of an invalid file format. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC0202069
– The adapter encountered an unrecognized data type of X. This could be caused by a damaged input file (source) or by an invalid buffer type (destination). component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020206B
– String too long. The adapter read a string that was X bytes long, and expected a string no longer than Y bytes, at offset Z. This could indicate a damaged input file. The file shows a string length that is too large for the buffer column. component “Raw File Destination” (57) failed the pre-execute phase and returned error code 0xC020206C

All the above error occur if one or more Data Collector cache files gets corrupted. The corruption may be caused for different reasons, like Data Collector may have encountered an exception while running or The disk must have run out of free space while Data Collector is writing to a cache file or A firmware or a driver problem. So, to resolve the issues, check the disk space on C:\ drive and ensure there is enough free space, make sure all firmware and third party drivers are up to date, and check MDW data collector properties to make sure they are all correct and importantly understood what they do, like cached and non-cached collector properties where enabling cached property will create .cache files under C:\ drives SQL Service account temp folder.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Limitations of using Stretch Database in SQL Server 2016

Leave a comment
SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Although this is great feature and very helpful for many organizations, but at this time of SQL Server 2016 RTM release, there are lot of restrictions which make it less likely to be used in many environments, because stretch database cannot be used with many widely used features. Some of the limitations are mentioned below.
– One of the difficult restriction which stops the usage of stretch database is that uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data.
– We can’t UPDATE or DELETE rows that have been migrated to Azure SQL database cloud, or on the rows that meet eligible criteria for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
– We are not allowed to INSERT rows into a Stretch-enabled table over a linked server.
– We cannot create an index for a view that includes Stretch-enabled tables.
– Filters on SQL Server indexes are not propagated to the remote table.
– Some of the Table properties limitations for stretch database include, Tables that have more than 1,023 columns or more than 998 indexes, FileTabless or tables that contain FILESTREAMM data, Tables that are replicated, or that are actively using Change Tracking or Change Data Capture, Memory-optimized tables, etc.

– Data types that are not support in a table to be part of stretch database include, text, ntextt and image
timestampp, sql_variantt, XML, CLR data types including geometry, geographyy, hierarchyidd, and CLR user-defined types
– Column types that are not supported with stretch database include COLUMN_SETt, Computed columns, Constraints,
Default constraints and check constraints, Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).
– Indexes which are not supported on stretch database include
Full text indexes, XML indexes, Spatial indexes, Indexed views that reference the table

Local database. The on-premises SQL Server 2016 Release Candidate (RC3) database.
Remote endpoint. The location in Microsoft Azure that contains the database’s remote data.
Local data. Data in a database with Stretch Database enabled that will not be moved to Azure based on the Stretch Database configuration of the tables in the database.
Eligible data. Data in a database with Stretch Database enabled that has not yet been moved, but will be moved to Azure based on the Stretch Database configuration of the tables in the database.
Remote data. Data in a database with Stretch Database enabled that has already been moved to Azure.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

How to Decide If Stretch Database Can Be Used in Your Environment

Leave a comment
SQLServerF1

Stretch Database is an interesting and popular new feature introduced with new SQL Server 2016. Stretch database helps in migrating our historical or less frequently used or archived or cold data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the organizations like reducing the storage costs, automatically archiving the old data to Azure, etc. However, stretch database also has its own limitations which make it less likely to be used as of now with SQL Server 2016 RTM release. Hopefully Microsoft comes up with significant improvements soon in service packs or with another release in another couple of years, like it has been releasing since year 2008. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc.

Stretch Database in a SQL Server instance requires at least one table. Once we enable it at instance level, database level and table level, it then silently begins to migrate the historical data to Azure SQL Database on Microsoft cloud. If we are storing historical data in a separate table on-premise database, then we can migrate the entire table to Azure cloud. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration. However, most important question from many organizations management and DBAs or developers is how to decide if stretch database suits their environment or requirement. Below are some points to help decide if Stretch Database can help in meeting your requirements and solve the existing problems.

– Are you looking to store historical data, and do not want to get rid of very old data, as it may be still required to be accessed rarely, then stretch database will be of great help in reducing the storage and maintenance costs of the old data and still allows you to access the old data from the azure cloud.
– Are you looking for archival solution to archive old data from the frequently accessed production data, then stretch database feature is a very good solution to your problem, as you can archive old data on to Azure SQL database cloud, and best thing is you do not need to change anything in your application, as the data storage and access is taken care purely by SQL Server itself.
– If you are looking to reduce storage, compute and memory costs, then this is ideal solution.
– If you see that some tables are very large and causing issues with maintenance like backups, reindexing, stats update, etc, then archiving is best solution and stretch database will be very good solution.
– If your backup/restore are taking too long and missing SLAs, then moving old data to Azure cloud using stretch database will move old data, thus reduces the size of on=premise tables and thus reduces size of on-premise database and now the backup/restore times will be less and should meet the SLA requirements.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

How Does Stretch Database Works in SQL Server 2016

Leave a comment
SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Before deciding to this new feature in production environment, it is important to understand how does Stretch Database works and its behavior. First, to enable stretch database, we need to enable the instance level configuration option “remote data archive”, after which we will be able to Enable a Stretch Database for a database or table. Once we enable stretch database for atleast one database and one table, it will internally start to migrate our historical data to Azure SQL database. If the historical data is stored in a separate archive table already, then we can migrate the entire table, if table contains both historical and current data then we need to specify a filter predicate to select the rows which are to be migrated to the Azure SQL database. Some of the cool features with the Stretch Database is that it ensures that no data is lost in case a failure occurs during the data migration to Azure SQL database. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view also provides us with the status of migration.

Also there us option to pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth or to migrate data only during non-business hours or during low activity period. Another cool thing about using stretch database is that we don’t have to change existing queries or client applications. We can continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

Benefits at Glance of Using New SQL Server 2016

Leave a comment
SQLServerF1

Microsoft has released most awaited RTM version of SQL Server 2016 on June 1 2016. There was lot of buzz around with the release of new SQL Server 2016 and has been already downloaded by many community DBAs and developers to start testing or learning using the new features. Just like any other SQL Server version, even in new SQL Server 2016, there has been lot of new features introduced in areas like security, performance, scalability, stability, etc. Also there is support for SQL Server on linux in pipeline, which makes it much more interesting and to enter new companies which did not use windows operating system. As a DBA or developer it is very important to gets handon new features and suggest to the management how it will benefit the clients or customers or their own business with the new features. Always, test in a test environment thoroughly before deciding to move to production. Also, as like any new version, it is expected to have lot of bugs too, so it is up to you to decide whether to wait till a service pack is released or just go ahead and install it right away now. My preference is to start testing on test environment and see how application reacts to it, and if the new features are beneficial and performance has improved with the applicaion, then I prefer to go ahead with the new SQL Server 2016 on production environment.

Following are some of the important benefits of new SQL Server 2016 which we might want to keep on top of our head.
– Microosft claims Enhanced in-memory performance which provides up to 30x faster transactions, and more than 100x faster queries than disk based relational databases and real-time operational analytics.
– New security feature Always Encrypted which helps in protecting our data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without requiring any changes to the application.
– Built-in advanced analytics– provide the scalability and performance benefits of building and running advanced analytics algorithms directly in the core SQL Server transactional database.
– Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android
Simplify management of relational and non-relational data with ability to query both through standard T-SQL using PolyBase technology

– New features like Stretch Database technology keeps more of our historical/cold data at our fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.
– Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore our on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.
– Closer integration with Azure SQL Database which helps more customers comfortable to migrated to cloud with the new security features.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 
1 2 3 … 9