|
|
Posted by Erland Sommarskog on 06/27/07 21:27
Richard (nassegris@gmail.com) writes:
> One big problem (as i see it, and I'm by no means a SQL expert) is
> that the db in question uses uniqueidentifier primary keys with
> clustered indexes on those almost EVERYWHERE, and there is nothing I
> can do to change that at the moment...Constructs like
Clustered indexes on GUIDs requires a lot of skill in monitoring
fragmentation.
With the standard setup with a high fill factor, clustering on GUIDs is bad,
because you get page splits and fragmentation galore.
SQL Server MVP Greg Linwood suggested to me that clustering on GUIDs may
still be good for INSERT performance, if you create the indexes with a low
fill factor, say 50%. Now when you add new rows, there are good chance
that there is a hole to plug into. When the table starts to fill up, you
need to reindex again. But this strategy requires careful planning, and is
nothing for the armchair DBA.
If you are stuck with these clustered indexes, make you sure you set up
a reindexing job that runs regularly, and you should probably aim at
a lower fill factor. If not 50%, maybe 75-80%. It depends a bit how
big the INSERT frequency is. And use DBCC SHOWCONTIG to monitor
fragmentation.
> FROM z
> INNER JOIN a ON ..GUID = ..GUID
> INNER JOIN b ON ..GUID = ..GUID
> INNER JOIN c ON ..GUID = ..GUID
> INNER JOIN d ON ..GUID = ..GUID
> LEFT OUTER JOIN eON ..GUID = ..GUID
> AND VERSION = (
> SELECT MAX(VERSION) FROM f WHERE ..GUID = ..GUID)
>
> make the queries run painfuly slow.
>
> So the question is, is there ANYTHING I can do to optimize this type
> of queries or is a redesign the only thing that would help?
With the information you have posted, it's impossible to tell. But
I would at least give defragmentation with DBCC DBREINDEX a chance
first if DBCC SHOWCONTIG show horrendeous numbers.
You could also consider adding covering non-clustered index on tables
where only a few columns of many are involved in the query.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|