Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts

Monday, August 19, 2019

AWS Redshift Performance versus SQL Multidimensional and Tabular cubes

This is an update to my recent blog AWS Redshift versus SQL Server cube. This performance benchmark includes SQL Server's Tabular cube technology, which wasn't possible as the in-memory database required more memory than available in the test server.
This updated benchmark includes
  • AWS Redshift
  • SQL Columnstore
  • SQL Multidimensional Cube
  • SQL Tabular Cube

The cubes were the slam dunk winners, which shouldn't surprise any Data Platform Architects. For many Data Platform Architects, this will validate their use of columnar databases for atomic data combined with an OLAP cube for fast and scalable BI queries.

The races within the race were interesting. With Multidmensional beating Tabular by a factor of two, and SQL Columnstore beating AWS Redshift by a factor of two also.

For the full report goto AWS Redshift versus OLAP Cubes Performance Benchmark AWS Redshift versus OLAP cubes: Performance Benchmark.

Saturday, August 10, 2019

AWS Redshift versus SQL Server cube

Here is a straight out performance benchmark between AWS' Redshift, SQL Server cube, and SQL Server columnstore indexing. If you are choosing between these (and potentially other) platforms, this will provide some interesting reading. 
Essentially, the Microsoft cube was 97% faster than Redshift.

Note, I tried to make the test as independent and representative of a retail enterprise's query load as possible. The underlying fact tables contained 100 million, 430 million and 600 million rows. The queries ranged from the banal to common calcs such as "year to date" and "stock days on hand".

If you are serious about making the right decision for your organisation, you might set up a similar test with your own data and popular queries. 

Here is the full report:
Data Platform Performance Paper

This benchmark has been updated to include SQL Tabular cube performance. Here is the updated paper.

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, May 25, 2017

Keep it Fresh and Easy

I've seen many data warehouses fail due to reconciliation, performance, time lag and lack of data confidence issues. All of which could be avoided with better designs.

Data typically comes into data warehouses in the format of the source system. I like to call this first layer of data as the "base data". In my view it should have similar structure and be easy to reconcile with its source system. A good practice is to frequently run reconciliation jobs that compare your base data with source system data. This will give confidence the base data in the warehouse accurately represents the source systems. If you go on to build fact tables and dimensions from these base data tables, you are complicating reconciliation of this information with source systems. Consider the common issue of a user questioning a number in a report. To reconcile the value of the figure back to the source systems, you'll need to interpret the ETL code that built the fact/dimension tables (over and above the ETL code that imported the base data). This code is not with the data. You'll need to determine which ETL (SSIS) packages and which SQL procedures created/updated the tables. Even when you find the ETL package, you cannot be sure there was not another process or manual update that changed the data. Also, maybe the packages/procedures have changed since the data was loaded? It really can be challenging to reconcile these materialised fact/dimension tables back to the source systems. If you cannot reconcile the report, you'll struggle to win data confidence with the users. The base data tables holding dimensions are typically stored as Type 2 dimensions. They are easy to reconcile (IsCurrent=1) with source systems, even though they hold historical (Type 2) information.

Now consider simplifying data lineage by creating views (over base data tables) for the fact and dimension "tables". You can unmistakeably reconcile the figure in the view with the base data tables, and these base data tables are easy to reconcile (and have been) with the source systems. All you need to do is read the logic in the view. So, when a user says, I don't think the number should be 995, you can explain exactly where the 995 comes from in the source system(s).

Sometimes it is challenging to use views to construct user fact/dimension tables. The most common challenge with views (with all the necessary transformation logic) is performance. Fortunately, there are a few techniques you can use to mitigate the cost of the transformation logic. Note, I would always encourage DW developers to use views early on in prototyping business intelligence, as they are extremely flexible and support customisation while users give feedback on what information/transformation they really need.

Techniques to improve performance on fact/dimension tables as views.
  1. The biggest need for materialising fact/dimension tables is to generate and store surrogate keys (PKs in dimensions and FKs in facts) for the type 2 dimensions. It is very costly to join a fact table with a type 2 dimension using a business key from the fact table and a fact date that is scanning a range in the dimension table. However, consider storing the EffectiveFromDate of the dimension in the fact table, when it is loaded. This is a one off cost, as the fact data is "incrementally" loaded from the source system. That allows the fact table to be joined with the type 2 dimension table with an equi-join. Ie. dim1.BusinessKey=fact.BusinessKey AND dim1.EffectFromDate=fact.dim1_EffectFromDate. Note, this does require your cube/BI tool supports composite primary keys. Surely all good cube technologies support composite keys?
  2. Judicious use of left outer and inner joins. Left outer joins are not implicated if the query does not request columns from the right hand table and the right hand table has a primary key on the join columns. Ie, SQL optimiser knows the left side can only join with one right side row, and since the row isn't used, the join is unnecessary. So, you should ensure the right side tables of outer joins have primary key (or unique) contraints.
  3. Create a clustered or filtered index on Type 2 tables current records. If you have a flag column, IsCurrent, then consider using it as the first part of the clustered index, or creating a filtered index WHERE IsCurrent=1. That way, any views that are looking for Type 1 records will have all the records together on contiguous pages.
  4. If you are merging data from multiple source systems, your fact and dimension tables will use UNION ALL for each source system table and it will include a SourceSystem column, which is simply a literal in the view. Having it as a literal in the view takes no space and costs the query nothing. When the user filters on SourceSystem='Source System 1', the SQL optimiser can see that in the view and will only read rows from that part of the UNION ALL query in the fact/dimension view.
  5. Use a cube technology, such as SSAS, that reads the fact data once, and thereafter incrementally, which avoids repeated reading the potentially expensive views.
  6. Use column store indexes on the base data tables, or, at least PAGE compress large transaction tables.
  7. Commonly used transformations could be computed columns in the base table. For example, rather than seeing 'Db' or 'Cr' some users might prefer to see 'Debit' or 'Credit'. The code for this is very simple, and it could be a computed column in the base table, taking no space and costing almost nothing to display. Every developer using the base table will see the computed column, so there is high probability the code for DebitCredit will be in one place and reused. This column has no data space costs, it is simply computed when the user query requests the column. The only performance cost is where the user filters by this column, as it cannot be indexed unless persisted. Note, it is possible to mark the column as persisted, in which case an index could be built on the column. Also note, an index on DebitCredit would typically not be useful due to the very low cardinality. Some of these computations will be more complex than a simple debit/credit, but having them as computed columns makes the transformation unmistakeable. It is right there with the code, not updateable from a random procedure or update process. Further down the line a computed column could be persisted and indexed with no change in their use from views.
  8. Date tables often have lots of computed columns for WeekdayNo, WeekdayName, MonthNo, MonthName, Year etc. There is no need to have these columns "persisted" unless they are commonly used as filters and the SQL optimiser would use an index on them. If, at a later stage, you determine that it would be beneficial to persist WeekdayName, for example, this would require an update to the table to convert WeekdayName from a computed column to a physical column as WeekdayName is not deterministic. This change to the table would change its use in views and queries.
People who know me, know of my affinity for virtualisation in data warehousing. I have had a lot of success creating virtual data warehouses directly on top of OLTP databases. Unfortunately, many text books have been bought and sold based on data warehouse designs for "star schemas", "snowflake schemas", etc. These concepts certainly have their place, but such vast data materialisation has significant costs. If you can deliver information to users without rematerializing facts and dimensions, you are generally on a winner. I have helped build business intelligence solutions with cubes that consume views over OLTP transaction data for KFC, Long John Silvers, McDonalds, Pizza Hut, Starbucks, to name a few. Since the cubes are incrementally processed, the overhead on the OLTP application is low, latency is low, data confidence is high, and information is vast. These data warehouses have been constructed by very small teams, as there is literally much less work to do. As an added benefit, these data warehouses are extremely flexible due to their simplicity and transparency.
If I were to draw an analogy, it would be with a salad bar. Creating a set of salads in advance would be like building fact/dimension tables, while creating bespoke salads would be like views. If the salad has been built in advance, it is difficult to be 100% sure of the ingredients, as you need to know the recipe and you need to be confident someone hasn't added ingredients subsequently. Also, the prebuilt salad gets stale from the time its built. Taking the analogy even further, getting fresh vegetables from the garden to make your salad is like building virtual data warehouses directly over the OLTP database. I prefer bespoke salads as they are always fresher and you know exactly what's in them.

Wednesday, March 16, 2016

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.

Monday, January 13, 2014

Determining a wayward query plan while it's executing

Occasionally, I get production performance issues of a long running SQL query, that is consuming lots of system resources and is running for much longer than usual.

You can see the long running query simply by using sp_who2, or some variant, that displays active SQL queries.  So you now have the SPID of the running query that is taking a long time.  I would typically then want to get the SQL command.

You can easily get the SQL for this query by using dbcc inputbuffer(SPID).  I might then use the sql query to get a show plan to see how it should execute.  You can quite easily do this by cutting and pasting the SQL into a new window and requesting a show plan.  However, sometimes the query plan that is running is not the same as the query plan that SQL Server tells you it would use now.  This can be for a variety of reasons including new statistics, parameter sniffing and different connection properties.

So what you really want to get is the plan that is currently executing.  You can obtain this from the sys.dm_exec_query_plan table.  First of all you will need the plan handle, which you can get from sys.dm_exec_requests using the SPID as the session_id.

Use the plan_handle to get the plan from sys.dm_exec_query_plan.  The query plan will return as an xml document.  Simply click on the xml in the query results window for SQLEM to open the query plan in a new window.

Now you have the query plan that is actually executing and you can compare that to the query plan you expected SQL to use.  From here you can tune the query to use a more appropriate query plan.

Note, I would encourage you to use all other options before using index or join hints to get the query plan you desire.  This is because once you override the optimiser with your index or join hint, it will always use that hint even when your table structures or data changes so much that it is obviously silly.  Optimiser hints such as FASTFIRSTROW, redundant predicates etc are OK with me, as they don't override the optimiser, they just tell the optimiser that you want a plan that will get the first row fast.

Thursday, April 4, 2013

Avoiding a Common Deadlock


A common cause of deadlocks is concurrent identical update/delete queries.  For example, a query such as
DELETE FROM MyTable WHERE MyCode=@code
When two queries start executing this query with different codes, they have a good chance of deadlocking.  This can be via lock escalation, where query A requests an escalation to a table lock, which will wait on the query B.  As soon as the query B requests a lock escalation, or any lock that query A has, there is a deadlock.  You can have a deadlock without table escalation, by the two concurrent queries requesting page locks.  Eventually query A might ask for a page that query B has and query B is waiting on query A.
A very simple solution to this kind of deadlock is to take a Table lock at the beginning of the query.  That way whoever gets in first will finish before the other query starts, removing the possibility of a deadlock on that one table.  This can be as simple as
DELETE FROM MyTable WITH (TABLOCKX) WHERE MyCode=@code
Taking a table lock will reduce parallelism.  No other queries (except NOLOCK) will be able to read the table while the TABLOCKX is held.   However, the reduction in parallelism is generally less painful that experiencing deadlocks.  Also, queries that DELETE/UPDATE many rows in a single query will necessarily lock large parts of the table (often the whole table if lock escalation is triggered), so taking an explicit table lock for the query generally is not too onerous.
Note, another trick that I sometimes use in stored procedures that are looping through many records with possible updates is to take an explicit lock on the table at the beginning of a transaction.  For example, the following code will take an explicit table lock on MyTable, which it holds until the transaction is committed.
BEGIN TRANSACTION
SELECT TOP 0 * FROM MyTable WITH (TABLOCKX)
-       - - put your code in here to perform updates/inserts/deletes on the table(s)
 COMMIT TRANSACTION
A couple of notes for deadlocks and locking
  • The amount of locking and deadlocks exponentially increase as transaction time increase.  So, anything you can do to reduce transaction time will reduce contention.
  • It is common practice for middle tier applications (eg .NET) to start transactions and call the database server multiple times within the transaction.  This is OK, but it is even safer (and reduced transaction time) if you encapsulate the logic in a stored procedure, and let the stored procedure begin/commit the transaction.
  • SELECT queries can be called without locks (NOLOCK).  DELETE/UPDATE/INSERT queries cannot be called without locking.
  • Read queries (DELETE/UPDATE/INSERT queries also read queries for this point) generally only lock the current record/page by using CURSOR STABILITY lock isolation.  It is possible for them to use more onerous strategies, such as, REPEATABLE READ, in which case they do not release read locks until the end of the transaction.  This can lead to contention and deadlocks with concurrent update queries. Sometimes these REPEATABLE READ read queries will benefit from an explicit table lock.
  • Lock escalation is generally your enemy in avoiding deadlocks.  If you see that a query is regularly escalating to a table lock, it is probably better for you to explicitly take a table lock at the beginning of the transaction/query as I outlined above.
  • Transactions can be started and committed while a stored procedure holds position on a cursor.  This way, you can commit and release locks every n records while processing rows in a cursor.
  • Use TRY CATCH in your procedures to handle exceptions such as deadlocks.

Wednesday, March 20, 2013

Pitfalls of Big Data

I have just assisted in another Big Data project review.  There appear to be a set of common pitfalls that new Big Data developers fall into.   In this blog I hope to shed light on a few pitfalls that are quite easy to avoid, but difficult to remedy. 

  • Insisting on flat file input sources, then simply load these into flat database tables.  I understand that some source systems are remote and that flat files are the appropriate vehicle to transport data to your warehouse.  However, if the source system is a relational system within your operations centre, then it will be much easier and more reliable to simply read the data straight from the source system.  It is more reliable, as databases will ensure data typing and referential integrity.  It will be faster as it avoids all the IO involved with writing the flat file and reading it again.  There is also the maintenance issue of adding/removing columns from flat files.  Typically both consumers and providers need to be synchronised for such a change, whereas a database can accommodate this change without the need for consumer and providers synchronising their change.
  • Measuring your Big Data project by the number of tera-, peta- or exa- bytes. This is an almost irrelevant figure.  Just like the amount of money companies spend on R and D won't tell you which one will come up with the next big idea. Big Data projects should be measured by the beneficial value of the information they provide.  When specifying storage requirements, amount of storage always comes after performance specifications (MB/second and ms/IO, for example).
  • Engaging the services company with the best Big Data sales person. Consultancy and software firms have discovered that Big Data projects can be very large and profitable.  Consequently, their best sales persons are employed to win, and they aim for the biggest projects. The big project is not always in the interest of the client, and the best salespersons are not always followed by the best developers. 
  • Technology is not your most important decision. The technology you choose is important, but it's not the overriding factor.  Also, as a corollary, only choose the technology, as you need it.  For example, if you are planning on taking 12 months to load the data, leave selection of the visualisation technology until after you have started loading.  This will enable you to make a better choice on the software (you will have some good sample data) and the software market will be a few months more mature.  
  • Purchasing production hardware before you need it.  If your project isn't going live for 12 months, there is no need to purchase production software until close to that time.  At that time you will have a better idea of the specifications required, and the hardware will be a little more advanced and/or cheaper.
  • Taking Kimball or Inmon literally and make all dimension attributes Type II.   Some naive Big Data developers worship Kimball and measure their technical prowess by how many Type II dimensions and attributes they have.  So much so that not only do they make your DW performance suffer, they remove natural keys making querying on current values impossible for end users.  It's OK to have Type II dimensions, but only where they benefit the business users.  Almost always, when you have Type II dimensions, you should also keep your business (natural) keys in the fact tables.  For example, a transaction record might have a surrogate key for Customer, which points to the customer record, as it was at that time.    If the Customer attributes change, later transactions will point to a different Customer record with a higher surrogate key.  Now, when the business selects transactions for customers that belong to "Harry", they wont see transactions for customers that belong to Harry unless Harry was the customer owner at the time of the transaction.  This can be very confusing and annoying to business users.
  • Reload entire tables that only get inserted to from source systems.  This is quite obvious even to naive developers and consequently an uncommon pitfall.
  • Reject data from the business because it doesn't conform to a Big Data rule.  For example, reject transactions because they have an invalid ItemId column.  This is a kind of "Holier than thou" issue.  The Big Data custodians want to keep their warehouse clean so they reject bad data.  The problem is that this record was for a sale, it has an amount and is part of the gross sales.  By rejecting it, the warehouse has got an invalid view of the business.  A valid view would be to include the transaction, albeit, with an "unknown" sales item.  It might be that at a later time the sales item table comes through with that missing ItemId, which the warehouse should accommodate without rejection or prejudice.
  • Force your business users to using one querying/visualisation technology.  The Big Data project will have selected specific software for collection, loading, storing, cubing, data mining etc.  However, the business users should be free to select the technology of their preference for querying and visualising this information.  Nowadays, structured data is well suited to OLAP cubes, and most warehouses exploit OLAP cubes.   There are hundreds of software applications that can consume cubes, and the business users should be free to use the ones they prefer.  It doesn't matter to the warehouse how many different cube browser technologies there are.  It might matter how many concurrent queries there are, but that's a different matter.

Wednesday, August 29, 2012

Vote for cubes in memory

As I have blogged before http://richardlees.blogspot.com.au/2011/12/why-doesnt-ssas-cache-entire-cube.html large multidimensional cubes are not retained in memory even though there is plenty of RAM and msmdsrv is using less than LowMemoryLimit.  Msmdsrv tends to hold about 2GB of aggregation cache, although this isn't a limit and it may be a result of the cache cleaner being too aggressive.   Whatever the reason, the problem is that msmdsrv won't hold data in cache even though there is plenty of RAM.   I have raised this as an issue with Microsoft, but I need your support to get this changed in the product.  Please go to https://connect.microsoft.com/SQLServer/feedback/details/760107/multidimensional-cube-is-not-retained-in-memory-even-though-there-is-plenty-of-ram  and vote for this enhancement.

This enhancement will mean that if you have a 20GB cube on a dedicated 100GB server, msmdsrv will retain the entire cube in memory and no further IO will be required.  As the product is now, with a high query load, the IO throughput can be measured in terabytes during a busy day.

By the way, in SQL Server 2012 we have Tabular models, which will store the entire cube in memory, but these models don't have the functionality that multidimensional cubes have, and they only work if the entire cube can fit in memory.

Thank you for helping make SQL Server a better product.

Sunday, January 29, 2012

Analysis Services Configuration Options

To see the SSAS configuration options (properties), right click on the service from SQL Server Management Studio and select properties. You will only see the Basic options, by default. To see all options, check the "Show Advanced (All) Properties".

Most of these options, you really shouldn't alter, but there are a few that you should know about. Here are a few SSAS properties that I think you should be aware of, and might want to change.

DataDir
This is where SSAS will, by default, put the SSAS database files. By default, this will be a sub directory of the "program files", which is not an ideal location. The SSAS databases should be on high performance storage. IO is very often a critical factor in large SSAS cubes.

ExternalCommandTimeout
As I understand it, this property is the number of seconds that SSAS will wait for the first row to return from an SQL query during processing. The default is 3600 seconds (1 hour) which is insufficient for many environments.  I often change this to 36000 (10 hours) in larger environments, or environments with Oracle as an rdbms (just kidding).

ForceCommitTimeout
This is the amount of time (milliseconds) that SSAS will wait on queries while performing process updates. A process update cannot commit while a query is active. The default is 30 seconds, which I think is quite reasonable, but you might have a need to change this value.

QueryLogSampling
The default value is 10, so that 1 in 10 queries are recorded. In practice, I either need all queries or none. So what I do is change this value to 0 to minimise overhead. When I am running a performance tuning exercise, I will temporarily change this to 1.

LowMemoryLimit
If this number is between 1 and 100, it is the percentage of memory that SSAS can use, without concern. By default it is 65, which tends to be an appropriate value. You might wonder why your 100GB server never sees 65GB memory for SSAS, even though your cubes are much larger and the IO demands are huge.  This is due to SSAS caching logic. Just because you have the memory and the cube is large doesn't mean that SSAS will hold the cube in memory. See my earlier blog http://richardlees.blogspot.com/2011/12/why-doesnt-ssas-cache-entire-cube.html

TotalMemoryLimit
You might see the LowMemoryLimit as a very soft limit, which can be exceeded when processing demands would benefit. The TotalMemoryLimit is a harder limit, but it can also be exceeded when necessary. One way that I look at these two options is the LowMemoryLimit is the memory limit during non processing times, and the TotalMemoryLimit is the limit when SSAS processing jobs are active. However, as I mentioned above, the more common concern is that SSAS isn't using the available memory, rather than it is using too much.

HardMemoryLimit
This is the real memory limit. Once SSAS reaches this value, it will start to cancel queries. I find that setting this value between TotalMemoryLimit and 100, will prevent one query (or several combined) from taking so much memory that SSAS over-commits the OS and causes the operating system to thrash so much it essentially stops servicing tasks. For example, an SSAS server might have TotalMemoryLimit set to 80 (that's 80% of available real memory) and I would set HardMemoryLimit to 82. That way, I know that if SSAS gets a rouge query that causes SSAS to overcommit virtual memory, it will cancel that query when SSAS reaches 82% of available memory. I would rather the query be cancelled than the OS die a slow death.

DrillThroughMaxRows
I haven't ever changed this property. Since 2005, SSAS hasn't offered a real drill-through. This is really a drill down option. If you want to support drill through see my blog http://richardlees.blogspot.com/2009/01/using-drill-through-in-analysis.html

Port
If you want to change the port that SSAS listens on (or you are running multiple instances) this is where you specify the port number. By default this value is 0, which denotes 2383.

ServerTimeout
This is the limit for SSAS queries in seconds. After this time (default 3600 or 1 hour) mdx queries will timeout. You may have reason to increase or decrease this value.