SQL Server 2012 Performance Sensitive Configuration Parameters

SQL server has plethora of configuration options and mainly can be divided into three levels – Server, Database and Connections. Many options overlap each other and hence will be separated by topics.  This article doesn't cover all options but only the most important one in terms of performance.

Startup Parameters
Flag -T834
»        Enable SQL Server to use larger pages
Flag –x
»        Disable SQL Server Performance counters and avoid potential overhead
Startup Stored Procedures
Scan for startup procs
»        Mark for automatic execution of stored procedures at SQL Server startup
»        Try enabling it when having an expensive query that takes a long time to run at first execution
Memory Configurations
Dynamic Memory Minimum
»        The goal is to have enough memory available so that windows don’t need to swap pages from physical memory to virtual memory support file (pagefile.sys)
»        It prohibits SQL Server from reducing memory below a certain point
Dynamic Memory Maximum
»        It prevents SQL server to grow beyond point where it can start contending with other applications or operating system
Fixed Memory Size
»        Usually don’t see any performance gains from switching from dynamic memory to fixed memory
Minimum Query Memory
»        Increasing this value may provide better performance for some queries but might cause risk of memory shortage
Query Wait
»        Alter it if there is a large query and taking longer time to complete
Index Create Memory
»        The amount of memory SQL server uses to perform sorts when creating an index
Processor Configurations
Processor Affinity
»        Allocate particular CPUs for disk IO processing and non-disk related CPU requirements
Maximum Worker Threads
»        Depending on the number of connections and the percentage of time those connections are idle, making number of worker threads lesser than number of connections force connection pooling, conserve memory and improve performance
»        The default value indicates SQL server automatically determines the correct number of active worker threads based on user requests
Boost SQL Server Priority on Windows
»        The default value is set to normal (7) and it is recommended not to change it
Lightweight Pooling
»        Use it to reduce the overhead of frequently switching processes among the CPUs
»        It is recommended to use the default value though i.e. 0
Max degree of parallelism
»        SQL Server detects the best number of processors to run a single statement for each parallel plan
»        It can be used to limit the number of processors to use in a parallel plan execution
»        The default value of the ‘max degree of parallelism’ option is 0, which tells SQL server to use all the available processors
»        The performance of SQL Server can degrade if more than 8 processors are used in a parallel plan
»        For servers that have NUMA configured, the option should not exceed the number of CPUs assigned to each NUMA node
»        For servers that have hyper threading enabled, the option should not exceed the number of physical processors
Cost threshold for parallelism
»        It is recommended to use default value
Connection Configurations
Max Concurrent User Connections
»        The default is unlimited user connections
»        Each connection has overhead whether is used or not
»        Maximum, 32,767 user connections are allowed
Query Cost Governor
»        The query governor limits the queries that SQL server can run according to estimated query cost on a specific hardware configuration
»        Use the query governor cost limit option to stop long-running queries before they start and thereby help prevent system resources from being consumed by these long running queries
Network Packet Size
»        It is recommended to use default value
»        Use it only when the data passed tends to greatly exceed the default size i.e. 4KB such as large text or image data


Comments

Post a Comment

Popular posts from this blog

Test Case Template for Project Using Agile Methodology

User Story One template that can be used while writing user stories could be – “As a (role) I want (functionality) so that (benefit).” For example – As a ‘Bank Customer’, wants to ‘Transfer Funds’ to one of their friends / acquaintances online, which is fast and auditable. Or, the user story could be described as a single line item (2-5 words) as well. For example – Fund Transfer Test Cases Few of the test cases (summary) for above user story could be as follows.  These test cases should be developed with the help of Business analyst. Transfer fund when fund is in range of acceptable limit and existing balance is sufficient. Or, Fund transfer amount less than existing balance Transfer fund when fund is in range of acceptable limit but existing balance is not sufficient. Or, Fund transfer amount more than existing balance Transfer fund when fund amount is beyond acceptable limit. Or, Fund transfer amount more t...
Read more

Measurement & Metrics

"You cannot improve what you cannot measure" A lot had been talked and spoken already about ‘Measurement & Metrics” in Software Testing.  In this article I am not saying that something new will be written or discussed, but my idea for this article is to summarize on this topic from various different sources. Measuring, Monitoring and Acting on it are key roles of any manager in any industry and Software Testing is no different.  A lot of times it has been seen that KPIs are being measured and monitored but not being acted.  In that case, this whole exercise becomes worthless.  Sometimes, the measurements themselves are not collected and reported correctly, which is more dangerous than not doing this activity at all. Other times, it had been observed that there are numerous KPIs are being monitored without having any goals in mind.  As per me, the number of KPIs that should be measured and monitored should be minimal and any addition to new KPIs sho...
Read more

Performance Test Run Report Template

You have gathered requirements from clients, identified key critical business scenarios, analyzed the workload distributions, found out performance goals and requirements, identified  tool for generating load, setup the test environment, designed the scripts, executed the performance run, collected the raw data, analyzed it, what next?  Send information to stakeholders? No!!  Wait!! It needs to formatted, customized for different stakeholder(s) and reported accordingly. This article suggests one of the ways to report performance run results to client especially if the performance testing activity has been outsourced to yours company.  It discusses the performance report template and importantly the reasons for including the items in the report. The performance report template contains information by data collected from client side only and doesn’t discuss / talk about data from server side like processor utilization etc. Broadly the performance report has been divi...
Read more