Reasons why sql server is slow ?

   5 Reasons why SQL Server is slow

   1.     BALANCED POWER PLAN

       2.     DEFAULT PARALLELISM SETTINGS

       3.     DATA TYPE MISMATCHES

       4.     FUNCTIONS ON COLUMNS

       5.     MAX SERVER MEMORY AT DEFAULT SETTING

               1.     BALANCED POWER PLAN:

                                     Windows Server 2008 introduced Power Plans. The default power plan is “balanced power plan”—which means that Windows may throttle the power to the CPUs to save energy. That sounds great in terms of green computing, an initiative that focuses on energy-efficient computing, but it is just not appropriate for SQL Server. This is particularly true with today’s per-core licensing of SQL Server. If you are willing to run SQL Server at 67% or 50% CPU power, you would be better off disabling one-third or half of the CPUs and saving hundreds of thousands of dollars in licensing.

The concept sounds good on paper: it throttles CPUs down when they are idle and back up when the server is busy. In reality, the CPU-throttling mechanism does not respond very well to CPU pressure. It requires a long, sustained period of very high CPU utilization to trigger a power increase in CPUs. In short, it’s not responsive to high CPU events. But that’s not even the worst thing about the CPU power being throttled.

When the CPU power is throttled down, everything runs slower on SQL Server. It does not even take high CPU utilization to feel the hit from power throttling. If the CPUs are throttled, everything takes longer to run. Even simple queries on a non-busy server can take up to twice as long to execute. SOS_Scheduler waits and lock waits may go up because contention on CPU time and resources are increased.

When every transaction takes longer, it can have a domino effect. The chance for a deadlock to occur is greater; concurrency is reduced because more queries end up running at the same time. The performance impact of this setting can manifest itself in many different ways.

There are two easy ways to see if the CPUs are getting power throttled. You may already be aware of the very popular free tool “CPU-Z,” downloadable from www.cpuid.com . This tool can be run as a stand-alone executable on a server, does not require installation, and is safe to use on a production server.

                  

The image shows the output from a real production server I investigated for CPU pressure several years ago. The “Specification” on the output is the speed that the processor is rated for, and the “Core Speed” is the actual speed (or power) at which the CPU is running. In this particular case, the rated speed was 2.27 GHz and the actual speed was 1600.0 MHz (or ~1.56 GHz). This means that these CPUs were running at about 67% power. When we changed the power plan from balanced to high performance on this server, the CPU pressure immediately resolved and did not return.

Windows Server 2008 R2 introduced a new performance counter for tracking throttling of the CPU power: Processor Information\% of Maximum Frequency. This counter shows you the power level of the CPUs as the current percentage of the maximum frequency. If I had been tracking this performance counter for the above referenced SQL Server, this counter would have shown a value of approximate 67%.

You can change this setting via the Power settings in Control Panel on most servers. A few server models must have power configured in the BIOS, so if the CPUs are throttled while on the high performance plan, you may need to disable power throttling in the BIOS. Some server models can also override OS power setting via the BIOS. This makes it easy for infrastructure engineers to ensure that CPUs do not get throttled without having to interact with the operating system.

In addition to setting the power plan via the Control Panel, there are a couple of methods I have outlined via blog posts for setting the power plan programmatically. You can set it via the command line using the method outlined here: Enabling High Performance Power Plan Via Command Line (http://blog.idera.com/sql-server/performance-and-monitoring/enablinghighperformancepowerplanviacommandline/). Or you can set it via PowerShell as described here: Enabling High Performance Power Plan via PowerShell (http://www.sqlsoldier.com/wp/sqlserver/enabling-high-performance-power-plan-via-powershell).

There is no legitimate reason why CPUs for production SQL Servers should be power-throttled. All production servers should run in high performance mode all the time. If you are running with a balanced power plan to save energy, you are essentially throwing bags of money away because you can get better performance with fewer fully-powered CPUs than with a higher number of throttled CPUs. Changing this setting when your CPUs are throttled is basically free performance.

      2.     DEFAULT PARALLELISM SETTINGS:

                                 Parallelism is a very important feature of the SQL Server database engine. In general, it is a crucial element of getting good performance for larger queries. However, in certain scenarios, the parallelism settings can cause performance problems. The best way to handle these scenarios is to change the settings to more appropriate values. There are two instance-level configuration settings that you should consider changing from the default value: Maximum Degree of Parallelism (MaxDOP) and Cost Threshold for Parallelism.

It is a rare edge case where performance actually benefits from disabling parallelism. You will hear advice from time to time suggesting you should disable parallelism—often in response to high CXPacket waits. Be wary of any advice to disable parallelism (setting MaxDOP = 1).

CXPacket waits are a symptom of parallelism and are not necessarily a problem themselves (see “The Barking Dog Analogy” at http://www.sqlsoldier.com/wp/sqlserver/thebarkingdoganalogy). Your goal with parallelism is not to get rid of it—your goal is to find the right balance between parallelism and concurrency. It is not possible to know the exact best settings for parallelism without testing a real production workload. The best any of us can do is to provide guidelines as a starting point and then monitor and adjust as needed.

Most SQL Servers today are NUMA (non-uniform memory access) machines with a high number of CPUs designed to handle very large workloads. Conventional wisdom regarding the MaxDOP setting relies upon several conditional points that you must consider. The following guidelines are for total logical CPUs.

•       If the machine is SMP (symmetric multiprocessing) and the CPU count is ≤ 8 CPUs, leave it at the default setting of 0.
•                        If the machine is SMP and the CPU count is > 8 CPUs, cap the setting at a maximum of 8.
•                         If the machine is NUMA, then cap the setting at the number of CPUs per NUMA node, or 8—whichever is less.

For most machines in use today, you mostly just need to focus on that last recommendation in the above guidelines. Over-parallelism can be a problem because it decreases concurrency and can cause queries to back up on worker threads waiting for time to execute on a CPU. For NUMA machines, over-parallelism is common if you do not restrict parallelism so that a query does not exceed the total number of CPUs on a single NUMA node. With few exceptions, SQL Server will opt to keep a parallelized task local within a single NUMA node when the number of parallel threads is high. This can lead to some threads of the same query sharing CPU time with other threads of the same query. If some CPU threads are handling multiple parallel threads of the same query, and some are handling only single threads, then the workload will be imbalanced—some threads may complete more quickly than others. Since all threads must complete for the entire parallel task to complete, some threads will be left sitting idle, thus causing CXPacket waits.

The other setting that you should consider changing is the “Cost Threshold for Parallelism.” The default value for this setting is 5. Cost is an estimate of how much work will be involved to complete a particular task in a query plan. The calculation is based on the CPU ticks of the original developer’s workstation at the time the feature was written. That was a long time ago, and the cost value alone is not very meaningful

However, we can use the cost estimates in query plans to understand whether or not a task is a candidate for parallelism. We can use these cost estimates to determine a threshold for small queries that we don’t want to parallelize. With the default setting of 5, queries do not have to be very large to be a candidate for parallelism. Increasing this value will prevent parallelization of small queries and free up more CPU threads for processing other queries. The smaller queries will take a little more time to complete, but it will allow you to increase concurrency without reducing the MaxDOP for large queries.

On new SQL Servers that do not have a known workload which I can use to identify an appropriate value for this setting, I generally set cost threshold to 50. Experience has shown me that this is a good starting point for this setting and will only occasionally need further adjusting.

One of the best things you can do in terms of managing parallelism is to tune queries to reduce the amount of IO they must perform. This, of course, requires a lot more work than just setting the cost threshold and MaxDOP settings to appropriate values. For a quick performance gain, change these two settings.

       3.     DATA TYPE MISMATCHES:

                             Causing implicit conversions in SQL Server can be very common if you’re not careful. Often, these conversions due to mismatched data types have a neglible cost. The amount of work required to simply convert a value to a different data type is generally very miniscule, and you cannot detect the difference in query cost. Other times, throwing a conversion into the mix can cause a table or index scan instead of a smaller, more efficient seek.

When there is a data type mismatch in the criteria of a query, in either the WHERE clause or a JOIN statement, the conversion may be applied to the data column rather than to a constant value. In order to ensure criteria are matched correctly, the entire column of data may have to be converted before values can be converted. I say “may have” because each version of SQL Server gets a little bit smarter about stuff like this.

To demonstrate this, I set up a demo using the AdventureWorksDW2012 sample database. I select a table with an nvarchar column and compared the differences between joining the column to a varchar column and to an nvarchar column. The table and column I chose were dbo.FactInternetSales.CarrierTrackingNumber.

First, I needed to create a couple of tables with data to use for the test, one with a varchar column and one with an nvarchar column. I also added an index to CarrierTrackingNumber so that an index seek would be an option the optimizer could choose.

-- Set up tables for join

Select Cast(CarrierTrackingNumber as varchar(25)) As CTN

Into dbo.FIS

From dbo.FactInternetSales;

Select CarrierTrackingNumber As CTN -- nvarchar(25)

Into dbo.FIS2

From dbo.FactInternetSales;

-- Add an index that can be used for the query

Create Index IX_FactInternetSales_CarrierTrackingNumber

On dbo.FactInternetSales(CarrierTrackingNumber);

The next step was to query the tables joined on the mismatched columns and the matched columns and compare the plans and execution times.

-- Query join with mismatched data type

Select FIS1.CarrierTrackingNumber

From dbo.FactInternetSales FIS1

Inner Join dbo.FIS FIS2

On FIS1.CarrierTrackingNumber = FIS2.CTN;

-- Query join with matched data type

Select FIS1.CarrierTrackingNumber

From dbo.FactInternetSales FIS1

Inner Join dbo.FIS2 FIS2

On FIS1.CarrierTrackingNumber = FIS2.CTN;

The query plans clearly show that the query with the mismatched data type has to perform an index scan of the index I added, whereas the query with matched data types can instead perform a seek. An oddity: when comparing the query plans to one another, it shows the optimizer thinks the first plan (with the mismatched data types and index scan) is more efficient than the second (with matched data types and an index seek). However, running the queries individually shows the truth. The query with mismatched data types and an index scan takes 2 minutes and 33 seconds. The query with the matched data types and an index seek takes less than a second.

   

If we query the same table with a varchar string or variable instead of a join and examine the query plan, we see that SQL is actually smart enough to convert the constant value to nvarchar and perform the comparison rather than following the rules for conversion. Older versions of SQL Server may not make this same choice. As I said earlier, SQL Server gets smarter about things like these with every major release.

When you need to optimize a procedure or query, keep an eye out for data type mismatches. The query plan can be misleading about the impact that it has, but correcting these mismatches can deliver a huge boost to performance. I’m not advocating that you go out and immediately start scanning queries for data type mismatches, but when you do need to tune a query, it’s an easy performance win. Or, if you are reviewing code for others, it’s good to keep an eye out for these mismatches.

       4.     FUNCTIONS ON COLUMNS:

                             Developers love code reusability. In fact, who doesn’t love it? Over the years, however, DBAs and SQL Developers alike have also learned to hate functions. It does make developing easier when you need to include some complex piece of logic repeatedly in queries, but it often comes at a high cost.

There are several things wrong with applying functions to a column in queries. Whether it is in the criteria or in the SELECT clause, it can have a huge performance impact. When used in the criteria of a query (WHERE or JOIN clause), a function on a column can force an index scan instead of an index seek and execute the function one time for each row in the table. This behavior is similar to row-by-row looping execution. In other words—it’s painful.

Functions can also wreak havoc with query plans because the cost of a function won’t show up in query plans—and the plans may look like the function is presenting no problem at all. You can get a better idea of the true cost of the function by getting the plan for the code inside the function and then executing it in a loop equal to the number of records it will be expected to run against.

Some may think this applies only to user-defined functions, but even built-in SQL functions can have this problem. I see them used frequently without any concern to how their usage affects performance. Let’s take a look at a simple example.

Once again, we will use the dbo.FactInternetSales table from the AdventureWorksDW2012 database. I will again use the CarrierTrackingNumber column that I created an index on when talking about data type mismatches. If you need to, recreate the same index for this demo form above.

Then, I compare plans for two versions of a common query pattern. One version uses the ISNULL function on both sides of the criteria so that, if the variable is null, the query returns records with null values and the other does multiple comparisons without using ISNULL (no function call on the column).

Declare @CTN nvarchar(25);

Set @CTN = NULL;

-- Function on column

Select CarrierTrackingNumber

From dbo.FactInternetSales

Where ISNULL(CarrierTrackingNumber, ‘’) = ISNULL(@CTN, ‘’);

-- Function on constant

Select CarrierTrackingNumber

From dbo.FactInternetSales

Where CarrierTrackingNumber = @CTN

Or (CarrierTrackingNumber Is Null And @CTN Is Null);

Both queries are functionally the same, but the plans are different. The query with the ISNULL functions performs a scan of the index and has a relative cost of 83% of the total cost of both queries. The query with the ISNULL functions performs a seek of the index and has a relative cost of 17%. The query with the function calls is almost 5 times more expensive.

   That difference of almost 5 times the cost also plays out in the IO statistics. If I re-execute the same queries, this time with STATISTICS IO set on, the IO for the query with the function calls is almost 5 times as much as the query without the function calls. Logical reads is 430—versus 92 pages. Imagine how this issue would be compounded exponentially as the data set grows.

(25398 row(s) affected)

Table ‘FactInternetSales’. Scan count 1, logical reads 430, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(25398 row(s) affected)

Table ‘FactInternetSales’. Scan count 1, logical reads 92, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Like the issue I talked about earlier, SQL Server has become—and continues to get—much smarter about these scenarios. For simple queries, you may not see a difference, but the more complex a query is, the greater the chance that even a built-in function will adversely affect the performance.

The task here is simple. When you are tuning a query or procedure, this is another design pattern to look for in the query plans and queries. When you find instances of functions—user-defined or built-in system—rewrite the query to so that the function call is not on a column. Removing the column altogether also works very well.

    5.     MAX SERVER MEMORY AT DEFAULT SETTING:

                     The Max Server Memory setting is responsible for a wide variety of problems. Many people are surprised at how many things can perform poorly when free memory is low and there isn’t enough memory for things to operate as they normally do. This is one of the first things I think of when someone complains how a process that uses memory not in the buffer pool normally runs fast, but occasionally runs really long. In particular, what I hear is that when it is running slow, restarting SQL Server “fixes” the problem for a while, but it returns after a while.

Restarting SQL Server flushes and releases all memory back to the operating system. When SQL starts up, it begins using memory again and, if the amount of data and workload is sufficient, it will keep using more memory until it is all gone or it has reached its maximum allowable memory. SQL Server was designed to assume it is the most important application on the server, and it does not give up memory willingly once it has it. When the operating system tells SQL Server it needs more, SQL is not always very responsive with how quickly it frees memory, and it only frees up a little at a time until the OS stops bugging it.

In SQL Server 2012, memory allocations were changed so that the buffer pool, the amount of memory covered by the max server memory setting, allocates more objects. The need for external free memory is lessened somewhat in SQL 2012, but there is still plenty that needs external memory. SSIS packages and linked server queries are two things that require external memory, and you can see processes that use these suffer very poor performance when free memory is low.

One of the first things I check when I inherit an existing server is the max server memory setting, especially when that server is known to have unexplained performance problems. For new servers, I make sure the memory settings are configured before releasing it for usage.

You may be wondering at this point, “What amount is the right setting for max memory?” That question depends heavily on many factors. In general though, the best way to figure this, is to determine how much memory you need to keep free for the OS and other external processes, subtract that number from the total memory, and use the resulting number as your max server memory setting.

One way to figure out the best option for this setting is to set it to a value much lower than you think is needed. Then monitor free memory on the server (Memory\Available MBytes) to figure out exactly how much memory the additional processes are going to use. Once you know this, you know how high you can bump the max server memory.

There are some good rule-of-thumb recommendations for unknown workloads as well. You can start with the numbers listed below and then pad on additional memory amounts for higher CPUs counts or for external processes and components that will be used on the server.

                        4 GB server with up to 2 CPUs - a minimum of 1 GB of RAM

                        8 GB or more with up to 4 CPUs - a minimum of 2 GB of RAM

                        32 GB or more with up to 16 CPUs - a minimum of 4 to 6 GB of RAM

                        64 GB or more with up to 32 CPUs – a minimum of 8 to 10 GB of RAM

Even if all you choose to do is apply the rule-of-thumb method above, it’s a much better alternative than leaving the max server memory setting at the default value, which ultimately equates to all memory. You can dive deep into setting this option, but you can also make a quick win and simply set it to an estimate of what you think is the right value. Even an estimate being a little off will be less troublesome than the default setting.