Choosing Where to Host SQL Server Database

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. When it comes to running SQL Server or hosting a SQL Server database, there are several options for DBAs or management which include On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS). There are various factors which can can consider about choosing which applications are better run on which of the above mentioned environment and which features impact on where the SQL Server instance and databases are better hosted in one of the above environments. Below are list of items to consider for choosing where to host SQL Server database from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) or SQL Azure database as service(PaaS).

SQL Server Version Support – If you are looking for flexibility and control over which versions of SQL Server instance and which patches to be applied then any one from On-premise physical server, On-premise Virtual server, Infrastructure as service(IaaS) can be your choice as all these support any version of SQL Server and patches to be applied based on DBA/Developer team recommendations. Where as SQL Azure database as service(PaaS) does not offer this flexibility and allows us to only choose from one the existing versions available and any new patches may be forced at times.
Security – For very critical applications, On-premise physical server, On-premise Virtual server are preferred as these are maintained at our own data centers and brings more control over security. Infrastructure as service(IaaS) can be used for sensitive applications which have high security requirements, but not too very highly critical.

Storage – In On-premise physical server, On-premise Virtual server, the storage support, configuration and its performance is purely determined by the storage teams as per the requirement from the DBAs, Developers and Management. For Infrastructure as service(IaaS) or SQL Azure database as service(PaaS), the storage and its speeds are to be chosen from available options and has different cost for different storage sizes and performance, so based on our requirement we can choose the require storage.

Backups – For On-premise physical server, On-premise Virtual server backups are to be taken care by DBA or backup team and can choose to use native maintenance plans, custom scripts or third party backup tools and can perform backups locally or to backup share or tape. In Infrastructure as service(IaaS) backups can be configured to store locally which is Azure storage. In SQL Azure database as service(PaaS) backups are taken care by Microsoft and we only have to choose how many days the backups are to be stored which decides RTO or RPO. Depending on the settings chosen the cost would be impacted.

Cost – On-premise physical server involves high cost for maintaining hardware, OS, Network, Storage, SQL Server, etc. For On-premise Virtual server the cost of hardware less compare to On-premise physical server as we can use one server host to host multiple guest systems, this reduces the hardware maintenance cost, but this will bring additional cost of administering virtualization, rest all costs remain same for Storage, SQL Server, etc. For Infrastructure as service(IaaS) the cost is further lower as this reduces the hardware maintenance cost as it is taken care by Microsoft, but other costs of Storage, SQL Server, etc will remain same. SQL Azure database as service(PaaS) is the lowest price option available as hardware, and many SQL Server operational costs are reduced as these are taken care by Microsoft.

Suited Applications – On-premise physical server, On-premise Virtual server are better suited for applications which are hosted on our own data center which avoids network latency between applications and databases. Mission critical, data sensitive and high performance required applications are better to be run on On-premise physical server, On-premise Virtual server where we want more control. Infrastructure as service(IaaS) is best for Dev, Test type servers or servers which are not mission critical, but still are important and expected high performance with varied performance at different times. SQL Azure database as service(PaaS) is best suited for new applications developer keeping in mind cloud technologies to take advantage of cloud features.

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

 

Pros and Cons of Running SQL Server On-premise vs Azure Cloud

SQLServerF1

Cloud solutions has been growing in popularity of recent with new offerings and more features added in different offerings and reduce or eliminating many traditional and operational overheads of maintaining hardware, servers, OS, Network, SQL Servers, patching, backups, High availability, Disaster recovery, etc. Depending on the type of offering choose, the cost will be impacted and features available. At present, SQL Server instances or databases can be hosted on different environments which include running SQL Server on physical hardware on-premise in client(our own) data center or running SQL Server on virtual machine guest system running on-premise or running SQL Server on Azure VMs(IaaS) or SQL Azure database as service (Paas).

There are many advantages as well as disadvantages of running SQL Server on above mentioned environments, with each one with their own pros and cons which are listed below.
Pros of running SQL Server on Azure
– Running SQL Server on Azure VMs will reduce the cost of hardware maintenance as it will be taken care by Microsoft, thus reduces the load on the data centers and reduces overall maintenance and costs.
– Depending on the type of Azure offering chooses, different administrative or operational costs are reduced. For example, using SQL Azure database as service PaaS will reduce the administrative tasks like Setting up and maintenance of hardware, Installation of SQL Server, maintenance tasks like patching, backups, High availability, etc are taken care by Microsoft. In Infrastructure as service offering IaaS, hardware costs are reduced.
– One of the important advantages of Azure VM and SQL Azure database as service are the flexibility of scaling up or down based on the requirement. For example, it is a common requirement for a test server with similar configuration of production specifications, which can be easily setup on Azure and once testing is complete, we can release or shutdown the Azure database, thus reduces the cost of maintaining the server outside of testing time, which can be significant. Another example, where we may need to scale up the performance of the server like shopping portal during thanks giving day or during festivals when there is high demand for the portal, Azure allows to quickly upscale the performance.

Cons of running SQL Server in Azure
– Although Azure provides cost effective cloud solution, but has its own disadvantages too. On SQL Azure database as service, there is not much control on the features or operations like patching, backups, etc.
– Also, another major concern about running SQL Server on Azure is security. Applications which store critical client data which comes under PII or finance related data, many organizations are not comfortable storing them on third party data centers where they do not have much control.
– The SQL Server instance databases main purpose is to store the data and allow it to be retrieved when required, so there is lot of dependency on the application and other services. So, it is important to consider where other dependent or related applications are running. For example, if applications and other services are running on-premise and SQL database on azure can introduce additional latency and any network issues can cause frustration to users, so any critical applications which are running on-premise may take advantage of SQL Server running on-premise too.
– Also, it is possible that some times the performance of SQL Server on azure is not predictable or consistent, because the SQL Server will be sharing the same hardware resources with other SQL Server instances or other applications, so heavy load from all servers or applications can lead to some resource saturation, thus causing bottlenecks at times, so performance is not always predictable on the azure.

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

 

Differences Between SQL Azure Database and SQL Server in Azure VM

SQLServerF1

Microsoft is making huge investments in cloud technology and is betting big for future growth in this area. Microsoft has started providing cloud services for various products, applications, Operating System or hardware support. Based on the type of service, Microsoft has made available different offerings which include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). Microsoft cloud support many applications or products out of which SQL Server is one of the Microsoft product which is supported on cloud by Microsoft. There are two different kind of offerings provided by Microsoft for SQL Server, which are Infrastructure as a Service (IaaS), Platform as a Service (PaaS).

Among these two services Infrastructure as a Service (IaaS), Platform as a Service (PaaS) offered by Microsoft, we a DBA or management, we need to understand what best suits our requirement and based on that we can choose the offering. It is important to understand the differences between these two offerings available for SQL Server, so that we can make an informed decision. Each offering for SQL Server from Microsoft has its own advantages and disadvantages interms if features, cost, High Availability, Disaster recovery, which operations tasks are taken care by Microsoft, etc. Below are some of the differences between SQL Azure SQL Database (PaaS) and SQL Server in Azure VM (IaaS).

SQL Azure database as service is best suited for new applications designed or optimized for cloud solutions or which depend on other cloud technologies, so that they can be used together. Developers building software-as-a-service (SaaS) applications can use Azure SQL Database to provide flexibility to support both explosive growth and profitable business models. On the other hand with Infrastructure as a Service (IaaS) is for applications which are already existing ones and cannot be changed easily and need to be migrated on to cloud without much changes to be made. Applications which are dependent on other on-premise resources are best suited for Infrastructure as a Service (IaaS) compared to SQL Azure database as service.

SQL Azure database as service does not support all features available in SQL Server and has many limitations for each feature, many of the features cannot be controlled by DBAs or developers and there is not much control for DBAs for operational tasks like patching, backups, HA or DR. Infrastructure as a Service (IaaS) is just like on-premise SQL Server instance on which DBAss or developers can make configuration changes, performs backups/restores, use required technologies for HA and DR purposes, etc. There are some limitations with SQL Azure database as service like max database size supported as of 2015 is only 500 GB, but this limitation does not apply for Infrastructure as a Service (IaaS). Another limitation include, SQL Azure database as service does not allow resources to be accessed from Azure to on-premise. Infrastructure as a Service (IaaS) does not have any such limitations.

In Infrastructure as a Service (IaaS), hardware, its maintenance costs are eliminated as it is taken care by Microsoft. SQL Azure database as service also eliminates the costs mentioned before, in addition to that, in SQL Azure database as service also eliminates maintenance and administration efforts and costs of patching, backups, HA, DR, etc. in SQL Azure database as service, there are different services available to choose for High Availability and disaster recovery where Point in Time Restore, Geo-Restore, and Geo-Replication to increase can be chosen to increase business continuity. In Infrastructure as a Service (IaaS), the High availability and disaster recovery is to be taken care by DBAs and we can choose any technology of our choice and we are responsible for administering, monitoring and fixing any problems.

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

 

Different offers from Microsoft Azure for SQL Server

SQLServerF1

Cloud solutions has been gaining increased support and many customers moving their data on to cloud technologies or planning to move in future. The reason for increase in popularity of the cloud technologies include that it reduces the operational and maintenance cost of hosting the own hardware and its day to day maintenance. Many operation tasks such as backups, patching, etc can also be taken care by the cloud solutions depending on the kind of offering selected. Microsoft and other companies like Amazon are investing a lot on the cloud technologies and trying to provide features that match the on-premise servers and applications or more than that.

Microsoft is offering its cloud services in different types depending on the services offered. These include Infrastructure as a Service (IaaS), Platform as a Service (PaaS), and Software as a Service (SaaS). These offerings are generic and is applicable for different applications which include SQL Server too. Coming specifically to SQL Server, Microsoft is offering two services SQL Server on Azure VMs (IaaS) and SQL Azure Database (PaaS).
SQL Server on Azure VMs (IaaS) – This is similar to on-premise SQL Server running on a virtual machine which is running on a host system whose hardware is maintained in Microsoft’s data center. In on-premise servers, DBAs or management discuss and decide on the hardware required like CPU, Memory and storage and the Operating System required, on top of which DBAs install SQL Server instances and create or restore databases. in SQL Server on Azure VMs (IaaS), Microsoft provides required hardware(CPU, Memory, Storage) and provides a VM with required Operating System too. DBAs can install SQL Server instance on this VM, configure and manage and administer it just like an on-premise SQL Server instance. If any support is required related to hardware, then DBAs are required to contact the Microsoft support. This is best suited for applications which does not need much changes after moving to cloud technology and where management want more control on the SQL Server.

SQL Azure Database (PaaS) – SQL Azure on the other hand is a Database as a Service offering of SQL Server. This SQL Server runs on a VM which is maintained by Microsoft and this server will be hosted on Microsoft Data Center. In this offering DBAs does not need to install, or manage things like patches, backups, upgrades, etc, as these things are taken care by Microsoft team either through automated scheduled tools to by manual maintenance depending on the type of the task. There are many things which are offloaded from DBA like high availability, disaster recovery, patch maintenance, etc. If high availability is required, we need to choose the right offering and license which provides the high availability. Here we can choose which server needs which features and based on the features used and the usage the price will be decided. DBAs or managers get access to Azure portal where we can setup new SQL Azure instances, configure them, choose required features, and find the connection strings to be used in the application or to use in SSMS to connect to this instance locally from out laptop or other devices. From SSMS or other applications, we can create databases, tables, users, etc and use the databases to store and retrieve the data. This is best suited for new applications which just need access to data and does not want to spend time and efforts on maintaining SQL Server like backups, patches, high availability, etc. One of the major limitation in this is that the existing applications are required to be rewritten to able to work on SQL Azure.

During initial stages when the SQL Azure service was introduced, there were lot of limitations, so was not used much, but as in 2015, the offerings have increased a lot and can support many applications to move their data to SQL Azure and use it without much issues. However there are still many limitations, when compared to on-premise SQL Server instances, which almost does not have any limitations.

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

 

Memory Counters to Check on Virtual Machine Running SQL Server

SQLServerF1

These days it is common to see SQL Server instances running on virtual machine. There would be multiple virtual machine guest systems running on one host server. To have more clarity, lets look at some terms.
Host – This is the server which runs on top of the hardware and manages and allocates all server hardware to all virtual machines.
Guest – This is the virtual machine which has been allocated few hardware resources(CPU, Memory, Storage) from the available pool of resources on the server.

As one virtual machine host system can create and host multiple guest virtual machine systems, all these guests will share the available CPU, Memory and Storage on the server. As a DBA, one of the responsibility is to ensure that the SQL Server instances running the virtual machine guest does not face any performance problems, but due to many configuration settings at the host and guest level, it is possible that all hardware resources allocated the the virtual machine guest system cannot be fully utilized by the SQL Server, instead the host may apply some further limits and does not allow the SQL Server to use the resources allocated to it. Sometimes, when the other applications on another guest system consumes more resources, it is possible that remaining guest systems on the same host may be impacted.

Memory is one of the important resource to ensure high performance, as reading data from memory is faster compared to reading from disk drives. Out guest system may be allocated certain amount of memory and DBAs may have set certain max server memory cap for SQL Server instance, but it is possible that SQL Server could never be allowed to use the allocated memory or is forced to release the memory allocated to it, so it is important to monitor if any memory is being asked to be released from the SQL Server. In general, DBAs monitor general memory counters like Server: Available Memory, Buffer Manager: Buffer cache hit ratio, Page Life Expectancy, Memory grants pending, Memory grants outstanding, free pages, total server memory, target server memory, etc. However there are also some memory counters to be monitored on virtual machine which include below.

Memory Limit(MB) – Amount of memory allocated to the guest.
Memory Reservation(MB) – This is the lowest amount of memory the guest will have. Although this is 100% of the guest’s memory, but that’s not always true. For SQL Server, generally the reservations is expected at 75% of the guest memory allocated.
Memory Ballooned(MB) – Memory released from the guest by Balloon driver.
Memory Swapped(MB) – Current amount of guest physical memory swapped out to the virtual machine’s swap file by the VMkernel. Swapped memory stays on disk until the virtual machine needs it.

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

 

Considerations for SQL Server Instances and Databases for Virtualization

SQLServerF1

Virtualization has growing demand and implementation after many success stories. Previously SQL Server instances were installed on servers with their own physical hardware, but that also introduced challenges of effective usage of the available hardware. Over a period of time, the hardware has dramatic changes which made available high configurations for cheaper price and smaller sizes and servers have become more powerful and can support a lot of CPUs, high amount of memory in terabytes, support for high storage, local, SAN and SSDs. So, virtualizaiton helps in using these powerful tools in an effective manner by allowing to create multiple virtual machine guests on one host server, thus making the hardware usage effectively.

With latest virtualization technologies have their own virtualization operating system which is installed on top of hardware even before the installation of the Windows Server Operating Systems. Once the Virtualization OS is installed, now it allows creation of multiple virtual machines each with certain set of CPU, Memory and Storage. Each VM acts as independent server and any operating system can be installed and any applications can be installed, this helps segregate multiple applications running on server server to running each application on its own virtual machine. When it comes to SQL Server, where the SQL Server instances are spreads cross many different servers need to be consolidated and moved into their own virtual machine or combine multiple databases and host them on their own VM and SQL Server instance.

To consolidate multiple SQL Server instances on different physical servers into their own VM ot on to a single SQL Server instance, we will need to follow a proper approach or consider various options to choose what suits the requirement better. Below are some of the options to consider inorder to choose which databases, instances and servers can be consolidated.

Version upgrade paths – Need to understand if we can group the databases which does not have issues upgrading to higher versions as soon as one is available. If the databases cannot be upgraded and requires lot of planning and testing before hand then such databases are better of to have their own dedicated VMs.
Maintenance Windows – Need to group databases which can have maintenance performed at certain time, such can be hosted on same VM and which have different maintenance times, can be moved to their dedicated VMs.
Security – Databases or applications which require high security are better of hosted on their own VMs.
Performance – Critical servers which require high performance can be left running on its own physical hardware or on a server which is shared by non-critical applications to avoid other servers using high amount of resources from the host server.
High Availability – Databases or applications which are very critical and require high availability needs their own dedicated VMs.
Licensing – If licensing cost needs to be reduced, then need to try and group databases with similar characteristics on one SQL Server instance or few SQL Server instances on different VMS.
Backup and Restore – Databases or applications which require very good planning for recovering data and time taken to recover the data is very small, such are to be hosted on own servers.

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

 

Tips to Improving SQL Server Backup Performance

SQLServerF1

Backups are one of the most important and core DBA task which every DBA needs to expertise. If there are any short comings or issues with backup planning, implementation, monitoring and testing, then it can result in data loss or can increase the time taken to recover the data with data loss. SQL Server supports multiple databases on SQL Server instances with each database size can be small of few MBs to very large in 100s of GBs to Tera Bytes TBs of late. There is additional care to be taken to plan and implement back strategy for large databases as the time taken to perform the backup increases significantly, so we need to diligently use the different backup methods available to backup the large databases like performing weekly full backups, daily differential backup and every 10-15 minutes log backups. The backups are important even when there are High availability and disaster recovery solutions implemented.

As a DBA some times we may need to look into possibilities of speeding up the backup process to save time taken for the backups of critical databases which are used 24×7. Performing backup can slowdown the databases to an extent due to IO load put by the backup on reading from database files and writing to backup file. There are different ways which we can consider to speed up the SQL Server database backup process to avoid problems for critical servers. Some of the methods or things which we can consider are below. It is important to note that some of the below mentioned options may be available only in latest versions of SQL Server instances, like backup compression available only starting with SQL Server 2008.

– Starting with SQL Server 2008, Microsoft introduced compressed backup. Using the backup compression not only reduces the size of the backup files, but also reduces the amount of IO performed to write to backup files, thus reducing the amount of time taken to perform the backups.

– SQL Server backups allow us to perform backup of one database into multiple backup files, thus increases the use of parallelism which speeds up the backup process. If we point the multiple backup files to different disk drives, then this will further spread out the IO load and makes the backup even faster. We can split database backup to be performed to many backup files, but we need to find the best number of files, as if we use too many files can impact negatively too.

– Some environments, it is common to perform backup directly to the network share or tape drives, which will cause the backup to take more time because the data has to be moved across network. If the backup of large databases is taking long time, then we can consider performing backup locally first to dedicated backup drive and then move the backup file from local backup drive to backup share or tape drive.

– We need to ensure that file system level backups and SQL Server backup timings does not overlap, as both running together will slowdown the system and causes the backup to take more time.

– For large databases we can choose alternative backup plans like performing weekly full backups, daily differential backup and every 10-15 minutes log backup, this we can restrict long time of full backups to weekly once. Differential backups are often fast compared to full backups. It is important to move all full, differential and log backups to backup share or tape so that they can be used in case the server or drive where backups are performed is lost or corrupted.

– We can also use some of the backup parameters like BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE to improve the backup performance. There is no good or bad values for these parameters, it is trial and error approach, where we need to test different values and come to conclusion on what works best for your environment.

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

 

Planning and Designing a SQL Server Database System

SQLServerF1

Planning, Designing and setting up a new server to host SQL Server instance and databases and customers data requires lot of knowledge about the process, requires lot of practical experience of solution delivery and track record. There are many factors which are involved in planning and designing a SQL Server database system. The SQL Server should be able to provide best performance, security, strong disaster recovery, high availability, easy scalability, etc. There are various processes, documentation, pre-planning, delivery, coordination, communication, testing, etc.

In this post, we are trying to highlight some of the top steps architects need to consider which planning and designing a strong SQL Server system for hosting critical data. First, we need to understand the requirement of what data will be stored, like OLLTP, OLAP, etc. What kind of data is stored, meaning structure data, unstructured data or semi-structured data. How many users will be accessing the system and over a period of 5 or more years what is the expected growth in number of users and amount of data. What kind of security measures are expected. Once we have the requirements available, these needs to be documented and signed off by all stake holders.

Next comes planning phase on how these requirements can be implemented, like how many resources are required to get this implemented and from which technology these resources are need to be from and the level of knowledge expected from the resources. Decide on a time frame on when this needs to be completed and this needs to be realistic to consider planning, implementation, testing, making any further changes, testing, planning and going live with the system. Need to select the versions like the operating system, SQL Server version, hardware to be used, etc. Planning of the hardware is very important as it is the one which decides how we can achieve performance, security, disaster recovery, high availability, scalability, etc. Need to consider future growth as well, because at the time when the plans are laid, the data size may be small and the number of users expected to use the system may be small, but over a period of time this may change and will require additional CPU, Memory, Disk or Network resources, so planning for future growth expectations will reduce the number of attempts to meet the growth requirements in future.

Next steps would be to buy the required hardware as planned before, and using the resources to build the server with best practices, get the Operating System installed with latest patches, ensure latest drivers are installed. Next install latest version of the SQL Server and patches and configure the OS and SQL Server settings as per best practices. Once the server is ready, restore the databases configure logins, jobs, etc and handover to testing. If testing is successful, then we can move forward with going live with the system, if there are any problems noticed, then we need to identify the cause and fix the issues and test again to ensure everything is working as expected. Load testing is one of the important tests to ensure best performance. Once testing is successful, need to involve all resources and plan step by step how to go-live with the system and perform a test go-live if possible and test the system. If all is good, then final go-live can be performed and new system to be monitored for few days to ensure there are no issues. We can then handover the server to the teams which will maintain and administer it going forward.

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

 

The query processor ran out of internal resources Error in SQL Server errorlog

SQLServerF1

There are many number of errors documented for SQL Server and are raised when certain error condition occurs which range from syntax errors to problems with instance, databases, jobs, performance, features, etc. When a particular error is received, the error message will have some information regarding the kind of error and what might have caused the error. Sometimes, it is found that the errors are not detailed or misleading and make troubleshooting move in wrong direction or can cause slowdown of resolution the errors.

Out of many errors, some errors are related to performance and the error message may not be enough to understand the reason of the failure. Below is one such error message which occurs due to performance issues with the server performance or with a specific stored procedure or a batch or a individual SQL query. When ever a query hits SQL Server instance, a query plan is created if it is the first time, if not, SQL server optimizer will check for existing plans and reuse if one is already present. There are various reasons the query plan generated may not be optimal and takes more time to complete and sometimes the query can fail altogether with different errors. Below is one such error.

Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

This particular error mentioned above is is a rare event and only expected to occur when a very extremely complex queries or queries that reference a very large number of tables or partitions is received by SQL Server. There can be different reasons on what a complex query is referred as, it can happen when attempting to select records through a query with a large number of entries in the “IN” clause like greater than 10,000. There are also some bugs or known issues that can cause this error and some connect bugs are raised too. You can use SQL Server profiler or extended events to track and identify the query causing this error. If this error is recurrent and happening on certain day or time, then we can check if there are any jobs running at that time leading to this error.

The resolution for this error is to simplify and rewrite the complex query. We can also try and divide and get part of the query working and then use extra joins and conditions. We can also use temp table and use temp tables in joins to avoid this error from occurring. We can also try other alternatives like we can try to run the query using the hint option like force order, or option hash join or merge join or using trace flag 4102 with a plan guide. Enabling Traceflag 4102 will revert the behavior of the query optimizer to older version of optimizer for handling semi-joins.

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

 

CPU Affinity Settings on Server with Multiple SQL Server Instances

SQLServerF1

It is common to see multiple SQL Server instances being installed on one server. The intention behind this is to reduce the licensing cost related to Operating System and also to use the hardware resources effectively. Also, having multiple instances separate maintenance and provides more security from not allowing users of other applications to connect to different SQL Server instance databases. With introduction of visualization, the reason for having multiple SQL Server instances has reduced a bit by allocating on VM for each SQL Server instance thus avoiding issues with maintenance of OS or hardware. However, still there are older systems or some newer systems too which still use multiple SQL Server instances on one server.

There are some disadvantages of running multiple SQL Server instances on one server like, if built-in administrators group is added to SQL Server instance as sysadmin, then all local administrators get access to SQL Server instance by default which can be security issue, irrespective of it, a local administrator can gain access to SQL Server instance without being having permissions to the instance through backdoor by starting SQL Server instance in single user mode. Also, there can be performance issues to all SQL Server instances, if one of the SQL Server instance starts to behave bad due to bad queries, improper index and stats maintenance or bad application design.

One of the performance issue you notice on servers is high CPU usage causing performance issues. There can be one of more instances consuming more CPU thus starving other SQL Server instances, thus overall server performance degrades and application users will start to complain. One of the ways to avoid each SQL Server instance consuming more CPU and causing contention is to allocate each SQL Server instance specific CPUs available on the server. For example, if a server has 16 processors and if there are 4 SQL Server instances running on the box, we could analyse CPU requirements for each SQL Server instance based on number of databases, size of databases, number of concurrent users and max CPU required during high load, and then assign specific number of CPUs to each instance. So, for one instance if it requires more CPU usage, then allocate 8 processors to user and for remaining 3 instances use assign remaining available processors based on the usage. If all SQL Server instances usage requirement is greater than available processors, then we will need to plan to move one or more SQL Server instances on to another server.

We can use sp_configure of from instance properties, choose which CPUs to be allocated for which SQL Server instances. There is also option available to choose IO affinity mask, so do not change it, instead just change the affinity mask or CPU affinity mask. There can also be affinity 64 settings, which you can ignore and just use affinity setting or CPU affinity setting. Do not change IO affinity setting. For each SQL Server instance choose different CPUs to avoid overlapping.

Also, it is good to consider reviewing and adjusting the Max Degree of Parallelism (MaxDoP) setting as per best practices and the allocated number of CPUs for each SQL Server instance.

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

 
1 2 3 4 558