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

 

SQL Server High CPU Issues Due to Edition Limitations

SQLServerF1

There are variety of performance issues that can cause SQL Server performance issues. Performances can range from low server Hardware configuration, improper OS or SQL Server settings, fragmentation, blocking, no proper maintenance like index rebuilds or stats updates, etc. It is not easy to identify or pin point what is causing the slowness or performance problems with the SQL Server. As a DBAs we will need to review everything related to the hardware, OS and SQL Server to able to find the root cause of the issue.

There are many cases where performance issues are caused due to low server hardware configuration like lack of CPU power, or insufficient memory available or under-performing disk IO. We can look at various parameters like perfmon, SQL Server dmv statistics, query waits, etc to be able to understand if there is real bottleneck with the hardware and if we need to upgrade the hardware for better performance. However there can be situations, where the server although has a good hardware configuration interms of CPU, Memory or Disks, improper settings on some limitations cause the SQL Server not to use all of the available hardware on the server.

One of such issue related to the slowness caused by CPU usage due to limitations on the SQL Server edition been used. On one of our client server, there were multiple SQL Server instances installed. There was a monitoring tool which used to monitor the server and used to raise intermittent alerts for high CPU usage. Upon connecting to the server and verifying, the CPU usage was only reported as 50% usage on the server. Users also used to report about the performance issues some times, but not frequently.

Upon checking the server, from task manager the CPU usage was reported around 50% and this server has 8 sockets with one core each, The server has Windows Server 2012 as the Operating System and the task manager, by default combines all the processors available on the server and shows the average usage of all the processors. There is also an option to see the detailed view of CPU usage of each individual processor usage, checking which we found that only four processors are pegged to 100%, where as rest 4 CPUs were having negligible usage. So, it appeared that the SQL Server instances were using only 4 CPUs thus the high CPU usage with the four CPUs only.

Upon checking further, we noticed that all the SQL Server instances installed on the server were Standard Edition. Checking the documentation showed that SQL Server standard edition can use maximum of 4 sockets or 16 cores, which ever is less. Similar limitations on other SQL Server 2008 R2 or SQL Server 2005 versions of 4 sockets only.
https://msdn.microsoft.com/en-us/library/ms143760(v=sql.110).aspx

We can also check and confirm this on SQL Server 2012 instances by checking the SQL errorlog which will have a message like “SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing”.

For SQL Server 2008 R2 or SQL Server 2005, we can query sys.dm_os_schedulers to see how many processors are been used by the SQL Server instances.

The server where we had issues had multiple SQL Server instances, so we had to use processor affinity mask settings and map each instance to use first 4 or last 4 processors allowed us to use all the available processors on the server, thus reducing the CPU contention.

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