Dear Robocopy, You Suck Very Little.

Resource_semaphore_query_compile

This wait brings back nightmares from SQL Server 2005 x86 pre SP2. However, I have been troubleshooting this wait type for a customer recently so I thought I would write about it. Loosely, it means that new plans cannot be put into the procedure cache fast enough so they wait. There can be, in my experience, be two causes(post SQL Server 2005 sp2) of this:

• Plan reuse issues
• Large memory grants

Plan Reuse Issues

This is the easy one or hard one depending on your perspective. It is easy to identify but hard to fix. In an ideal world, you rewrite all of your adhoc sql as stored procedures following best practices for plan reuse. Easier said than done for large application that did not begin this way. Here is how you diagnose it.

SELECT TEXT ,cp.*
FROM sys.dm_exec_cached_plans cp
      CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cacheobjtype = 'Compiled Plan' and usecounts = 1
ORDER BY cp.size_in_bytes DESC

The key columns are the size_in_bytes and usecounts. You want low size_in_bytes and high usecounts. Rows with 1 usecount are not being reused.

Aside from rewriting the application, there are a few setting that you can use to help. In SQL Server 2005+, you can turn on forced parameterization. This will auto-parameterize a lot of queries. It sounds good but it is usually not a silver bullet. SQL Server 2008 introduces the “Optimize for ADHOC Workloads” server setting. This creates small stub plans for plans that will not be reused. This further reduces the footprint of the procedure cache.

Here are some additional resources:

Compilation and recompilation whitepaper

Parameters and Execution Plan Reuse

Queries take a longer time to finish running when the size of the TokenAndPermUserStore cache grows in SQL Server 2005 (See workaround section)

From the horse’s mouth

Large Memory Grants

This is because the more memory used for grants, the less that will be available for query compilation. The symptoms are easy to identify. A high number in the perfmon counter memory manager : memory grant outstanding is a dead giveaway. Once you see that, run this query.

SELECT TEXT, query_plan, requested_memory_kb, granted_memory_kb, used_memory_kb 
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

This will give you queries currently waiting on a memory memory grant along with the query text and their xml showplan. Typically, when I see a server in a problem state, this query returns more than 10 rows(sometimes a lot more rows) and the queries are requesting fifty to several hundred megabytes of memory each.

I usually look for a common query that is waiting many times and requesting a large amount of memory. Look at its query plan. You want to find iterators that are large memory consumers like hash joins\aggregates, sorts and merge joins with sorts.

Sometimes the resolution is just recompiling the query or updating stats.  If that doesn’t work, you can look for missing indexes. I would keep covering indexes in mind as well as filtered indexes. If the query still does a hash join(and index scan) on the filtered index, it will be cheaper. If there are no good choices for indexes, look for places to tune the query. Specifically, I have found help by moving the filters to the on clause but that might not meet requirements so be careful.

If there are cardinality overestimation problems and the iterator is actually only touching a few rows, you can try LOOP join hint. However, this should be rare or there may be parameter sniffing issues due uneven data distribution. If this is the case, simply recompiling the query should fix it most of the time. If that works, a statement level recompile, plan guides or OPTIMIZE FOR hint.

This is a tough wait state to solve. If it is not bad statistics, then you probably have some application design issues that need to be addressed in the long run. Hopefully this post will help you identify the problem and at least put some short term Band-Aids in place.

Private Clouds and the RDBMS

Private clouds are all the rage right now. The concepts in the post applies to all virtualization products and all of the major enterprise databases servers so I am going to stay vendor neutral. My experience is mostly with VMWare and SQL Server so you may have to convert to the terminology of your platform.

So what is a “Private Cloud”? Right now, it is simply a large virtual machine cluster with management software. I expect it to mature to include more value added software like content deployment, dynamic load balancing of guests and other % as a service. It is often used during a tech refresh and\or a consolidation project. You can almost take two datacenter rows of 5 year old hardware and get it into two racks of current hardware chopped into virtual machines.

Let’s say our private cloud cluster consists of 5 servers having 4 sockets & 6 core with 96GB of RAM. Each virtual machine gets 2 cores and 2-8GB of RAM. This gives us about 60 virtual machines. Some platforms allow for oversubscription but we will keep it simple.

Here is a simple logical picture.

 

As you can see that if you can replace 60 physical servers and get as good or better performance then this is a no brainer. You save on power, space, CapEx hardware costs, licensing(depending on platform), and administration.

However, you also have 10 various Database Servers. They range from mission critical to dev\QA boxes. Where do these belong? It depends.

You will always get better performance out of bare iron because there is some overhead with virtualization. Some workloads suffer more than others. For example, a CPU bound workload will suffer the most and an IO bound workload will suffer the least(assuming you can give the VM enough memory.) Another consideration is the size of a VM. A database server with two cores and 2GB of memory will see less of an impact of virtualization than an 8 core VM with 16GB of RAM. Adding cores to VM’s does not scale linearly. Having said all of that, it make sense to leverage consolidation features of the database server and use bare iron for most private cloud applications.

There are still good applications for private cloud database VM’s. Here are a few:

• Development and test environments
• Supporting legacy environments that have old RDBMS installations
• Support databases that require separation due to security or compliance
• Sandbox environment to prevent resource intensive databases from impacting mission critical databases.

Here is the racks again with the database servers.

The ability to mix physical devices for virtual machines is one of the biggest benefits of a private cloud. Most public clouds do not offer this functionality. At least not yet. Use this design to get the most out of your environment.

Note: While I am recommending physical hardware for the DB server, they do run well virtualized sized up to 4 or 8 cores. However, since we would most likely need more power than that in this scenario, bare iron would be the best.

Large Pages and SQL Server

Bob Ward posted a good article on Large Pages and SQL Server support. There are a couple of things I had to do further research on so I thought I would pass it one. First, I wanted to understand the internals. Secondly, I want to know when to use it.

The Internals

This feature is actually part of the processor architecture and is implemented on a lot of operating systems. For example, this is a common optimization when running Oracle on linux.

So here are the internals that I have translated for me to understand keeping SQL Server in mind.

Without large pages, Windows x64 has 4KB pages. This means that if your buffer pool is 16GB, you will have 4,194,304 page table entries (PTEs). The page table is used to translate mappings of virtual addresses to physical addresses. As you can imagine, there is overhead in maintaining PTEs and the overhead increases with the size of the buffer pool.

Picture Source: Wikipedia

When Large Pages is enabled for the buffer pool, windows uses 2MB pages so the same 16GB buffer pool will have 8192 PTEs. That means the overhead(CPU and memory) will be reduced. It also means that the translation lookup aside buffer is smaller and there is much less of a chance for a cache miss\page fault.

When to use it?

I am having a hard time thinking of cases when you don’t want large pages for the buffer pool if your configuration supports it and the server is dedicated for SQL Server. It is recommended by Microsoft for TPC-E and TPC-C. I know… Not all TPC settings are meant for production.

There may be times where it does not help like if you have a lot more processor time and cache than the workload needs. The slow startup side effect may also be more important than the performance gain encountered.

That said, you should definitely test before blindly applying this to production especially if you are doing it on this post. I am going to be testing and will post results. Plase let me know what your results are.

The Latest Greatest Productivity Killer

Alt Title: “Like” this Post.

I am talking about Google reader. It was already the defacto RSS reader for those that realize that the internet IS serious bidnezz. They just took it to the next level by “innovating” and “not being evil”. Otherwise known as copying features from other services like FriendFeed or Facebook. Whatever though.

The new features are following, liking, and people search. Read about the new features here.

Following

You could follow before but it was difficult. In fact, I could not even tell you how to do it. Rob Boek(WWW, Twitter) figured out how to do it but I think he had sacrifice a goat or something. Now it is as easy as following someone on twitter. One click and everything they share shows up in your reader stream. Presumably, if you follow someone, you have similar interests and you will find their shares interesting.

Likes

In addition to sharing RSS posts, you can now “like” a post. So what does “Liking” do? Well, if a lot of people like a post, you can probably assume it is worth the next 2 minutes of your life reading the post. You can also follow the people that liked a post. Again, we assume that if someone liked a post that we also like then we should find their shared posts relevant and interesting. Here is an example of following someone who liked a post.

People Search

This is simple. Search for people with similar interests or search by names and follow them.

How to get the most out of it?

• Use Google Reader, of course. If you need to get started, here is

  my OPML

  .

• Share and like what you find interesting.
• Follow those with similar interests.
• Fill out your Google Profile so people can find you.
• Don’t forget to follow me!

#SQLPASS is Getting Closer

I have signed up for the conference and booked my room at the Sheraton. I am contemplating pre\post con sessions. I would like to go to Kimberly Tripp’s (WWW, Twitter) Indexing or Itzik Ben-Gan’s (WWW) Query tuning pre\post con sessions because that is what I would find most interesting. However, I think there would be the most overlap of what I already know. I am going to wait until the Microsoft sessions are released but I am leaning towards Adam Machanic’s(WWW, Twitter) CLR pre-con.

I think CLR really shines for administrative tasks. If you are using them in your OLTP code, you are solving a really unique problem but for admin tasks you can access hardware\OS\WMI info. I have some experience with CLR but it takes me a week to write a 15 line function.

Anyway, soon it will be time to make plans for after hours events. Kimberly mentioned a pub with lots of beers from Belgium and good food on Facebook. Let me know if you know of anything going on or if you have any ideas.

As it gets closer, I predict we will have a new flood of Database Professionals hitting twitter. If this is you and you are a first timer, here are four quick tips to get the most of it during PASS.

1. Get a real client like tweetdeck or peoplebrowsr.
2. Follow everybody here and me of course.
3. Stay tuned to this search.
4. Attend Social Computing for the Database Professional by Brent Ozar(WWW, Twitter) and I.

See you at #SQLPASS 2009.

So You Want to Scale Out?

In the first post, I stated that it is better to scale up than out(my opinion) but I have seen applications try to scale out in several ways. Some worked better than others. I am going to talk about three different scenarios I have worked with in regards to scaling out.

Separating different databases

This sounds like a no brainer and an easy thing to do. However, developers tend to begin using data across databases after a few years of the same db’s being on the same server. Now you have dependencies that require linked servers or replication if you split databases across servers. Also other technologies like mirroring encourages designing everything in one(or very few) databases.

If you think this is a route that you are going to go, design it like that from the beginning if you can. Try to keep each database a complete self contained unit. If you have small, fairly static lookup tables that are shared between each database, keep a copy in each database updated through a nightly job or other method. If you have very large highly used tables that you want to split across servers, you need to look at a different method.

The key point is avoiding dependencies that will require cross database queries.

Replication Scale out

I have worked with apps that used merge replication for read\write scale out and transactional replication for read only scale out.

I have never seen an app use merge replication that was so large it could not scale up. It was mainly used to keep a live near real time copy of the database in another datacenter for a live disaster recovery site.

Sounds pretty cool, huh? The down sides include the fact that a GUID column gets added to every table. This may require app changes if you are not following best practices. Special consideration needs to be made for some schema changes. This is also expensive. You have to have an additional server, storage and licensing. That is the cheap part. You also have to have a global load balancing network device like one from F5 or Netscaler. On top of all of that, you have to manage all of this.

Disclaimer: I have yet to see an app that needed to scale beyond 24-48 cores use this method and I suspect it would be a management nightmare so test thoroughly.

The other replication scale out method use transactional replication for read only scale out.

The downsides to this includes the facts that it requires more app dev to split off the reads from the writes, the read servers still have to take all the writes, the log of the write server now has a lot of read activity and data is duplicated X number of times. On top of that, the hardware is split X number of times. As you can tell, I don’t like this one too much. I will expand on my experiences if you there are questions about it.

Horizontal Partitioning

If you want to scale out, this is the way to go. However, it will be really hard to get there  if you do not start this way from the beginning. I cringe at the thought of trying to get a 1TB+ database there after years in production.

This method has also been called sharding in recent years. The basic idea is having the entire database partitioned by a key in a single table. Let’s say the users table. We split it by UserID with 10 million users per server. You must also keep all data in every other table related to that user in that DB on that server. Depending on how you partition, you would have a lookup SQL server to tell the app where the user is or just hardcode UserID 1-10,000,000 = Server1 as an application scoped variable.

Some challenges include dynamic data in tables that do not rely on the application partitioning key. Avoid parent\child relationships where children are on multiple servers if you have to report on that in any way. Do not partition on a key like last name. Having to split the “S server” into Sa-Se and Sf-Sz servers would be a real pain. I am sure you can also imagine the increase in application complexity especially in database design.

On the plus side, you can add servers relatively easy. Data is not duplicated except small lookup tables. There is very little overhead of of the scale out itself. This scale out method also adds very little overhead to database administration as well.

That said, it is a lot easier to write a few paragraphs than to actually do this and I am still of the opinion that you should scale up unless the application is truly going to be huge.

There are other methods of scaling out SQL Server like with peer to peer replication and 3rd party products but I do not have any experience with them. I would love to hear your experiences.

48 Cores of AMD Hotness

The new HP DL785 G6’s hit the HP web site this week. They will be available mid September. 8 sockets that support the new AMD 6 core 8400 series Opterons. It can support 512GB of RAM with 64 – 8GB sticks. It is 7u and has 6 power supplies. Really, its only use is a monster Database server or big virtual machine host. Though, I would be happy to use this as a desktop.

Pricing is not out yet but I loaded up a 785 G5 with quad core procs and 512GB of RAM for $117,703 so a load G6 will have to be at least $130k. Would I buy this for the crown jewels if I had the money to tech refresh over the next year? I cannot see it happening. I would not lock myself into a platform for the next 3-5 years on an already dated DDR2 platform.

Rumor has it that HP will release a DL780 G6 next year with 8 core Nehalem Intel procs. I am sure it will be very expensive but they should also have DL 580 G6’s with nehalem procs and DDR3. An eight core 4 socket box  DDR3 box will most likely be faster that this 48 core server. Just a guess though.

However, if you have budget that you need to use it or lose it before the end of the year, this server will probably be the way to go if you need very big iron.

Capt. Varchar & the Pagelatch Posse Vol. 26

The Default Value.

DBA’s are notoriously noncommittal. If you don’t believe me, next time you see Paul Randal(WWW, Twitter) at a conference or other event, walk right up to him and ask him how long checkdb on a 500GB database will take.

Scaling up vs. Scaling out

Jeff Atwood posted about the hidden costs of scaling up vs. scaling out. It is a good a post. In a nutshell, here are his points:

• Scaling out gets you about 10 times the hardware for the same price.
• Scaling out costs roughly 8 times more in software licensing.
• Scaling out my only make financial sense using open source software(or when you can no longer scale up).

Those are good points. However, there are more downsides to scaling out.

• Administrative overhead of more servers and the replication\load balancing methodology.
• Scaling out with read only server is a misnomer. The read only server still has to take all the writes one way or another.
• App dev time. All of the scale out setups I have seen require a good amount of dev work. Some more than others.

It is my opinion that the app be should tuned first, then upgrade the hardware and then tuned some more. After that, look to offload work to the app\web tier with stuff like caching. By then, newer hardware will be available for a tech refresh. Scale out as a last resort. Sound like a pain? Try scaling out.

You see funny problems start popping up when you scale out. Take a read only scale out using transactional replication in SQL Server or Master\Slave in MySQL for example. In this scenario, you can lose read consistency. Lets say you get the product list from the read server, place your order which writes to the write server but in that time that product sold out. It is like NOLOCK but worse because the latency is longer. Sometimes much longer. 

There are diminishing returns and resource splitting with scaling out. The diminishing returns is usually due to the replication overhead while reading from the write server and writing to the read servers. On top of that, now you N times the data with the number of disks\CPU\memory divided by N. You could get around this through sharding but then you are talking major app dev time.

Still want to scale out? Ok, ok, I will talk about good not bad ways to do it in the next post.

Next Page »

• My RSS

  

• 

• My LifeStream

• My Presence

• My Friends

• My Games

  
  

• Recent Topics

  • Imagine a 3500 Page Book…
  • What is Professional Development?
  • Filtered Stats to Counter Data Skew Issues
  • Sampling Problem with Filtered Statistics
  • Life
  • Professional Development: Internet Image
  • OT: Music – What the Fork?
  • The DR Cloud: An Example
  • Can you be too Thorough?
  • Disaster Recovery in the Cloud

• My Cloud

  • automagic bad practices beta Blogging bugfix Capt. Varchar certification cloud Clustering codeplex community conferences Crybaby cxpacket Data collector Database Mirroring DBA disaster recovery DMV's DMV’s editorial Feature pack filtered indexes flamebait friendfeed fud geek geek pr0n google reader HA Hardware Heroes Happen Here humor IaaS indexes lamp lifestreaming MCITP meme memory Mirroring Mixed Workloads music mysql NTSSUG Off topic Offtopic OLTP opml Oracle pbm performance powershell procedure cache professional development profiler query optimizer query tuning RC0 RDBMS Reporting rss rumors SAN scale out vs. scale up scaling screencasts SDS Security social computing SQL Connections sql heros sql injection sql pass SQL Server 11 SQL Server 2000 SQL Server 2005 SQL Server 2008 sql server web edition SQL shorts SQLCLR sqlheros SQLPASS sqlps SSD ssms SSRS Statistics teched tsql twitter Upgrade utility computing Visual Studio 2008 VMWare Windows Windows Server 2008 WMI x64 xquery