Archive for March, 2014

SQL Server Locks – SP – 24/31 de Maio.

Galera, bom dia.

É com um imenso prazer que venho anunciar um treinamento específico sobre locks no SQL Server. A ideia de criá-lo veio enquanto analisava os materiais e conteúdos que existem por ai. Tem bastante coisa, porém, ainda não vim nenhum especifico de lock e isso é muito comum no dia-a-dia do DBA / Desenvolvedor.

Segue mais detalhes:

Agenda –

Parte I – Fundamentals About Locks, Transactions and Concurrency

  • Concurrency and Transactions
  • Properties
  • Scope
  • Isolation
  • Pessimistic Vs Optimistic Lost Update Problem
  • Lock Basic Concept
  • Lock Overview
  • Lock Resources
  • Lock Duration
  • Lock Ownership
  • Lock Metadata

Parte II – Advanced Lock

  • Lock Compatibility
  • Lock Conversion
  • Special Intent Locks
  • SIX, UIX, SIU,Key RangeS-S, RangeS-U, RangeX-X
  • Lock Scalation
  • Controlling Concorrency
  • Lock Timeout
  • Lock hints

Parte III – Advanced Troubleshooting

  • Troubleshooting Locking
  • Troubleshooting Blocking
  • Troubleshooting DeadLocking
  • Optimistic Concurrency
  • Row Versionning
  • Snapshot Isolations Level
  • RCSI
  • SI
  • Version Store

Parte IV –Examples

  • Examples of all earlier issues spoken.

Obs: A data pode ser reagendada no caso do quorum minimo não ter atingido o esperado.

Muito Obrigado.

How much do you know about disks – Part I.

Hello everyone, today I will speak about best practices and the better way When choose your disks. In most places not always is a rule to know, but, how this can help you in our day-by-day? Let’s go.

There are many factors to consider an appropriate disks. Consider the follows aspects:

Seek Time and Rotation Latency
Types of disks drive
Storage array type
RAID types.

First all, unfortunately disk capacity has improved greatly, but their speed has not been increased on the same proportion such as CPU and Memory resource. It is a serious problem on the busy OLTP and most large OLAP systems. A possible solution would have to use the compress, but, there is no free lunch. Which can increase your CPU time and if your CPU is a problem will be “crap”. Always have a baseline before any modification in your environment or at least a good justification for change.

In the OLTP we have a characteristic of short transactions and random access to disks. At the point view a main point the must considered the seek time.

Seek time: Is the time it takes the head to physically move across the disk to find the data. This will be a limiting factor in the number of I/O operations a single disk may perform per second (IOPS) that your system can support.

On the other side, in most OLAP systems we have different characteristic that is: longer-running queries and sequential IO. There are much static data and DSS/DW.

Rotation Latency: Is the time it takes for the disk to spin to read the data off the disk. This is a limiting factor in the amount of data a single disk can read per second (usually measured in MB/s), in other words the I/O throughput of that disk.

References: SQL Server Hardware by Glenn Berry

Types of disks drive
Database server have traditionally used magnetic hard drive storage. The component to the magnetic hard disks have been not improved since some years ago and probably this won’t happen in the future. The current modern hard drives have until 15.000 rpm it could be very little in some cases, but, this limitation has been increasing the utilization SANs. However, SANs have become expensive and sometimes complex depending of the how many disk you put there. These SANs are generally shared among many databases and thus become more complex to administer. We will speak more about SAN later in this article.

Solid-state-drives (SSD) are different from traditional magnetic drives, because they have no spindle and have no rotation latency. Therefore these disks can offer better performance than magnetic drives. This performance does come at a much higher cost per gigabyte. When you find this situation think if is possible add SSDs disks on the volatile data and disk slower for historical data.  

Another option for flash memory could be a Fusion-IO cards. They are connected to your server through a PCI-E slot. Instead of a SAS or SATA controller. These cards are excellent for read and write performance ensuring extremely good I/O performance without the need for a SAN. The Fusion-IO cards can be used with a new feature of SQL Server 2012, local tempdb in AlwaysOn Solution.

On the next week We will finish this article.

Ta-Ta for Now

Categories: Administração