'전체'에 해당되는 글 24건

  1. 2011/05/16 글로벌.. (2)
  2. 2011/05/06 지친다.
  3. 2011/05/03 DB 이야기
  4. 2011/04/29 SQL2008 데이터형 우선순위
  5. 2011/04/29 SQL MD5
  6. 2011/04/28 DB 패치
  7. 2011/04/26 Microsoft iSCSI Software Target 3.3
  8. 2011/04/25 NUMA, Hyperthreading and NUMA.PreferHT
  9. 2011/04/22 실행계획 보기
  10. 2011/04/15 ODBC Connection Failed: SQLState: '08001' DBMSSOCN
분류없음2011/05/16 22:13
서버는 유럽에..

오피스는 한국에..

메일 서버는 미국에..

이 얼마나 글로벌한 기업인가...



저작자 표시
Posted by yangdb
잡담2011/05/06 14:27
이놈의 회사는 단 하루도 문제가 없는 날이 없다.
지친다 이제.. 젠장
저작자 표시

'잡담' 카테고리의 다른 글

지친다.  (0) 2011/05/06
DB 패치  (0) 2011/04/28
Microsoft iSCSI Software Target 3.3  (0) 2011/04/26
내가 다가가지 않으면...  (0) 2011/04/15
여직원..  (0) 2011/03/30
사라브라이트만  (0) 2010/10/29
Posted by yangdb
DB이야기2011/05/03 01:56


생각은 복잡하게 하되 결과는 단순해야 한다.

저작자 표시

'DB이야기' 카테고리의 다른 글

DB 이야기  (0) 2011/05/03
공백 이야기  (0) 2011/04/13
Lock pages in memory Setting for 64 bit Standard Edition of SQL Server  (0) 2011/01/19
아 쓰댕..  (0) 2010/12/17
Posted by yangdb
DB창고2011/04/29 15:52

사용자 정의 데이터 형식(가장 높음)
sql_variant
xml
datetimeoffset
datetime2
datetime
smalldatetime
date
time
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar(nvarchar(max) 포함)
nchar
varchar(varchar(max) 포함)
char
varbinary(varbinary(max) 포함)
binary(가장 낮음)



 

저작자 표시

'DB창고' 카테고리의 다른 글

SQL2008 데이터형 우선순위  (0) 2011/04/29
SQL MD5  (0) 2011/04/29
NUMA, Hyperthreading and NUMA.PreferHT  (0) 2011/04/25
실행계획 보기  (0) 2011/04/22
ODBC Connection Failed: SQLState: '08001' DBMSSOCN  (0) 2011/04/15
SQL Server Perfmon Counters Poster  (4) 2011/03/07
Posted by yangdb
DB창고2011/04/29 11:40

select SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', '123456')), 3, 32) 
저작자 표시

'DB창고' 카테고리의 다른 글

SQL2008 데이터형 우선순위  (0) 2011/04/29
SQL MD5  (0) 2011/04/29
NUMA, Hyperthreading and NUMA.PreferHT  (0) 2011/04/25
실행계획 보기  (0) 2011/04/22
ODBC Connection Failed: SQLState: '08001' DBMSSOCN  (0) 2011/04/15
SQL Server Perfmon Counters Poster  (4) 2011/03/07
Posted by yangdb
잡담2011/04/28 17:59

회사에서 퍼블리싱 하는 게임 DB 스크립트를 전달 받았다.

스크립트가 십만팔천 라인이다.

테스트 서버에 돌려보니 12시간 40분이 걸렸다.

스크립트 적용하는데 중간 중간 오류 나는거 수정한 시간 까지 포함하면 거의 16시간을 소비했다.

내가 뭐하고 있는 걸까란.. 생각이 문득 든다..

저작자 표시

'잡담' 카테고리의 다른 글

지친다.  (0) 2011/05/06
DB 패치  (0) 2011/04/28
Microsoft iSCSI Software Target 3.3  (0) 2011/04/26
내가 다가가지 않으면...  (0) 2011/04/15
여직원..  (0) 2011/03/30
사라브라이트만  (0) 2010/10/29
Posted by yangdb
잡담2011/04/26 11:28
오호 재밌는 기능이네..

http://technet.microsoft.com/en-us/library/gg232606(WS.10).aspx
저작자 표시

'잡담' 카테고리의 다른 글

지친다.  (0) 2011/05/06
DB 패치  (0) 2011/04/28
Microsoft iSCSI Software Target 3.3  (0) 2011/04/26
내가 다가가지 않으면...  (0) 2011/04/15
여직원..  (0) 2011/03/30
사라브라이트만  (0) 2010/10/29
Posted by yangdb
DB창고2011/04/25 16:12

by http://frankdenneman.nl/2010/10/numa-hyperthreading-and-numa-preferht/


I received a lot of questions about Hyperthreading and NUMA in ESX 4.1 after writing the ESX 4.1 NUMA scheduling article. A common misconception is that Hyperthreading is ignored and therefore not used on a NUMA system. This is not entirely true and due to the improved Hyperthreading code on Nehalems, the CPU scheduler is programmed to use the HT feature more aggressively than the previous releases of ESX. The main reason why I think this misconception exists is the way the NUMA load balancer handles vCPU placement of vSMP virtual machine. Before continuing, let’s get our CPU elements nomenclature aligned, I’ve created a diagram showing all the elements:

NUMA and CPU elemenents

The Nehalem Hyperthreading feature is officially called Symmetric MultiThreading (SMT), the term HT and SMT are interchangeable.

1. An Intel Nehalem processor often called a CPU or package.
2. An Intel Nehalem processor contains 4 cores in one package.
3. Each core contains 2 threads if Hyperthreading is enabled.
4. A SMT Thread equals a logical processor.
5. A logical processor is translated in esxtop as a PCPU.
6. A vCPU is scheduled on a PCPU.
7. NUMA= Non-uniform Memory Access (Each Processor has its own local memory assigned)
8. LLC= Last Level Cache: Shared by Cores is last on-die cache memory before turning to Local memory.

NUMA load balancer virtual machine placement
During placement of a vSMP virtual machine, the NUMA load balancer assigns a single vCPU per CPU core and “ignores” the availability of SMT threads. As a result a 4-way vSMP virtual machine will be placed on four cores. In ESX 4.1 this virtual machine can be placed on one processor or on two processors, depending on the amount of cores on the processor or if set the advanced option numa.vcpu.maxPerMachineNode.

When a virtual machine contains more vCPUs than the amount of cores the processor, this virtual machine will span across multiple processors (Wide-VM). The default policy is to span the virtual machine across as few processors (NUMA nodes) as possible, but this can be overridden by an advanced option called numa.vcpu.maxPerMachineNode, which defines the maximum amount of vCPUs of a virtual machine per NUMA client. But as always, only use advanced options if you know the full impact of this setting on your environment. But I digress; let’s go back to NUMA and Hyperthreading.

Now the key to understand is that only during placement the SMT threads are ignored by the NUMA load balancer. It is the up to the CPU scheduler to decide in which way it will schedule the vCPUs within the core. It can allow the vCPU to use the full core or schedule it on a SMT thread depending on the workload, resource entitlement, the amount of active vCPUs and available pCPUs in the system.
Because SMT threads share resources within a core will result into lesser performance than running a vCPU on a dedicated singe core. The ESX scheduler is designed in such a way that it will try to spread the load across all the cores in the NUMA node or in the server.
But basically, If the workload is low it will try to schedule the vCPU on a complete core, if that’s not possible, it will schedule the vCPU on a SMT thread.

As mentioned before, running a vCPU on a SMT thread will not offer the same progress than running on a complete core; therefore a different charging scheme is used for each scenario. This charging scheme is used to keep track of the delivered resources and to check if the VM gets it entitled resources, more on this topic can be found in the article “Reservations and CPU scheduling”.

NUMA.preferHT=One NUMA node to rule them all?
Although the CPU scheduler can decide how to schedule the vCPU within the core, it will only schedule one vCPU of a vSMP virtual machine onto one core. Scott Drummonds article about numa.preferHT might offer a solution. Setting the advanced parameter numa.preferHT=1 allows the NUMA load balancer to assign vCPU to SMT thread and if possible “contain” one vSMP VM into a single NUMA node. However the amount of vCPU must be less or equal than the amount of pCPUs within the NUMA node.

By placing all vCPUs within a processor a virtual machine with a “intensive-cache-footprint” workload can benefit from a “warmed-up” cache. The vCPUs can fetch the memory from Last Level Cache instead of turning to local memory resulting in less latency. And this is exactly why this setting might not be beneficial to most environments.

The numa.preferHT setting is a CPU scheduler wide setting, that means that the NUMA load balancer will place every vSMP virtual machine inside a processor i.e. both intensive cache workloads and low-cache footprint workloads. Currently the ESX 4.1 CPU scheduler does not detect different workloads so it cannot distinguish virtual machines from each other and select an appropriate placement method i.e. place the virtual machine within one processor and use SMT threads or use wide-VM numa placement and “isolate” a vCPU per core.

It is crucial to know that by placing all vCPU on one processor doesn’t guarantee it to have all its memory in local memory, the main goal is to use LLC as much as possible, but if there is a cache miss (memory not available in cache) it will fetch it from local memory. The VMkernel tries to keep memory as local as possible but if there is not enough room inside local memory, it will place the memory into remote memory. Storing memory in remote memory is still faster than swapping it out to disk but inter-socket communication is noticeable slower than intra-socket communications.

This brings me to migration of virtual machines between NUMA nodes, if a virtual machines home node is more heavily loaded than other NUMA nodes, it will be migrated to a less loaded NUMA node. During the migration phase, local memory turns into remote memory. This newly remote memory is moved gradually because moving memory has high overhead.

By using the numa.preferHT option forces you to scope the maximum amount of memory assigned to a virtual machine and the consolidation ratio. Having multiple virtual machine traverse the quick path interlink to fetch memory stored in remote memory defeats the purpose of containing the virtual machines inside a processor.

저작자 표시

'DB창고' 카테고리의 다른 글

SQL2008 데이터형 우선순위  (0) 2011/04/29
SQL MD5  (0) 2011/04/29
NUMA, Hyperthreading and NUMA.PreferHT  (0) 2011/04/25
실행계획 보기  (0) 2011/04/22
ODBC Connection Failed: SQLState: '08001' DBMSSOCN  (0) 2011/04/15
SQL Server Perfmon Counters Poster  (4) 2011/03/07
Posted by yangdb
DB창고2011/04/22 20:05

SELECT    

      db_name(qt.dbid) AS 'db_name'   

    , OBJECT_NAME(qt.objectid,qt.dbid) as sp_name   

    , qp.query_plan   

    , execution_count as execution_count   

    , ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second'   

    , DATEDIFF(Minute, qs.cached_time, GetDate()) AS 'Age in Cache(min)'   

    , qs.cached_time   

    , qs.last_execution_time   

    , ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0 AS 'avg_Elapsed_Time(ms)'   

    , qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'   

    , max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'   

    , ISNULL(qs.total_worker_time/qs.execution_count, 0)/1000.0 AS 'avg_worker_time(ms)'   

    , total_worker_time/1000.0/1000.0  as 'total_worker_time(sec)'   

    , qs.max_worker_time/1000.0 as 'max_worker_time(msdb)'   

    , ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'   

    , total_logical_reads as total_logical_reads   

    , qs.max_logical_reads as max_logical_reads   

    , ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'   

    , total_physical_reads as total_physical_reads   

    , qs.max_physical_reads as max_physical_reads   

    , ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'   

    , qs.total_logical_writes as total_logical_writes   

    , qs.max_logical_writes as max_logical_writes   

    , qs.plan_handle

FROM

    sys.dm_exec_procedure_stats AS qs   

CROSS APPLY

    sys.dm_exec_sql_text(qs.sql_handle) AS qt   

CROSS APPLY

    sys.dm_exec_query_plan(qs.plan_handle) qp   

where

    db_name(qt.dbid) is not null   

    AND OBJECT_NAME(qt.objectid,qt.dbid) = @ProcName

    AND qt.dbid = DB_ID(@DBName)

저작자 표시
Posted by yangdb
DB창고2011/04/15 22:52
that error msg Invalid Instance() makes me think your
providor is using a named instance, just ask them if they are, then
reference your server as "servername\instancename" (check the slash
direction, can't remember off hand if it's forward or backward).
Another issue could be the port number, try either "servername,1433"
or "servername\instancename,1433" as your server address. this is
often a problem if your using load balanced sql server. also there's
somestuff about the server knowing which port to reply on (for
sql2005), but this is a bit more complex and sometimes requires some
server side config in awkward situations, anyways if it's 2k5 your
connecting to read up about sqlbrowser replying to the client on port
1434 instead of 1433.
저작자 표시
Posted by yangdb