Entries for the 'consolidation' Category

Buffer Pool Usage by Database

Here is a quick query I wrote today. It is the first time I had to go to this DMV so I thought I would share. It would be useful when planning for consolidation and troubleshooting a bunch of apps that have been consolidated or are hosted in a shared environment. Once you find the database, you can break it down by object and index with Tom Davidson's query.

select db_name(database_id) as dbName, count(*)*8/1024 as BufferPoolMB

from sys.dm_os_buffer_descriptors

group by db_name(database_id)

order by 2 desc

email it! |   |   | 

Permalink     1 Comments

Reporting on your OLTP system

Just a quick note on this topic since it really is not a best practice and I am sure no one does it. ;)

Business requirements > best practices?
Even if you have a reporting server dedicated for ...err... reports, the business might dictate real time data in those reports. That may or may no be possible on a reporting server but that is another post. Most of the time, the reporting server is not real time. At the same time, the OLTP data is not optimized reporting and if it is, transactional performance decreases. The solution usually dictates some reporting functionality being added to your production OLTP server. Maybe not at a DSS level but an adhoc query built by a web page. A company's order history, an store inventory, or even an advanced search web page. You know the one:

select * from widgets

where color is null and size is null and flavor is null........ and (title like '%ice cream%' or description like '%ice cream%' or keywords like '%ice cream%')

Is that really a reporting database?

So you have a copy of your OLTP database either through mirroring\snapshots, logshipping or replication that you call a reporting database. What does that buy you? Well, we you are offloading reporting right. Kinda. You separate your reporting queries and your OLTP queries but here are some downsides:

• The reporting database is not optimized for reporting. This tends to be really apparent if reporting is critical.
• The OLTP database now has read activity on the log(except for logshipping)
• The reporting database still takes all of the write traffic in one way or another.
• The OLTP database may lose resources. For example, less disks get dedicated to it. Or worse, reporting and OLTP end up on the same physical SAN disk basically doubling the IO.

So what is the solution? 

The real solution is to design an incremental ETL process that loads to a report optimized database. If a closer to real time data is required, triggers, modified replication or possibly asynchronous triggers might be the way to go. I am not going to pretend to know how to architect a solution like that. However, it is safe to say that a nightly or real time solution will require some serious dev work. This includes creating the ETL process and rewriting the application to use the report optimized database.

Paint yourself into a corner?

Say you have a home grown database that has gone from megabytes to 100's of gigabytes. Separating reporting functions to a new database is going to take time. However, SQL Server 2008 provides new features that scream consolidation. Not only instance consolidation but functionality consolidation.

• Resource Governor is the backbone of a consolidation strategy.
• Filtered indexes - Index the different workloads while impacting writes as little as possible.
• Data Compression -  combine with partitioning and shrink the reporting data while leaving the really hot data uncompressed.
• Not just SQL 2008 but don't forget about indexed views and indexed persisted computed columns.

The other assumption of consolidation is larger hardware. If you are not spending money on a copy of OLTP reporting server, you can get a larger OLTP box.

Warning: this is forward looking since I don't have any production SQL Server 2008 servers yet. :)

Conclusion

Not to sound like a Microsoft fan boy but SQL Server 2008 Enterprise Edition provides a lot of benefits for mixed workload boxes. However, the real point of the post is that making a copy of the OLTP database for reporting may not provide gains one would expect.

email it! |   |   | 

Permalink     1 Comments

RE: Dead DBA's

I have half written post called "The Rebirth of the DBA". I am not sure if it is going to make the cut. There is not enough time before I go on vacation and there is some good community discussion both in the comments and in the blogosphere(Brent O, Grant Fritchy, Pythian).

I will summarize really quick. I was halfway playing devil's advocate. While it is possible that they could write some really amazing software that could script the DBA, it is unlikely that they can totally do it. The DBA will still be around. The numbers may be less. Maybe substantially. The roles may change. Maybe drastically. Hell, your SQL Server 2015 VM may have a "cloud partition" in a physical table. There are other factors in the mix like consolidation, virtualization and LINQ\ORM that are also going to have to have an effect. It is all speculation but the only constant is change.

email it! |   |   | 

Permalink     5 Comments

Return of the 8 way

It seems like just yesterday Compaq Proliant 8500’s were the bomb with 8 – 550 mhz processors. 4.4 ghz of processing madness! That was even before hyperthreading and multi-cores. Since then, the big 3 server hardware vendors eliminated 8 way machines in their commodity server lines. You still had 8 way options but there were more cost effective configurations because multi-core processors removed the need for 8 way boxes in most cases.

That is until now as HP releases the dl785 g5. Eight sockets capable of running quad-core 2.3GHz AMD Opterons. That is a combined speed of 73.6GHz. They comes with 8GB of RAM but they support 256GB of RAM(512GB when 8GB dimm's become available). The servers, themselves, are going to be relatively cheap compared to the high end SAN's and large amount of memory needed to get the throughput high enough tax the processor sub system. Without a large spindle count\cache and amount of RAM, the system will have an IO bottleneck long before the processors in most cases. Of course, some applications have special needs. :)  

So when will these come into play? I think the biggest use of this box will be for consolidation particularly on SQL Server 2008. Imagine taking 20 or 40 instances on different OS’s, hardware, storage etc and making it one(or even 2 or 3) SQL Server 2008 instance. The environment would be so much easier to manage. The SQL Server 2008 resource governor was made for consolidation. Some of the new features in SQL Server 2008 are going to be CPU hungry like the spatial data, partition parallelism improvements and transparent data encryption.  The data and backup compression features push both ways by lowering IO and increasing CPU with the idea of decreasing execution time. Even if you go to SQL 2005, it would be a nice upgrade for a consolidation box.

Of course, you have to worry about having all of your eggs in one basket but that is another post.

Introducing the dl785 g5:

email it! |   |   | 

Permalink     2 Comments