Search results matching tags 'Storage Design' and 'performance'

T-SQL Tuesday #004: Real World SSD’s

merrillaldrich — Tue, 09 Mar 2010 04:22:00 GMT

A contribution for T-SQL Tuesday #004, hosted by the illustrious Mike Walsh!

In the past few weeks I had some correspondence with Kendal Van Dyke leading up to his SQL Saturday presentation on SSDs, and he got me fired up to share a little of my team’s experience with a real implementation. Over the past four months or so, our IT group at work has deployed a new disk array incorporating enterprise-class fibre channel SSDs for database functions, and I am happy to report it’s been a success. I do have a few nuggets that I learned along the way, which might be useful if you are contemplating a move like this. Some of these tips I’ve talked about before, but they are doubly true in the rarefied (read: expensive) air surrounding SAN SSD’s. You really don’t want to sink this kind of money and not get the maximum possible return!

Project

In the Fall our organization faced a large data migration to a second datacenter, to enhance our disaster recovery and business continuity position. This entailed the purchase of some new infrastructure, and for various reasons the group decided a storage array including SSD’s made strategic sense for the next several years. I had had one eye on SSD’s when they started becoming cost-effective enough to make it into the smaller scale TPC benchmarks – indicating the cost/performance ratio was starting to make sense. Our SAN admin had also been watching them too, for all kinds of reasons, and I think I might have seen her drooling a little when this notion came up.

Anyway, we were able to obtain an EMC Symmetrix-class array with three tiers of storage, from SSD’s at the high end to 15k Fibre Channel disks to SATA disks. The array was incorporated into our existing SAN, and attached to a combination of new and repurposed servers, to create production environments for both Oracle and SQL Server. We tested the complete environment as pre-production for several weeks, mainly to validate the IO performance of this new architecture relative to what we had been running, and then have progressively cut production systems over to the equipment.

I can say that it’s performed really well – the Oracle and SQL Server systems that have SSD storage on the new array definitely saw a net boost in performance. Most of the time, our Oracle DBA’s report that they see about a 2/3 increase in performance, with some exceptions. I am running a data warehouse function on the SSD’s, and I can say we shaved perhaps 25% off of our ETL/load window by moving to the new hardware. At the same time, I would like to caution that, though it might seem like these things are supernatural in terms of performance, they are not a make-all-your-problems-disappear silver bullet. They are dramatically faster for some particular tasks, but not for everything.

Method

With help from EMC, EMC professional services consultants and our internal staff, we went through this overall process to design and deploy:

We came up with a general size and performance profile of all the existing systems we planned to move and/or consolidate onto the new array. Importantly, we got the data from real performance stats collected off our existing servers and arrays, so we could describe to EMC what the real IO requirements looked like, as well as the capacity needed. IO usually drives the cost of storage more than capacity in the database world, and we had to take pains to make sure our execs and other parties didn’t get sidetracked into looking only at capacity (so many TB) instead of performance.
EMC came back with a general design showing how many of what type of disks would be in the array. That design set most of our budget parameters :-). The new array ended up being sort of mid-range, with 46 200 GB STEC Fibre Channel SSD’s, 124 450 GB 15k RPM FC Disks, and 36 1TB 7200 RPM SATA disks.
While the array was in the manufacturing and testing queue at EMC, we did a deep dive into the specifics of how to arrange the data on all the available devices. We took about a month’s worth of performance counters from all our existing storage and mashed that into aggregate IO targets for the new servers on the new array. EMC professional services then took that data and proposed a design showing exactly how the LUNs and files would physically lay out. We did a couple of back-and-forth iterations before settling on the final design.
The basic guiding principle around the SSDs was to use them for the files that would benefit most: the read-intensive ones. We literally found the read-intensive files or disks on our existing systems from the IO counters and placed those, only, on the SSD tier. The rest of the files are on conventional 15K FC disks. All the SSD’s are set up as RAID5 (reads being the priority) and all the 15K disks are mirrored pairs, for performance.
Then The Beast, as I like to call it, was delivered, put together and health-checked by EMC.
We moved our pre-production data to the new system, and ran automated performance tests for about a month, just so we knew how to expect this thing to act.
Finally, we moved production systems over one at a time.

Take-Aways

I learned a lot going through this process – and it was really fun, to boot. Here are some basic ideas to think about if you are looking at SSDs in SAN:

If you don’t have a system for gathering IO performance counters on a rolling basis I strongly recommend, as I have before, getting or building one. This capability is vital to right-size your infrastructure and spend money wisely. I think it’s most cost-effective just to buy a monitoring system (and put your valuable working hours into something business-specific that can’t be purchased), but if you can’t afford a monitor, it’s not very hard to build a basic counter-collection system. Worst case, it is possible to log this data with perfmon, but that takes extra effort and can push your schedule out, as you have to gather the data when needed rather than just having it already in a repository.
Resist the idea that SSD’s are just so miraculously fast that it’ll be like RAM, or make all your performance worries disappear. In fact they solve mainly one problem – an important problem, but one only. They avoid the penalty of random access to disks, which requires mechanical moving parts to get to some region of a disk, then pass the data on the disk past the disk head. If you suffer from that problem, they will help – a lot. If not, then you should dig deeper and see if SSD’s will really be an advantage for your workload. Writes might not be that much faster, nor sequential reads, depending on the details. You might be best off with a hybrid solution that uses two kinds of storage, and it’s worth considering a design like that. Be realistic about their true performance characteristics and set expectations appropriately.
You still have to RAID the devices together. SSD’s can fail just as disks can fail, and if you are running a production system then you will want the redundancy. That obviously has a cost impact. On the other hand, the arithmetic might go like this: if you need, say, 48 disks in RAID 1+0 to get good performance for an existing server (and those disks are probably mostly empty) but SSD’s could get you the same performance (imaginary numbers here) with only 6 or 8 devices in RAID5, then maybe the budget starts to look attractive.

Still, it was really fun last week when I spied our little data warehouse doing over 7,900 IOPS! Sweet!

SAN 101 for the DBA

merrillaldrich — Tue, 16 Feb 2010 22:45:00 GMT

As will become apparent from this post, I am no Storage Admin. My apologies for offending the sensibilities of those who know this topic better than I do!

I get asked occasionally about placing SQL Server data on SAN storage, and I've done it with a few systems, and a lot of smart people helping me, so here's a SAN 101 crib sheet for DBAs who may be new to this.

SAN Basics

SAN stands for Storage Area Network, like LAN or WAN. The first thing of note is that it's a network, and not a device. Often it's a fiber-optic network, for speed and bandwidth, but sometimes it can be a copper network. It's easy to lose sight of the fact that its a network, because it's not what we all think of as "the network," and because the storage admins are usually the only ones that play there. Hence the colloquial term "a SAN" referring to one storage device. One device on the network is really a Storage Array (SA?), not a Storage Array Network (SAN). Tomato, tom-AH-to, I suppose, but it's important in larger environments to talk about one array separately from the storage network itself.
Because it's a network, there's a network protocol for devices to communicate. Usually that is FCP for fiber or iSCSI for copper (with exceptions.) The protocols really send SCSI commands, the same kind many hard disk devices use within a single server. That means, for practical purposes, that devices a server "sees" on the SAN look and act like hard disks rather than network devices. There can be switches in a SAN, and so on, just like other networks.
Servers typically connect to a fiber SAN with one or two (maybe more) Host Bus Adapters (HBAs). An HBA just acts as an interpreter, making the devices visible on the storage network appear to the host bus, transparently, as if they were disks. So, if you are building such a setup, you'll have a server with a couple of HBAs, those are connected to a switch or two on the fiber network, which is then connected to one or more storage arrays. If a chunk of storage on one of the arrays is allocated for your server, then you see that storage space as if you'd stuck a hard disk in. Partition, format, write. Simple!

Rule 1: There is no Magic

Storage vendors do have a reputation (they are selling things, after all) to pitch their stuff in a way that makes it seem like all your performance worries will vanish in some kind of magic fiber-optic {poof}. Not so, I'm afraid. Enterprise arrays are good, but they are still subject to the laws of physics. The disks in an array are still disks, the cache is just a cache. This is good for the DBA, in a way, because if you know how to size direct attached storage by spindle count and IOPS, then - whooee! - it's not really that different. There are things that help performance some, like a huge write cache with write re-ordering and fault tolerance, but you don't have to throw out what you know and start over. Lots of random IO still means you need lots of random IO capacity, which still means disks.

Rule 2: Performance Costs More than Space

The fundamental mistake I have seen is to hand a SAN administrator (or your SAN vendor) a spec about how much space you need for your data. That is a recipe for disaster. What is important is what the performance profile is, and, just as with plain ol' disks, IOPS will determine what the hardware has to look like in an array. And you want to do that before you get the price quote, because the performance, not the space, will drive the cost. Ask for a terabyte? You might get one 1 TB SATA disk. That won't be happy at all.

Rule 3: Yes, Direct Attached Storage is Cheaper ... But

OK, so there's a whole economics problem to be aware of. SAN storage is expensive, sometimes very expensive. In some circumstances, it's still worth it, but the price tag is undeniable. Examples: if you have the same amount of storage (or in the SQL world, amount of storage performance) in direct attached disks, and in SAN storage, the SAN flavor is going to cost a lot more up front. It's pretty easy to spend $1,000 per disk for 15k fiber channel disk drives in an array.

However, with real IT in real organizations of any size, things are more complicated - there isn't one, unchanging and monolithic system. In fact, things, servers, storage requirements, circumstances, applications all change at a breakneck pace. What we do, most of the time, is try to accomodate change and keep things "up." That is life for many IT pros. So it is not cheaper to have piles and piles of unutilized or overspec'd direct attached storage sitting around, married to single servers. If you had to buy twice what you needed, where's the savings? When you buy into a SAN what you are buying is, or should be, flexibility. The flexibility to use it fully, all the time, and to change the storage allocation for all your servers without physically scrapping storage hardware and buying new. If you have a SAN, and it's not providing that value, then you probably wasted your money.

So, if you have one relatively static database server and you're looking at storage options, direct-attached might save you a pile of money. Don't build a SAN for performance around a couple of servers -- there's no compelling performance-per-dollar argument. On the other hand, if you find that you look around your enterprise and see tens or hundreds of servers where there's a huge waste factor for storage in some places, and full disks in others, there's your SAN opportunity. This server over here is low on disk space? OK, let's just add some. That one had too much, and we're wasting space? Take some away. Storage over here is too slow? Migrate it to a set if disks with more performance.

In other words:

Best case: one SQL Server or cluster with correct direct attached storage, fully utilized. Nice, but this is the real world!

Next best: a heterogeneous, changing environment that uses SAN storage to best advantage

Worse: a collection of over-spec'd DAS hardware that is under 50% utilized, but can't realistically be changed. It seemed cheap at the time, but there's so darn much of it!

Worst: a big, expensive, under-utilized SAN

Rule 4: You Need a Good Relationship with the SAN Admin

I've blogged about this before, but suffice it to say that bad communication with the SAN admin = FAIL. SQL Server often has unique and demanding IO requirements that don't go away just because you have a fancy array. You have to be able to work that out with the storage admins, if you have them, or the vendor, if you are in a smaller shop. Together you will have to talk through the need to separate logs, data and backups, and what the performance profile of each "virtual" disk system needs to be, backed by perf counter data, to prevent the SAN nightmare: "We spent our $5,000,000 and the VP wants to know why it's SLOW."

Choices in physical storage design – EAV Case Study

merrillaldrich — Tue, 24 Nov 2009 00:30:00 GMT

Partitioning for Fun and Profit

Over the past year or so I’ve been playing with a toy SQL monitoring application, just to sharpen my dev skills a bit. Part of the app is a SQL database to store historical performance data, in order to do trending. Fooling with the design of the performance database allowed me to look at EAV (Entity Attribute Value) versus other patterns and do some real performance measurements.

I went into this design with some preconceptions about EAV:

I figured EAV could be a good fit for this problem, given that one would not know which of the hundreds of available performance counters the app would track until run time. At design time, all I know is that the database has to store some unknown, user-defined collection of performance counters. It seems like the classic EAV problem of run time, user-defined attributes.
I predicted that EAV would be more convenient to implement than a dynamic / multiple table design because the schema would not change at run time. The question is, how much more convenient? Conversely, how much effort and risk is there in extending the schema at run time?
I thought there would be tradeoffs, where EAV is bigger and slower than having tables built out, especially when it comes to reporting queries. Generally it's possible to boost database performance when you can partition sub-sets of rows into physically separate structures, rather than having them interleaved together in one huge table. The questions here are, how much bigger, and specifically which kinds of queries are slower? Are there concrete rules that can indicate when EAV will work well or not?

To set the stage for this, I am planning to store the performance data in a way that aligns with how Windows performance counters are structured: there’s a tree of counters labeled performance objects / counters / instances in perfmon or counter categories / counters / instance names in C#. It’s pretty easy to dump out a tabular list of available counters with a simple C# or PowerShell snippet:


using System;
using System.Text;
using System.Diagnostics;

namespace ListPerfCounters
{
    class Program
    {
        static void Main(string[] args)
        {
            using (System.IO.StreamWriter outfile 
                = new System.IO.StreamWriter("c:\\CounterList.txt"))
            {
                PerformanceCounterCategory[] cats 
                    = PerformanceCounterCategory.GetCategories();
                foreach (PerformanceCounterCategory cat in cats)
                {
                    // Exclude category "Thread" to eliminate an insane number of counters:
                    if (cat.CategoryName != "Thread")
                    {
                        // Single instance categories get labled as instance = "Default":
                        if (cat.CategoryType == PerformanceCounterCategoryType.SingleInstance)
                        {
                            PerformanceCounter[] counters = cat.GetCounters();
                            foreach (PerformanceCounter counter in counters)
                            {
                                outfile.WriteLine(cat.CategoryName + "\t" 
                                    + counter.CounterName 
                                    + "\tDEFAULT");
                            }
                        }
                        // Multi-instance categories are listed out per instance:
                        else if (cat.CategoryType == PerformanceCounterCategoryType.MultiInstance)
                        {
                            String[] instances = cat.GetInstanceNames();
                            foreach (String instance in instances)
                            {
                                PerformanceCounter[] counts = cat.GetCounters(instance);
                                foreach (PerformanceCounter counter in counts)
                                {
                                    outfile.WriteLine(cat.CategoryName + "\t"
                                        + counter.CounterName + "\t"
                                        + instance);
                                }
                            }
                        }
                        //Ignore CategoryType = unknown
                        else
                        {
                            // do nothing
                        }
                    }
                }
            }
            Console.WriteLine("Press the 'Any' key...");
            Console.ReadKey();
        }
    }
}

The output is a tab-delimited list of most available counters:

Processor	% Processor Time	_Total
Processor	% User Time	_Total
Processor	% Privileged Time	_Total
Processor	Interrupts/sec	_Total
Processor	% DPC Time	_Total
Processor	% Interrupt Time	_Total
Processor	DPCs Queued/sec	_Total
Processor	DPC Rate	_Total
Processor	% Idle Time	_Total
Processor	% C1 Time	_Total
Processor	% C2 Time	_Total
Processor	% C3 Time	_Total
Processor	C1 Transitions/sec	_Total
Processor	C2 Transitions/sec	_Total
Processor	C3 Transitions/sec	_Total
Processor	% Processor Time	0
Processor	% User Time	0
Processor	% Privileged Time	0
Processor	Interrupts/sec	0
Processor	% DPC Time	0
Processor	% Interrupt Time	0
Processor	DPCs Queued/sec	0
Processor	DPC Rate	0
Processor	% Idle Time	0
Processor	% C1 Time	0
Processor	% C2 Time	0
… etc …

First the EAV schema

The EAV pattern essentially says that since we don’t know what attributes (what specific performance counters, in this case) need to be stored, we store the data in labeled rows, instead of in columns – rows being really easy to change at run time, while columns are not. The simplest implementation of this is with two tables, one small table of monitored servers, and one huge table of labeled performance counter values. If a “sample” is defined as a set of specific counter values at one point in time, then this big table will have a set of rows for each server, for each time point, where each row is labeled per performance counter:


CREATE DATABASE [CountersEAV] 
GO

ALTER DATABASE [CountersEAV] SET RECOVERY SIMPLE 
GO

USE [CountersEAV]
GO

CREATE TABLE dbo.[Servers](
      ServerID int IDENTITY(1,1) NOT NULL,
      ServerName varchar(128) NOT NULL,
      [Description] varchar(255) NOT NULL,
      CONSTRAINT PK_Servers PRIMARY KEY CLUSTERED ( ServerID ASC )
);

GO

CREATE UNIQUE NONCLUSTERED INDEX UniqueServerNames ON dbo.[Servers] 
(
      ServerName ASC
);
GO

CREATE TABLE dbo.CounterSamples(
      SampleTime datetime NOT NULL,
      ServerID int NOT NULL,
      PerfObject nvarchar(30) NOT NULL,
      Counter nvarchar(50) NOT NULL,
      Instance nvarchar(30) NOT NULL,
      Value sql_variant NOT NULL,
 CONSTRAINT PK_CounterSamples PRIMARY KEY CLUSTERED 
      (
            SampleTime ASC,
            PerfObject ASC,
            [Counter] ASC,
            Instance ASC,
            ServerID ASC
      )
); 

GO

ALTER TABLE dbo.CounterSamples
WITH CHECK ADD  CONSTRAINT FK_CounterSamples_Servers FOREIGN KEY( ServerID )
REFERENCES dbo.[Servers] ( ServerID );
GO

ALTER TABLE dbo.CounterSamples CHECK CONSTRAINT FK_CounterSamples_Servers;
GO

Inserting the counter data into this table is trivial:


CREATE PROCEDURE saveCounterSample (
      @SampleTime datetime,
      @ServerID int,
      @PerfObject nvarchar(30), 
      @Counter nvarchar(50), 
      @Instance nvarchar(30), 
      @Value float
) AS
BEGIN
      SET NOCOUNT ON;
      
      INSERT INTO dbo.CounterSamples (
            SampleTime,
            ServerID,
            PerfObject,
            [Counter],
            Instance,
            Value
    ) VALUES (
            @SampleTime,
            @ServerID,
            @PerfObject,
            @Counter,
            @Instance,
            @Value
    );
    
END               
GO

Next, EAV Sample Data

In order to see how this will perform at scale, we need to populate it with a lot of sample data, certainly more than will fit in memory. I’ve got a script that can do this by just creating random values for a reasonable set of different counters, in bulk:


INSERT INTO dbo.[Servers] ( ServerName, [Description] ) 
      VALUES ( 'MyServer', 'Test Server' );
 
INSERT INTO dbo.[Servers] ( ServerName, [Description] )
      VALUES ( 'BigServer','Big One' )
 
INSERT INTO dbo.[Servers] ( ServerName ,[Description] )
      VALUES ( 'ProdServer' ,'Some Server' )
 
INSERT INTO dbo.[Servers] ( ServerName ,[Description] )
      VALUES ( 'SQLProd01' ,'Yet Another Server' )
 
INSERT INTO dbo.[Servers] ( ServerName ,[Description] )
      VALUES ( 'SQLProd02' ,'Still another' )
GO

Sample counter values (note: this makes a decent quantity of data, four weeks of one minute sample intervals for 194 counters on five imaginary servers 4 * 24 * 60 * 5 * 194 ~= 39,000,000 rows)


-- Populate with test data

-- Create rows for many counters representing a 24 hour period

DECLARE @now datetime;
SET @now = '2009-01-01';
DECLARE @val float;

WHILE @now < '2009-01-02' BEGIN
      BEGIN TRANSACTION;
      
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Memory', N'Pages/sec', N'Default', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'PhysicalDisk', N'Avg. Disk Queue Length', N'_Total', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'_Total', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'0', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'1', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'2', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'3', @val;
      
/* … repeat for many sample counters … */

      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'MSSQL$TEST2000:General Statistics', N'User Connections', N'DEFAULT', @val;
      SET @val = RAND() * 10; 
EXEC saveCounterSample @now, 1, N'MSSQL$TEST2000:Locks', N'Average Wait Time (ms)', N'_Total', @val;
      
      COMMIT;
      
      SET @now = DATEADD( minute, 1, @now );
END;
GO

-- Use the first day's values and a random multiplier to mock test data for a month:
DECLARE @i int;
SET @i = 1;
WHILE @i < 28 BEGIN

      INSERT INTO dbo.CounterSamples (
            SampleTime,
            ServerID,
            PerfObject,
            [Counter],
            Instance,
            Value
      ) SELECT
            DATEADD( DAY, @i, SampleTime ) as SampleTime,
            ServerID,
            PerfObject,
            [Counter],
            Instance,
            CAST ( Value as float ) * ( RAND() * 1.5 ) as Value
      FROM dbo.CounterSamples
      WHERE '2009-01-01' <= SampleTime and SampleTime < '2009-01-02';
      
      SET @i += 1;
END;
GO

-- Use the whole month's data and a random multiplier to generate mock data for more servers
DECLARE @i int;
SET @i = 2;
WHILE @i <= 5 BEGIN
      
      INSERT INTO dbo.CounterSamples (
            SampleTime,
            ServerID,
            PerfObject,
            [Counter],
            Instance,
            Value
      ) SELECT
            SampleTime,
            @i as ServerID,
            PerfObject,
            [Counter],
            Instance,
            CAST ( Value as float ) * ( RAND() * 1.5 ) as Value
      FROM dbo.CounterSamples
      WHERE ServerID = 1;
      
      SET @i += 1;
END;
GO

Index rebuild at the end, as the table is almost certainly highly fragged.

This design does function, but there are some challenges:

The CounterSamples table is obviously huge, and highly repetitive because of the row labels.
A query is most likely to “want” the data from one counter or another, as separate sets, but practically never from a mixture of different counters. Directly aggregating the “total % processor used” values with, for example, the “percent log used” values makes no sense, because the counters measure two unrelated metrics. So it seems like we have rows mixed together that most queries will want to separate.

Counterpoint: Dynamic Table Solution

Another possible schema is driven by the notion that at query time this data “wants” to be partitioned by counter, so all the values for each specific counter are in one place. Since we don’t know the counter list until run time, it means we have to code around the fact that tables need to be added dynamically to the schema. This does add to the complexity of the code, while possibly paying dividends in storage requirement and performance.

First, we can use the same Servers table as in the first example, so assume we have that one. Next, we will need to be able to dynamically generate a table to store counter samples for any specific performance counter. A template for such a table might look like this:


CREATE TABLE dbo.[LogicalDisk_% Disk Read Time__Total](
      SampleTime datetime NOT NULL,
      ServerID int NOT NULL,
      Value float NOT NULL,
 CONSTRAINT [PK_LogicalDisk_% Disk Read Time__Total] PRIMARY KEY CLUSTERED 
      (
            SampleTime ASC,
            ServerID ASC
      )
); 
GO

ALTER TABLE [dbo].[LogicalDisk_% Disk Read Time__Total]  
WITH CHECK ADD  CONSTRAINT [FK_LogicalDisk_% Disk Read Time__Total_Servers] FOREIGN KEY([ServerID])
REFERENCES dbo.[Servers] ([ServerID])
GO

ALTER TABLE dbo.[LogicalDisk_% Disk Read Time__Total] CHECK CONSTRAINT [FK_LogicalDisk_% Disk Read Time__Total_Servers]
GO

But, code has to be written to take a performance counter and generate a table like this at run time. In a “real” scenario, it probably makes sense to do that client side, but to mock this up I am going to make a fake, dynamic SQL driven stored procedure that will generate these tables:


CREATE PROCEDURE addCounterTable ( 
      @PerfObject nvarchar(30), 
      @Counter nvarchar(50), 
      @Instance nvarchar(30) 
) AS
BEGIN

DECLARE @tableName varchar(128);
SET @tableName = @PerfObject + '_' + @Counter + '_' + @Instance;

IF NOT EXISTS (
      select * from INFORMATION_SCHEMA.TABLES 
      where TABLE_SCHEMA = 'dbo' 
            and TABLE_NAME = @tableName
      ) BEGIN

            -- Requested table is missing, so create it

            DECLARE @qtablename nvarchar(128)
            DECLARE @qpkname nvarchar(128)
            DECLARE @qfkname nvarchar(128)
            
            SET @qtablename = QUOTENAME( @tableName );
            SET @qpkname = QUOTENAME( 'PK_' + @tableName );
            SET @qfkname = QUOTENAME( 'FK_' + @tablename + '_Servers' );
            
            declare @createStmts nvarchar(max);
            
            set @createStmts = 

            'CREATE TABLE dbo.' + @qtablename + '( ' + CHAR(10)
            +     '  SampleTime datetime NOT NULL, ' + CHAR(10)
            +     '  ServerID int NOT NULL, ' + CHAR(10)
            +     '  Value float NOT NULL, ' + CHAR(10)
            + 'CONSTRAINT ' + @qpkname + ' PRIMARY KEY CLUSTERED ' + CHAR(10)
            + ' ( ' + CHAR(10)
            +     '  [SampleTime] ASC, ' + CHAR(10)
            +     '  [ServerID] ASC ' + CHAR(10)
            + ' ) ' + CHAR(10)
            +'); ' + CHAR(10)

            + 'ALTER TABLE dbo.' + @qtablename + ' '  + CHAR(10)
            + 'WITH CHECK ADD CONSTRAINT ' + @qfkname + ' '  + CHAR(10)
            + 'FOREIGN KEY( ServerID ) ' + CHAR(10)
            + 'REFERENCES dbo.Servers ( ServerID ); ' + CHAR(10)

            + 'ALTER TABLE dbo.' + @qtablename  + ' ' + CHAR(10)
            + 'CHECK CONSTRAINT ' + @qfkname + '; ' + CHAR(10)

            -- PRINT @createStmts 
            
            EXEC( @createStmts )
      END
END
GO

Similarly, client-side code would have to be written to insert counter samples into the database by intelligently using the correct table for each counter. Technically this isn’t very difficult, as one could put a check for a matching table into the constructor of a client-side object, which would validate that an appropriate table exists (or create one), and then use that table from that point on. I can also mock this in T-SQL with a dynamic SQL stored procedure, though I would not implement it this way on a real system:


CREATE PROCEDURE saveCounterSample (
      @SampleTime datetime,
      @ServerID int,
      @PerfObject nvarchar(30), 
      @Counter nvarchar(50), 
      @Instance nvarchar(30), 
      @Value float
) AS
BEGIN
      SET NOCOUNT ON;
      
      DECLARE @tableName varchar(128);
      SET @tableName = @PerfObject + '_' + @Counter + '_' + @Instance;

      DECLARE @checkedTableName nvarchar(128);

      SELECT @checkedTableName = TABLE_NAME 
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_SCHEMA = 'dbo' 
            and TABLE_NAME = @tableName;

      IF( @checkedTableName is not null )
      BEGIN
            DECLARE @insertcmd nvarchar(max);
            SET @insertcmd = 
                  N'INSERT INTO dbo.' + QUOTENAME( @checkedTableName ) + N' ( ' + char(10)
                  + N'  SampleTime, ServerID, Value ' + char(10)
                  + N') VALUES (' + char(10)
                  + N'  @SampleTime, @ServerID, @Value ' + char(10)
                  + N');';

            DECLARE @paramlist nvarchar(1000);
            SET @paramlist = N'@SampleTime datetime, @ServerID int, @value float';
            
            --PRINT @insertcmd;
            
            EXECUTE sp_executesql 
                  @insertcmd, 
                  @paramlist,
                  @sampleTime = @sampleTime,
                  @serverID = @ServerID,
                  @Value = @Value;
       
      END
      ELSE
      BEGIN
            RAISERROR('The required table does not exist.', 10, 1);
      END
END               
GO

Dynamic Tables Sample Data

So, in a real app the tables would be created on demand. In order to load sample data, though, it’s simpler just to initialize the database with the whole sample set of tables:


-- Setup for Sample Data

EXEC addCounterTable N'Memory', N'Pages/sec', N'Default'
EXEC addCounterTable N'PhysicalDisk', N'Avg. Disk Queue Length', N'_Total'

EXEC addCounterTable N'Processor', N'% Processor Time', N'_Total'
EXEC addCounterTable N'Processor', N'% Processor Time', N'0'
EXEC addCounterTable N'Processor', N'% Processor Time', N'1'
EXEC addCounterTable N'Processor', N'% Processor Time', N'2'
EXEC addCounterTable N'Processor', N'% Processor Time', N'3'
EXEC addCounterTable N'Processor', N'% Processor Time', N'4'
EXEC addCounterTable N'Processor', N'% Processor Time', N'5'
EXEC addCounterTable N'Processor', N'% Processor Time', N'6'
EXEC addCounterTable N'Processor', N'% Processor Time', N'7'

EXEC addCounterTable N'Process', N'% Processor Time', N'sqlservr'

EXEC addCounterTable N'LogicalDisk', N'% Disk Read Time', N'_Total'
EXEC addCounterTable N'LogicalDisk', N'% Disk Time', N'_Total'

/* ... More executions to make req’d tables ... */

EXEC addCounterTable N'MSSQL$TEST2000:Locks', N'Average Wait Time (ms)', N'_Total'

Then generate the same quantity of sample data as in the first example:


-- Populate with Sample Data

-- Create 24 hours of samples
DECLARE @now datetime;
SET @now = '2009-01-01';
DECLARE @val float;

WHILE @now < '2009-01-02'
BEGIN
      BEGIN TRANSACTION
      
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Memory', N'Pages/sec', N'Default', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'PhysicalDisk', N'Avg. Disk Queue Length', N'_Total', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'_Total', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'0', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'1', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'2', @val;
      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'Processor', N'% Processor Time', N'3', @val;

/* ... More executions for all counters ... */

      SET @val = RAND() * 10; 
      EXEC saveCounterSample @now, 1, N'MSSQL$TEST2000:Locks', N'Average Wait Time (ms)', N'_Total', @val;
      
      COMMIT
      
      SET @now = DATEADD( minute, 1, @now );
END

-- Use the first day's values and a random multiplier to mock test data for a month:
DECLARE @i int;
SET @i = 1;
WHILE @i < 28 BEGIN
      DECLARE @insertstmt nvarchar(max);
      SET @insertstmt = '';
      SELECT @insertstmt = @insertstmt +
            'insert into dbo.' + QUOTENAME( table_name ) + '( '
            + 'SampleTime, '
            + 'ServerID, '
            + 'Value '
            + ') select dateadd( day, ' + cast(@i as varchar(5) ) + ', SampleTime) as SampleTime, '
            + 'ServerID, '
            + 'Value * ( RAND() * 1.5 ) as Value '
            +'from dbo.' + QUOTENAME( table_name ) + ' '
            + 'where ''2009-01-01'' <= SampleTime and SampleTime < ''2009-01-02'';' + CHAR(10)
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME != 'Servers';
      
      -- print @insertstmt;
      
      EXEC( @insertstmt );
      
      SET @i += 1;
END
GO

-- Use the month's data and a random multiplier to generate mock data for more servers
DECLARE @i INT;
SET @i = 2;
WHILE @i <= 5 BEGIN
      DECLARE @insertstmt NVARCHAR(max);
      SET @insertstmt = '';
      SELECT @insertstmt = @insertstmt +
            'INSERT INTO dbo.' + QUOTENAME( table_name ) + '( '
            + '  SampleTime, '
            + '  ServerID, '
            + '  Value '
            + ') SELECT SampleTime, '
            +    cast( @i as varchar(5) ) + ' as ServerID, '
            + '  Value * ( RAND() * 1.5 ) as Value '
            + 'FROM dbo.' + QUOTENAME( table_name ) + ' ' 
            + 'WHERE ServerID = 1;' + CHAR(10)
      FROM INFORMATION_SCHEMA.TABLES 
      WHERE TABLE_NAME != 'Servers';
      
      -- PRINT @insertstmt;
      
      EXEC( @insertstmt );
      
      SET @i += 1;
END;

First Impressions

The first thing I note when looking at the sample data is the size discrepancy: the first model requires 5.5 GB to store the counter data, where the second fits in 1.2 GB. Most of that difference has to do with the fact that the EAV “labels” for this case are quite wide, so there’s a lot of repeated text in the EAV model.

The second thing of note is the interleave of different types of perf counters in the one large table of samples, which is what we’re going to examine for performance. This depends some on the order of the cluster key, but essentially it looks like this:

2009-01-01 00:00:00.000	1	LogicalDisk	% Disk Read Time	_Total	1.8793138675398
2009-01-01 00:00:00.000	2	LogicalDisk	% Disk Read Time	_Total	2.52991540434083
2009-01-01 00:00:00.000	3	LogicalDisk	% Disk Read Time	_Total	1.22021347428242
2009-01-01 00:00:00.000	4	LogicalDisk	% Disk Read Time	_Total	0.696864840326046
2009-01-01 00:00:00.000	5	LogicalDisk	% Disk Read Time	_Total	0.645544958044297
2009-01-01 00:00:00.000	1	LogicalDisk	% Disk Read Time	C:	2.26820505352887
2009-01-01 00:00:00.000	2	LogicalDisk	% Disk Read Time	C:	3.05343721676384
2009-01-01 00:00:00.000	3	LogicalDisk	% Disk Read Time	C:	1.47271534391143
2009-01-01 00:00:00.000	4	LogicalDisk	% Disk Read Time	C:	0.841068849517578
2009-01-01 00:00:00.000	5	LogicalDisk	% Disk Read Time	C:	0.779129213808746
< … 100's of rows … >
2009-01-01 00:01:00.000	1	LogicalDisk	% Disk Read Time	_Total	7.72396922675191
2009-01-01 00:01:00.000	2	LogicalDisk	% Disk Read Time	_Total	10.3979378149299
2009-01-01 00:01:00.000	3	LogicalDisk	% Disk Read Time	_Total	5.01507038723846
2009-01-01 00:01:00.000	4	LogicalDisk	% Disk Read Time	_Total	2.86411050056799
2009-01-01 00:01:00.000	5	LogicalDisk	% Disk Read Time	_Total	2.65318607846297
2009-01-01 00:01:00.000	1	LogicalDisk	% Disk Read Time	C:	9.49033453385117
< … &c >

Rows that I imagine one query “cares” about (green) are dispersed through the table, with a lot of other rows in between (gray).

So, tune in for the next post where I’ll have report query performance information, and a look at the buffer cache contents when reading from these two designs.