Memory is Oxygen for Large Database Systems

I am frequently invited in to assist with database (OLTP, DW and OLAP) performance, and often the issue is IO throughput related. So, the solution tends to involve tuning queries (and indexes) to reduce IO or improving the IO capacity of the SAN (or both). Of course, in all of these IO related performance issues, RAM can be a great elixir. Databases love memory. They are designed to use RAM by holding popular pages, compiled plans and work tables in memory. So, if you have a large database with a performance issue; very quick, and relatively cheap relief might be gained simply by adding RAM.

SQL Server is particularly good at exploiting available RAM - and (just as important) reducing memory demands if the operating system is paging. With the default SQL install options, all you need to do is give RAM to the OS and SQL will use it to hold more data and plans in memory. Sometimes this can be an exponential beneficial effect since, by reducing IO demands, the SAN is no longer a bottleneck and remaining IO is executed much faster.

Multidimensional OLAP databases also like to have lots of RAM. Even larger than the combined OLAP database size! See earlier blog. Unfortunately, SQL Server OLAP is not as memory effective as the relational SQL, but it will hold data (and aggregations) in memory, improving query and processing performance. SQL Tabular is an exception, as it requires the entire cube to reside in memory. It will simply not work without sufficient memory.

RAM, by and large, has no licencing cost with SQL Server, which can be a big consideration. Adding CPUs will generally incur significant OS and SQL licensing costs. I said, by and large, since with SQL 2012 and SQL 2014, there are, unfortunately, limits on SQL Server Standard Edition memory.

• SQL 2008 Standard Edition has no memory limit
• SQL 2012 Standard Edition has 64GB memory limit
• SQL 2014 Standard Edition has 128GB memory limit
• (SQL Server Enterprise Edition versions support their OS limits)

So, if you have a performance issue with a large database, and can't wait for a database tuning expert; before adding CPUs, Fusion IO cards etc.; try giving your system more RAM.

SQL Server Advanced Scan, aka Merry-go-round or Scan Piggybacking

I think SQL Server 2005 was the first SQL Server version with Advanced Scan. I have been excited by this feature, comforted by the thought my databases are performing better, but also a little unsure of its efficacy. According to the documentation, when one query is running a table scan, subsequent queries that require a table scan will "piggyback" on the first scan, avoiding additional scan IO. Apparently, any number of queries can all piggyback on the same scan. Once the first one completes, the others carry on from the beginning, as they may have joined in after the first scan started. So there should never be more than one scan of any one table at a point in time.

What a great feature? However, there are no Profiler traces for Advanced Scan, so how do you know it is actually working? I've just ran a performance test of Advanced Scan with 6 concurrent queries that all scan the same table (with slightly different predicates). I ran another test with one query running solo.

I was impressed with the results.
The table I used is about 10 times the size of the available memory, so SQL had no chance of caching the table. The solo scan ran as fast as the read ahead engines could provide the data. The multiple read engines (from the solo scan) kept the SAN storage busy with an IO queue count constantly greater 50 and reached a steady state of about 100MB/second. I ran this a couple of times to warm up the system. Note, this single query was running with MAXDOP=1. It would not run faster with MAXDOP=0 as the SAN storage was already working at maximum throughput with the workload of the multiple readahead engines.

I then ran 6 queries concurrently that all required a scan of the same table, but with slightly different predicates. These 6 scans all completed in a similar time as the first solo scan! That told me that Advanced Scan was working, but where could I see it? I could not find any evidence in Profiler (apart from the duration) that Advanced Scan was operating. In the queries I had SET STATISTICS IO ON. The statistics had the evidence that Advanced Scan was functioning. All 7 queries had similar logical reads, physical reads, CPU time, and elapsed time. However, the 6 concurrent queries had much smaller read-ahead reads. The total for the 6 concurrent queries was equivalent to the one solo scan.

It's unfortunate, that Profiler does not have an Advanced Scan event (or flag on scan). However, this test does demonstrate that the feature works and that it can be quantified.

Note: Advanced Scan is only available in Enterprise Edition. I initially tried to monitor this feature in a developer version of SQL Server, but it gave mixed results. Also note, the test above had Max Degree of Parallelism set to 1. I received similar results with MAXDOP set to 0.

My SSAS databases are corrupted

Did you happen to have a unplanned shutdown?  This is quite likely to be the cause of the failure.  This is unlike SQL Server, which has a transaction log and will always recover databases on restart.
As I have mentioned earlier, SQL Server Analysis Services exploits the Windows File Cache to help minimise IO, which might lead you to suspect the Windows cache.  However SSAS uses the FlushFileBuffers API to minimise the possibility of having dirty pages in the Windows File Cache.  However, this does not protect SSAS from corruption on an unplanned shutdown.
So, if you really want to avoid corrupted SSAS databases on power failure, you should ensure your SSAS servers are supported by UPS.
Most production SSAS servers have UPS.  But, if you don't have UPS, like my demonstration site (http://RichardLees.com.au/Sites/Demonstrations) and, like my site, the cubes are continually updating, when the power fails, SSAS databases have a good chance of being corrupted.  The only solution to this, that I know of, is to restore the affected (typically all) SSAS databases.  I find the quickest way to do this is to stop SSAS, empty the SSAS data directory, start SSAS and recover all the databases.  One thing you will also need to do is add back any userids that had SSAS administrator privileges as they were held in a file on the SSAS data directory.

Why doesn't SSAS cache the entire cube?

Would you expect a 36GB server dedicated to SSAS to eventually cache an entire 11GB cube in memory?  If so, you would be wrong.  SSAS does not necessarily keep aggregations in memory, even though memory used is less than Memory\LowMemoryLimit.

I have seen this effect on many production servers but never quite believed it.  There is much more RAM than cube size, yet SSAS is continually losing data from cache and asking for physical IO.  Actually, SSAS relies on the Windows File Cache to reduce much of its IO.  However, the Windows File Cache does not cache the entire cube either.

To demonstrate this effect, I have set up a 36GB server, dedicated to SSAS, and set up an 11GB MOLAP cube.  Then, over a period of 19 hours, I have client tasks querying the cube in a semi random fashion.  The MOLAP cube was not updated over the period.  Over the 19 hours, if SSAS or Windows File Cache were effective, there wouldn't be much more than 11GB of read IO on the SSAS data drive over the 19 hours.  However, the system reached an equilibrium, where it was requesting over 20MB of read IO/second.  If you add this up, the system read 1.3 terabytes of data from the 11GB cube (20MB*60*60*19).  Obviously, the system was not effectively caching the cube.  This is very different from SQL Server, which would quite happily keep an 11GB database in memory, effectively eliminating further read IO.

My server was running Windows 2008, SQL Server 2008 R2 with default configuration settings.  The SSAS database was located on the d: drive.  Nothing else is located on the d: drive
Here is a Perfmon chart with some of the key counters during my test.
SSAS (msmdsrv.exe) did not use more than 12GB of memory during the test, which is well below the LowMemoryLimit 65% (which is about 23GB).

During the 19 hours, SSAS requested, on average, 102MB/second of read IO.  The Windows File Cache was able to satisfy about 80% of these read requests, so the physical disk bytes/second was only 20MB/second. 

The Perfmon chart above shows some of the key counters.  Notice

• The Process (msmdsrv) Bytes/second y axis scale is in MB, often going off scale at 100MB/sec. These are not necessarily physical IO, as the Windows cache will satisfy many.
• The PhysicalDisk Bytes/second is averaging 20.1MB/second for the 19 hours
• The Windows Cache Bytes is sitting between 0.6GB and 1.1 GB.
• The red line (Cache Copy Reads/sec) is the IO requested of the cache.  These are IO that the Windows cache has managed to satisfy by finding the page in its cache.
• See how the Cache Copy Reads/sec is satisfying IO requested of the msmdsrv process, reducing the PhysicalDisk Bytes/sec.  Not so easy to see, as the reads are reads/sec, while the PhysicalDisk and Process are in bytes/sec, but you can see it.

Now that I understand this effect, I am much more appreciative of the Windows Cache.  This is very different from a SQL Server system, where the Windows Cache has little to do as SQL disables the Windows File Cache for its database files.

However, you can clearly see that SSAS (or the system) is performing much more IO than would be necessary if the entire 11GB cube was cached in memory.
There are a couple of takeaways from this exercise

1. Be thankful that we have a Windows Cache, and consider configuring it for SSAS.
2. Try and put SSAS databases on very fast (ideally solid state disks) storage devices.  If you can't avoid the IO, at least make it fast.
3. Partition large cubes.  This helps reduce logical IO requests and to the extent it bunches the popular data together it will probably help the Windows Cache hold the hot data more effectively.
4. Hopefully, in a future version, either SSAS or the Windows File Cache will cache more of the cube.

By the way, this effect is not always noticeable.  If your cube is less than 2-3GB, then it is likely to be well cached between SSAS and Windows File Cache.  Also, if you cube is much larger than the memory available to SSAS, then you would expect to see continual IO, and it is likely to be quite well optimised.  However, when you have a 64 bit server with a cube that is larger than 3GB but is comfortably less than the server memory, you might be surprised to see the volume of continual IO.

Please help get this fixed by voting on https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram

Parallelised Queries are Selfish Queries

SQL Server Parallel Query Processing and MAXDOP

I am prompted into writing this post after reading the msdn article Understanding and Controlling Parallel Query Processing in SQL Server.  It is a very informative article.  I cannot disagree with anything in the article, but there is an elephant in the room.  The article dissregards IO performance bottlenecks.  It actually states that it will disregard IO performance.  "We assume that your I/O system is configured properly ..... so that the I/O system is not the primary performance limiter."  I am going to discuss IO performance with MAXDOP, because I believe it is often the main performance limiter.

As a bit of background, SQL Server has a parallel query processing feature that is enabled by default.  This permits SQL Optimiser to choose a plan that involves breaking one query into sub tasks that can run in parallel.  This can involve executing, essentially, the same plan, but in parallel subtasks, or it can involve a completely different plan and again breaking the query into subtasks that can run in parallel.  Really quite an amazing piece of technology. When it is working well for a query, it is interesting to see figures like cpu=10 seconds, elapsed=1 second!

In many of the very large database environments where I work, IO is the most critical resource.  Perhaps this is my misfortune, but I suggest that IO performance is often a performance limiter in large database systems, and probably will be so until SSD (solid state disks) are more commonly employed.  The problem with parallel query plans is that one query can simultaneously request multiple table scans, selfishly requesting more resources in parallel in the hope of getting a faster response.  Just one table scan from one query will tend to have parallel IO requests, as multiple SQL Server read engines are given multiple page read ahead requests.  So, even with a single non parallelised SQL query, there will already be some parallel IO requests.  Run several non parallelised queries at the same time and there are many more parallel IO requests than there are queries.  That is all before Parallel Query Processing kicks in!

Let's take a very simple example, where the SQL query has a predicate on a non indexed column of a very large table (the table is larger than SQL cache), therefore requiring a sequential scan of the table.  Without Parallel Query Processing, SQL Server will sequentially scan the very large table, using several read engines working in parallel to get multiple page ranges.  Note, there is already parallel IO requests with a single non parallelised SQL query.  Now if we enable Parallel Query, SQL can break the scan into several sub tasks, each of which will ask for range scans of the table.  Each of these scans can busy up multiple read engines, which can lead to IO throughput saturation.  That is the parallelised query will result in scanning the same table, but it will flood the IO system with many more parallel requests that can end up in a long queue.  A good way to think about it is to imagine an old fashioned bank branch with 30 tellers and everyone queues up for the next available teller.  That would be non parallelised.  Now imagine that someone comes in with bags of money that requires lots of counting and when he/she gets to the front of the queue, all the tellers attend to counting his/her money together (in parallel).  This might be good for the selfish individual, they get served faster, but it is not good for the performance of the branch, as now all customers will be waiting for the selfish customer to finish.  For this reason, I like to think of parallelised queries as selfish, or unsocial.  They want to take all the resources, even though this is not an efficient use of the overall resources and others are negatively impacted.  This is fine in a system with very few (perhaps one) customer.  Note, there is always an overhead in parallelising a query, even if it is using essentially the same plan as a non parallelised query.  This is because there is overhead in distributing and regathering the sub tasks.

There is an argument that data warehouses have very few users so that Parallel Query is an ideal performance option.  However, I see modern data warehouses with large numbers of users simultaneously submitting queries (dashboards, web pages, reports etc), and you typically don't want one user to selfishly hog critical resources such as IO.

I might sound like I am against Parallel Query.  That isn't true.  It is a fantastic feature.  However, I would encourage DBAs to consider setting Max Degree of Parallelism to 1.  Note; that is not disabling Parallel Query, it is just the default.  So, by default, queries will not be selfish.  If you have a particular ETL task or report query that you want to parallelise, you can simple add OPTION (MAXDOP n) to the query, where n is the number of potential sub tasks running in parallel.  So you are not turning it off, you are just ensuring that queries aren't selfish by default.  Also, just to be clear, with Max Degree of Parallelism set to 1, your system can still have many SQL queries running in parallel (with each other) it is only that individual queries will not be broken into parallel running subtasks.

The reason I configured Max Degree of Parallelism to 1 the first few times was because I was working in environments with a wayward parallelised queries.  SQL Optimiser chose a very very poor plan thinking that parallelisation would make this plan fast.  The same queries with Max Degree of Parallelism =1 (or MAXDOP 1) the optimiser chose a very different, and efficient plan.  When I reduced the "Max Degree of Parallelism" to 1, these queries, and the whole system ran more efficiently.

An annoying limitation of MAXDOP is that you are unable to set MAXDOP for queries that SQL Server Analysis Services uses when processing fact tables.  You can only provide SSAS with a table or view name, and it constructs the query.  There is no way to inject a MAXDOP option.  This is another reason I like to set Max Degree of Parallelism = 1, so that the default setting is non parallelised.

One word of warning.  Changing Max Degree of Parallelism to 1, can (in fact by design it should) make some queries run slower.  It can also change dramatically the query plan (as I discussed above) and while this will usually result in more efficient queries, it can also involve a poorer performing plan.  In my experience the reverse is more likely (when there are considerably different plans between parallelised and non parallelised, the non parallelised plan is more often the more efficient.  Consequently, it is less risky to configure Max Degree of Parallelism =1 while developing a solution (and using MAXDOP n for individual queries where appropriate) than to change a production environment.

The effect of Max Degree of Parallelism is not just on the individual query.  If you want to know the impact of Max Degree of Parallelism, you should monitor vital statistics (cpu, memory, IO), get average query response times (for common queries) and compare them with before and after  configuration changes.  Another, albeit small, cost of Max Degree of Parallelism<>1 is that each parallelised query will now have two plans (it always keeps a non parallel version for when cpu is high) so that more pressure is put on the plan cache (it is bigger, or it holds fewer plans).  Note, SQL Server is great in that you can change the Max Degree of Parallelism option dynamically.  No SQL Server restart required.  You might even consider setting Max Degree of Parallelism =1 for the online day and resetting Max Degree of Parallelism =0 for the overnight ETL.

A closely related issue is Resource Governor, which also has a MAXDOP option that looks like it can override the MAXDOP option in the query.  However, as I understand it, the Resource Governor does not work like MAXDOP in a query or sp_configure.  If you set MAXDOP to 1 in the Resource Governor, the underlying queries can still chose parallel plans (no change there).  What the Resource Governor does is to restrict the query to only one thread!   This is almost the worst of both worlds, as the optimiser can choose the selfish (potentially faster) parallel plan, but the Resource Governor will ensure that the parallel tasks are serialised with just one thread.

Solid State Disks and IO Performance

I just read a non bulls**t whitepaper from EMC's site about disk performance and solid state disks for the enterprise. They are calling the disks EFD (Enterprise Flash Drives). I have tired of reading storage vendors's whitepapers that dodge real IO bottleneck performance issues. This paper is very informative and discusses IO throughput, which, in my experience, tends to be one of the most limiting factors in hardware performance.

Download from emc.com

Or download my copy from here.

I have always known that solid state disks have faster IO than magnetic disks, but was not sure about their IO throughput. This paper has a performance test (conducted by Microsoft's SQLCAT team) and they found over 1000% throughput improvement by moving to solid state disks. One solid state disk could sustain over 2500 IOs per second! This is great news for any IO bound database server.

The whitepaper also discusses SQL Server's system checkpoint the implication on the log, if you didn't know already.

I suggest the whitepaper is a must read for any DBA interested in system performance.

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

Instant File Initialization for SQL Server 2008 (and 2005)

I will do anything to reduce the IO load on large databases, which is why I am a great fan of compression in SQL Server 2008. However, another way of reducing IO is to ask SQL Server to format new data extents without writing zeroes out to all pages. If your database takes a new extent (by default it is 10% of the file size, which typically isn't good) SQL Server will want to write binary zeroes to all the pages to ensure that some old data isn't hidden in your database. SQL Server does not need the binary zeroes, it is done to ensure data security. Someone else may have deleted a file and perhaps they don’t not want you seeing the data.

Very often, the data security is not an issue and you would like to avoid the writing of binary zeroes. This can be achieved (on NTFS drives) by ensuring that the account running SQL Server has SE_MANAGE_VOLUME_NAME privilege. You can grant this privilege in User Rights Assignment on Windows 2003, XP or above. To do this, grant the SQL Service account Perform volume maintenance tasks local security rights. Alternatively, if the SQL service account has administrator privileges, it will automatically have this privilege. That's all you need to do to avoid having SQL Server write all those binary zeros. By the way, SQL Server always needs to write binary zeroes to the log files, we are only avoiding binary zeroes on the data files.

If you don't want to grant this privilege to the SQL service account, and you want to avoid the IO load during peak times, I suggest that you extend (manually or automatically) your datasets during an off peak times (i.e. before SQL does it for you).

One of the most noticeable tasks affected by binary zeros is database restores. If you watch the restore progress, it will typically not move off 0% until it has written out the entire data files with binary zeroes. After writing every data page, it will then write the backup files over the data pages, during which time, you will see the progress percent increase. So writing binary zeroes can approximately double full database restore times.

Hope that helps you reduce IO load and increase SQL Server performance.

For real-time SQL demonstrations, including Perfmon, which is writing over 300 records every second, see http://RichardLees.com.au/Sites/Demonstrations