4

Inherited control over a database that has this nasty config. It also has large sections of code generated by NHibernate, including the gneeration of GUIDs before they get to the db so no chance of using NEWSEQUENTIALID() either.

Obviously changing some of these sounds like a huge piece of work, but changing the Lookups doesn't sound too painful. I've documented the fragmentation and CL width and NC indexes built from these, optimizer choosing incorrect plans, etc.. but tasked with a workaround i'm coming up a bit short.

My current plan is to investigate how much is possible to change and in the interim add an identity field to the main heavily indexed tables and make that the clustered index and keep the GUIDs as PK's. Is this the best I can hope for? or not even worth it? any other workarounds that i have omitted ?

Thanks

  • 1
    Do you have control over the app code that uses the database (whether it's in stored procedures or in the app)? If it's outside of your reach you will need some cooperation from your developers. I'd start there. – Matan Yungman Jan 9 '14 at 19:15
  • 2
    How do you know the GUID are causing you the problem? What are the major pain points of the database? – Thomas Kejser Jan 9 '14 at 23:11
  • @ThomasKejser Hi Thomas, I'm afraid currently a number issues, but limited memory so the tables and indexes being much wider than they should is an issue currently. MI queries run on the db so joining via 16 byte columns is creating very wide queries. GUIDS for even small tables such a country LU. Bad plans due to tables being hugely fragmented after an hour or so. – DamagedGoods Jan 10 '14 at 10:15
  • 1
    Damaged: fragmentation doesn't create bad plans - bad stats do. On small tables - you are not really going to gain much by making a row smaller - so probably not worth it either. What is you major wait types? – Thomas Kejser Jan 10 '14 at 11:43
  • 1
    Who gives a damn about a table that contain a the counties of the world. Even if you decided to. Ale th key CHAR(900) - if really doesn't matter. There are about 200 of those. Any optimisation you do here is not really a factor I the big picture. – Thomas Kejser Jan 11 '14 at 1:38
3

(Moving discussion to answer)

First of all, you have to ask yourself: Why go after GUID first (if at all)? You could end up spending a lot of time rearranging tables and gain no benefit at all.

What do we know:

  • You state that the queries are generated by NHibernate. This indicates that you are an OLTP system.
  • Your major waits are CXPACKET and PAGEIOLATCH
  • Query plans are not doing what you want them to do

None of the above indicate that changing away from GUID is the best thing to do here. Here is why:

First, while moving away from GUID might reduce fragmentation - this has no effect on your query plans (Statistics has). As a matter of fact, it may even make things worse since you will now have to deal with the ascending key problem described here: http://blog.kejser.org/2011/07/01/the-ascending-key-problem-in-fact-tables-part-one-pain/ which will cause your parallel queries to run single threaded.

Second, the presence of CXPACKET as a high wait indicates that you are running a lot of parallel queries. This indicates that you are getting wrong estimates or are lacking indexes - GUID's wont help with that either (and lowering DOP is not the answer here)

Third: Even if you did manage to switch to GUID and using your 20M row example, how much would you actually save? Every row would be 12B bigger (unless you use a heap of course, but that has to be understood well first). There are 20M row them - this is 240MB of RAM (assuming of course, that the pages are full, which they only will be on the keys, not the secondary keys). 10 times that is just over 2GB, which today costs less than one hour of quality DBA consulting. In other words: trying to squeeze more space out of the pages is throwing money and your precious time out the window.

In your situation, it sounds like you need to collect some data from sys.dm_exec_query_stats and try to find out where the poor performance is really coming from. Since this is NHibernate, I would look out for:

  • Poorly parameterised queries (can be fixed with simple hints or better stats)
  • Over indexing or lacking good covering indexes
  • Lacking join conditions
  • Optimiser chosing hash join when loop would be better (you can use OPTION (LOOP JOIN) temporarily to see if a good loop join plan is even possible)

The best way to remove PAGEIOLATCH is not to squeeze more into memory (unless the database is very small or you can just buy cheap RAM) - it is to avoid doing bad table scans in the first place.

  • I did mention in the other comments in answer to your question that I have literally just inherited and would be tackling the CXPackets & other waits separately.. The indexing is fairly complete but far from perfect because at some point, application performance was slow and someone developed some denormalised tables to meet complex queries, then other developers have coded using these tables and an indexing strategy for these on an OLTP system is hard to find. Can you squeeze an hour of consulting into a server with no more capacity for RAM :) – DamagedGoods Jan 10 '14 at 22:30
  • You did say you wanted to tackle parallelism. But you also said that you were thinking of changing the maxdop setting in the server. This indicates that you are not really going to tackle it (changing maxdop is not they way to tackle parallelism). If you are already at max DRAM - do you actually know that you are using that DRAM for? I find it highly unlikely that the majority of that is GUIDs – Thomas Kejser Jan 11 '14 at 1:36
  • And if you are already at max DRAM (in other words: 1TB on a 2 socket on latest HW) - You must have enough budget to remove the I/O bottleneck too. I today's world - if you can't drive your CPU to 100% (in other words: remove PAGEIOLATCH) you are doing something wrong in the DB. Once you fixed that - let's see if you still want an IDENTITY column – Thomas Kejser Jan 11 '14 at 1:59
  • I've added a blog entry about this: blog.kejser.org/2014/01/12/clustered-indexes-vs-heaps – Thomas Kejser Jan 12 '14 at 21:19
  • no I said I was looking at the cost threshold for Parallelism not Maxdop. I'm certainly not on the latest dedicated hardware.. its contending for resources with Other VM's and resources for SAN. So i'm constrained in several ways.. I dont think most people are dealing with the kind of hardware and scale that would mean IDENTITY would not scale Thomas. – DamagedGoods Jan 12 '14 at 23:33
2

I have had great success with overall perfomance gains by moving the clustered index to an identity, and leaving the GUID as the PK.

If you have a GUID as a clustered PK, you are telling SQL to physically sort the data randomly, leading to page splits and fragmentation in the main table as well as in the non-clustered indexes.

By getting your database on solid footing, you can than take a look at the problematic queries and start cherry picking. I use SQL profiler and capture the longest running transactions, or the ones causing the greatest amount of I/O and start from there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.