GUIDs as PRIMARY KEYs and/or the clustering key

Expanding on the topic of “are you kidding me”… one of the MOST PREVALENT problems I see today is the dreaded “GUIDs as PKs” problem. However, just to be clear, it’s not [as much of a] problem that your PRIMARY KEY is a GUID as much as it is a problem that the PRIMARY KEY is probably your clustering key. They really are two things BUT the default behavior in SQL Server is that a PRIMARY KEY uses a UNIQUE CLUSTERED INDEX to enforce entity integrity. So, I thought I’d take this post to really dive into why this is a problem and how you can hope to minimize it.

Relational Concepts – What is a PRIMARY KEY? (quick and basic reminder for what is what and why)

Starting at the very beginning… a primary key is used to enforce entity integrity. Entity integrity is the very basic concept that every row is uniquely identifiable. This is especially important in a normalized database because you usually end up with many tables and a need to reference rows across those tables (i.e. relationships). Relational theory says that every table MUST have a primary key. SQL Server does not have this requirement. However, many features – like replication – often have a requirement on a primary key so that they can guarantee which row to modify on a related database/server (like the subscriber in a replication environment). So, most people think to create one. However, not always…

What happens when a column(s) is defined as a PRIMARY KEY – in SQL Server?

The first thing that SQL Server checks is that ALL of the columns that make up the PRIMARY KEY constraint do not all NULLs. This is a requirement of a PRIMARY KEY but not a requirement of a UNIQUE KEY. They also check to make sure (if the table has data) that the existing data meets the uniqueness requirement. If there are any duplicate rows, the addition of the constraint will fail. And, to check this as well as to enforce this for [future] new rows – SQL Server builds a UNIQUE index. More specifically, if you don’t specify index type when adding the constraint, SQL Server makes the index a UNIQUE CLUSTERED index. So, why is that interesting…

What is a clustered index?

In SQL Server 7.0 and higher the internal dependencies on the clustering key CHANGED. (Yes, it’s important to know that things CHANGED in 7.0… why? Because there are still some folks out there that don’t realize how RADICAL of a change occurred in the internals (wrt to the clustering key) in SQL Server 7.0). It’s always (in all releases of SQL Server) been true that the clustered index defines the order of the data in the table itself (yes, the data of the table becomes the leaf level of the clustered index) and, it’s always been a [potential] source of fragmentation. That’s really not new. Although it does seem like it’s more of a hot topic in recent releases but that may solely because there are more and more databases out there in general AND they’ve gotten bigger and bigger… and you feel the effects of fragmentation more when databases get really large.

What changed is that the clustering key gets used as the “lookup” value from the nonclustered indexes. Prior to SQL Server 7.0, SQL Server used a volatile RID structure. This was problematic because as records moved, ALL of the nonclustered indexes would need to get updated. Imagine a page that “splits” where half of the records are relocated to a new page. If that page has 20 rows then 10 rows have new RIDs – that means that 10 rows in EACH (and ALL) of your nonclustered indexes would need to get updated. The more nonclustered indexes you had, the worse it got (this is also where the idea that nonclustered indexes are TERRIBLY expensive comes from). In 7.0, the negative affects of record relocation were addressed in BOTH clustered tables and heaps. In heaps they chose to use forwarding pointers. The idea is that the row’s FIXED RID is defined at insert and even if the data for the row has to relocate because the row no longer fits on the original page – the rows RID does not change. Instead, SQL Server just uses a forwarding pointer to make one extra hop (never more) to get to the data. In a clustered table, SQL Server uses the clustering key to lookup the data. As a result, this puts some strain on the clustering key that was never there before. It should be narrow (otherwise it can make the nonclustered indexes UNNECESSARILY wide). The clustering key should be UNIQUE (otherwise the nonclustered indexes wouldn’t know “which” row to lookup – and, if the clustering key is not defined as unique then SQL Server will internally add a 4-byte uniquifier to each duplicate key value… this wastes time and space – both in the base table AND the nonclustered indexes). And, the clustering key should be STATIC (otherwise it will be costly to update because the clustering key is duplicated in ALL nonclustered indexes).

In summary, the clustering key really has all of these purposes:

  1. It defines the lookup value used by the nonclustered indexes (should be unique, narrow and static)
  2. It defines the table’s order (physically at creation and logically maintained through a linked list after that) – so we need to be careful of fragmentation
  3. It can be used to answer a query (either as a table scan – or, if the query wants a subset of data (a range query) and the clustering key supports that range, then yes, the clustering key can be used to reduce the cost of the scan (it can seek with a partial scan)

However, the first two are the two that I think about the most when I choose a clustering key. The third is just one that I *might* be able to leverage if my clustering key also happens to be good for that. So, some examples of GOOD clustering keys are:

  • An identity column
  • A composite key of date and identity – in that order (date, identity)
  • A pseudo sequential GUID (using the NEWSEQUENTIALID() function in SQL Server OR a “homegrown” function that builds sequential GUIDs – like Gert’s “built originally to use in SQL 2000” xp_GUID here:http://sqldev.net/xp/xpguid.htm

But, a GUID that is not sequential – like one that has it’s values generated in the client (using .NET) OR generated by the newid() function (in SQL Server) can be a horribly bad choiceprimarily because of the fragmentation that it creates in the base table but also because of its size. It’s unnecessarily wide (it’s 4 times wider than an int-based identity – which can give you 2 billion (really, 4 billion) unique rows). And, if you need more than 2 billion you can always go with a bigint (8-byte int) and get 263-1 rows. And, if you don’t really think that 12 bytes wider (or 8 bytes wider) is a big deal – estimate how much this costs on a bigger table and one with a few indexes…

  • Base Table with 1,000,000 rows (3.8MB vs. 15.26MB)
  • 6 nonclustered indexes (22.89MB vs. 91.55MB)

So, we’re looking at 25MB vs 106MB – and, just to be clear, this is JUST for 1 million rows and this is really JUST overhead. If you create an even wider clustering key (something horrible like LastName, FirstName, MiddlieInitial – which let’s say is 64bytes then you’re looking at 427.25MB *just* in overhead….. And, then think about how bad that gets with 10 million rows and 6 nonclustered indexes – yes, you’d be wasting over 4GB with a key like that.

And, fragmentation costs you even more in wasted space and time because

of splitting. Paul’s covered A LOT about fragmentation on his blog so I’ll skip that discussion for now BUT if your clustering key is prone to fragmentation then you NEED a solid maintenance plan – and this has it’s own costs (and potential for downtime).

So…………… choosing a GOOD clustering key EARLY is very important!

Otherwise, the problems can start piling up!

 

via: http://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

Fundamentos de SQL: Transacciones

Antes de nada una definición:

Una transacción es una unidad de trabajo compuesta por diversas tareas, cuyo resultado final debe ser quese ejecuten todas o ninguna de ellas.

SQL-Transacciones

Por regla general en un sistema de base de datos todas las operaciones relacionadas entre sí que se ejecuten dentro un mismo flujo lógico de trabajo, deben ejecutarse en bloque. De esta manera si todas funcionan la operación conjunta de bloque tiene éxito, pero si falla cualquiera de ellas, deberán retrocederse todas las anteriores que ya se hayan realizado. De esta forma evitamos que el sistema de datos quede en un estado incongruente.

BONUS: Consigue tu ebook recopilatorio GRATIS >> Héroe en SQL: manual de iniciación

Por ejemplo, si vamos al banco y ordenamos una transferencia para pagar una compra que hemos realizado por Internet, el proceso en sí está formado por una conjuto (o bloque) de operaciones que deben ser realizadas para que la operación global tenga éxito:

  1. Comprobar que nuestra cuenta existe es válida y está operativa.
  2. Comprobar si hay saldo en nuestra cuenta.
  3. Comprobar los datos de la cuenta del vendedor (que existe, que tiene posibilidad de recibir dinero, etc…).
  4. Retirar el dinero de nuestra cuenta
  5. Ingresar el dinero en la cuenta del vendedor.

Dentro de este proceso hay cinco operaciones, las cuales deben tener éxito o fallar conjuntamente.

En el caso de las operaciones 4 y 5 que modifican datos es algo obvio que no puede funcionar una y fallar la otra. Si se retira el dinero de nuestra cuenta en el paso 4 y hay algún problema que evita que pueda continuar el proceso, el dinero habrá salido de nuestra cuenta pero no se ha anotado en la cuenta de destino porque se ha producido un error. De repente hay un dinero que ha desaparecido y la base de datos se encuentra en un estado inconsistente. Es evidente que esto no puede ocurrir.

Precisamente para evitar este tipo de situaciones existen las transacciones: marcan bloques completos de operaciones y comprueban que, o se realizan todas, o que si hay algún problema se deshacen todas.

En nuestro ejemplo de transferencia fallida, al producirse un error en el paso 5 se habría deshecho automáticamente la operación de retirada de dinero del paso 4 y toda la información habría quedado como antes de comenzar el proceso.

Otro tema importante relacionado con las transacciones es la gestión de la concurrencia y los bloqueos. En el ejemplo del banco los 3 primeros pasos realmente no implican modificación alguna de datos, por lo que si fallan no dejan la base de datos en un estado inconsistente ¿o quizá sí?. ¿Qué pasaría si al mismo tiempo que se está realizando nuestra transferencia, entra en nuestra cuenta un cargo diferido que teníamos pendiente? Sería posible que de repente nos quedásemos sin saldo para realizar la operación actual o, peor aún, que se anotasen mal ambos cargos en cuenta de modo que se “pisasen” dejando un saldo inconsistente. O puede que entre los pasos 3 y 5 la cuenta del vendedor de repente se haya cancelado, justo en medio de la operación. El dinero iría a parar a una cuenta no válida.

Para controlar el comportamiento de las transacciones en estos casos se definen diferentes niveles de aislamiento de una transacción.

Otro ejemplo más común: la creación de un pedido. En este proceso se debe consultar antes la tabla de productos y ver si hay stock, se inserta un registro en la tabla de pedidos (quién hace el pedido, sus datos, fecha, importe total..), uno o varios registros en la tabla de detalles del pedido (qué productos se incluyen y su cantidad),  y se actualiza la tabla de stock de productos. Si además se genera al mismo tiempo la factura, el proceso continua involucrando a varias tablas más (cabeceras de factura, líneas de factura). En este caso también es muy importante que se lleve la operación a cabo por completo o que se deshaga por completo, ya que sino nos encontraríamos con datos incoherentes.

Existen infinidad de posibilidades de que algo salga mal  en cualquier proceso que involucre varias operaciones, y las posibilidades de fallo se multiplican a medida que aumenta la simultaneidad de acceso a la misma información. Por eso los sistemas de datos grandes son muy complejos.

Para todas estas situaciones nos ayudan las transacciones.

Propiedades ACID

Una transacción, para cumplir con su propósito y protegernos de todos los problemas que hemos visto, debe presentar las siguientes características:

  • Atomicidad: las operaciones que componen una transacción deben considerarse como una sola.
  • Consistencia: una operación nunca deberá dejar datos inconsistentes.
  • Aislamiento: los datos “sucios” deben estar aislados, y evitar que los usuarios utilicen información que aún no está confirmada o validada. (por ejemplo: ¿sigue siendo válido el saldo mientras realizo la operación?)
  • Durabilidad: una vez completada la transacción los datos actualizados ya serán permanentes y confirmados.

A estas propiedades se las suele conocer como propiedades ACID (de sus siglas en inglés: Atomicity, Consistency,Isolation y Durability).

Cómo definir transacciones

Por regla general en los gestores de datos relacionales modernos disponemos de tres tipos de transacciones según la forma de iniciarlas:

  • De confirmación automática: el gestor de datos inicia una transacción automáticamente por cada operación que actualice datos. De este modo mantiene siempre la consistencia de la base de datos, aunque puede generar bloqueos.
  • Implícitas: cuando el gestor de datos comienza una transacción automáticamente cada vez que se produce una actualización de datos, pero el que dicha transacción se confirme o se deshaga, lo debe indicar el programador.
  • Explícitas: son las que iniciamos nosotros “a mano” mediante instrucciones SQ. somos nosotros, los programadores, los que indicamos qué operacio0nes va a abarcar.

Una transacción explícita se define de manera general con una instrucción que marca su inicio, y dos posibles instrucciones que marcan su final en función de si debe tener éxito o debe fracasar en bloque.

Cada sistema gestor de bases de datos tiene sus pequeñas particularidades, pero podemos escribir con un pseudo-código, la sintaxis de una transacción genérica:

BEGIN TRAN
      Operación 1...
      Si fallo: ROLLBACK TRAN 
       Operación 2....
       Si fallo: ROLLBACK TRAN
...

      Operación N....
      Si fallo: ROLLBACK TRAN
COMMIT TRAN

Es decir, se define el comienzo de una transacción, se comprueban posibles errores en cada paso, echando atrás todo el proceso (se le suele llamar “hacer un Rollback”), o confirmando el conjunto de operaciones completas al final del todo si no ha habido problemas (en la jerga habitual se suele hablar de “hacer un Commit”).

De hecho no suele ser necesario comprobar los errores por el camino ya que por regla general el gestor de datos si detecta un error en cualquiera de los pasos dentro de una transacción, realizará un rollback automático de toda la operación.

Transacciones en SQL Server

En SQL Server las instrucciones equivalentes a las genéricas que acabamos de ver son:

  • BEGIN TRANSACTION o BEGIN TRAN: marca el inicio de una transacción. TRAN es un sinónimo de TRANSACTION y se suele usar más a menudo por abreviar.
  • ROLLBACK TRANSATION o ROLLBACK TRAN: fuerza que se deshaga la transacción en caso de haber un problema o querer abandonarla. Cierra la transacción.
  • COMMIT TRANSACTION O COMMIT TRAN: confirma el conjunto de operaciones convirtiendo los datos en definitivos. Marca el éxito de la operación de bloque y cierra la transacción.

Los niveles de aislamiento que nos ofrece SQL Server son:

  • SERIALIZABLE: No se permitirá a otras transacciones la inserción, actualización o borrado de datos utilizados por nuestra transacción. Los bloquea mientras dura la misma.
  • REPEATABLE READ: Garantiza que los datos leídos no podrán ser cambiados por otras transacciones, durante esa transacción.
  • READ COMMITED: Una transacción no podrá ver los cambios de otras conexiones hasta que no hayan sido confirmados o descartados.
  • READ UNCOMMITTED: No afectan los bloqueos producidos por otras conexiones a la lectura de datos.
  • SNAPSHOT: Los datos seleccionados en la transacción se verán tal y como estaban al comienzo de la transacción, y no se tendrán en cuenta las actualizaciones que hayan sufrido por la ejecución de otras transacciones simultáneas.

La instrucción SET TRANSACTION ISOLATION LEVEL nos permite cambiar el nivel de aislamiento.

Puedes leer la documentación oficial sobre transacciones de SQL Server.

Transacciones en MySQL

Hay que tener en cuenta que MySQL es un gestor de datos relacional que soporta diversos motores de almacenamiento por debajo (al menos 20 la última vez que los contamos). De todos esos solamente unos pocos soportan transacciones. Los dos más comunes son el tradicional MyISAM y el más moderno InnoDB. De estos dossolamente InnoDB soporta el uso de transacciones (con razón myISAM es tan rápido: da mucha velocidad a costa de no ofrecer consistencia en las operaciones, lo cual puede ser muy útil para ciertos tipos de aplicaciones).

En MySQL InnoDB las instrucciones equivalentes a las genéricas son las siguientes:

  • START TRANSACTION o BEGIN: marca el inicio de una transacción. Se suele usar más a menudo BEGIN porque es más corto.
  • ROLLBACK: fuerza que se deshaga la transacción en caso de haber un problema o querer abandonarla. Cierra la transacción.
  • COMMIT: confirma el conjunto de operaciones convirtiendo los datos en definitivos. Marca el éxito de la operación de bloque y cierra la transacción.

En cuanto a los niveles de aislamiento que nos ofrece MySQL son los siguientes:

  • SERIALIZABLE
  • REPEATABLE READ
  • READ COMMITED
  • READ UNCOMMITTED

La instrucción que nos permite cambiar el nivel de aislamiento es también SET TRANSACTION ISOLATION LEVEL .

Puedes leer la documentación oficial sobre transacciones de MySQL. Hay unas cuantas particularidades así que conviene leerlo detenidamente.

Transacciones en Oracle

En Oracle las instrucciones equivalentes a las genéricas son las siguientes:

  • START TRANSACTION o BEGIN: marca el inicio de una transacción. Se suele usar más a menudo BEGIN porque es más corto.
  • ROLLBACK: fuerza que se deshaga la transacción en caso de haber un problema o querer abandonarla. Cierra la transacción.
  • COMMIT: confirma el conjunto de operaciones convirtiendo los datos en definitivos. Marca el éxito de la operación de bloque y cierra la transacción.

En cuanto a los niveles de aislamiento que nos ofrece Oracle son idénticos a los anteriores:

  • SERIALIZABLE
  • REPEATABLE READ
  • READ COMMITED
  • READ UNCOMMITTED

La instrucción que nos permite cambiar el nivel de aislamiento es también SET TRANSACTION ISOLATION LEVEL. Es interesante leer al respecto este artículo de su documentación oficial sobre concurrencia de datos y consistencia.

Puedes leer la documentación oficial sobre transacciones de Oracle.

Con este artículo terminamos con la serie dedicada a los fundamentos del lenguaje SQL y el manejo de información en sistemas gestores de datos relacionales.

via: http://www.campusmvp.es/recursos/post/fundamentos-de-sql-transacciones.aspx

[Solved] High Memory Usage by Metafile on Windows 2008 R2

Dear All,

Have you seen such type of task manager parameters in any of your server? if please read the below scenario

You can see the memory utilized is 98% and when I went to processes tab I saw as below

But when I tried to sum all processes memory it was not going beyond 50 % so where the memory is getting utilized?

Installed RAMMap.exe of sysinternals and checked

After reading lot on various blogs I came to know metafile is

” Metafile is part of the system cache and consists of NTFS metadata. NTFS metadata includes the MFT as well as the other various NTFS metadata files… In the MFT each file attribute record takes 1KB and each file has at least one attribute record. Add to this the other NTFS metadata files and you can see why the Metafile category can grow quite large on servers with lots of files ”

checked for file type wise disk space utilization and 98 % of E: contains around 228 GB of JPG files

So searched further for ” how to clean / reduce METAfIle size ” and most of blogs where suggesting to install Dynamic Cache Service so tried that also but the service doesn’t work with windows 2008 r2 Web edition.

Now what to do ?

After lots of efforts ….. got the solution

As I have clicked on “Empty System Working Set” the memory utilization started reducing from 98% to………. 48 % J

Thanks

Prashant Deshpande

via: https://prashantd.wordpress.com/2013/11/14/solved-high-memory-usage-by-metafile-on-windows-2008-r2/