SQL Server Counters With AG

* Counter Kaldırmak için; unlodctr MSSQL$TEST * Counter Eklemek için; lodctr "C:Program FilesMicrosoft SQL ServerMSSQL14.TESTMSSQLBinnperf-MSSQL$TESTsqlctr.ini" * Counter Kontrol Etmek için; select * from sys.dm_os_performance_counters order by object_name Aşağıdaki template’i data collector içerine xml uzantılı olarak eklenebilir. <?xml version="1.0" encoding="UTF-16"?> -<DataCollectorSet> <Status>0</Status> <Duration>0</Duration> <Description> </Description> <DescriptionUnresolved> </DescriptionUnresolved> <DisplayName> </DisplayName> <DisplayNameUnresolved> </DisplayNameUnresolved> <SchedulesEnabled>-1</SchedulesEnabled> <LatestOutputLocation> </LatestOutputLocation> <Name>SQLServerCountersWithAG</Name> <OutputLocation>C:perflogServerName</OutputLocation> <RootPath>C:perflogServerName</RootPath> <Segment>0</Segment> <SegmentMaxDuration>0</SegmentMaxDuration> <SegmentMaxSize>2048</SegmentMaxSize> <SerialNumber>1</SerialNumber> <Server> </Server> <Subdirectory> </Subdirectory> <SubdirectoryFormat>1</SubdirectoryFormat> <SubdirectoryFormatPattern>yyyyMMdd-NNNNNN </SubdirectoryFormatPattern> <Task> </Task> <TaskRunAsSelf>0</TaskRunAsSelf> <TaskArguments> </TaskArguments> <TaskUserTextArguments> </TaskUserTextArguments> <UserAccount>SYSTEM</UserAccount> <StopOnCompletion>0</StopOnCompletion> -<PerformanceCounterDataCollector> <DataCollectorType>0</DataCollectorType> <Name>DataCollector01</Name> <FileName>Test</FileName> <FileNameFormat>1</FileNameFormat> <FileNameFormatPattern>N </FileNameFormatPattern> <LogAppend>0</LogAppend> <LogCircular>-1</LogCircular> <LogOverwrite>0</LogOverwrite> <LatestOutputLocation> </LatestOutputLocation> <DataSourceName> </DataSourceName> <SampleInterval>10</SampleInterval> <SegmentMaxRecords>0</SegmentMaxRecords> <LogFileFormat>3</LogFileFormat> <Counter>CacheDirty Pages</Counter> <Counter>CacheLazy Write Flushes/sec</Counter> <Counter>LogicalDisk(*)% Free Space</Counter> <Counter>LogicalDisk(*)% Idle Time</Counter> <Counter>LogicalDisk(*)Avg. Disk Bytes/Read</Counter> <Counter>LogicalDisk(*)Avg. Disk Bytes/Transfer</Counter> <Counter>LogicalDisk(*)Avg. Disk Bytes/Write</Counter> <Counter>LogicalDisk(*)Avg. Disk Queue Length</Counter> <Counter>LogicalDisk(*)Avg. Disk sec/Read</Counter> <Counter>LogicalDisk(*)Avg. Disk sec/Transfer</Counter> <Counter>LogicalDisk(*)Avg. Disk sec/Write</Counter> <Counter>LogicalDisk(*)Current Disk Queue Length</Counter> <Counter>LogicalDisk(*)Disk Bytes/sec</Counter> <Counter>LogicalDisk(*)Disk Reads/sec</Counter> <Counter>LogicalDisk(*)Disk Transfers/sec</Counter> <Counter>LogicalDisk(*)Disk Writes/sec</Counter> <Counter>LogicalDisk(*)Free Megabytes</Counter> <Counter>Memory% Committed Bytes In Use</Counter> <Counter>MemoryAvailable MBytes</Counter> <Counter>MemoryCommit Limit</Counter> <Counter>MemoryCommitted […]

SQL Server Counters With AG

* Counter Kaldırmak için;

unlodctr MSSQL$TEST

* Counter Eklemek için;

lodctr "C:Program FilesMicrosoft SQL ServerMSSQL14.TESTMSSQLBinnperf-MSSQL$TESTsqlctr.ini"

* Counter Kontrol Etmek için;

select * from sys.dm_os_performance_counters order by object_name

Aşağıdaki template’i data collector içerine xml uzantılı olarak eklenebilir.

<?xml version="1.0" encoding="UTF-16"?>
-<DataCollectorSet>
<Status>0</Status>
<Duration>0</Duration>
<Description> </Description>
<DescriptionUnresolved> </DescriptionUnresolved>
<DisplayName> </DisplayName>
<DisplayNameUnresolved> </DisplayNameUnresolved>
<SchedulesEnabled>-1</SchedulesEnabled>
<LatestOutputLocation> </LatestOutputLocation>
<Name>SQLServerCountersWithAG</Name>
<OutputLocation>C:perflogServerName</OutputLocation>
<RootPath>C:perflogServerName</RootPath>
<Segment>0</Segment>
<SegmentMaxDuration>0</SegmentMaxDuration>
<SegmentMaxSize>2048</SegmentMaxSize>
<SerialNumber>1</SerialNumber>
<Server> </Server>
<Subdirectory> </Subdirectory>
<SubdirectoryFormat>1</SubdirectoryFormat>
<SubdirectoryFormatPattern>yyyyMMdd-NNNNNN </SubdirectoryFormatPattern>
<Task> </Task>
<TaskRunAsSelf>0</TaskRunAsSelf>
<TaskArguments> </TaskArguments>
<TaskUserTextArguments> </TaskUserTextArguments>
<UserAccount>SYSTEM</UserAccount>
<StopOnCompletion>0</StopOnCompletion>
-<PerformanceCounterDataCollector>
<DataCollectorType>0</DataCollectorType>
<Name>DataCollector01</Name>
<FileName>Test</FileName>
<FileNameFormat>1</FileNameFormat>
<FileNameFormatPattern>N </FileNameFormatPattern>
<LogAppend>0</LogAppend>
<LogCircular>-1</LogCircular>
<LogOverwrite>0</LogOverwrite>
<LatestOutputLocation> </LatestOutputLocation>
<DataSourceName> </DataSourceName>
<SampleInterval>10</SampleInterval>
<SegmentMaxRecords>0</SegmentMaxRecords>
<LogFileFormat>3</LogFileFormat>
<Counter>CacheDirty Pages</Counter>
<Counter>CacheLazy Write Flushes/sec</Counter>
<Counter>LogicalDisk(*)% Free Space</Counter>
<Counter>LogicalDisk(*)% Idle Time</Counter>
<Counter>LogicalDisk(*)Avg. Disk Bytes/Read</Counter>
<Counter>LogicalDisk(*)Avg. Disk Bytes/Transfer</Counter>
<Counter>LogicalDisk(*)Avg. Disk Bytes/Write</Counter>
<Counter>LogicalDisk(*)Avg. Disk Queue Length</Counter>
<Counter>LogicalDisk(*)Avg. Disk sec/Read</Counter>
<Counter>LogicalDisk(*)Avg. Disk sec/Transfer</Counter>
<Counter>LogicalDisk(*)Avg. Disk sec/Write</Counter>
<Counter>LogicalDisk(*)Current Disk Queue Length</Counter>
<Counter>LogicalDisk(*)Disk Bytes/sec</Counter>
<Counter>LogicalDisk(*)Disk Reads/sec</Counter>
<Counter>LogicalDisk(*)Disk Transfers/sec</Counter>
<Counter>LogicalDisk(*)Disk Writes/sec</Counter>
<Counter>LogicalDisk(*)Free Megabytes</Counter>
<Counter>Memory% Committed Bytes In Use</Counter>
<Counter>MemoryAvailable MBytes</Counter>
<Counter>MemoryCommit Limit</Counter>
<Counter>MemoryCommitted Bytes</Counter>
<Counter>MemoryFree & Zero Page List Bytes</Counter>
<Counter>MemoryFree System Page Table Entries</Counter>
<Counter>MemoryLong-Term Average Standby Cache Lifetime (s)</Counter>
<Counter>MemoryPages Input/sec</Counter>
<Counter>MemoryPages Output/sec</Counter>
<Counter>MemoryPages/sec</Counter>
<Counter>MemoryPool Nonpaged Bytes</Counter>
<Counter>MemoryPool Paged Bytes</Counter>
<Counter>MemoryPool Paged Resident Bytes</Counter>
<Counter>MemorySystem Cache Resident Bytes</Counter>
<Counter>MemoryTransition Pages RePurposed/sec</Counter>
<Counter>Network Inspection SystemAverage inspection latency (sec/bytes)</Counter>
<Counter>Network Interface(*)Bytes Received/sec</Counter>
<Counter>Network Interface(*)Bytes Sent/sec</Counter>
<Counter>Network Interface(*)Bytes Total/sec</Counter>
<Counter>Network Interface(*)Current Bandwidth</Counter>
<Counter>Network Interface(*)Output Queue Length</Counter>
<Counter>Network Interface(*)Packets Outbound Errors</Counter>
<Counter>Network Interface(*)Packets Received/sec</Counter>
<Counter>Network Interface(*)Packets Sent/sec</Counter>
<Counter>Network Interface(*)Packets/sec</Counter>
<Counter>Paging File(*)% Usage</Counter>
<Counter>Paging File(*)% Usage Peak</Counter>
<Counter>PhysicalDisk(*)Avg. Disk Queue Length</Counter>
<Counter>PhysicalDisk(*)Avg. Disk sec/Read</Counter>
<Counter>PhysicalDisk(*)Avg. Disk sec/Write</Counter>
<Counter>PhysicalDisk(*)Current Disk Queue Length</Counter>
<Counter>PhysicalDisk(*)Disk Bytes/sec</Counter>
<Counter>Process(*)% Privileged Time</Counter>
<Counter>Process(*)% Processor Time</Counter>
<Counter>Process(*)Handle Count</Counter>
<Counter>Process(*)ID Process</Counter>
<Counter>Process(*)IO Data Operations/sec</Counter>
<Counter>Process(*)IO Other Operations/sec</Counter>
<Counter>Process(*)IO Read Operations/sec</Counter>
<Counter>Process(*)IO Write Operations/sec</Counter>
<Counter>Process(*)Private Bytes</Counter>
<Counter>Process(*)Thread Count</Counter>
<Counter>Process(*)Virtual Bytes</Counter>
<Counter>Process(*)Working Set</Counter>
<Counter>Processor Information(*)% DPC Time</Counter>
<Counter>Processor Information(*)% Interrupt Time</Counter>
<Counter>Processor Information(*)% of Maximum Frequency</Counter>
<Counter>Processor Information(*)% Privileged Time</Counter>
<Counter>Processor Information(*)% Processor Time</Counter>
<Counter>Processor Information(*)% User Time</Counter>
<Counter>Processor Information(*)DPC Rate</Counter>
<Counter>Processor Information(*)Parking Status</Counter>
<Counter>Processor(*)% DPC Time</Counter>
<Counter>Processor(*)% Interrupt Time</Counter>
<Counter>Processor(*)% Privileged Time</Counter>
<Counter>Processor(*)% Processor Time</Counter>
<Counter>Processor(*)% User Time</Counter>
<Counter>Processor(*)DPC Rate</Counter>
<Counter>ServerPool Nonpaged Failures</Counter>
<Counter>ServerPool Paged Failures</Counter>
<Counter>SystemContext Switches/sec</Counter>
<Counter>SystemProcessor Queue Length</Counter>
<Counter>SystemSystem Calls/sec</Counter>
<Counter>TCPv4Connection Failures</Counter>
<Counter>MSRS 2011 Web ServiceCache Misses/Sec</Counter>
<Counter>MSRS 2011 Web ServiceReport Requests</Counter>
<Counter>MSRS 2011 Web ServiceTotal Memory Cache Misses</Counter>
<Counter>MSRS 2011 Web ServiceTotal Requests</Counter>
<Counter>Process(sqlservr)% Privileged Time</Counter>
<Counter>Process(sqlservr)% Processor Time</Counter>
<Counter>ReportServer:ServiceErrors Total</Counter>
<Counter>ReportServer:ServiceErrors/sec</Counter>
<Counter>ReportServer:ServiceMemory Pressure State</Counter>
<Counter>ReportServer:ServiceMemory Shrink Amount</Counter>
<Counter>ReportServer:ServiceMemory Shrink Notifications/sec</Counter>
<Counter>SQLAgent:JobsActive jobs</Counter>
<Counter>SQLAgent:JobsFailed jobs</Counter>
<Counter>SQLAgent:JobsJob success rate</Counter>
<Counter>SQLAgent:JobsSuccessful jobs</Counter>
<Counter>SQLAgent:JobStepsActive steps</Counter>
<Counter>SQLAgent:JobStepsTotal step retries</Counter>
<Counter>SQLServer:Access MethodsForwarded Records/sec</Counter>
<Counter>SQLServer:Access MethodsFreeSpace Scans/sec</Counter>
<Counter>SQLServer:Access MethodsFull Scans/sec</Counter>
<Counter>SQLServer:Access MethodsIndex Searches/sec</Counter>
<Counter>SQLServer:Access MethodsMixed page allocations/sec</Counter>
<Counter>SQLServer:Access MethodsPage Splits/sec</Counter>
<Counter>SQLServer:Access MethodsRange scans/sec</Counter>
<Counter>SQLServer:Access MethodsScan Point Revalidations/sec</Counter>
<Counter>SQLServer:Access MethodsTable Lock Escalations/sec</Counter>
<Counter>SQLServer:Access MethodsWorkfiles Created/sec</Counter>
<Counter>SQLServer:Access MethodsWorktables Created/sec</Counter>
<Counter>SQLServer:Access MethodsWorktables From Cache Ratio</Counter>
<Counter>SQLServer:Availability Replica(*)Bytes Received from Replica/sec</Counter>
<Counter>SQLServer:Availability Replica(*)Bytes Sent to Replica/sec</Counter>
<Counter>SQLServer:Availability Replica(*)Bytes Sent to Transport/sec</Counter>
<Counter>SQLServer:Availability Replica(_Total)Receives from Replica/sec</Counter>
<Counter>SQLServer:Availability Replica(_Total)Resent Messages/sec</Counter>
<Counter>SQLServer:Availability Replica(_Total)Sends to Replica/sec</Counter>
<Counter>SQLServer:Buffer ManagerBackground writer pages/sec</Counter>
<Counter>SQLServer:Buffer ManagerBuffer cache hit ratio</Counter>
<Counter>SQLServer:Buffer ManagerCheckpoint pages/sec</Counter>
<Counter>SQLServer:Buffer ManagerFree list stalls/sec</Counter>
<Counter>SQLServer:Buffer ManagerFree pages</Counter>
<Counter>SQLServer:Buffer ManagerLazy writes/sec</Counter>
<Counter>SQLServer:Buffer ManagerPage life expectancy</Counter>
<Counter>SQLServer:Buffer ManagerPage lookups/sec</Counter>
<Counter>SQLServer:Buffer ManagerPage reads/sec</Counter>
<Counter>SQLServer:Buffer ManagerPage writes/sec</Counter>
<Counter>SQLServer:Buffer ManagerReadahead pages/sec</Counter>
<Counter>SQLServer:Buffer ManagerTarget pages</Counter>
<Counter>SQLServer:Buffer Node(*)Database pages</Counter>
<Counter>SQLServer:Buffer Node(*)Foreign pages</Counter>
<Counter>SQLServer:Buffer Node(*)Local node page lookups/sec</Counter>
<Counter>SQLServer:Buffer Node(*)Page life expectancy</Counter>
<Counter>SQLServer:Buffer Node(*)Remote node page lookups/sec</Counter>
<Counter>SQLServer:Cursor Manager by TypeCursor Requests/sec</Counter>
<Counter>SQLServer:Database Replica(*)Log Bytes Received/sec</Counter>
<Counter>SQLServer:Database Replica(*)Mirrored Write Transactions/sec</Counter>
<Counter>SQLServer:Database Replica(*)Recovery Queue</Counter>
<Counter>SQLServer:Database Replica(_Total)Log remaining for undo</Counter>
<Counter>SQLServer:Database Replica(_Total)Log Send Queue</Counter>
<Counter>SQLServer:Database Replica(_Total)Redo blocked/sec</Counter>
<Counter>SQLServer:Database Replica(_Total)Redo Bytes Remaining</Counter>
<Counter>SQLServer:Database Replica(_Total)Redone Bytes/sec</Counter>
<Counter>SQLServer:Database Replica(_Total)Total Log requiring undo</Counter>
<Counter>SQLServer:Database Replica(_Total)Transaction Delay</Counter>
<Counter>SQLServer:Databases(*)Active Transactions</Counter>
<Counter>SQLServer:Databases(*)Backup/Restore Throughput/sec</Counter>
<Counter>SQLServer:Databases(*)Bulk Copy Throughput/sec</Counter>
<Counter>SQLServer:Databases(*)Data File(s) Size (KB)</Counter>
<Counter>SQLServer:Databases(*)Log Bytes Flushed/sec</Counter>
<Counter>SQLServer:Databases(*)Log File(s) Size (KB)</Counter>
<Counter>SQLServer:Databases(*)Log File(s) Used Size (KB)</Counter>
<Counter>SQLServer:Databases(*)Log Flush Wait Time</Counter>
<Counter>SQLServer:Databases(*)Log Flush Waits/sec</Counter>
<Counter>SQLServer:Databases(*)Log Flushes/sec</Counter>
<Counter>SQLServer:Databases(*)Log Growths</Counter>
<Counter>SQLServer:Databases(*)Log Shrinks</Counter>
<Counter>SQLServer:Databases(*)Log Truncations</Counter>
<Counter>SQLServer:Databases(*)Percent Log Used</Counter>
<Counter>SQLServer:Deprecated Features(*)Usage</Counter>
<Counter>SQLServer:General StatisticsActive Temp Tables</Counter>
<Counter>SQLServer:General StatisticsLogins/sec</Counter>
<Counter>SQLServer:General StatisticsLogouts/sec</Counter>
<Counter>SQLServer:General StatisticsProcesses Blocked</Counter>
<Counter>SQLServer:General StatisticsTemp Tables Creation Rate</Counter>
<Counter>SQLServer:General StatisticsTemp Tables For Destruction</Counter>
<Counter>SQLServer:General StatisticsUser Connections</Counter>
<Counter>SQLServer:LatchesAverage Latch Wait Time (ms)</Counter>
<Counter>SQLServer:LatchesLatch Waits/sec</Counter>
<Counter>SQLServer:LatchesTotal Latch Wait Time (ms)</Counter>
<Counter>SQLServer:Locks(*)Average Wait Time (ms)</Counter>
<Counter>SQLServer:Locks(*)Lock Requests/sec</Counter>
<Counter>SQLServer:Locks(*)Lock Timeouts/sec</Counter>
<Counter>SQLServer:Locks(*)Lock Wait Time (ms)</Counter>
<Counter>SQLServer:Locks(*)Lock Waits/sec</Counter>
<Counter>SQLServer:Locks(*)Number of Deadlocks/sec</Counter>
<Counter>SQLServer:Memory ManagerGranted Workspace Memory (KB)</Counter>
<Counter>SQLServer:Memory ManagerMaximum Workspace Memory (KB)</Counter>
<Counter>SQLServer:Memory ManagerMemory Grants Outstanding</Counter>
<Counter>SQLServer:Memory ManagerMemory Grants Pending</Counter>
<Counter>SQLServer:Memory ManagerOptimizer Memory (KB)</Counter>
<Counter>SQLServer:Memory ManagerStolen Server Memory (KB)</Counter>
<Counter>SQLServer:Memory ManagerTarget Server Memory (KB)</Counter>
<Counter>SQLServer:Memory ManagerTarget Server Memory(KB)</Counter>
<Counter>SQLServer:Memory ManagerTotal Server Memory (KB)</Counter>
<Counter>SQLServer:Memory Node(*)Database Node Memory (KB)</Counter>
<Counter>SQLServer:Memory Node(*)Foreign Node Memory (KB)</Counter>
<Counter>SQLServer:Memory Node(*)Stolen Node Memory (KB)</Counter>
<Counter>SQLServer:Memory Node(*)Target Node Memory (KB)</Counter>
<Counter>SQLServer:Memory Node(000)Total Node Memory (KB)</Counter>
<Counter>SQLServer:Plan Cache(*)Cache Hit Ratio</Counter>
<Counter>SQLServer:Query Store(*)Query Store CPU Usage</Counter>
<Counter>SQLServer:Query Store(*)Query Store logical reads</Counter>
<Counter>SQLServer:Query Store(*)Query Store logical writes</Counter>
<Counter>SQLServer:Query Store(*)Query Store physical reads</Counter>
<Counter>SQLServer:Resource Pool Stats(*)CPU usage %</Counter>
<Counter>SQLServer:Resource Pool Stats(*)Max memory (KB)</Counter>
<Counter>SQLServer:Resource Pool Stats(*)Target memory (KB)</Counter>
<Counter>SQLServer:Resource Pool Stats(*)Used memory (KB)</Counter>
<Counter>SQLServer:SQL Errors(*)Errors/sec</Counter>
<Counter>SQLServer:SQL StatisticsAuto-Param Attempts/sec</Counter>
<Counter>SQLServer:SQL StatisticsBatch Requests/sec</Counter>
<Counter>SQLServer:SQL StatisticsFailed Auto-Params/sec</Counter>
<Counter>SQLServer:SQL StatisticsSafe Auto-Params/sec</Counter>
<Counter>SQLServer:SQL StatisticsSQL Attention rate</Counter>
<Counter>SQLServer:SQL StatisticsSQL Compilations/sec</Counter>
<Counter>SQLServer:SQL StatisticsSQL Re-Compilations/sec</Counter>
<Counter>SQLServer:SQL StatisticsUnsafe Auto-Params/sec</Counter>
<Counter>SQLServer:TransactionsFree Space in tempdb (KB)</Counter>
<Counter>SQLServer:TransactionsLongest Transaction Running Time</Counter>
<Counter>SQLServer:TransactionsNonSnapshot Version Transactions</Counter>
<Counter>SQLServer:TransactionsSnapshot Transactions</Counter>
<Counter>SQLServer:TransactionsTransactions</Counter>
<Counter>SQLServer:TransactionsVersion Cleanup rate (KB/s)</Counter>
<Counter>SQLServer:TransactionsVersion Generation rate (KB/s)</Counter>
<Counter>SQLServer:TransactionsVersion Store Size (KB)</Counter>
<Counter>SQLServer:User Settable(*)Query</Counter>
<Counter>SQLServer:Wait Statistics(*)Lock waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Log write waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Network IO waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Non-Page latch waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Page IO latch waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Page latch waits</Counter>
<Counter>SQLServer:Wait Statistics(*)Wait for the worker</Counter>
<Counter>SQLServer:Workload Group Stats(*)Active parallel threads</Counter>
<Counter>SQLServer:Workload Group Stats(*)Active requests</Counter>
<Counter>SQLServer:Workload Group Stats(*)CPU usage %</Counter>
<Counter>SQLServer:Workload Group Stats(*)Queued requests</Counter>
<Counter>SQLServer:Workload Group Stats(*)Reduced memory grants/sec</Counter>
<Counter>SQLServer:Workload Group Stats(*)Requests completed/sec</Counter>
<Counter>SQLServer:Workload Group Stats(*)Suboptimal plans/sec</Counter>
</PerformanceCounterDataCollector>
-<DataManager>
<Enabled>0</Enabled>
<CheckBeforeRunning>0</CheckBeforeRunning>
<MinFreeDisk>0</MinFreeDisk>
<MaxSize>0</MaxSize>
<MaxFolderCount>0</MaxFolderCount>
<ResourcePolicy>0</ResourcePolicy>
<ReportFileName>report.html</ReportFileName>
<RuleTargetFileName>report.xml</RuleTargetFileName>
<EventsFileName> </EventsFileName>
</DataManager>
</DataCollectorSet>
Benzer Yazılar

HashTable HashIndex HashPartition

SQL Server 6 gün önce

Bu makalede modern veri tabanı sistemlerinde verimliliği artıran HashTable, HashIndex ve Hashpartition yapılarını inceleyeceğiz. Bu kavramların nasıl çalıştığını, avantajlarını, dezavantajlarını ve pratik kullanım örneklerini SQL kodlarıyla açıklayacağız.İçindekilerHash Table Nedir?Hash Index Nedir?Hash Partitioning Nedir? Hash tabanlı bir veri yapısı tasarlayacağımız ortamda bilmemiz gereken özellikle hash partitioning ya da bucketing gibi bölümlendirme stratejilerinde sıkça karşımıza çıkacak bir parametre olan bucket_count, verilerin kaç adet bucket içine dağıtılacağını belirleyen sayısal bir değerdir. Her bir bucket, belirli bir hash değeri aralığına denk gelir ve veriler, belirli bir kolona uygulanan hash fonksiyonunun çıktısına göre bu bucket ‘lardan birine atanır. Peki, bu bizim için neden önemli? Doğru belirlenmiş bir bucket_count değeri, verilerin eşit dağılımı sağlar bu da sorgu performansını iyileştirir. Bucket sayısı, paralel çalışan işlemlerin sayısını doğrudan etkiler, örneğin bir tablo 16 bucket sahipse, aynı anda 16 thread veriyi işleyebilir. Aynı bucket sayısıyla bölünmüş tablolar arasında yapılan join işlemleri daha verimli olur. CREATE TABLE UserActiviy ( Id_user […]

In-Memory Table and Native Stored Procedures

SQL Server 1 ay önce

Bu makalede 2014 yılında yayınlanan In-Memory Table OLTP(Online Transaction Processing) Engine yapısından bahsedeceğim.İçindekilerKaynak: In-Memory Table, adından da anlaşılacağı üzere, verileri fiziksel disk yerine RAM üzerinde saklandığı özel bir tablo türüdür. Bu yapı, geleneksel disk tabanlı tablolara kıyasla çok daha hızlı veri okuma ve yazma performansı sunar çünkü disk erişimi esnasında yaşanan darboğaz sorununa alternatif bir çözüm sunmuştur. Verinin, buffer pool içinde cache’lenmesi, düşük gecikme(low latency) ve yüksek throughput en büyük avantajlarıdır. Özellikle yüksek hacimli veri işleyen, çok sayıda eş zamanlı işlem gerçekleştiren veya gerçek zamanlı yanıt süresi gerektiren sistemlerin kullanımı oldukça yaygındır. (Örneğin: finansal platformlar, IoT işlemleri, sipariş işlemleri) Avantajlarını göz önünde bulundurarak o zaman her ortama bunu yapalım diyebiliriz 🙂 pahalı bir donanım olan RAM ‘den sınırsız bir şekilde elinizde bulunduruyor olmanız gerekebilir çünkü In-Memory bulunduğu ortama yüksek RAM maliyeti, kendine has filegroup yapısı ile yönetimsel zorluklar, failover sürelerinin uzaması gibi dezavantajları da beraberinde getirmektedir. In-Memory Tabloların kendine özgü […]

SMART BACKUP

SQL Server 1 ay önce

Bu makalede SQL Server 2017 versiyonuyla gelen DMV ‘ler ile kullanımı kolaylaşan bir özellik olan Smart Backup dan bahsedeceğim. Bu makaleye başlamadan şunu belirtmekte fayda var; bu özelliği üçüncü parti yazılımlar veya eklentiler ile yapabilmek mümkün MSSQL haricinde diğer ilişkisel veri tabanı sistemlerinde de buna benzer özellikler tanımlanabilir, kullanılabilir. Bu makalemizin anlaşılabilirliğini artırmak adına öncelikle performans ve veri bütünlüğü açısından hayati öneme sahip olan Checkpoint kavramından bahsetmek istiyorum; SQL Server, veri değişikliklerini önce RAM’de (buffer cache) tutar, bu sırada log dosyasına (LDF) işlemi yazar. Checkpoint olduğunda, bu değişmiş (dirty) geçici verileri .LDF dosyasından kalıcı hale getirir .MDF dosyasına (diske) yazar. Checkpoint, varsayılan olarak her 60 saniyede bir çalışır ancak SQL Server 2012 ile indirect checkpoints tanıtıldı ve veri tabanı bazlı olarak ayarlanabilir. Şimdi iki Checkpoint yönetimine daha yakından bakarak artı ve eksilerinden bahsedelim; Otomatik checkpoint modunda, tüm buffer pool’daki sayfalar taranır ve değişmiş sayfalar bulunur bu ilk baktığımızda bizim için […]

1 Yorum

Yorum Yaz

Rastgele