Showing posts with label SQL Server Performance. Show all posts
Showing posts with label SQL Server Performance. Show all posts

Friday, April 30, 2021

Data Tune - Don't Wait For a Problem

Just like the human body, we should keep our databases tuned and running efficiently. Your doctor will recommend to you to monitor and manage your cholesterol, body fat, and general health. We shouldn't wait until we need a gastric band before we change our eating habits. It's the same with a database system. We shouldn't wait for a problem. Rather, we should continually monitor and tune our databases. 

How do we do this

  • Continually monitor total CPU usage. If it's over 90% for sustained periods, there's little headroom and you should tune your expensive queries (and/or add more cpu resource). Note, expensive queries are the ones that use the most "total" CPU. So a query that runs 5 times per second using 0.5 seconds is 100 times more expensive than a query that runs once per hour using 90 seconds CPU.
  • Continually monitor average read and write IO latencies. If average latency is over 15ms for more than a few minutes per day, then you have an emerging IO bottleneck. The solution is often to tune queries that incur lots of IO. These queries often only require better predicates, updated statistics, or an index to prevent a table scan. Note, if your queries are well tuned, the solution might be to increase RAM (SQL holds more in the buffer so needs less IO) or to upgrade the disk subsystem to support higher IO rates.
  • Periodically enable SQL Profiler and find the most expensive SQL queries. It might surprise you how much you can take off the total system resources by tuning the top 10 SQL queries
  • Look at your compression strategy. SQL Server likes compressed tables. They take less space in the data cache and require fewer IOs for a full/partial table scan. The CPU overhead in reading these tables is almost negligible. 
  • Look at your fillfactor for large tables. If you are sequentially (by clustered key) loading these tables, the default of 10% is just wasting space on disk and data cache. Use fillfactor=100 for sequentially loaded tables.
  • Etc.
If you ignore your database until there is a performance issue, it will be hard to fix. With a performance bottleneck, adding Profiler traces etc, will only make it more unstable, initially. So it will be hard to find the issue without further slowing down the system.
Continual tuning will also keep your system overhead to a minimum and you'll get payback from lower hardware and licensing costs. Note, as you add cpu (and to some extent RAM) to your server, your SQL Server licensing costs will increase too. So keep your database systems tuned and healthy. 
I am happy to discuss this topic in more detail as it applies to your systems.

Friday, March 16, 2018

Same query, fast and slow

One of my clients had an issue where the same query was running fast or slow depending on the database connected to. This was in spite of both databases residing on the same server and the query was predicated with a database name, so both executions were executing exactly the same code on exactly the same database with the same data. How can this be?
 
Actually, there are a few reasons, with the most obvious being the COMPATIBILITY_LEVEL. Even though you are executing a query on Database dbA, if you are executing it from Database dbB, SQL will honour the compatibility level of dbB. In the case of my client, both databases had the same compatibility level of 130 (SQL Server 2016).

So, what happened?
Microsoft introduced LEGACY_CARDINALITY_ESTIMATION in SQL Server 2016 specifically so that you could configure a database with compatibility level 130, while keeping the old cardinality estimator (CE). A database upgraded to 2016 will keep the old CE by default, even though you run with compatibility level of 130 (2016). However, a new database on the same 2016 server will have compatibility level of 130 and the new CE.

So, two databases with compatibility level, 130, on the same server can have different CEs, leading to different plans (potentially one fast and one slow) for the same query pointing to the same database. In my client's situation, this led to one query executing in 7 seconds and the other executing in 10 minutes.

Essentially this was the code to reproduce the issue

Use dbA;
go
--This query executes in 7 seconds with an efficient plan
select *
      from dbA.dbo.myView
      where myKey= 23;   
Use dbB;
go 
--This query (on the same connection) executes in 10 minutes with an inefficient plan

select *
      from dbA.dbo.myView
      where myKey= 23;
    

I imagine the new cardinality estimator is generally better than the old one and its use should be encouraged. However, it could be that in your environment, the old CE is better. If you are having performance woes, it might be worth benchmarking the two CEs. It could even be that you create a second empty database, just to be used for those queries that prefer the old CE. (They would be executed from the second, dummy, database).

For instructions on altering the CE see https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql.
 

 

Friday, January 12, 2018

Querying the Query Store

SQL Server 2016 has finally included Query Store functionality where SQL Server efficiently records query/plan performance activity. Operational DBAs really need this information to see which queries are taking the bulk of the resources, and which queries have degraded on a plan flip. Before Query Store, we needed to use SQL Profiler to sample and aggregate. Query Store is much easier and is so efficient it can be left turned on in most environments.
SQL Server provides some visualisations of the top queries etc., but it doesn't give me the detailed information I need. But that's no problem, as we can write our own queries on the Query Store tables.
Here is my base query, which will give a list of queries and their plans, ordered by the queries with the greatest total CPU consumption. It's easy to change minimum thresholds, date range etc.
Note, if you haven't enabled Query Store, here is a link for turning it on. It's very easy. https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store#Enabling
Here is my base Query Store query 
declare
        @DaysBack int = 3
       ,@TotalCPUSecondsThreshold int =600;
with qTot as
       (select q.query_id, sum(rs.avg_cpu_time*rs.count_executions)/1000000 TotalCPUSeconds
              from sys.query_store_plan AS p 
              inner join sys.query_store_query AS q 
                     ON p.query_id = q.query_id 
              inner join sys.query_store_query_text AS qt 
                     ON q.query_text_id = qt.query_text_id
              inner join sys.query_store_runtime_stats rs
                     on rs.plan_id=p.plan_id
              inner join sys.query_store_runtime_stats_interval i
                     on i.runtime_stats_interval_id=rs.runtime_stats_interval_id
              where i.end_time>getdate()-@DaysBack
              group by q.query_id)
select
        q.query_id
       ,qt.query_sql_text
       , p.plan_id
       ,p.query_plan
       ,q.object_id
      ,convert(datetime,min(DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),rs.first_execution_time))) FirstExecutionTime
       ,convert(datetime,max(DATEADD(hh,DATEDIFF(hh,GETUTCDATE(),GETDATE()),rs.last_execution_time))) LastExecutionTime
       ,sum(rs.count_executions) Executions
       ,max(rs.max_dop) Max_DOP
       ,sum(rs.avg_cpu_time*rs.count_executions)/1000000 TotalCPUSeconds
       ,sum(rs.avg_duration*rs.count_executions)/1000000 TotalDurationSeconds
       ,100*sum(rs.avg_cpu_time*rs.count_executions)/sum(nullif(rs.avg_duration,0)*rs.count_executions) AvgCPUSaturationPct
       ,sum(rs.avg_cpu_time*rs.count_executions)/sum(rs.count_executions)/1000000 AvgCPUSeconds
       ,sum(rs.avg_duration*rs.count_executions)/sum(rs.count_executions)/1000000 AvgDurationSeconds 
       ,sum(rs.avg_physical_io_reads*rs.count_executions)/sum(rs.count_executions) AvgPhysicalReads 
       ,avg(rs.avg_rowcount) AvgRowcount
       ,qTot.TotalCPUSeconds TotalQueryCPU
from sys.query_store_plan p 
inner join sys.query_store_query q 
    ON p.query_id = q.query_id 
inner join sys.query_store_query_text qt 
    ON q.query_text_id = qt.query_text_id
inner join sys.query_store_runtime_stats rs
       on rs.plan_id=p.plan_id
inner join qTot
       on qtot.query_id=q.query_id
inner join sys.query_store_runtime_stats_interval i
       on i.runtime_stats_interval_id=rs.runtime_stats_interval_id
where i.end_time>getdate()-@DaysBack
  and qTot.TotalCPUSeconds > @TotalCPUSecondsThreshold
group by
        q.query_id
       ,qt.query_sql_text
       ,p.plan_id
       ,p.query_plan
       ,q.object_id
       ,qTot.TotalCPUSeconds
order by
        qTot.TotalCPUSeconds desc
       ,q.query_id

       ,p.plan_id

Hope that helps you tune your high performing SQL databases.

Thursday, November 20, 2014

Yikes - A read query is blocking everything!

This is something I saw in an OLTP database with an application (.NET) read query scanning a multi-billion row table while holding a full table S lock. The query thought it was using the default isolation level of read committed. Almost every other query in the system was locked in a chain behind this read query as all update queries on the table (arguably the most important table in this OLTP application) also prevented any other read queries from jumping the queue and had them waiting for a lock too.

By default, SQL Server read queries will use a locking isolation level of READ COMMITTED. Most developers just assume this and, understandably, do not bother to set isolation level. With READ COMMITTED, a read query will only lock the current record while scanning many rows. It has an "intent" lock on the table, which is compatible with all other "intent" locks on the table. However, if you are using connection pooling, which most applications do nowadays, it is possible that your query has picked up an isolation level of REPEATABLE READ from a recycled connection. This is quite serious. Now your query won't release locks as it scans through rows, and once it has read a few thousand rows, it will stop taking row/page locks and lock the entire table.  This is called Lock Escalation, as you have taken so many locks on one object, SQL Server says, "that's enough you may as well have one lock on the entire table". Now it gets interesting. Any INSERT, UPDATE, or DELETE query will have to wait on your query, since they are incompatible with your S (share) lock on the table. What happens next is even more interesting (in a nefarious kind of way). All subsequent read queries (except NOLOCK read queries) will wait for a lock too.  They are compatible with the full read lock but they are incompatible with the IX locks of the insert, update, delete queries, so they must wait in queue. I call this the "no queue jumping" rule.

How exactly does your query get a REPEATABLE READ isolation level when it didn't ask for it? The answer is connection pooling. An application program can change the isolation level of the connection and leave the changed isolation on the released connection. The next thread to pick up the connection will have the isolation level left behind. Many people have raised this issue before and there has been a Connect issue supported by many developers.
http://connect.microsoft.com/SQLServer/feedbackdetail/view/243527/sp-reset-connection-doesnt-reset-isolation-level
Unfortunately, I feel, Microsoft's response has been to close the "Bug" with an "as By Design" flag!

So what can we do to avoid queries escalating to full table locks when they assume isolation of READ COMMITTED?
  1. The cleanest solution would be for the connection pool to pass on used connections with their default settings. This is unlikely to happen in the near future as indicated above by Microsoft. 
  2. Yould could disable connection pooling. This is quite heavy handed and you will pay for it in a slower application as new connections generally take 100's of milliseconds to create.
  3. In your application that uses connection pooling, ensure that all connections are reset to the default isolation level before release. Note, sp_reset_connection does not do this. Also note, there might be times when a piece of code does not follow this standard. Unfortunately, the non standard code won't be affected.  It will be the innocent code that takes on the connection that initiates the locking issue.  
  4. In your application that uses connection pooling, ensure that all new connections are set to the desired isolation level. Ie, don't rely on a new connection having the default settings. This is probably a good practice, as all code that follows this standard will be OK.
  5. Use WITH (NOLOCK) table hint on your read queries. This will certainly avoid the lock escalation. However, there can be interesting data integrity issues in reading with no locks, such as reading the same row twice. I think there is a place for NOLOCK, but I wouldn't advise the use NOLOCK throughout your application. The problem is the use of REPEATABLE READ not READ COMMITTED.
  6. Use WITH (READCOMMITTED) table hint on your read queries. This will override REPEATABLE READ isolation level on the connection for this table. So the problem above is avoided, but it is a bit of a task changing all the queries to use this table hint. Note, query hints have a bad rap, which is understandable when they override the optimizer's choice of access path. I see no problem with query hints when they are requesting a specific isolation level.
  7. Use READ COMMITTED SNAPSHOT ISOLATION on your database. This is not a solution as the read query is on a connection that has a REPEATABLE READ isolation. As the name suggests, READ COMMITTED SNAPSHOT ISOLATION only works on connections that are using READ COMMITTED. 
  8. Use TRANSACTION COMMITTED SNAPSHOT ISOLATION on your database. This is not a solution either, as it does not override the REPEATABLE READ isolation level of the read query.
  9. Wait for upgrade to SQL Server 2014.  It looks like the isolation levels are reset on pooled connections.  However, you should check, as this information has simply be posted in an msdn forum, albeit from a SQL team member. 
How can I determine if REPEATABLE READ is being used by my application?
  • A SQL Server Profiler trace on ExistingConnection events will provide the isolation level of all active connections. Just look for set transaction isolation level and see what follows. If any of your current connections have REPEATABLE READ you should be concerned. ExistingConnection will only display currently active connections. To see if isolation level is changed, you will need to trace SQL:BatchCompleted and SP:StmtCompleted, and Prepare SQL. Looking for the text '%set%transaction%isolation%level%repeatable%read%'.
  • Another useful Profiler trace is to trace Lock:Escalation. I believe that any table lock escalation in your OLTP environment should be of some concern to you, particularly if you have locking or deadlock issues. However, if you have too many to look at, you could filter in/out by Type=5 (Object), objectId (table) and/or Mode=3 (S-share).
How lucky are we? There are two ways of viewing the locking incident.

  1. Unfortunate that it happened and get a quick fix asap for this particular query.
  2. Fortunate that this relatively small incident occurred, which enabled us to uncover something quite serious (full table locks in an OLTP environment). The issue, has given us an opportunity to discover the dynamics of connection pooling and helping us find an application wide fix to prevent its future occurrence. Also, if there is a future locking incident, we have more knowledge to diagnose that too. I see this as analogous to someone having high blood pressure and going to the doctor for a fix.  The high blood pressure could be seen as annoying and simply needing some pills to lower the blood pressure, or it could be an opportunity of determining the cause of the high blood pressure (be it diet, stress, etc.) and rectifying the underlying cause.








Wednesday, August 27, 2014

Database IO Throughput

I've written on this before, but I feel I need to say it again in a different way as so many people fail to grasp the significance of IO throughput capacity.  If you see messages in the SQL log along the lines of the following, then you have a very very serious IO throughput issue. I didn't make these logs up.  They are real logs!
The best analogy I can draw is if you can imagine your company has 1000 staff and you have a building of 20 floors with 4 lifts that can adequately house all staff.  Your 1000 staff tend to drift in between 9 and 10AM and the 4 lifts can move the 1000 staff to their desired floors in the hour.  Infrequently, staff will queue for a lift as the first lift will be full, but it's rarely longer than one lift ride for the wait.  During the day, the lift demands are lower and there are no queues.

Now, over time, your company grows to 2500 staff, and you've added another 30 floors to your building and the lifts are extended to cater for all 50 floors.  The CEO is relieved that the building capacity has been increased to house the new staff and that the lifts work across all 50 floors.  However, no one has thought about the increased throughput required by the lifts.  Between 9 and 10AM 2500 staff now drift into the office and want a lift to their floors, however the 4 lifts are only capable of moving about 1200 staff/hour.  The queues for the lifts build up and are not fully drained until after 11AM.  The company is now in trouble with disgruntled staff and find it needs to ask staff to come in earlier or later.  Also, there is the hidden issue of the fire exit stairways also having throughput limits.  With 1000 staff, the building could be evacuated in 15 minutes, now with 2500 staff (and the same stairways) it will take over 40 minutes.

This is analogous to a SQL Server database growing over time, and the SAN (playing the role of the lifts) is extended to hold more TeraBytes, but its throughput capacity (measured in MegaBytes/second of reading and writing) is not increased.  The IO queues start to build up and IO times degrade.  The SQL Log picture above is an example where hundreds of IO every minute are taking longer than 15 seconds to complete!  This is an unbelievably dire situation that can cause SQL Server to shutdown. 

I am not advising that you monitor your SQL Server log for long running IOs, although if you see them, you should act immediately.  I advise you to monitor IO throughput, average latency, and queue length continually, using Perfmon or related tool.  You can detect throughput capacity when MB/second tends to flatline while IO latency and queue lengths are waxing and waning.  Unfortunately, Perfmon, is not a complete tool, but it does have all the base information and there are many third party tools, or you can do what I do and create your own by having Perfmon send records to a common SQL Server database and cube.  You only need a few counters and only record every 60 seconds, or so.  If the Perfmon database is on another server, there will be very little load on your production servers, essentially no IO.  Your Perfmon database could hold performance data for all your production servers, so that you have all the performance information you need in one place.  Also, when you have a performance issue you have historical information of this issue and trends earlier in time.  Companies that don't continually record these metrics are flying somewhat blind, or, at least, flying with blinkers on.  Here's an earlier blog on creating a Perfmon cube, although, unfortunately, the online demonstration is no longer available.  

This posting is not intended to provide answers to the IO throughput issue.  The solution can be many and varied.  The solution will probably involve speeding up the storage system, but it might be to make the database more IO efficient.  The most important point I can make is that you should be monitoring and be able to recognise this issue.  Also, don't let a storage person tell you that you have hit the maximum possible throughput.  There are always ways to increase the throughput performance of your storage, as there are always ways to improve your database performance.



Thursday, July 23, 2009

Who's Using Resources on My Server?

One of my favourite analytical pages is Perfmon by Process. This page shows me which Windows processes are using my critical resources, such as Processor, IO, page file and virtual bytes. When you click on this link, it will show you the critical resources and for each of the last three hours, it shows you which process has been the biggest consumer. The two charts on the right show you the top 10 consumers for the critical resource you have selected on the left (scorecard). By default, the selection is %Processor Time, but you can click on others. My favourite is IO Data Bytes/sec, since this tends to be the most critical resource in a large database system. Try clicking on IO Data Bytes/sec and other counters. See how the grraphs on the right change dynamically, and will show you real time analysis for the last 60 minutes and last 60 hours.

This is just a demonstration. If you have a large production system, do you have such analytical tools available to you? If you don't, I would suggest that you are operating blind, and that you should get some. The toolset I have created uses nothing but SQL Server 2005/2008 and PerformancePoint (for visualisation). In fact, it's so simple, I would like Microsoft to have something like it available as an optional install from SQL Server.

There are lots more real-time demonstrations on http://RichardLees.com.au

Monday, June 15, 2009

Learn SQL - SELECT Product(column)

I have on occasion been quoted as suggesting that SQL code with temp tables can indicate the author lacks strong SQL skills. Consequently, I have been challenged to write single SQL statements to replace multiple statements stuck together with temp tables. One of these occasions was with Ron Soukup over a bottle of whisky, but that story deserves its own blog.

On this occasion (sometime in 1999) I was challenged to write a query to get the product of a set of numbers. On another bottle of whiskey, as it happens. My friend wanted something like the SUM() function, but he wanted the product of a set of numbers rather than the sum. The business application was quite simple. His client, a financial organisation, needed to calculate compound interes for money on overnight deposit. Each day will have a different market interest rate.

This problem had an interesting solution using a couple of maths functions - Sum(), Exp() and Log(). Here is something like the SQL that I wrote for them.

select exp(sum(LOG(OvernightInterestRate+1))) as CompoundRate
from OvernightDeposits
where TheDate Between '1999-07-23' and '2009-06-18'


Now that's really quite easy, and is much more efficient than putting the data into a temp table and reprocessing it. Having said that, it would be quite useful (and I thought quite easy) to include a Product() function in SQL Server.

For real-time SQL, OLAP and Data Mining demonstrations, goto http://RichardLees.com.au/Sites/Demonstrations