Files and Filegroups implementation in SQL Server

Usage of different filegroups is a very good feature that is available in SQL Server as well as many other RDBMS (though in other RDBMS like Oracle and DB2 UDB, the concept is a bit different for tablespaces but similar feature exists).  Filegroups allow files to be grouped together for administrative and data allocation/placement purposes. For example, three files (data1.ndf, data2.ndf, and data3.ndf) can be created on three disk drives, respectively, and assigned to the filegroup fgroup1. A table can then be created specifically on the filegroup fgroup1. Queries for data from the table will be spread across the three disks, thereby improving performance.

The same performance improvement can be accomplished with a single file created on a RAID/SAN stripe set. Files and filegroups, however, allow you to easily add new files on new disks. Additionally, if your database exceeds the maximum size for a single Windows file, you can use secondary data files to allow your database to continue to grow.  The advantage of using such an approach is that for large database systems you can keep the filegroups on separate disks, thus increasing response time for your queries.

One other advantage relates to quick backup and recovery. You can take the backup of separate filegroups and hence restore only a particular filegroup and continue working. This can be particularly useful in cases of production databases where these activities are time critical and, of course, minimizing downtime is always desirable.

You should never store objects in the primary filegroup which is also used for storing the system objects in the user database.  Filegroups are also a good choice if you have decided to implement partitioning for Very Large Databases (VLDBs).  Where RAID disk technology is being used, multiple filegroups can be set up to span different stripe sets.  The objective here is to spread the I/O as evenly as possible across the physical disk devices.

When you are using filegroups, how often have you seen scenarios where-in you have a multi-processor powerful box but all the processors are not being utilized ?  You take a look at the processor usage, either through task manager or Performance (System) Monitor, and notice only one processor seems to be hard at work, the others are just idling.  Well,  before you start cursing Microsoft, have a look at how many files you have for your database out on disk.  Is it one .mdf and one .ldf – the default ?  Yes?  Well that’s most likely your problem – you can only write to one file with one thread.  Your database has become IO bound as only one processor can write to the database at any one time.

The solution is to create a new database (or add to the existing database) with as many data files (all  the same size) as you have processors and transfer the data from the old database into this new one – this is to ensure you get an even proportional fill across the files.

You can still run into issues at times though even after you do this.  If you do create the files properly and still see this issue, then you have had automatic database growth turned on for this database.  Assuming when you created the database you created it with all the database data files the same size, then in such a scenario,  at least one of those files would have now grown beyond that size?  If this is the case then I think your problem is you’ve lost ‘proportional fill’.  Look at the following excerpt from Books Online (BOL).

 From Books Online: As data is written to the filegroup, Microsoft® SQL Server™ writes an amount proportional to the free space in the file to each file within the filegroup, rather than writing all the data to the first file until full and then writing to the next file.  As soon as all the files in a filegroup are full, SQL Server automatically expands one file at a time in a round-robin fashion to accommodate more data (provided that the database is set to grow automatically).

You can loose proportional fill because now you only have the one, newly expanded, file to write to; all the others are full!  With only one file to write to, you are essentially only able to use one CPU

Solution is to avoid automatic database growth on multi-processor system and have alerts in place to notify you well in advance when the size is going to become an issue.

Difference in Clustering architecture

A very common question that we get from our clients is the differentiation between the clustering architectures of Oracle vs SQL Server. Many shops are familiar with only one and when they implement the other RDBMS, the behavior is very different and to some it comes as a surprise

SQL Server supports shared nothing architecture i.e. at any given point of time, among the nodes of a cluster only one of the nodes will have ownership of the databases and hence access to the shared resources i.e. clustering in the case of SQL Server is not for load balancing – it is only for failover and high availability unlike Oracle-RAC which deploys a shared-everything architecture i.e. multiple nodes access the shared resources at the same time and hence provide load-balancing, failover and high availability.

Active-Active clustering in the case of SQL Server means that you have 2 sets of Active/Passive clusters i.e. say you have 2 machines(nodes) and the external storage is on the SAN, then you have this configuration:

Machine 1 Machine2

Active Node 1 Passive Node 1

Passive Node 2 Active Node 2

|———————————————-|

| STORAGE |

|———————————————-|

So, an OLTP application can be put on say Cluster1 and the node on machine one shares all the shared resources. Another application like say JD Edwards is also being run and this is on node 2 and the node on Machine2 owns the shared resources and is the active node while the Node on Machine 1 becomes passive node.

Having such a configuration ensures that no machine is sitting idle and that the hardware is getting utilized properly. Proper care needs to be taken though when sizing the hardware such that in the event of a failover of one of the nodes, the other machine can host both the nodes and thus processor and memory needs to be sized accordingly.

It does not provide horizontal scalability like RAC does (RAC – Real Application Clusters in Oracle has a very different architecture and we are writing a whitepaper on that which will soon be put on our site)- and the reason is that it is a shared nothing architecture. Horizontal scalability can be achieved through different means though like DPVs implementation & a proper failover scheme but requires changes to the application as well.

I hope this helps explain this difference a bit…

Estimating & Projecting the size of a table in Oracle

The ONLY way to really accurately size a table (and project it’s future growth) is to load it with a small percentage of the real data, analyze it, and multiply.   You can use Tom Kyte’s (asktom.com) show_space code to help you with the #of blocks evaluation or just use a plain simple technique shown below.  Many people just use the average rowlength (avg_row_len column) in order to ascertain the size after doing a CTAS (Create Table AS)…however, that is not accurate as we will show below:

Example:

SQL> CREATE TABLE TEST AS SELECT * FROM ALL_OBJECTS;
Table created.

EXEC DBMS_STATS.GATHER_TABLE_STATS( USER, ‘TEST’);

SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME = ‘TEST’
/

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
———- ———- ———— ———–
    183546       2694            0          98

So, the average rowlength is being reported as 98.  

The “right” and the only sure-shot way of calculating the size would be to calculate using these steps:

1) We check on the existing schemas and that will give us the tables filled with representative data (say expected volume for one of the schemas for a large table is say 1 million rows – we check the # of blocks for n number of rows).
2) Collect DBMS_STATS.
3) Check the number of blocks.
4) Then multiply by the multiplying factor i.e. if we estimated for say 1% of the actual requirement, multiply by 100

Example (Same table from above):

SQL>
SQL> compute sum of blocks on report
SQL> break on report
SQL> select extent_id, bytes, blocks
  2      from user_extents
  3     where segment_name = ‘TEST’
  4       and segment_type = ‘TABLE’
  5  /

–FYI: Results abridged for easy reading
 EXTENT_ID      BYTES     BLOCKS
———- ———- ———-
       330      65536          8
       331      65536          8
       332      65536          8
       333      65536          8
       334      65536          8
       335      65536          8
       336      65536          8
                      ———-
sum                         2696

SQL> select blocks, empty_blocks,
  2           avg_space, num_freelist_blocks
  3      from user_tables
  4     where table_name = ‘TEST’
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
———- ———— ———- ——————-
      2694            0          0                   0

So :
1)  We have 2696 blocks allocated to the table TEST. 
2)  0 blocks are empty (of course – in this example that is bound to happen – not in reality though).
3)  2694 blocks contain data (the other 2 are used by the system).
4)  Average of 0k is free on each block used.

So,

1) Our table TEST consumes 2694 blocks of storage in total for 183546 records.
2) Out of this : 2694 * 8k blocksize – (2694 * 0k free) = 21552k is used for our data.

The calculation from the average row-length would have yielded : 183546 * 98 = ~17566k (see the difference ?).

Also, now that we have the calculation, if the actual table TEST needs to be sized for say 10 million records, then we use the multiplying factor for it :

183546 records take —> 21552 k
10 million will take —-> (21552k * 10 million) / 183546

That way, you will be assured that the data calculations are correct.  Likewise for the indexes.  Oracle Guru Tom Kyte has a lot of very good examples on his site that you should read before you embark on your sizing calculators for an Oracle Schema.

Execution Plan at runtime

How often has it occured that one of your clients calls you and tells you that the performance of your application is going downhill because of some ugly SQL statement?  And then you extract that SQL out (either from the application logs if you have good code instrumentation and were using it at the time that it occured) or you take it out from the cache.  However, in order to run it, you need the values for those bind variables (assming your application is using bind variables –> it should if it is an OLTP system, datawarehouses are a different ball game).  If you do not have those bind variable values available, you take a best guess or ask the developers for those values and then run the SQL to see what the execution plan is.  However, this will not tell you the execution plan that the optimizer came up with when that actual issue had occured.  Since the situation could have been different at the time of the execution (concurrent load on the system could be different, the IO or CPU or memory restraints could have been different, the bind variable values themselves could be different etc.), there is no assurance that your current execution matches with the one that occured at run time.

In order to see the execution runtime plan, you can extract it out from the cache if it has not been aged out.  Here is how you can do it:

– Get the Statement directly from the Shared_pool
– NOTE: First get the related values from v$sql and then see actual plan executed by that sql by putting those values in the next SQL:

SELECT sql_id,address,hash_value,child_number FROM V$SQL WHERE SQL_TEXT LIKE ‘….’;

SELECT     id, parent_id, LPAD (‘ ‘, LEVEL – 1) || operation || ‘ ‘ ||
           options operation, object_name
FROM       (
           SELECT id, parent_id, operation, options, object_name
           FROM   v$sql_plan
           WHERE  address = ‘070000007BBC97D0′
           AND    hash_value = 2692560189
           AND    child_number = 0
           )
START WITH id = 0
CONNECT BY PRIOR id = parent_id;

– Alternate method
– Extracting the optimizer environment settings for SQL that is executing from v$SQL_PLAN OR from V$SQL
– Supply SQL_ID and SQL_CHILD_NUMBER

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR((‘SQL_ID’),CHILD_NUMBER));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR((‘5qk509xugpmpv’),1));
– Displays execution plan from last executed SQL
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR();

More on sp_configure

While on the same topic, here is a comparison of the instance level settings between SQL Server 2005 and SQL Server 2000.

Some of the default values have changed in SQL Server 2005 and there are 15 new parameters in SQL Server 2005. Table shown in the attached document below displays those differences and also lists out the new parameters in SQL Server 2005. The differences and new parameters are marked in bold blue to make them easier to spot.

sp_configure.doc

SQL Server – sp_configure

We were brought in to look at a production system today and had to change one of the instance level parameters using sp_configure. We made the recommendations and presented it to the client who then proceeded with immediately putting those in the production environment despite out warning.  One point of caution that everyone should be aware of is that when you use the sp_configure command to re-set the value for an option, it also issues DBCC FREEPROCCACHE command. As you know, this DBCC command removes all the stored procedure plans from the cache and that in turn means that it will then require a recompilation of new plan the next time the stored procedure runs.  The reason why this is done probably is because some of the options can invalidate stored procedure plans and thus there is this general rule of invalidating the cache.

Luckily, the client system was not a heavily hit OLTP application so the effect of this change was very less in their environment.